Docstoc
EXCLUSIVE OFFER FOR DOCSTOC USERS
Try the all-new QuickBooks Online for FREE.  No credit card required.

DBMS Lab

Document Sample
DBMS Lab Powered By Docstoc
					                                                                                                 1


   1. Consider the Insurance database given below. The primary keys are underlined and
   the data types are specified:

   PERSON (driver-id:string,name:string,address:string)
   CAR (Regno:string,model:string,year:int)
   ACCIDENT (report-number:int,date:date,location:string)
   OWNS (driver-id:string,regno:string)
   PARTICIPATED (driver-id:string,regno:string,report-number:int,damage-amount:int)

       i) create the above tables by properly specifying the primary keys and the foreign keys
       ii) Enter atleast five tuples for each relation
       iii) Demonstrate how you
               a. Update the damage amount for the car with a specific regno in accident with
                   report number 12 to 25000
               b. Add a new accident to the database
       iv) Find the total number of people who owned cars that were involved in accidents in
            2006.
       v) Find the number of accidents in which cars belonging to a specific model were
            involved.
       vi) Generation of suitable reports
       vii) Create suitable front end for querying and display the results

create table person
(
driverid varchar(5) primary key,
name varchar(30),
address varchar(50)
);

create table car
(
regno varchar(5) primary key,
model varchar(25),
year int
);

create table accident
(
reportno int primary key,
date1 date,
location varchar(30)
);

create table owns
(
driverid references person,
regno references car
);
                                                              2



create table participated
(
driverid references person,
regno references car,
reportno references accident,
damages int
);

insert into person values (1,'abc','hkbkce,nagawara');
insert into person values (2,'bcd','atria,yelahanka');
insert into person values (3,'ade','ramahiah,near iisc');
insert into person values (4,'cfg','mvjce,whitefield');
insert into person values (5,'rte','pesit,somewhere');

insert into car values ('a1','merc',2007);
insert into car values ('a2','porsche',2005);
insert into car values ('b2','rolls royce',2006);
insert into car values ('b4','ferrari',1999);
insert into car values ('c1','bentley',2000);
insert into car values ('d2','maybach',2007);

insert into accident values (1,'1-jan-2006','domlur');
insert into accident values (2,'28-mar-2006','m.g.road');
insert into accident values (3,'2-dec-2006','r.t.nagar');
insert into accident values (4,'5-jan-2007','yelahanka');
insert into accident values (5,'26-jan-2007','koramangla');
insert into accident values (6,'4-feb-2007','majestic');

insert into owns values (1,'a1');
insert into owns values (2,'a2');
insert into owns values (2,'c1');
insert into owns values (3,'b2');
insert into owns values (4,'b4');
insert into owns values (5,'d2');

insert into participated values (1,'a1',1,500);
insert into participated values (2,'a2',1,2000);
insert into participated values (3,'b2',1,1000);
insert into participated values (1,'a1',2,1500);
insert into participated values (4,'c1',2,800);
insert into participated values (5,'d2',3,750);
insert into participated values (2,'c1',3,600);
insert into participated values (1,'a1',3,200);
insert into participated values (5,'d2',4,1000);
insert into participated values (2,'a2',5,1200);
insert into participated values (3,'b2',6,10000);
insert into participated values (2,'a1',6,5000);
                                                                  3


select * from person;
DRIVERID NAME                        ADDRESS
---------------- ----------        ----------------------
       1           abc              hkbkce,nagawara
       2           bcd             atria,yelahanka
       3           ade             ramahiah,near iisc
       4           cfg              mvjce,whitefield
       5           rte              pesit,somewhere

select * from car;
REGNO MODEL                  YEAR
----------- ---------------- ------------
    a1          merc           2007
    a2          porsche        2005
    b2         rolls royce     2006
    b4         ferrari         1999
    c1         bentley         2000
    d2         maybach         2007

select * from accident;
REPORTNO            DATE1             LOCATION
----------------- --------------   -----------------------
        1         01-JAN-06            domlur
        2         28-MAR-06            m.g.road
        3        02-DEC-06             r.t.nagar
        4        05-JAN-07             yelahanka
        5        26-JAN-07             koramangla
        6        04-FEB-07             majestic

