B.E / B.

Tech PRACTICAL END SEMESTER EXAMINATIONS, JUNE 2022

Fourth Semester

CS8481 & DATA BASE MANAGEMENT SYSTEMS LABORATORY

(Regulations 2017)

Time : 3 Hours Answer any one Question Max. Marks 100

(To be filled by the question paper setter)

Database design , Viva-Voce Record Total

Program / Queries Output
creation of table , Data
30 50 10 10 100

Create the following tables with given attributes having appropriate data type and specify the
1.
necessary primary and foreign key constraints:
Customer (Custid, Custname, Age, phone)
Loan (Loanid, Amount, Custid, EMI)
a) List the name of the customers who have taken loan for more than Rs.50,000.
b) List the Customer id of those who have no loan.
c) List the total count of loan availed.
d) Create a procedure to print the Amount and Custid when the Loanid is given as input. Handle
Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
2.
necessary primary and foreign key constraints:
Employee (EmpId, Empname, Sal, Deptno)
Dept (Deptno, Dname, Loc,DeptmanagerId)
a) List the count of Employees and average salary of each department.
b) List the employee name, department name and the salary of all the employees.
c) Display the Employee name and the respective department manager name.
d) Create a function to return the salary of the employee when Empid is given as input
parameter. Handle Exceptions.
Table creation with Appropriate Constraints and Data Type (15)
Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
 . Create the following tables with given attributes having appropriate data type and specify the
3.
necessary primary and foreign key constraints:
Voter (VoterId, Votername, Gender, Boothid,Checkvote)
Booth (Boothid, Location,BIncharge )
a) (i)List the count of voters in each Booth (ii) List the count of Male voters voted.
b) Display the overall count of voters voted in the election.
c) Display the Boothid, Location and count of voters voted.

d) Write a function to return the percentage of poll in a booth when boothid is given as input.
Handle Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
4.
necessary primary and foreign key constraints:
User (Userid, Name, Dept, Bookid, Accdate)
Book (Bookid, Book_name, Author, Publication, Price)
a) List the name of the user who had accessed the costliest book.
b) List the userid and count of books accessed by the user.
c) List the books published by Wiley publisher.
d) Write a PL/SQL program to print the details of the book when Bookid is given as input.
Handle appropriate exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
5.
necessary primary and foreign key constraints:
Customer (Custid, Custname, Addr, phno,panno)
Loan (Loanid, Amount, Interest, Custid)
Account (Acctno, Accbal, Custid)
a. Display the Account balance amount of a particular customer “ARUN”
b. Update the interest with 1% when Accbal of the Custid >50% of Loan Amount
c. Create a View with Accbal and Loan Amount of all Customers
d. Create a trigger which checks for minimum balance in the account.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
 Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
6.
necessary primary and foreign key constraints:
Customer (Custid, Custname, phno,pan,DOB)
HomeLoan (HLoanid, Amount, Custid)
VehicleLoan (VLoanid, Amount, Custid)
a) List the Custid of the customers who have both homeloan and vehicle loan.
b) List the Custid of the customers who donot have any loan.
c) Create a view with customerid, Customer name and total loan amount (HomeLoan and
VehicleLoan)
d) Write a trigger which displays the Homeloan details whenever the values are inserted in the
respective table.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Trigger (d) (20) marks

Create the following tables with given attributes having appropriate data type and specify the
7.
necessary primary and foreign key constraints:
Product (Prodid, Prodesc, Price, Stock)
Sales (Salesid, Prodid, qty)
a) Add a column reorder in Product table having value 50 for all products.
b) List the Prodid of the Products .
c) List the Salesid, Prodid of the Sales.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a) (10) marks
(b) & (c) (2 x 20) (40) marks

Create the following tables with given attributes having appropriate data type and specify the
8.
necessary primary and foreign key constraints:
Product (Prodid, Prodesc, Price, Stock)
Sales (Salesid, Prodid, qty)
a) Add a column reorder in Product table having value 50 for all products.
 b) Display the Sales Report.
