Skip to content
Related Articles
Open in App
Not now

Related Articles

GATE | GATE CS 2020 | Question 23

Improve Article
Save Article
  • Difficulty Level : Basic
  • Last Updated : 26 May, 2021
Improve Article
Save Article

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
Related Articles

Start Your Coding Journey Now!