Consider the following relation Cinema(theater, address, capacity) Which of…
2015
Consider the following relation
Cinema(theater, address, 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?
- A.
WHERE P1.capacity >= All (select P2.capacity from Cinema P2)
- B.
WHERE P1.capacity >= Any (select P2.capacity from Cinema P2)
- C.
WHERE P1.capacity > All (select max(P2.capacity) from Cinema P2)
- D.
WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2)
Attempted by 202 students.
Show answer & explanation
Correct answer: A
Key idea: use a condition that ensures the selected capacity is as large as every capacity in the table (so ties for maximum are kept).
Why the chosen condition works:
The clause WHERE P1. capacity >= All (select P2. capacity from Cinema P2) is true only when P1.capacity is greater than or equal to every capacity in Cinema, so it selects rows with the maximum capacity (including ties).
Other variants fail: using ANY returns rows that beat at least one capacity (not necessarily the maximum), and comparing with max(...) using > will exclude ties or return nothing.
A simpler equivalent is to compare directly to the maximum value:
SELECT P1.address FROM Cinema P1 WHERE P1.capacity = (SELECT MAX(P2.capacity) FROM Cinema P2)
A video solution is available for this question — log in and enroll to watch it.