GATE CSE 2022 | Question: 46

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)

$$\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 ____________.

 

Answer 1

Answer is 2

 

Comments

@adithya_nayak Good answer. It would be better if you reupload it with better quality

---

@adithya_nayak Very well explained!

Answer 2

Answer : The number of row returned by the above sql query is 2

Answer 3

Given that correlated query,

Query returning the tuples from S where students who’re registered with all the courses which are associated with D01 department.

By seeing the relation instances, we can understand that only two students from S ( S01 and S04) , registered with all the courses which are associated with D01 department.

Therefore Query will return 2 rules from S.

 

How to get output without analyzing the query ?

take each tuple from S, execute the inner query.

Answer 4

basically the query is asking for students who have enrolled in both c11 and c12 and here is how:-

1. Not exist returns true when their subquery is a empty set so tuples which will be selected from student table are those which will have inner query as empty set.                                                                             
2. 1’st part of sub query selects c11 and c12 and now we have to select cno for each tuple of student table(S.sno) and if it contains c11 and c12 then subquery will be empty which will return true and hence it will be selected.