Consider the following SQL query: SELECT DISTINCT T.branch_name FROM branch AS…

2011

Consider the following SQL query:

SELECT DISTINCT T.branch_name
FROM branch AS T, branch AS S
WHERE T.assets > S.assets
  AND S.branch_city = 'DELHI';

What does the query return?

Answer: A. All branches whose assets exceed the assets of at least one branch located in Delhi. — ConceptA comma-separated FROM clause forms candidate pairs of rows. A WHERE condition retains a pair only when all its predicates are true. If a projected row…

  1. A.

    All branches whose assets exceed the assets of at least one branch located in Delhi.

  2. B.

    All branches whose assets exceed the assets of every branch located in Delhi.

  3. C.

    The Delhi branch with the greatest assets.

  4. D.

    All Delhi branches whose assets exceed the assets of at least one branch outside Delhi.

Attempted by 28 students.

Show answer & explanation

Correct answer: A

Concept

A comma-separated FROM clause forms candidate pairs of rows. A WHERE condition retains a pair only when all its predicates are true.

If a projected row has at least one qualifying partner, it appears in the result; DISTINCT then removes duplicate projected values.

Application

  1. T and S are two aliases of the same branch table, so the query considers pairs consisting of a candidate branch T and a comparison branch S.

  2. The predicate S.branch_city = 'DELHI' limits S to branches located in Delhi.

  3. For a fixed T row, the condition T.assets > S.assets is satisfied when there exists at least one Delhi branch S with fewer assets.

  4. The SELECT clause projects T.branch_name, and DISTINCT keeps each qualifying branch name only once even if several Delhi rows satisfy the comparison.

Cross-check and contrast

  • The phrase “every Delhi branch” would require a universal comparison such as > ALL or a comparison with the maximum Delhi assets.

  • Selecting the richest Delhi branch would require restricting T to Delhi and applying a maximum or ordering rule.

  • Comparing Delhi branches with outside-Delhi branches would require explicit city predicates on both aliases.

Therefore, the query returns all branches whose assets exceed the assets of at least one branch located in Delhi.

Explore the full course: Tpsc Assistant Technical Officer

Loading lesson…