DATABASE MANAGEMENT SYSTEM LAB.pdf

Database Management System Lab Manual
Prof. K. Adisesha 1
DATABASE MANAGEMENT SYSTEM LAB
PART – A
1. The STUDENT detail databases has a table with the following attributes. The primarykeys are
underlined. STUDENT (regno: int, name: string, dob: date, marks: int)
a) Create the above table.
b) Remove the existing attributes from the table.
c) Change the date type of regno from integer to string.
d) Add a new attribute phoneno to the existing table.
e) Enter five tuples into the table.
f) Display all the tuples in student table.
g) Delete all the tuples in student table.
h) Delete student table.
Answer
1. Create table studentdb (regno number(10) primary key, name varchar(20), dob date, marks
number(10));
2. Desc studentdb;
3. Alter table studentdb drop column marks;
4. Alter table studentdb modify regno varchar2(20) ;
5. Alter table studentdb add phoneno number(10) ;
6. Desc studentdb;
7. Insert into studentdb values(&regno, &name, &dob, &phoneno);
8. Select * from studentdb;
9. Truncate table studentdb;
10. Drop table studentdb;
****************************************
2. A LIBRARY database has a table with the following attributes.
LIBRARY(bookid:int, title:string, author:string, publication:string, yearpub:int, price:real)
a) Create the above table and display attributes.
b) Enter the five tuples into the table
c) Display all the tuples in library table.
d) Display the different publishers from the list.
e) Arrange the tuples in the alphabetical order of the book titles.
f) List the details of all the books whose price ranges between Rs. 100 and Rs. 300
Answer
1. Create table librarydb (bookidnumber(10) primary key, title varchar2(20),author varchar2(20),
publication varchar2(20), year number(5),price number(6,2));
2. Desclibrarydb;

Prof. K. Adisesha 2
3. Insert into librarydb values(&bookid, &title, &author, &publication, &year, &price);
4. Select * from librarydb;
5. Select distinct publication from librarydb;
6. Select * from librarydb order by title asc;
7. Select * from librarydb where price between 100 and 300;
*****************************
3. The SALARY database of an organization has a table with the following attributes.
EMPSALARY(empcod:int, empnamee:string, dob:date, department:string, salary:real)
a) Create the above table and display table attributes.
b) Enter the five tuples into the table
c) Display all the employees working in each department
d) Display all the employees working in particular department
e) Display all the number of employees working in department.
f) Find the sum of the salaries of all employees.
g) Find the sum and average of the salaries of employees of a particular department.
h) Find the least and highest salaries that an employee draws.
Answers
1. Create table salarydb(empcode number(10) primary key, empname varchar2(20), dob date,dept
varchar2(15),salary number(10,2));
2. Descsalarydb;
3. Insert into salarydb values(&empcode, &empname,&dob, &dept, &salary) ;
4. Select * from salarydb;
5. Select * from salarydb where dept= “computer”;
6. Select dept, count(*) from salarydb group by dept;
7. Select sum(salary) from salarydb;
8. Select sum(salary), avg(salary) from salarydb where dept= “computer”;
9. Select min(salary) from salarydb;
10. Select max(salary) from salarydb;
*****************************
4. Consider the insurance database given below. The primary keys are underlined and the data types
are specified.
PERSON(driver-id-no: string, name: string, address: string)
CAR(regno: string, model: string, year: int)
ACCIDENT(report-no: int, date: date, location: String)
OWNS(driver-id-no: string, regno: string)
PARTICIPATED(driver-id-no: string, regno: string, report-no: int, damage-amount: int)
a) Create the above tables by properly specifying the primary keys and the foreign keys
b) Enter at least five tuples for each relation.
c) Demonstrate how you

