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

2013

Consider the following relational schema: 

Suppliers (sid:integer, sname:string, saddress:string)
 Parts (pid:integer, pname:string, pcolor:string)
 Catalog (sid:integer, pid:integer, pcost:real)

What is the result of the following query? 

(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sid = Catalog.sid)
 MINUS
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid)
  1. A.

    pid of Parts supplied by all except sachin

  2. B.

    pid of Parts supplied only by sachin

  3. C.

    pid of Parts available in catalog supplied by sachin

  4. D.

    pid of Parts available in catalogs supplied by all except sachin

Attempted by 196 students.

Show answer & explanation

Correct answer: B

MINUS (also called EXCEPT) is a set-difference operator: for two same-shaped queries A and B, 'A MINUS B' keeps only the rows of A that do not also appear in B. A common exam pattern built on this is the 'associated with X only' query: if A = every value linked to ANY entity, and B = every value linked to at least one entity OTHER than X, then 'A MINUS B' is exactly the set of values whose ONLY linked entity is X.

  1. The first subquery -- SELECT Catalog.pid FROM Suppliers, Catalog WHERE Suppliers.sid = Catalog.sid -- joins every Catalog row to its supplier and projects pid, so it returns every pid that is supplied by some supplier (the full set of parts present in the catalog). (Note: the originally printed query compared Suppliers.sid to Catalog.pid, a transcription slip; the join condition here is corrected to Suppliers.sid = Catalog.sid so the two tables are actually linked by supplier id.)

  2. The second subquery -- SELECT Catalog.pid FROM Suppliers, Catalog WHERE Suppliers.sname <> 'sachin' AND Suppliers.sid = Catalog.sid -- returns every pid that has at least one catalog row whose supplier is NOT named 'sachin', i.e. parts with at least one non-sachin supplier.

  3. MINUS removes every pid found in the second subquery from the first subquery's result, so any part with even one non-sachin supplier is dropped.

  4. What survives is the set of pids that (a) are supplied by someone, because they came from the first subquery, and (b) have no non-sachin supplier at all -- the only way both hold is when 'sachin' is the sole supplier of that part.

Quick check with sample data: suppose sachin supplies parts 10 and 20, and another supplier, ravi, supplies parts 20 and 30. The first subquery returns {10, 20, 30}; the second (non-sachin suppliers) returns {20, 30}, since ravi supplies both. MINUS gives {10} -- part 20 is excluded because ravi also supplies it, leaving only the part that sachin alone supplies.

Explore the full course: Isro