select * from owns;
DRIVERID REGNO
-------------- -----------
     1           a1
     2           a2
     2           c1
     3           b2
     4           b4
     5           d2

select * from participated;
DRIVERID REGNO REPORTNO DAMAGES
---------------- ----------- ----------------- ----------------
      1              a1             1               500
      2              a2             1               2000
      3              b2              1             1000
      1              a1             2               1500
      4              c1             2               800
      5             d2              3              750
      2             c1              3               600
      1             a1              3               200
                                                                                      4


     5              d2               4               1000
     2              a2               5               1200
     3              b2                6              10000
     2              a1               6               5000

iii.a Update the damage amount for the car with a specific regno in accident with report
number 12 to 25000

update participated set damages=25000 where reportno=4 and regno='d2';
1 row updated

select * from participated;
DRIVERID REGNO REPORTNO DAMAGES
---------------- ----------- ----------------- ----------------
      1              a1             1               500
      2              a2             1               2000
      3              b2              1             1000
      1              a1             2               1500
      4              c1             2               800
      5             d2              3              750
      2             c1              3               600
      1             a1              3               200
      5             d2              4              25000
      2             a2              5              1200
      3             b2               6             10000
      2             a1              6              5000


iii.b Add a new accident to the database

insert into accident values (7,'16-mar-2007','nagawara');
1 row updated

insert into participated values (2,'a1',7,15000);
1 row updated

select * from accident;
REPORTNO            DATE1             LOCATION
----------------- --------------   -----------------------
        1         01-JAN-06            domlur
        2         28-MAR-06            m.g.road
        3        02-DEC-06             r.t.nagar
        4        05-JAN-07             yelahanka
        5        26-JAN-07             koramangla
        6        04-FEB-07             majestic
        7        16-MAR-07             nagawara
                                                                                             5


select * from participated;
DRIVERID REGNO REPORTNO DAMAGES
---------------- ----------- ----------------- ----------------
      1              a1             1               500
      2              a2             1               2000
      3              b2              1             1000
      1              a1             2               1500
      4              c1             2               800
      5             d2              3              750
      2             c1              3               600
      1             a1              3               200
      5             d2              4              25000
      2             a2              5              1200
      3             b2               6             10000
      2             a1              6              5000
      2             a1              7             15000


iv. Find the total number of people who owned cars that were involved in accidents in 2006
select count(distinct p.driverid) from accident a,owns o,participated p
where date1 like '%06' and a.reportno=p.reportno
and p.regno=o.regno and o.driverid=p.driverid;

COUNT(DISTINCTP.DRIVERID)
---------------------------------------------
                  4
v. Find the number of accidents in which cars belonging to a specific model were involved.
select count(*) from car c,participated p where model='porsche' and c.regno=p.regno;

COUNT(*)
--------------
      2
                                                                                             6


2. Consider the following relations for an order processing database application in a
company.

CUSTOMER (Cust #: int, Cname: string, City: string)
ORDER (Order #: int, Odate: date, Cust #: int, Ord-Amt: int)
ORDER-ITEM (Order #: int, Item #: int, qty: int)
ITEM (Item #: int, Unit Price: int)
SHIPMENT (Order #: int, Warehouse #: int, Ship-Date: date)
WAREHOUSE (Warehouse #: int, City: string)

   i)     Create the above tables by properly specifying the primary keys and the foreign keys.
   ii)    Enter at least five tuples for each relation.
   iii)   Produce a listing: CUSTNAME, NO_OF_ORDERS, AVG_ORDER_AMT, where
          the middle column is the total number of orders by the customer and the last column
          is the average order amount for that customer.
   iv)    List the Order# for the orders that were shipped from all the warehouses that the
          company has in a specific city.
   v)     Demonstrate how you delete Item# 10 from the ITEM table and make that field null
          in the ORDER-ITEM table.
   vi)    Generation of suitable reports.
   vii)   Create a suitable front end for querying and displaying the results.

   create table customer
    (
   cno int primary key,
   cname varchar(10),
   city varchar(15)
   );

   create table order1
   (
   ono int primary key,
   odate date,
   cno references customer,
   oamt int
   );

   create table item
   (
   ino int primary key,
   uprice int
   );

   create table oitem
   (
   ono references order1,
   ino references item on delete set null,
   qty int
   );
                                                      7


