Question

Consider a relational database with schemas: Catalogue(sno, pno, cost), Suppliers(sno, sname, location), Parts(pno, pname, part_spec). Catalogue data: (S1,P1,150), (S1,P2,50), (S1,P3,100), (S2,P4,200), (S2,P5,250), (S3,P1,250), (S3,P2,150), (S3,P5,300), (S3,P4,250).

SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND
  cost > (SELECT AVG(cost)
          FROM Catalogue
          WHERE pno = 'P4'
          GROUP BY pno);

The number of rows returned by the above SQL query is

Accepted Answer

The correct answer is (A) 4. The subquery averages cost over rows where pno='P4': (200+250)/2 = 225. The outer query then joins Suppliers and Catalogue on sno, keeping rows with cost > 225. Scanning the Catalogue rows: S2,P5,250 (>225 ✓), S3,P1,250 (✓), S3,P5,300 (✓), S3,P4,250 (✓) — 4 rows satisfy cost>225, each joining to exactly one Suppliers row (sno is Suppliers' primary key), and since the query has no DISTINCT, all 4 rows are returned as-is (S3 appears three times, S2 once).

Explanation