The Binary BrothersTwo Mentors, One Mission – Your Selection
All mock tests are FREE till 31 December — register to save your progress
Unit 4 · Database Management Systems Paper 2 CS · December 2022 (Shift 1)

Directions (Q96–100): Academic database schema: 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). deptno in student, professor and course refers to department.deptid; advisor refers to professor.empid; hod refers to professor.empid; enrollment.rollno refers to student.rollno; courseid in enrollment and teaching refers to course.courseid; teaching.empid refers to professor.empid; precourseid and courseid in prerequisite refer to course.courseid.

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

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

Show answer & explanation

Correct answer: C. Select s.rollno, s.name From student s Where NOT EXISTS (select * prerequisite p where p.courseid = " 324 ") and NOT EXISTS (select * from enrollment e where e.courseid = p.precourseid and e.rollno = s.rollno))

Explanation

'Enrolled in all prerequisites' is relational division, written in SQL as a double NOT EXISTS: there must not exist a prerequisite of 324 for which there is no enrollment by this student. Option C has this structure (with typos: a missing FROM and a misplaced AND).

Note on the official key: the official key says option B. Official/PW key is B. 'e.courseid = ALL (subquery)' compares one enrollment row with every prerequisite, which is true only when there is a single prerequisite (or none), so it does not express 'all prerequisites'.

More Database Management Systems questions