create database bank
create table BRANCH(
bname varchar(15)primary key,
bcity varchar(15),
assets real
)
insert into BRANCH values('synd_nitte','karkala',200000)
insert into BRANCH values('Corp_nitte','karkala',300000)
insert into BRANCH values('PNB_nitte','karkala',100000)
insert into BRANCH values('Corp_mang','Mangalore',300000)
insert into BRANCH values('PNB_mang','Mangalore',500000)
insert into BRANCH values('state_udupi','Udupi',500000)
insert into BRANCH values('synd_udupi','Udupi',500000)
select * from BRANCH
create table ACCOUNT(
accno int,
bname varchar(15),
balance real,
primary key(accno),
foreign key(bname) references BRANCH(bname) on delete cascade on update cascade
)
insert into ACCOUNT values(12345,'synd_nitte',6000)
insert into ACCOUNT values(14345,'Corp_nitte',15000)
insert into ACCOUNT values(13345,'PNB_nitte',11000)
insert into ACCOUNT values(13346,'PNB_nitte',11000)
insert into ACCOUNT values(13347,'PNB_nitte',11000)
insert into ACCOUNT values(12340,'synd_nitte',6000)
insert into ACCOUNT values(14341,'Corp_nitte',15000)
insert into ACCOUNT values(15345,'synd_udupi',11000)
insert into ACCOUNT values(21345,'synd_nitte',10000)
insert into ACCOUNT values(21346,'PNB_mang',10000)
insert into ACCOUNT values(13245,'state_udupi',5000)
insert into ACCOUNT values(13241,'state_udupi',5000)
insert into ACCOUNT values(12375,'state_udupi',12000)
insert into ACCOUNT values(12377,'state_udupi',12000)
insert into ACCOUNT values(12378,'state_udupi',12000)
insert into ACCOUNT values(12453,'PNB_mang',17000)
insert into ACCOUNT values(15342,'state_udupi',19000)
insert into ACCOUNT values(12450,'PNB_mang',17000)
insert into ACCOUNT values(13340,'PNB_nitte',11000)
insert into ACCOUNT values(12451,'state_udupi',17000)
insert into ACCOUNT values(12452,'PNB_mang',17000)
insert into ACCOUNT values(12455,'Corp_nitte',17000)
select * from depositor D , account A where D.accno = A.accno and D.cname = 'rakesh'
select * from ACCOUNT
select * from DEPOSITOR
create table CUSTOMER(
cname varchar(20)primary key,
cstreet varchar(25),
ccity varchar(20)
)
insert into CUSTOMER values('Rakesh','3rd main','karkala')
insert into CUSTOMER values('Ramesh','4th main','karkala')
insert into CUSTOMER values('Rajesh','4th block','mangalore')
insert into CUSTOMER values('Kareem','456 nagar','mangalore')
insert into CUSTOMER values('John smith','452 street','Uudpi')
create table DEPOSITOR(
cname varchar(20),
accno int,
primary key(cname,accno),
foreign key(cname) references CUSTOMER(cname) on delete cascade on update cascade,
foreign key(accno) references ACCOUNT(accno) on delete cascade on update cascade,
unique(accno)
)
select * from account
insert into DEPOSITOR values('Rakesh',12345)
insert into DEPOSITOR values('Rakesh',13345)
insert into DEPOSITOR values('Rakesh',14345)
insert into DEPOSITOR values('Rakesh',13346)
delete from DEPOSITOR where accno = 12345
insert into DEPOSITOR values('Ramesh',12375)
insert into DEPOSITOR values('Ramesh',12377)
insert into DEPOSITOR values('Ramesh',12378)
insert into DEPOSITOR values('Ramesh',12450)
insert into DEPOSITOR values('Ramesh',13340)
insert into DEPOSITOR values('Ramesh',12451)
insert into DEPOSITOR values('Ramesh',12452)
insert into DEPOSITOR values('Ramesh',12455)
delete from DEPOSITOR where accno = 12450
insert into DEPOSITOR values('Rakesh',15342)
insert into DEPOSITOR values('Rakesh',12340)
insert into DEPOSITOR values('Rakesh',14341)
insert into DEPOSITOR values('Rajesh',15345)
insert into DEPOSITOR values('Rajesh',13241)
insert into DEPOSITOR values('Kareem',21346)
insert into DEPOSITOR values('Kareem',13245)
insert into DEPOSITOR values('John smith',21345)
insert into DEPOSITOR values('John smith',12453)
insert into DEPOSITOR values('John smith',13347)
select * from depositor D , account A where D.accno = A.accno and D.cname = 'rakesh'
select * from customer
select distinct bcity from branch
select * from DEPOSITOR
select * from ACCOUNT
select * from CUSTOMER
create table LOAN (
loanno int,
bname varchar(15),
amount real,
primary key(loanno),
foreign key(bname) references BRANCH(bname) on delete cascade on update cascade
)
insert into LOAN values(1,'Corp_mang',12000)
insert into LOAN values(2,'Corp_mang',11000)
insert into LOAN values(3,'Corp_mang',10000)
insert into LOAN values(4,'Corp_nitte',16000)
insert into LOAN values(5,'Corp_nitte',13000)
insert into LOAN values(6,'PNB_mang',12000)
insert into LOAN values(7,'state_udupi',20000)
insert into LOAN values(8,'state_udupi',23000)
insert into LOAN values(9,'synd_nitte',32000)
insert into LOAN values(10,'PNB_nitte',12000)
select * from LOAN
create table BORROWER(
cname varchar(20),
loanno int
primary key(cname,loanno),
foreign key(cname) references CUSTOMER(cname) on delete cascade on update cascade,
foreign key(loanno) references LOAN(loanno) on delete cascade on update cascade,
unique(loanno)
)
insert into BORROWER values('John smith',1)
insert into BORROWER values('John smith',2)
insert into BORROWER values('John smith',3)
insert into BORROWER values('Kareem',4)
insert into BORROWER values('Kareem',5)
insert into BORROWER values('Rajesh',6)
insert into BORROWER values('Rajesh',7)
insert into BORROWER values('Rajesh',8)
insert into BORROWER values('Rakesh',9)
insert into BORROWER values('Ramesh',10)
select * from BORROWER
select * from BRANCH
1. Find all the customers who have at least two accounts at the Main branch.
select D.cname from DEPOSITOR D , ACCOUNT A
where D.accno = A.accno and A.bname = 'state_udupi' group by D.cname having count(*) >= 2
2A. Find all the customers who have an account at all the branches
located in a specific city.
select C.cname from CUSTOMER C
where not exists(
select bname from BRANCH where bcity = 'karkala' and bname not in
(select distinct(A.bname) from ACCOUNT A , BRANCH B,DEPOSITOR D
where A.bname = B.bname
and D.accno = A.accno
and B.bcity = 'karkala'
and D.cname = C.cname )
)
OR
select C.cname from CUSTOMER C
where not exists(
select B.bname from BRANCH B where bcity = 'karkala' and B.bname not in
(select distinct(A.bname) from ACCOUNT A,DEPOSITOR D
where D.accno = A.accno
and A.bname = B.bname
and D.cname = C.cname )
)
OR
select C.cname from CUSTOMER C
where not exists(
select B.bname from BRANCH B where B.bcity = 'karkala'
and not exists(
(select * from ACCOUNT A , DEPOSITOR D
where D.accno = A.accno
and A.bname = B.bname
and D.cname = C.cname ))
)
2B. Find all the customers who have atleast 2 accounts at all the branches
located in a specific city.
select C.cname from CUSTOMER C
where not exists(
select B.bname from BRANCH B where B.bcity = 'karkala' and bname not in
(select A.bname from ACCOUNT A ,DEPOSITOR D
where D.accno = A.accno
and A.bname = B.bname
and D.cname = C.cname group by A.bname having count(*) >= 2)
)
or
select * from CUSTOMER
Find all the customers who have accounts in atleast 1 branch located in all the cities
select C.cname from CUSTOMER C
where not exists(
select distinct(B.bcity) from BRANCH B
where not exists
(
select A.bname from ACCOUNT A ,DEPOSITOR D
where D.accno = A.accno
and D.cname =C.cname and A.bname in (select bname from BRANCH where bcity = B.bcity)
)
)
select C.cname from CUSTOMER C
where not exists(
select distinct(B1.bcity) from BRANCH B1
where not exists(
select count( distinct B.bname) from BRANCH B, ACCOUNT A ,DEPOSITOR D
where A.bname = B.bname
and D.accno = A.accno
and B.bcity = B1.bcity
and D.cname = C.cname group by B.bcity having count(*) >=1))
select * from customer
select * from branch
Find all the customers who have accounts in atleast 2 branches located in a specific city.
select C.cname from CUSTOMER C
where exists(
select count( distinct B.bname) from BRANCH B, ACCOUNT A ,DEPOSITOR D
where A.bname = B.bname
and D.accno = A.accno
and B.bcity = 'karkala'
and D.cname = C.cname group by B.bcity having count(*) >=2)
Find all the customers who have accounts in atleast 2 branches located in all the cities
select C.cname from CUSTOMER C
where not exists(
select distinct(B1.bcity) from BRANCH B1
where not exists(
select count( distinct B.bname) from BRANCH B, ACCOUNT A ,DEPOSITOR D
where A.bname = B.bname
and D.accno = A.accno
and B.bcity = B1.bcity
and D.cname = C.cname group by B.bcity having count(*) >=2))
select * from customer
select * from branch
select * from BORROWER
select bname from BRANCH B where B.bcity = 'karkala'
select L.bname from BORROWER B , LOAN L where L.loanno = B.loanno and B.cname = 'Rajesh'
Find the branch name that has maximum number of customers in a specific city
select D.cname, A.bname, count(*) from ACCOUNT A, DEPOSITOR D
where A.accno = D.accno group by D.cname , A.bname
select A.bname,count(distinct D.cname) from ACCOUNT A, DEPOSITOR D
where A.accno = D.accno group by A.bname
having count(distinct D.cname) >= all (select count(distinct D.cname) from ACCOUNT A, DEPOSITOR D
where A.accno = D.accno group by A.bname)
select * from ACCOUNT
No comments:
Post a Comment