SQL> create table BRANCH(branch_name varchar(10),branch_city varchar(10),assets number(7,2),primary key(branch_name));
Table created.
SQL> create table CUSTOMER2(customer_name varchar(10),customer_street varchar(20),customer_city varchar(10),primary key(customer_name));
Table created.
SQL> create table DEPOSITOR1(customer_name varchar(10)references customer2(customer_name),acc_no number(6)on delete set null,primary key(acc_no));
Table created.
SQL> create table ACCOUNT2(acc_no number(6)references depositor1(acc_no),branch_name varchar(10) references BRANCH(branch_name),balance number(7,2));
Table created.
SQL> create table LOAN(loan_number number(6),branch_name varchar(10) references BRANCH(branch_name),amount number(7,2),primary key(loan_number));
Table created.
SQL> create table BORROWER1(customer_name varchar(10) references customer2(customer_name),loan_number number(6) references LOAN(loan_number));
Table created.
SQL> insert into branch values('SBI','Dehli',3000);
1 row created.
SQL> insert into branch values('SBM','Mysore',5000);
1 row created.
SQL> insert into branch values('CYNDICATE','Mandya',2500);
1 row created.
SQL> insert into branch values('CANARA','Mangalore',10000);
1 row created.
SQL> insert into branch values('ICICI','Mangalore',7500);
1 row created.
SQL> insert into account2 values('100','SBI',10000);
1 row created.
SQL> insert into account2 values('200','SBM',20000);
1 row created.
SQL> insert into account2 values('300','CYNDICATE',50000);
1 row created.
SQL> insert into account2 values('400','CANARA',20000);
1 row created.
SQL> insert into account2 values('500','ICICI',50000);
1 row created.
SQL> insert into depositor1 values('Mohammed',100);
1 row created.
SQL> insert into depositor1 values('Punith',200);
1 row created.
SQL> insert into depositor1 values('Dumini',300);
1 row created.
SQL> insert into deposito1r values('Paul',400);
1 row created.
SQL> insert into depositor1 values('Sachin',500);
1 row created.
SQL> insert into customer2 values('Mohammed','V.V.Nagar','Delhi');
1 row created.
SQL> insert into customer2 values('Punith','V.Puram','Mandya');
1 row created.
SQL> insert into customer2 values('Dumini','Yalahanka','Bangalore');
1 row created.
SQL> insert into customer2 values('Paul','R.T.Nagar','Bangalore');
1 row created.
SQL> insert into customer2 values('Sachin','Newtown','Bangalore');
1 row created.
SQL> insert into loan values(1000,'SBI',10000);
1 row created.
SQL>insert into loan values(2000,'SBM',20000);
1 row created.
SQL> insert into loan values(3000,'CYNDICATE',25000);
1 row created.
SQL> insert into loan values(4000,'CANARA',36000);
1 row created.
SQL> insert into loan values(5000,'ICICI',60000);
1 row created.
SQL> insert into borrower1 values('Mohammed',1000);
1 row created.
SQL> insert into borrower1 values('Punith',2000);
1 row created.
SQL> insert into borrowe1 values('Dumini',3000);
1 row created.
SQL> insert into borrower1 values('Paul',4000);
1 row created.
SQL> insert into borrower1 values('Sachin',5000);
1 row created.
SQL> select *from branch;
BRANCH_NAM BRANCH_CIT ASSETS
------
SBI Dehli 3000
SBM Bangalore 5000
CYNDICATE Mandya 2500
CANARA Mangalore 10000
ICICI Mangalore 7500
SQL> select *from depositor1;
CUSTOMER_N ACC_NO
------
Ram 700
Ram 800
Punith 900
Mohammed 100
Punith 200
Dumini 300
Paul 400
Sachin 500
Punith 600
9 rows selected.
SQL> select *from account2;
ACC_NO BRANCH_NAM BALANCE
------
700 CANARA 15000
800 ICICI 46112
400 ICICI 45000
900 ICICI 56215
100 SBI 10000
200 SBM 20000
300 CYNDICATE 50000
500 SBI 12000
600 SBM 12000
9 rows selected.
SQL> select *from customer2;
CUSTOMER_N CUSTOMER_STREET CUSTOMER_C
------
Ram R.T.Nagar Mangalore
Manu XXX YYY
Mohammed V.V.nagar Dehli
Punith V.Puram Mandya
Dumini Yalahanka Bangalore
Paul R.T.Nagar Bangalore
Sachin Newtown Bangalore
7 rows selected.
6 rows selected.
SQL> select *from loan;
LOAN_NUMBER BRANCH_NAM AMOUNT
------
1000 SBI 10000
2000 SBM 20000
3000 CYNDICATE 25000
4000 CANARA 36000
5000 ICICI 60000
SQL> select *from borrower1;
CUSTOMER_N LOAN_NUMBER
------
Mohammed 1000
Punith 2000
Dumini 3000
Paul 4000
Sachin 5000
SQL> select distinct(customer_name) from depositor1 where acc_no in(select acc_no from account2 where branch_name in(select branch_name from branch where branch_city='Mangalore'))
CUSTOMER_N
------
Punith
Ram
Paul
SQL> select distint(customer_name) from depositor1 where acc_no in(select acc_no from account2 where branch_name in(select branch_name from branch where branch_name=’SBM’)) group by customer_name having count(customer_name)>=2);
CUSTOMER_N
------
Punith
SQL> delete from account2 where branch_name in(select branch_name from branch where branch_city=’Bangalore’);
2 rows are deleted.
SQL>select *from account2;
ACC_NO BRANCH_NAM BALANCE
------
700 CANARA 15000
800 ICICI 46112
400 ICICI 45000
900 ICICI 56215
100 SBI 1000
300 CYNDICATE 50000
500 SBI 12000
7 rows selected.
SQL> select *from depositor1;
CUSTOMER_N ACC_NO
------
Ram 700
Ram 800
Punith 900
Mohammed 100
Punith
Dumini 300
Paul 400
Sachin 500
Punith
9 rows selected