GATE CS 2015 Set 3 Q3 — Tuple Calculus and SQL

To find the addresses of theaters with the maximum capacity, we need to select those tuples where the capacity is greater than or equal to every capacity present in the Cinema relation.

Let us analyze each option:

• Option A:

  WHERE P1.capacity >= ALL (SELECT P2.capacity FROM Cinema P2)

  The subquery (SELECT P2.capacity FROM Cinema P2) returns the set of all capacities in the relation. The condition P1.capacity >= ALL (...) evaluates to true only if P1.capacity is greater than or equal to every element in this set. This is the definition of the maximum value. Thus, this option correctly finds the theaters with the maximum capacity.
• Option B:

  WHERE P1.capacity >= ANY (SELECT P2.capacity FROM Cinema P2)

  The condition >= ANY evaluates to true if P1.capacity is greater than or equal to at least one capacity in the database. This will select almost all theaters (except those with capacity strictly less than the minimum, which is impossible since every capacity is $\ge$ itself).
• Option C:

  WHERE P1.capacity > ALL (SELECT MAX(P2.capacity) FROM Cinema P2)

  The subquery returns a single value: the maximum capacity $M$. The condition simplifies to P1.capacity > M. Since no capacity can be strictly greater than the maximum capacity $M$, this query will always return an empty result.
• Option D:

  WHERE P1.capacity > ANY (SELECT MAX(P2.capacity) FROM Cinema P2)

  Similar to Option C, this simplifies to P1.capacity > M, which also yields an empty result.

Therefore, Option A is the correct choice.