Capital University of Science and Technology
Department of Computer Science
CS2313 – Introduction to Database Systems
Sample Midterm (Lab)
Semester: Spring 23
Instructor: Muhammad Shoaib
In this exam, you will create a small database called AMUSEMENT PARK from the ER model shown in Figure 1
and later query it to retrieve useful information. This will involve you creating the table structures in SQL Server
using the CREATE TABLE command. In order to do this, you are provided with a data directory (see Table 1) that
lists appropriate data types for each of the table structure along with any constraints that have been imposed (e.g.
primary and foreign key).
Figure 1: ER Model for AMUSEMENT PARK Database
Table 1: Data Dictionary for AMUSEMENT PARK Database
2 Tasks
2.1 Creating Database
Create a database called AMUSEMENT PARK and then select the database for use. After that, create the table
structures for the database based on the ER model in Figure 1 and data directory in Table 1.
2.2 Loading Data
For each table structure, a file is provided as Comma Separated Values (CSV) format file. Every line in the CSV file
contains row data with attributes aligned to that schema. You have to bulk insert the data in CSV files into the
respective table structures using the following syntax.
BULK INSERT <table name>
FROM ’ path/ to / table file ’
WITH
(
FIELDTERMINATOR = ’ , ’ , −− CSV field delimiter
ROWTERMINATOR = ’ 0x0a ’ , −− Shift the control to next row
ERRORFILE = ’ path/ to / table error file ’ ,
TABLOCK
)
You can access the data by double-clicking the Data.7z package.
2.3 Querying Database
1. Write a query to display all Theme Parks except those in the UK.
2. Write a query to display the count of number of sales that occurred on the 26th November 2013.
3. Write a query to display the count of number of tickets for which price is between €20 and €22.
4. Display all attractions that have a capacity of more than 85 at the Theme Park FR1001.
5. Write a query to display the hourly rate for each attraction where an employee had worked, along with the hourly
rate increased by 20%. Your query should only display the ATTRACTION NO, HOUR RATE and the HOUR
RATE with the 20% increase.
6. Write a query to count all the unique employees that exist in the HOURS table.x
7. Display the employee numbers of all employees and the total number of hours they have worked.
8. Show the attraction number and the minimum and maximum hourly rate for each attraction ordered by attraction
number.
9. Display all information from the SALES table in descending order of the sale date.
10. Write a query to display the attraction number, employee name and the date they worked on the attraction. Order
the results by the date worked.
11. Display the park names and total sales for Theme Parks who are located in the country ’UK’ or ’FR’.
12. Write a query which lists the names and dates of births of all employees born on the 14th day of the month.
13. Write a query which lists the approximate age of the employees on the company’s tenth anniversary date
(11/25/2016).
14. Write a query which generates a list of employee user passwords, using the first three digits of their phone
number, and the first two characters of their name in lower case. Label the column USER PASSWORD. You should
also display the name and phone number of employee.
15. Write a query which displays the last date a ticket was purchased in all Theme Parks. You should also display
the Theme Park name. Print the date in the format 12th January 2017.