GATE CSE 2020 Q23: Rows returned by a join with a nested AVG subquery

Databases
1 mark
MCQ
−1/3 if wrong

Consider a relational database containing the following schemas.

Catalogue (primary key: sno, pno)
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 (primary key: sno)
sno	sname	location
S1	M/s Royal furniture	Delhi
S2	M/s Balaji furniture	Bangalore
S3	M/s Premium furniture	Chennai

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

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 primary key of each table is indicated by underlining the constituent fields (shown here in each caption).

The number of rows returned by the above SQL query is

Try it: multiple choice: exactly one correct option

Wrong answer costs 1/3 mark in GATE.

Show answer and explanation

Answer: A (official GATE 2020 final answer key)

Concept used: Evaluate the scalar subquery first, then count joined rows that pass the filter (duplicates are kept without DISTINCT).

First step: The subquery averages the P4 costs: $(200 + 250)/2 = 225$ . Count Catalogue rows with cost above 225.

Full step-by-step solution

Detailed working, common mistakes and linked mock tests will be available with a free GATEiit account. Log-in is coming soon.

Share this question

WhatsAppDownload image

Source: official GATE 2020 CS question paper and final answer key, organised by IIT Delhi. Question paper · Answer key

PracticePractise Databases questions →Full paperAll GATE CSE 2020 questions →

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

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

GATE CSE 2020 Q23: Rows returned by a join with a nested AVG subquery

Question

Full step-by-step solution

More Databases PYQs

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