GATE CSE 2012 | Question: 51

Question

Consider the following relations $A, B$ and $C:$
 

$$\overset{\textbf{A}}{\begin{array}{|c|c|c|}\hline\\
\textbf{Id}&    \textbf{Name}&  \textbf{Age} \\\hline
12&     \text{Arun}&    60 \\ \hline   
15&     \text{Shreya}& 24 \\     \hline
99&     \text{Rohit}&  11    \\\hline
\end{array}}\qquad \overset{\textbf{B}}{\begin{array}{|c|c|c|}\hline\\
\textbf{Id}&    \textbf{Name}&  \textbf{Age} \\\hline
15&     \text{Shreya}& 24 \\     \hline 25 & \text{Hari} & 40 \\ \hline 98 & \text{Rohit} & 20 \\ \hline
99&     \text{Rohit}&  11    \\\hline
\end{array}}\qquad \overset{\textbf{C}}{\begin{array}{|c|c|c|}\hline\\
\textbf{Id}&    \textbf{Phone}&  \textbf{Area} \\\hline
10&     2200&    02 \\ \hline   
99&     2100& 01\\     \hline
\end{array}}$$

How many tuples does the result of the following SQL query contain?

SELECT A.Id 
FROM A 
WHERE A.Age > ALL (SELECT B.Age 
                    FROM B 
                    WHERE B.Name = ‘Arun’)

• A. $4$
• B. $3$
• C. $0$
• D. $1$

Comments

in the same question we are use from Any in the place of All then the answer become zero

---

Yes.
If the inner subquery is empty, then replacing ALL with ANY, IN, or EXISTS would make the result contain 0 tuples.

---

The universal quantifier always returns true for an empty domain because there are no counterexamples to make the condition false.

Answer 1

<cond> ALL evaluates to TRUE if inner query returns no tuples. So, Number of tuples returned will be number of tuples in $A = 3$.

Reference: http://dcx.sap.com/1200/en/dbusage/all-test-quantified-subquery.html

Correct Answer: $B$

Comments

Will this hold even for  "... >any( /*empty collection*/ )...." or  "... >some( /*empty collection*/ )...." also?

---

What would be the answer if the following question would have ANY clause instead of ALL

---

Try it :)

---

In case of any, when subquery returns empty set, then outer query will return nothing. Same link which Arjun Sir has mentioned.

 

http://dcx.sap.com/1200/en/dbusage/quantified-comparison-test-how-subquery.html

---

@shell for your query ANY will return false so 0 will be the answer

---

if ' >all(   )' is true then why ' >any(    ) ' is not?

---

Think in terms of quantifiers in which domain is empty. For all gives true as for not a single tuple it is false. Any means there exist. There doesn't exist a single value for which it is true. Hence for ANY it is false.

---

What about 'some' ?

---

for all == ALL , There exists == any/some

---

if ANY it needs to find atleast one person  in B whose name is Arun and Age greater than him.

But since the subquery returns NOTHING, it wont find any such record. So ANY returns NULL.

---

yes i agree that all returns true in case of empty results from inner query but then we have to compare that true with the greater than so it that mean true is less than all the ages given over there

---

What will be the output of the following query?

SELECT A.Id 
FROM A 
WHERE A.Age > (SELECT B.Age 
                    FROM B 
                    WHERE B.Name = ‘Arun’)

 

---

Would be the same I think because we still have no tuple to compare.

---

I think it would be the same I think because we still have no tuple to compare.

---

someone please update the link, it is not working

---

ANY in the sense there exsists ... so we need atleast one tuple that it satisfies.Since the inner query have no tuples in result,there doesn't exist atleast one tuple which satisfies ,so False .

If we think , ALL - we need atleast one counter example to make it false , as there are no tuples to make it false , ALL(phi) is True 

Answer 2

Ans 3 tuple

All condition with empty return true

Also the result of subquery is empty So all id of A will be selected.

Comments

Manojk

"With Any  if result of subquery is empty  the ANY test returns FALSE.So No tuple will be selected."

Why with Any it returns false?

---

@rajinder singh

Check this link you will get it..

https://gateoverflow.in/185060/sql-keyword-any-and-all

---

If the subquery returns no rows, the ANY condition becomes trivially true for all rows in the outer query. This is because ANY is essentially saying "check if the value in A.Age is greater than any of the values in the subquery." Since there are no values in the subquery, this condition is satisfied by default for all rows in A.

Answer 3

ALL(empty)==> TRUE

When the set is empty then there is no element which returns false on condition, since there is not a single element that returns false, FOR ALL is TRUE

ANY(empty) ==>FALSE

Any means there exists some element in the set such that our condition will hold, since there is no element in the set we can’t say our condition will hold. So  it returns false.

Answer 4

Answer (B)
The meaning of “ALL” is the A.Age should be greater than all the values returned by the subquery. There is no entry with name “arun” in table B. So the subquery will return NULL. If a subquery returns NULL, then the condition becomes true for all rows of A (See this for details). So all rows of table A are selected.

Answer 5

BEST ANSWER