midterm.exam.pdf

School of Computing Science and Engineering
DBMS
MID TERM EXAM
Slot : l23+l24


Question 1)

Consider the following relational schemas

Passenger(pname: varchar2(10); pnr_number: number(8); amount: number(5,2); doj: date; status:
varchar2(3))
Reserved_for(pnr_number: number(8); train_no: number(5))
Train(train_no: number(5); train_name: varchar2(10); source: varchar2(15); destination: varchar2(15))

Note: ‘doj’ stands for date of journey.
Status may be ‘cf’(confirmed), ‘ncf’ (not confirmed), and ‘c’(cancelled).

Do the following
1. Create the above tables with composite primary on Passenger table and primary key on Train table.
Enforce the foreign keys on fields of ‘Reserved_for’ table. (b-5)
2. See that ‘status’ field value should be any one of these ‘cf’ , ’ncf’, and ‘c’. Also see that ‘source’ and
‘destination’ fields should never be empty. Ensure that the date of journey should be not more than 30
days from date of reservation. (c-5)
3. Add at least 6 records to each table as per the requirements of queries given as below. (e-5)
Mandatory: Display the structure and content of each table for each change and store it in your file to
be uploaded. It carries 5 marks- (b,c,e-5)

Answer the following queries in SQL and obtain required output (Output for each query be there in
your file and it carries half of the marks allotted to the query)
4. List tickets numbers along with train names that travels from ‘Bangalore’ to ‘Chennai’ on journey
dates between 13-November-2015 and 15-November-2015. (b-2)
5. Display date of journey field values in the format ‘ddth of month yyyy’ by adding one day to it. (c-2)
6. Get the total number of tickets reserved on each date of journey. (e-2)
7. List the ticket numbers whose amount is more than the amount of every ticket on 13-November-
2015. (Sub Query) (i-4)
8. Create a virtual table to display ticket numbers along with train numbers and train names on a
particular date of journey those travel from ‘Bangalore’ to ‘Tirupati’ (k-5)
-----------------------------------------------------End of set 1 ---------------------------------------------------------------------

,Question 2)

Consider the following relational schemas

Actor( aname: varchar2(20); role: varchar2(15); sex: char; amount :number(6); city:
varchar2(10))
Performs(aname: varchar2(20); title: varchar2(20))
Skit(title: varchar2(20); theme: varchar2(15); duration: number(3); rating:varchar2(3))


Do the following

1. Create the above tables with primary keys on appropriate fields. Enforce the foreign keys on fields of
‘Performs’ table. (b-5)
2. Put a check constraint to see that rating of the skit should be any one of these A, A+, and
A++. Also see that the fields ‘role’ and ‘amount’ should be unique and not empty respectively.
Include ‘street’ column in the’Actor’ table of string data type. (c-5)
3. Add at least 6 records to each table as per the requirements of the queries given below (e-5)
Mandatory: Display the structure and content of each table for each change and store it in your file to
be uploaded. It carries 5 marks- (b,c,e-5)

Answer the following queries in SQL and obtain required output (Output for each query be there in
your file and it carries half of the marks allotted to the query)
4. Change the city to ‘Chennai’ of an actor whose name start with ‘J’ and acted in the skit with
title ends with ‘n’. (b-2)
5. Display number of male actors acted and their total remuneration skit wise. (c-2)
6. Retrieve names of actors who perform in more than or equal to 2 skits. (e-2)
7. List skit names in which female artists acted is more than or equal to male artists acted. (Sub
Query) (i-4)
8. Create a virtual table that shows skit names with rating A+ and in which actors have been paid an
amount less than or equal to Rs.25000. (k-5)


----------------------------------------------End of set 2-----------------------------------------------------------------------------
-

, Question 3)
Consider the following relational schemas

Singer(singerid: number(4); singer_name: varchar2(20); city: varchar2(15); street: varchar2(10);
city: varchar2(15))
Sings(singerid: number(4); lyricid: number(4))
Lyric( lyricid: number(4); duration: number(3); dor: date; lyric_writer: varchar2(20); movie:
varchar2(25))

Note: ‘dor’ stands for ‘date of recording’.

Do the following

1. Create the above tables with primary on appropriate fields. Enforce the foreign keys on the
fields of ‘Sings’ table. (b-5)
2. Ensure that duration of each lyric should be not less than 5mins and not more than 7 mins.
Also ensure that field ‘singer_name’ should not be null. Eliminate ‘street’ field. (c-5)
3. Add at least 6 records to each table as per the requirements of queries given below (e-5)
Mandatory: Display the structure and content of each table for each change and store it in your file to
be uploaded. It carries 5 marks- (b,c,e-5)

Answer the following queries in SQL and obtain required output (Output for each query be there in
your file and it carries half of the marks allotted to the query)
4. Display lyricid sung by each singer and then get total number of lyrics sung by each singer.
(b-2)
5. Retrieve maximum, minimum and average duration of songs for each movie. (c-2)
6. Display date of recording of the song by adding one month to each one of them. (e-2)
7. Retrieve the lyric whose duration is more than the duration of any other lyric in the movie
‘Khiladi’. (Sub Query) (i-4)
8. Create a virtual table that displays singer names in capitals, movie title in smaller letters and the
length of movie titles. (k-5)




---------------------------------------------------End of set 3------------------------------------------------------------------------
-