Consider the relational database with the following four schemas and their…
2022
Consider the relational database with the following four schemas and their respective instances.
Student(sNo, sName, dNo) Dept(dNo, dName)
Course(cNo, cName, dNo) Register(sNo, cNo)
SQL Query:
SELECT * FROM Student AS S WHERE NOT EXIST
(SELECT cNo FROM Course WHERE dNo = “D01”
EXCEPT
SELECT cNo FROM Register WHERE sNo = S.sNo)
The number of rows returned by the above SQL query is___________.
Attempted by 91 students.
Show answer & explanation
Correct answer: 2
Key insight: the query selects students who have registered for every course offered by department D01.
Courses offered by D01: C11 and C12.
Meaning of the subquery: (courses in D01) EXCEPT (courses the student has registered). If this difference is empty, NOT EXISTS is true, so the student has registered for all D01 courses.
Check each student against D01 courses:
S01 (James): registered C11 and C12 → qualifies.
S02 (Rocky): registered C11 only → missing C12 → does not qualify.
S03 (Jackson): no registrations for D01 courses → does not qualify.
S04 (Jane): registered C11 and C12 → qualifies.
S05 (Milli): registered C11 only → missing C12 → does not qualify.
Conclusion: Two students qualify (S01 James and S04 Jane), so the query returns 2 rows.
A video solution is available for this question — log in and enroll to watch it.