1. Consider the Insurance database given below.

PERSON(driver_ID, name, address)

CAR(regno, model,year )
ACCIDENT(report_number,accd_date,location)
OWNS(driver_id,regno)
PARTICIPATED(driver_id,regno,report_number,damage_amount)
i. Specify the primary keys and foreign keys and enter at least five tuples for each relation.
ii. Update the damage amount for the car with specific regno in the accident with report number
1025.
iii. Add a new accident to the database.
iv. Find the total number of people who owned cars that were involved in accidents in the year 2024.
v. Find the number of accidents in which cars belonging Wagon R were involved.
Database design , Viva-Voce Record Total
Program /
creation of table , Output
Queries
Data
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

2. Create the Book database and do the following:

book(book_name,author_name,price,quantity).
i. Write a query to update the quantity by double in the table book.
ii. List all the book_name whose price is greater than those of book named "Database for Dummies".
iii. Retrieve the list of author_name whose first letter is ’a’ along with the book_name and price.
iv. Write a PL/SQL Procedure to find the total number of books of same author.
Database design , Viva-Voce Record Total
Program /
creation of table , Output
Queries
Data
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

3. Create the Company database with the following tables and do the following:
Administration(employee_salary, development _cost, fund_amount, turn_over,bonus)
Emp_details (emp_no, emp_name, DOB, address, doj, mobile_no, dept_no, salary).
i. Calculate the total and average salary amount of the employees of each department.
ii. Display total salary spent for employees.
iii. Develop a PL/SQL function to display total fund_amount spent by the administration department.
Database design , Viva-Voce Record Total
Program /
creation of table , Output
Queries
Data
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

4. Create the student database with the following tables and do the following:
assessment(reg_no,name, mark1, mark2, mark3, total)
dept_details (dept_no, dept_name, location).
i. Using alter command drop the column location from the table dept_details.
ii. Display all dept_name along withdept_no.
iii. Drop the table dept_details.
iv. Write a PL/SQL Trigger to verify the data before insertion on assessment table.
Database design , Viva-Voce Record Total
Program /
creation of table , Output
Queries
Data
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

5. Consider the following tables.

SAILOR(sid, sname, rating, age)
BOATS(bid, bname, colour)
RESERVES(sid, bid, day)
i. List the sailors in the descending order of their rating.
ii. List the sailors whose youngest sailor for each rating and who can vote.
iii. List the sailors who have reserved for both ‘RED’ and ‘GREEN’ boats.
iv. Create synonym for sailor table.
v. Create a PL / SQL Function that accepts SID and returns the name of sailor.
Database design , Viva-Voce Record Total
Program /
creation of table , Output
Queries
Data
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

6.Consider the following relational schema for a banking database application:

CUSTOMER (CID, CNAME)
BRANCH (BCODE, BNAME)
ACCOUNT (ANO, ATYPE, BALANCE, CID, BCODE)
TRANSACTION (TID, ANO, TTYPE, TDATE, TAMOUNT)
i. Develop a SQL query to list the details of branches and the number of accounts in each branch.
ii. Develop a SQL query to list the details of customers who have performed the most transactions
today
iii. Create a view that will keep track of the details of each customer and account details who have both
savings and current account.
iv. Develop a database trigger that will update the value of BALANCE in ACCOUNT table when a
record is inserted in the transaction table
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 7. Consider the following database of student enrollment in courses and books adopted for that course.
STUDENT(regno, name, major, bdate)
COURSE(courseno, cname, dept)
ENROLL(regno, courseno, sem, marks)
i. Display the total number of students register for more than two courses in a department specified.
ii. Display the students who have secured the highest mark in each course
iii. List the youngest student of each course in all departments.
iv. Develop PL/SQL Cursor that selects marks of a particular student in a specified semester.
Database design , Viva-Voce Record Total
Program /
creation of table , Output
Queries
Data
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

8. Create the student database with the following tables and do the following:
mark_details(reg_no,name, mark1, mark2, mark3, total)
dept_details (dept_no, dept_name, HOD)
stud_details(reg_no,name, dob, address)
i. Using alter command to assign foreign key in mark_details.
ii. Display the address of the students who have secured the top three ranks.
iii. Write a PL/SQL procedure to update the grade according to the marks secured.

Database design , Viva-Voce Record Total

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

