Consider the following database
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(cname: string, meets_at: time, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
Specify the following queries in SQL on the database schema
i. Find the names of all students who are enrolled in two classes that meet at the same time.
Select S.sname
From Student S
Where S.snum in (
Select E.snum
From Enrolled E, Enrolled E1, Class C1, Class C2
Where E.snum = E1.snum
And E.cname = C1.cname
And E1.cname = C2.cname
And C1.cname <> C2.cname
And C1.meets_at = C2.meets_at
)
AND
S.snum in (
Select E1.snum
From Enrolled E1
Group by E1.snum
Having count(E1.snum) = 2
)
ii. Find the names of faculty members who teach in every room in which some class is taught.
Select F.fname
From Faculty F
Where NOT EXISTS (
Select C.room
From Class C
Where c.room not in
(
Select C1.room
From Class C1
Where F.fid = C1.fid
)
)
iii. 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, F.fid, count(*)
From Faculty F
Where EXISTS (
Select C.fid
From Class C
Where .room = ‘R128’
And F.fid = C.fid
And c.fid not in
(
Select C1.fid
From Class C1
Where C1.room <> ‘R128’
)
)
Group by F.fname,F.fid
iv. Find the names of all classes that either meet in room R128 or have five or more students enrolled.
Select C.cname
From Class C
Where C.cname in (
Select C1.cname
From Class C1
Where C1.room = ‘R128’
)
OR
C.cname in (
Select E.cname
From Enrolled E
Group by (E.cname)
Having count (E.cname) >= 5
)
OR
Select C.cname
From Class C
Where C.room = ‘R128’
OR C.cname in (
Select E.cname
From Enrolled E
Group by (E.cname)
Having count (E.cname) >= 5
)
v. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
Select F.fname
From faculty F
Where ( Select count (C.fid)
From (Class C join Enrolled E on C.cname = E.cname and C.fid = F.fid)
Group by (fid)
) < 5
vi. Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach.
Select s.sname, max(age) as old_age
From Student S
Where S.major = ‘History’
OR S.snum in (
Select E.snum
From Enrolled E, Class C, Faculty F
Where E.cname = C.cname
And C.fid = F.fid
And F.fname = ‘I.Teach’
)
