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

 

Comments

ALWAYS REMEMBER CORELLATED QUERY EXECUTE  TUPLE BY TUPLE

MEANS WE WILL RUN INNER QUERY FOR EVERY TUPLE OF OUTER QUERY SEPREATELY .

---

This query basically prints all the students who have registered for all the courses of D01, right?

---

TOTAL ROWS RETURNED = 2

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!

---

Nice Explaination

---

Big Thanks to you Man

Its cleared by doubt to solve this question in future :)

Answer 2

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

Comments

Very good step by step explanation. Thanks.

---

Thanks a lot Ray

---

Good explanation! Thanks

Answer 3

EASIEST EXPLANATION

 

Whenever you see exists or exists check for the emptiness of the subquery 

1.EXISTS returns true if the subquery is not empty

2.NOT EXISTS returns true if the subquery is empty

 

Now see here here its a correlated subquery not just a subquery .

So for every Student Tuple check the inner subquery .

For example take the first tuple for the STUDENT relation i.e [S01,JAMES,D01] WHERE NOT EXISTS((C11,C12) EXCEPT(C11 C12)).hence here the correlated subquery is empty so the tuple(S01,JAMES,D01) comes in the output .Similarly if you do you could get (S04,JANE,D01) in the Output.

HENCE THE ANSWER WILL BE 2

Comments

Thanks was really confused it was a  great explanation indeed

---

video solution https://www.youtube.com/watch?v=CMfGeZ2TDr8
you explained the crux of question....

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.

Answer 5

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 6

NOTE:

EXCEPT == MINUS == SET DIFFERENCE OPERATOR IN Discrete Maths

A-B = will have value only when A is non empty AND A not equal to B.

A-B= will have ZERO values when A equal to B. [ Question asking this condition ]

FROM QUESTION:

A={c11 , c12}  // All courses of CSE department

students(sNo)  who having cNo { c11 ,c12 } then we printing those students details in querry...[Becoz of NOT EXIST]

So Querry Giving all the students details who enrolled in all the CSE {c11, c12} courses.so it will print students details of {S01 , S04}.

So answer is 2 tuples.

 

 

Comments

Thanks was really confused great explanation indeed