GATE CS Mastery
Cheat sheets

GATE CS 2020 · Databases

Question 23

MCQPrinted as CS Q.131 markWrong answer: −1/3Medium (estimate)

Question

Consider a relational database containing the following schemas.

Catalogue
snopnocost
S1P1150
S1P250
S1P3100
S2P4200
S2P5250
S3P1250
S3P2150
S3P5300
S3P4250
Suppliers
snosnamelocation
S1M/s Royal furnitureDelhi
S2M/s Balaji furnitureBangalore
S3M/s Premium furnitureChennai
Parts
pnopnamepart_spec
P1TableWood
P2ChairWood
P3TableSteel
P4AlmirahSteel
P5AlmirahWood

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

Choose one option
Official answer and solution
Official answer
A
From
Official GATE 2020 answer key

Solution

Evaluate the nested SQL subquery to find the threshold cost, then count matching rows from the join.

Step 1: Evaluate the inner subquery:

SELECT AVG(cost)
FROM Catalogue
WHERE pno = 'P4'
GROUP BY pno
  • In Catalogue, the rows where pno = 'P4' are:
    • (S2, P4, 200)
    • (S3, P4, 250)
  • The average cost for P4 is:
    AVG(cost)=200+2502=225\text{AVG}(cost) = \frac{200 + 250}{2} = 225

Step 2: Filter rows in Catalogue where cost > 225:

  • Checking all 9 rows of Catalogue:
    • (S1, P1, 150): 150 is not > 225
    • (S1, P2, 50): 50 is not > 225
    • (S1, P3, 100): 100 is not > 225
    • (S2, P4, 200): 200 is not > 225
    • (S2, P5, 250): 250 > 225 (Match 1)
    • (S3, P1, 250): 250 > 225 (Match 2)
    • (S3, P2, 150): 150 is not > 225
    • (S3, P5, 300): 300 > 225 (Match 3)
    • (S3, P4, 250): 250 > 225 (Match 4)

Step 3: Join with Suppliers on s.sno = c.sno:

  • Each of the 4 matching rows from Catalogue has a valid sno ('S2' or 'S3') present in Suppliers:
    1. (S2, M/s Balaji furniture) from Catalogue (S2, P5, 250)
    2. (S3, M/s Premium furniture) from Catalogue (S3, P1, 250)
    3. (S3, M/s Premium furniture) from Catalogue (S3, P5, 300)
    4. (S3, M/s Premium furniture) from Catalogue (S3, P4, 250)
  • Since the query does not specify DISTINCT, all 4 rows are returned in the result set.
Final answer

A

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper
GATE 2020 Computer Science and Information Technology (CS)
Question
23 of 65, printed as CS Q.13
Section
Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.