Practical Record Book - Oracle Lab
Practical Record Book - Oracle Lab
SUBJECT:
ORACLE LAB
Name :
Roll No. :
Reg. No. :
Semester :III Semester
Course Code :BCA/3/CC/17
Table of Content
Experiment
No. Program Practical Remarks
2 Insurance Database
Salesman
Create Table Salesman
(
SNUM number(4),
SNAME char(10),
CITY char(10),
COMMISSION number(2),
primary key (SNUM));
Orders
Create Table Orders
(
ONUM number(4),
AMOUNT number(10),
ODATE date,
CNUM number(4), SNUM
number(4), primary
key(ONUM));
OUTPUT:
OUTPUT:
OUTPUT:
OUTPUT:
OUTPUT:
OUTPUT:
h) Create a view called Big orders which stores all orders larger than
Rs.4000. Ans: create view BigOrders as select * from Orders where
amount>4000;
OUTPUT:
i) Create a view that shows all the customers who have the highest
ratings. Ans: create view Highest_Ratings as select cname,city,rating
from customer where rating=(select max(rating) from customer);
OUTPUT:
j) Remove all orders of customer Chandu from the Orders table Ans:
delete from orders where cnum=(select cnum from customer where
cname='CHANDU');
OUTPUT:
INSURANCE DATABASE
Q2. 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,damageamount:int)
1. Create the above tables by properly specifying the primary keys and the
foreign keys.
2. Enter at least five tuples for each relation
Person
create table person
(
DriverID number(3),
name char(20), address
char(20),
primary key(DriverID));
Accident
create table accident
(
Report_number number(3),
Accident_Date date, Location
char(20), primary
key(Report_number));
insert into accident (Report_number,Accident_Date,Location) values(1,'15-feb-
2016',’Serkawn’);
insert into accident (Report_number,Accident_Date,Location) values(2,'7-Mar-
2017’,’Rahsiveng’);
insert into accident (Report_number,Accident_Date,Location) values(3,'9-feb-
2015',’Aizawl’);
insert into accident (Report_number,Accident_Date,Location) values(4,’4-Oct-
2014',’Venglai’);
insert into accident (Report_number,Accident_Date,Location) values(5,'6-
May2015',’Chanmari’);
OUTPUT: Select * from accident
Owns
create table owns
(
DriverID number(3), Regno
number(4),
primary key(DriverID));
Participated
create table participated
(
DriverID number(2),
Regno number(4),
Report_Number number(4),
Damage_Amount number(5), primary
key(DriverID));
OUTPUT:
OUTPUT:
OUTPUT:
OUTPUT:
ii. Calculate the total damage amount
Ans: select sum(damage_amount) Total_Damage_Amount from participated
OUTPUT:
iii. Find the lowest and highest amount of money spent in accident. Ans:
select max (damage_amount) ,min(damage_amount) from participated
OUTPUT:
OUTPUT:
v. Find the total number of people who owned cars that were involved
in accidents in 2015.
OUTPUT:
OUTPUT:
vii. Create a VIEW called OWNERSHIP that will appear the following:
Driver name, address, registration and location
OUTPUT:
OUTPUT:
OUTPUT:
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:char,unitprice:int)
SHIPMENT (order:int,warehouse:int,ship-date:date)
WAREHOUSE (warehouse:int,city:string)
1. Create the above tables by properly specifying the primary keys and the
foreign keys.
2. Enter at least five tuples for each relation.
Customer
Create Table customer3(
cust number(5), cname
Char(20), city char(20),
primary key(cust));
Order
Create Table Order3 (Order0 number(10),
Odate date,
Cust number(10),
Ord_amt number(10),
Primary key(Order0));
Order_Item
Create Table Order_Item(
Order0 number(10),
Item char(20),
Quantity number(10),
Primary key(Order0));
Shipment
Create Table Shipment
(
Order3 number(3),
Warehouse char(20),
ShipDate date,
Primary key(Order3));
Warehouse
Create Table Warehouse
(
Warehouse char(20),
City char(20),
Primary key(Warehouse));
OUTPUT:
b) List the order # for orders that were shipped from all
warehouses that the company has in a specified city.
OUTPUT:
OUTPUT:
OUTPUT:
d) List the orders date, items and unit price. Ans: select
odate,item,unit_Price from order3,item
OUTPUT:
OUTPUT:
OUTPUT:
g) Create a VIEW called Big which show all orders larger than
` 2000. Ans: Create view Big as select order0, Ord_amt from
order3 where ord_amt>2000
OUTPUT:
OUTPUT:
h) Select unit price in order processing using subquery. Ans:
select item ,unit_Price from item where unit_price=(select
max(Unit_Price) from item)
OUTPUT:
STUDENTS ENROLMENT IN COURSE AND BOOKS ADPOTED FOR EACH
COURSE
Q4. Consider the following database of student enrolment in courses and books
adopted for each course -
STUDENT (regno:string,name:string,major:string,bdate:date)
COURSE (course:int,cname:string,dept:string)
ENROLL (regno:string,course:int,marks:int)
BOOK_ADOPTION (course:int,sem:int,book-ISBN:int)
TEXT (book-ISBN:int,book-title:string,publisher:string,author:string)
1. Create the above tables by properly specifying the primary keys and
foreign keys
2. Enter five tuples for each relation
Customer
Create table student
(
Regno char(10),
Name char(20),
Major char(10),
Bdate date,
Primary key (Regno));
Enroll
Create table enroll
(
Regno number(3),
Course number(10),
Mark number(5),
Primary key (Regno));
Text
Create table text
(
book_ISBN number(5),
book_title char(15),
publisher char(20), author
char(10), Primary key
(book_ISBN));
OUTPUT:
OUTPUT:
OUTPUT:
OUTPUT:
OUTPUT:
BOOK DEALER DATABASE
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,categoryid:
int,year:int,price:int)
CATEGORY (category-id:int,description:script)
ORDER-DETAILS (order-no:int,book-id:int,quantity:int)
1. Create the above details by properly specifying the primary keys and
foreign keys
2. Enter at least five tuples for each relation
Author Table.
Create table author
(
author_id number(5),
name char(15), city
char(15), Country
char(20),
Primary key (author_id));
Catalog Table
Create table catalog5
(
book_id number(5),
title char(20), author_id
number(5), publisher_id
number(5), category_id
number(5), year
number(5), price
number(10), Primary
key (book_id)); insert
into catalog5 values(1,'3
Mistakes',101,201,401,
2007,150) insert into
catalog5 values(2,'Road
to
success',102,202,402,20
10,200) insert into
catalog5
values(3,'Visual
Basic',103,203,403,2010
,750) insert into
catalog5
values(4,'Heaven and
Hell',104,204,404,2014,
300) insert into
catalog5
values(5,'DBMS',105,20
5,405,1999,500)
Category Table
Create table Category
(
category_id number(5),
description char(30), Primary
key (category_id));
OUTPUT:
b) Demonstrate how you increase the price of books
published by a specific publisher by 10%
OUTPUT:
OUTPUT:
OUTPUT:
e) Select the order detail ordered by quantity. Ans: Select
* from order_details order by quantity
OUTPUT:
OUTPUT:
1. Create the above tables by properly specifying the primary and foreign
keys
2. Enter 5 tuples for each relation
Branch
create table branch
(
branch_name char(20),
branch_city char(20), assets
char(20),
primary key(branch_name));
Depositor create
table depositor
(
customer_name char(20),
Accno number(5), loan_no
number(3),
primary key(loan_no));
Loan
create table loan
(
loan_no number(5),
branch_name char(20),
amount number(20), primary
key(loan_no));
Borrower
create table borrower
(
customer_name char(20), cusromer_street
char(20),
city char(20), accno
number(5),
primary key(customer_name));
OUTPUT:
OUTPUT:
OUTPUT:
e) Find the customer Jonathan who take loan from the Noida branch.
Ans: select customer_name from depositor where loan_no=(select
loan_no from loan where branch_name='Noida')
OUTPUT:
f) Create a view called Personal loan that shows customer name, account
no and loan
OUTPUT: