HIGHER AND TECHNICAL INSTITUTE, MIZORAM

Department of Computer Science

Kawmzawl, Pukpui, Lunglei – 796691

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

1 Create the following Table

2 Insurance Database

3 Order Processing Database

Students enrolment in course and books adopted

4
for each course

5 Book Dealer Database

6 Banking Enterprise Database

Q1. Create the following Tables:

Salesman
Create Table Salesman
(
SNUM number(4),
SNAME char(10),
CITY char(10),
COMMISSION number(2),
primary key (SNUM));

insert into Salesman(SNUM,SNAME,CITY,COMMISSION)

values(1001,'PIYUSH','LONDON',12); insert into
Salesman(SNUM,SNAME,CITY,COMMISSION)
values(1002,'NIRAJ','SURAT',13);
insert into Salesman(SNUM,SNAME,CITY,COMMISSION)
values(1003,'MITI','LONDON',11);
insert into Salesman(SNUM,SNAME,CITY,COMMISSION)
values(1004,'RAJESH','BARODA',15) insert into
Salesman(SNUM,SNAME,CITY,COMMISSION)
values(1005,'ANAND,'NEW DELHI',10) insert into
Salesman(SNUM,SNAME,CITY,COMMISSION)
values(1006,'RAM','PATANT',10)
insert into Salesman(SNUM,SNAME,CITY,COMMISSION)
values(1007,'LAXMAN','BOMBAY',09)

OUTPUT: Select * from salesman

Customer
Create Table Customer
(
CNUM number(4),
CNAME char(10),
CITY char(10),
RATING number(3),
SNUM number(4) Primary
key(CNUM));
insert into Customer (CNUM,CNAME,CITY,RATING,SNUM)
values(2001,'HARDIK','LONDON',100,1001) insert into
Customer (CNUM,CNAME,CITY,RATING,SNUM)
values(2002,'GITA','ROME',200,1003) insert into
Customer (CNUM,CNAME,CITY,RATING,SNUM)
values(2003,'LAXIT','SURAT',200,1002) insert into
Customer (CNUM,CNAME,CITY,RATING,SNUM)
values(2004,'GOVIND','BOMBAY',300,1002) insert into
Customer (CNUM,CNAME,CITY,RATING,SNUM)
values(2005,'CHANDU','LONDON',100,1001) insert into
Customer (CNUM,CNAME,CITY,RATING,SNUM)
values(2006,'CHAMPAK','SURAT',300,1007) insert into
Customer (CNUM,CNAME,CITY,RATING,SNUM)
values(2007,'PRATIK','ROME',100,1004)

OUTPUT: Select * from customer

Orders
Create Table Orders
(
ONUM number(4),
AMOUNT number(10),
ODATE date,
CNUM number(4), SNUM
number(4), primary
key(ONUM));

insert into Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)

values(3001,18.69,TO_DATE(’10 /03/1999’,’DD/MM/YYYY’),2008,1007); insert
into Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)
values(3002,767.19,TO_DATE(‘10/03/1999’,’ DD/MM/YYYY’),2001,1001);
insert into Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)
values(3003,1900.10, TO_DATE(‘10/03/1999’,’DD/MM/YYYY’),,2007,1004);
insert into Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)
values(3004,5160.45, TO_DATE(‘10/03/99’,’DD/MM/YYYY’),2003,1002);
insert into Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)
values(3005,1098.25, TO_DATE(‘10/04/99’,’DD/MM/YYYY’),2008,1007);
insert into Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)
values(3006,1713.12, TO_DATE(‘10/04/99’,’DD/MM/YYYY’),2002,1003);
insert into Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM) values(3007,75.75,
TO_DATE(‘10/05/99’,’DD/MM/YYYY’),2004,1002); insert into
Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM) values(3008,4723.00,
TO_DATE(‘10/05/99’,’DD/MM/YYYY’),2006,1001); insert into
Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM) values(3009,1309.95,
TO_DATE(‘10/05/99’,’DD/MM/YYYY’),2004,1002); insert into
Orders(ONUM,AMOUNT,ODATE,CNUM,SNUM)
values(3010,9898.87, TO_DATE(‘10/06/99’,’DD/MM/YYYY’),2006,1001);

