Question

Assume that you are the Manager of the Loans department of a Finance House. To keep track of the loans you have created two tables : CUSTOMERS and LOANS. The sample data in these tables is given below:

Table: CUSTOMERS
C_ID	C_Name	Phone
00001	Raj Malhotra	1234567890
00003	David Xavier	3456789012
00004	Damini Iyer	3156789012
00008	Abdul	2345678901

Table: LOANS
SNO	CID	L Amt	L Date	Terms	RoI
1	00003	200000	2025-12-06	60	7.80
2	00008	2500000	2023-08-09	60	9.00
3	00001	500000	2025-08-13	48	6.00
4	00003	300000	2026-12-07	36	8.00
5	00004	600000	2026-12-07	60	6.00

Note: The tables may contain more records than shown here.

The management of the Finance House needs certain reports from you. Write the queries to extract the following data to create the reports:

1. Number of records from LOANS table where Rate of Interest (RoI) is above 7.0.
2. Names of the customers whose loan amount (L_Amt) is above 1000000.
3. C_ID, C_Name and Terms of all those records where Loan Date (L_Date) is after 31st December, 2024.
4. (a)
   1. Details of all the loans in the descending order of RoI.
      OR
   2. C_ID and average term for each C_ID from the LOANS table.

Answer 1

1. SELECT COUNT(*) FROM LOANS WHERE RoI > 7.0;
   

2. SELECT C_Name FROM CUSTOMERS C, LOANS L 
   WHERE C.C_ID = L.C_ID AND L_Amt > 1000000;
   

3. SELECT C.C_ID, C_Name, Terms FROM CUSTOMERS C, LOANS L 
   WHERE C.C_ID = L.C_ID AND L_Date > '2024-12-31';
   

4. 1. SELECT * FROM LOANS ORDER BY RoI DESC;
      

      OR

   2. SELECT C_ID, AVG(Terms) FROM LOANS GROUP BY C_ID;