MY SQL HOMEWORK Homework 2. Due by Friday, Nov. 19 11:59 PM. Please copy all your queries (sql code) from MySQL Workbench and paste them into this Word

MY SQL HOMEWORK Homework 2. Due by Friday, Nov. 19 11:59 PM.

Please copy all your queries (sql code) from MySQL Workbench and paste them into this Word document for each question. Upload this Word file with your answers to the HW2 assignment folder on Blackboard. Check the file after uploading it.

YOU WILL HAVE 5 ATTEMPTS. Please submit once if possible and use other attempts if the submitted file in the first attempt was wrong or corrupted or if you find major mistakes in the first attempt. The last attempt will be graded (as long as it is before the deadline).

The create_safety.sql script creates a database which contains the 6 tables described below. The data contains safety-related information for 179 company branch locations. To test your queries, create those tables using the create_safety.sql script.

Locations

Field Description

Location_ID (pk)

4 digit location id number

Headcount

Number of employees at locations

Division

2 character division code

State

Location state

City

Location city

Employee_Safety_Committee

Yes/No indicator of safety committee at location

Audits

Field Description

Location_ID (pk and fk)

4 digit location id number

Audit_Date (pk)

Date of audit

Auditor

Name of head auditor

Audit_Findings

Pass/Fail indicator of audit results

Corrective_Action (Unique)

5 digit CA number, if any (default = NULL)

Correctiveactions

Field Description

Corrective_Action (pk and fk)

5 digit CA number, if any (default = NULL)

Audit_Date

Date of audit

Complete_Date

Date CA was completed

Trainings

Field Description

Location_ID (pk and fk)

4 digit location id number

Training_Date (pk)

Date of training

Training_Location

Location of training (Onsite/Offsite)

lti (Lost Time Incidents)

Field Description

Location_ID (pk and fk)

4 digit location id number

Incident_ID (pk)

4 digit incident id number

Department

2 digit department number

EmployeeNumber

5 digit employee number

InjuryCode

Description of injury type

DaysLost

# of days lost due to injury

nmi (Near Miss Incidents)

Field Description

Location_ID (pk and fk)

4 digit location id number

Incident_ID (pk)

4 digit incident id number

Dept

2 digits department number

IncidentCode

Description of incident type (root cause, etc.)

EmployeeNumber

5 digit employee number

1. (10 points) What is the total headcount for division “FD”? (Hint: use locations table). The query needs to produce the total headcount

SELECT sum(Headcount) AS “Total_Headcount”
FROM locations
WHERE division = “FD”;

2. (10 points) Show the most recent audit date for location 2408. USE STR_TO_DATE function (as I show in Panopto video 3) to work with dates

SELECT Location_ID, Audit_Date,
STR_TO_DATE (audits.Audit_Date,”%m/%d/%y”)
FROM audits
ORDER BY location_ID;

3. (10 points) How many audits did the auditor make whose name includes “arr” as part of the name? The query needs to produce the total count of audits

4. (10 points) What is the total number of audits for each auditor?

5. (10 points) How many audits did NOT have any corrective actions?

6. (10 points) Use only NMI table. Count NMI incidents by location id and modify query to show only results for locations where the number of incidents is more than 100.

7. (10 points) Use only LTI table. Count LTI incidents by location id and modify query to show only results for locations where location_id starts with the number “2”. You may use WHERE or HAVING for that.

8. (10 points) USE Subquery. Use only ‘locations’ table. Show headcount by location id and modify query to show only results for locations where headcount is more than the average headcount for all locations. You may use WHERE or HAVING to show that report. Think about if you need to use GROUP BY or not.

Some results are shown below

9. (10 points) Create a report showing the number of LTI (Lost Time Incidents) by location as well as LTI Percent (LTI / Location Headcount). Only show results where LTI Percent is more than 30% (i.e., more than 0.3). To be able to confirm the answer by looking at the query results, you need to group by Location_ID, Division, Headcount and use the same attributes in SELECT part.

Some results are shown below

10. (10 points) Create the same report for NMI and NMI% (NMI / Location Headcount) by location. But for that report, only show results for the division “KJ” (you may use WHERE or HAVING to filter the results).

Some results are shown below

Looking for this or a Similar Assignment? Click below to Place your Order