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

DBMS Lab Manual

 2

SOLUTION:.

i) Create the above tables by properly specifying the primary keys and the foreign
keys

create table person(driver_id char(4) primary key,

name varchar2(30),
address varchar2(30)
);

create table car(reg_no char(15) primary key,

model char(20),
year number(4)
);

create table accident(report_no number primary key,

acc_date date,
location varchar2(30)
);

create table owns(driver_id references person,

reg_no references car
);

create table participated( driver_id references person,

reg_no references car,
report_no references accident,
damage_amt number(10,2)
);

ii) Enter atleast five tuples for each relation

insert into person values('1234','amith','no a-1-12 koppal');

insert into person values('2345','anil','23 vijaya apts');
insert into person values('3412','john','no 3423 vicky apts');
insert into person values('4567','arun','kamal nivas koppal');
insert into person values('4522','sunil','no 54 ravi nagar');

DBMS Lab Manual

 3

insert into car values('ka37k32','hyundai',2004);

insert into car values('ka05d34','maruti 800',1998);
insert into car values('ka23j90','zen',2002);
insert into car values('ka35f45','fiat',2001);
insert into car values('ka36m78','benz',2000);

insert into accident values(12,'12-feb-1990','vit cross');

insert into accident values(34,'31-jan-1999','jayanagar');
insert into accident values(56,'12-dec-1998','btm layout');
insert into accident values(67,'07-jul-2003','jp nagar');
insert into accident values(87,'01-may-2001','allalsandra');

insert into owns values('1234','ka37k32');

insert into owns values('2345','ka05d34');
insert into owns values('3412','ka23j90');
insert into owns values('4567','ka35f45');
insert into owns values('4522','ka36m78');

insert into participated values('1234','ka37k32',12,12000);

insert into participated values('2345','ka05d34',34,13000);
insert into participated values('3412','ka23j90',56,14000);
insert into participated values('4567','ka35f45',67,12450);
insert into participated values('4522','ka36m78',87,10000);

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
Soln:
a. Update participated set damage_amt = 25000 where reg_no = &reg_no and
report_no = 12;
b. Insert into accident values(93,02-may-2002,allalsandra);

iv) Find the total number of people who owned cars that were involved in accidents in
2006.

SQL> Select count(*) from accident where to_char(acc_date,yy) = 02;

DBMS Lab Manual

 4

v) Find the number of accidents in which cars belonging to a specific model were
involved.

SQL> Select count(a.report_no) from accident a,participated p,car c where c.reg_no =

p.reg_no and

a.report_no = p.report_no and c.model = '&model' group by a.report_no

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)

DBMS Lab Manual

 5

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.

Solution:..

i) Create the above tables by properly specifying the primary keys and the
foreign keys.

create table customer(cust_no number primary key,

cname varchar2(30),

city varchar2(30));

create table orders( order_no number primary key,

odate date,

cust_no references customer,

ord_amt number);

create table item( item_no number primary key,

unit_price number);

DBMS Lab Manual

 6

create table orders_item( order_no references orders,

item_no references item,

qty number);

create table warehouse(warehouse_no number primary key,

city varchar2(30));

create table shipment(order_no references orders,

warehouse_no references warehouse,

ship_date date);

ii) Enter at least five tuples for each relation.

insert into customer values(10,'ajay','bangalore');

insert into customer values(21,'arun','hyderabad');

insert into customer values(32,'ikram','hubli');

insert into customer values(45,'suraj','kanpur');

insert into customer values(78,'niraja','panji');

insert into orders values(12345,'25-mar-2005',10,0);

insert into orders values(12346,'26-mar-2005',21,0);

insert into orders values(12347,'30-mar-2005',32,0);

insert into orders values(12348,'01-apr-2005',45,0);

insert into orders values(12349,'01-apr-2005',78,0);

DBMS Lab Manual

 7

insert into item values(10,100);

insert into item values(20,60);

insert into item values(30,140);

insert into item values(40,35);

