Slip1
QUESTION
Consider the following Entities and Relationships
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
Relation between Customer and Loan is Many to Many
Constraint: Primary key, loan_amt should be > 0.
Create a Database in 3NF & write queries for following.
- Find details of all customers whose loan is greater than 10 lakhs.
- List all customers whose name starts with 'ba'.
- List names of all customers in descending order who has taken a loan in Nasik city.
- Display customer details having maximum loan amount.
- Calculate total of all loan amount.
SOLUTION
Consider the following Entities and Relationships
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
Relation between Customer and Loan is Many to Many
Constraint: Primary key, loan_amt should be > 0.
Solution:-
Create a Database in 3NF & write queries for following.
- Find details of all customers whose loan is greater than 10 lakhs.
SQL>select Customers.cust_name,Customers.address from Customers,Loan,Customer_Loan where Customers.cust_no=Customer_Loan.cust_no and Loan.loan_no= Customer_Loan. loan_no and Loan.loan_amt>=10000;
- List all customers whose name starts with 'ba'.
SQL>Select * from Customers Where cust_name like 'ba%';
- List names of all customers in descending order who has taken a loan in Nasik city.
SQL>select Customers.cust_name,Customers.address from Customers,Loan,Customer_Loan where Customers.cust_no=Customer_Loan.cust_no and Loan.loan_no= Customer_Loan. loan_no and Customers.city='nashik' order by Customers.cust_name desc ;
- Display customer details having maximum loan amount.
SQL>select Customers.cust_name,Customers.address from Customers,Loan,Customer_Loan where Customers.cust_no=Customer_Loan.cust_no and Loan.loan_no= Customer_Loan. loan_no and Loan.loan_amt =(select max(Loan.loan_amt) from Loan ) ;
- Calculate total of all loan amount.
SQL>select sum(Loan.loan_amt) from Loan;