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

Answer 2

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 3

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 4

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.