GATE CS 2016 Set 2 — Question 62
Go beyond PYQs with Success TrackerAI-powered personalised practice and doubt support. Unlimited practice on eligible plans; AI usage limits apply.NAT+2 / -0MediumAggregate FunctionsSQLDatabasesGrouping & OrderingSubqueries
Databases → SQL → Aggregate Functions
Last updated
Question
Consider the following database table named water_schemes :
The number of tuples returned by the following SQL query is _______.
| 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 |
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
Correct answer
2 to 2
Solution
1.First, we compute the
total CTE which groups by district_name and sums the capacity:- Ajmer:
- Bikaner:
- Churu:
- Dungargarh:
total_avg CTE which calculates the average capacity from the total results:- Average =
total where the capacity is greater than or equal to the average ():- Bikaner (): Included
- Churu (): Included
- Ajmer (): Excluded
- Dungargarh (): Excluded
Continue learning with Success Tracker
A step still unclear? Work through it with support
Use Success Tracker to ask about the reasoning, then try another GATE CS question to check your understanding.
AI-powered practice· Unlimited practice on eligible plans
- PYQs with solutions
- Attempt available previous-year questions, then compare your reasoning with the worked solution. Coverage varies by stream.
- Practice that adapts
- Choose a topic, work on weaker areas and bookmark questions to revisit. Your attempts feed your progress tracking.
- AI doubt support
- Ask follow-up questions about a step or concept while practising, instead of stopping at the final answer.
Unlimited practice is available on eligible plans. Free practice and AI usage have limits; check the current plan allowances before choosing.
This page stays readable without an account. AI responses can be wrong; check them against the solution and source material.
More questions on SQL
2026 Set 2 Q15In the context of DBMS, consider the two sets T and S given below. | T | S | |---|---| | I:…2026 Set 2 Q20Consider concurrent execution of two transactions and in a DBMS, both of which access a…2026 Set 1 Q30Let and be the attributes of a relation in a relational schema. Let…2026 Set 1 Q31In the context of relational database normalization, which of the following statements is/are true?2026 Set 2 Q42In the context of schema normalization in relational DBMS, consider a set F of functional…