GATE CS Mastery
Cheat sheets

GATE CS 2022 · Databases

Question 56

NAT2 marksNo negative markingMedium (estimate)

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)

Database instances for Student(sNo, sName, dNo), Dept(dNo, dName), Course(cNo, cName, dNo), and Register(sNo, cNo).

SQL Query:

SELECT * FROM Student AS S WHERE NOT EXISTS
 (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___________.

Official answer and solution
Official answer
2
From
Official GATE 2022 answer key

Solution

Evaluate the correlated NOT EXISTS / EXCEPT query for each student tuple.

The inner query computes:

(SELECT cNo FROM Course WHERE dNo = "D01"
 EXCEPT
 SELECT cNo FROM Register WHERE sNo = S.sNo)

  1. From the Course table, the courses offered by department D01 are:

    {cNo∣dNo="D01"}={"C11","C12"}\{\text{cNo} \mid \text{dNo} = \text{"D01"}\} = \{\text{"C11"}, \text{"C12"}\}

  2. For each student SS in the Student table, the query finds whether the student has registered for all courses in {"C11","C12"}\{\text{"C11"}, \text{"C12"}\}. The NOT EXISTS condition is TRUE if and only if the EXCEPT result is empty, meaning the student has registered for both C11 and C12:

  • S01 (James): Registered for C11, C12. {C11, C12}∖{C11, C12}=∅\{\text{C11, C12}\} \setminus \{\text{C11, C12}\} = \emptyset. NOT EXISTS is TRUE. (Selected)
  • S02 (Rocky): Registered for C11. {C11, C12}∖{C11}={C12}≠∅\{\text{C11, C12}\} \setminus \{\text{C11}\} = \{\text{C12}\} \ne \emptyset. NOT EXISTS is FALSE.
  • S03 (Jackson): Registered for C21, C22, C23. {C11, C12}∖{C21, C22, C23}={C11, C12}\{\text{C11, C12}\} \setminus \{\text{C21, C22, C23}\} = \{\text{C11, C12}\}. NOT EXISTS is FALSE.
  • S04 (Jane): Registered for C11, C12. {C11, C12}∖{C11, C12}=∅\{\text{C11, C12}\} \setminus \{\text{C11, C12}\} = \emptyset. NOT EXISTS is TRUE. (Selected)
  • S05 (Milli): Registered for C11, C21. {C11, C12}∖{C11, C21}={C12}\{\text{C11, C12}\} \setminus \{\text{C11, C21}\} = \{\text{C12}\}. NOT EXISTS is FALSE.

Thus, exactly 2 students (S01 and S04) satisfy the query condition, and 2 rows are returned.

Final answer

2

Common trap

Assuming the student must belong to department D01; the query filters on courses in D01, not the student's own department.

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper
GATE 2022 Computer Science and Information Technology (CS)
Question
56 of 65
Section
Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.