Consider the following database table named (formula) . — GATE CS 2017 Set 2 Q46

Consider the following database table named $\text{top\_scorer}$ .

$\overset{\text{top\_scorer}}{\begin{array}{|c|c|c|}\hline\\ \textbf{player}& \textbf{country}& \textbf{goals} \\ \hline \text{Klose}& \text{Germany}& 16\\ \hline \text{Ronaldo}& \text{Brazil}& 15 \\ \hline \text{G Muller}& \text{Germany}& 14 \\ \hline \text{Fontaine}& \text{France}& 13 \\ \hline \text{Pele}& \text{Brazil}& 12 \\ \hline \text{Klinsmann}& \text{Germany}& 11 \\ \hline \text{Kocsis}& \text{Hungary}& 11 \\ \hline \text{Batistuta}& \text{Argentina}&10 \\ \hline \text{Cubillas}& \text{Peru}&10 \\ \hline \text{Lato}& \text{Poland}& 10 \\ \hline \text{Lineker}& \text{England}& 10 \\ \hline \text{T Muller}& \text{Germany}& 10 \\ \hline \text{Rahn}& \text{Germany}& 10 \\ \hline \end{array}}$

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 ______

View worked solution

Answer: 7

To find the number of tuples returned by the SQL query, let us analyze the two conditions in the WHERE clause separately.

Condition 1: `ta.goals > ALL (...)`

The first subquery is:

SELECT tb.goals FROM top_scorer AS tb WHERE tb.country = 'Spain'

Looking at the top_scorer table, there are no players from Spain.
Therefore, this subquery returns an empty set.
In SQL, any comparison of the form value > ALL (empty_set) always evaluates to TRUE.

Thus, Condition 1 is satisfied for all tuples in the table.

Condition 2: `ta.goals > ANY (...)`

The second subquery is:

SELECT tc.goals FROM top_scorer AS tc WHERE tc.country = 'Germany'

The players from Germany and their goals are:
- Klose: 16
- G Muller: 14
- Klinsmann: 11
- T Muller: 10
- Rahn: 10
So, the subquery returns the set of values: {16, 14, 11, 10}.
The operator > ANY means the value must be strictly greater than at least one value in the returned set. Mathematically, this is equivalent to: $\text{ta.goals} > \min(\{16, 14, 11, 10\})$ $\text{ta.goals} > 10$

Combining the Conditions

Since the query uses an AND operator, both conditions must be true. This simplifies to finding all players with goals > 10:

Player	Goals	Goals > 10?
Klose	16	Yes (✔)
Ronaldo	15	Yes (✔)
G Muller	14	Yes (✔)
Fontaine	13	Yes (✔)
Pele	12	Yes (✔)
Klinsmann	11	Yes (✔)
Kocsis	11	Yes (✔)
Batistuta	10	No
Cubillas	10	No
Lato	10	No
Lineker	10	No
T Muller	10	No
Rahn	10	No

The players who satisfy the condition are: Klose, Ronaldo, G Muller, Fontaine, Pele, Klinsmann, and Kocsis.

The total number of tuples returned is 7.

Free practice · Your answers on this page are not saved.

GATE CS 2017 Set 2 Q46 — Tuple Calculus and SQL

Condition 1: ta.goals > ALL (...)

Condition 2: ta.goals > ANY (...)

Combining the Conditions

Related questions

Condition 1: `ta.goals > ALL (...)`

Condition 2: `ta.goals > ANY (...)`