OUTPUT: Select * from orders

a) List of all orders for more than Rs.1000 Ans: select * from orders
where amount>1000;

OUTPUT:

b) List all customers whose name begins with a letter ‘C’

Ans: select * from customer where cname like 'C%';

OUTPUT:

c) Count all Orders of 10th March 1999

Ans: select count(odate) from orders where odate='10-Mar-1999';

OUTPUT:

d) List all customers serviced by salesman with commission above 12%.

Ans: select cname, sname, commission from customer, salesman
where salesman.commission>12 and customer.snum=salesman.snum;
OUTPUT:
e) Produce the name and rating of all customers who have above
average orders.
Ans: select cname, rating from orders, customer where
orders.cnum=customer.cnum and amount>(Select avg(Amount) from orders);

OUTPUT:

f) Double the commission of all salesmen of London.

Ans: update salesman set commission=commission*2 where city='LONDON';

OUTPUT:

g) Calculate the total of orders for each day.

Ans: select sum(AMOUNT), odate from orders group by odate

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));

INSERT INTO person VALUES(1,'Lalchungnunga','salem');

INSERT INTO person VALUES(2,'Saithangpuia','salem');
insert into person values(3,'Abednego','hnahthial'); insert
into person values(4,'Nghakmawia','hnahthial'); insert
into person values(5,'Abraham','college veng');

OUTPUT: Select * from person

Car
create table car
(
Regno number(4), Model char(20), year number(5), primary
key(Regno)) insert into car(Regno,Model,Year)
values(1001,’Maruti’,2005); insert into car(Regno,Model,Year)
values(1002,’Maruti’,2007); insert into car(Regno,Model,Year)
values(1003,’Fort’,1999); insert into car(Regno,Model,Year)
values(1004,’Fort’,2000); insert into car(Regno,Model,Year)
values(1005,’Mahindra’,2011);

OUTPUT: Select * from car

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));

insert into owns(DriverID,Regno) values(1,1001); insert

into owns(DriverID,Regno) values(2,1002); insert into
owns(DriverID,Regno) values(3,1003); insert into
owns(DriverID,Regno) values(4,1004); insert into
owns(DriverID,Regno) values(5,1005);

OUTPUT: Select * from owns

Participated
create table participated
(
DriverID number(2),
Regno number(4),
Report_Number number(4),
Damage_Amount number(5), primary
key(DriverID));

insert into owns(DriverID,Regno,Report_Number,Damage_Amount)

values(1,1001,5,7500);
insert into owns(DriverID,Regno,Report_Number,Damage_Amount)
values(2,1002,4,4000);
insert into owns(DriverID,Regno,Report_Number,Damage_Amount)
values(3,1003,3,8500);
insert into owns(DriverID,Regno,Report_Number,Damage_Amount)
values(4,1004,2,10000);
insert into owns(DriverID,Regno,Report_Number,Damage_Amount)
values(5,1005,1,500);

OUTPUT: Select * from participated

i. Demonstrate how you -

a) update the damage amount for the car with a specific
regno in accident with
 report number 3 to Rs.25000

Ans: update participated set damage_Amount=25000 where regno=1003

OUTPUT:

Select * from participated

OUTPUT:

b) add a new accident to the database

Ans: insert into accident(Report_number,accident_date,location)
values(6,'5March-2017','Ramthar')

OUTPUT:

Select * from accident

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:

iv. Select location of accident using subquery.

Ans: select * from participated p where p.report_number=(select
Report_Number from accident a where location='Serkawn' and
a.report_number=p.report_number)

OUTPUT:
v. Find the total number of people who owned cars that were involved
in accidents in 2015.

Ans: select count(*) from accident where to_char(Accident_date,'yyyy')=2015;

