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

My Personal Notes arrow_drop_up

Save