Assignment 5: Microsoft Access Queries and SQL Statements

Assignment 5:
Microsoft Access Queries and SQL Statements

Project 1: Written SQL Statements
Your company has a number of employees that are MANAGERS. Each MANAGER may ‘manage’
one or more PRODUCT. The database stores the date the MANAGER was assigned to each
PRODUCT, the number of employees each manages per PRODUCT and the location of where each
PRODUCT is manufactured. The database also stores the price of each PRODUCT.
Using the database shown above and create a Microsoft Word document containing the SQL
statements (as shown in the lecture materials) to do the following:
1. Find the Manager ID, first name, last name, phone number and province for managers who
live in Peterborough.
2. Modify the employee information for Manager ID equal to 3137 to your first name, last name
and full address. (you may use a fake address, but must be your actual name).
3. Find only the Manager’s first and last name and product managed ID for all assigned dates
that took place in March 2018.
4. Find the Product Name, Product Price, Number of Employees Managed, Assigned Date, and
Manager’s Last Name for the products that have less than 50 employees being managed.
5. Find the number of products that you manage and save the result in a field labeled:
Products Manages. (You MUST use your name in the query, NOT your Manager ID).
6. Find the Managers first and last name, manager’s phone number, Assigned Date, Location
Name and Product Name for all products that cost less than $100.00.
Save the SQL statements in the Word document named "youraccountname_sql.doc(x)” .
HINT 1:
it is very easy to tell if a value is stored as a number or as text in an MS Access table.
-if the value is right justified (pushed to the right) then it is stored as a number.
-if the value is left justified (pushed to the left) then it is stored as a text.
HINT 2:
remember how field names with spaces are treated differently from field names without spaces.
HINT 3:
remember, round brackets are required to be used with all INNER JOIN statements.
The format (how the SQL statements are written) MUST match the style shown in the notes.
Each SQL reserved word MUST appear on their own line and in capital letters in the document.
Each SQL Statement MUST be indented as shown below. You will lose major marks otherwise.
example:
SELECT
something
FROM
( somewhere
INNER JOIN
somewhere else ON some condition )
WHERE
some condition is true;
This is non-optional. You MUST use this standard.
You will be graded on adhering to this standard.
You MUST write the SQL without the use or aid of any electronic method.
For example: You can NOT use MS Access Query Builder to create the SQL graphically and then
copy or type in the result to your Word document. You will be graded on adhering to this standard.
You must identify yourself on the document. You will lose marks if this is missing.
Somewhere visible on the beginning (top) of the Word file you must include:
- your first and last name
- your Western ID (see below for a description of your Western ID)
- your student number
Save your SQL statements in the Word file named "youraccountname_sql.doc(x)” and attach the file
to your submission.
Project 2: Queries using Query Designer in Microsoft Access
Create a brand new, blank database and name it “youraccountname_Trains.accdb (.mdb)”.
(substitute youraccountname with your actual account name.)
Using the XML Import utility in MS Access import the data in the file Assign5.xml.
This will import five (5) tables with data into your database. Everything is set by this XML and the
schema. No further action or intervention is required by the students on the MS Access file.
This database represents a listing of PASSENGER(s), the destination(s), and the CONDUCTOR(s)
for the Ampax Train Line.
It also contains information on which CONDUCTORS drive (trains) to which destination and for
which destination the PASSENGERS have purchased tickets.
Create a query using the Graphical Query Design Tool in the Create tab in MS Access for each of
the following and name each query as follows: QueryA, QueryB, etc. so they match:
A. Create a query that changes the conductor’s name with the CID of 001289348 to your first
and last name.
B. Find the Province, TrainStation, Gate and Class for all of the DESTINATIONS whose
TrainStations end with the designation of ‘1032’ and have a ‘B’ Gate.
C. Find the Destination Identification number (DID), Province, TrainStation and Gate for all of
the trains taken by the Passenger with the PassengerID is equal to "0056348".
D. Find the passenger number (PassengerID), first name and last name for all passengers who
have bought tickets for a train to any TrainStation in Alberta. In addition to showing the
passenger number, passenger first name and passenger last name, show the TrainStation
and Gate as part of the resulting dataset. If a passenger has taken more than one train to
Alberta that passenger will appear more than once in the resulting dataset.
E. Find the total number of tickets with the Destination of Alberta. Passengers who have taken
more than one train to Alberta will be included in the answer for each train (ticket) that they
have taken. Label this result field as: Alberta Trains
F. Find the passenger first name, passenger last name, Passenger Identification number
(PassengerID), TrainStation, Gate and date of the train for all of the trains that you are the
conductor. (use your last name and first name in the query not the CID). List the
passengers in ascending order by the passenger’s last name.
Save your database in the file you named "youraccountname_Trains.accdb (.mdb)” and include the
file to your submission to OWL.
Submission Instructions:
You must upload, attach and submit, via the CS1032 OWL/Sakai course site, the following 2 files:
• youraccountname_sql.docx (for later versions) (.doc – for Word 2003 or earlier)
• youraccountname_Trains.accdb (for later versions) (.mdb – for Access 2003)
NOTE: This description of the assignment contains instructions that tell you to create files with names
that have a specific format. In these file names; the “youraccountname” is your university username.