Question

Question (3)

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)

The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.

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

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

ii) (2 points) Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach.

iii) (2 points) Find the names of all classes that either meet in room R128 or have five or more students enrolled.

iv) (3 points) Find the names of all students who are enrolled in two classes that meet at the same time.

v) (2 points) Find the names of faculty members who teach in every room in which some class is taught.

vi) (2 points) Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.

vii) (2 points) For each level, print the level and the average age of students for that level.

**Please Write in Oracle SqlPlus**

Answer 1

1)

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.fhame = ‘I.Teach’ AND S.level = ‘JR’

2)

SELECT MAX(S.age) FROM Student S WHERE (S.major = ‘History’) OR S.num 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)

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)

4)

SELECT DISTINCT S.sname FROM Student S WHERE S.snum IN (SELECT E1.snum FROM Enrolled El, Enrolled E2, Class Cl, Class C2 WHERE E1.snum = E2.snum AND E1.cname <> E2.cname AND E1.cname = C1.name AND E2.cname = C2.name AND Cl.meets_at = C2.meets_at)

5)

SELECT DISTINCT F.fname FROM Faculty F WHERE NOT EXISTS (( SELECT *FROM Class C )EXCEPT(SELECT C1.room FROM Class C1 WHERE C1.fid = F.fid ))

6)

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)

7)

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