ITM 209 Spring 2020
Assignment #2: Database Querying
Due: 11:59 pm on February 28, 2020 into the D2L drop box
Points: 60 points (20 point penalty if less than 24 hours late, 30 point penalty if more than 24 hours late. After 48 hours, no credit will be granted.)
Purpose:
Be familiar with –
• Relational tables within a database
• Creating queries using SQL language
Tools / Materials Needed:
• Teleworks.sql exported schema & data file from D2L
• MySQL Workbench
Deliverable(s) to Submit:
• Microsoft Word completed answer sheet (firstInitial.lastName-TeleworksAnswers.docx) from D2L containing the following:
• Your queries copied into the answer sheet (MS Word)
• Screenshots of the query, query results and action output showing the record count from the query for each of the queries. Do not include just the query logic itself.
Scenario: The same manufacturing company from Assignment 1, Teleworks, has created and starting using the database for recording call center information. Now that they have the database with information in it, they need some queries created that give specific information they need to analyze.
Task: You have been hired to develop the queries that they need for their daily monitoring and operational needs.
• Import the SQL script with the tables and data posted with the assignment to D2L into MySQL. Refer to Appendix A for import instructions.
• Alter each table to have the name prefixed with your initials + your last name. Example: tjdixoncustomers
To alter the table, right click on the table and then click alter table. Rename the table in the field that appears and then hit apply. After renaming the table, you need to refresh the schema view in the navigator to the left. Refer to Appendix B for details.
• Develop the following five queries. Be sure to keep a copy of the SQL logic and screenshots in the provided answer sheet on D2L. You can also save queries as .sql files to open later using the menu option File -> Save Script As.
• QUERY 1: List the number of closed issues for each consultant. Include their consultant ID, first and last names, email addresses, and the number of their closed issues. Do not show the status ID or status description (if you’re using any one of them for the criteria purpose). Have the output column labeled Number of Closed Issues. Order by the consultants with the most closed.
• QUERY 2: Which consultants serve customers in the state of Michigan? List the customers’ (contact) names and the consultants’ last names. Do not show the field of state. Do not show redundant records. Order by the consultants’ last names.
• QUERY 3: Teleworks wants to find which customers have issues of which the priority level is critical and contact them to ask for their opinions. List their contact names, phone numbers, issue description, and corresponding consultants’ last name. Do not show the field of Priority ID or PriorityLevel.
• QUERY 4: Teleworks wants to figure out some metrics on their customers and what is outstanding. They would like to list all customers (contact name and phone number) and the total number of open critical priority issues and closed critical priority issues.
The count for open issues will be on a separate line from the count of closed issues.
The list needs to include all customers whether or not they have any critical issues.
Include the field with the status description (open or closed) in the output.
Sort by the customer contact name and then status description (both alphabetically).
• QUERY 5: It costs Teleworks $20 for each customer support call processed. These costs reflect processing time and labor. How much has each customer cost Teleworks by making support calls (Issue) that have been closed? For each customer, list their contact name, state, and costs of customer. Sort from the most expensive customers to the least expensive. Label the column in the results with the costs of the customer as Costs of Support.
• Submit the deliverable listed above to the drop box.
Grading Scale:
Partial credit may be granted for queries that were close but not exact at the discretion of the instructional team.
Item
Possible Points
Complete submission with all deliverables included. Instructions followed appropriately.
10
Queries
Query One
10
Query Two
10
Query Three
10
Query Four
10
Query Five
10
Total Possible
60
Academic Integrity is essential.
Appendix A – Importing Data (generic instructions):
Background – the import process is simply running a script that creates a schema (MySQL database), then creates the tables, then imports the data. This is all contained within the .sql file that is provided on D2L. Therefore we can open the script and execute it the same as we would a query.
Instructions:
Click File -> Open SQL Script
Browse to where you saved the .sql file and open the script. (ex. teleworks.sql)
After opening, click the lightning bolt to execute the script. This should import the data.
Appendix B – Renaming Tables:
Right click on the table name and click Alter Table.
Update the table to have your initials and last name prefixed on the table. Click apply. After the operating completes, refresh the table list in the schema view through the navigator on the right of the screen.