OUTPUT:

vi. Find the number of accidents in which cars belonging to a specific

model were involved.

Ans: select count(*) Total_Car_Accident from participated, car where

participated.Regno=car.Regno and model='Maruti';

OUTPUT:

vii. Create a VIEW called OWNERSHIP that will appear the following:
Driver name, address, registration and location

Ans: CREATE view ownership as select name, address,Regno,location

from person,participated,accident where
person.driverID=participated.Driver_ID and
participated.report_number=accident.Report_Number;

OUTPUT:

Select * from ownership

OUTPUT:

viii. Remove the model column from the CAR table.

Ans: alter table car drop column model

OUTPUT:

Select * from car

OUTPUT:

ORDER PROCESSING DATABASE

Q3. 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: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));

insert into customer3(cust,cname,city)

values(101,'Lalchungnunga','Hyderabad') insert into
customer3(cust,cname,city) values(102,'Saithangpuia','London') insert into
customer3(cust,cname,city) values(103,'Abednego','New Delhi') insert into
customer3(cust,cname,city) values(104,'Nghakmawia','Aizawl') insert into
customer3(cust,cname,city) values(105,'Abraham','Aizawl')

OUTPUT: Select * from customer3

Order
Create Table Order3 (Order0 number(10),
Odate date,
Cust number(10),
Ord_amt number(10),
Primary key(Order0));

insert into order3 (Order0,Odate,Cust,Ord_amt) values(1,'2-Sep-

2015',103,7500)
insert into order3 (Order0,Odate,Cust,Ord_amt) values(2,'1-Jul-
2016',102,24000)
insert into order3 (Order0,Odate,Cust,Ord_amt) values(3,'2-Sep-
2015',101,2000)
insert into order3 (Order0,Odate,Cust,Ord_amt) values(4,'1-Jul-
2016',105,3000)
insert into order3 (Order0,Odate,Cust,Ord_amt) values(5,'2-Sep-
2015',104,1500)

OUTPUT: select * from order3

Order_Item
Create Table Order_Item(
Order0 number(10),
Item char(20),
Quantity number(10),
Primary key(Order0));

insert into order_Item (Order0,Item,Quantity) values(1,'Graphics Card',3)

insert into order_Item (Order0,Item,Quantity) values(2,'Projector Screen',1)
insert into order_Item (Order0,Item,Quantity) values(3,'Mouse',4) insert
into order_Item (Order0,Item,Quantity) values(4,'Keyboard',5) insert into
order_Item (Order0,Item,Quantity) values(5,'UPS',2)
OUTPUT: select * from order_item
Item
Create Table Item
(
Item char(20),
Unit_Price number(10),
Primary key(Item));

insert into Item (Item,Unit_Price) values('Graphics Card',2500)

insert into Item (Item,Unit_Price) values('Projector Screen',24000)
insert into Item (Item,Unit_Price) values('Mouse',500) insert into
Item (Item,Unit_Price) values('Keyboard',600) insert into Item
(Item,Unit_Price) values('UPS',750)

OUTPUT: select * from item

Shipment
Create Table Shipment
(
Order3 number(3),
Warehouse char(20),
ShipDate date,
Primary key(Order3));

insert into Shipment (Order3,Warehouse,ShipDate) values(1,'Indian

Post','7Sep-2015')
insert into Shipment (Order3,Warehouse,ShipDate) values(2,'Fed Ex','4-Jul-
2016')
insert into Shipment (Order3,Warehouse,ShipDate) values(3,'Blue Dart','9-Sep-
2015') insert into Shipment (Order3,Warehouse,ShipDate) values(4,'Gati','6-Jul-
2016') insert into Shipment (Order3,Warehouse,ShipDate) values(5,'Safe
Express','5Sep-2015')

OUTPUT: select * from shipment

Warehouse
Create Table Warehouse
(
Warehouse char(20),
City char(20),
Primary key(Warehouse));

insert into Warehouse (Warehouse,City) values('Indian Post','Hyderabad')

insert into Warehouse (Warehouse,City) values('Fed Ex','London') insert
into Warehouse (Warehouse,City) values('Blue Dart','New Delhi') insert
into Warehouse (Warehouse,City) values('Gati','Aizawl') insert into
Warehouse (Warehouse,City) values('Safe Express','Aizawl') OUTPUT:
Select * from warehouse
a) Produce a listing: CUSTNAME,# of orders,
AVG_ORDER_AMT, where the middle column is the total
no of orders by the customer and the last column is the
average order amount for that customer.

Ans: Select cname "Customer_Name",count(*) "No. of Orders",avg(Ord_amt)

"Average Order Amount" from order3 o,customer3 c where c.cust=o.cust
group by cname

OUTPUT:

b) List the order # for orders that were shipped from all
warehouses that the company has in a specified city.

Ans: select order3 from shipment s.warehouse w where

w.warehouse=s.warehouse and w.city='Aizawl'

OUTPUT:

c) Demonstrate how you delete item #10 from ITEM table

and make the field null in the ORDER_ITEM table.
Ans: delete from item where item='UPS'

OUTPUT:

select * from item

OUTPUT:

d) List the orders date, items and unit price. Ans: select
odate,item,unit_Price from order3,item

OUTPUT:

e) Calculate the total of orders for each day.

Ans: select count(order0) from order3 where odate='2-Sep-2015'

OUTPUT:

f) Find out which unit price is lowest.

Ans: select min(Unit_Price) "Minimum Unit Price" from item

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:

Select * from big

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));

