GATE CS 2017 Set 2 Q.46 (CS Q.46): SQL Queries and Integrity Constraints

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

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

Your answer

Official answer and solution

Official answer: 7
From: Official GATE 2017 answer key

Solution

Evaluate the SQL subqueries separately, then apply the >ALL and >ANY comparison predicates to the rows of table top_scorer.

Examine the two subqueries in the WHERE clause:

First subquery: SELECT tb.goals FROM top_scorer AS tb WHERE tb.country = 'Spain'.
Looking at the table top_scorer, there are no records with country = 'Spain'. Thus, this subquery returns the empty set $\emptyset$ .
In standard SQL, x >ALL (empty set) evaluates to TRUE for any value of $x$ .
Second subquery: SELECT tc.goals FROM top_scorer AS tc WHERE tc.country = 'Germany'.
The goals scored by players from Germany are $\{16, 14, 11, 10, 10\}$ .
The condition ta.goals >ANY (...) is satisfied if ta.goals > min(16, 14, 11, 10, 10) = 10.

Combining the conditions: a row qualifies if ta.goals > 10.
Check all players in top_scorer with goals > 10:

Klose (Germany): 16 goals ( $16 > 10$ )
Ronaldo (Brazil): 15 goals ( $15 > 10$ )
G Müller (Germany): 14 goals ( $14 > 10$ )
Fontaine (France): 13 goals ( $13 > 10$ )
Pelé (Brazil): 12 goals ( $12 > 10$ )
Klinsmann (Germany): 11 goals ( $11 > 10$ )
Kocsis (Hungary): 11 goals ( $11 > 10$ )

The remaining players (Batistuta, Cubillas, Lato, Lineker, T Müller, Rahn) each have 10 goals, which is not strictly greater than 10.

Therefore, exactly 7 tuples are returned by the query.

Final answer

Common trap

Assuming >ALL (empty set) evaluates to FALSE; in standard SQL, universal quantification over an empty set is vacuously TRUE.

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2017 Computer Science and Information Technology (CS) Session 2
Question: 46 of 65, printed as CS Q.46
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source