Slip 5
Consider
the following entities and their relationships. Create a RDB in 3 NF with
appropriate
data types and Constraints. [15 Marks]
Customer
(cust_no, cust_name, address, city)
Loan
(loan_no, loan_amt)
The
relationship between Customer and Loan is Many to Many
Constraint:
Primary key, loan_amt should be > 0.
Solution
Create table Customer(cust_no int primary key, cust_name varchar(10), address varchar(10), city varchar(10));
insert
into Customer values(101,'asha','rastapeth','pune');
select
* from Customer;
Commit;
Create
table Loan (loan_no int primary key, loan_amt int check (loan_amt>0));
insert
into Loan values(201,23000);
Select
* from Loan;
Commit;
Create
table Customer_Loan(cust_no int ,loan_no int);
insert
into Customer_Loan values(101,201);
Select
* from Customer_Loan;
Commit;
How to join 3 tables in Many to Many
Relation ?
Select
* from First table,SEcond Table,Third Table
where
Firsttable.Firsttables
pk=thirdtable.Firsttables pk
and
Second
table.secondtable pk=thirdtable.secondtable pk;
Consider the above tables and execute the
following queries:
1. Add
Phone_No column in customer table with data type int.
SQL>ALTER
TABLE Customer ADD Phone_No int;
2.
Delete the details of customer whose loan_amt<1000.
SQL>DELETE
FROM Customer WHERE loan_amt<1000;
Consider the above tables and execute the
following queries: [25 Marks]
1. Find
details of all customers whose loan_amt is greater than 10 lakh.
SQL>select Customer.cust_name,Customer.address
from Customer,Loan,Customer_Loan
where Customer.cust_no=Customer_Loan.cust_no
and Loan.loan_no= Customer_Loan.loan_no
and Loan.loan_amt>=1000000;
2. List
all customers whose name starts with 'D' character.
SQL>Select
* from Customer Where cust_name like 'D%';
3. List the names of customer in descending order who has taken a loan from Pimpri city.
SQL>select
Customer.cust_name,Customer.address from Customer,Loan,Customer_Loan
where Customer.cust_no=Customer_Loan.cust_no
and Loan.loan_no= Customer_Loan. loan_no and Customers.city='Pimpri' order by
Customers.cust_name desc ;
4.
Display customer details having maximum loan amount.
SQL>select Customer.cust_name,Customer.address from Customer,Loan,Customer_Loan
where
Customer.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 ) ;
5.
Update the address of customer whose name is “Mr. Patil” and loan_amt is
greater than 100000.
SQL>update
customer set city='pune' where cname='patil' and loan_no in(select loan_no from
loan where loan_amt>100000);