create table warehouse
(
wno int primary key,
city varchar(15)
);

create table shipment
(
ono references order1,
wno references warehouse,
sdate date
);

insert into customer values(1,'abc','bangalore');
insert into customer values(2,'cde','mysore');
insert into customer values(3,'def','chennai');
insert into customer values(4,'efg','mumbai');
insert into customer values(5,'adf','kolkata');

insert into order1 values(1,'1-jan-2006',1,20000);
insert into order1 values(2,'26-mar-2006',2,10000);
insert into order1 values(3,'12-jun-2006',1,5000);
insert into order1 values(4,'15-sep-2006',3,9000);
insert into order1 values(5,'5-jan-2007',4,2500);
insert into order1 values(6,'10-jan-2007',4,2400);
insert into order1 values(7,'3-mar-2007',5,3500);

insert into item values(1,500);
insert into item values(2,300);
insert into item values(3,2500);
insert into item values(4,800);
insert into item values(5,700);

insert into oitem values(1,1,40);
insert into oitem values(2,1,20);
insert into oitem values(3,3,2);
insert into oitem values(5,3,1);
insert into oitem values(4,2,30);
insert into oitem values(6,4,3);
insert into oitem values(7,5,5);

insert into warehouse values(100,'bangalore');
insert into warehouse values(101,'chennai');
insert into warehouse values(102,'mumbai');
insert into warehouse values(103,'kolkata');
insert into warehouse values(104,'mysore');

insert into shipment values(1,100,'3-jan-2006');
insert into shipment values(2,100,'28-mar-2006');
insert into shipment values(3,101,'13-jun-2006');
                                                    8


insert into shipment values(4,102,'18-sep-2006');
insert into shipment values(5,103,'11-jan-2007');
insert into shipment values(6,104,'13-jan-2007');
insert into shipment values(7,103,'3-mar-2007');


select * from customer;
 CNO CNAME CITY
---------- ---------- ---------------
    1       abc         bangalore
    2       cde         mysore
    3       def         chennai
    4       efg         mumbai
    5       adf         kolkata


select * from order1;
ONO ODATE                    CNO         OAMT
---------- ---------------- ---------- ----------
       1 01-JAN-06               1     20000
       2 26-MAR-06               2     10000
       3 12-JUN-06               1      5000
       4 15-SEP-06               3      9000
       5 05-JAN-07               4      2500
       6 10-JAN-07               4      2400
       7 03-MAR-07               5      3500


select * from item;
  INO UPRICE
---------- ----------
       1       500
       2       300
       3      2500
       4       800
       5       700


select * from oitem;
  ONO        INO       QTY
---------- ---------- ----------
       1        1        40
       2        1        20
       3        3         2
       5        3         1
       4        2        30
       6        4         3
       7        5         5
                                                                                    9


    select * from warehouse;
      WNO CITY
    ---------- ---------------
       100      bangalore
       101      chennai
       102      mumbai
       103      kolkata
       104 mysore


    select * from shipment;
     ONO WNO SDATE
    ---------- -------- ------------
         1      100     03-JAN-06
         2      100     28-MAR-06
         3      101     13-JUN-06
         4      102     18-SEP-06
         5      103     11-JAN-07
         6      104     13-JAN-07
         7      103     03-MAR-07


iii. Produce a listing: CUSTNAME, NO_OF_ORDERS, AVG_ORDER_AMT, where the
middle column is the total number of orders by the customer and the last column is the
average order amount for that customer.

select c.cname,count(q.qty),avg(oamt)
from customer c, order1 o, oitem q
where q.ono=o.ono and c.cno=o.cno
group by cname;

CNAME COUNT(Q.QTY)                      AVG(OAMT)
------------ ----------------------     --------------------
  abc                2                      12500
  adf                1                      3500
  cde                1                      10000
  def                1                      9000
  efg                2                      2450

iv. List the Order# for the orders that were shipped from all the warehouses that the
company has in a specific city.

select w.city, s.wno, s.ono
from warehouse w, shipment s
where w.wno=s.wno and w.city='mumbai';

CITY            WNO ONO
--------------- ---------- ----------
mumbai           102           4
                                                                                   10


v. Demonstrate how you delete Item# 10 from the ITEM table and make that field null in
the ORDER-ITEM table
delete from item where ino=5;
1 row deleted

