How to write the sql to represent ALL for example Find the n

How to write the sql to represent \"ALL\", for example Find the names of all students who have taken every course taught by Eric Lee. I think it is easy to handle it with relational division in relational algebra. Without using \"Except\" or \"Contains\" how to represent the \"division\"? Please give some examples.

Solution

Database schema:

Department(deptid, dname, location)

Student(snum, sname, deptid, slevel, age)

Faculty(fid, fname, deptid)

Class(cname, time, room, fid)

Enrolled(snum, cname)

SELECT s.sname FROM Student s WHERE 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 = ‘Eric Lee\');

\"IN \" handles required result above

It can also be written as

select s.* from Student s join Faculty f on f.deptid=s.deptid join class c on Class c on c.fid=f.fid join Enrolled e on e.snum=s.snum and e.cname=c.cname where f.fname =\'Eric Lee\';