sql exp 3


--1. Write query to display roll numbers and names.
SELECT
    rollno,
    name
FROM
    student;

--2. Write query to display roll numbers and subject5 marks.
SELECT
    rollno,
    sub5
FROM
    student_marks;

--3. Write query to display list of address.
SELECT
    address
FROM
    student;

--4. Write query to display list of addresses
but avoid repetition.
SELECT
    distinct address
FROM
    student;

--5. Write query to display student table.
SELECT
    *
FROM
    student;

-- 6. Write query to display roll number,total
-- marks and percentage marks obtained by
-- students.
SELECT
    rollno,
    sub1 + sub2 + sub3 + sub4 + sub5,
    (sub1 + sub2 + sub3 + sub4 + sub5) / 500 * 100
FROM
    student_marks;

-- 7. Write query to display roll number and
-- name of FinalTC students.
SELECT
    rollno,
    name
FROM
    student
WHERE
    class = 'FinalTC';

-- 8. Write query to display details of students
-- of Ichalkaranji.
SELECT
    *
FROM
    student
WHERE
    address = 'Ichalkaranji';

-- 9. Write query to display roll number and
-- subject1 marks of students who have
-- passed in subject1.
SELECT
    rollno,
    sub1
FROM
    student_marks
WHERE
    sub1 >= 40;

-- 10. Write query to display details of students
-- who are not from Ichalkaranji.
SELECT
    *
FROM
    student
WHERE
    address <> 'Ichalkaranji';

-- 12. Write query to display details of students
-- of MMTT department.
SELECT
    *
FROM
    student
WHERE
    class = 'FYMMTT'
    or class = 'SYMMTT'
    or class = 'TYMMTT'
    or class = 'FinalMMTT';

-- 13. Write query to display roll number and
-- marks of students who have passed in all
-- subjects.
SELECT
    *
FROM
    student_marks
WHERE
    sub1 >= 40
    and sub2 >= 40
    and sub3 >= 40
    and sub4 >= 40
    and sub5 >= 40;

-- 14. Write query to display roll number and
-- marks of students who have not passed in
-- all subjects.
SELECT
    *
FROM
    student_marks
WHERE
    not (
        sub1 >= 40
        and sub2 >= 40
        and sub3 >= 40
        and sub4 >= 40
        and sub5 >= 40
    );

-- 15. Write query to display roll number and
-- marks in subjects of students having marks
-- in between 40 to 60 in subject1.
SELECT
    rollno,
    sub1
FROM
    student_marks
WHERE
    sub1 between 40
    and 60;

-- 16. Write query to display roll number, total
-- marks and percentage marks of students.
-- Rename roll number as PRN, total marks as
-- total_marks and percentage marks as
-- percentage.
SELECT
    rollno AS PRN,
    sub1 + sub2 + sub3 + sub4 + sub5 AS Total_Marks,
    (sub1 + sub2 + sub3 + sub4 + sub5) / 500 * 100 AS Percentage
FROM
    student_marks;

-- 17. Write query to display details of students
-- in ascending order of roll number.
SELECT
    *
FROM
    student
ORDER BY
    rollno;

-- 18. Write query to display details of
-- students in alphabetical order of name.
SELECT
    *
FROM
    student
ORDER BY
    name;

-- 19. Write query to display details of students
-- in alphabetical order of address and if
-- address is same, display those records in
-- ascending order of roll number.
SELECT
    *
FROM
    student
ORDER BY
    address asc,
    rollno asc;

-- 20. Write query to display total marks in
-- subject1, average marks in subject2,
-- minimum marks in subject3, maximum
-- marks in subject4 and number of students
-- appeared for subject5. Rename fields of
-- output table appropriately.
SELECT
    sum(sub1) AS Total_Sub1,
    avg(sub2) AS Average_Sub2,
    min(sub3) AS Sub3_Min,
    max(sub4) AS Sub4_Max,
    count(sub5) AS Sub5_Count
FROM
    student_marks;

-- 21. Write query to display number of records
-- student table has.
SELECT
    count(*) AS No_of_Students
FROM
    student -- 22. Write query to display number of
    -- students of MMTT department.
SELECT
    count(*) AS no_of_students
FROM
    student
WHERE
    class = 'FYMMTT'
    or class = 'SYMMTT'
    or class = 'TYMMTT'
    or class = 'FinalMMTT';

-- 23. Write query to display number of
-- students of Ichalkaranji.
SELECT
    count(*) AS no_of_students
FROM
    student
WHERE
    address = 'Ichalkaranji';

-- 24. Write query to display number of
-- students of each place.
SELECT
    address,
    count(*) AS strength
FROM
    student
GROUP BY
    address;

-- 25. Write query to display number of
-- students of each class.
SELECT
    class,
    count(*) AS strength
FROM
    student
GROUP BY
    class;

-- 26. Write query to display places from where
-- only one student is coming.
SELECT
    address,
    count(*) AS strength
FROM
    student
GROUP BY
    address
HAVING
    count(*) = 1;

-- 27. Write a query to display roll number, name, class and marks obtained by students in all
-- subjects.
SELECT
    student.rollno,
    name,
    class,
    sub1,
    sub2,
    sub3,
    sub4,
    sub5
FROM
    student,
    student_marks
WHERE
    student.rollno = student_marks.rollno;

-- 28. Write a query to display roll number, name, class, marks obtained in all subjects, total marks,
-- percentage marks of all clear students. In the output table, display records in ascending order
-- of percentage.
SELECT
    student.rollno,
    name,
    class,
    sub1,
    sub2,
    sub3,
    sub4,
    sub5,
    sub1 + sub2 + sub3 + sub4 + sub5 AS Total_Marks,
    (sub1 + sub2 + sub3 + sub4 + sub5) / 5 AS Percentage
FROM
    student,
    student_marks
WHERE
    student.rollno = student_marks.rollno
    and sub1 >= 40
    and sub2 >= 40
    and sub3 >= 40
    and sub4 >= 40
    and sub5 >= 40
ORDER BY
    (sub1 + sub2 + sub3 + sub4 + sub5) / 5;

Post a Comment

0 Comments