Question

Suppliers(Sid: integer, sname:string, city:string, street:string)

Parts(pid:integer, pname:string, color:string)

Catalog(sid:integer, pid:integer, 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 of the following is the correct interpretation of the above query?

1. Find the names of all suppliers who have supplied a non-blue part
2. Find the names of all suppliers who have not supplied a non-blue part
3. Find the names of all suppliers who have supplied only blue parts  
4. Find the names of all suppliers who have not supplied only blue parts  

Answer 1

Correct Answer - Option 4 : Find the names of all suppliers who have not supplied only blue parts  

The correct answer is "option 4".

EXPLANATION:

The above query will return the Name of all suppliers who will not supply blue parts, which means it can include non-blue parts & null values.

Consider the following table:

Name	ID	COLOR
A	1	Blue
B	2	Blue
C	3	Blue
D	4	Blue
E	5	Red
F	6	Red
G	7	Red
H	8	Red
I	9	Null
J	10	Null

 

The QUERY "SELECT P.pid FROM Parts P WHERE P.color < > ‘blue’ " will give:

NAME	ID	COLOR
E	5	red
F	6	Red
G	7	Red
H	8	Red
I	9	Null
J	10	Null

 

The Query "SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P

WHERE P.color < > ‘blue’)" will give :

NAME	ID	COLOR
A	1	Blue
B	2	Blue
C	3	Blue
D	4	Blue

 

The query " 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’)) " will give :

NAME	ID	COLOR
E	5	Red
F	6	Red
G	7	Red
H	8	Red
I	9	Null
J	10	Null

 

Option 1: FALSE

The non-blue part does not include the Null values.

Option 2: FALSE

It will include both Null values and Blue parts.

Option 3: FALSE

It will include only Blue parts.

Option 4: TRUE

It will include non-blue parts and null values.

Hence, The correct answer is "option 4".