Week 3 Help Desk SQL Assignment: We Are Going To Follow The

Week 3 Help Desk Sql Assignmentwe Are Going To Follow The Same Study P

Week 3 Help Desk Sql Assignmentwe Are Going To Follow The Same Study P

This assignment involves creating and querying a help desk database with two tables: employee and tickets. You will set up the database with provided SQL scripts, verify the data, and then write queries to answer specific questions about help desk tickets and employees. For each question, you will provide the SQL code and a screenshot of the query results. The questions require you to join tables and filter data according to various conditions, such as ticket status, priority, duration, location, and employee assignments. You will also use aggregate functions, concatenation, and sorting to generate reports relevant to help desk management. The assignment emphasizes practicing SQL joins, filtering, grouping, and ordering in a real-world help desk context.

Paper For Above instruction

The objective of this assignment is to demonstrate proficiency in SQL querying within the context of a help desk database system. The task involves creating a structured database, inserting data, and writing complex SQL queries to extract meaningful reports that support help desk management decisions. The database schema consists of two tables: employee and tickets. The employee table records staff details, including ID, first name, last name, and location, while the tickets table captures help desk incidents, including descriptions, durations, priorities, statuses, and assigned employees.

The initial setup involves executing two SQL scripts: one to create the database schema and another to populate the tables with data. After ensuring the data is correctly entered, students are tasked with answering a series of questions through SQL queries, focusing on the relationships between employees and tickets. These questions include retrieving lists of employees with their tickets, filtering tickets based on status and duration, identifying high-priority malware-related tickets, summing durations of resolved tickets per employee, counting the number of completed tickets, analyzing ticket durations based on employee location, and identifying distinct ticket types based on descriptions.

Each question requires writing a specific SQL statement that joins the employee and ticket tables, applies filters using WHERE clauses, groups data with GROUP BY statements, and sorts results with ORDER BY. Students are instructed to copy and paste the SQL code and provide screenshots of query results, ensuring the queries reflect only the data visible in the database. This practice reinforces understanding of relational joins, aggregate functions, and sorting in SQL, essential skills for effective database management and reporting in help desk operations.

The assignment concludes with additional practice on report generation, including aggregating total durations, counting tickets, calculating averages, and identifying unique ticket descriptions. These exercises help develop skills in data analysis, report creation, and insight extraction, critical for managing and optimizing help desk workflows. The station-wide focus on ethical reporting ensures students appreciate the importance of privacy, accuracy, and fairness in data analysis and presentation, adhering to ethical guidelines similar to those outlined by the American Psychological Association.

Questions and SQL Solutions

1. List employees assigned to tickets with their details

SQL:

SELECT e.FirstName, e.LastName, t.idTickets, t.Description, t.Duration


FROM employee e


JOIN tickets t ON e.idEmployee = t.Employee_idEmployee


WHERE t.Employee_idEmployee IS NOT NULL


ORDER BY e.FirstName ASC, t.Duration ASC;

Screenshot: (Include a screenshot of the first 10 rows of the query results here.)

2. Workload by employee including tickets, even if no tickets assigned

SQL:

SELECT e.FirstName, e.LastName,


GROUP_CONCAT(t.idTickets) AS TicketIDs,


GROUP_CONCAT(t.Description) AS Descriptions,


GROUP_CONCAT(t.Duration) AS Durations


FROM employee e


LEFT JOIN tickets t ON e.idEmployee = t.Employee_idEmployee


WHERE t.Employee_idEmployee IS NOT NULL


GROUP BY e.idEmployee


ORDER BY e.FirstName ASC, MIN(t.Duration) ASC;

Screenshot: (Include a screenshot of the result here.)

3. NO QUESTION provided

4. Tickets in progress with duration ≥ 20 minutes

SQL:

SELECT t.idTickets, t.Description, t.Duration, t.Priority, t.Status, e.FirstName


FROM tickets t


JOIN employee e ON t.Employee_idEmployee = e.idEmployee


WHERE t.Status = 'In Progress' AND t.Duration >= 20;

Screenshot: (Include a screenshot of the results here.)

5. High-priority malware or Maryland employees' tickets

SQL:

SELECT t.Description, t.Duration, t.idTickets, t.Status, e.FirstName, e.Location, t.Priority


FROM tickets t


LEFT JOIN employee e ON t.Employee_idEmployee = e.idEmployee


WHERE t.Priority LIKE '%High%'


OR e.Location = 'MD'


ORDER BY t.Description ASC, t.Duration DESC, t.idTickets ASC;

Screenshot: (Include a screenshot here.)

6. Total duration of completed tickets per employee, sorted descending

SQL:

SELECT e.FirstName || ' ' || e.LastName AS EmployeeName,


SUM(t.Duration) AS TotalDuration


FROM tickets t


JOIN employee e ON t.Employee_idEmployee = e.idEmployee


WHERE t.Status = 'Completed'


GROUP BY e.idEmployee


ORDER BY TotalDuration DESC, EmployeeName ASC;

Screenshot: (Include screenshot.)

7. Number of completed tickets and total duration per employee, sorted by most tickets

SQL:

SELECT e.FirstName || ' ' || e.LastName AS EmployeeName,


SUM(t.Duration) AS TotalDuration,


COUNT(t.idTickets) AS CompletedTickets


FROM tickets t


JOIN employee e ON t.Employee_idEmployee = e.idEmployee


WHERE t.Status = 'Completed'


GROUP BY e.idEmployee


ORDER BY COUNT(t.idTickets) DESC, EmployeeName ASC;

Screenshot: (Include here.)

8. Average duration of tickets for California employees, grouped by priority and description

SQL:

SELECT t.Priority, t.Description,


AVG(t.Duration) AS AvgDuration


FROM tickets t


JOIN employee e ON t.Employee_idEmployee = e.idEmployee


WHERE e.Location = 'CA' AND t.Status IN ('Completed', 'In Progress')


GROUP BY t.Priority, t.Description


ORDER BY t.Priority ASC, t.Description ASC;

Screenshot: (Include here.)

9. List unique ticket types based on description

SQL:

SELECT DISTINCT Description


FROM tickets;

Screenshot: (Include here.)

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th Edition). Pearson.
  • Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
  • Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, and Management. Thomson Course Technology.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th Edition). McGraw-Hill Education.
  • Date, C. J. (2012). Database Design and Relational Theory: Normal Forms and All That. O'Reilly Media.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • U.S. Census Bureau. (2020). State Population Data. https://www.census.gov
  • Technical documentation for SQLite. (2021). https://sqlite.org/docs.html
  • Laudon, K. C., & Traver, C. G. (2021). E-commerce 2021: Business, Technology, Society. Pearson.
  • MySQL AB. (2023). MySQL Reference Manual. https://dev.mysql.com/doc/

Related Assignments