Create tables as per following definitions.
Deposit3 :
Actno
Cname
Brname
Amount
Date
SQL> create table deposit3
(actno number(5),cname varchar(20),brname varchar2(20),amount number(10,2),dt date);
Table created.
Branch3 :
Brid
Brname
City
Ins_date
Update_date
SQL> create table branch3
(brid varchar2(20), brname varchar2(20),city varchar2(20),insdate date,update_date date);
Table created.
Customer3 :
Custid
Cname
City
ins_date
update_date
SQL> create table customer3
(custid varchar2(20), cname varchar2(20),city varchar2(20),insdate date,update_date date);
Table created.
Borrow3 :
loanno
custname
brname
amount
ins_date
SQL> create table borrow3
(loanno number(10), custname varchar2(20),brname varchar2(20),amount number(10,2),insdate date);
Table created.
Describe all tables.
SQL> desc deposit3
Name Null? Type
-------------------------------------------------------------------
ACTNO NUMBER(5)
CNAME VARCHAR2(20)
BRNAME VARCHAR2(20)
AMOUNT NUMBER(10,2)
DT DATE
SQL> desc branch3
Name Null? Type
-------------------------------------------------------------------
BRID VARCHAR2(20)
BRNAME VARCHAR2(20)
CITY VARCHAR2(20)
INSDATE DATE
UPDATE_DATE DATE
SQL> desc customer3;
Name Null? Type
------------------------------------------------------------------
CUSTID VARCHAR2(20)
CNAME VARCHAR2(20)
CITY VARCHAR2(20)
INSDATE DATE
UPDATE_DATE DATE
SQL> desc borrow3;
Name Null? Type
-------------------------------------------------------------------------------
LOANNO NUMBER(10)
CUSTNAME VARCHAR2(20)
BRNAME VARCHAR2(20)
AMOUNT NUMBER(10,2)
INSDATE DATE
List all data from all four tables.
SQL> select * from deposit3;
ACTNO CNAME BRNAME AMOUNT DT
------------------------------------------------------------------------------------------------------------
100 Anil VRCE 1000 01-MAR-95
101 Sunil AJNI 5000 04-JAN-96
102 Mehul Karolbagh 3500 17-APR-95
103 Madhuri Chandeni 1200 13-DEC-95
104 Pramod MCAD 3000 23-MAR-96
105 Sandip Andheri 2000 27-MAR-96
106 Shivani Virar 1000 31-MAR-96
107 Kranti Nehru Plane 5000 05-SEP-95
108 Naren Powai 7000 10-AUG-05
9 rows selected.
SQL> select * from branch3;
BRID BRNAME CITY INSDATE UPDATE_DA
---------------------------------------------------------------------------------------------------
A101 VRCE Nagpur 22-JAN-12 24-JAN-12
A102 AJNI Nagpur 22-JAN-12 24-JAN-12
A103 Karolbagh Delhi 22-JAN-12 24-JAN-12
A104 Chandani Delhi 22-JAN-12 24-JAN-12
A105 Dharampeth Nagpur 22-JAN-12 24-JAN-12
A106 MG Road Bangalore 22-JAN-12 24-JAN-12
A107 Andheri Mumbai 22-JAN-12 24-JAN-12
A108 Virar Mumbai 22-JAN-12 24-JAN-12
A109 Nehruplane Delhi 22-JAN-12 24-JAN-12
A110 Powai Mumbai 22-JAN-12 24-JAN-12
10 rows selected.
SQL> select * from customer3;
CUSTID CNAME CITY INSDATE UPDATE_DA
-------------------------------------------------------------------------------------------------------------
C201 Anil Calcutta 14-JAN-12
C202 Sunil Delhi 14-JAN-12
C203 Mehul Baroda 14-JAN-12 24-JAN-12
C204 Mandar Patna 14-JAN-12
C205 Madhuri Nagpur 14-JAN-12
C206 Pramod Nagpur 14-JAN-12
C207 Sandip Surat 14-JAN-12 24-JAN-12
C208 Shivani Mumbai 14-JAN-12
C209 Kranti Mumbai 14-JAN-12
C210 Naren Mumbai 14-JAN-12 24-JAN-12
10 rows selected.
SQL> select * from borrow3;
LOANNO CUSTNAME BRNAME AMOUNT INSDATE
--------------------------------------------------------------------------------------------------------
201 Anil VRCE 1000 24-JAN-12
206 Mehul AJNI 5000 24-JAN-12
311 Sunil Dharampeth 3000 24-JAN-12
321 Madhuri Andheri 2000 24-JAN-12
375 Pramod Virar 8000 24-JAN-12
481 Kranti Nehruplane 3000 24-JAN-12
Give names of depositors having amount greater than 4000.
SQL> select cname from deposit3 where amount>4000;
CNAME
--------------------
Sunil
Kranti
Naren
Give names of customers living in the city ="Nagpur"
SQL>select cname from customer3 where city = 'Nagpur';
CNAME
--------------------
Madhuri
Pramod
Give names of customer who opened account after date '1-12-1995 '
SQL>select cname from deposit3 where dt>'1-dec-95';
CNAME
--------------------
Sunil
Madhuri
Pramod
Sandip
Shivani
Naren
6 rows selected.
Give amount no & deposite amount of customer having account opened between dates '1-06-1995 ' and '1-12-1995 '
SQL>select actno,cname,amount from deposit3
where dt Between '1-jun-95' and '1-dec-95';
ACTNO CNAME AMOUNT
---------------------------------------------------
107 Kranti 5000
Give details of loan whose custname starts with 'N' or having 'a' in between words
SQL> select * from borrow3 where custname like 'N%' or custname like '%a%' ;
LOANNO CUSTNAME BRNAME AMOUNT INSDATE
----------------------------------------------------------------------------------------------------------
321 Madhuri Andheri 2000 24-JAN-12
375 Pramod Virar 8000 24-JAN-12
481 Kranti Nehruplane 3000 24-JAN-12
Use Joins.
Give name of customers from depositor having city like 'Mumbai' and branch city 'Delhi '
SQL> select d.cname from deposit3 d, branch3 b, customer3 c
where d.bname=b.brname
and b.city='DELHI'
and d.cname=c.cname
and c.city='MUMBAI';
CNAME
--------------------
KRANTI
Give name of customers who are borrowers as well as depositors and having living city like 'Nagpur '
SQL> select d.cname
from deposit3 d, borrow3 b, customer3 c
where d.cname=b.custname
and d.cname=c.cname
and c.city='NAGPUR';
CNAME
--------------------
MADHURI
PRAMOD
Give depositor details and loan details of the customer in the city where 'Pramod' is living.
SQL>select d.*, b.* from customer3 c,deposit3 d,borrow3 b
where c.cname=d.cname and c.cname=b.custname
and city=(select city from customer3 where cname='Pramod');
ACTNO CNAME BRNAME AMT DT LOANNO CUSTNAME BRNAME AMT INSDATE
------------------------------------------------------------------------------------------------------------------------------------
103 Madhuri Chandeni 1200 13-DEC-95 321 Madhuri Andheri 2000 24-JAN-12
104 Pramod MCAD 3000 23-MAR-96 375 Pramod Virar 8000 24-JAN-12
Give names of depositor having the same branch city as that of 'Sunil' and having same living city as that of 'Anil'
SQL> select d.cname
from deposit3 d, customer3 c, branch3 br
where br.city in (select br.city from branch3 where d.cname='SUNIL')
and c.city in (select c.city from customer3 where c.cname='ANIL')
and d.cname=c.cname and d.bname=br.brname;
no rows selected
Give branch city of 'Sunil' or branch city of 'Anil'
SQL> select distinct b.city from deposit3 d, branch3 b
where d.bname=b.brname
and b.city in
(select b.city from branch3 where d.cname='SUNIL' or d.cname='ANIL');
CITY
--------------------
NAGPUR
Set operators(minus, Union, Intersect, In, Any, All).
List all the customers who are depositors but not borrowers
SQL> select cname from deposit3
minus
(select cname from borrow3);
CNAME
--------------
Naren
Sandeep
Shivani
List all the customers who are both depositors and borrowers
SQL> select distinct cname from deposit3
intersect
select custname from borrow3;
CNAME
--------------------
ANIL
KRANTI
MADHURI
MEHUL
PRAMOD
SUNIL
6 rows selected.
List the branch cities of 'Anil' and 'Sunil'
SQL> select b.city from branch3 b, deposit3 d
where b.brname=d.bname and d.cname='ANIL'
union
select b.city from branch3 b, deposit3 d
where b.brname=d.bname and d.cname='SUNIL';
CITY
--------------------
NAGPUR
List all the depositors living in the city Nagpur and having branches in city 'Delhi'
SQL> select d.cname from deposit3 d, customer3 c
where d.cname=c.cname and c.city='NAGPUR'
intersect
select d.cname from deposit3 d,branch3 b
where d.bname=b.brname and b.city='DELHI';
CNAME
--------------------
MADHURI
List the cities which are either branch city of 'Anil' or living city of 'Sunil'
SQL>select b.city from branch3 b, deposit3 d
where b.brname=d.bname and d.cname='ANIL'
union
select city from customer3 where cname='SUNIL’;
CITY
--------------------
DELHI
NAGPUR
List the customers who are both borrowers and depositors and having same branch city as 'Anil'
SQL>select custname from borrow3
intersect
select d.cname from deposit3 d,branch3 b
where d.bname=b.brname and b.city in (select city from branch3, deposit3
where branch3.brname=deposit3.bname and deposit3.cname='ANIL');
CUSTNAME
--------------------
ANIL
SUNIL
Aggregate Functions.
List total deposit, max deposit of customers living in city 'Nagpur'.
SQL> select sum(amount), max(amount)
from deposit3,customer3
where customer3.city='Nagpur’;
MAX(AMOUNT) SUM(AMOUNT)
---------------------------------------------------------
7000 57400
Give branchwise loan of customers living in 'Nagpur'.
SQL> select borrow3.brname, sum(amount)
from borrow3, branch3
where branch3.city='nagpur' and borrow3.brname=branch3.brname
group by(borrow3.brname);
BRNAME SUM(AMOUNT)
----------------------------------------------
AJNI 5000
DHARAMPETH 3000
VRCE 1000
Give no of customers who are depositors as well as borrowers.
SQL> select count(cname) from deposit3, borrow3
where deposit3.cname=borrow3.custname;
COUNT(CNAME)
------------------------
6
List the name of branch having highest no of depositers (use group by, having, ALL)
SQL> select deposit3.brname from deposit3
group by deposit3.brname
having count(deposit3.cname)>=all(select count(deposit3.cname) from deposit3
group by deposit3.brname);
BRNAME
--------------------
AJNI
Andheri
Chandni
Karoubangh
MGRoad
Nehru place
Powai
VRCE
Virar
9 rows selected
Give names of cust in ‘Powai’ branch having more deposit than any other customer in ‘VRCE’ branch. (use group by, having, Any)
SQL> select c1.cname,d1.amount
from customer3 c1,deposit3 d1,branch3 b1
where b1.brname='Powai' and b1.brname=d1.brname and d1.cname=c1.cname
and d1.amount > (select max(amount)
from deposit3
where brname='VRCE');
CNAME AMOUNT
----------------------------------------
Naren 7000
Give names of branches having the number of depositers more than the number of borrowers.
SQL> select d1.brname
from deposit3 d1
group by d1.brname
having count(d1.cname) > all (select count(b1.cname)
from borrow3 b1
where b1.brname=d1.brname
group by b1.brname);
BRNAME
--------------------
Andheri
Chandni
Karoubangh
MGRoad
Nehru place
Powai
Virar
7 rows selected.
Give names of customers living in the city where max number of depositors are located.
SQL>select cname from customer3
where city = (select c.city from deposit3 d,customer3 c
where upper(c.cname) = upper(d.cname)
group by c.city having count(c.city) >= ALL (select count(d.cname)
from deposit3 d,customer3 c
where upper(c.cname) = upper(d.cname)
group by (c.city)));
CNAME
----------------
Shivani
Kranti
Naren