GATE CS 2015 Set 3 Q.13 (CS Q.3): SQL Queries and Integrity Constraints

Consider the following relation

\text{Cinema}(\text{theater}, \text{address}, \text{capacity})

Which of the following options will be needed at the end of the SQL query

SELECT P1.address
FROM Cinema P1

such that it always finds the addresses of theaters with maximum capacity?

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

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

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

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

Official answer and solution

Official answer: A
From: Official GATE 2015 answer key

Solution

Evaluate SQL quantified comparison predicates to select records achieving the maximum value of an attribute.

A theater $P1$ has maximum capacity in Cinema if and only if its capacity is greater than or equal to the capacity of every theater $P2$ in Cinema:

\forall P2 \in \text{Cinema}, \quad P1.\text{capacity} \ge P2.\text{capacity}

Evaluate the candidate SQL clauses:

Option A: WHERE P1.capacity >= ALL (SELECT P2.capacity FROM Cinema P2)
The >= ALL predicate evaluates to true if and only if P1.capacity is greater than or equal to every value returned by the subquery. This precisely characterizes the maximum capacity (including all theaters tied for the maximum). Valid.
Option B: WHERE P1.capacity >= ANY (...) selects any theater whose capacity is greater than or equal to at least one theater, which includes all theaters except those strictly below the minimum.
Option C: WHERE P1.capacity > ALL (SELECT MAX(P2.capacity) FROM Cinema P2) requires capacity strictly greater than the maximum, returning an empty set.
Option D: WHERE P1.capacity > ANY (SELECT MAX(P2.capacity) FROM Cinema P2) also requires capacity strictly greater than the maximum, returning empty.

Therefore, Option A correctly finds the addresses of theaters with maximum capacity.

Final answer

Common trap

Using > ALL instead of >= ALL, which would require an element strictly greater than itself and produce an empty result.

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

Source

Paper: GATE 2015 Computer Science and Information Technology (CS) Session 3
Question: 13 of 65, printed as CS Q.3
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