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

2009

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 blue parts.

  4. D.

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

Attempted by 173 students.

Show answer & explanation

Correct answer: A

Correct interpretation: Names of suppliers who do not supply any blue part (i.e., suppliers whose catalog contains no part whose color is 'blue').

  • The inner-most query SELECT P.pid FROM Parts P WHERE P.color <> 'blue' returns the set of part IDs that are non-blue.

  • The middle condition C.pid NOT IN (that set) selects catalog rows whose part ID is not a non-blue part. In other words, it selects catalog rows for parts that are blue. Therefore SELECT C.sid ... returns the set of supplier IDs that supply at least one blue part.

  • The outer condition S.sid NOT IN (that set of supplier IDs) keeps suppliers whose ID is not among suppliers who supply a blue part. Thus the outer query returns suppliers who supply zero blue parts (they may supply only non-blue parts or supply no parts at all).

Consequence: This is not the same as "suppliers who have supplied a non-blue part," because that description includes suppliers who supply both blue and non-blue parts, whereas the query excludes any supplier that supplies a blue part.

Explore the full course: Gate Guidance By Sanchit Sir