Homework 2. Due by Friday, Nov. 19 11:59 PM. Please copy all your queries (sql code) from MySQ

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

#DROP DATABASE IF EXISTS safety;
CREATE DATABASE safety;

USE safety;

— locations
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`Location_ID` int(11) NOT NULL,
`Headcount` int(11) NOT NULL,
`Division` text NOT NULL,
`State` text NOT NULL,
`City` text NOT NULL,
`Employee_Safety_Committee` text NOT NULL,
PRIMARY KEY (`Location_ID`)
);

INSERT INTO `locations` VALUES (2408,554,’KJ’,’OH’,’Toledo’,’yes’),(2415,181,’PK’,’OH’,’Toledo’,’no’),(2417,327,’PK’,’PA’,’Harrisburg’,’yes’),(2440,273,’FD’,’OH’,’Toledo’,’yes’),(2453,228,’CF’,’PA’,’Erie’,’yes’),(2464,183,’SM’,’PA’,’Erie’,’yes’),(2468,365,’PK’,’PA’,’Erie’,’yes’),(2493,430,’CF’,’OH’,’Delware’,’yes’),(2505,159,’FD’,’OH’,’Delware’,’yes’),(2528,224,’AE’,’NY’,’Albany’,’yes’),(2534,422,’KJ’,’NY’,’Syracuse’,’yes’),(2551,196,’FD’,’PA’,’Harrisburg’,’yes’),(2554,316,’AE’,’OH’,’Cleveland’,’yes’),(2572,437,’CF’,’PA’,’Scranton’,’yes’),(2594,319,’KJ’,’PA’,’Scranton’,’yes’),(2612,580,’SM’,’NY’,’Albany’,’yes’),(2616,373,’AE’,’PA’,’Scranton’,’no’),(2623,292,’FD’,’PA’,’Harrisburg’,’yes’),(2639,141,’KJ’,’NY’,’Albany’,’no’),(2643,126,’SM’,’PA’,’Erie’,’yes’),(2656,340,’AE’,’NY’,’Rome’,’yes’),(2664,141,’KJ’,’OH’,’Delware’,’yes’),(2671,391,’FD’,’NY’,’Rome’,’yes’),(2689,148,’AE’,’NY’,’Albany’,’yes’),(2707,233,’KJ’,’PA’,’Harrisburg’,’yes’),(2709,136,’KJ’,’PA’,’Scranton’,’yes’),(2728,515,’CF’,’OH’,’Toledo’,’yes’),(2751,574,’CF’,’PA’,’Erie’,’no’),(2759,606,’SM’,’OH’,’Cleveland’,’yes’),(2761,208,’FD’,’NY’,’Syracuse’,’yes’),(2786,212,’CF’,’OH’,’Delware’,’yes’),(2803,378,’CF’,’PA’,’Harrisburg’,’yes’),(2825,452,’SM’,’PA’,’Scranton’,’yes’),(2840,224,’KJ’,’OH’,’Cleveland’,’yes’),(2859,370,’CF’,’OH’,’Cleveland’,’yes’),(2883,226,’FD’,’NY’,’Syracuse’,’no’),(2898,301,’CF’,’OH’,’Delware’,’yes’),(2910,549,’KJ’,’NY’,’Albany’,’no’),(2915,392,’CF’,’OH’,’Cleveland’,’yes’),(2922,567,’SM’,’OH’,’Cleveland’,’yes’),(2938,504,’CF’,’OH’,’Cleveland’,’yes’),(2958,364,’SM’,’PA’,’Erie’,’yes’),(2971,167,’KJ’,’OH’,’Toledo’,’yes’),(2990,306,’AE’,’NY’,’Syracuse’,’yes’),(3014,224,’KJ’,’PA’,’Erie’,’yes’),(3025,113,’PK’,’PA’,’Scranton’,’no’),(3038,111,’PK’,’NY’,’Syracuse’,’yes’),(3043,414,’SM’,’OH’,’Delware’,’yes’),(3064,315,’AE’,’OH’,’Toledo’,’no’),(3078,554,’CF’,’NY’,’Albany’,’yes’),(3081,323,’AE’,’OH’,’Toledo’,’yes’),(3095,272,’PK’,’OH’,’Toledo’,’yes’),(3097,175,’KJ’,’NY’,’Rome’,’yes’),(3115,390,’KJ’,’OH’,’Cleveland’,’no’),(3137,604,’AE’,’PA’,’Harrisburg’,’yes’),(3156,301,’AE’,’PA’,’Scranton’,’yes’),(3177,431,’KJ’,’NY’,’Syracuse’,’yes’),(3202,191,’PK’,’OH’,’Toledo’,’yes’),(3218,343,’CF’,’PA’,’Scranton’,’yes’),(3243,401,’PK’,’OH’,’Cleveland’,’yes’),(3250,477,’KJ’,’NY’,’Rome’,’no’),(3265,399,’KJ’,’OH’,’Toledo’,’yes’),(3271,120,’AE’,’NY’,’Albany’,’yes’),(3273,409,’KJ’,’NY’,’Syracuse’,’yes’),(3290,338,’SM’,’NY’,’Rome’,’no’),(3294,503,’CF’,’PA’,’Scranton’,’no’),(3305,401,’SM’,’NY’,’Syracuse’,’yes’),(3321,437,’KJ’,’NY’,’Rome’,’yes’),(3340,163,’SM’,’OH’,’Cleveland’,’yes’),(3354,319,’CF’,’PA’,’Scranton’,’yes’),(3366,466,’FD’,’OH’,’Cleveland’,’yes’),(3376,267,’AE’,’NY’,’Rome’,’no’),(3394,546,’KJ’,’PA’,’Harrisburg’,’yes’),(3398,557,’FD’,’OH’,’Toledo’,’no’),(3417,323,’FD’,’PA’,’Erie’,’yes’),(3423,487,’AE’,’OH’,’Delware’,’yes’),(3435,259,’FD’,’NY’,’Rome’,’yes’),(3451,533,’AE’,’NY’,’Albany’,’no’),(3454,393,’FD’,’OH’,’Toledo’,’yes’),(3477,164,’FD’,’NY’,’Syracuse’,’no’),(3480,293,’CF’,’OH’,’Toledo’,’no’),(3486,247,’AE’,’OH’,’Toledo’,’yes’),(3493,558,’AE’,’NY’,’Albany’,’yes’),(3511,401,’AE’,’OH’,’Toledo’,’yes’),(3515,344,’FD’,’OH’,’Cleveland’,’no’),(3534,229,’PK’,’OH’,’Cleveland’,’yes’),(3555,140,’CF’,’NY’,’Rome’,’yes’),(3576,193,’CF’,’PA’,’Erie’,’no’),(3594,339,’SM’,’OH’,’Cleveland’,’yes’),(3611,337,’FD’,’NY’,’Syracuse’,’yes’),(3627,229,’SM’,’NY’,’Rome’,’yes’),(3652,345,’KJ’,’OH’,’Cleveland’,’no’),(3675,350,’KJ’,’NY’,’Rome’,’yes’),(3682,392,’KJ’,’OH’,’Cleveland’,’yes’),(3691,241,’KJ’,’OH’,’Toledo’,’yes’),(3698,445,’FD’,’OH’,’Toledo’,’yes’),(3710,477,’CF’,’OH’,’Toledo’,’yes’),(3734,568,’KJ’,’NY’,’Albany’,’yes’),(3751,455,’AE’,’OH’,’Delware’,’yes’),(3766,538,’CF’,’PA’,’Scranton’,’yes’),(3776,595,’AE’,’NY’,’Syracuse’,’yes’),(3791,221,’KJ’,’PA’,’Scranton’,’yes’),(3802,528,’SM’,’PA’,’Erie’,’no’),(3806,486,’CF’,’OH’,’Cleveland’,’yes’),(3818,595,’SM’,’OH’,’Cleveland’,’yes’),(3836,406,’KJ’,’PA’,’Harrisburg’,’yes’),(3845,367,’KJ’,’PA’,’Scranton’,’yes’),(3864,367,’PK’,’NY’,’Syracuse’,’yes’),(3873,515,’SM’,’PA’,’Scranton’,’yes’),(3889,499,’KJ’,’PA’,’Scranton’,’yes’),(3908,563,’KJ’,’NY’,’Albany’,’yes’),(3929,248,’CF’,’PA’,’Harrisburg’,’yes’),(3933,222,’AE’,’NY’,’Syracuse’,’yes’),(3954,512,’PK’,’PA’,’Scranton’,’yes’),(3973,343,’PK’,’OH’,’Cleveland’,’no’),(3980,413,’FD’,’PA’,’Scranton’,’yes’),(3994,461,’PK’,’OH’,’Cleveland’,’yes’),(3997,521,’PK’,’NY’,’Albany’,’yes’),(4005,447,’PK’,’OH’,’Toledo’,’yes’),(4023,469,’CF’,’OH’,’Cleveland’,’yes’),(4025,476,’PK’,’OH’,’Toledo’,’yes’),(4046,460,’AE’,’PA’,’Scranton’,’yes’),(4057,245,’AE’,’NY’,’Albany’,’yes’),(4076,282,’FD’,’OH’,’Delware’,’yes’),(4083,604,’CF’,’NY’,’Syracuse’,’yes’),(4096,501,’CF’,’OH’,’Toledo’,’yes’),(4118,258,’PK’,’NY’,’Syracuse’,’no’),(4126,370,’FD’,’NY’,’Syracuse’,’yes’),(4130,573,’PK’,’OH’,’Delware’,’yes’),(4134,339,’KJ’,’PA’,’Erie’,’no’),(4159,461,’KJ’,’PA’,’Harrisburg’,’no’),(4171,362,’AE’,’PA’,’Erie’,’yes’),(4176,528,’AE’,’NY’,’Albany’,’no’),(4181,263,’CF’,’OH’,’Toledo’,’no’),(4193,590,’SM’,’OH’,’Delware’,’yes’),(4218,482,’SM’,’OH’,’Cleveland’,’yes’),(4220,420,’FD’,’OH’,’Toledo’,’yes’),(4239,390,’CF’,’OH’,’Cleveland’,’yes’),(4252,548,’KJ’,’OH’,’Toledo’,’yes’),(4260,288,’CF’,’OH’,’Toledo’,’yes’),(4264,254,’SM’,’OH’,’Cleveland’,’yes’),(4278,261,’AE’,’NY’,’Rome’,’yes’),(4283,192,’CF’,’OH’,’Delware’,’no’),(4298,409,’AE’,’NY’,’Rome’,’yes’),(4309,112,’AE’,’OH’,’Toledo’,’yes’),(4320,424,’FD’,’OH’,’Toledo’,’no’),(4345,564,’SM’,’NY’,’Albany’,’yes’),(4368,532,’AE’,’NY’,’Syracuse’,’no’),(4391,377,’PK’,’OH’,’Cleveland’,’yes’),(4407,486,’SM’,’OH’,’Toledo’,’no’),(4411,116,’AE’,’NY’,’Syracuse’,’yes’),(4421,455,’KJ’,’OH’,’Toledo’,’yes’),(4445,492,’CF’,’OH’,’Delware’,’yes’),(4448,586,’AE’,’NY’,’Syracuse’,’no’),(4463,509,’SM’,’OH’,’Delware’,’yes’),(4482,265,’AE’,’NY’,’Rome’,’no’),(4497,546,’CF’,’NY’,’Albany’,’yes’),(4516,596,’PK’,’PA’,’Harrisburg’,’yes’),(4534,540,’SM’,’NY’,’Syracuse’,’yes’),(4556,118,’AE’,’OH’,’Delware’,’yes’),(4575,269,’AE’,’NY’,’Albany’,’yes’),(4584,179,’FD’,’NY’,’Syracuse’,’yes’),(4588,287,’KJ’,’NY’,’Albany’,’yes’),(4598,265,’FD’,’NY’,’Syracuse’,’yes’),(4612,220,’FD’,’OH’,’Delware’,’yes’),(4622,414,’FD’,’OH’,’Cleveland’,’yes’),(4636,584,’FD’,’NY’,’Syracuse’,’yes’),(4659,278,’SM’,’PA’,’Harrisburg’,’yes’),(4670,454,’SM’,’NY’,’Rome’,’yes’),(4672,145,’CF’,’OH’,’Delware’,’yes’),(4676,301,’PK’,’PA’,’Erie’,’yes’),(4691,518,’PK’,’PA’,’Erie’,’yes’),(4710,114,’PK’,’PA’,’Harrisburg’,’no’),(4734,324,’KJ’,’PA’,’Erie’,’yes’),(4739,441,’KJ’,’PA’,’Erie’,’no’),(4756,272,’AE’,’PA’,’Harrisburg’,’no’),(4767,548,’AE’,’PA’,’Erie’,’yes’),(4781,548,’SM’,’OH’,’Cleveland’,’yes’),(4800,384,’PK’,’NY’,’Albany’,’yes’);

