Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)
SELECT S.sname 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'))
the answer of this will be :
the inner most quesry returns the product ids of the products which are not blue then the midlevel query will first apply the where condition on the entire relation and remove the tuples of the suppliers that supplied non blue products but the catalog table has a composite key of both pid and sid hence even the suppliers who supplied blue and nonblue (combindly) and the suppliers who sold all blue products will remain and those who sell only non blue parts will be filtered and those who sell bothe non blue and blue and those who sell blue alone will remain .the outer query says not in so now we remove these two categories and hence the supplier ids of those who sell non blue parts ALONE will be printed there is no option for this hence this must be an add mark @pritham sir what do u say ??
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
(A) Find the names of all suppliers who have supplied a non-blue part.
(B) Find the names of all suppliers who have not supplied a non-blue part.
(C) Find the names of all suppliers who have supplied only blue parts.
(D) Find the names of all suppliers who have not supplied only blue parts.