THE AIR FORCE SCHOOL : SUBROTO PARK : DELHI CANTT- 110 010
ASSIGNMENT - SQL
1. Create a database LOAN
2. Open database LOAN
3. Create a table loan_account
4. Insert the following records
accno
Cust_name
Loan_amt
instalment
Int_rate
Start_date
1
R.K. GUPTA
300000
36
12.00
2009-07-19
2
S.P. SHARMA
500000
48
10.00
2008-03-22
3
K.S. DHALL
300000
36
NULL
2007-03-08
4
S.P. SINHA
800000
60
10.00
2008-12-06
5
S.P. SINHA
200000
36
12.50
2010-01=03
6
S.P. SHARMA
700000
60
12.50
2008-06-05
7
K. S. DHALL
500000
48
NULL
2008-03-05
5. Add two more column Interest of float type and Address of character type.
6. Remove address column from the table.
7. Change the interest rate to 11.50% for all the loans for which interest rate is NULL.
8. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000
9. For each loan replace interest with ( loan_amt * int_rate*instalemnts) * 12/100
10. Display the details of all the loaners.
11. Display the accno, cust_name and loan_amt of all the loaners.
12. Display the details of all the loaners with less than 40 instalments.
13. Display the accno and loan amount of all the loaners started before 01-04-2009.
14. Display the int_rate of all the loaners started after 01-04-2009.
15. Display the details of all the loaners whose rate of interest is 12.50.
16. Display the details of all the loaners whose rate of interest is not 12.50.
17. Display the different(unique) loan_amount of various loans.
18. Display the number of installments of various loans.
19. Display the details of all loans started after 31-12-2008 for which the no of installments are more
than 36.
20. Display cust_name and loan amount for all the loaners for which the loan amount is less than
500000 or int_rate is more than 12.
21. Display the details of all the loans whose loan amount is in the range 400000 to 500000
22. Display the details of all the loans whose rate of interest is in the range 11% to 12%
23. Display the cust_name and loan amount for all loans for which the number of installments are
24,36 or 48
24. Display accno, cust_name and loan amount for the loans for which the cust_name ends with
‘SHARMA’
25. Display accno, cust_name and loan amount for all the loans for which cust_name ends with ‘a’
26. Display accno,cust_name and loan amount for all the loans for which cust_name contains ‘a’
27. Display accno,cust_name and loan amount for all the loans for which cust_name does not contains
‘P’