— audits table
DROP TABLE IF EXISTS `audits`;
CREATE TABLE `audits` (
`Location_ID` int(11) NOT NULL,
`Audit_Date` varchar(10) NOT NULL,
`Auditor` text NOT NULL,
`Audit_Findings` text NOT NULL,
`Corrective_Action` varchar(5) DEFAULT NULL,
PRIMARY KEY (`Location_ID`,`Audit_Date`),
UNIQUE KEY `CorrectiveAction_UNIQUE` (`Corrective_Action`),
CONSTRAINT `audits_fk_locations` FOREIGN KEY (`Location_ID`) REFERENCES `locations` (`location_id`)
);

INSERT INTO `audits` VALUES …

Get Your Paper Before the Deadline. Our Services are 100% private and Confidential

Useful Links That Will Help You Around

Link to new order https://studentsolutionsusa.com/orders/stud/new

link to login page https://studentsolutionsusa.com/orders/login

New user registration link https://studentsolutionsusa.com/orders/register

Forgot Password https://studentsolutionsusa.com/orders/forgot/password

FREQUENTLY ASKED QUESTIONS

Question: How does this work?

Answer: Good Question. We are a group of a freelance board of students and professional essay writers. At our website, you may get help with any type of academic assignments: essay, coursework, term paper, business plan, case study, article review, research paper, presentation, and speech. Top writers can help with complex assignments such as dissertations, thesis papers, etc. All of them are professionals possessing excellent knowledge in their field of expertise, perfect writing skills, quality, and speed. When you place an order on our website, we assign it to the best writer. Once the writer finishes the work, the paper is submitted to our quality assurance desk who go through it and ensure it is unique and plagiarism free and that the instructions were followed to the detail. After this step we upload the paper in your account, we also send a copy to the email that you used to register the account with. we can guarantee you that the paper will be 100% plagiarism free. Besides, our services are 100% private and confidential

