GATE CS 2017 Set 2 — Question 46
Go beyond PYQs with Success TrackerAI-powered personalised practice and doubt support. Unlimited practice on eligible plans; AI usage limits apply.NAT+2 / -0MediumSubqueriesSQLDatabases
Databases → SQL → Subqueries
Last updated
Question
Consider the following database table named top_scorer.
Consider the following SQL query:The number of tuples returned by the above SQL query is __________.
top_scorer
| player | country | goals |
|---|---|---|
| Klose | Germany | 16 |
| Ronaldo | Brazil | 15 |
| G Müller | Germany | 14 |
| Fontaine | France | 13 |
| Pelé | Brazil | 12 |
| Klinsmann | Germany | 11 |
| Kocsis | Hungary | 11 |
| Batistuta | Argentina | 10 |
| Cubillas | Peru | 10 |
| Lato | Poland | 10 |
| Lineker | England | 10 |
| T Müller | Germany | 10 |
| Rahn | Germany | 10 |
SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals >ALL (SELECT tb.goals
FROM top_scorer AS tb
WHERE tb.country = 'Spain')
AND ta.goals >ANY (SELECT tc.goals
FROM top_scorer AS tc
WHERE tc.country = 'Germany')
Correct answer
7 to 7
Solution
1.The first subquery
SELECT tb.goals FROM top_scorer AS tb WHERE tb.country = 'Spain' returns an empty set because there are no players from Spain in the table.2.The condition
ta.goals > ALL (empty set) is vacuously true for all rows in the table.3.The second subquery
SELECT tc.goals FROM top_scorer AS tc WHERE tc.country = 'Germany' returns the set of goals for German players: .4.The condition
ta.goals > ANY {16, 14, 11, 10} is equivalent to ta.goals > min(16, 14, 11, 10), which simplifies to ta.goals > 10.5.Combining the conditions, we need to count rows where
TRUE AND ta.goals > 10.6.The players with more than 10 goals are:
- Klose (16)
- Ronaldo (15)
- G Müller (14)
- Fontaine (13)
- Pelé (12)
- Klinsmann (11)
- Kocsis (11)
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…