In class, professor said that SQL language does not provide 'for all' operator.
In order to use 'for all' you have to use 'not exist( X except Y)'
At this point, I can't figure out why 'for all' is same meaning as 'not exist( X except Y)'
I give you example relation:
student as S
not exists (
(select cID from course where deptName = 'CS')
(select T.cID from takes as T where S.sID = T.sID)
You professor is right. SQL has no direct way to query all records that have all possible relations of a certain type.
It's easy to query which relations of a certain type a record has. Just
INNER JOIN the two tables and you are done.
But in an M:N relationship like "students" to "taken courses" it's not that simple.
To answer the question "which student has taken all possible courses" you must find out which relations could possibly exist and then make sure that all of them do actually exist.
select distinct S.sid, S.name from student as S where not exists ( (select cID from course where deptName = 'CS') except (select T.cID from takes as T where S.sID = T.sID) );
can be translated as
give me all students SELECT for whom it is true: WHERE that the following set is empty NOT EXISTS (any course in 'CS') "all relations that can possibly exist" minus EXCEPT (all courses the student has taken) "the ones that do actually exist"
In other words: Of all possible relations there is no relation that does not exist.
There are other ways of expressing the same thought that can be used in database systems without support for
select S.sid, S.name from student as S inner join takes as T on T.sID = S.sID inner join course as C on C. cID = T. cID where c. deptName = 'CS' group by S.sid, S.name having count(*) = (select count(*) from course where deptName = 'CS');