GATE CSE 2015 Set 3 | Question: 3

Question

Consider the following relation

Cinema($\textit{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)

Comments

I was also confused in C,D now it is clear why they are not the right choice

---

(b) → ‘Any’ means that if there is atleast one theater whose capacity is less than that of P1.capacity, then P1.capacity will return. Possibility of other theaters having capacity > P1.capacity still exists. To cover this, ‘All’ is needed. Hence (a)

---

P1 and P2 are just two instances of same table, i.e both P1 and P2 will have same entries.

Answer 1

A is the answer

B - Returns the addresses of all theaters.
C - Returns null set. max() returns a single value and there won't be any value > max.
D - Returns null set. Same reason as C. All and ANY works the same here as max returns a single value.

Comments

@krati liotriya

In option A condition  is.     >= all

And  with >all  output is null but  with = all  output should be all  selected capacities by inner query . So  every capacity wheather it is maximum or not,  is going to be selected.

you are getting it wrong. in option a) ( select p2. capacity from cinema P2) this inner query will give the table of all theaters with their capacity .now when it applies on outer query >=all outer query will select only those theaters from P1 (outer table) whose capacity is greater than all of the theaters​​​​​​​ of P2 table.

 

---

This simple example absolutely cleared ma doubt!!!;)

---

WHERE P1.capacity > All (select P2.capacity from Cinema P2)

 Here '>' use so, null set return..

WHERE P1.capacity >= All (select P2.capacity from Cinema P2)

Here, '>=' use means it will return the "max" because all capacity less than the max capacity in the table. only the max value from the instance P2 is >= max value in the instance p1.

Answer 2

Answer is A.

Inner query collects capacities of all the theatres and in outer query we are filtering the tuples with the condition  " p1.capacity>=All". So the theatre which is having maximum capacity will be satisfy the condition.

Answer 3

(A) : Inner query collects capacities of all the theatres and in outer query we are filtering the tuples 
with the condition “capacity>=All”. So the theatres which are having maximum capacity will 
be satisfy the conductivity and they will.

(B) : Returns all the rows as ANY is used

(C)  & (D): Returns no rows as nothing can be greater than all or any of the maximum values

Comments

thanks heena singh Junior

---

Anyone please explain how to work all , any function with example

---

What if in option (C) it is >= ALL ?