The GATE Grind

GATE 2020 CS – Question 23

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 1 mark · Multiple choice

Consider a relational database containing the following schemas.

Catalogue(sno, pno, cost): (S1,P1,150), (S1,P2,50), (S1,P3,100), (S2,P4,200), (S2,P5,250), (S3,P1,250), (S3,P2,150), (S3,P5,300), (S3,P4,250)

Suppliers(sno, sname, location): (S1, M/s Royal furniture, Delhi), (S2, M/s Balaji furniture, Bangalore), (S3, M/s Premium furniture, Chennai)

Parts(pno, pname, part_spec): (P1, Table, Wood), (P2, Chair, Wood), (P3, Table, Steel), (P4, Almirah, Steel), (P5, Almirah, Wood)

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

  1. 4
  2. 5
  3. 0
  4. 2

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (A) 4

Explanation

The average cost of P4 is (200+250)/2 = 225. Catalogue rows with cost > 225 are (S2,P5,250), (S3,P1,250), (S3,P5,300) and (S3,P4,250). The join returns one row per qualifying row, so 4 rows.