in Databases edited by
975 views
3 votes
3 votes

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)
            Students
  sNo    sName   dNo
  S01    James   D01
  S02    Rocky   D01
  S03    Jackson   D02
  S04    Jane   D01
  S05    Milli   D02

$$\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}$$

            Depth
   dNo    dName
   D01    CSE
   D02    EEE

$$\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}$$

     Course  
    cNo   cName    dNo
    C11    DS    D01
    C12    OS    D01
    C21    DE    D02
    C22    PT    D02
    C23    CV    D03

$$\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}$$

             Register
   sNo    cNo
   S01    C11
   S01    C12
   S02    C11
   S03    C21
   S03    C22
   S03    C23
   S04    C11
   S04    C12
   S05    C11
   S05    C21

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

 
in Databases edited by
by
2437 3623 5535
975 views

Subscribe to GO Classes for GATE CSE 2022

2 Answers

1 vote
1 vote
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.
edited by
by
35 104 555
0 votes
0 votes

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.
ago
Answer:

Related questions