GATE CSE 2016 Set 2 | Question: 52

Question

Consider the following database table named water_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

Answer 1

1st  query will return the following:

Table Name : Total (name, capacity)

$${\begin{array}{|c|c|}\hline
 \textbf{name}&  \textbf{capacity}\\\hline
    \text{Ajmer}&    20  \\ \hline   
   \text{Bikaner}& 40 \\     \hline
   \text{Churu}& 30     \\\hline
   \text{Dungargarh}&10     \\\hline
\end{array}}$$

2nd Query will return, Total_avg (capacity) ${\begin{array}{|c|}\hline
25  \\\hline
\end{array}}$

Since sum of capacity $= 100/4=25$

3rd query will be final and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to $25$

$${\begin{array}{|c|}\hline
  \textbf{name}\\\hline 
   \text{Bikaner} \\     \hline
   \text{Churu}   \\\hline
\end{array}}$$

Hence, 2 tuples returned.

Comments

shashank bhai aapko 21 topo ki salaami

---

@amitqy aggregate function sum, calculates the sum of values present in specified attribute. It will ignore NULL values and gives NULL when no value is present in the attribute 

---

@amitqy

First Group By used then it do sum of of group by elements.