Slip1
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;
