Database-Management-System

Question 8

Consider a relational database containing the following schemas.

The primary key of each table is indicated by underlying 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	0
B	5
C	4
D	2

Database-Management-SystemSQLGATE 2020Video-Explanation

Question 8 Explanation:

The inner query “select avg(cost) from catalogue where pno=’P4′ group by pno;” returns:
AVG(COST)
————
225
The outer query “select s.sno, s.sname from suppliers s, catalogue c where s.sno=c.sno” returns:

SNO SNAME
—————————————-
S1 M/s Royal furniture
S1 M/s Royal furniture
S1 M/s Royal furniture
S2 M/s Balaji furniture
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture

S3 M/s Premium furniture
S3 M/s Premium furniture

So, the final result of the query is:

SN SNAME
—————————————-
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture

Therefore, 4 rows will be returned by the query.

Correct Answer: C