Consider the following database table named water_schemes : The number of…
2016
Consider the following database table named water_schemes :
The number of tuples returned by the following SQL query is _____________ .
with total (name, capacity) as
select district_name, sum (capacity)
from water_schemes
group by district_name
with total_avg (capacity) as
select avg (capacity)
from total
select name
from total, total_avg
where total.capacity ≥ total_avg.capacity
Attempted by 78 students.
Show answer & explanation
Correct answer: 2
Key insight: compute the total capacity per district, find the average of those totals, then count districts whose total is greater than or equal to that average.
Compute totals by district:
Ajmer: 20
Bikaner: 10 + 10 + 20 = 40
Churu: 10 + 20 = 30
Dungargarh: 10
Compute the average of these totals: (20 + 40 + 30 + 10) / 4 = 100 / 4 = 25
Select districts whose total is greater than or equal to the average (>= 25): Bikaner (40) and Churu (30)
Final answer: the query returns 2 tuples (Bikaner and Churu).
A video solution is available for this question — log in and enroll to watch it.