Database Design and SQL Consider the following relations: Student (snum: integer

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.

Explanation / Answer

/****************** 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);

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---