select * from item;
     INO UPRICE
---------- ----------
       1       500
       2       300
       3      2500
       4       800
                                                                                                     11


3. Consider the following database of student enrollement in courses and books adopted for
each course .

STUDENT (regno :string , name : string , major : string , bdate : int)
COURSE (course# : int , cname : string , dept : string)
ENROLL ( regno : string , course#: int , sem : int , marks : int )
BOOK_ADAPTION ( course#: int , sem : int , book_isbn :int)
TEXT( book_isbn : int , book-title : string , publisher : string , author : string).

     i) Create the above tables by properly specifying the primary keys and the foreign key .
     ii) Enter atleast five tuples for each relation .
     iii) Demonstrate how you add a new text book to the database and make this book be
          adopted by some department.
     iv) Produce a list of text books( include course # ,book_isbn,book-title) in the alphabetical
          order for courses offered by the cs department that use more than 2 books.
     v) List any department that has all its adopted books published by specific publisher.
     vi) Generation of suitable reports.
     vii) Create suitable front end for querying and display the results

    create table student
    (
    regno varchar(5) primary key,
    name varchar(10),
    major varchar(10),
    bdate date
    );

    create table course
    (
    cno int primary key,
    cname varchar(10),
    dept varchar(10)
    );

    create table enroll
    (
    regno references student,
    cno references course,
    sem int primary key,
    marks int
    );

    create table text
    (
    isbn int primary key,
    title varchar(15),
    publisher varchar(10),
    author varchar(10)
    );
                                                                    12


create table bad
(
cno references course,
sem references enroll,
isbn references text
);

insert into student values('cs42','mikhil','cse','17-dec-1986');
insert into student values('cs48','mujeeb','cse','02-sep-1986');
insert into student values('ec26','pradeep','cse','16-aug-1987');
insert into student values('ee37','majid','ise','28-may-1986');
insert into student values('is48','wajid','ise','28-may-1986');

insert into course values(1,'.net','computer');
insert into course values(2,'j2ee','computer');
insert into course values(3,'mis','infosci');
insert into course values(4,'fs','infosci');
insert into course values(5,'oracle','computer');

insert into enroll values('cs42',1,6,98);
insert into enroll values('cs48',2,3,97);
insert into enroll values('ec26',5,5,50);
insert into enroll values('is48',3,7,90);
insert into enroll values('ee37',4,4,80);

insert into text values(101,'let us c','lpe','fahad');
insert into text values(102,'c++','abc','mujeeb');
insert into text values(103,'oracle','def','othman');
insert into text values(104,'.net','lpe','naushad');
insert into text values(105,'j2ee','pearson','mikhil');

insert into bad values(1,6,101);
insert into bad values(1,6,103);
insert into bad values(1,6,102);
insert into bad values(4,4,104);
insert into bad values(5,7,105);

select * from student;
REGNO NAME             MAJOR BDATE
---------- ---------- ------------- -------------
   cs42     mikhil        cse       17-DEC-86
   cs48     mujeeb        cse       02-SEP-86
   ec26     pradeep       cse       16-AUG-87
   ee37     majid          ise      28-MAY-86
   is48     wajid          ise      28-MAY-86
                                                                                    13


select * from course;
  CNO CNAME DEPT
---------- -------------- ------------
     1         .net        computer
     2         j2ee        computer
     3         mis         infosci
     4         fs          infosci
     5         oracle      computer

select * from enroll;
 REGNO CNO SEM MARKS
------------ ---------- ---------- ------------
    cs42         1          6          98
    cs48         2          3          97
    ec26         5          5          50
    is48         3         7           90
    ee37         4         4           80

select * from text;
  ISBN        TITLE        PUBLISHER AUTHOR
---------- --------------- ------------------ ---------------
   101         let us c           lpe             fahad
   102         c++                abc             mujeeb
   103         oracle             def             othman
   104         .net                lpe            naushad
   105         j2ee               pearson         mikhil

select * from bad;
  CNO        SEM ISBN
---------- ---------- ----------
    1          6        101
    1          6        103
    1          6        102
    4          4        104
    5          7         105

iii.Demonstrate how you add a new text book to the database and make this book be
adopted by some department.
insert into text values(106,'JAVA','pearson','Avril');
insert into bad values(2,7,106);

