2.9k views

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'))

1. Find the names of all suppliers who have supplied non-blue part.
2. Find the names of all suppliers who have not supplied non-blue part.
3. Find the names of all suppliers who have supplied only non-blue parts
4. Find the names of all suppliers who have not supplied only non-blue part.
| 2.9k views
+5

Ans (C)

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

by Boss (45.4k points)
+1
why not option C  ??
0
Because  IN operator allows us to specify multiple values in a WHERE clause. here we are not just considering only blue parts .thats how we are considering non blue parts but not only non blue parts.
+2

The complete query gives the suppliers who has not supplied ANY blue part. @shekhar rt?

0
0

ISRO answer key is interesting here :)

0
0
ans is c
Answer will be option A and C both as per ISRO-2015 modified answer keys.
by Junior (521 points)
0
That itself is a contradiction :)
Take an example:

suppliers                              catalog                               parts

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

sid                                     sid    pid                              pid    color

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

1                                       1       22                               22    Blue

2                                                                                   23   Red

by Boss (18.5k points)

Explanation -->

by