GATE DA 2025 | Question: 23

Question

On a relation named $\text{Loan}$ of a bank:
\[
\begin{array}{|c|}
\hline
\textbf{Loan} \\
\hline
\begin{array}{c|c|c}
\textbf{loan_number} & \textbf{branch_name} & \textbf{amount} \\ 
\hline
\text{L11} & \text{Banjara Hills} & 90000 \\  \hline
\text{L14} & \text{Kondapur} & 50000 \\  \hline
\text{L15} & \text{SR Nagar} & 40000 \\  \hline
\text{L22} & \text{SR Nagar} & 25000 \\  \hline
\text{L23} & \text{Balanagar} & 80000 \\  \hline
\text{L25} & \text{Kondapur} & 70000 \\  \hline
\text{L19} & \text{SR Nagar} & 65000 \\ 
\end{array} \\
\hline
\end{array}
\]
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).

Comments

First 65000 is selected then all attributes greater than 65000 are all selected
so, 90000 , 80000, 70000 => (3)

---

no of tuples/rows is  3

Answer 1

inner query will return maximum amount of $SR$  $NAGAR$ then which is $65000$ now outer query will return all  loan numbers that is having greater amount from it for ex $L11, L23, L25$

Comments

I think  loan number should be L11,L23,L25

---

I guess the loan_number is L11, L23, L25.

L19 can't be the in result because it's equal to 65000 not greater than it.

---

yeah updated thnx :)

Answer 2

The answer to this question is 3

 

The given query is a normal subquery. It is not a correlated subquery as L1 is not used inside the nested subquery. 

So, we will first calculate the inner query. 

The inner query selects the maximum loan amount from the branches named 'SR Nagar'. The output of this will be 65000.

Now the outer query compares the loan amount of each row with 65000.

L11, L23 and L25  have amount greater than 65000. Hence the output will be 3.

Hence the answer is 3.

Answer 3

THE INNER QUERY WILL RETURN THE LAST ROW (65000) OF THE TABLE DUE TO WHERE CONDITION.

OUTER QUERY WILL CHECK THEIR AMOUNT MUST BE THE GRATER THEN 65000.

IF THEIR AMOUNT GRATER THEN IT WILL RETURN THEIR LOAN NUMBER

90000>65000

80000>65000

70000>65000

ANSWER IS 3

 

 

Answer 4

The correct answer is 3.

Selected loan numbers are L11, L23, and L25. Therefore, the number of rows selected is 3.

Answer 5

We are given the relation:

$\texttt{Loan}(\texttt{loan_number}, \texttt{branch_name}, \texttt{amount})$

with the following instance:

\[
\begin{array}{|c|c|c|}
\hline
\texttt{loan_number} & \texttt{branch_name} & \texttt{amount} \\
\hline
\texttt{L11} & \texttt{Banjara Hills} & 90000 \\
\texttt{L14} & \texttt{Kondapur} & 50000 \\
\texttt{L15} & \texttt{SR Nagar} & 40000 \\
\texttt{L22} & \texttt{SR Nagar} & 25000 \\
\texttt{L23} & \texttt{Balanagar} & 80000 \\
\texttt{L25} & \texttt{Kondapur} & 70000 \\
\texttt{L19} & \texttt{SR Nagar} & 65000 \\
\hline
\end{array}
\]

The SQL query to evaluate is:

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

 

Step 1: Evaluate the subquery

The subquery is:

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

First, filter the $\texttt{Loan}$ relation for rows where $\texttt{branch\_name} = \texttt{'SR Nagar'}$:

\[
\begin{array}{|c|c|c|}
\hline
\texttt{loan_number} & \texttt{branch_name} & \texttt{amount} \\
\hline
\texttt{L15} & \texttt{SR Nagar} & 40000 \\
\texttt{L22} & \texttt{SR Nagar} & 25000 \\
\texttt{L19} & \texttt{SR Nagar} & 65000 \\
\hline
\end{array}
\]

Now compute the maximum of the $\texttt{amount}$ column in this filtered set:

\[
\max(40000,\ 25000,\ 65000) = 65000
\]

Thus, the subquery returns the scalar value 65000.

 

Step 2: Evaluate the outer query

The outer query becomes:

SELECT L1.loan_number
FROM Loan L1
WHERE L1.amount > 65000;

We now scan the full $\texttt{Loan}$ instance and select all tuples with $\texttt{amount} > 65000$:

\[
\begin{array}{|c|c|c|}
\hline
\texttt{loan_number} & \texttt{branch_name} & \texttt{amount} \\
\hline
\texttt{L11} & \texttt{Banjara Hills} & 90000 \\
\texttt{L23} & \texttt{Balanagar} & 80000 \\
\texttt{L25} & \texttt{Kondapur} & 70000 \\
\hline
\end{array}
\]

These three rows satisfy the condition. The query projects only the $\texttt{loan_number}$ attribute, yielding:

• $\texttt{L11}$
• $\texttt{L23}$
• $\texttt{L25}$

Hence, the query returns 3 rows.

$\boxed{\text{Final Answer: 3}}$