select * from text;
  ISBN        TITLE        PUBLISHER AUTHOR
---------- --------------- ------------------ ---------------
   101          let us c          lpe             fahad
   102          c++               abc             mujeeb
   103          oracle            def             othman
   104          .net              lpe             naushad
   105          j2ee              pearson         mikhil
   106         JAVA               pearson          Avril
                                                                                         14


select * from bad;
  CNO        SEM ISBN
---------- ---------- ----------
    1          6        101
    1          6        103
    1          6        102
    4          4        104
    5          7         105
    2          7         106

iv. Produce a list of text books( include course # ,book_isbn,book-title) in the
alphabetical order for courses offered by the cs department that use more than 2 books.

SELECT s.cno,t.title,b.isbn
FROM course s,bad b,text t
where s.cno=b.cno
AND b.isbn=t.isbn
AND s.dept='computer'
AND b.cno in (select b.cno
from bad b,course s
where b.cno=s.cno
group by b.cno
having count(*)>2)
order by t.title;

  CNO        TITLE           ISBN
---------- --------------- ----------
    1           c++           102
    1           let us c      101
    1          oracle         103

v. List any department that has all its adopted books published by specific publisher.
select s.dept, t.publisher
from course s, bad b, text t
where s.cno=b.cno
and t.isbn=b.isbn
and publisher='lpe';

   DEPT            PUBLISHER
-------------     --------------------
 computer                lpe
 infosci                 lpe
                                                                                             15


4. Consider the following relations for the details maintained by a book dealer.

AUTHOR (Author-id: int, Name: string, City: string, Country: string)
PUBLISHER (Publisher-id: int, Name: string, City: string, Country: string)
CATALOG (Book-id: int, title: string, author-id: int, Publisher-id: int, Category-id: int, Year:
int, Price: int)
CATEGORY (Category-id: int, Description: string)
ORDER-DETAILS (Order-no : int, Book-id: int, Quantity: int)
    i.   Create the above tables by properly specifying the primary keys and the foreign keys.
   ii.   Enter at least five tuples for each relation.
 iii. Give the details of the authors who have 2 or more books in the catalog and the price of
         the books is greater than the average price of the books in the catalog and the year of
         publication is after 2000.
  iv.    Find the author of the book which has maximum sales.
   v.    Demonstrate how you increase the price of books published by a specific publisher by
         10%.
  vi.    Generation of suitable reports.
 vii.    Create a suitable front end for querying and displaying the results.

   create table author
   (
   aid int primary key,
   name varchar(15),
   city varchar(15),
   country varchar(15)
   );

   create table publisher
   (
   pid int primary key,
   name varchar(15) ,
   city varchar(15) ,
   country varchar(15)
   );

   create table category
   (
   cid int primary key,
   description varchar(15)
   );

   create table catalog
   (
   bid int primary key,
   title varchar(15),
   aid references author,
   pid references publisher,
   cid references category,
   year int,
   price int );
                                                               16


create table odetails
(
ono int,
bid references catalog,
qty int
);
insert into author values (1,'mik','bang','ind');
insert into author values (2,'muj','bang','ind');
insert into author values (3,'prad','tri','aus');
insert into author values (4,'maj','anan','ame');
insert into author values (5,'waj','anan','euro');
insert into publisher values (101,'pearson','bang','ind');
insert into publisher values (102,'tata','mumbai','aus');
insert into publisher values (103,'sapna','che','euro');
insert into publisher values (104,'abc','tri','ame');
insert into publisher values (105,'xyz','anan','ind');
insert into category values (1001,'computer');
insert into category values (1002,'electronics');
insert into category values (1003,'maths');
insert into category values (1004,'science');
insert into category values (1005,'electrical');
insert into catalog values (111,'lib1',1,101,1001,2002,500);
insert into catalog values (112,'lib2',2,102,1002,2000,800);
insert into catalog values (113,'lib3',3,103,1003,2003,200);
insert into catalog values (114,'lib4',4,104,1001,2006,350);
insert into catalog values (115,'lib5',5,105,1004,2007,100);
insert into catalog values (116,'lib6',2,103,1005,2007,600);
insert into catalog values (117,'lib7',2,105,1002,2007,450);
insert into catalog values (118,'lib8',1,101,1001,2002,500);

insert into odetails values (1,111,2);
insert into odetails values (2,112,3);
insert into odetails values (3,111,5);
insert into odetails values (4,113,1);
insert into odetails values (5,114,2);
insert into odetails values (6,115,1);
insert into odetails values (1,114,2);
insert into odetails values (2,113,2);

select * from author;
  AID       NAME             CITY         COUNTRY
---------- --------------- --------------- ---------------
    1       mik              bang              ind
    2       muj              bang              ind
    3       prad             tri              aus
    4       maj              anan             ame
    5       waj              anan             euro
                                                                                     17


select * from publisher;
 PID         NAME            CITY        COUNTRY
---------- --------------- --------------- ---------------
  101         pearson        bang               ind
  102         tata           mumbai            aus
  103         sapna          che               euro
  104         abc            tri               ame
  105         xyz            anan              ind

select * from category;
 CID       DESCRIPTION
---------- ---------------------
  1001         computer
  1002         electronics
  1003         maths
  1004         science
  1005         electrical

select * from catalog;
BID TITLE             AID       PID CID YEAR PRICE
------- ------------ --------- -------- -------- ---------- ----------
111      lib1           1       101 1001 2002                 500
112 lib2                2       102 1002 2000                 800
113 lib3                3       103 1003 2003                 200
114 lib4                4       104 1001 2006                 350
115 lib5                5       105 1004 2007                 100
116 lib6                2       103 1005 2007                 600
117 lib7                2       105 1002 2007                 450
118 lib8                1       101 1001 2002                 500

select * from odetails;
 ONO         BID        QTY
---------- ---------- ----------
     1      111          2
     2      112          3
     3      111          5
     4      113          1
     5      114          2
     6      115          1
     1      114          2
     2      113          2

iii. Give the detail of the author who have 2 or more books in the catalog and the price
of the book is greater than the average price of the book in the catalog and the year of
publication after 2000

select name,city,country
from author
where aid in (select aid from catalog where year>2000 and price>(select avg(price) from
catalog)
                                                                                          18


group by aid having count(*)>1);
 NAME          CITY          COUNTRY
----------- --------------- ---------------
  mik           bang              ind
  muj           bang              ind

iv. Find the author of the book which has maximum sales
select a.aid, name from author a, catalog c
where a.aid=c.aid and c.bid=(select bid from odetails group by bid having
sum(qty)=(select max(sum(qty)) from odetails group by bid));

 AID        NAME
---------- ---------------
  1      mik

v. Demonstrate how to increase the price of books published by a specific publisher by
10%
update catalog set price=1.1*price where pid in (select pid from publisher where name='abc');

select * from catalog;
BID TITLE             AID       PID CID YEAR PRICE
------- ------------ --------- -------- -------- ---------- ----------
111      lib1           1        101 1001 2002                500
112      lib2           2        102 1002 2000                800
113      lib3           3        103 1003 2003                200
114 lib4                4        104 1001 2006                385
115      lib5           5        105 1004 2007                100
116      lib6           2        103 1005 2007                600
117      lib7           2        105 1002 2007                450
118      lib8           1        101 1001 2002                500
                                                                                            19


5. Consider the following database for a banking enterprise

BRANCH (branch_name: string, branch_city: string, assets: real)
ACCOUNT (accno: int, branch_name: string, balance: real)
CUSTOMER (customer_name: string, customer_street: string, city:string)
DEPOSITOR (customer_name: string, accno: int)
LOAN (loan_number: int, branch_name: string, amount: real)
BORROWER (customer_name: string, loan_number: int)

   i)   Create the above tables by properly specifying the primary keys and the foreign keys.
   ii)  Enter atleast five tuples for each relation.
   iii) Find all the customers who atleast two accounts at the MAIN branch.
   iv)  Find all the customers who have an account at all branches located in a specific city.
   v)   Demonstrate how you delete all account tuples at every branch located in a specific
        city.
   vi) Generation of suitable reports.
   vii) Create suitable front end for querying and displaying the results.

