고지웅 고지웅 - 1 month ago 8
SQL Question

'for all' Queries in sql

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:


  • course (cID,title,deptName,credit),

  • teaches (pID,cID,semester,year,classroom),

  • student (sID,name,gender,deptName)



Q: Find all student names who have taken all courses offered in 'CS' department

The answer is:

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 you give me specific explain about that?

ps. Sorry for my English skill

Answer

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 EXCEPT.

For example

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');
Comments