The GATE Grind

GATE 2016 CS (CS2) – Question 62

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Numerical answer

Table water_schemes has (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). How many tuples does the query return?

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

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: 2.0 to 2.0

Explanation

Interpret the printed CTE pseudocode as its intended aggregate query. District totals are Ajmer 20, Bikaner 40, Churu 30, Dungargarh 10. Their average is 100/4=25. Only Bikaner and Churu meet the threshold, giving 2 tuples. Standard SQL would use one WITH clause, comma-separated parenthesized CTE definitions.

More Relational Model: Relational Algebra, Tuple Calculus, SQL questions

All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →