The Gateway to Computer Science Excellence
+4 votes
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.
in Databases by Veteran (106k points) | 2.9k views
+5

Ans (C)

4 Answers

+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

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

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

0
answer should be C
0
ans is c
0 votes
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 :)
0 votes
Take an example:

suppliers                              catalog                               parts

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

sid                                     sid    pid                              pid    color

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

1                                       1       22                               22    Blue

2                                                                                   23   Red

 

Answer (B)
by Boss (18.5k points)
0 votes

Answer in (C) Part.

Explanation -->

by
Answer:

Related questions

+3 votes
3 answers
2
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,391 answers
198,591 comments
105,442 users