insert into item values(50,150);

insert into orders_item values(12345,10,4);

insert into orders_item values(12346,20,2);

insert into orders_item values(12347,30,5);

insert into orders_item values(12348,40,10);

insert into orders_item values(12349,50,18);

insert into warehouse values(1501,'bangalore');

insert into warehouse values(1502,'hyderabad');

insert into warehouse values(1503,'hubli');

insert into warehouse values(1504,'delhi');

insert into warehouse values(1505,'belgaum');

insert into shipment values(12345,1501,'5-mar-2005');

insert into shipment values(12346,1502,'28-mar-2005');

insert into shipment values(12347,1503,'01-apr-2005');

insert into shipment values(12348,1504,'04-apr-2005');

insert into shipment values(12349,1505,'05-apr-2005');

DBMS Lab Manual

 8

update orders set ord_amt = (select sum(oi.qty * i.unit_price) from orders_item oi,item I
where oi.order_no = orders.order_no and I.item_no = oi.item_no);

commit;

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.

SQL> select cname CustName,count(*) No of Orders,avg(ord_amt) Average order

amt from orders o,customer c where o.cust_no = c.cust_no group by cname;

CustName No of Orders Average amt

------------------------------ ------------ ---------------------------
ajay 1 400
arun 1 120
ikram 1 700
niraja 1 2700
suraj 1 350

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

SQL> Select order_no from warehouse w,shipment s where w.warehouse_no =

s.warehouse_no and w.city = &city;

v) Demonstrate how you delete item# 10 from the item table and make that field
null in the order table.

SQL> Delete from item where item_no = 10;

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)

DBMS Lab Manual

 9

COURSE (course# : int , cname : string , dept : string)

ENROLL ( regno : string , course#: int , sem : int , marks : int )

BOOK_ADOPTION ( 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.
Create suitable front end for querying and display the results

Solution:

i) Create the above tables by properly specifying the primary keys and the foreign key .

create table student(reg_no char(10) primary key,

name varchar2(30),

major char(1),

bdate date

);

create table course(course_no number primary key,

cname varchar2(30),

dept varchar2(30)

DBMS Lab Manual

 10

);

create table enroll(reg_no references student,

course_no references course,

sem number(1),

marks number(3),

primary key(reg_no,course_no,sem)

);

create table text(book_isbn number(4) primary key,

book_title varchar2(30),

publisher varchar2(30),

author varchar2(30)

);

create table book_adoption(course_no references course,

sem number(1),

book_isbn references text

);

ii) Enter atleast five tuples for each relation .

insert into student values('1mv02is033','prasanna','y','24-oct-84');

DBMS Lab Manual

 11

insert into student values('1mv02is050','sunil','y','22-may-84');

insert into student values('1mv02cs001','aditya','y','11-jan-83');

insert into student values('1mv02mca22','raju','n','30-mar-83');

insert into student values('1mv02mba33','vishal','y','02-apr-82');

insert into course values(1,'be','ise');

insert into course values(2,'be','cse');

insert into course values(3,'be','ece');

insert into course values(4,'mca','ca');

insert into course values(5,'mba','hr');

insert into enroll values('1mv02is033',1,6,97);

insert into enroll values('1mv02is050',2,5,66);

insert into enroll values('1mv02cs001',3,4,45);

insert into enroll values('1mv02mca22',4,1,77);

insert into enroll values('1mv02mba33',5,2,88);

insert into text values(1234,'dbms','bpb','navathe');

insert into text values(2345,'cn','lpe','tanenbaum');

insert into text values(3456,'ds','galgotia','padmareddy');

insert into text values(4567,'cpp','pearson','herbert');

insert into text values(5678,'unix','bpb','das');

insert into text values(1333,'cn','pearson','tanenbaum');

insert into text values(1444,'cn','pearson','tanenbaum');

DBMS Lab Manual

 12

insert into book_adoption values(1,6,1234);

insert into book_adoption values(2,5,2345);

insert into book_adoption values(3,4,3456);

insert into book_adoption values(5,1,5678);

insert into book_adoption values(4,2,1234);

insert into book_adoption values(2,6,4567);

insert into book_adoption values(2,1,5678);

insert into book_adoption values(2,6,1333);

insert into book_adoption values(2,6,1444);

commit;

iii) Demonstrate how you add a new text book to the database and make this book be
adopted by some department.

