The GATE Grind

GATE 2017 CS (CS2) – Question 56

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Numerical answer

top_scorer(player,country,goals) contains 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. Find the tuple count returned.

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')

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: 7.0 to 7.0

Explanation

There are no Spain rows, so >ALL over that empty subquery is true for every row. >ANY over Germany’s goal counts means exceed at least one, equivalently exceed their minimum, 10. Seven players have more than ten goals: Klose, Ronaldo, G Müller, Fontaine, Pelé, Klinsmann, Kocsis. Thus 7 tuples.

More Relational Model: Relational Algebra, Tuple Calculus, SQL questions

All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →