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

Solution:
SELECT AVG (cost)
       FROM Catalogue
       WHERE pno = 'P4'
       GROUP BY pno;

Step 1: Enteries from Catalogue table that have pno = 'P4'

                      

Step 2: Avg. of cost from the above result.
             average = 200+2502=4502=225

Step3:

SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno=c.sno AND cost > 225

Output for the above query is

         

number of rows returned = 4