insert into student values(101,'Lalchungnunga','Economics','8-Feb-1997')

insert into student values(102,'Saithangpuia','Physics', '23-May-1995')
insert into student values(103, 'Abednego','Maths', '4-Jul-1996') insert
into student values(104,'Nghakmawia','Graphics', '3-Sep-1993') insert
into student values(105,'Abraham','Chemistry', '15-Mar-1994')

OUTPUT: select * from student

Course
Create table course
(
Course number(10),
cname char(20), dept
char(10), Primary key
(course));

insert into course values(401,'Oracle','BCA')

insert into course values(402,'E&E','BA') insert
into course values(403,'GUI','B.Com') insert into
course values(404,'Networking','BCA')
insert into course values(405,'Sociology','BSW')

OUTPUT: select * from course

Enroll
Create table enroll
(
Regno number(3),
Course number(10),
Mark number(5),
Primary key (Regno));

insert into enroll values(101,401,97) insert

into enroll values(102,402,58) insert into
enroll values(103,403,69) insert into
enroll values(104,404,75) insert into
enroll values(105,405,64) OUTPUT:
select * from enroll
Book_adoption
Create table book_adoption
(
course number(3), sem
number(3), book_ISBN
number(5),
Primary key (course));

insert into book_adoption values(401,4,221) insert

into book_adoption values(402,4,222) insert into
book_adoption values(403,2,223) insert into
book_adoption values(404,2,224) insert into
book_adoption values(405,6,225)

OUTPUT: select * from book_adoption

Text
Create table text
(
book_ISBN number(5),
book_title char(15),
publisher char(20), author
char(10), Primary key
(book_ISBN));

insert into text values(221,'Learning Oracle','Leitlangpui','Jerome')

insert into text values(222,'EVS','Zobawm','Mawia') insert into text
values(223,'Visual Basic','Leitlangpui','Zorindika') insert into text
values(224,'Communication','Zobawm','Neihthangi') insert into text
values(225,'Social Values','Vital','Dinpuia')

OUTPUT: select * from text

a) Demonstrate how you add a new text book to the

database Ans: insert into text values(226,'English
Grammer','Zobawm','Nathan')

OUTPUT:

b) Produce a list of text books in alphabetical order for

courses offered by
BCA department that use more than two books
Ans: select book_title,dept from course c,text t,book_adoption b where
t.book_isbn=b.book_isbn and b.course=c.course and c.dept='BCA' order by
book_title

