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)

GATE CSE 2022 Q56: Rows returned by a NOT EXISTS ... EXCEPT division query

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

Student( $\underline{sNo}$ , sName, dNo) Dept( $\underline{dNo}$ , dName)

Course( $\underline{cNo}$ , cName, dNo) Register( $\underline{sNo}$ , $\underline{cNo}$ )

Dept
dNo	dName
D01	CSE
D02	EEE

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___________.

Try it: numerical answer: type the value

Your answer

Numerical answer. No negative marking.

Show answer and explanation

Answer: 2 (official GATE 2022 final answer key)

Concept used: NOT EXISTS (A EXCEPT B) selects students whose registered courses include every course in A (relational division).

First step: The D01 courses are C11 and C12; find the students registered for both.

Detailed working, common mistakes and linked mock tests will be available with a free GATEiit account. Log-in is coming soon.

Share this question

Source: official GATE 2022 CS question paper and final answer key, organised by IIT Kharagpur. Question paper · Answer key