Which of the following is correct implementation of given SQL query(Suppliers, Catalog,Parts)?

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.

2Comments
Pritam Prasun @pritam 13 Aug 2017 11:59 pm

Yes, None of the options is correct.

We can apply a bottom up approach (as you said) for better understanding:

1. SELECT P.pid FROM Parts P WHERE P.color<> 'blue' : Non-Blue parts id
2. SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (1):  Supplier ID who supply at least one blue part
3. SELECT S.sname FROM Suppliers S WHERE S.sid NOT IN (2): Supplier name who supplies only non-blue parts. 

venkat @saikumartadi 14 Aug 2017 12:34 am

thanks a lot sir :) finally i got a good platform  where i get my doubts clarified thanks a lot !!!