Prof. K. Adisesha 3
i) Update the damage amount for the car with specific regno in the accident with report no 12 to
25000.
ii) Add a new accident to the database.
d) Find total number of people who owned cars that were involved in accidents in 2022
e) Find the number of accidents in which cars belonging to a specific model were involved
Answers
1. Create table persondb(driver_id varchar2(20) primary key, name varchar2(20) not null, address
varchar2(30));
2. Create table cardb(regno varchar2(20) primary key, model varchar2(20) not null, year number(5));
3. Create table accidentdb (report_no number(10) primary key, accident_date date, location
varchar2(20));
4. Create table ownsdb (driver_id varchar2(20) references persondb, regno varchar2(20) references
cardb) ;
5. Create table participateddb (driver_id varchar2(20) references persondb, regno varchar2(20);
references cardb, report_no number(10) references accidentdb, damage_amount number(10));
6. Insert into persondb values (&driver_id, &name, &address);
7. Insert into cardb values (&regno, &model, &year);
8. Insert into accidentdb values (&report_no, &accident_date, &location);
9. Insert into ownsdb values(&driver_id, &reg_no) ;
10. Insert into participateddb values(&driver_id, &reg_no, &report_no, &damage_amount);
11. Update participateddb set damage_amount = 25000 where reg_no = 1001 and report_no = 12;
12. Insert into participateddb values (1004,2004,10,3000) ;
13. Select count(*) from accidentdb where accident_date LIKE ‘%-%-22’;
14. Select count(*) from cardb C , participateddb P where C.regno = P.regno and C.model = ‘scoda’;
*****************************
5. Consider the following database of students enrollment in courses and books adopted for each
course.
STUDENT(regno: string, name: string, major: string, bdate: date)
COURSE(course-no: int cname: string, dept: string)
ENROLL(reg-no: string, course-no: int, sem: int, marks: int)
BOOK-ADOPTION(course-no: int, sem: int, book-isbn: int)
TEXT(book-isbn: int, book-title: string, publisher: string, author: string)
b) Enter atleast five tuples for each relation.
c) Demonstrate how you add a new text book to the database and make this book be adopted by some
department.
d) Produce a list of text books (include Course-no, book-isbn, book-title) in the alphabetical order for
courses offered by the ‘Compute Science’ department that use more than two books.
e) List any department that has all its adopted books published by a specific publisher.
Answers
1. Create table student (regno varchar2(10) primary key, name varchar2(20), major varchar2(20), dob
date);

Prof. K. Adisesha 4
2. Create table course (courseno number(10) primary key, cname varchar2(20), dept varchar2(20)) ;
3. Create table enrol (regno varchar2(10) references student, courseno number(10) references course,
sem number(5),marks number(5));
4. Create table text(bkisbn number(5) primary key, book_title varchar2(20), publisher varchar2(20),
author varchar2(20)) ;
5. Create table book_adpt (course_no number (10) references course, bkisbn number(5) references
text,sem number(5)) ;
6. Insert into student values (&regno, &name, &major, &dob) ;
7. Insert into course values (&courseno, &cname, &dept) ;
8. Insert into enroll values (&regno, &courseno, &sem, &marks) ;
9. Insert into text values (&bkisbn, &book_title, &publisher, &author);
10. Insert into book_adpt values (&course_no, &bkisbn, &sem);
11. Insert into text values (2022,’vb’,’skyword’,’SRIKANTH’);
12. Insert into book_adptvalues (104,2022,4);
13. create view compdept as ( selectc.dept, c.courseno, t.book_title, t.bkisbn from course c, book_adpt ba,
text t where c.courseno = ba.course_no and ba.bkisbn = t.bkisbn and dept = ’ COMPUTER ’) order
by t.book_title;
14. select * from compdept;
15. select course_no, bkisbn, book_title from compdept where dept in (select dept from compdept group
by dept having count(*) > =2 ) ;
16. select c.dept, t.book_title, t.publisher from course c,text t, book_adpt b where t.publisher = ‘himalaya’
and c.course_no = b.course_no and b.bkisbn = t.bkisbn;
***********************
6. 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)
CATLOG(book-id: int, title : string, author-id: int, publisher-id: int, category: int, year: int,
price: int)
CATEGORY(category-id: int, description: string)
ORDER-DETAILS(order-no: int, book-id: int, quantity: int)
a) Create above tables by properly specifying the primary keys and the foreign keys.
b) Enter at-least five tuples for each relation.
c) 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 2021.
d) Find the author of the book which has maximum sales.
e) Demonstrate how to increase price of books published by specific publisher by 10%
Answers
1. create table authordb (author_idnumber(5) primary key , name varchar2(15) not null, city
varchar2(10),country varchar2(10)) ;
2. create table publisherdb (publisher_id number(5) primary key, name varchar2(15) not null, city
varchar2(15),country varchar2(15));