SQL> insert into text values(1222,maths,DSC,KSC);

SQL> insert into book_adoption values(4,6,1222);

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.

SQL> Select c.course_no,t.book_title,c.dept from course c,text t,book_adoption b

where c.course_no = b.course_no and
b.book_isbn = t.book_isbn
and
c.dept = 'cse'
group by book_title,c.course_no,c.dept having

DBMS Lab Manual

 13

count(book_title) >2 ;

v) List any department that has all its adopted books published by specific publisher.

SQL> select c.dept,t.publisher from course c,book_adoption b,text t where

c.course_no = b.course_no and
b.book_isbn = t.book_isbn and
t.publisher = '&publisher'
group by c.dept,t.publisher;

Enter value for publisher: bpb

old 4: t.publisher = '&publisher'
new 4: t.publisher = 'bpb'

DEPT PUBLISHER
------------------------------ ------------------------------
ca bpb
cse bpb
hr bpb
ise bpb

DBMS Lab Manual

 14

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.

Solution:

i) Create the above tables by properly specifying the primary keys and the foreign
keys.

create table author(author_id number(4) primary key,

aname varchar2(30),

acity varchar2(30),

acountry varchar2(30)

DBMS Lab Manual

 15

);

create table publisher(pub_id number(4) primary key,

pname varchar2(30),

pcity varchar2(30),

pcountry varchar2(30)

);

create table category(cat_id number(4) primary key,

description varchar2(30)

);

create table catalog(book_id number(4) primary key,

title varchar2(30),

author_id references author,

pub_id references publisher,

cat_id references category,

year number(4),

price number(6,2)

);

create table order_details(order_no number(4) primary key,

book_id references catalog,

qty number(5)

DBMS Lab Manual

 16

);

ii) Enter at least five tuples for each relation.

insert into author values(1001,'balaguruswamy','hyderabad','india');

insert into author values(1002,'tenanbaum','california','usa');

insert into author values(1003,'padma reddy','bangalore','india');

insert into author values(1004,'godse','pune','india');

insert into author values(1005,'oppenham','los angeles','usa');

insert into publisher values(3001,'suhas','bangalore','india');

insert into publisher values(3002,'bpb','delhi','india');

insert into publisher values(3003,'prentice','delhi','india');

insert into publisher values(3004,'pearson','california','usa');

insert into publisher values(3005,'subhas','bangalore','india');

insert into category values(4001,'programming');

insert into category values(4002,'os');

insert into category values(4003,'database');

insert into category values(4004,'networks');

insert into category values(4005,'logic design');

insert into catalog values(1,'let us c',1001,3001,4001,1999,375);

insert into catalog values(2,'database',1002,3002,4003,2002,450);

insert into catalog values(3,'network',1003,3003,4002,1998,500);

DBMS Lab Manual

 17

insert into catalog values(4,'logic design',1004,3004,4004,2004,750);

insert into catalog values(5,'c++',1005,3005,4005,2005,450);

insert into catalog values(6,'c',1001,3002,4002,2003,880);

insert into catalog values(7,'cn',1001,3004,4002,2005,750);

insert into order_details values(1,1,34);

insert into order_details values(2,1,45);

insert into order_details values(3,2,45);

insert into order_details values(4,2,75);

insert into order_details values(5,3,69);

commit;

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.

SQL> Select * from author where author_id in

(select author_id from catalog where year >2000 and

price > (select avg(price) from catalog) )

and

author_id in

(select author_id from catalog group by author_id having count(author_id) > 1);

iv) Find the author of the book which has maximum sales.

