The GATE Grind

GATE 2022 CS – Question 56

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Numerical answer

Consider the relational database with the following four schemas and their respective instances.
Student(sNo, sName, dNo): (S01, James, D01), (S02, Rocky, D01), (S03, Jackson, D02), (S04, Jane, D01), (S05, Milli, D02)
Dept(dNo, dName): (D01, CSE), (D02, EEE)
Course(cNo, cName, dNo): (C11, DS, D01), (C12, OS, D01), (C21, DE, D02), (C22, PT, D02), (C23, CV, D03)
Register(sNo, cNo): (S01,C11), (S01,C12), (S02,C11), (S03,C22), (S03,C23), (S04,C11), (S04,C12), (S05,C11), (S05,C21)
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 ___________.

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: 2

Explanation

The query keeps students $S$ for which $\{\text{courses of dept D01}\}-\{\text{courses } S \text{ has registered for}\}=\emptyset$ (`NOT EXISTS` of the leftover set). That is, students who registered for **every** course of department D01.

Courses of D01: $C11$, $C12$.

StudentRegistered coursesCovers C11 and C12?
S01C11, C12yes
S02C11no
S03C22, C23no
S04C11, C12yes
S05C11, C21no

**2** rows (S01 and S04).

More Relational Model: Relational Algebra, Tuple Calculus, SQL questions

All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →