Database Systems: SQL Assignment 2 - Query and Modification Tasks

This assignment, "Assignment 2 Basic (7.5%)", is designed to provide students with practical experience in formulating SQL queries and modifying database contents. It is divided into two main parts: query formulation using the SELECT statement and database manipulation using the INSERT, UPDATE, and DELETE statements. The query formulation section is further divided into two parts, with problems ranging from listing specific data from tables (cities, employees, resources, events) to more complex queries involving joins, filtering based on multiple criteria, and aggregate functions like COUNT and SUM. The database manipulation section requires students to add, modify, and delete data within the database, including inserting new facilities, locations, and modifying location names, and deleting a facility and its related locations. The assignment emphasizes the importance of understanding SQL and encourages students to formulate queries using SQL directly, rather than relying on query design tools. The assignment also includes a third part with more complex query problems, requiring the use of subqueries, aggregate functions with date and time calculations, and applying conditions across multiple tables.

Assignment 2 Basic
(7.5%)
This assignment provides experience with formulating queries and modifying the contents
of the database. For the query formulation problems, you should use the SELECT statement of
SQL. For the database manipulation problems, you should use the INSERT, UPDATE, and
DELETE statements of SQL.
To provide guidance in completion of this assignment, you should complete part 1 (query
formulation) by the end of the third week and part 2 (query formulation) and the data
manipulation problems by the end of the fourth week. The part 1 problems are not difficult so
you should be able to complete them in the first week of coverage about query formulation.
To facilitate grading, please number the SQL statements and format them neatly. I do not
need to see the result tables. Name your file as “LastNameFirstNameA2B.doc” (or .txt if using a
text file) where LastName and FirstName are your last and first name respectively.
Query Formulation Directions
Solve the following problems using the SQL SELECT statement. You may formulate the
queries in Query Design although some problems require formulation with SQL. To gain a
proper understanding of SQL, I strongly recommend that you formulate every problem in SQL
without the use of Query Design. You will be required to solve query problems using SQL on
the exam.
Query Formulation Part 1 Problems (25%)
1. List the city, state, and zip codes in the customer table. Your output should not have
duplicates. (5 points)
2. List the name, department, phone number, and email address of employees with a phone
number beginning with “5-”. (5 points)
3. List all columns of the resource table with a rate between $10 and $15 including the end
points 10 and 15. Sort the result by rate. (5 points)

8/21/2024 Assignment 2 Basic (SQL) Page 2
4. List the event requests with a status of “Approved” or “Denied” and an authorized date in
July or August 2013. Include the event number, authorization date, and status in the output.
(Hint: In Access, you must surround a date constant with pound symbols (#). In Oracle, you
surround a date constant with single quotes. Use the default date format as shown in textbook
Chapter 4.) (5 points)
5. List the location number and name of locations that are part of the “Basketball arena”. Your
query must not use the facility number (“F101”) of the basketball arena in the WHERE
clause. Pretend that the user knows only the facility name, not the facility number. Thus, the
WHERE clause should not have a condition involving the facility number compared to a
constant (“F101”). (5 points)
Query Formulation Part 2 Problems (50%)
6. For each event plan, list the plan number, count of the event plan lines, and sum of the
number of resources assigned. For example, plan number “P100” has 4 lines and 7 resources
assigned. You only need to consider event plans that have at least one line. (10 points)
7. For event requests, list the event number, event date (eventrequest.dateheld), and count of the
event plans. Only include event requests in the result if the event request has more than one
related event plan with a work date in December 2013. (10 points)
8. List the plan number, event number, work date, and activity of event plans meeting the
following two conditions: (1) the work date is in December 2013 and (2) the event is held in
the “Basketball arena”. Your query must not use the facility number (“F101”) of the
basketball arena in the WHERE clause. Pretend that the user knows only the facility name,
not the facility number. Thus, the WHERE clause should not have a condition involving the
facility number compared to a constant (“F101”). (10 points)
9. List the event number, event date, status, and estimated cost of events where there is an event
plan managed by Mary Manager and the event is held in the basketball arena in the period
October 1 to December 31, 2013. Your query must not use the facility number (“F101”) of
the basketball arena or the employee number (“E101”) of “Mary Manager” in the WHERE
clause. Pretend that the user knows only the facility name and employee name, not the

8/21/2024 Assignment 2 Basic (SQL) Page 3
facility number and employee number. Thus, the WHERE clause should not have conditions
involving the facility number or employee number compared to constant values. (10 points)
10. List the plan number, line number, resource name, number of resources
(eventplanline.number), location name, time start, and time end where the event is held at the
basketball arena, the event plan has activity of activity of “Operation”, and the event plan has
a work date in the period October 1 to December 31, 2013. Your query must not use the
facility number (“F101”) of the basketball arena in the WHERE clause. Thus, the WHERE
clause should not have a condition involving the facility number compared to a constant
(“F101”). (10 points)
Database Modification Problems (25%)
Modify the contents of your database according to the following problems. For each
problem, paste your SQL statement in the word processing document that you began with Part 1.
1. Insert a new row into the Facility table with facility name “Swimming Pool”. (5 points)
2. Insert a new row in the Location table related to the Facility row in modification problem 1.
The new row should have “Gate” for the location name. (5 points)
3. Insert a new row in the Location table related to the Facility row in modification problem 1.
The new row should have “Locker Room” for the location name. (5 points)
4. Change the location name for all locations of the swimming pool facility. The new location
name should be “Door”. You must not use the facility number of the swimming pool in your
UPDATE statement. You also cannot reference specific location numbers in your SQL
statement. (5 points)
5. Delete the swimming pool facility and all related locations. When deleting the locations,
you must not use the facility number of the swimming pool in your DELETE statement.
Pretend that the user knows only the facility name, not the facility number. In addition, your
solution should not rely on changing the design of the ICA database. In the design, the
relationship from Facility to Location has the RESTRICT option for referenced rows. You
cannot change the option to CASCADE. (5 points)

8/21/2024 Assignment 2 Basic (SQL) Page 4
Query Formulation Directions
Solve the following problems using the SQL SELECT statement. You may formulate the
queries in Query Design although some problems require formulation with SQL. To gain a
proper understanding of SQL, I strongly recommend that you formulate every problem in SQL
without the use of Query Design. You will be required to solve query problems using SQL on
the exam.
Query Formulation Part 3 Problems
1. List the event number, customer name, contact name, and authorization date where the event
request has been approved but there is not an event plan. (Hint: see textbook Chapter 9 for
similar problems.) (20 points)
2. List the plan number, count of the lines in the plan, and the sum of the resource cost for event
plans at the basketball arena with a work date in October 2013. You should only include
event plans with at least one event plan line. The resource cost is the number of hours times
the number of resources times the rate of the resource. The number of hours staffed is the
difference in minutes between the start and end time divided by 60. In Access, the number of
hours can be computed using the expression DateDiff("n", timestart,
timeend)/60. In Oracle, the number of hours can be computed as (TimeEnd –
TimeStart) * 24. You should use the entire expression inside the SUM function.
Your query must not use the facility number (“F100”) of the football stadium. Pretend that
the user knows only the facility name, not the facility number. Thus, the WHERE clause
should not have a condition involving the facility number compared to a constant (“F100”).
(20 points)
3. List the event number, date requested, date authorized, plan number, work date, and name of
the supervising employee of events meeting the following conditions: (1) the date requested
and date authorized are in June 2013, (2) the event request has “Approved” status, and (3)
there is at least one event plan. Include a row in the result even if there is no supervising
employee for the event plan. (20 points)
4. For each location in the football stadium, list the number of event planning lines in October
2013 and the sum of the hours staffed. You should only include locations related to at least
one event plan line in October 2013. The output should include the location number, location

8/21/2024 Assignment 2 Basic (SQL) Page 5
name, number of event planning lines, and sum of the hours staffed. The hours staffed is the
difference in hours between the start and end time. In Access, the number of hours can be
computed by using the expression DateDiff("n", timestart, timeend)/60 in
the SELECT clause. In Oracle, the number of hours can be computed as (TimeEnd –
TimeStart) * 24. Your query must not use the facility number (“F100”) of the football
stadium. (Hint: use an expression inside the SUM function.) Pretend that the user knows only
the facility name, not the facility number. (20 points)
5. List the plan number, activity, and work date of plans meeting following conditions: (1) the
work date occurs in the December 2013 and (2) the plan uses all resources with a rate greater
than $15. For example, if there are five resources with a rate > 15, a plan must use all five
resources. For this problem you should read textbook Section 9.3 and Appendix 9.A. (20
points)