The Gateway to Computer Science Excellence
+25 votes
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$
in Databases by Veteran (105k points)
edited by | 3.2k views
+5

I think this is prerequisite :D 

ALL(empty) => True

 

​​​​​​Check here

2 Answers

+33 votes
Best answer

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

by Veteran (425k points)
edited by
+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
+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.
by Boss (38.4k points)
edited by
+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

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,647 questions
56,508 answers
195,530 comments
100,967 users