Skip to main content

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

Popular posts from this blog

AGRICULTURAL SCIENTIST RECRUITMENT BOARD

Agricultural Research Service

National
Eligibility Test/ Senior Research Fellowship Examination 

The Agricultural Scientists Recruitment Board (ASRB)
 holds a Competitive Examination for recruiting Scientists of the ARS in the pay scale of Rs. 8,000-13,500 in the ICAR Institutes, combined with National Eligibility Test (NET) for recruitment of Lecturers and Assistant Professors by the State Agricultural Universities (SAUS) and for award of ICAR Senior Research Fellowships.

The selected candidates for Agricultural Research Service must serve in the institutes to which they are posted until they find appointment for higher positions through selection at other institutes.

(i) Candidates successful in ARS are appointed as Scientists in the Indian Council of Agricultural Research in the pay scale of Rs. 8,000-13,500.

(ii) Candidates clearing the National Eligibility Test are recommended to various State Agricultural Universities who will consider them for appointment as Lecturers or Assistant Pro…

CIVIL SERVICES' (I.A.S.) EXAMINATION

The Union Public Service Commission (U.P.S.C.) conducts Civil Services' Examination once a year in two stages. The Preliminary Examination (Objective Type) for selection of candidates for the Main Examination is held in the month of May.

The Civil Services Main Examination
 is held in the months of October/November. Blank application forms and other particulars are published in the Employment News, generally in the month of December.

The last date for the submission of applications to the Secretary, Union Public Service Commission, Dholpur House, Shahjahan Road, NewDelhi-11001 1 is usually the last week of January of the year of examination.

The Combined Civil Services Examination is conducted for Recruitment to the following Services/Posts:

1. Indian Administrative Service.
2. Indian Foreign Service.
3. Indian Police Service.
4. Indian P & T Accounts & Finance Service, Group 'A'.
5. Indian Audit and Accounts Service, Group 'A'.
6. Indian Customs and Central Excise S…

HSC Exam Time Table 2013 Arts, Commerce, Science

HSC Exam Time Table 2013:- Date: 21 Feb 2013 (Thursday)

Paper: (Marathi / Gujarati / Kannada / Sindi / Malyalam / Tamil / Telugu / Punjabi / Bengali
Time: 11.00 am to 2.00 pm

Date: 22 Feb 2013 (Friday)

Paper: Hindi
Time: 11.00 am to 2.00 pm
Paper: German / Andhramagadhi / Persian
Time: 3.00 am to 6.00 pm

Date: 23 Feb 2013 (Saturday)

Paper: English
Time: 11.00 am to 2.00 pm

Date: 25 Feb 2013 (Monday)

Paper: Secretarial Practice
Time: 11.00 am to 2.00 pm
Paper: Physics
Time: 11.00 am to 2.00 pm
Paper: Political Science
Time: 3.00 am to 6.00 pm

Date: 27 Feb 2013 (Wednesday)

Paper: Book Keeping & Accoutancy
Time: 11.00 am to 2.00 pm
Paper: Chemistry (will Be held on 26 March)
Time: 11.00 am to 2.00 pm
Paper: Philosophy
Time: 3.00 am to 6.00 pm

Date: 1 March 2013 (Friday)

Paper: Mathematics & Statistics Paper (Revised)
Time: 11.00 am to 2.00 pm
Paper: Mathematics & Statistics - I (Commerce)
Time: 11.00 am to 1.00 pm
Paper: Sociology
Time: 3.00 am to 6.00 pm

Date: 2 March 2013 (Saturd…