edited by
11,006 views
56 votes
56 votes

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
edited by

1 Answer

Best answer
93 votes
93 votes

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.

edited by
Answer:

Related questions

64 votes
64 votes
6 answers
1
Akash Kanase asked Feb 12, 2016
21,837 views
Consider the following database schedule with two transactions $T_{1}$ and $T_{2}$.$S= r_{2}\left(X\right); r_{1}\left(X\right); r_{2} \left(Y\right); w_{1} \left(X\right...