GATE CS 2016 Set 2 Q.62 (CS Q.52): SQL Queries and Integrity Constraints

Consider the following database table named water_schemes:

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

Your answer

Official answer and solution

Official answer: 2
From: Official GATE 2016 answer key

Solution

Evaluate each CTE in the SQL query: find district capacity totals, calculate their average, and count rows exceeding the average.

Step 1: Evaluate the first CTE total(name, capacity):
Groups water_schemes by district_name and computes sum(capacity):

Ajmer: $20$
Bikaner: $10 + 10 + 20 = 40$
Churu: $10 + 20 = 30$
Dungargarh: $10$
The resulting table has 4 rows: (Ajmer, 20), (Bikaner, 40), (Churu, 30), (Dungargarh, 10).

Step 2: Evaluate the second CTE total_avg(capacity):
Computes the average capacity across the rows of total:

\text{Average} = \frac{20 + 40 + 30 + 10}{4} = \frac{100}{4} = 25

Step 3: Evaluate the final query:
select name from total, total_avg where total.capacity >= total_avg.capacity
Compare each district total against 25:

Ajmer: $20 \ge 25$ (False)
Bikaner: $40 \ge 25$ (True)
Churu: $30 \ge 25$ (True)
Dungargarh: $10 \ge 25$ (False)
The query returns 2 tuples: Bikaner and Churu.

Therefore, the number of tuples returned is 2.

Final answer

Common trap

Computing the average over the 7 raw table rows ( $100 / 7 \approx 14.28$ ) instead of the 4 district aggregates.

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

Source

Paper: GATE 2016 Computer Science and Information Technology (CS) Session 2
Question: 62 of 65, printed as CS Q.52
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.

Question

Solution

Source