#Problem statement
A bank has many branches and a large number of customers. Bank is
identified by its code. Other details like name, address and phone for each
bank are also stored. Each branch is identified by its bank. Branch has name,
address and phone. A customer can open different kinds of accounts with the
branches. An account can belong to more than one customer. Customers are
identified by their SSN, name, address and phone number. Age is used as a
factor to check whether customer is a major. There are different types of
loans, each identified by a loan number. A customer can take more than one type
of loan and a loan can be given to more than one customer. Loans have a
duration and interest rate. Make suitable assumptions and use them in showing
maximum and minimum cardinality ratios.
Queries:
a) List the details of customers who have joint account and also
have at least one loan.
b) List the details of the branch which has given maximum
loan.
c) List the details of saving accounts opened in the SBI branches
located at Bangalore.
d) List the name of branch along with its bank name and total
amount of loan given by it.
e) Retrieve the names of customers who have accounts in all the
branches located in a specific city.
…………………………………CREATE TABLE……………………………………………
create table banks
(
bank_id
varchar(10),
name
varchar(15),
address
varchar(20),
phno
number(10),
primary key(bank_id)
);
create table branches
(
bank_id
varchar(10),
branch_id varchar(10),
name
varchar(15),
address
varchar(20),
phno
number(10),
primary key(branch_id),
foreign key(bank_id)
references banks
);
create table customers
(
ssn
varchar(10),
name
varchar(15),
address
varchar(20),
phno
number(10),
age
number(5),
primary key(ssn)
);
create table loans
(
loan_id
varchar(7),
branch_id varchar(10),
ssn
varchar(10),
type
varchar(10),
amount
number(8),
duration
number(8),
interest
number(4,2),
primary key(loan_id,ssn),
foreign
key(branch_id)
references branches,
foreign
key(ssn)
references customers
);
create table accounts
(
accno
number(6),
branch_id varchar(10),
ssn
varchar(10),
acctype
varchar(15),
balance
number(6,2),
primary key(accno,ssn),
foreign key(branch_id) references
branches,
foreign
key(ssn)
references customers
);
…………………….INSERT INTO TABLE…………………….
insert into banks values('IN001','SBI','Bangalore','9420767321');
insert into banks values('IN002','Union','Mysore','9720768321');
insert into banks values('IN003','Canaar','Chennai','9424767321');
insert into banks values('IN004','Axis','Belgaum','9420767721');
insert into banks values('IN005','Syndicate','Goa','9420744321');
insert into banks values('IN006','Dena','Mumbai','9320767221');
insert into banks values('IN007','Corporation','Kerala','9020767321');
insert into banks values('IN008','Citi','Delhi','9420768821');
insert into banks values('IN009','Yes
Bank','Bangalore','8020767321');
insert into banks values('IN0010','SBM','Gujarat','7720767321');
insert into branches
values('IN001','SB001','SB','Bangalore','9923490782');
insert into branches
values('IN001','UN001','UN','Mysore','8023490782');
insert into branches
values('IN003','CN001','CN','Chennai','7723490782');
insert into branches values('IN004','AX001','AX','Belgaum','9023490782');
insert into branches
values('IN005','SY001','SY','Goa','9823450782');
insert into branches
values('IN006','DE001','DE','Mumbai','8823490782');
insert into branches values('IN007','CO001','CO','Kerala','9923495682');
insert into branches
values('IN008','CI001','CI','Delhi','7923490782');
insert into branches
values('IN009','YE001','YE','Bangalore','8823490782');
insert into branches
values('IN0010','SM001','SM','Gujarat','9223490782');
insert into customers values('S01','Vaibhav','Belgaum',2480047,30);
insert into customers values('S02','Vivek','Bangalore',2461415,24);
insert into customers values('S03','Kiran','Chennai',2480417,24);
insert into customers values('S04','Shrikrish','Goa',2531532,34);
insert into customers values('S05','Avinash','Kerala',2532300,50);
insert into customers values('S06','Dhanraj','Delhi',2095773,43);
insert into customers
values('S07','Akhilesh','Bangalore',2509543,24);
insert into customers values('S08','Ajay','Gujarat',2434771,19);
insert into customers values('S09','Shankar','Belgaum',29545,21);
insert into customers
values('S010','Vaibhav','Belgaum',2095777,20);
insert into loans
values('L001','SB001','S01','Housing','50000','10','2.4');
insert into loans
values('L002','SB001','S02','Vehicle','30000','5','1.4');
insert into loans
values('L003','SB001','S03','Education','20000','3','1.2');
insert into loans
values('L004','UN001','S04','Personal','10000','6','2.2');
insert into loans values('L005','YE001','S05','Medical','000','2','3.2');
insert into loans
values('L006','DE001','S06','Housing','75000','5','2.4');
insert into loans values('L007','CO001','S07','Education','35000','8','2.5');
insert into loans
values('L008','SB001','S08','Car','78000','3','2.8');
insert into loans
values('L009','SB001','S09','Housing','50000','10','2.4');
insert into loans values('L0010','SB001','S010','Personal','50000','5','2.4');
insert into accounts values('100','SB001','S01','Savings','5000');
insert into accounts values('100','SB001','S02','Savings','2000');
insert into accounts values('100','UN001','S03','Savings','3000');
insert into accounts values('100','UN001','S04','Savings','00');
insert into accounts values('104','DE001','S05','Savings','2000');
insert into accounts values('104','YE001','S03','Joint','1000');
insert into accounts values('104','AX001','S04','Current','8000');
insert into accounts values('107','SB001','S06','Fixed Deposit','00');
insert into accounts values('108','AX001','S06','Savings','2000');
insert into accounts values('109','CO001','S09','Joint','3000');
insert into accounts values('109','CO001','S010','Savings','5000');
SQL> select * from banks;
BANK_ID
NAME
ADDRESS
PHNO
---------- --------------- -------------------- ----------
IN001
SBI Bangalore
9420767321
IN002
Union
Mysore
9720768321
IN003
Canaar
Chennai
9424767321
IN004 Axis
Belgaum
9420767721
IN005
Syndicate
Goa
9420744321
IN006
Dena
Mumbai
9320767221
IN007
Corporation
Kerala
9020767321
IN008
Citi
Delhi
9420768821
IN009 Yes
Bank
Bangalore
8020767321
IN0010 SBM
Gujarat
7720767321
10 rows selected.
SQL> select * from accounts;
ACCNO BRANCH_ID
SSN
ACCTYPE
BALANCE
---------- ---------- ---------- --------------- ----------
100
SB001
S01
Savings
5000
100
SB001
S02
Savings
2000
100
UN001
S03
Savings
3000
100
UN001
S04
Savings
00
104
DE001
S05
Savings
2000
104
YE001
S03
Joint
1000
104
AX001
S04
Current
8000
107
SB001
S06 Fixed
Deposit 00
108 AX001
S06
Savings
2000
109
CO001
S09
Joint
3000
109
CO001 S010
Savings
5000
11 rows selected.
SQL> select * from loans;
LOAN_ID BRANCH_ID
SSN
TYPE
AMOUNT DURATION INTEREST
------- ---------- ---------- ---------- ---------- ----------
----------
L001 SB001
S01
Housing
50000
10 2.4
L002 SB001
S02
Vehicle
30000
5 1.4
L003 SB001
S03
Education
20000
3 1.2
L004 UN001
S04
Personal
10000
6 2.2
L005 YE001
S05
Medical 000
2 3.2
L006 DE001
S06 Housing
75000
5 2.4
L007 CO001
S07
Education
35000
8 2.5
L008 SB001
S08
Car
78000 3
2.8
L009 SB001
S09
Housing
50000
10 2.4
L0010 SB001
S010
Personal
50000
5 2.4
10 rows selected.
SQL> select * from branches;
BANK_ID BRANCH_ID
NAME
ADDRESS
PHNO
---------- ---------- --------------- --------------------
----------
IN001
SB001
SB
Bangalore
9923490782
IN001
UN001
UN
Mysore
8023490782
IN003
CN001
CN
Chennai
7723490782
IN004
AX001
AX
Belgaum
9023490782
IN005
SY001
SY
Goa
9823450782
IN006
DE001
DE
Mumbai
8823490782
IN007
CO001 CO
Kerala
9923495682
IN008
CI001
CI
Delhi
7923490782
IN009
YE001
YE
Bangalore
8823490782
IN0010 SM001
SM
Gujarat
9223490782
10 rows selected.
SQL> select * from customers;
SSN
NAME
ADDRESS
PHNO AGE
---------- --------------- -------------------- ----------
----------
S01
Vaibhav
Belgaum
2480047 30
S02
Vivek
Bangalore
2461415 24
S03
Kiran
Chennai
2480417 24
S04
Shrikrish
Goa
2531532 34
S05
Avinash
Kerala
2532300 50
S06
Dhanraj
Delhi
2095773 43
S07
Akhilesh
Bangalore
2509543 24
S08
Ajay
Gujarat
2434771
19
S09
Shankar
Belgaum
29545 21
S010
Vaibhav
Belgaum
2095777 20
10 rows selected.
………………………QUERIES……………………………………
select distinct(c.ssn),c.name,c.address,c.phno,c.age,a.accno
from customers c,accounts a,loans l
where c.ssn=a.ssn and a.ssn=l.ssn and acctype='Joint';
SSN
NAME
ADDRESS
PHNO
AGE ACCNO
---------- --------------- -------------------- ----------
---------- ----------
S03
Kiran
Chennai
2480417
24 104
S09
Shankar
Belgaum
29545
21 109
2)
select b.branch_id,b.bank_id,b.address,b.phno
from branches b
where b.branch_id in (select l.branch_id from loans l
where amount in (select max(amount) from loans));
BRANCH_ID
BANK_ID
ADDRESS
PHNO
----------
----------
--------------------
----------
SB001
IN001
Bangalore
9923490782
3)
select a.accno,a.ssn,a.branch_id,b.name,a.balance
from accounts a,branches br,banks b
where b.bank_id=br.bank_id and
br.branch_id=a.branch_id and
a.acctype='Savings' and b.name='SBI' and
br.address like '%Bangalore%';
ACCNO
SSN BRANCH_ID
NAME
BALANCE
---------- ---------- ---------- --------------- ----------
100
S01
SB001
SBI
5000
100
S02
SB001
SBI
2000
4)
select b.name,br.name,sum(l.amount) as total_loan
from banks b,branches br,loans l
where b.bank_id=br.bank_id and
br.branch_id=l.branch_id
group by b.name,br.name;
NAME
NAME
TOTAL_LOAN
--------------- ---------------
----------
SBI
SB
278000
SBI
UN
10000
Dena
DE
75000
Yes Bank
YE
000
Corporation
CO
35000
5)
select distinct c.ssn,c.name
from customers c,accounts a,branches br
where c.ssn=a.ssn and
a.branch_id=br.branch_id and
br.address ='&address';
SSN NAME
---------- ---------------
S01 Vaibhav
S02 Vivek
S03 Kiran
S06 Dhanraj
No comments:
Post a Comment