SQL Assignment 2- 330

Week 3 Help Desk SQL Assignment

We are going to follow the same study plan as last week. You will:

· There are two parts to this document.

· Part 1 doesn’t have anything to turn in but contains important directions

· Part 2 contains the assignment you do need to turn in

· Go through this document and write SQL to answer all the questions listed below. As part of your SQL, you will capture screenshots and paste them where indicated in Part 2.

· Refer to this document as you complete the Week 3 SQL Check online quiz which verifies your homework. You may attempt the Week 3 SQL Check twice. Your highest score will be posted to the Grades area. The questions may change from one attempt to the next.

· You are welcome to use the quiz results to update your homework file.

· When you have completed the online quiz, submit the Word document.

Part 1 – Set up your Database

We are now working with a database which notes helpdesk action.

There are two tables. First, there is the employee table, which contains the employee’s ID as the primary key, first name, last name, and location. You can assume all employees at our company are listed here.

Second, there is the ticket table, which contains each individual helpdesk ticket. The ticket table contains the ticket ID as the primary key, a description of the helpdesk incident, the duration in minutes, a priority, a status, and which employee (if any) has been assigned to the ticket. If nobody is available, a ticket will be logged into the system in this table, but no employee will be assigned yet.

The incident status can be completed, in progress, or not started. If an incident is completed, the duration field will contain the number of minutes the incident took and the employee ID will reference the employee who completed the service.

If an incident is in progress, the duration field number denotes the number of minutes since the incident started and the employee ID will reference the employee who is currently working on the service (assume for now that all incidents will be completely serviced by the single employee who is assigned to them.)

If an incident service is not yet started, the duration field will be blank, and there will not be an employee assigned to that incident, either.

Let’s get started. One difference this week in preparing the database for use in SQLiteonline.com, is that we have separated the database creation (Setup) and database population (INSERT) into two scripts, two steps. You must create the database before inserting data.

1. Open the “Helpdesk_setup.sql” in Notepad and run it (Forgot how? You can refer back to how you used the SQL script and setup the database in Week 1 and 2 Mythical Creatures).

2. Open and run the “Helpdesk_insert.sql” script to insert data into the helpdesk database.

3. Run the following counts to verify your helpdesk database is up and running properly:

a. SELECT COUNT(*) from employee; (Should be 7 records)

b. SELECT COUNT(*) from tickets; (Should be 30 records)

Name: _________________________________

Part 2 –Queries with Join

This is where you start to turn things in. To turn in your assignment, delete the preceding pages and start with this one. Make sure to put your name up top.

Construct SQL to answer each of the following questions. For each query, paste your SQL code below, and also paste a screenshot of the first 10 or so rows the query gives you.

Give an answer to the question based only on what you see in your SQLite database. (Do not use Excel or other tools to answer these questions; since we are trying to learn SQLite here, you should do these exercises in SQLite.)

You will eventually submit this document as part of your week’s assignment.

After you have completed your first draft of this document, you will use the Week 3 SQL Check online quiz to verify your answers. The Week 3 SQL Check online quiz uses the same numbering system given here, so you can quickly find the question you need.

You are welcome to update your assignment document with results from the online quiz, and submit the document after you’ve checked your results with the Week 3 SQL Check.

Write SQL queries to answer the following questions:

1. [SQL Week 3 Question 300-001] Print the first name, last name, ticket ID, ticket description, and duration of the employees who are assigned with a ticket(s). Sort them first alphabetically by first name (starting with letter A at the top) and then ascending by Duration.

<Paste/Enter SQL Code here>

<Paste Screenshot here>

2. [SQL Week 3 Question 300-002] We want to get a workload by employee printout. For every employee, print the first name, last name, all ticket ID numbers assigned to that employee, the ticket description, and ticket duration of our records. Include only tickets which have been assigned to an employee. Include all employees, even if that person doesn’t have any tickets in the current database. Sort them first alphabetically by first name (starting with letter A at the top) and then ascending by ticket duration.

