ISRO2015-22

Question

Consider the following relational query on the above database:

SELECT S.name 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'))

• A. Find the names of all suppliers who have supplied non-blue part.
• B. Find the names of all suppliers who have not supplied non-blue part.
• C. Find the names of all suppliers who have supplied only non-blue parts
• D. Find the names of all suppliers who have not supplied only non-blue part.

Comments

Ans (C)

Answer 1

Answer : A

Find the names of all suppliers who have supplied non-blue part.

The sub-query “SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pids of parts which are not blue.

The bigger subquery “SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sids of all those suppliers who have supplied blue parts.

The complete query gives the names of all suppliers who have supplied a non-blue part

Comments

ISRO answer key is interesting here :)

https://drive.google.com/open?id=0B8_aYGBndW4Hd2JNWDYxYlVpYlU

---

answer should be C

---

ans is c

Answer 2

Answer will be option A and C both as per ISRO-2015 modified answer keys.

Comments

That itself is a contradiction :)

Answer 3

Take an example:

suppliers                              catalog                               parts

---------------------------------------------------------------------------------------------------------

sid                                     sid    pid                              pid    color

--------------------------------------------------------------------------------------------------------

1                                       1       22                               22    Blue

2                                                                                   23   Red

 

Answer (B)

Answer 4

Answer in (C) Part.

Explanation -->