19CS3002-Database Management Systems Laboratory
Model Practical Examination
Date: 09-11-2021 Time: 3 Hrs.
Max. Mark: 100
Dear Students,
Carefully read the instructions then start the examination.
Each students have allocated one Question, answer that question only.
__________________________________________________________________________________
First Page: (Same Format, Write in your Paper)
Register Number: xxxxxxxxx
Name: xxxxxxxxxxxx
Class with Section: II – B.E.(CSE), - B Section
Subject: 19CS3002-Database Management Systems Laboratory
Exam: Model Practical Examinations (Online Mode)
Date of Exam: 09-11-2021 / 5.00 to 8.00 PM
Problem & Aim Programme Result (screenshot) Viva-voce Total
5 65 20 10 100
Internal Examiner External Examiner
Second Page: (Second page Onwards)
Write Experiment details (Problem), Aim, Programme statement, after executed
the program, you can past your resultant screenshot.
Final submission:
Finally take photos of your written work and screenshot, convert into single PDF
file format then upload google classroom before 8.00 PM.
[Link] EXPERIMENT DETAILS MARKS COS
1 Table 1: Busdiv
Buscode BusDesc
01 Super Delux
02 Delux
03 Super Fast
04 Normal
Table 2: Busroute
Route_i Route_n Buscod Origin Dest Fare Dis Capacity
d o e t
100
201 33 01 Chennai Madurai 170 300 45
202 25 02 Trichy Madurai 45 100 50
203 15 03 Nellai Madurai 30 90 50
204 36 04 Chennai Bangalore 150 250 55
205 40 01 Bangalore Madurai 170 250 45
206 38 02 Madurai Chennai 160 300 50
207 39 03 Hyderabad Chennai 160 190 50
208 41 04 Chennai Cochin 148 320 55
209 47 02 Chennai Coimbatore 165 300 50
210 46 04 Coimbatore Chennai 150 300 55
1. Create the tables with the following constraints and populate the tables. (25)
CO1
Constraints
Busdiv Busroute
Buscode(primary key) Route_id (primary key)
Busdesc(Unique)
Buscode(Foreign key)
Route_no(Unique)
2. Display route_no, origin, destination from Bus route in the ascending order by route_no. CO1
(10)
3. Display the desc of bus whose fare is greater than the average fare in the table busroute.
(10)
4. Create a procedure that will increment the selected records fare in the busroute table by CO2
250 update the table. (20)
5. Create a trigger that does not allow manipulation to the table Busdesc. (10)
CO4
6. Create a function to print factorial of given number. (15)
CO4
CO4
Table 1: Journey
J_Id B_Date B_Time Route_id Buscode
01 13-Jan-97 10:00:00 201 01
02 13-Jan-97 12:00:00 201 01
03 13-Jan-97 13:00:00 201 01
04 13-Apr-97 15:00:00 202 02
05 13-Apr-97 17:00:00 202 03
100
06 13-Apr-97 19:00:00 203 04
Table 2: Ticket
1. Create the tables with the following constraints and populate the tables. (25)
Constraints
Journey Ticket
J_Id(primary key) Tick_no (primary key) CO1
Day(Notnull) J_Id(Foreign key)
Time(Notnull) Time(Notnull)
Origin(Notnull)
Dest(Notnull)
2. Display the maximum totfare for individual route id. (10)
3. Display the dates three months after the booking of tickets. (10)
4. Create a view jview from the Journey table such that it contains Day, Time and route_id CO1
as J_day, J_time, J_r_id as column headings. Update the jview such that the J_day is “20-
jan-98” where J_r_id is 201. (15) CO1
5. Create a sequence ticket where minimum value is 1 and maximum value is 20 with an
increment of 2 and starting with 1. Insert the sequence ticket into the tick_no column of
ticket table. (10) CO3
6. Create a procedure that will increment the selected records totfare in the ticket table by
100 update the table. (20)
CO4
CO4
Table 1: Ticket 100
Table 2: Ticketdetail
Tick_no T_Name Sex Age Fare
001 Latha F 24 170
001 Anand M 10 85
002 Pradeep M 30 45
002 Kuldeep M 32 45
003 Rakesh M 48 170
003 Brindha F 08 85
004 Radhika F 22 30
004 Juliat F 21 30
1. Create the tables with the following constraints and populate the tables. (25)
Constraints CO1
Ticket Ticketdetail
Tick_no (primary key) Tick_no (Foreign key)
Time(Notnull) Sex (Check constraint for
accepting either M of F)
Origin(Notnull)
Dest(Notnull)
2. Give the average of total fare from ticket table. (10)
3. Display the female passengers age and the name beginning with ‘L’. (10)
CO1
4. Display the name of the passengers who have booked their ticket in the month of
CO1
January. (10)
CO2
5. Display the journey time for passenger Latha. (10)
6. Create a database trigger that will not permit any DML operations on Ticket during
non-working days (Saturday or Sunday). (15)
CO2
7. Write a PL / SQL program to find factorial of a given number. (10)
CO4
CO4
4 Table 1: Busdiv 100
Buscode BusDesc
01 Super Delux
02 Delux
03 Super Fast
04 Normal
Table 2: Busroute
Route_i Route_n Buscod Origin Dest Fare Dis Capacity
d o e t
201 33 01 Chennai Madurai 170 300 45
202 25 02 Trichy Madurai 45 100 50
203 15 03 Nellai Madurai 30 90 50
204 36 04 Chennai Bangalore 150 250 55
205 40 01 Bangalore Madurai 170 250 45
206 38 02 Madurai Chennai 160 300 50
207 39 03 Hyderabad Chennai 160 190 50
208 41 04 Chennai Cochin 148 320 55
209 47 02 Chennai Coimbatore 165 300 50
210 46 04 Coimbatore Chennai 150 300 55
1. Create the tables with the following constraints and populate the tables. (25)
Constraints
CO1
Busdiv Busroute
Buscode(primary key) Route_id (primary key)
Busdesc(Unique) Buscode(Foreign key)
Route_no(Unique)
2. Display the route_id, origin, destination, fare from Busroute in the order of highest to
lowest fare. (10)
CO1
3. Display fare for a normal bus originating from Chennai and terminating at Cochin. (10)
4. Display the buscode and bus desc which are neither originating from Chennai nor
reaching Madurai. (10) CO1
5. Create a procedure that will increment the selected records fare in the busroute table by CO2
250 update the table. (20)
6. Create a trigger that does not allow manipulation to the table Busroute. (15)
CO4
CO4
Table 1: Busroute
Route_i Route_n Buscod Origin Dest Fare Dis Capacity
d o e t
201 33 01 Chennai Madurai 170 300 45
202 25 02 Trichy Madurai 45 100 50
203 15 03 Nellai Madurai 30 90 50
204 36 04 Chennai Bangalore 150 250 55
205 40 01 Bangalore Madurai 170 250 45
206 38 02 Madurai Chennai 160 300 50
207 39 03 Hyderabad Chennai 160 190 50
208 41 04 Chennai Cochin 148 320 55
209 47 02 Chennai Coimbatore 165 300 50
210 46 04 Coimbatore Chennai 150 300 55
5 100
Table 2: Journey
J_Id B_Date B_Time Route_id Buscode
01 13-Jan-97 10:00:00 201 01
02 13-Jan-97 12:00:00 201 01
03 13-Jan-97 13:00:00 201 01
04 13-Apr-97 15:00:00 202 02
05 13-Apr-97 17:00:00 202 03
06 13-Apr-97 19:00:00 203 04
1. Create the tables with the following constraints and populate the tables. (25)
CO1
Constraints
Busroute Journey
Route_id (primary key) J_Id(primary key)
Route_id (Foreign key)
Day(Notnull)
Time(Notnull)
2. Display the number of buses that are having destination as Chennai. (10)
3. Display the dates three months after the booking of tickets. (10) CO1
4. Display the entire busroute relation in descending order of the fare. (10) CO1
5. Create a view jview from the Journey table such that it contains Day, Time and route_id CO1
as J_day, J_time, J_r_id as column headings. Update the jview such that the J_day is “20-
jan-98” where J_r_id is 201. (15) CO3
6. Perform left outer join operations on the tables Busroute and Journey for the columns
Route_id, origin, B_Date. (10)
7. Write a PL / SQL program to find factorial of a given number. (10)
CO2
CO4
6 Table 1: Ticket 100
Table 2: Ticketdetail
Tick_no T_Name Sex Age Fare
001 Latha F 24 170
001 Anand M 10 85
002 Pradeep M 30 45
002 Kuldeep M 32 45
003 Rakesh M 48 170
003 Brindha F 08 85
004 Radhika F 22 30
004 Juliat F 21 30
1. Create the tables with the following constraints and populate the tables. (25)
Constraints
Ticket Ticketdetail
Tick_no (primary key) Tick_no (Foreign key)
CO1
Time(Notnull) Sex (Check constraint for
accepting either M of F)
Origin(Notnull)
Dest(Notnull)
2. Display the total number of peoples who have reserved their ticket. (10)
3. Display the female passengers age and the name beginning with ‘R’. (10)
4. Display the months between dob and doj. (10)
5. Display the name of the passengers who have booked their ticket in the month of
March. (10) CO1
6. Create a sequence ticket where minimum value is 1 and maximum value is 20 with an CO1
increment of 2 and starting with 1. Insert the sequence ticket into the tick_no column of
ticket table. (10) CO1
7. Create a database trigger that will not permit any DML operations on Ticket during CO2
non-working days (Saturday or Sunday). (15)
CO4
CO4
7 Table 1: Busroute 100
Route_i Route_n Buscod Origin Dest Fare Dis Capacity
d o e t
201 33 01 Chennai Madurai 170 300 45
202 25 02 Trichy Madurai 45 100 50
203 15 03 Nellai Madurai 30 90 50
204 36 04 Chennai Bangalore 150 250 55
205 40 01 Bangalore Madurai 170 250 45
206 38 02 Madurai Chennai 160 300 50
207 39 03 Hyderabad Chennai 160 190 50
208 41 04 Chennai Cochin 148 320 55
209 47 02 Chennai Coimbatore 165 300 50
210 46 04 Coimbatore Chennai 150 300 55
Table 2: Journey
J_Id B_Date B_Time Route_id Buscode
01 13-Jan-97 10:00:00 201 01
02 13-Jan-97 12:00:00 201 01
03 13-Jan-97 13:00:00 201 01
04 13-Apr-97 15:00:00 202 02
05 13-Apr-97 17:00:00 202 03
06 13-Apr-97 19:00:00 203 04
1. Create the tables with the following constraints and populate the tables. (25)
CO1
Constraints
Busroute Journey
Route_id (primary key) J_Id(primary key)
Route_id (Foreign key)
Day(Notnull)
Time(Notnull)
2. Display the buscode and Route_id which are neither originating from Chennai nor
reaching Madurai. (10)
CO1
3. Display the dates three months after the booking of tickets. (10)
4. Display the entire busroute relation in ascending order of the fare. (10)
CO1
5. Create a view jview from the Journey table such that it contains Day, Time and route_id
as J_day, J_time, J_r_id as column headings. (10) CO1
6. Perform right outer join operations on the tables Busroute and Journey for the columns CO3
Route_id, origin, B_Date. (10)
7. Create a trigger that does not allow manipulation to the table Busroute. (15)
CO2
CO4
8 Table 1: Busroute 100
Route_i Route_n Buscod Origin Dest Fare Dis Capacity
d o e t
201 33 01 Chennai Madurai 170 300 45
202 25 02 Trichy Madurai 45 100 50
203 15 03 Nellai Madurai 30 90 50
204 36 04 Chennai Bangalore 150 250 55
205 40 01 Bangalore Madurai 170 250 45
206 38 02 Madurai Chennai 160 300 50
207 39 03 Hyderabad Chennai 160 190 50
208 41 04 Chennai Cochin 148 320 55
209 47 02 Chennai Coimbatore 165 300 50
210 46 04 Coimbatore Chennai 150 300 55
Table 2: Ticket
1. Create the tables with the following constraints and populate the tables. (25)
Constraints
Busroute Ticket
Route_id (primary key) Tick_no (primary key) CO1
Route_no(Unique) Route_id (Foreign key)
Time(Notnull)
Origin(Notnull)
Dest(Notnull)
2. Select distinct route_id from busroute and ticket tables. (10)
3. Display the number of buses that are having destination as Chennai. (10)
4. Display the months between dob and doj.(10)
5. Create a view Tview from the Ticket table such that it contains Tick_no, Dob, Origin CO1
and totfare as T_No, T_book, T_origin, T_totfare as column headings. (10)
CO1
6. Create a trigger that does not allow manipulation to the table Busroute. (15)
CO1
7. Write a PL / SQL program to find factorial of a given number. (10)
CO3
CO4
CO4
9 Table 1: Journey 100
J_Id B_Date B_Time Route_id Buscode
01 13-Jan-97 10:00:00 201 01
02 13-Jan-97 12:00:00 201 01
03 13-Jan-97 13:00:00 201 01
04 13-Apr-97 15:00:00 202 02
05 13-Apr-97 17:00:00 202 03
06 13-Apr-97 19:00:00 203 04
Table 2: Ticket
1. Create the tables with the following constraints and populate the tables. (25)
Constraints
Journey Ticket
J_Id(primary key) Tick_no (primary key) CO1
Day(Notnull) J_Id(Foreign key)
Time(Notnull) Time(Notnull)
Origin(Notnull)
Dest(Notnull)
2. Display the total number of peoples who have reserved their ticket. (10)
3. Display the dates three months after the booking of tickets.(10)
4. Create a view jview from the Journey table such that it contains Day, Time and route_id
CO1
as J_day, J_time, J_r_id as column headings. Update the jview such that the J_day is “20-
jan-98” where J_r_id is 201. (15) CO1
5. Create a sequence ticket where minimum value is 1 and maximum value is 20 with an
increment of 2 and starting with 1. Insert the sequence ticket into the tick_no column of
ticket table. (10) CO3
6. Display the details of the ticket booked in the month of March. (10)
7. Write a PL / SQL program to print factorial of a given number using function (10)
CO4
CO4