create table branch
(
bname varchar(10) primary key,
city varchar(10),
assets real
);

create table account
(
accno int primary key,
bname varchar(10),
balance real,
foreign key(bname) references branch(bname)
);

create table cust
(
cname varchar(10) primary key,
cstreet varchar(10) not null,
city varchar(10) not null
);

create table depositor
(
cname varchar(10),
accno int,
primary key(cname,accno),
foreign key(accno) references account(accno) on delete cascade,
foreign key(cname) references cust(cname) on delete cascade
);
                                                                20


create table loan
(
lno int primary key,
bname varchar(10) not null,
amt real,
foreign key(bname) references branch(bname) on delete cascade
);

create table borrower
(
cname varchar(10),
lno int,
primary key(cname,lno),
foreign key(cname) references cust(cname) on delete cascade,
foreign key(lno) references loan(lno) on delete cascade
);

insert into branch values('abc','bang',1200000);
insert into branch values('def','che',2000000);
insert into branch values('abn','mum',330000);
insert into branch values('xyz','hyd',555555);
insert into branch values('mno','bang',9999999);

insert into   account values(1,'abc',25000);
insert into   account values(2,'def',12000);
insert into   account values(3,'def',1000);
insert into   account values(4,'abn',10000);
insert into   account values(5,'mno',600000);
insert into   account values(6,'xyz',50000);