INTERNAL EXAMINER EXTERNAL EXAMINER

9. Create a database for Placement and Training cell.

Stud_details(regno, name, dept, percentage)
Company(companyID,name, noOfVacancy)
Training_Details(CourseID, name, Trainer)
Placed(regno, companyID,minSal)
i. List the students who are eligible for recruitment in a particular company.
ii. Display the student who has been placed with highest salary
iii. Develop a PL/SQL exception that provides an alternate for not eligible students.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

10. 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)
i) List the name of the customers who have taken loan for more than Rs.50,000.
ii) List the Customer id of those who have no loan.
iii) List the total count of loan availed.
iv) Create a procedure to print the Amount and Custid when the Loanid is given as input. Handle
Exceptions.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER

11. Create the following tables with given attributes having appropriate data type and specify the
necessary primary and foreign key constraints:
Employee (EmpId, Empname, Sal, Deptno)
Dept (Deptno, Dname, Loc,DeptmanagerId)
i) List the count of Employees and average salary of each department.
ii) List the employee name, department name and the salary of all the employees.
iii) Display the Employee name and the respective department manager Id.
iv) Create a function to return the salary of the employee when Empid is given as input
parameter. Handle Exceptions.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

12. Create the following tables with given attributes having appropriate data type and specify the
necessary primary and foreign key constraints:
Voter (VoterId, Votername, Gender, Boothid,Checkvote)
checkvote is 1(voted) or 0 (not voted)
Booth (Boothid, Location,BIncharge )
i) List the count of voters in each Booth
ii) List the count of Male voters voted.
iii) Display the overall count of voters voted in the election.
iv) Display the Boothid, Location and count of voters voted.
v) Write a function to return the percentage of poll in a booth when boothid is given as input.
Handle Exceptions.
Database design , Program / Viva-Voce Record Total
creation of table , Data Output
Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

13. 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)
i) List the name of the user who had accessed the costliest book.
ii) List the userid and count of books accessed by the user.
iii) List the books published by Wiley publisher.
iv) Write a PL/SQL program to print the details of the book when Bookid is given as input.
Handle appropriate exceptions.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

14. Create the following tables with given attributes having appropriate data type and specify the
necessary primary and foreign key constraints:
Customer (Custid, Custname, phno,pan,DOB)
HomeLoan (HLoanid, Amount, Custid)
VehicleLoan (VLoanid, Amount, Custid)
i) List the Custid of the customers who have both homeloan and vehicle loan.
ii) List the Custid of the customers who donot have any loan.
iii) Create a view with customerid, Customer name and total loan amount (HomeLoan and
VehicleLoan)
iv) Write a trigger which displays the Homeloan details whenever the values are inserted in the
respective table.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

15. Create the following tables with the appropriate constraints.

a. stu_details (reg_no, stu_name, DOB, address, city)
b. mark_details (reg_no, mark1, mark2, mark3, total)
(i). Display only those rows whose total ranges between 250 and 300.
(ii). Drop the table mark_details.
(iii). Delete the row whose reg_no=161.
(iv). Display all details whose names begins with 'A'
(v) Write PL/SQl program for sum of digits
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

16. Create the following tables with the appropriate constraints.
a. emp_details (emp_no, emp_name, DOB, address, doj, mobile_no, dept_no,
salary).
b. dept_details (dept_no, dept_name, location).
(i) Display the contents of emp_details and dept_details
(ii) Truncate the table dept_details.
(iii) Display the average salary of the employee.
(iv) Display the emp_name getting highest salary.
(v) Write PL /Sql program using a for loop for printing the numbers from 1 to 5 and in
reverse order.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

17. Create the following tables with the appropriate constraints. .

a. Customer (Cust_id, Cust_name, Addr, ph_no,pan_no)
b. Loan (Loan_id, Amount, Interest, Cust_id)
(i)Display the details of customer who has taken a loan.
(ii) Display the sum of Loan Amount.
(iii)Display the count of customer who has taken loan
(iv)Add a column nol(number of loans) in customer table
(v)Design a PL program to output Fibonacci series

Database design , Program / Viva-Voce Record Total

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

INTERNAL EXAMINER EXTERNAL EXAMINER

18. Consider the following employee and department tables.

