Skip to content
GATETECH
Official PYQ · 2016Set 2 · Q52Tuple Calculus and SQLNAT1 mark

GATE CS 2016 Set 2 Q52 — Tuple Calculus and SQL

Explain with AI

Consider the following database table named water_schemes:
scheme_nodistrict_namecapacity1Ajmer201Bikaner102Bikaner103Bikaner201Churu102Churu201Dungargarh10Water_schemes\overset{\text{Water\_schemes}}{\begin{array}{|c|c|c|}\hline \textbf{scheme\_no}& \textbf{district\_name}& \textbf{capacity} \\ \hline 1& \text{Ajmer}& 20 \\ \hline 1& \text{Bikaner}& 10 \\ \hline 2& \text{Bikaner}& 10 \\ \hline 3& \text{Bikaner}&20 \\ \hline 1& \text{Churu}& 10 \\ \hline 2& \text{Churu}& 20 \\ \hline 1& \text{Dungargarh}& 10 \\ \hline \end{array}}

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
View worked solution

Answer: 2

To find the number of tuples returned by the given SQL query, we can execute it step-by-step:

Step 1: Evaluate the first CTE (total)

The first CTE groups the water_schemes table by district_name and calculates the sum of capacity for each district:

total(name, capacity)=namecapacityAjmer20Bikaner10+10+20=40Churu10+20=30Dungargarh10\text{total(name, capacity)} = \begin{array}{|c|c|} \hline \textbf{name} & \textbf{capacity} \\ \hline \text{Ajmer} & 20 \\ \hline \text{Bikaner} & 10 + 10 + 20 = 40 \\ \hline \text{Churu} & 10 + 20 = 30 \\ \hline \text{Dungargarh} & 10 \\ \hline \end{array}

Step 2: Evaluate the second CTE (total_avg)

The second CTE calculates the average of the capacities obtained in the total relation:

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

So, total_avg contains a single value: 25.

Step 3: Evaluate the main query

The main query performs a cross join between total and total_avg, filtering for rows where total.capacity ≥ total_avg.capacity (i.e., ≥25\ge 25):

  • Ajmer: 20≥2520 \ge 25 ✗ (False)
  • Bikaner: 40≥2540 \ge 25 ✓ (True)
  • Churu: 30≥2530 \ge 25 ✓ (True)
  • Dungargarh: 10≥2510 \ge 25 ✗ (False)

The query returns the names of the districts that satisfy this condition: Bikaner and Churu.

Thus, the number of tuples returned is 2.

Free practice · Your answers on this page are not saved.

Published
Report a correction
Keep exploringMore Tuple Calculus and SQL questions