<Paste/Enter SQL Code here>

<Paste Screenshot here>

3. [SQL Week 3 Question 300-003] <NO QUESTION 300-003>

4. [SQL Week 3 Question 300-004] Management wants to pay special attention to calls which are in progress and for which the duration is currently 20 minutes or longer (include calls of exactly 20 minutes duration). Print the ticket ID number, description, duration, priority, status, and the employee first name for all of these calls.

<Paste/Enter SQL Code here>

<Paste Screenshot here>

5. [SQL Week 3 Question 300-005] You suspect there has been a malware breach at the Maryland facility. They’re not sure if it’s an inside job (involving one of your employees) or an outside job (involving an outside attack.) Make a listing of all high priority helpdesk tickets which have either been identified as malware, or which were taken by an employee whose location is in the Maryland facility. Print the description, duration, ticket ID number, ticket status, employee first name, employee location, and priority of ticket. Sort it alphabetically by description, then by duration (longest duration on top), then ascending by ticket ID number.

<Paste/Enter SQL Code here>

<Paste Screenshot here>

6. [SQL Week 3 Question 300-006] We want to see the total duration for completed helpdesk calls by employee. Only include employees who have completed helpdesk tickets; if an employee doesn’t have any completed tickets, their name should not show up in this report. Generate a report for which the

· First column contains the employee’s first and last name (don’t use two columns for this – put the first and last name together)

· Second column contains the total duration of all completed tickets by that employee. If an employee had more than one ticket, add the durations together.

· Report is sorted so the largest total duration is on the top, and then alphabetically by employee name (sort it using the “First Last” combination, so that “Mickey Mouse” would come before “Nancy Mouse”.)

· Only include completed tickets; don’t include those in progress or not yet started.

· Hint: Be sure to review the Concatenation tutorial in the LEO classroom.

For example, assume your initial data was as follows.

Employee First Name	Employee Last Name	Ticket status	Ticket Duration
Mickey	Mouse	Completed	5
Mickey	Mouse	Completed	10
Donald	Duck	In Progress	15
Donald	Duck	Completed	20
Donald	Duck	Completed	25
Donald	Duck	Completed	30

Your report would contain the following information:

Employee Name	Duration of completed tickets
Donald Duck	75
Mickey Mouse	15

Note that Mickey Mouse has two calls here, both of which were completed, so their duration is 5 + 10 = 15. Donald Duck has four calls here, but only three of them are completed, so his duration is 20 + 25 + 30 = 75 minutes; Donald’s 15 minute call is still in progress and is not counted here. And Donald’s 75 minutes is the largest number of minutes, so it goes on top.

<Paste/Enter SQL Code here>

<Paste Screenshot here>

7. [SQL Week 3 Question 300-007] Do the same as Problem 6 above, except in addition to the sum of the ticket durations, also include an additional column to give us the number of completed tickets as well. Now sort your database so the largest number of completed tickets is on the top.

Here's a sample of the output for Mr. Duck and Mr. Mouse.

Employee Name	Duration of completed tickets	Total number of completed tickets
Donald Duck	75	3
Mickey Mouse	15	2

<Paste/Enter SQL Code here>

<Paste Screenshot here>

8. [SQL Week 3 Question 300-008] You want to learn more about the average duration of all tickets taken by employees in California. Include all tickets which have been completed or are in progress by employees in California. Sort them alphabetically by priority, and then alphabetically by ticket description. Additionally, group them first by priority (highest on top), then alphabetically by description. So your final report will look something like this (your data may vary):

Priority	Description	Average duration of tickets
1-High	Malware	99
1-High	Virus	12
2-Medium	Internet	5
2-Medium	Malware	8
2-Medium	Virus	3 etc.

9. [SQL Week 3 Question 300-009] Your management wants to know what the different types of tickets are. Write a query that uniquely identifies ticket type based on the ticket description.

<Paste/Enter SQL Code here>

<Paste Screenshot here>

Week 3 Helpdesk SQL for Students

Page 2