Consider the relational database with the following four schemas and their respective instances:

Student(sNo, sName, dN...

Question

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)

Dept:
dNo | dName
D01 | CSE
D02 | EEE

Course:
cNo | cName | dNo
C11 | OS    | D01
C12 | OS    | D01
C21 | DE    | D02
C22 | PT    | D02
C23 | CV    | D03

Register:
sNo | cNo
S01 | C12
S02 | C11
S03 | C21
S03 | C22
S04 | C11
S04 | C12
S05 | C21

Consider the following SQL query:

SELECT sNo
FROM Student AS S
WHERE dNo = 'D01'
  AND sNo IN (
    SELECT sNo FROM Register AS R
    WHERE cNo IN (
      SELECT cNo FROM Course WHERE dNo = 'D01'
    )
  )
EXCEPT
SELECT sNo
FROM Student
WHERE dNo = 'D01'
  AND sNo NOT IN (
    SELECT sNo FROM Register
    WHERE cNo IN (
      SELECT cNo FROM Course WHERE dNo = 'D01'
    )
  )

The number of rows returned by the above SQL query is ____________.
A. 4
B. 5
C. 0
D. 3

Accepted Answer

D. 3 — the query returns 3 rows (S01, S02, S04). We have four tables: Student, Dept, Course, and Register. The SQL query first finds D01 students who ARE registered in at least one D01 course, then uses EXCEPT to remove those D01 students who are NOT registered in any D01 course. We need to count how many rows the final result has.