Question

Consider the following database table named water_schemes, with columns scheme_no, district_name, capacity:
(1,Ajmer,20), (1,Bikaner,10), (2,Bikaner,10), (3,Bikaner,20), (1,Churu,10), (2,Churu,20), (1,Dungargarh,10).

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

Accepted Answer

The correct answer is 2. Grouping by district and summing capacity: Ajmer=20, Bikaner=10+10+20=40, Churu=10+20=30, Dungargarh=10. The average of these four totals is (20+40+30+10)/4 = 100/4 = 25. Districts whose total capacity is ≥ 25 are Bikaner (40) and Churu (30) — 2 tuples.

Explanation