SQL

Question

Consider the Following Collection of relational schema

• Professor( profname , deptname)
• Department (deptname, building)
• Committee(commname, Profname)

Which of following query finds all the professors who have not offices in any of those buildings that professor Piper has offices in

A. 

Select distinct P.profname 
from professor P 
Where not exists (( 
    Select building 
    from department d 
    where P.deptname= D.deptname ) 
    Union (
    Select building 
    from department D1, professor P1 
    Where P1.profname=’Piper’ and P1.deptname= D1.deptname ))

B. 

Select distinct P.profname Prom professor P
Where not exists
(( Select building from department d where P.deptname= D.deptname )
Intersect
(Select building from department D 1., professor P1
Where P1.profname=’Piper’ and P1.deptname= D1.deptname))

C)

Select distinct P.profname
From professor P
Where not exists(
Select Building from Department d1, professor p1
Where P1.Profname=’Piper’ and p1.deptname=D1.deptname )

D) None of these

Answer 1

Option B is correct, even though it has NOT EXISTS, it checks whether set of the building(s) belonging to the "current" professor (in the outer query) has any intersection with the set of building(s) belonging to Prof. Piper, and if they intersect, we shouldn't take the current professor, otherwise yes. This is ensured by the NOT EXISTS clause that is dependent on the outer query this time. 

Comments

Arjun sir are you trying to say that dept of piper can be same with any other prof??

---

@Arjun Sir query is correlated..
For each Professor P it checks inner query.
If it is nested then NOT EXISTS always return false but here inner query relates outer query by correlated variable P..

Is it not so ??

---

Yes. I missed the 'P' in the inner query. B is correct.

Answer 2

Acc to me b is the correct answer let me explain what it does

Output the profname
If number of tuples generated in this below query is < 0

Buildings in which a particular employee works
Intersect
Buildings in which piper works