Categories
Uncategorized

Query

Ex 5.1 ( Ramakrishnan and Gehrke)

Consider the following relations:

Student(snum: integer, sname: string, major: string, level: string, age: integer)

Class(name: string, meets at: string, room: string, fid: integer)

Enrolled(snum: integer, cname: string)

Faculty(fid: integer, fname: string, deptid: integer)

Write the following queries in SQL. No duplicates should be printed in any of the answers.

  • Find the names of all Juniors (level = JR) who are enrolled in a class taught by I. Teach.

               SELECT DISTINCT S.Sname FROM Student S, Class C, Enrolled E, Faculty F

               WHERE S.snum = E.snum AND E.cname = C.name

               AND C.fid = F.fid AND F.fname = ‘I.Teach’ AND S.level = ‘JR’

  • Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach.

             SELECT MAX(S.age) FROM Student S

             WHERE (S.major = ‘History’) OR S.snum IN (SELECT E.snum FROM Class C, Enrolled E, Faculty F

                                                                                         WHERE E.cname = C.name AND C.fid = F.fid

                                                                                          AND F.fname = ‘I.Teach’ )

  • Find the names of all classes that either meet in room R128 or have five or more students enrolled.

                 SELECT C.name FROM Class C

                 WHERE C.room = ‘R128’ OR C.name IN (SELECT E.cname FROM Enrolled E

                                                                                       GROUP BY E.cname HAVING COUNT (*) >= 5)

  • Find the names of all students who are enrolled in two classes that meet at the same time.

            SELECT DISTINCT S.sname FROM Student S

            WHERE S.snum IN (SELECT E1.snum

                                               FROM Enrolled E1, Enrolled E2, Class C1, Class C2

                                               WHERE E1.snum = E2.snum AND E1.cname <> E2.cname

                                               AND E1.cname = C1.name AND E2.cname = C2.name

                                               AND C1.meets at = C2.meets at)

  • Find the names of faculty members who teach in every room in which some class is taught.

           SELECT DISTINCT F.fname FROM Faculty F

           WHERE NOT EXISTS (( SELECT * FROM Class C )

                                                 EXCEPT (SELECTC1.room FROM Class C1

                                                                WHERE C1.fid = F.fid ))

  • Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.

          SELECT DISTINCT F.fname FROM Faculty F

          WHERE 5 > (SELECT COUNT (E.snum) FROM Class C, Enrolled E

                               WHERE C.name = E.cname AND C.fid = F.fid)

  • For each level, print the level and the average age of students for that level.

          SELECT S.level, AVG(S.age) FROM Student S

          GROUP BY S.level.

  • For all levels except JR, print the level and the average age of students for that level.

           SELECT S.level, AVG(S.age) FROM Student S

           WHERE S.level <> ‘JR’ GROUP BY S.level

  • For each faculty member that has taught classes only in room R128, print the faculty member’s name and the total number of classes she or he has taught.

          SELECT F.fname, COUNT(*) AS CourseCount FROM Faculty F, Class C

          WHERE F.fid = C.fid GROUP BY F.fid, F.fname

          HAVING EVERY ( C.room = ‘R128’ )

  • Find the names of students enrolled in the maximum number of classes.

         SELECT DISTINCT S.sname FROM Student S

         WHERE S.snum IN (SELECT E.snum FROM Enrolled E

         GROUP BY E.snum

          HAVING COUNT (*) >= ALL (SELECT COUNT (*)

                                                           FROM Enrolled E2 GROUP BY E2.snum ))

  • Find the names of students not enrolled in any class.

           SELECT DISTINCT S.sname FROM Student S

           WHERE S.snum NOT IN (SELECT E.snum FROM Enrolled E )

  • For each age value that appears in Students, find the level value that appears most often. For example, if there are more FR level students aged 18 than SR, JR, or SO students aged 18, you should print the pair (18, FR).

          SELECT S.age, S.level FROM Student S

          GROUP BY S.age, S.level,

          HAVING S.level IN (SELECT S1.level FROM Student S1

                                           WHERE S1.age = S.age GROUP BY S1.level, S1.age

                                           HAVING COUNT (*) >= ALL (SELECT COUNT (*) FROM Student S2

                                                                                            WHERE s1.age = S2.age GROUP BY S2.level, S2.age))




            Reference: DBMS, Ramakrishna Gehrke Book.