Consider the following database table named Consider the following SQL query:…
2017
Consider the following database table named
Consider the following SQL query:
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')
The number of tuples returned by the above SQL query is ______
Attempted by 83 students.
Show answer & explanation
Correct answer: 7
Step 1: Evaluate the subquery for Spain.
The subquery that selects goals for country = 'Spain' returns an empty set because there are no players from Spain in the table.
Important note: a comparison using ALL against an empty set is vacuously true, so ta.goals > ALL (empty set) is true for every row.
Step 2: Evaluate the subquery for Germany.
The Germany goals returned are: 16, 14, 11, 10.
The condition ta.goals > ANY (these Germany goals) means ta.goals must be greater than at least one of those values. That is true exactly when ta.goals > 10, so ta.goals must be at least 11.
Step 3: Combine both conditions.
The ALL condition is true for every row (from Step 1).
So the rows that satisfy the WHERE clause are those with goals >= 11.
Players with goals >= 11 are: Klose (16), Ronald (15), G Muller (14), Fontaine (13), Pele (12), Klinsmann (11), and Kocsis (11).
Final answer: The query returns 7 tuples.
A video solution is available for this question — log in and enroll to watch it.