One can use NOT EXISTS to find clubs to which no one belongs, and then use NOT EXISTS again to find clubs
to which everyone belongs. Here is the example:
SELECT Cname
FROM Club
WHERE NOT EXISTS (
SELECT *
FROM Student
WHERE Student.Major = 'Math' AND NOT EXISTS (
SELECT *
FROM ClubMembership
WHERE Student.Sname = ClubMembership.MemberName
AND Club.Cname = ClubMembership.Cname));
This query works through each row in the Club table. For each row in the Club table it works through
each row in the Student table. Then for each Club row/Student row combination, it works through each
row in the ClubMembership table.
The innermost query finds clubs in which at least some math majors do not participate. However, if ALL
math majors participate in a particular club, the innermost query will return a NULL. If the innermost query is
NULL, then the innermost NOT EXISTS will be true (it??™s NULL; it does not exist; so NOT EXISTS is true). If
the innermost NOT EXISTS is true, then that club qualifies, and that club is reported as being one to which all
math majors belong.
It can take a while to digest this idea!
156 DATABASE [CHAP. 8
STORED PROCEDURES
Most database management systems allow users to create stored procedures.
Pages:
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431