1
SQL> create user dbms identified by lab;
SQL> grant connect, resource to dbms;
den login with same account
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
- Update the damage amount for the car with a specific regno in accident with report number 12 to 25000
- 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
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');
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
- Update the damage amount for the car with a specific regno in accident with report number 12 to 25000
- Add a new accident to the database
Soln:
- Update participated set damage_amt = 25000 where reg_no = ‘®_no’ and report_no = 12;
- 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;
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)
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);
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);
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');
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)
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)
);
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');
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');
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
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
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)
- Create the above tables by properly specifying the primary keys and the foreign keys.
- Enter at least five tuples for each relation.
- 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.
- Find the author of the book which has maximum sales.
- Demonstrate how you increase the price of books published by a specific publisher by 10%.
- Generation of suitable reports.
- 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)
);
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)
);
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);
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
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 branchlocated 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.
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);
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');
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