OUTPUT:

c) List any department that has all its adopted books

published by a specific publisher

Ans: select publisher,dept from text t,course c, book_adoption b where

t.publisher='Leitlangpui' and t.book_isbn=b.book_isbn and b.course=c.course
and dept='B.Com'

OUTPUT:

d) Select marks of the student using sub query.

Ans: select name,mark from enroll e,student s where mark in (select mark from
enroll where mark>65) and e.regno=s.regno

OUTPUT:

e) List out student marks in ascending order

Ans: select mark from enroll order by mark
OUTPUT:

f) Add new column position in enroll table Ans: alter table

enroll add position number(10)

OUTPUT: select * from enrol

g) Create a view Black Market that gives the count of no.

of publisher. Ans: create view Black_Markets
(No_of_Publisher) as select count(name) from publisher

OUTPUT: select * from black_markets

h) Delete the student bdate from the student table.

Ans: alter table student drop column bdate

OUTPUT:
BOOK DEALER DATABASE

Q5. The following tables 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,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));

insert into author values(101,'Richard','Hyderabad','India')

insert into author values(102,'Henry','Chennai','India')
insert into author values(103,'Nathan','New York','USA')
insert into author values(104,'Rachel','Aizawl','India') insert
into author values(105,'Jacob','New York','USA')

OUTPUT: select * from author

Publisher Table
Create table publisher
(
publisher_id number(5),
name char(15), city
char(15), Country
char(20),
Primary key (publisher_id));

insert into publisher values(201,'Remruata','Hyderabad','India')

insert into publisher values(202,'Zorina','Rio','Brazil') insert
into publisher values(203,'Lalbera','Chennai','India') insert into
publisher values(204,'Mami','Aizawl','India') insert into
publisher values(205,'Dinpuia','New York','USA')

OUTPUT: select * from publisher

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)

OUTPUT: select * from catalog5

Category Table
Create table Category
(
category_id number(5),
description char(30), Primary
key (category_id));

insert into category values(401, 'Best Seller of the year')

insert into category values(402, 'Inspired by true story')
insert into category values(403, 'Learn Programming')
insert into category values(404, 'Are you Saved?') insert
into category values(405, 'Managing Database')

OUTPUT: select * from category

Order_details
Create table Order_details
(
Order_no number(5),
Book_id number(3),
Quantity number(3),
Primary key (order_no));

insert into order_details values(501,1,20)

insert into order_details values(502,2,50)
insert into order_details values(503,3,7) insert
into order_details values(504,4,15) insert into
order_details values(505,5,30)

OUTPUT: select * from order_details

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

Ans: select a.author_id, a.name from author a, catalog5 c, order_details o
where a.author_id=c.author_id and o.book_id=c.book_id and o.book_id
in(select book_id from order_details where quantity=(select max(quantity)
from order_details))

OUTPUT:
b) Demonstrate how you increase the price of books
published by a specific publisher by 10%

Ans: update catalog5 set price = price*1.1 where publisher_id=201

OUTPUT:

c) List all authors whose name begins with a letter ‘L’.

Ans: select name from author where name like 'L%'

OUTPUT:

d) Select the price and author using subquery.

Ans: select price,name from catalog5,author where name=(select name from
author where author.author_id=catalog5.author_id)

OUTPUT:
e) Select the order detail ordered by quantity. Ans: Select
* from order_details order by quantity

OUTPUT:

f) Shows the total and average quantity of book order

Ans: select count(quantity),avg(quantity) from
order_details

OUTPUT:

g) Create a view called Booking which shows author

name, book id, price, and year
Ans: create view Booking as select name,book_id,price,year from
author,catalog5 where name=(select name from author where
author.author_id=catalog5.author_id)

OUTPUT: select * from booking

h) Delete the quantity of book orders.

Ans: alter table order_details drop column quantity

OUTPUT: select * from order_details

BANKING ENTERPRISE DATABASE