Prof. K. Adisesha 5
3. create table catalogdb (book_id number(5) primary key, title varchar2(15) not null, author_id
number(5) references authordb, publisher_id number(5) references publisherdb, year number(5), price
number(8)) ;
4. create table categorydb (category_id number(5) primary key, description varchar2(15)) ;
5. create table orderdb (order_id number(5) primary key, book_id number(5) references
catalogdb,quantity number(5)) ;
6. insert into authordb values (&author_id, &name, &city, &country) ;
7. insert into publisherdb values (&publisher_id, &name, &city, &country) ;
8. insert into catalogdb values (&book_id, &title, &author_id, &publisher_id, &year, &price) ;
9. insert into categorydb values (&category_id, &description);
10. insert into orderdb values (&order_id, &book_id, &quantity) ;
11. select c.author_id, a.name from catalogdb c, authordb a where a.author_id = c.author_id and c.price
> (select avg (price) from catalogdb group by c.author_id , a.name having count(*) > 2) ;
12. update catalogdb set price = price*1.10 where publisher _id = 10;
***************************************
7. Consider the following database for BANK.
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-no: int, branch-name: string, amount: real)
BORROWER(customer-name: string, loan-no: int)
a) Create the above tables by properly specifying the primary keys and foreign keys.
b) Enter at-least five tuples for each relation.
c) Find all the customers who have at-least two accounts at the main branch.
d) Find all customer who have an account at all the branches located in a specific city.
e) Demonstrate how to delete all account tuples at every branch located in specific city.
Answers
1. Create table branchdb (bname varchar2(15) primary key, bcity varchar2(15) not null, asset
number(8,4));
2. Create table accountdb (accno number(10) primary key , bname varchar2(15) references branchdb,
balance number(10,2)) ;
3. create table depositordb(cname varchar2(15) not null, accno number(5) references accountdb primary
key(cname, accno));
4. create table customerdb (cname varchar2(15) primary key, cstreet varchar2(15), ccity varchar(15));
5. create table loandb (loanno number(10) primary key, bname varchar2(15) references branchdb
,amount number(10,2));
6. create table borrowerdb (cname varchar2(20) not null, loanno number(10) references loandb, primary
key(cname, loanno) ;

Prof. K. Adisesha 6
7. insert into branchdb values (&bname, &bcity, &asset) ;
8. insert into accountdb values (&accno, &bname, &balance) ;
9. insert into depositordb values (&cname, &accno);
10. insert into customerdb values (&cname, &cstreet, &ccity);
11. insert into loandb values (&loanno, &bname, &amount);
12. insert into borrowerdb values (&cname, &loanno);
13. create view decacc as (selelctb.bname,a.accno,d.cname from branchdb b,accountdb a,depositordb d
where a.accno = d.accno and a.bname = b.bname and b.bname = ‘KR Puram’);
14. select bname, accno, cname from decacc where cname in (select cname from decacc group by cname
having count (*)>=2);
15. select d.cname,a.accno,b.bname,b.bcity from depositordb d, accountdb a, branchdb b where b.bcity =
‘bangalore’ and d.accno = a.ccno and a.bname=b.bname;
16. delete from accountdb where bname in (select bname from branchdb where bcity= ‘Bangalore’);
******************************************
8. Consider the following database for ORDER PROCESSING.
CUSTOMER(cust-no: int, cname: string, city: string)
ORDER(orderno: int, odate: date, ord-amt: real)
ORDER_ITEM(orderno: int, itemno:int, qty: int)
ITEM(itemno: int, unitprice: real)
SHIPMENT(orderno: int, warehouseno: int, ship-date: date)
WAREHOUSE(warehouseno: int, city: string)
b) Enter at least five tuples for each relation.
c) List the order number and ship date for all orders shipped from particular warehouse
d) Produce a listing: customer name, no of orders, average order amount
e) List the orders that were not shipped within 30 days of ordering
Answers
1. create table customerdb (cust_idnumber(6) primary key,cname varchar2(20) not null, ccity
varchar2(20));
2. create table custorderdb (orderno number(6) primary key,orderdate date, customer_no number(6),
order_amt number(6)) ;
3. create table itemdb (item_no number(6) primary key , unitprice number(6)) ;
4. create table warehoused (warehouseno number(5) primary key,city varchar2(12)) ;
5. create table shipmentdb (orderno number(6) references custorderdb, warehouse_no number(5)
references warehouse_db, shipdate date);
6. insert into customerdb values(&cust_id, &cname, &ccity) ;
7. insert into custorderdb values(&orderno, &orderdate, &customer_no, &order_amt) ;
8. insert into itemdb values(&item_no, &unitprice) ;
9. insert into warehousedb values(&warehouseno, &city) ;

