Question 5 of 8: SQL queries — Suppliers/Parts/Catalog

Question 5: SQL queries — Suppliers/Parts/Catalog (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Suppliers(sid, sname, address), Parts(pid, pname, color), Catalog(sid, pid, cost) with Catalog.sid/pid foreign keys into Suppliers/Parts.

(a) pnames of parts for which there is some supplier — a straightforward join/semijoin: any part appearing in Catalog has a supplier.

SELECT DISTINCT P.pname
FROM   Parts P
WHERE  EXISTS (SELECT * FROM Catalog C WHERE C.pid = P.pid);

(b) suppliers who supply every red part — a universal ("for all") quantification, expressed by double negation: no red part exists that this supplier does NOT supply.

SELECT S.sname
FROM   Suppliers S
WHERE  NOT EXISTS (
         SELECT * FROM Parts P
         WHERE  P.color = 'red'
         AND    NOT EXISTS (
                  SELECT * FROM Catalog C
                  WHERE  C.sid = S.sid AND C.pid = P.pid));

(c) sids who charge more for some part than that part's average cost — a correlated subquery re-evaluated per Catalog row, averaging over only the suppliers who actually supply that same part.

SELECT DISTINCT C1.sid
FROM   Catalog C1
WHERE  C1.cost > (
         SELECT AVG(C2.cost) FROM Catalog C2
         WHERE  C2.pid = C1.pid);

(d) for every supplier supplying both a green and a red part, the name and price of their most expensive part — filter suppliers by the two EXISTS conditions, then a per-supplier correlated MAX.

SELECT S.sname,
       (SELECT MAX(C3.cost) FROM Catalog C3 WHERE C3.sid = S.sid) AS maxprice
FROM   Suppliers S
WHERE  EXISTS (SELECT * FROM Catalog C1, Parts P1
               WHERE C1.sid = S.sid AND C1.pid = P1.pid AND P1.color = 'green')
AND    EXISTS (SELECT * FROM Catalog C2, Parts P2
               WHERE C2.sid = S.sid AND C2.pid = P2.pid AND P2.color = 'red');