c) Display the Product Report.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-b) (20) marks
(c) 15 x 2 (30) marks
Design a Database for Library Management System using ER Diagram.
9.
Create the following table with the given attributes having appropriate data type and specify the necessary
key constraints.
 BOOK(Bookcode,Name,Author,Price,Publish)
 Display the Book Details.
 Display the author, price and publish.
ER Diagram (15) marks
Data Insertion (15)
Queries (a-b) (20) marks
(c) 15 x 2 (30) marks
Create an application for student contacts management application. The application enables a faculty
10.
user to login and search the details of a Student (Name, Address, Contact no, Email id, parent contact)
with his register number.
 Student (Name, Address, Contact no, Email id, parent contact)
 Display the Student Details.
 Display the Contact no, Email id, parent contact.

Table creation , Appropriate Constraints and Data Type

(15)
Data Insertion (10)
(20) marks
(c) 15 x 2 (30) marks
Design a Database for Library Management System using ER Diagram.
11.
Create the following table with the given attributes having appropriate data type and specify the necessary
key constraints.
 BOOK(Bookcode,Name,Author,Price,Publish)
 Display the Book Details.
 Display the author, price and publish.
ER Diagram (15) marks
Data Insertion (15)
 Queries (a-b) (20) marks
(c) 15 x 2 (30) marks
Create the following tables with given attributes having appropriate data type and specify the
12.
necessary primary and foreign key constraints:
Marks(Regno,Name , Dept, Subj1,Subj2,Subj3)
a) Add a column Total in student table and update the Total field with the sum of 3
subject Marks.
b) Find the second maximum total in the table.
c) Display the name of the student with maximum total.
d) Write a Table creation with Appropriate Constraints and Data PL/SQL program to
display the Type (10) report sheet of the
students Data Insertion (10) using cursors.
Queries (a-c) (30) marks
(c) PL/SQL program (30)

Create the following tables with given attributes having appropriate data type and specify the
13.
necessary primary and foreign key constraints:
Customer (Custid, Custname, Age, phone)
Loan (Loanid, Amount, Custid, EMI)
a) List the name of the customers who have taken loan for more than Rs.50,000.
b) List the Customer id of those who have no loan.
c) List the total count of loan availed.
d) Create a procedure to print the Amount and Custid when the Loanid is given as input. Handle
Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with the mapping given below.
14.
stu_details (reg_no, stu_name, DOB, address, city)
mark_details (reg_no, mark1, mark2, mark3, total)
(a) Alter the table mark_details to add a column average with data type as long.
(b) Display the months between the DOB and till date.
 (c) Using alter command drop the column address from the table stu_details.
(d) Write a PL/SQL program to find the sum & average marks of all the student using procedures.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (30) marks
(d) PL/SQL program (20)

Create the following tables with the mapping given below.

15.
Employee (Empno, Ename, Job, MgrId, DoB, DoJ, Sal, Comm, Deptno)
Department (Dname, Deptno, Dloc)
(a) Display the Emp no, name, salary and experience of each employee ordered by salary
(highest to lowest)
(b) List the names of the employee working for “Marketing” Department.
(c) List the names of the employees born in the current month.
(d) Write a PL/SQL function to display the details of the employee when Employee no given as
input. Handle Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (30) marks
(d) PL/SQL program (20)

Create the following tables with given attributes having appropriate data type and specify the
16.
necessary primary and foreign key constraints:
User (Userid, Name, Dept, Bookid, Accdate)
Book (Bookid, Book_name, Author, Publication, Price)
a) List the name of the user who had accessed the costliest book.
b) List the userid and count of books accessed by the user.
c) List the books published by Wiley publisher.
d) Write a PL/SQL program to print the details of the book when Bookid is given as input.
Handle appropriate exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
17.
Create the following tables with given attributes having appropriate data type and specify the
necessary primary and foreign key constraints:
Customer (Custid, Custname, Age, phone)
Loan (Loanid, Amount, Custid, EMI)
a) List the name of the customers who have taken loan for more than Rs.50,000.
b) List the Customer id of those who have no loan.
c) List the total count of loan availed.
d) Create a procedure to print the Amount and Custid when the Loanid is given as input. Handle
Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks

18.
Create the following tables with given attributes having appropriate data type and specify the
necessary primary and foreign key constraints:
User (Userid, Name, Dept, Bookid, Accdate)
Book (Bookid, Book_name, Author, Publication, Price)
a) List the name of the user who had accessed the costliest book.
b) List the userid and count of books accessed by the user.
c) List the books published by Wiley publisher.
d) Write a PL/SQL program to print the details of the book when Bookid is given as input.
Handle appropriate exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
19.
necessary primary and foreign key constraints:
Employee (EmpId, Empname, Sal, Deptno)
Dept (Deptno, Dname, Loc,DeptmanagerId)
a) List the count of Employees and average salary of each department.
b) List the employee name, department name and the salary of all the employees.
c) Display the Employee name and the respective department manager name.
d) Create a function to return the salary of the employee when Empid is given as input
parameter. Handle Exceptions.
Table creation with Appropriate Constraints and Data Type (15)
 Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
20
necessary primary and foreign key constraints:
Customer (Custid, Custname, Age, phone)
Loan (Loanid, Amount, Custid, EMI)
a) List the name of the customers who have taken loan for more than Rs.50,000.
b) List the Customer id of those who have no loan.
c) List the total count of loan availed.
d) Create a procedure to print the Amount and Custid when the Loanid is given as input. Handle
Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks

Create the following tables with given attributes having appropriate data type and specify the
21
necessary primary and foreign key constraints:
Customer (Custid, Custname, Age, phone)
Loan (Loanid, Amount, Custid, EMI)
a) List the name of the customers who have taken loan for more than Rs.50,000.
b) List the Customer id of those who have no loan.
c) List the total count of loan availed.
d) Create a procedure to print the Amount and Custid when the Loanid is given as input. Handle
Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
22
necessary primary and foreign key constraints:
Employee (EmpId, Empname, Sal, Deptno)
Dept (Deptno, Dname, Loc,DeptmanagerId)
a) List the count of Employees and average salary of each department.
b) List the employee name, department name and the salary of all the employees.
 c) Display the Employee name and the respective department manager name.
d) Create a function to return the salary of the employee when Empid is given as input
parameter. Handle Exceptions.
Table creation with Appropriate Constraints and Data Type (15)
Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
23
necessary primary and foreign key constraints:
User (Userid, Name, Dept, Bookid, Accdate)
Book (Bookid, Book_name, Author, Publication, Price)
a) List the name of the user who had accessed the costliest book.
b) List the userid and count of books accessed by the user.
c) List the books published by Wiley publisher.
d) Write a PL/SQL program to print the details of the book when Bookid is given as input.
Handle appropriate exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
24
necessary primary and foreign key constraints:
Customer (Custid, Custname, Age, phone)
Loan (Loanid, Amount, Custid, EMI)
a) List the name of the customers who have taken loan for more than Rs.50,000.
b) List the Customer id of those who have no loan.
c) List the total count of loan availed.
d) Create a procedure to print the Amount and Custid when the Loanid is given as input. Handle
Exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks
Create the following tables with given attributes having appropriate data type and specify the
25
necessary primary and foreign key constraints:
User (Userid, Name, Dept, Bookid, Accdate)
Book (Bookid, Book_name, Author, Publication, Price)
a) List the name of the user who had accessed the costliest book.
b) List the userid and count of books accessed by the user.
c) List the books published by Wiley publisher.
d) Write a PL/SQL program to print the details of the book when Bookid is given as input.
Handle appropriate exceptions.

Table creation with Appropriate Constraints and Data Type (15)

Data Insertion (15)
Queries (a-c) (10 x 3 =30) marks
Procedure (d) (20) marks