GATE 2022 CS – Question 56
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$.
| Student | Registered courses | Covers C11 and C12? |
|---|---|---|
| S01 | C11, C12 | yes |
| S02 | C11 | no |
| S03 | C22, C23 | no |
| S04 | C11, C12 | yes |
| S05 | C11, C21 | no |
**2** rows (S01 and S04).
More Relational Model: Relational Algebra, Tuple Calculus, SQL questions
- GATE 2021 CS (CS1) Q33
- GATE 2021 CS (CS1) Q37
- GATE 2020 CS Q23
- GATE 2019 CS Q61
- GATE 2019 CS Q65
- GATE 2018 CS Q22
All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →