GATE CS 2022 Q46 — Tuple Calculus and SQL

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)

$$\begin{array} {|c|c|c|} \hline & \text{Students} & \\ \hline \text{sNo} & \text{sName} & \text{dNo} \\ \hline \text{S01} & \text{James} & \text{D01} \\ \hline \text{S02} & \text{Rocky} & \text{D01} \\ \hline \text{S03} & \text{Jackson} & \text{D02} \\ \hline \text{S04} & \text{Jane} & \text{D01} \\ \hline \text{S05} & \text{Milli} & \text{D02}\\ \hline \end{array} \quad \begin{array} {|c|c|} \hline & \text{Depth} \\ \hline \text{dNo} & \text{dName} \\ \hline \text{D01} & \text{CSE} \\ \hline \text{D02} & \text{EEE} \\ \hline \end{array} \quad \begin{array} {|c|c|c|} \hline & \text{Course} & \\ \hline \text{cNo} & \text{cName} & \text{dNo} \\ \hline \text{C11} & \text{DS} & \text{D01} \\ \hline \text{C12} & \text{OS} & \text{D01} \\ \hline \text{C21} & \text{DE} & \text{D02} \\ \hline \text{C22} & \text{PT} & \text{D02} \\ \hline \text{C23} & \text{CV} & \text{D03}\\ \hline \end{array}\quad \begin{array} {|c|c|} \hline & \text{Register} \\ \hline \text{sNo} & \text{cNo} \\ \hline \text{S01} & \text{C11} \\ \hline \text{S01} & \text{C12} \\ \hline \text{S02} & \text{C11} \\ \hline \text{S03} & \text{C21}\\ \hline \text{S03} & \text{C22} \\ \hline \text{S03} & \text{C23} \\ \hline \text{S04} & \text{C11} \\ \hline \text{S04} & \text{C12} \\ \hline \text{S05} & \text{C11}\\ \hline \text{S05} & \text{C21} \\ \hline \end{array}$$

$\textbf{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 $\text{SQL}$ query is ____________.