GATE CSE 2009 | Question: 55

Question

Consider the following relational schema:

$\text{Suppliers}(\underline{\text{sid:integer}},\text{ sname:string, city:string, street:string})$ 

$\text{Parts}(\underline{\text{pid:integer}}, \text{ pname:string, color:string})$ 

$\text{Catalog}(\underline {\text{sid:integer, pid:integer}}, \text{cost:real})$

Consider the following relational query on the above database:

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

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 non-blue part.

• D. Find the names of all suppliers who have not supplied only blue parts.

Comments

Here given option C is wrong. (c)  who have supplied only blue parts

---

Similar example to understand this question:

$\\ Select\ S.sname\\ from\ sailors\ S\\ where\ S.sid\ NOT\ IN(Select\ R.sid\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\ from\ reserves\ R\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\ where\ R.bid\ NOT\ IN(Select\ B.bid\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\quad \quad \quad \quad \quad \quad\quad\quad\quad\quad from\ boats\ B\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\quad\quad \quad \quad \quad \quad\quad\quad\quad\quad where\ B.color= red))$

 

Output: Collection of sailor names who have not reserved anything otherwise if at all they have reserved anything then they have reserved only red boats.

---

which answer was given marks in gate 2009 for this question can't we ever know for sure ..

Answer 1

The subquery “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

source - http://www.geeksforgeeks.org/gate-gate-cs-2009-question-55/

and in made easy book they also given A so please confirm and i also think A because in your answer also you have said 

"Selects suppliers who have not supplied any blue parts." so in other words it can be said as  names of all suppliers who have supplied a non-blue part. and that is option A

Answer 2

so none can be the answer.

Answer 3

I think the actual result of the query is –

If any supplier supplies any part then it must colour other than blue .

So no answer matches that requirment.

Answer 4

SELECT P.pid FROM Parts P WHERE P.color<>'blue'

means select all those parts whose color is not blue

 

SELECT C.sid 
FROM Catalog C 
WHERE C.pid NOT IN (SELECT P.pid 
                    FROM Parts P 
                    WHERE P.color<>'blue')

means select all those suppliers who have not supplied any non-blue part => Select all those suppliers who have supplied only blue parts.

 

 

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

Select all those suppliers who have not supplied only blue parts. So D is correct.

A should be incorrect since it this query also includes those suppliers who have supplied no part, as they have also not supplied only blue parts. However option A prints only names of those suppliers who have supplied at least 1 part.