EMPLOYEE(empno, ename, age,designation, manager, hiredate, salary, commission, deptno)
DEPARTMENT(deptno, dname, location)
i.Create the above tables by properly specifying the primary keys and foreign keys and enter at
least five tuples for each relation.
ii.List the names of employees whose name contain substring ‘LA’.
iii.List the details of employees of salary are greater than or equal to the average salary of
employee table.
iv.Create a view which consists of details of all ‘SALESMAN’.
v. Write a trigger to ensure that age in employee table is maintained greater than 25 .
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

19. Consider the following tables.
SAILOR(sid, sname, rating, age)
BOATS(bid, bname, colour)
RESERVES(sid, bid, day)
i.Create the above tables by properly specifying the primary keys and foreign keys and enter at
least five tuples for each relation.
ii.List the sailors in the descending order of their rating.
iii.List the youngest sailor and oldest sailor,
iv.List the sailors who have reserved for both ‘RED’and ‘GREEN’boats.
b.Write a PL/SQL to find the factorial of a number.

Database design , Program / Viva-Voce Record Total

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

INTERNAL EXAMINER EXTERNAL EXAMINER

20. Consider the insurance database given below.

a. PERSON(driver_id, name, address)
CAR(regno, model,year )
ACCIDENT(report_number,accd_date,location)
OWNS(driver_id,regno)
PARTICIPATED(driver_id,regno,report_number,damage_amount)
i. Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
ii. Update damage amount for the car with REGNO 2509.
iii. Add a new accident to the database.
iv. Find the number of accidents in which cars belonging to a specific model were involved.
b. Write a PL/SQL to find and display the sum of digits.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

21. Consider the following database of student enrollment in courses and books adopted for that
course.
STUDENT(regno, name, major, bdate)
COURSE(courseno, cname, dept)
ENROLL(regno, courseno, sem, marks)
BOOK_ADOPTION(courseno, sem, book_isbn)
TEXT(book_isbn,book_title,publisher, author)
i.Create the above tables by properly specifying the primary keys and foreign keys and enter
at least five tuples for each relation.
ii.Add a new text book to the database
iii.Produce a list of text books in the alphabetical order
iv.List the books published by a specific publisher.
v. Write a trigger to avoid the entry of age less than 25.
Database design , Program / Viva-Voce Record Total
Output
creation of table , Data Queries
30 50 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

22. Consider the following relations for order processing database application in a company.
CUSTOMER(custno, cname, city)
ORDER(orderno, odate, custno, ord_amt )
ORDER_ITEM(orderno, itemno, quantity)
ITEM(itemno, unitprice)
SHIPMENT(orderno, warehouseno, ship_date)
WAREHOUSE(warehouseno, city)
i. Create the above tables by properly specifying the primary keys and foreign keys and enter
at least five tuples for each relation.
ii. Find the average order amount,
iii. List the item according to quantity in ascending order..
iv. Demonstrate the deletion of ITEM table
b. Write a PL/SQL to handle divide by zero exception.

Database design , Program / Viva-Voce Record Total

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

INTERNAL EXAMINER EXTERNAL EXAMINER

 23. Consider the following database of student enrollment in courses and books adopted for that
course.
STUDENT(regno, name, major, bdate)
COURSE(courseno, cname, dept)
ENROLL(regno, courseno, sem, marks)
BOOK_ADOPTION(courseno, sem, book_isbn)
TEXT(book_isbn,book_title,publisher, author)
i. Create the above tables by properly specifying the primary keys and foreign keys and enter
at least five tuples for each relation.
ii. Add a new course detail in the course table
iii. Display the books using isbn number in descending order
iv. List all the books published by Pearson publication.
ii. Write a trigger to implement its use.

Database design , Program / Viva-Voce Record Total

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

INTERNAL EXAMINER EXTERNAL EXAMINER

24. Consider the following employee and department tables.

EMPLOYEE(empno, ename, designation, manager, hiredate, salary, commission, deptno)
DEPARTMENT(deptno, dname, location)
i. Create the above tables by properly specifying the primary keys and foreign keys and enter
at least five tuples for each relation.
ii. List the employees of employee table in descending order of their salaries.
iii. List the details of highest paid employee in ‘SALES’ department.
iv. Write a query using aggregate function.
v. Develop a program to implement triggers using PL/SQL

Database design , Program / Viva-Voce Record Total

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

INTERNAL EXAMINER EXTERNAL EXAMINER