Question

Database Design and SQL

Consider the following relations:

Student (snum: integer, sname: string, major: string, level: string, age: integer)
Class (name: string, meets_at: time, room: string, fid: integer)
Enrollment (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)

Write the following queries in SQL. Also, there should be no duplicates printed in any of the answers.

a) Find the names of all students who are enrolled in two classes that meet at the same time.
b) Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
c) Print the Level and the average age of students for that Level, for each Level.
d) Print the Level and the average age of students for that Level, for all Levels except JR.
e) Find the names of students who are enrolled in the maximum number of classes.
f) Find the names of students who are not enrolled in any class.

Answer 1

/****************** ANSWER A**********************/
SELECT DISTINCT S.SNAME
FROM
  STUDENT S
WHERE
  S.SNUM IN (SELECT E1.SNUM
             FROM
               ENROLLMENT E1,
               ENROLLMENT 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
  );

/****************** ANSWER B**********************/
SELECT DISTINCT F.fname
FROM
  FACULTY F
WHERE 5 > (SELECT COUNT(E.SNUM)
           FROM CLASS C, ENROLLMENT E
           WHERE C.NAME = E.CNAME AND C.FID = F.FID);


/****************** ANSWER C**********************/
SELECT
  S.LEVEL,
  AVG(S.AGE) AS 'AVERAGE AGE'
FROM STUDENT S
GROUP BY S.LEVEL;


/****************** ANSWER D**********************/

SELECT
  S.LEVEL,
  AVG(S.AGE) AS 'AVERAGE AGE'
FROM STUDENT S
WHERE S.LEVEL <> 'JR'
GROUP BY S.LEVEL;


/****************** ANSWER E**********************/

SELECT DISTINCT S.SNAME
FROM STUDENT S
WHERE S.SNUM IN (SELECT E.SNUM
                 FROM ENROLLMENT E
                 GROUP BY E.SNUM);


/****************** ANSWER E**********************/

SELECT DISTINCT S.SNAME
FROM STUDENT S
WHERE S.SNUM NOT IN (SELECT E.SNUM
                     FROM ENROLLMENT E);