On a relation named Loan of a bank:
LOAN 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)
The question asks for the number of loans whose amounts are greater than the maximum loan amount from the 'SR Nagar' branch.
SQL Query Analysis
The SQL query consists of two parts:
- A subquery: $SELECT MAX (L2.amount) FROM Loan L2 WHERE L2.branch_name = 'SR Nagar'$
- A main query: $SELECT L1.loan_number FROM Loan L1 WHERE L1.amount > (subquery_result)$
Subquery Execution
First, the subquery identifies loans associated with the 'SR Nagar' branch and finds the maximum amount among them.
Loans from 'SR Nagar' branch:
| loan_number | branch_name | amount |
| L15 | SR Nagar | 40000 |
| L22 | SR Nagar | 25000 |
| L19 | SR Nagar | 65000 |
The maximum amount for 'SR Nagar' is 65000.
Main Query Execution
Next, the main query selects loan numbers ($loan_number$) from the $Loan$ table (aliased as $L1$) where the loan amount ($L1.amount$) is strictly greater than the result of the subquery (65000).
We need to find loans with $amount > 65000$:
- L1: amount = 90000 (> 65000)
- L14: amount = 50000 (≤ 65000)
- L15: amount = 40000 (≤ 65000)
- L22: amount = 25000 (≤ 65000)
- L23: amount = 80000 (> 65000)
- L25: amount = 70000 (> 65000)
- L19: amount = 65000 (≤ 65000)
The loans satisfying the condition $amount > 65000$ are L1, L23, and L25.
Result Count
The query returns the $loan_number$ for these loans.
The loan numbers are: L1, L23, L25.
The total number of rows returned is 3.
Important Questions from SQL
Consider a relation book (title, price) which contains the titles and prices of different books.
Assuming that no two books have the same price, what does the following SQL query list ?
Select title
from book as B
where (select count (*)
from book as T
where T.price > B.price) < 7Match the following -
List I List II (a) DDL (i) LOCK TABLE (b) DML (ii) COMMIT (c) TCL (iii) Natural Difference (d) Binary operation (iv) REVOKE Given the following STUDENT‐COURSE scheme : STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find total number of students enrolled in each course, along with its coursename.
A. SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;
B. SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;
C. SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);
Which of the following is used to create a database schema?
Which of the component module of DBMS does rearrangement and possible ordering of operations, eliminate redundancy in query and use efficient algorithms and indexes during the execution of a query?
