Skip to content
Related Articles

Related Articles

Improve Article
GATE | GATE CS 2020 | Question 23
  • Difficulty Level : Basic
  • Last Updated : 26 May, 2021

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:

S2M/s Balaji Furniture
S3M/s Premium Furniture
S3M/s Premium Furniture
S3M/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

Attention reader! Don’t stop learning now. Learn all GATE CS concepts with Free Live Classes on our youtube channel.

My Personal Notes arrow_drop_up
Recommended Articles
Page :