Prof. K. Adisesha 7
10. insert into shipmentdb values (&orderno, &warehouse_no, &shipdate);
11. select orderno, shipdate from shipmentdb where warehouse_no=1002;
12. select c.cname, count(co.orderno), avg(co.order_amt) from customerdbc, customer_db co where
c.cust_id = co.customer_no group by c.cname,co. customer_no;
13. select c.cname ,co.orderno,co.orderdate,sh.shipdate from customerdbc, custorderco, shipmentdbsh
where c.cust_id =co.customer_no and co.orderno = sh.orderno and (to_date (sh.shipdate) - o_date
(co.orderdate))>30 ;
*********************************************
PART – B
1. Write a PL/SQL program to find the largest of three numbers
declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=&c;
if (a>b and a>c) then
dbms_output.put_line('a is largest'||a);
elsif (b>a and b>c) then
dbms_output.put_line('b is largest'||b);
else
dbms_output.put_line('c is the largest'||c);
end if;
end;
/
2. Write a PL/SQL program to generate reverse for given number
declare
n number(4) := &n;
s number(4) := 0;
r number(4);
begin
while n > 0
loop
r:= mod(n,10);
s:=(s*10)+r;
n:=trunc(n/10);

Prof. K. Adisesha 8
end loop;
dbms_output.put_line('the reverse number is');
dbms_output.put_line(s);
end;
/
3. Write a PL/SQL program to find the factorial of a given number
declare
inumber(4) :=1;
n number(4) := &n;
f number(4) :=1;
begin
for i in 1..n
loop
f:=f*i;
end loop;
dbms_output.put_line('factorial of a number is'|| f);
end;
/
4. Write a PL/SQL program to check whether given number is prime or not
declare
num number;
inumber := 1;
c number := 0;
begin
num := &num;
for i in 1..num
loop
if ((mod(num,i))=0)
then
c:=c+1;
end if;
end loop;
if (c>2)
then
dbms_output.put_line(num|| 'not prime');
else
dbms_output.put_line(num || 'is prime');
end if;
end;
/
5. Write a PL/SQL program to generate Fibonacci series upto N
declare
a number(3) := 1;
b number(3) := 1;

Prof. K. Adisesha 9
c number(3);
n number(3);
begin
n:=&n;
dbms_output.put_line('the Fibonacci series is:');
while a<=n loop
dbms_output.put_line(a);
c:=a+b;
a:=b;
b:=c;
end loop;
end;
/
6. Write a PL/SQL program for inserting a row into employee table
create table employee (emp_idnumber(5) primary key,emp_name varchar2(30),Emp_dept
varchar2(10),emp_salary varchar2(8));
Declare
begin
insert into employee values(10,'Adisesha','hod',40000);
End;
/
7. Write a pl/sql program to handle a predefined exception
declare
n number(4);
d number(4);
begin
n:=&n;
d:=n/0;
exception
when zero_divide then
dbms_output.put_line('divide by error exception is caught');
end;
/
8. Write a pl/sql program for creating a procedure for calculating sum of two numbers.
Create or replace procedure sum(n1 in number,n2 in number) is
Total number(6);
Begin
Total:= n1+n2;
Dbms_output.put_line('the sum is'||total);
End;
/
Execution:
SQL>exec sum(10,20);
The sum is : 30

Prof. K. Adisesha 10
9. Write a procedure to check the given year is leap year or not
Create or replace procedure leapyear(y in number) is
Begin
If y mod 4 = 0 and y mod 100 <> 0 or y mod 400 =0 then
Dbms_output.put_line ('the given year is leap year');
Else
Dbms_output.put_line('the given year is not leap year');
End if;
End;
/
Calling a above procedure
Leapyear(2022);

DATABASE MANAGEMENT SYSTEM LAB.pdf

Recommended

More Related Content

What's hot (20)

Similar to DATABASE MANAGEMENT SYSTEM LAB.pdf (20)

More from Prof. Dr. K. Adisesha (20)

Recently uploaded (20)

DATABASE MANAGEMENT SYSTEM LAB.pdf