Question

Consider the following relational schema:

Faculty(fid: integer, fname: string, deptid: integer)

Student(snum: integer, sname: string, major: string, level: string, age: integer)

Class(name: string, meets_at: time, room: string, fid: integer)

Enrolled(snum: integer, cname: string)

Enrolled has one record per student-class pair such that the student is enrolled in the class. Write an SQL assertion for the following integrity constraint: “Every faculty member must teach at least two courses.”

Answer 1

Q1:
SELECT deptid
FROM Faculty
WHERE fname = ‘I.Teach’
Q2:
SELECT S.sname
FROM Student S
WHERE S.level = ‘JR’
ORDERED BY S.age
Q3:
SELECT COUNT(DISTINCT E.cname)
FROM Enrolled as E
Q4:
SELECT S.sname, S.major
FROM Student S, Enrolled E
WHERE S.snum = E.snum
Q5:
SELECT COUNT(DISTINCT S.sname)
FROM Student S, Enrolled E1, Enrolled E2
WHERE E1.snum = E2.snum AND
E1.cnum <> E2.cnum AND
S.snum = E1.snum
Q6:
SELECT DISTINCT S.sname as Student_Name
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’