sonu. corp.. limited…
1
DBMS Lab Manual
2011
DBMS Lab Manual
2
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 amount:int)
(driver-id:string,regno:string,report-number:int,damage-
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.
DBMS Lab Manual
3
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) );
DBMS Lab Manual
4
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 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 = ‘®_no’ and report_no = 12; b. Insert into accident values(93,’02-may-2002’,’allalsandra’);
DBMS Lab Manual
5
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
DBMS Lab Manual
6 2. Consider the following application in a company.
relations
for
an
order
processing
database
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) iii) iv) v) vi) vii)
Create the above tables by properly specifying the primary keys and the foreign keys. ii) Enter at least five tuples for each relation. 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. List the Order# for the orders that were shipped from all the warehouses that the company has in a specific city. Demonstrate how you delete Item# 10 from the ITEM table and make that field null in the ORDER-ITEM table. 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 customer(cust_no number primary key, cname varchar2(30), city varchar2(30));
create table orders( order_no number primary key,
DBMS Lab Manual
7 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);
DBMS Lab Manual
8 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');
DBMS Lab Manual
9 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;
DBMS Lab Manual
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),
DBMS Lab Manual
11 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
DBMS Lab Manual
12 );
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');
DBMS Lab Manual
13 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);
DBMS Lab Manual
14 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
DBMS Lab Manual
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. ii. iii. iv. v. vi. vii.
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.
DBMS Lab Manual
16 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) );
DBMS Lab Manual
17 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);
DBMS Lab Manual
18 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.
DBMS Lab Manual
19 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 branch located in a specific city. vi) Generation of suitable reports. vii) Create suitable front end for querying and displaying the results.
DBMS Lab Manual
20
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);
DBMS Lab Manual
21 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
DBMS Lab Manual
22 (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
23
DBMS Lab Manual
