Filled Under: ,

Advanced Database Techniques Practical - Experiment No.1

PRACTICAL NO. 1

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