lab: students data: solutions:
1. 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’

2. 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’ )

3. 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 )

4. 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)

5. 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

6. 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

7. For each faculty member, 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

8. 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)

9. 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)