Multiple choice

Consider the relation schema: Parts (Pno, pname) Supplier (Sid, Sname) SP (sid, pno, qty) The SQL query which display all the parts having more than one supplier is

  1. select distinct p.pname from parts P, SP a, SP b where a.Pno = b.Pno and a.Sid = b.sid and a.Pno = P.pno

  2. select distinct P.name from parts P, SP a, SP b where a.pno = b.pno and a.sid < > b.sid and P.Pno = a.Pno

  3. cannot be determined

  4. none of these
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To find parts with more than one supplier, we need to identify parts supplied by at least two DIFFERENT suppliers. Option B correctly self-joins the SP table twice with alias a and b, requiring the same part number (a.pno = b.pno) but DIFFERENT supplier IDs (a.sid <> b.sid), then joins with Parts to get the name. Option A is incorrect because it requires a.Sid = b.sid, which would find the same supplier twice.

Keep practicing — related questions