retagged by
388 views
2 votes
2 votes

Consider the following relation schema:

Student (RollNo, Sname, Scity)
Packages (Pcode, Pname, Price)   
Registers (RollNo PCode, Date)    

What does the following SQL query return?

Select distinct S.RollNo From Student S
Where NOT EXISTS ( (Select P.Pcode From Packages P) Minus
(Select R.Pcode From Registers R Where R.RollNo = S.RollNo))
  1. RollNos of students who did not register for any of the packages.
  2. RollNos of students who did not register for at least one package.
  3. RollNos of students who registered for at least one package.
  4. RollNos of students who registered for all the packages.
retagged by

1 Answer

Best answer
6 votes
6 votes
Select distinct S.RollNo 
From Student S 
Where NOT EXISTS ( 
    (Select P.Pcode From Packages P)  
    Minus  
    (Select R.Pcode From Registers R Where R.RollNo = S.RollNo) 
)
(Select P.Pcode From Packages P)

This selects Pcode of all the packages.

(Select R.Pcode From Registers R Where R.RollNo = S.RollNo) 

This selects Pcode of packages that belong to that particular student.

Subtracting second from first will give, Pcode of packages that do not belong to that student. If this does not exist, then that student will be selected. We can say that a student is selected if there does not exist any package which does not belong to that student. 

Or, every package belongs to that student.

Or, student who registered for all the packages.

Option (D)

selected by
Answer:

Related questions

3 votes
3 votes
1 answer
4
Bikram asked Aug 26, 2017
394 views
What is the number of rows returned by an SQL query on the below EMP table?$\begin{array}{|c|c|c|} \hline \text{eno} & \text{ename} & \text{manager} \\ \hline 1 & A & \te...