1. Customer_loan
create table cust0521
(
cno number(5) primary key,
cname varchar2(20) NOT NULL,
address varchar2(20),
city varchar2(20)
);
create table loan0522
(
lno number(5) primary key,
lamt number(20)check(lamt>0)
);
create table cust_loan0523
(
cno number(5)references cust0521(cno),
lno number(5)references loan0522(lno)
);
insert into cust0521 values(10,'basanti','katraj','pune');
insert into cust0521 values(20,'rohit','dhayari','nasik');
insert into cust0521 values(30,'ankit','vadgaon','pune');
insert into cust0521 values(40,'hitesh','katraj','nasik');
insert into cust0521 values(50,'barathi','ambegaon','mumbai');
insert into loan0522 values(11,500000);
insert into loan0522 values(33,700000);
insert into loan0522 values(45,350000);
insert into loan0522 values(22,200000);
insert into loan0522 values(88,550000);
insert into cust_loan0523 values(10,11);
insert into cust_loan0523 values(20,33);
insert into cust_loan0523 values(30,45);
insert into cust_loan0523 values(40,22);
insert into cust_loan0523 values(50,88);
1. select * from cust0521, loan0522,cust_loan0523
where cust0521.cno=cust_loan0523.cno and
loan0522.lno=cust_loan0523.lno and
loan0522.lamt>10000;
CNO CNAME ADDRESS CITY
---------- -------------------- -------------------- --------------------
LNO LAMT CNO LNO
---------- ---------- ---------- ----------
10 basanti katraj pune
11 500000 10 11
20 rohit dhayari nasik
33 700000 20 33
30 ankit vadgaon pune
45 350000 30 45
CNO CNAME ADDRESS CITY
---------- -------------------- -------------------- --------------------
LNO LAMT CNO LNO
---------- ---------- ---------- ----------
40 hitesh katraj nasik
22 200000 40 22
50 barathi ambegaon mumbai
88 550000 50 88
2. select * from cust0521 where cname like 'ba%';
CNO CNAME ADDRESS CITY
---------- -------------------- -------------------- --------------------
10 basanti katraj pune
50 barathi ambegaon mumbai
3. select cname from cust0521, loan0522,cust_loan0523
where cust0521.cno=cust_loan0523.cno AND
loan0522.lno=cust_loan0523.lno AND
city='nasik' order by cname DESC;
CNAME
--------------------
rohit
hitesh
4. select * from cust0521, loan0522,cust_loan0523
where cust0521.cno=cust_loan0523.cno and
loan0522.lno=cust_loan0523.lno and
loan0522.lamt=(select MAX(lamt)from loan0522);
CNO CNAME ADDRESS CITY
---------- -------------------- -------------------- --------------------
LNO LAMT CNO LNO
---------- ---------- ---------- ----------
20 rohit dhayari nasik
33 700000 20 33
5. select SUM(lamt) from loan0522;
SUM(LAMT)
-----------------
2300000
Tags:
Dbms