The Gateway to Computer Science Excellence

+30 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’)

- $4$
- $3$
- $0$
- $1$

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

+3

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

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

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

+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.

All condition with empty return true

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

52,345 questions

60,487 answers

201,823 comments

95,293 users