Question: How do I place an Order after getting to the order page

Answer: There are three major steps in the ordering process

Step 1 ....................................................paper details In this step, you will fill in the instructions of your paper; you can upload any materials that you feel will make your assignment a success. Besides, you can also email us at [email protected] Remember to specify the correct academic level. Please note that sources mean the number of references.

Step 2...................................................... Price calculation Kindly specify the number of pages, type of spacing and the correct deadline. This step will give you the estimated cost minus discount -- you may add the extra features if you wish.

Step 3 ....................................................discount and payment Use the discount code HAPPY2018 to enjoy up to 30% discount of your total cost After this step, proceed to safe payment; you can checkout using your card or PayPal Please note we will send the complete paper to the email you will provide while registering. A copy will also be uploaded to your account

Question: How will I know when my paper is complete? or How will I get the complete Paper?

Answer: Once we are done with the paper, we will be uploaded to your account. A copy will also be sent to the email you registered with. We can guarantee you the following:- 1. Our service is private and confidential; we don't spam or share your contacts with anyone 2. The final paper will be plagiarism free. We will send a Turnitin Report to the email you registered with 3. At our company, willing to do free unlimited revisions until you are satisfied with your paper

Question:- Am a new client, How can I get the guarantee that the paper will be completed and sent to me before my deadline?

Answer: Thank you for expressing your concerns. We would love to have you as our loyal customer. We are certain if we do good work, you will come back for me. Besides, you will give us referrals to your friends and family. For that reason, we can’t fail to deliver your paper within your specified time frame. We will ensure we submit the paper on time so that you can have enough time to go through it, if you have problems with the paper delivered, you can request a free revision. You are entitled to as many revisions as you would wish until you get a paper that satisfies you

Homework 2. Due by Friday, Nov. 19 11:59 PM. Please copy all your queries (sql code) from MySQ

Useful Links That Will Help You Around

FREQUENTLY ASKED QUESTIONS

Question: How does this work?

Question: How do I place an Order after getting to the order page

Question: How will I know when my paper is complete? or How will I get the complete Paper?

Question:- Am a new client, How can I get the guarantee that the paper will be completed and sent to me before my deadline?

Useful Links That Will Help You Around

Contact Us