edited by
13,582 views
48 votes
48 votes

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’)
  1. $4$
  2. $3$
  3. $0$
  4. $1$
edited by

4 Answers

Best answer
64 votes
64 votes

<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$

edited by
13 votes
13 votes
Ans 3 tuple

All condition with empty return true

Also the result of subquery is empty So all id of A will be selected.
edited by
3 votes
3 votes

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.

edited by
0 votes
0 votes

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:

Related questions

70 votes
70 votes
6 answers
2
gatecse asked Sep 29, 2014
29,076 views
Consider the following relations $A, B$ and $C:$$$\overset{\text{A}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Name} & \text {Age} \\\hline\text{12}& \text{Arun}...
70 votes
70 votes
7 answers
4