Test by Bikram | Databases | Test 2 | Question: 22

Question

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))

• A. RollNos of students who did not register for any of the packages.
• B. RollNos of students who did not register for at least one package.
• C. RollNos of students who registered for at least one package.
• D. RollNos of students who registered for all the packages.

Comments

@Bikram Sir, i have a doubt, in the inner query we are selecting pcode and in outer query we are comparing it with srollno, how can it be done ?

---

@Surabhi Kadur In this question that doesn't matter because when inner query "Except operation (-)" returns "null" then "NOT EXISTS" became "TRUE" and that srollno will be printed.

Answer 1

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)