IAE Academy

GATE 2025 DA Q.33: solution and answer

GATE 2025 · DAQ.33 · 1 mark · NAT

On a relation named Loan of a bank:

| loan_number | branch_name | amount |
|---|---|---|
| L11 | Banjara Hills | 90000 |
| L14 | Kondapur | 50000 |
| L15 | SR Nagar | 40000 |
| L22 | SR Nagar | 25000 |
| L23 | Balanagar | 80000 |
| L25 | Kondapur | 70000 |
| L19 | SR Nagar | 65000 |

the following SQL query is executed.

SELECT L1.loan_number
FROM Loan L1
WHERE L1.amount > (SELECT MAX (L2.amount)
                   FROM Loan L2
                   WHERE L2.branch_name = 'SR Nagar');

The number of rows returned by the query is ______ (Answer in integer)

GATE 2025 DA question 33: A table titled Loan with columns loan_number, branch_name, amount and seven rows: L11 Banjara Hills 90000;Tap to open full size
Figure from the GATE 2025 DA paper. It is part of the question.
Figure description

A table titled Loan with columns loan_number, branch_name, amount and seven rows: L11 Banjara Hills 90000; L14 Kondapur 50000; L15 SR Nagar 40000; L22 SR Nagar 25000; L23 Balanagar 80000; L25 Kondapur 70000; L19 SR Nagar 65000.

Answer and solution

ANSWER

The query returns 3 rows (L11, L23 and L25).

Official answer: 3Source: Official answer key, GATE 2025 DA (IIT Roorkee)

  1. The subquery does not refer to L1, so it is uncorrelated and is evaluated once. It returns the largest amount among the loans whose branch_name is 'SR Nagar'.

  2. The SR Nagar loans are L15 (40000), L22 (25000) and L19 (65000). So the subquery returns .

  3. The outer query returns every loan, from any branch, whose amount is strictly greater than 65000. There is no branch filter in the outer query.

  4. Row by row: L11 (90000) passes. L14 (50000) fails. L15 (40000) fails. L22 (25000) fails. L23 (80000) passes. L25 (70000) passes. L19 (65000) fails, because is false.

  5. The qualifying loans are L11, L23 and L25, so the query returns rows.

KEY IDEA

An uncorrelated subquery with an aggregate is evaluated once and gives a single value. Here that value is the SR Nagar maximum, 65000. The outer > comparison against it is strict and applies to all branches.

WATCH OUT FOR

Two slips are common. One is to count only SR Nagar rows in the outer query, which has no branch filter. The other is to count L19, whose amount equals the maximum, even though > is strict.

From the GATE 2025 DA paper. Every question of this paper · Download the full paper · All 4 SQL questions, with notes

More SQL questions