Assignment SQLO
Queres
Consider a table FINANCE with the following data types and data:
FINANCE
Structure of table:
Field Name Data Type Constraint Description
NUMER(4) PRIMARY KEY Account number
Accno
VARCHAR(15) NOT NULL Bank name
Bname
VARCHAR(25) NOT NULL Customer name
CName
NUMBER(8, 2) -Loan amount
LAmount
Instalments NUMBER(3) Total iumber of instalments
NUMBER(5,2) -DEFAULT 8.0 Interest rate
IRate
ISDate
Date Interest starting date
FINANCE
Data in table:
Accno Bname CName LAmount Instalments IRate ISDate
1- SBI Vikash-Rai 750000 142 12:00 -2011-07-19
1580000 252 10.00 2010-03-22
2 ICICI Milkha Singh
PNB Binod Goel 530000 -140 -NULL 2012-03-08
3
SBI VINI Gil 850000 165 10.00 2011-06-12
5 OBC [Link] 1200000. -210 12.50 2011-01-03
6 ICICI [Link] 1700000 154 12:50 2009-06-05
HDEC J. N Mittäl. 1500000 190. NULL 2010-03-05
Write SQL'query commands for the following questions:
Create Table/Insert Into:
1. Create the table FINANCE.
2.. Display the structure of the table FIÑANCE.
3. Insert records/tuples in it.
Simple SELECT query questions
4. Display the details of alI the rows from FINANCE table
S.-Display the rows containing AccNo, Bniarme, Cname,rand LAmountcolumns.
Conditional SELECT-query using-WHERE Clause
6. Display the details of all:the finance records whose instalment is lessthan 200:
. Display thé Accno, Chame [Link]. allthe records,which started. before (2011-01-01.
8. Display the Accno, Chame and LAMount of all the finance records whose interest rate is imore than
and equalto;010.00.
SELECT query Using NULL:
NUDL:
9. Display the details of all theloans whose rate of interest ls
t - t t t t t t
NULL.
10. Display the details of all;the loans'whose rate of interest is not
SELECT query Using DISTINCT Clause:
from the table FINANCE!
11. Display the name of banks who finance various loans
variousloans excluding NULL.
12. Displaythe different interest rates that banksare charging for
438 nn Informatics Practices XI
SELECT query using Logical Operators (NOT, AND, OR) :
13. Display the details of all the finance arnount which started after 2010-12-31 and for which the
number of instalmentsis more than 150.
14. Display the CName and LAmount for all those records for which either instalments are less than 150
or bank name is SBI.
15. Display the CName and LAmount for allthose loans for which the loan amount is either less than
1200000 or IRate is more than 11.50.
16. Display the details of allthe loans which are financed in the year 2009.
17.4 Display thedetails of all the loans whose LAmount is in the range 800000 to 1500000.
18. Display the details of all theloans whose rate of interest is in the range 11:00 to 12.00.
SELECT query using IN Operator :
19. Display the Cust Name and Loan Amount for allthe loansifor which the number of instalments are
140, 165, and 190. (Using IN operator).
SELECT query using BETWEEN Operator:
20. Display the details of all the amounts whose LAmount is in the range 800000 to 1500000.(Using
BETWEEN operator).
21. Display the details of all the loans whose rate of interest is in the tange 10.00 to 12.00 (Using
BETWEEN operator).
SELECT query using LIKE Operator:
22. Display the Accno, C Name, and LAmount for all the loans for which the Cust Name ends with
'Singh.
23. Display the Accno, CName, and LAmount for all the loans for which the CName ends with'a'.
24. Display the AccNo, Cust Name, and Loan Amountfor all the loans forwhich the Cust Namecontains:
25. Display the Accno, CName, and LAmount for all the loansfor which the Cust Name does not contain
'n'
26. Display the AccNo, Cust_Name, and Loan Amount for all the toans for which the Cust Namecontains
a' as the second last character.
SELECT query using ORDER BY clause :
27. Display the details of all theloans in the ascending order oftheir LAmount.
28. Display the details of all the loans in the descending order of their lSDate.
29. Display the details of all the loans in the ascernding orderof their toan Amount and within
Loan_Amount in the descending [Link] their. Start Date.!
Using UPDATE, DELETE, ALTER TABLE :
[Link] the interest rate11.50 forall the loans for which-the interest rate is NUEL
31. Increase the interest rate by 0.2for all, the loansfor which the loanamount is more than 1000000.
32. Delete the records of all the loans whose start date isibefore 2007.
33. Delete the records of all the [Link]'K.P. Singh'.
34. Add another column Category of type CHAR(1) in the FINANCE table!
SELECT query using aggregate functions:
35. To find the highest loan amount whose interest rate is 12.70.
36. To find customer name and minimum loan amount
which started thyear 20111
37. To find bank name who has highest interestrrate.
SQL SELECT Statement 439
38. To count total number of customers'whose interest rate is NULL:.
39. To find total loan amount financed in year 2010:
SELECT query using GROUP BY and HAVING clause:
40. Writeaquery to bank wise display total finance amount.
41. Write a query to find the maximum loan amount of each bank that has amaxímum loan amount
more than 1000000.