SQL> Select a.author_id,a.aname from author a,catalog c,max_sales m where

DBMS Lab Manual

 18

a.author_id = c.author_id and

c.book_id = m.book_id and

m.book_id in (select book_id from max_sales where quantity =

(select max(quantity) from max_sales));

v) Demonstrate how you increase the price of books published by a specific

publisher by 10%.

SQL> update catalog set price = price*1.1 where pub_pd = &publisher_id;

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.

Solution:..

i) Create the above tables by properly specifying the primary keys and the foreign keys.

DBMS Lab Manual

 19

create table branch(br_name varchar2(30) primary key,

br_city varchar2(30),
assets number(10,2)
);

create table account(acc_no number(4) primary key,

br_name references branch,
balance number(10,2)
);

create table customers(c_name varchar2(30) primary key,

c_street varchar2(30),
c_city varchar(30)
);

create table depositor(c_name references customers,

acc_no references account,
qty number
);

create table loan(loan_no number(4) primary key,

br_name references branch,
amt number(10,2)
);

create table borrower(c_name references customers,

loan_no references loan
);

ii) Enter atleast five tuples for each relation.

insert into branch values('rajaji nagar','bangalore',1000000);

insert into branch values('jayanagar','bangalore',50000);
insert into branch values('mvit','bangalore',10000);
insert into branch values('jawahar nagar','bangalore',100000);
insert into branch values('rajbhavan','bangalore',23566);

insert into account values(1000,'rajaji nagar',2500);

insert into account values(2000,'rajaji nagar',8996);
insert into account values(3000,'rajaji nagar',7415);
insert into account values(4000,'jayanagar',2121);
insert into account values(5000,'mvit',8596);
insert into account values(6000,'jawahar nagar',9999);
insert into account values(7000,'rajbhavan',235);

DBMS Lab Manual

 20

insert into customers values('prasanna','patel road','raichur');

insert into customers values('harish','indiranagar','bangalore');
insert into customers values('sunil','ring road','bangalore');
insert into customers values('srinivas','woc road','bangalore');
insert into customers values('rudre','maruti galli','belgaum');

insert into depositor values('prasanna',1000,2000);

insert into depositor values('prasanna',2000,3000);
insert into depositor values('harish',3000,5000);
insert into depositor values('sunil',4000,1520);
insert into depositor values('srinivas',5000,1120);
insert into depositor values('rudre',6000,1250);
insert into depositor values('prasanna',7000,1250);

insert into loan values(100,'rajaji nagar',5000);

insert into loan values(200,'rajaji nagar',4000);
insert into loan values(300,'jayanagar',6323);
insert into loan values(400,'mvit',4512);
insert into loan values(500,'jawahar nagar',1235);
insert into loan values(600,'rajbhavan',9632);
insert into loan values(700,'rajbhavan',3456);

insert into borrower values('prasanna',100);

insert into borrower values('harish',200);
insert into borrower values('sunil',300);
insert into borrower values('srinivas',400);
insert into borrower values('rudre',500);

iii) Find all the customers who atleast two accounts at the MAIN branch.

SQL>Select c_name from depositor d,account a where

a.acc_no = d.acc_no and
a.br_name = 'rajaji nagar
group by c_name
having count(*) > 1;

iv) Find all the customers who have an account at all branches located in a specific city.

SQL> select c_name from customers c where not exists

(select br_name from branch where br_city='bangalore' minus select br_name from
depositor d,acc
where d.acc_no=a.acc_no and d.c_name=c.c_name)
and exists
(select br_name from branch where br_city='bangalore');

DBMS Lab Manual

 21

v) Demonstrate how you delete all account tuples at every branch located in a specific city.

SQL> Delete from depositor where acc_no in

(select acc_no from account where br_name in
(select br_name from branch where br_city = 'bangalore));
SQL> Delete from account where br_name in
(select br_name from branch where br_city = 'bangalore);

DBMS Lab Manual

 22

DBMS Lab Manual