Consider the following relational schema:
Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
Consider the following relational query on the above database:
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C.sid
FROM Catalog C
WHERE C.pid NOT IN (SELECT P.pid
FROM Parts P
WHERE P.color<> \'blue\'))
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
Find the names of all suppliers who have supplied a non-blue part.
Find the names of all suppliers who have not supplied a non-blue part.
Find the names of all suppliers who have supplied only blue parts.
Find the names of all suppliers who have not supplied only blue parts.
(D) option matched because given query returns suppliers who have not supplied any blue parts. That means it can include other than blue parts.
(A): False, as this may include blue parts and may not include \”null\” parts.
(B): Obviously false because it returning other than any blue part.
(C): Obviously false because it does not return this.
(D): Correct. Please try here: http://sqlfiddle.com/#!9/9ae12d/1/0
This explanation is contributed by Archit Garg.
Quiz of this Question
Please comment below if you find anything wrong in the above post
Level Up Your GATE Prep!
Embark on a transformative journey towards GATE success by choosing Data Science & AI
as your second paper choice with our specialized course. If you find yourself lost in the vast landscape of the GATE syllabus, our program is the compass you need.