insert into cust values('mik','ab','bang');
insert into cust values('muj','cd','bang');
insert into cust values('maj','ef','che');
insert into cust values('waj','xy','del');
insert into cust values('prad','lm','mum');
insert into cust values('now','op','hyd');

insert into depositor values('mik',2);
insert into depositor values('muj',1);
insert into depositor values('muj',5);
insert into depositor values('prad',4);
insert into depositor values('maj',3);
insert into depositor values('waj',6);
insert into depositor values('mik',3);

insert into loan values(1,'abc',5000);
insert into loan values(2,'def',1500);
insert into loan values(3,'abn',10000);
insert into loan values(4,'xyz',3500);
insert into loan values(5,'mno',20000);
                                           21



insert into borrower values('mik',2);
insert into borrower values('muj',1);
insert into borrower values('prad',3);
insert into borrower values('maj',4);
insert into borrower values('waj',5);


select *from branch;
BNAME CITY                    ASSETS
----------  ----------        ----------
  abc        bang            1200000
  def        che             2000000
  abn        mum             330000
  xyz        hyd             555555
  mno        bang            9999999


select *from account;
ACCNO BNAME BALANCE
----------   ---------- ----------
       1       abc      25000
       2       def      12000
       3       def      1000
       4       abn       10000
       5       mno      600000
       6       xyz      50000


select *from cust;
CNAME        CSTREET          CITY
----------    ------------    ----------
  mik             ab           bang
  muj             cd          bang
  maj             ef           che
  waj             xy          del
  prad           lm           mum
  now            op           hyd


select *from depositor;
CNAME ACCNO
---------- ------------
  mik          2
  muj          1
  muj          5
  prad         4
  maj          3
  waj          6
  mik          3
                                                                                         22


select *from loan;
 LNO BNAME AMT
---------- ------------ ----------
   1           abc        5000
   2           def       1500
   3           abn        10000
   4           xyz        3500
   5           mno        20000

select *from borrower;
CNAME          LNO
------------- ----------
   mik            2
   muj            1
   prad          3
   maj            4
   waj           5

iii. Find all the customers who atleast two accounts at the MAIN branch.
select cname from account a,depositor d where a.accno=d.accno and bname='def' group by
cname having count(*)>1;
CNAME
----------
   mik

iv. Find all the customers who have an account at all branches located in a specific city
select cname from cust c where not exists
(select bname from branch where city='bang' minus select bname from depositor d,account a
where d.accno=a.accno and d.cname=c.cname)
and exists
(select bname from branch where city='bang');
CNAME
------------
   muj

v. Demonstrate how you delete all account tuples at every branch located in a specific
city.
delete from account where bname in (select bname from branch where city='che');
2 rows deleted.

select *from account;
ACCNO BNAME BALANCE
----------- ------------ ---------------
     1         abc          25000
     4         abn          10000
     5         mno          600000
     6         xyz          50000


                                           **************

				
DOCUMENT INFO
Shared By:
Categories:
Tags: DBMS
Stats:
views:99
posted:4/10/2012
language:English
pages:22
Description: DBMS Lab Programs