Q6. 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)
DEPOSITOR (customer-name:string,accno:int, loan_no-int)
CUSTOMER (customer-name:string,customer-street:string,city:string)
LOAN (loan-number:int,branch-name:string,loan-number-int)
BORROWER (customer-name:string,customer-street:string,city:string,
accnoint)

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));

insert into branch values('Dawrpui','Aizawl','407 Truck')

insert into branch values('Bazar','Lunglei', 'Mini Van')
insert into branch values('Chanmari','Aizawl','Building')
insert into branch values('Noida','Silchar','Train') insert
into branch values('Thenzawl','Serchhip','JCB')

OUTPUT: select * from branch

Account create
table account
(
Accno number(5),
branch_name char(20),
balance number(10), primary
key(Accno));

insert into account values(3140,'Bazar',15000) insert

into account values(3141,'Noida',25000) insert into
account values(3142,'Dawrpui',20000) insert into
account values(3143,'Chanmari',30000)
insert into account values(3144,'Thenzawl',40000)

OUTPUT: select * from account

Depositor create
table depositor
(
customer_name char(20),
Accno number(5), loan_no
number(3),
primary key(loan_no));

insert into depositor values('Remruata',3140,101)

insert into depositor values('Jonathan',3141,102) insert
into depositor values('Joela',3142,103) insert into
depositor values('Joela',3143,104) insert into
depositor values('Uday Aditya',3144,105) OUTPUT:
select * from depositor
Customer6
create table customer6
(
customer_name char(20),
customer_street char(20), city
char(20),
primary key(customer_name));

insert into customer6 values('Rka','Parallel Road','Lunglei') insert

into customer6 values('Pca','Venglai Road','Serchhip') insert into
customer6 values('Nuntluanga','Chanmari Road','Aizawl') insert into
customer6 values('Nathan','St.Peter’s Road','Silchar') insert into
customer6 values('Akima','Moses Road','Aizawl')

OUTPUT: select * from customer6

Loan
create table loan
(
loan_no number(5),
branch_name char(20),
amount number(20), primary
key(loan_no));

insert into loan values(101,'Bazar',100000)

insert into loan values(102,'Noida',15000) insert
into loan values(103,'Dawrpui',35000) insert
into loan values(104,'Chanmari',50000)
insert into loan values(105,'Thenzawl',75000)

OUTPUT: select * from loan

Borrower
create table borrower
(
customer_name char(20), cusromer_street
char(20),
city char(20), accno
number(5),
primary key(customer_name));

insert into borrower values('stephen','venglai Street','Lunglei',3140) insert

into borrower values('Vena','Pangkai Street','Aizawl',3141) insert into
borrower values('Andrew','Theipalingkawh Street','Aizawl',3142) insert into
borrower values('Duhawma','Tuikhur Street','Serchhip',3143) insert into
borrower values('Ruatfela','Gandhi Street','Silchar',3144) OUTPUT:
select * from borrower
a) Find all the customers who have an account at all the branches located
in a specified City

Ans: select distinct customer_name from depositor where accno in (select

accno from account where branch_name in(select branch_name from branch
where branch_city ='Aizawl'))

OUTPUT:

b) Find the average loan taken in any branch

Ans: select branch_name,avg(amount) from loan group by branch_name

OUTPUT:

c) Select the borrower name and balance using sub query.

Ans: select customer_name,balance from borrower b, account a where
a.accno=b.accno and balance in (select balance from account)
OUTPUT:

d) Find the lowest and highest balance in account table.

Ans: select min(balance),max(balance) from account

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

Ans: create view Personal_Loan as select customer_name,accno,amount from

loan l,depositor d where l.loan_no=d.loan_no
OUTPUT: select * from Personal_Loan

g) Demonstrate how you delete all account tuples at every branch

located in a specified City

Ans: delete from account where branch_name in(select branch_name from

branch b where branch_city='Aizawl')

OUTPUT:

OUTPUT: select * from account