3.2k views

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 | 3.2k views
+5

I think this is prerequisite :D

ALL(empty) => True

​​​​​​Check here

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

Correct Answer: $B$

by Veteran (425k points)
edited
+3
Will this hold even for  "... >any( /*empty collection*/ )...." or  "... >some( /*empty collection*/ )...." also?
0
What would be the answer if the following question would have ANY clause instead of ALL
+2
Try it :)
0
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
0
@shell for your query ANY will return false so 0 will be the answer
0
if ' >all(   )' is true then why ' >any(    ) ' is not?
+9
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.
0
What about 'some' ?
+2
for all == ALL , There exists == any/some
Ans 3 tuple

All condition with empty return true

Also the result of subquery is empty So all id of A will be selected.
by Boss (38.4k points)
edited
+1

what will hapen if we have any instead of all?

+19

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

0
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?
0