5,031 views
5 votes
5 votes

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.

4 Answers

8 votes
8 votes

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

0 votes
0 votes
Answer will be option A and C both as per ISRO-2015 modified answer keys.
0 votes
0 votes
Take an example:

suppliers                              catalog                               parts

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

sid                                     sid    pid                              pid    color

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

1                                       1       22                               22    Blue

2                                                                                   23   Red

 

Answer (B)
0 votes
0 votes

Answer in (C) Part.

Explanation -->

Answer:

Related questions

5 votes
5 votes
3 answers
2
go_editor asked Jun 17, 2016
5,500 views
The maximum length of an attribute of type text is$127$$255$$256$It is variable
7 votes
7 votes
3 answers
4
go_editor asked Jun 19, 2016
3,768 views
If a node has $K$ children in $B$ tree, then the node contains exactly _____ keys.$K^2$$K-1$$K+1$$\sqrt{K}$