student (rollno, name, degree, year, sex, deptno, advisor)
department (deptid, name, hod, phone)
professor (empid, name, sex, startyear, deptno, phone)
course (courseid, cname, credits, deptno)
enrollment (rollno, courseid, sem, year, grade)
teaching (empid, courseid, sem, year, classroom)
prerequisite (precourseid, courseid)

Which of the following queries would retrieve the roll numbers and names of students who have enrolled in ALL the prerequisite courses of course number 324?

1. A

   SELECT s.rollno, s.name FROM student s, enrollment e
   WHERE e.rollno = s.rollno AND e.courseid = ANY
     (SELECT precourseid FROM prerequisite p WHERE p.courseid = '324')

2. B

   SELECT s.rollno, s.name FROM student s, enrollment e
   WHERE e.rollno = s.rollno AND e.courseid = ALL
     (SELECT precourseid FROM prerequisite p WHERE p.courseid = '324')

3. C

   SELECT s.rollno, s.name FROM student s
   WHERE NOT EXISTS (SELECT * FROM prerequisite p
     WHERE p.courseid = '324' AND NOT EXISTS
       (SELECT * FROM enrollment e
        WHERE e.courseid = p.precourseid AND e.rollno = s.rollno))

4. D

   SELECT s.rollno, s.name FROM student s
   WHERE EXISTS (SELECT * FROM prerequisite p
     WHERE p.courseid = '324' AND EXISTS
       (SELECT * FROM enrollment e
        WHERE e.courseid = p.precourseid AND e.rollno = s.rollno))

Pick an option to check yourself, or open the answer below.

SELECT s.rollno, s.name FROM student s
WHERE NOT EXISTS (SELECT * FROM prerequisite p
  WHERE p.courseid = '324' AND NOT EXISTS
    (SELECT * FROM enrollment e
     WHERE e.courseid = p.precourseid AND e.rollno = s.rollno))