Consider the following relational schema: Suppliers(sid:integer, sname:string,…

2015

Consider the following relational schema: 

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 one of the following is the correct interpretation of the above query?

  1. A.

    Find the names of all suppliers who have supplied a non-blue part.

  2. B.

    Find the names of all suppliers who have not supplied a non-blue part

  3. C.

    Find the names of all suppliers who have supplied only non blue parts.

  4. D.

    Find the names of all suppliers who have not supplied only non-blue parts.

Attempted by 238 students.

Show answer & explanation

Correct answer: C

Let’s simplify the query step by step.

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

Step 1: Innermost query

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

→ All non-blue parts

Step 2:

C.pid NOT IN (non-blue parts)

→ Parts that are NOT non-blue = only blue parts

So this gives:
→ Suppliers who supply blue parts

Step 3:

SELECT C.sid ...

→ Supplier IDs who supply at least one blue part

Step 4:

S.sid NOT IN (...)

→ Suppliers who do NOT supply any blue parts

Final Meaning:

Suppliers who supply no blue parts, i.e., they supply only non-blue parts (given relations are not empty).

✅ Correct Answer:

Option 3: Find the names of all suppliers who have supplied only non blue parts.

Explore the full course: Isro