GATE CS 2012 Q.51 (CS Q.51): SQL Queries and Integrity Constraints

Common data questions

Common Data for Questions 50 and 51:

Consider the following relations A, B and C:

Relation A:

Relation B:

Relation C:

Id	Phone	Area
10	2200	02
99	2100	01

Shared with question 50.

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')

Official answer and solution

Evaluate SQL > ALL subquery behavior when the inner query produces an empty result set.

SELECT B.Age FROM B WHERE B.Name = 'Arun'

Relation $B$ contains no tuple with Name = 'Arun'. Therefore, the subquery returns the EMPTY SET $\emptyset$ .

In SQL standard semantics, the comparison val > ALL (subquery) is defined as: for all elements $y$ in the subquery result, $val > y$ .
When the subquery returns an empty set, this universal quantification is vacuously TRUE for all values (there is no element in the subquery that violates the condition).
Consequently, the WHERE condition evaluates to TRUE for every tuple in relation $A$ .
Relation $A$ has 3 tuples: (12, Arun, 60), (15, Shreya, 24), and (99, Rohit, 11). All 3 tuples satisfy the condition and their Ids are returned.
Therefore, the query returns 3 tuples, matching option (B).

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.