LAB PROGRAM #1
=====================================================================
CONSIDER THE INSURANCE DATABASE GIVEN BELOW. THE PRIMARY KEYS ARE UNDERLINED AND DATA TYPES ARE SPECIFIED.
PERSON (DRIVER-ID : STRING, NAME : STRING, ADDRESS : STRING)
CAR (REGNO : STRING, MODEL : STRING, YEAR : INT)
ACCIDENT (REPORT-NO : INT, ACCD-DATE : DATE, LOCATION : STRING)
OWNS (DRIVER-ID : STRING, REGNO : STRING)
PARTICIPATED(DRIVER-ID : STRING, REGNO : STRING, REPORT-NO : INT, DAMAGE-AMT : INT)
=====================================================================
Drop table PERSON;
Drop table CAR;
Drop table ACCIDENT;
Drop table OWNS;
Drop table PARTICIPATED;
=====================================================================
1. CREATE THE ABOVE TABLES BY PROPERLY SPECIFYING THE PRIMARY KEYS AND FOREIGN KEYS.
---------------------------------------------------------------------
Create table PERSON
(
driver_id varchar2(10),
name varchar2(10),
address varchar2(10),
primary key(driver_id)
);
---------------------------------------------------------------------
Create table CAR
(
regno varchar2(10),
model varchar2(10),
year number(4),
primary key(regno)
);
---------------------------------------------------------------------
Create table ACCIDENT
(
report_no number(10),
accd_date date,
location varchar2(10),
primary key(report_no)
);
---------------------------------------------------------------------
Create table OWNS
(
driver_id varchar2(10),
regno varchar2(10),
primary key(driver_id,regno),
foreign key(driver_id) references PERSON(driver_id),
foreign key(regno) references CAR(regno)
);
---------------------------------------------------------------------
Create table PARTICIPATED
(
driver_id varchar2(10),
regno varchar2(10),
report_no number(10),
damage_amt number(5);
primary key(driver_id,regno,report_no),
foreign key(driver_id) references PERSON(driver_id),
foreign key(regno) references CAR(regno),
foreign key(report_no) references ACCIDENT(report_no)
);
=====================================================================
Desc PERSON;
Desc CAR;
Desc ACCIDENT;
Desc OWNS;
Desc PARTICIPATED;
=====================================================================
2. ENTER ATLEAST FIVE TUPLES TO EACH RELATION.
---------------------------------------------------------------------
Insert into PERSON values('ydit1','ABC','P1');
Insert into PERSON values('ydit2','BCD','P2');
Insert into PERSON values('ydit3','CDE','P3');
Insert into PERSON values('ydit4','DEF','P4');
Insert into PERSON values('ydit5','EFG','P5');
---------------------------------------------------------------------
Insert into CAR values('ka04','Maruti',2008);
Insert into CAR values('ka05','Maruti',2007);
Insert into CAR values('ka06','Fiat',2008);
Insert into CAR values('ka09','Chevorlet',2009);
Insert into CAR values('ka03','Maruti',2005);
---------------------------------------------------------------------
Insert into ACCIDENT values('1','1-jan-2008','India');
Insert into ACCIDENT values('2','2-jan-2008','India');
Insert into ACCIDENT values('3','3-jan-2008','India');
Insert into ACCIDENT values('4','4-jan-2008','India');
Insert into ACCIDENT values('5','5-jan-2008','India');
---------------------------------------------------------------------
Insert into OWNS values('ydit1','ka04');
Insert into OWNS values('ydit2','ka05');
Insert into OWNS values('ydit3','ka06');
Insert into OWNS values('ydit4','ka09');
Insert into OWNS values('ydit5','ka03');
Insert into PARTICIPATED values('ydit1','ka04',1,10000);
Insert into PARTICIPATED values('ydit2','ka05',2,20000);
Insert into PARTICIPATED values('ydit3','ka06',3,30000);
Insert into PARTICIPATED values('ydit4','ka09',4,40000);
Insert into PARTICIPATED values('ydit5','ka03',5,50000);
=====================================================================
Select * from PERSON;
DRIVER_ID NAME ADDRESS
---------- ---------- ----------
ydit1 ABC P1
ydit2 BCD P2
ydit3 CDE P3
ydit4 DEF P4
ydit5 EFG P5
---------------------------------------------------------------------
Select * from CAR;
REGNO MODEL YEAR
---------- ---------- ---------
ka04 Maruti 2008
ka05 Maruti 2007
ka06 Fiat 2008
ka09 Chevorlet 2009
ka03 Maruti 2005
---------------------------------------------------------------------
Select * from ACCIDENT;
REPORT_NO ACCD_DATE LOCATION
--------- --------- ----------
1 01-JAN-08 India
2 02-JAN-08 India
3 03-JAN-08 India
4 04-JAN-08 India
5 05-JAN-08 India
---------------------------------------------------------------------
Select * from OWNS;
DRIVER_ID REGNO
---------- ----------
ydit1 ka04
ydit2 ka05
ydit3 ka06
ydit4 ka09
ydit5 ka03
Select * from PARTICIPATED;
DRIVER_ID REGNO REPORT_NO DAMAGE_AMT
---------- ---------- --------- ----------
ydit1 ka04 1 10000
ydit2 ka05 2 20000
ydit3 ka06 3 30000
ydit4 ka09 4 40000
ydit5 ka03 5 50000
=====================================================================
3. DEMONSTRATE HOW YOU
a. UPDATE THE DAMAGE AMOUNT TO 25000 FOR THE CAR WITH A SPECIFIC REGNO AND REPORT NUMBER IN THE ACCIDENT TABLE
b. ADD A NEW ACCIDENT TO THE DATABASE.
---------------------------------------------------------------------
Update PARTICIPATED set damage_amt=25000 where regno='ka04' and report_no=1;
Select * from PARTICIPATED;
DRIVER_ID REGNO REPORT_NO DAMAGE_AMT
---------- ---------- --------- ----------
ydit1 ka04 1 25000
ydit2 ka05 2 20000
ydit3 ka06 3 30000
ydit4 ka09 4 40000
ydit5 ka03 5 50000
---------------------------------------------------------------------
Insert into ACCIDENT values(12,'8-jan-2008','USA');
Select * from ACCIDENT;
REPORT_NO ACCD_DATE LOCATION
--------- --------- ----------
1 01-JAN-08 India
2 02-JAN-08 India
3 03-JAN-08 India
4 04-JAN-08 India
5 05-JAN-08 India
12 08-JAN-08 USA
---------------------------------------------------------------------
Insert into PERSON values('ydit8','PQR','P8');
Insert into CAR values('ka01','Maruti',2005);
Insert into PARTICIPATED values('ydit8','ka01',12,55000);
---------------------------------------------------------------------
Select * from PERSON;
DRIVER_ID NAME ADDRESS
---------- ---------- ----------
ydit1 ABC P1
ydit2 BCD P2
ydit3 CDE P3
ydit4 DEF P4
ydit5 EFG P5
ydit8 PQR P8
Select * from CAR;
REGNO MODEL YEAR
---------- ---------- ---------
ka04 Maruti 2008
ka05 Maruti 2007
ka06 Fiat 2008
ka09 Chevorlet 2009
ka03 Maruti 2005
ka01 Maruti 2005
---------------------------------------------------------------------
Select * from PARTICIPATED;
DRIVER_ID REGNO REPORT_NO DAMAGE_AMT
---------- ---------- --------- ----------
ydit1 ka04 1 25000
ydit2 ka05 2 20000
ydit3 ka06 3 30000
ydit4 ka09 4 40000
ydit5 ka03 5 50000
ydit8 ka01 12 55000
=====================================================================
4. FIND THE TOTAL NUMBER OF PEOPLE WHO OWNED CARS THAT WERE INVOLVED IN ACCIDENTS IN 2008.
---------------------------------------------------------------------
Select count(*) from PERSON P, ACCIDENT A, PARTICIPATED PD
where accd_date like '%08'
and P.driver_id=PD.driver_id
and A.report_no=PD.report_no;
COUNT(*)
---------
6
=====================================================================
5. FIND THE TOTAL NUMBER OF ACCIDENTS IN WHICH CARS BELONGING TO A SPECIFIC MODEL WERE INVOLVED.
---------------------------------------------------------------------
Select count(*) from ACCIDENT A, CAR C, PARTICIPATED PD
where C.model='Maruti'
and C.regno=PD.regno
and A.report_no=PD.report_no;
COUNT(*)
---------
4
=====================================================================
LAB PROGRAM #2
=====================================================================
CONSIDER THE FOLLOWING RELATIONS FOR AN ORDER PROCESSING DATABASE APPLICATION IN A COMPANY
CUSTOMER (CUST-NO : INT, CNAME : STRING, CITY : STRING)
ORDER (ORDER-NO : INT, ODATE : DATE, CUST-NO : INT, ORD-AMT : INT)
ORDER-ITEM(ORDER-NO : INT, ITEMNO : INT, QTY : INT)
ITEM (ITEMNO : INT, UNIT-PRICE : INT)
SHIPMENT (ORDER-NO : INT, WAREHOUSE-NO : INT, SHIP-DATE : DATE)
WAREHOUSE (WAREHOUSE-NO : INT, CITY : STRING)
=====================================================================
Drop table CUST;
Drop table ORD;
Drop table ITEM;
Drop table ORDER_ITEM;
Drop table WAREHOUSE;
Drop table SHIPMENT;
=====================================================================
1. CREATE THE ABOVE TABLES BY PROPERLY SPECIFYING THE PRIMARY KEYS AND FOREIGN KEYS.
---------------------------------------------------------------------
Create table CUST(
cust_no number(10),
cname varchar2(10),
city varchar2(10),
primary key(cust_no)
);
---------------------------------------------------------------------
Create table ORD(
order_no number(10),
odate date,
cust_no number(10),
ord_amt number(10),
primary key(order_no)
);
---------------------------------------------------------------------
Create table ITEM(
itemno number(10),
unit_price number(10),
primary key(itemno)
);
---------------------------------------------------------------------
Create table ORDER_ITEM(
order_no number(10),
itemno number(10),
qty number(10),
primary key(order_no,itemno),
foreign key(order_no) references ORD(order_no),
foreign key(itemno) references ITEM(itemno)
on delete cascade
);
---------------------------------------------------------------------
Create table WAREHOUSE(
warehouse_no number(10),
city varchar2(10),
primary key(warehouse_no)
);
---------------------------------------------------------------------
Create table SHIPMENT(
order_no number(10),
warehouse_no number(10),
ship_date date,
primary key(order_no,warehouse_no),
foreign key(order_no) references ORD(order_no),
foreign key(warehouse_no) references WAREHOUSE(warehouse_no)
);
=====================================================================
2. ENTER ATLEAST FIVE TUPLES TO EACH RELATION.
---------------------------------------------------------------------
Insert into CUST values(111,'ABC','BANGALORE');
Insert into CUST values(222,'BCD','MYSORE');
Insert into CUST values(333,'CDE','GULBARGA');
Insert into CUST values(444,'DEF','HASSAN');
Insert into CUST values(555,'EFG','SHIMOGA');
---------------------------------------------------------------------
Insert into ORD values(1,'1-JAN-2009',111,10000);
Insert into ORD values(2,'1-FEB-2009',222,20000);
Insert into ORD values(3,'1-MAR-2009',333,30000);
Insert into ORD values(4,'1-APR-2009',444,40000);
Insert into ORD values(5,'1-MAY-2009',555,50000);
---------------------------------------------------------------------
Insert into ITEM values(11,100);
Insert into ITEM values(12,200);
Insert into ITEM values(13,300);
Insert into ITEM values(14,400);
Insert into ITEM values(15,500);
---------------------------------------------------------------------
Insert into ORDER_ITEM values(1,11,1000);
Insert into ORDER_ITEM values(2,12,2000);
Insert into ORDER_ITEM values(3,13,3000);
Insert into ORDER_ITEM values(4,14,4000);
Insert into ORDER_ITEM values(5,15,5000);
---------------------------------------------------------------------
Insert into WAREHOUSE values(101,'BANGALORE');
Insert into WAREHOUSE values(102,'MYSORE');
Insert into WAREHOUSE values(103,'GULBARGA');
Insert into WAREHOUSE values(104,'BELGAUM');
Insert into WAREHOUSE values(105,'HASSAN');
---------------------------------------------------------------------
Insert into SHIPMENT values(1,101,'15-JAN-2009');
Insert into SHIPMENT values(2,102,'15-FEB-2009');
Insert into SHIPMENT values(3,103,'15-MAR-2009');
Insert into SHIPMENT values(4,104,'15-APR-2009');
Insert into SHIPMENT values(5,105,'15-MAY-2009');
=====================================================================
Select * from CUST;
CUST_NO CNAME CITY
--------- ---------- ----------
111 ABC BANGALORE
222 BCD MYSORE
333 CDE GULBARGA
444 DEF HASSAN
555 EFG SHIMOGA
---------------------------------------------------------------------
Select * from ORD;
ORDER_NO ODATE CUST_NO ORD_AMT
--------- --------- --------- ---------
1 01-JAN-09 111 10000
2 01-FEB-09 222 20000
3 01-MAR-09 333 30000
4 01-APR-09 444 40000
5 01-MAY-09 555 50000
---------------------------------------------------------------------
Select * from ITEM;
ITEMNO UNIT_PRICE
--------- ----------
11 100
12 200
13 300
14 400
15 500
---------------------------------------------------------------------
Select * from ORDER_ITEM;
ORDER_NO ITEMNO QTY
--------- --------- ---------
1 11 1000
2 12 2000
3 13 3000
4 14 4000
5 15 5000
---------------------------------------------------------------------
Select * from WAREHOUSE;
WAREHOUSE_NO CITY
------------ ----------
101 BANGALORE
102 MYSORE
103 GULBARGA
104 BELGAUM
105 HASSAN
---------------------------------------------------------------------
Select * from SHIPMENT;
ORDER_NO WAREHOUSE_NO SHIP_DATE
--------- ------------ ---------
1 101 15-JAN-09
2 102 15-FEB-09
3 103 15-MAR-09
4 104 15-APR-09
5 105 15-MAY-09
=====================================================================
3. PRODUCE A LISTING: CUSTNAME, NO-OF-ORDERS, AVG-ORD-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(O.order_no) AS No_Of_Orders,AVG(O.ord_amt) AS Avg_Ord_Amt
from CUST C,ORD O
where C.cust_no=O.cust_no group by C.cname;
CNAME NO_OF_ORDERS AVG_ORD_AMT
---------- ------------ -----------
ABC 1 10000
BCD 1 20000
CDE 1 30000
DEF 1 40000
EFG 1 50000
=====================================================================
4. LIST THE ORDER-NO FOR ORDERS THAT WERE SHIPPED FROM ALL THE WAREHOUSES THAT THE COMPANY HAS IN A SPECIFIC CITY
---------------------------------------------------------------------
Select S.ORDER_NO,S.WAREHOUSE_NO,W.CITY from SHIPMENT S,WAREHOUSE W
where W.WAREHOUSE_NO = S.WAREHOUSE_NO
and W.CITY='BANGALORE';
ORDER_NO WAREHOUSE_NO CITY
--------- ------------ ----------
1 101 BANGALORE
=====================================================================
5. DEMONSTRATE THE DELETION OF AN ITEM FROM THE ITEM TABLE AND DEMONSTRATE A METHOD OF HANDLING THE ROWS IN THE ORDER-ITEM TABLE THAT CONTAIN THIS PARTICULAR ITEM.
---------------------------------------------------------------------
Delete from ITEM where itemno=11;
---------------------------------------------------------------------
Select * from ITEM;
ITEMNO UNIT_PRICE
--------- ----------
12 200
13 300
14 400
15 500
---------------------------------------------------------------------
Select * from ORDER_ITEM;
ORDER_NO ITEMNO QTY
--------- --------- ---------
2 12 2000
3 13 3000
4 14 4000
5 15 5000
=====================================================================
LAB PROGRAM #3
=====================================================================
CONSIDER THE FOLLOWING DATABASE OF A STUDENT ENROLLMENT IN COURSES AND BOOKS ADOPTED FOR EACH COURSE
STUDENT (REGNO : STRING, NAME : STRING, MAJOR : STRING, BDATE : DATE)
COURSE (COURSENO : INT, CNAME : STRING, DEPT : STRING)
ENROLL (REGNO : STRING, COURSENO : INT, SEM : INT, MARKS : INT)
BOOK_ADOPTION(COURSENO : INT, SEM : INT, BOOK-ISBN : INT)
TEXT (BOOK-ISBN : INT, BOOK-TITLE : STRING, PUBLISHER : STRING, AUTHOR : STRING)
=====================================================================
Drop table STD;
Drop table COURSE_CHOSEN;
Drop table ENROLLED;
Drop table BOOK_ADPT;
Drop table TXT;
=====================================================================
1. CREATE THE ABOVE TABLES BY PROPERLY SPECIFYING THE PRIMARY KEYS AND FOREIGN KEYS.
---------------------------------------------------------------------
Create table STD(
RegNo varchar2(10),
Name varchar2(10),
Major varchar2(10),
BDate date,
primary key(RegNo)
);
---------------------------------------------------------------------
Create table COURSE_CHOSEN(
CourseNo number(10),
CName varchar2(10),
Dept varchar2(10),
primary key(CourseNo)
);
---------------------------------------------------------------------
Create table ENROLLED(
RegNo varchar2(10),
CourseNo number(10),
Sem number(2),
Marks number(3),
primary key(RegNo,CourseNo,Sem),
foreign key(RegNo) references STD(RegNo),
foreign key(CourseNo) references COURSE_CHOSEN(CourseNo)
);
---------------------------------------------------------------------
Create table TXT(
Book_ISBN number(5),
Book_Title varchar2(10),
Publisher varchar2(10),
Author varchar2(10),
primary key(Book_ISBN)
);
---------------------------------------------------------------------
Create table BOOK_ADPT(
CourseNo number(10),
Sem number(2),
Book_ISBN number(5),
primary key(CourseNo,Sem),
foreign key(CourseNo) references COURSE_CHOSEN(CourseNo),
foreign key(SEM) references ENROLLED(SEM),
foreign key(Book_ISBN) references TXT(Book_ISBN)
);
=====================================================================
2. ENTER ATLEAST FIVE TUPLES TO EACH RELATION.
---------------------------------------------------------------------
Insert into STD values('1yd07cs050','Sudhanshu','DBMS','17-Nov-1989');
Insert into STD values('1yd07cs052','Suhas','Maths','4-Mar-1990');
Insert into STD values('1yd07cs057','Vikram','Chemistry','28-Aug-1989');
Insert into STD values('1yd07cs058','Vishwas','Maths','24-May-1990');
Insert into STD values('1yd07cs017','Lohith','Maths','15-May-1988');
---------------------------------------------------------------------
Insert into COURSE_CHOSEN values(1,'DB2','CSE');
Insert into COURSE_CHOSEN values(2,'Calculus','Maths');
Insert into COURSE_CHOSEN values(3,'Organic','Chemistry');
Insert into COURSE_CHOSEN values(4,'Trig','Maths');
Insert into COURSE_CHOSEN values(5,'Statistics','Maths');
---------------------------------------------------------------------
Insert into ENROLLED values('1yd07cs050',1,5,95);
Insert into ENROLLED values('1yd07cs052',2,4,85);
Insert into ENROLLED values('1yd07cs057',3,2,80);
Insert into ENROLLED values('1yd07cs058',4,1,99);
Insert into ENROLLED values('1yd07cs017',5,3,86);
---------------------------------------------------------------------
Insert into TXT values(111,'DBMS_study','ABC','SN');
Insert into TXT values(222,'MATHS4','BCD','DSC');
Insert into TXT values(333,'ENG_CHEM','CDE','GADAG');
Insert into TXT values(444,'MATHS1','BCD','DSC');
Insert into TXT values(555,'MATHS3','BCD','DSC');
---------------------------------------------------------------------
Insert into BOOK_ADPT values(1,5,111);
Insert into BOOK_ADPT values(2,4,222);
Insert into BOOK_ADPT values(3,2,333);
Insert into BOOK_ADPT values(4,1,444);
Insert into BOOK_ADPT values(5,3,555);
=====================================================================
Select * from STD;
REGNO NAME MAJOR BDATE
---------- ---------- ---------- ---------
1yd07cs050 Sudhanshu DBMS 17-NOV-89
1yd07cs052 Suhas Maths 04-MAR-90
1yd07cs057 Vikram Chemistry 28-AUG-89
1yd07cs058 Vishwas Maths 24-MAY-90
1yd07cs017 Lohith Maths 15-MAY-88
---------------------------------------------------------------------
Select * from COURSE_CHOSEN;
COURSENO CNAME DEPT
--------- ---------- ----------
1 DB2 CSE
2 Calculus Maths
3 Organic Chemistry
4 Trig Maths
5 Statistics Maths
---------------------------------------------------------------------
Select * from ENROLLED;
REGNO COURSENO SEM MARKS
---------- --------- --------- ---------
1yd07cs050 1 5 95
1yd07cs052 2 4 85
1yd07cs057 3 2 80
1yd07cs058 4 1 99
1yd07cs017 5 3 86
---------------------------------------------------------------------
Select * from TXT;
BOOK_ISBN BOOK_TITLE PUBLISHER AUTHOR
--------- ---------- ---------- ----------
111 DBMS_study ABC SN
222 MATHS4 BCD DSC
333 ENG_CHEM CDE GADAG
444 MATHS1 BCD DSC
555 MATHS3 BCD DSC
---------------------------------------------------------------------
Select * from BOOK_ADPT;
COURSENO SEM BOOK_ISBN
--------- --------- ---------
1 5 111
2 4 222
3 2 333
4 1 444
5 3 555
=====================================================================
3. DEMONSTRATE HOW YOU ADD A NEW BOOK TO THE DATABASE AND MAKE THIS BOOK BE ADOPTED BY SOME DEPARTMENT.
---------------------------------------------------------------------
Insert into COURSE_CHOSEN values(6,'JAVA','CSE');
Select * from COURSE_CHOSEN;
COURSENO CNAME DEPT
--------- ---------- ----------
1 DB2 CSE
2 Calculus Maths
3 Organic Chemistry
4 Trig Maths
5 Statistics Maths
6 JAVA CSE
---------------------------------------------------------------------
Insert into TXT values(666,'CR_JAVA','TMH','HS');
Select * from TXT;
BOOK_ISBN BOOK_TITLE PUBLISHER AUTHOR
--------- ---------- ---------- ----------
111 DBMS_study ABC SN
222 MATHS4 BCD DSC
333 ENG_CHEM CDE GADAG
444 MATHS1 BCD DSC
555 MATHS3 BCD DSC
666 CR_JAVA TMH HS
---------------------------------------------------------------------
Insert into BOOK_ADPT values(6,6,111);
Select * from BOOK_ADPT;
COURSENO SEM BOOK_ISBN
--------- --------- ---------
1 5 111
2 4 222
3 2 333
4 1 444
5 3 555
6 6 666
=====================================================================
4. PRODUCE A LIST OF TEXT BOOKS (INCLUDE COURSENO,BOOK_ISBN,BOOK_TITLE) IN ALPHABETICAL ORDER FOR COURSES OFFERED BY THE 'CS' DEPARTMENT THAT USE MORE THAN TWO BOOKS.
---------------------------------------------------------------------
Select C.CourseNo, C.CName, B.Book_ISBN, T.Book_Title
from COURSE_CHOSEN C, BOOK_ADPT B, TXT T
Where C.CourseNo=B.CourseNo
AND B.Book_ISBN=T.Book_ISBN
AND C.CourseNo IN(
Select C1.CourseNo from COURSE_CHOSEN C1,BOOK_ADPT B1
Where C1.CourseNo=B1.CourseNo AND Dept='CSE'
GROUP BY C1.CourseNo
)
ORDER BY Book_Title;
COURSENO CNAME BOOK_ISBN BOOK_TITLE
--------- ---------- --------- ----------
6 JAVA 666 CR_JAVA
1 DB2 111 DBMS_study
=====================================================================
5. LIST ANY DEPARTMENT THAT HAS ALL ITS ADOPTED BOOKS PUBLISHED BY A SPECIFIC NUMBER.
---------------------------------------------------------------------
Select DISTINCT C.Dept,T.Publisher
from COURSE_CHOSEN C, TXT T, BOOK_ADPT B
Where C.CourseNo=B.CourseNo
and T.Book_ISBN=B.Book_ISBN
and T.Publisher='BCD';
DEPT PUBLISHER
---------- ----------
Maths BCD
=====================================================================
LAB PROGRAM #4
=====================================================================
THE FOLLOWING DETAILS ARE 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)
=====================================================================
Drop table AUTHOR;
Drop table PUBLISHER;
Drop table CATALOG;
Drop table CATEGORY;
Drop table ORD_DET;
=====================================================================
1. CREATE THE ABOVE TABLES BY PROPERLY SPECIFYING THE PRIMARY KEYS AND FOREIGN KEYS.
---------------------------------------------------------------------
Create table AUTHOR (
Author_ID Number(10),
Name Varchar2(10),
City Varchar2(10),
Country Varchar2(10),
Primary Key (Author_ID)
);
---------------------------------------------------------------------
Create table PUBLISHER (
Publisher_ID Number(10),
Name Varchar2(10),
City Varchar2(10),
Country Varchar2(10),
Primary Key (Publisher_ID)
);
---------------------------------------------------------------------
Create table CATEGORY (
Category_ID Number(10),
Description Varchar2(10),
Primary Key (Category_ID)
);
---------------------------------------------------------------------
Create table CATALOG (
Book_ID Number(10),
Title Varchar2(10),
Author_ID Number(10),
Publisher_ID Number(10),
Category_ID Number(10),
Year Number(4),
Price Number(5),
Primary Key (Book_ID),
Foreign Key(Category_ID) references CATEGORY(Category_ID),
Foreign Key(Author_ID) references AUTHOR(Author_ID),
Foreign Key(Publisher_ID) references PUBLISHER (Publisher_ID)
);
---------------------------------------------------------------------Create table ORD_DET (
Order_No Number(10),
Book_ID Number(10),
Qty Number(10),
Primary Key (Order_No,Book_ID),
Foreign Key(Book_ID) references CATALOG(Book_ID)
ON DELETE CASCADE
);
=====================================================================
2. ENTER ATLEAST FIVE TUPLES TO EACH RELATION.
---------------------------------------------------------------------
Insert into AUTHOR values(1,'DSC','BANGALORE','INDIA');
Insert into AUTHOR values(2,'kSC','BANGALORE','INDIA');
Insert into AUTHOR values(3,'HERBERT','CALIF','USA');
Insert into AUTHOR values(4,'BGS','BANGALORE','INDIA');
Insert into AUTHOR values(5,'LLB','LONDON','ENGLAND');
---------------------------------------------------------------------
Insert into PUBLISHER values(1,'SUBASH','BANGALORE','INDIA');
Insert into PUBLISHER values(2,'SUBASH','BANGALORE','INDIA');
Insert into PUBLISHER values(3,'TMH','CALIF','USA');
Insert into PUBLISHER values(4,'PRINCETON','BANGALORE','INDIA');
Insert into PUBLISHER values(5,'PRINCETON','LONDON','ENGLAND');
---------------------------------------------------------------------
Insert into CATEGORY values(1,'MATHS');
Insert into CATEGORY values(2,'MATHS');
Insert into CATEGORY values(3,'CPP');
Insert into CATEGORY values(4,'JAVA');
Insert into CATEGORY values(5,'SS');
---------------------------------------------------------------------
Insert into CATALOG values(1,'MATHS1',1,1,1,2006,250);
Insert into CATALOG values(2,'MATHS2',2,2,2,2007,250);
Insert into CATALOG values(3,'CRCPP',3,3,3,2006,450);
Insert into CATALOG values(4,'JAP',4,4,4,2008,300);
Insert into CATALOG values(5,'SSHB',5,5,5,2007,350);
Insert into ORD_DET values(1,1,100);
Insert into ORD_DET values(2,2,200);
Insert into ORD_DET values(3,3,300);
Insert into ORD_DET values(4,4,400);
Insert into ORD_DET values(5,5,500);
=====================================================================
Select * from AUTHOR;
AUTHOR_ID NAME CITY COUNTRY
--------- ---------- ---------- ----------
1 DSC BANGALORE INDIA
2 kSC BANGALORE INDIA
3 HERBERT CALIF USA
4 BGS BANGALORE INDIA
5 LLB LONDON ENGLAND
---------------------------------------------------------------------
Select * from PUBLISHER;
PUBLISHER_ID NAME CITY COUNTRY
------------ ---------- ---------- ----------
1 SUBASH BANGALORE INDIA
2 SUBASH BANGALORE INDIA
3 TMH CALIF USA
4 PRINCETON BANGALORE INDIA
5 PRINCETON LONDON ENGLAND
---------------------------------------------------------------------
Select * from CATEGORY;
CATEGORY_ID DESCRIPTIO
----------- ----------
1 MATHS
2 MATHS
3 CPP
4 JAVA
5 SS
---------------------------------------------------------------------
Select * from CATALOG;
BOOK_ID TITLE AUTHOR_ID PUBLISHER_ID CATEGORY_ID YEAR PRICE
------- ----- --------- ------------ ----------- ----- -----
1 MATHS1 1 1 1 2006 250
2 MATHS2 2 2 2 2007 250
3 CRCPP 3 3 3 2006 450
4 JAP 4 4 4 2008 300
5 SSHB 5 5 5 2007 350
---------------------------------------------------------------------
Select * from ORD_DET;
ORDER_NO BOOK_ID QTY
--------- --------- ---------
1 1 100
2 2 200
3 3 300
4 4 400
5 5 500
=====================================================================
3. Give the details 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 books in the catalog and the year of publication is after 2000.
---------------------------------------------------------------------
Select Author_ID,NAME,CITY,COUNTRY from AUTHOR
WHERE
Author_ID IN
( Select Author_ID from CATALOG
WHERE
YEAR>2000 AND Price>(Select AVG(Price) from CATALOG)
GROUP BY Author_ID HAVING COUNT(*)>=1
);
AUTHOR_ID NAME CITY COUNTRY
--------- ---------- ---------- ----------
3 HERBERT CALIF USA
5 LLB LONDON ENGLAND
=====================================================================
4. Find the author of the book which has maximum sales.
---------------------------------------------------------------------
Select A.Name from AUTHOR A, CATALOG C, ORD_DET O WHERE
A.Author_ID=C.Author_ID AND C.Book_ID=O.Book_ID
AND O.Qty=(Select MAX(Qty) from ORD_DET);
NAME
----------
LLB
=====================================================================
5. Demonstrate how you increase the price of the books published by a specific publisher by 10%.
---------------------------------------------------------------------
Update CATALOG set Price=Price*1.10 WHERE Publisher_ID=1;
Select * from CATALOG;
BOOK_ID TITLE AUTHOR_ID PUBLISHER_ID CATEGORY_ID YEAR PRICE
------- ----- --------- ------------ ----------- ---- ------
1 MATHS1 1 1 1 2006 275
2 MATHS2 2 2 2 2007 250
3 CRCPP 3 3 3 2006 450
4 JAP 4 4 4 2008 300
5 SSHB 5 5 5 2007 350
=====================================================================
LAB PROGRAM #5
=====================================================================
CONSIDER THE FOLLOWING DATABASE FOR THE BANKING ENTERPRISE:
BRANCH(BRANCH-NAME:STRING, BRANCH-CITY:STRING, ASSETS:REAL)
ACCOUNT(ACCNO:INT, BRANCH-NAME:STRING, BALANCE:REAL)
DEPOSITOR(CUSTOMER-NAME:STRING, ACCNO-INT)
CUSTOMER(CUSTOMER-NAME:STRING, CUSTOMER-STREET:STRING,CUSTOMER-CITY:STRING)
LOAN(LOAN-NUMBER:INT, BRANCH:STRING, AMOUNT:REAL)
BORROWER(CUSTOMER-NAME:STRING, LOAN-NUMBER:INT)
=====================================================================
Drop table BRANCH;
Drop table ACCOUNT;
Drop table DEPOSITOR;
Drop table CUSTMER;
Drop table LOAN;
Drop table BORROWER;
=====================================================================
1. CREATE THE ABOVE TABLES BY PROPERLY SPECIFYING THE PRIMARY KEYS AND FOREIGN KEYS.
---------------------------------------------------------------------
Create table BRANCH (
Branch_Name Varchar2(10),
Branch_City Varchar2(10),
Assets Number(10,3),
Primary Key(Branch_Name),
Check(Assets>0)
);
---------------------------------------------------------------------
Create table CUSTMER (
Cust_Name Varchar2(10),
Cust_Addr Varchar2(10),
Cust_City Varchar2(10),
Primary Key(Cust_Name)
);
---------------------------------------------------------------------
Create table ACCOUNT (
Acc_No Varchar2(10),
Branch_Name Varchar2(10),
Bal_No Varchar2(10),
Primary Key(Acc_No),
Foreign Key(Branch_Name) References BRANCH(Branch_Name) ON DELETE CASCADE
);
---------------------------------------------------------------------
Create table DEPOSITOR (
Cust_Name Varchar2(10),
Acc_No Varchar2(10),
Primary Key(Cust_Name,Acc_No),
Foreign Key(Cust_Name) References CUSTMER(Cust_Name) ON DELETE CASCADE,
Foreign Key(Acc_No) References ACCOUNT(Acc_No)
ON DELETE CASCADE
);
---------------------------------------------------------------------
Create table LOAN (
Loan_No Varchar2(10),
Branch_Name Varchar2(10),
Amt Number(10,4),
Primary Key(Loan_No),
Foreign Key(Branch_Name) References BRANCH(Branch_Name) ON DELETE CASCADE
);
---------------------------------------------------------------------
Create table BORROWER (
Cust_Name Varchar2(10),
Loan_No Varchar2(10),
Primary Key(Cust_Name,Loan_No),
Foreign Key(Cust_Name) References CUSTMER(Cust_Name)
Foreign Key(Loan_No) References LOAN(Loan_No)
ON DELETE CASCADE
);
=====================================================================
2. ENTER ATLEAST FIVE TUPLES TO EACH RELATION.
---------------------------------------------------------------------
Insert into BRANCH values('JAYANAGAR','BANGALORE',2000000);
Insert into BRANCH values('J.P.NAGAR','HASSAN',2000000);
Insert into BRANCH values('VIJAYNAGAR','BANGALORE',2000000);
Insert into BRANCH values('R.T.NAGAR','MYSORE',2000000);
Insert into BRANCH values('VIVEKNAGAR','MYSORE',2000000);
---------------------------------------------------------------------
Insert into CUSTMER values('VISHWAS','JAYANAGAR','BANGALORE');
Insert into CUSTMER values('SUHAS','J.P.NAGAR','HASSAN');
Insert into CUSTMER values('VIKRAM','VIJAYNAGAR','BANGALORE');
Insert into CUSTMER values('LOHITH','R.T.NAGAR','MYSORE');
Insert into CUSTMER values('ASHWATH','VIVEKNAGAR','MYSORE');
---------------------------------------------------------------------
Insert into ACCOUNT values('1YD07CS058','JAYANAGAR','10000');
Insert into ACCOUNT values('1YD07CS052','J.P.NAGAR','20000');
Insert into ACCOUNT values('1YD07CS057','VIJAYNAGAR','30000');
Insert into ACCOUNT values('1YD07CS017','R.T.NAGAR','40000');
Insert into ACCOUNT values('1YD07CS005','VIVEKNAGAR','50000');
---------------------------------------------------------------------
Insert into DEPOSITOR values('VISHWAS','1YD07CS058');
Insert into DEPOSITOR values('SUHAS','1YD07CS052');
Insert into DEPOSITOR values('VIKRAM','1YD07CS057');
Insert into DEPOSITOR values('LOHITH','1YD07CS017');
Insert into DEPOSITOR values('ASHWATH','1YD07CS005');
---------------------------------------------------------------------
Insert into LOAN values('LNJ58','JAYANAGAR',50000);
Insert into LOAN values('LNJP52','J.P.NAGAR',25000);
Insert into LOAN values('LNV57','VIJAYNAGAR',5000);
Insert into LOAN values('LNR17','R.T.NAGAR',55000);
Insert into LOAN values('LNVI05','VIVEKNAGAR',30000);
---------------------------------------------------------------------
Insert into BORROWER values('VISHWAS','LNJ58');
Insert into BORROWER values('SUHAS','LNJP52');
Insert into BORROWER values('VIKRAM','LNV57');
Insert into BORROWER values('LOHITH','LNR17');
Insert into BORROWER values('ASHWATH','LNVI05');
=====================================================================
Select * from BRANCH;
BRANCH_NAM BRANCH_CIT ASSETS
---------- ---------- ---------
JAYANAGAR BANGALORE 2000000
J.P.NAGAR HASSAN 2000000
VIJAYNAGAR BANGALORE 2000000
R.T.NAGAR MYSORE 2000000
VIVEKNAGAR MYSORE 2000000
---------------------------------------------------------------------
Select * from CUSTMER;
CUST_NAME CUST_ADDR CUST_CITY
---------- ---------- ----------
VISHWAS JAYANAGAR BANGALORE
SUHAS J.P.NAGAR HASSAN
VIKRAM VIJAYNAGAR BANGALORE
LOHITH R.T.NAGAR MYSORE
ASHWATH VIVEKNAGAR MYSORE
---------------------------------------------------------------------
Select * from ACCOUNT;
ACC_NO BRANCH_NAM BAL_NO
---------- ---------- ----------
1YD07CS058 JAYANAGAR 10000
1YD07CS052 J.P.NAGAR 20000
1YD07CS057 VIJAYNAGAR 30000
1YD07CS017 R.T.NAGAR 40000
1YD07CS005 VIVEKNAGAR 50000
---------------------------------------------------------------------
Select * from DEPOSITOR;
CUST_NAME ACC_NO
---------- ----------
VISHWAS 1YD07CS058
SUHAS 1YD07CS052
VIKRAM 1YD07CS057
LOHITH 1YD07CS017
ASHWATH 1YD07CS005
---------------------------------------------------------------------
Select * from LOAN;
LOAN_NO BRANCH_NAM AMT
---------- ---------- ---------
LNJ58 JAYANAGAR 50000
LNJP52 J.P.NAGAR 25000
LNV57 VIJAYNAGAR 5000
LNR17 R.T.NAGAR 55000
LNVI05 VIVEKNAGAR 30000
---------------------------------------------------------------------
Select * from BORROWER;
CUST_NAME LOAN_NO
---------- ----------
VISHWAS LNJ58
SUHAS LNJP52
VIKRAM LNV57
LOHITH LNR17
ASHWATH LNVI05
=====================================================================
3. Find all the customers who have atleast two accounts at the main branch.
---------------------------------------------------------------------
Select Cust_Name from ACCOUNT A, DEPOSITOR D
where A.Acc_No=D.Acc_No AND Branch_Name='JAYANAGAR'
GROUP BY Cust_Name HAVING Count(*)>1;
---------------------------------------------------------------------
CUST_NAME
----------
VISHWAS
=====================================================================
4. Find all the customers who have an account at all the branches located in a specific city.
---------------------------------------------------------------------
Select * from CUSTMER c WHERE NOT EXISTS
( Select Branch_Name from BRANCH
WHERE Branch_City='BANGALORE'
MINUS
Select A.Branch_Name from ACCOUNT A,DEPOSITOR D
WHERE D.Acc_No=A.Acc_No
AND
C.Cust_Name=D.Cust_Name
);
---------------------------------------------------------------------
NO ROWS SELECTED.
=====================================================================
5. Demonstrate how you delete tuples in ACCOUNT relation at every branch located in a specific city.
---------------------------------------------------------------------
Delete from ACCOUNT WHERE Branch_Name IN
( Select Branch_Name from BRANCH WHERE Branch_City='Bangalore'
);
---------------------------------------------------------------------
select * from ACCOUNT;
ACC_NO BRANCH_NAM BAL_NO
---------- ---------- ----------
1YD07CS052 J.P.NAGAR 20000
1YD07CS017 R.T.NAGAR 40000
1YD07CS005 VIVEKNAGAR 50000
=====================================================================
No comments:
Post a Comment