Page 1 of 5 SET 2

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 2018.
v. Find the number of accidents in which cars belonging Wagon R were involved.

Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 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 Procedure to find the total number of books of same author.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 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 function to display total fundamount spent by the administration department.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 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 Trigger to verify the data before insertion on assessment table.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 2 of 5 SET 2

5. Consider the following Tables for a bus reservation system application:

BUS (ROUTENO, SOURCE, DESTINATION)
PASSENGER (PID, PNAME, DOB, GENDER)
BOOK_TICKET (PID, ROUTENO, JOURNEY_DATE, SEAT_NO)
i. Include constraint that DOB of passenger should be after 2000
ii. Display the passengers who had booked the journey from Mumbai to Chennai on 02-Feb-2019
iii. List the details of passengers who have traveled more than three times on the same route.
iv. Create a View that displays the RouteNo, source, destination and journey_date which moves from
Chennai to Delhi.
v. In the above created procedure, include exceptions to display "No ticket booked on specified date"
for a given journey_date.

Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

6. 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 function that accepts SID and returns the name of sailor.

Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

7. Consider the following relations for an order processing application:

CUSTOMER (CID, NAME)
PRODUCT (PCODE, PNAME, UNIT_PRICE)
CUST_ORDER (OCODE, ODATE, CID)
ORDER_PRODUCT (OCODE, PCODE, QTY)
i. Develop a Trigger to ensure the product to be Pen , Eraser, Pencil during insertion
ii. Develop a PL/SQL Function to calculate the total cost of ordered product.
iii. Use Sequence for PCODE insertion in product table

Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 3 of 5 SET 2

8. Consider the following relations for a transport management system application:

DRIVER (DCODE, DNAME, DOB, GENDER)
CITY (CCODE, CNAME)
TRUCK (TRUCKCODE, TTYPE)
i. Include the constraint as mentioned above and the gender of driver is always 'male'.
ii. Develop a SQL query to list the details of each driver and the number of trips traveled.
iii. Create an index on truck_code in Drive_truck table .
iv. Use Cursor to display the details of all drivers, and the truck_code

Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

9. 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
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

10. 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 Cursor that selects marks of a particular student in a specified semester.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 4 of 5 SET 2

11. The following are maintained by a book dealer.

AUTHOR(author_id, name, city, country)
PUBLISHER(publisher_id, name, city, country)
CATALOG(book_id, title, author_id, publisher_id , category_id, year, price)
CATEGORY(category_id, description)
ORDER_DETAILS(order_no, book_id, quantity)
i. List the author of the book that has minimum sales.
ii. Display total number of books in each category.
iii. Develop a procedure that updates the price of the book by 10% those with maximum sales.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

12. 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 procedure to update the grade according to the marks secured.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

13. Create a database for IoT simulator with the following tables.
Device_details (deviceID, devicename, properties)
Connect_status(deviceID, loginTime, logoutTime)
Transaction_details(transID, deviceID, updatedProperties, timeofUpdation)
i. List the details of the devices that are connected in a particular session
ii. Display the details of the device and its property that has been active for most of the time.
iii. Develop a procedure that deletes the details of the devices that have been least updated.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

14. Create a database for maintaining the cloud database

PAAS_details(server, platform, startDate, endDate, rate)
SAAS_details(server, software, startDate, endDate, rate)
DAAS_details(server, database, startDate, endDate, rate)
transaction(service, logintime, logouttime)
i. List the details of the services requested from 5th Feb to 10th Feb, 2019.
ii. Display the details of the service that are least used and most used.
iii. Develop a function that returns the total amount invested on platform service in the month of
February.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 5 of 5 SET 2

15. 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 exception that provides an alternate for not eligible students.
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

16. Create a database for Timetable generation using the following tables:
Faculty_details(FacultyID,FacultyName, dept)
Subject_details(Subcode, subtitle, dept, year)
Subject_allocated(Subcode, year, dept,FacultyID)
Timetable(period, timefrom, timeto, Subcode,year,dept)
i. Display the timetable of individual faculty
ii. Display the timetable of each class separately
iii. Display the timetable of particular subject
iv. Develop a procedure that displays individual class timetable
Aim & Procedure Queries & Program Output & Result Viva-Voce Record Total
20 30 30 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER