Consider a relational database containing the following schemas. The primary…
2020
Consider a relational database containing the following schemas.
The primary key of each table is indicated by underlining the constituent fields.
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
- A.
4
- B.
5
- C.
0
- D.
2
Attempted by 134 students.
Show answer & explanation
Correct answer: A
Step 1 — compute the subquery (average cost for P4):
The Catalogue rows for part P4 are costs 200 and 250, so AVG(cost) = (200 + 250) / 2 = 225.
Step 2 — find catalogue rows with cost > 225:
S2 supplies P5 at cost 250
S3 supplies P1 at cost 250
S3 supplies P5 at cost 300
S3 supplies P4 at cost 250
Step 3 — apply the join and projection in the outer query:
The FROM ... WHERE join pairs each matching catalogue row with its supplier and the SELECT returns s.sno and s.sname for each matching row. The query does not use DISTINCT, so multiple matching catalogue rows for the same supplier produce multiple result rows.
There are four catalogue rows with cost > 225, so the query returns 4 rows.
Answer: 4