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

ayush.5 0, NULL is not comparable.

---

Just for Knowledge

:)

---

Thanks for this! @Yaman Sahu 

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

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.

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

what will hapen if we have any instead of all?

---

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

---

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

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.