GATE | GATE CS 2020 | Question 23
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
Answer: (A)
Explanation: The resultant table after the execution of the above query will be:
s.sno |
s.sname |
S2 |
M/s Balaji Furniture |
S3 |
M/s Premium Furniture |
S3 |
M/s Premium Furniture |
S3 |
M/s Premium Furniture |
Result of the inner query will be 225(avg(200,250)) and subsequently every such tuple which has s.sno=c.sno and cost>225 will get selected from the Cartesian product of supplier and catalogue table.
Option (A) is correct.
Quiz of this Question
Last Updated :
26 May, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...