SCHOOL OF COMPUTING SCIENCE AND ENGINEERING

DBMS MODEL FAT Lab

23-APR-2021 –L11+L12

Instructions
1. Give Commit Command every 10mns to save your schemas in the database.

2. Copy the content from sql prompt to word doc file frequently.

3. As this is a proctored examination, it is appropriate to turn-on the video during the en-
tire duration of the test.  However, keeping in view the network / bandwidth issues, stu-
dents are required to switch-on the video as and when it is required by the course fac-
ulty.

4.Keep your laptop with proper power backup and related software.
In your answer sheet copy all the queries from Query Number 3
Upload your text files using the link given below VTop Stu
File upload should be done only once. Only files with the naming convention registration
number.txt [example-17BCE0001.doc] will be accepted. Multiple file uploads are not allowed.

Answer Sheet - First Page Details

Course – Database Management Systems

Course Code – CSE2004
Slot – L11+L12
Date – 23/04/2022
Time – 11:30-01:00 P.M

Components Marks
DDL(15)
DML (25)
PL/SQL (10)
SET 1
Consider the following relations for an order processing database application in a
Company

Customer (Cust#, Cname, City)

Cust ID Cname City DOB

Orders (Order#, Odate, Cust#, Ord_Amt)

Order ID Cust ID Order_date Order_amt

Order_Item (Order#, Item#, Qty)

Order ID Item ID Quantity

Item (Item#, Unit_price)

Item Id Price
SET -1

Using SQL perform the following operations

1. Create the above-mentioned tables and insert all the records [10]
2. Insert the following constraints on the tables [5]
Customer Table
Cust ID  Primary key
City  Check constraint on city to check location in Texas ,Dallas or Houston
Order Table
Order ID  Primary Key ,Cust ID  Foreign Key referencing customer table
Check constraint on quantity in order table.(0<Quantity <=100)
Order Item Table
Order ID  Foreign Key referencing order table,
Item ID  Foreign Key referencing item table
Primary key  Order Id + Item ID
Not null constraint should be established on price
Item Table
Item ID  Primary key

3. Display the order ID ,Customer Id and Item ID of all customers who are above 30 years of
Age [5]
4. Update the customer name Chris parker as Chris Churchill using the replace function. [4]
5. Display the Order_ID ,Item ID and Price of all items ordered by the customer and also price
of items that are not ordered by any customers [4]
6. Display the customer ID customer name, total Order amount for all customers who have
made a total order that is more than 10000 rupees [4]
7. Display the total number of times a particular item has been ordered. For instance 441 is
present in orders with order id 21 and 40, therefore the count for item id 441 is 2. [4]
8. Using subqueries display all the customer names who are from Houston and whose order
quantity is greater than 50 from the order_items table. [4]
9. Write a Pl/Sql block to retrieve name, salary of the particular employee identified by ssn
from employee table by reading ssn value during run time [10]
SET 2
Consider the following relations for database that keeps track of business trips of
salespersons in a sales office:

Salesperson

SSN Name Join_date Dept_No

Trip

Trip ID From _City To_city Departure_date Return Date

Trip assignment

SSN Trip ID Amount

Assigned

Department

Department Department Location

number name
SET -2

Using SQL perform the following operations

1. Create the above mentioned tables and insert necessary records. [10]
2. Insert the following constraints on the tables [5]
Salesperson Table
SSN  primary key
Not null constraint should be maintained on the name column
Dept no is a foreign key referencing department table
Trip Table
Trip ID  Primary Key ,
Insert check constraints on from_city and to_city to check for the locations Dallas
,Houston or Austin
Trip Assignment Table
Trip Id  Foreign Key referencing trip table, SSN  Foreign Key referencing
Salesperson table
Trip ID + SSN  Primary Key
Department Table
Unique key constraint on department name
Department number is the primary key
3. Display the SSN number ,Name and department name of sales persons who have been
assigned trips and also Sales persons who are not assigned trip [5]
4. Display the total number of salespersons for each department who have been assigned
trips.For instance the total number of salesperson who have been assigned trips from
department number 5 is 3. [4]
5. Display the first three letters of the cities in the column from_city from the trip table for all
trips [4]
6. Display the total expense of all salesperson who have been assigned a trip that takes more
than two months from the departure date [4]
7. Using subqueries display the names of those salespersons who have been assigned a trip to
dallas city [4]
8. Create a view with Salesperson SSN,Department name, Trip ID for all salespersons whose
departure date is june 2014 [4]
 9. Write a PLSQL program to check if a given number is a palindrome [10]

SET 3
Consider the following relational schema:
Hotel (hotelno:integer, hotelname:string, city:string)

Hotel_No H_name H_city

Room (roomno:integer, hotelno:integer, type:string, price:real)

Room_No Hotel_no Price /day

Booking (hotelno:integer, guestno:integer, datefrom:date, dateto:date, roomno:integer)

Hotel_No Guest_no Check in Check out Room_no

date date

Guest (guestno:integer,guestname:string,guestaddress:string)

Guest_No Guest_name address

SET – 3

Using SQL perform the following operations

1. Create the above mentioned tables and insert necessary records. [10]
2. Insert the following constraints on the tables [5]
Hotel
Hotel_no  primary Key
Insert not null constraint on hotel_city
Room
Room_no  primary key , Hotel_no  foreign key
Insert a check constraint on price such that price should not be greater than 10000
Booking
Hotel_no ,Room_no,Guest_no  foreign Keys
Hotel_no+Room_no+Guest_no  primary key
Guest
Guest_no  primary key
3. List the hotel id,hotel name and room numbers that was booked for more than two months.
[5]
4. List the hotel_no and hotel_name and Room_no,price for hotels that have the room price
details and also the hotels that don’t have the room price details [4]
5. Using subqueries display the names of all the guests who are from new york and who have
booked the room number 3. [4]
6. Display the total price of bookings for each guest.For instance for guest id 111 the total
price of bookings is 5500 (4500 for room number 3 and 1000 for room number 8) [4]
7. Create a view that has hotel name ,guest name of all guests who have booked a room with a
check in date that is not greater than may 2012 [4]
8. Display the total number of rooms booked by each guest. For instance guest number 111 has
booked two rooms (room number 3 and room number 8) [4]
9. Write a PL SQL program to calculate area of a circle given its radius [10]
SET 4
Movie Table
Movie Id Movie Name Movie Release Collection
Date

Movie Category
Category Minimum Maximum
Collection Collection

Theatre Table
Theatre Id Theatre name Location Capacity

Movie Shown Details

Theatre ID Movi
e ID
SET - 4

Using SQL perform the following operations

1. Create the above mentioned tables and insert necessary records.[10]

2. Insert the following constraints on the tables [5]
Movie Table
Movie ID  primary Key
Insert not null constraint on Movie Name
Movie Category
Category  primary key
Insert a check constraint on minimum and maximum collection such that the
collections should not be less than zero
Theatre
Theatre Id  Primary Key
Theatre Name  Apply Unique Key Constraint
Movie Shown
Theatre Id  Foreign Key referencing Theatre Table
Movie ID  Foreign Key referencing Movie Table
Theater ID + Movie ID  Primary Key

3. Display the Movie ID,Movie Name and Category for each movie depending on the
collection [5]
4. Display the movie Id ,movie name ,Theatre Id ,Theatre name for all movies that have a
theatre and also movies which does not have a theatre. [4]
5. Using subqueries display the names of all the movies that released after May 2014 at
Marcus Theatres. [4]
6. Display the total collection for each theatre in the descending order. [4]

7. Create a view that has the Movie Id Movie name and Theatre name of all movies that come

under the flop category [4]

8. Display the theatre name and total number of movies for each theatre. [4]

9. Write a PL/SQL block to find out Simple Interest given P=10000, N=2 and R=10% [10]
SET 5:

Product Table
Product Id Product Name Manufactured Date Expiry Date
Primary Key Name should not be
null

Product details Table

Product Id Price Defect Rate
Foreign Key Should be >0 Should be >=0

Quality Table
Quality Index Minimum_Defect_rate Maximum_Defects_rate
Should be unique

Sold Table
Product Id Date Sold
Foreign Key
SET -5

Using SQL perform the following operations

1. Create the tables [7.5]

2. Insert the given records in the tables and enforce the constraints [7.5]

3. Display the details of products that will expire within three years from the date of

manufacture [5]

4. Using sub queries display the names of all products which has a manufacturing date less

than five months from the current date and whose defect rate is 0 [4]

5. Display the total number of products having quality index 1 [4]

6. Create a view name prod_view that consist only of product id product name, price and

quality index of all products that have a quality index greater than 1. [4]

7. Display total number of products sold based on the price of each product [4]

8. Display the product name of all products whose total number of products sold is greater

than 100 units. [4]

9. Write PL/SQL block to generate Fibonacci Series up to given number. [10]