HW2_Srinivas_vardhan_cis467_to_post_FAllA_2023

.docx

School

University of Rochester *

*We aren’t endorsed by this school

Course

467

Subject

Electrical Engineering

Date

Jan 9, 2024

Type

docx

Pages

6

Uploaded by MasterIbis312

Homework 2. Due by Saturday September 30 at 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. 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. If you use Chat GPT, please use the “Share” button (looks like ‘upward arrow’) in the right corner of ChatGPT chat, and ‘copy link’ and share the link to that chat in this Word document and briefly explain how you used it for your Homework (for each HW question if you used it). No points off will be taken for using ChatGPT (it is allowed to use it for Homework) but you are required to share the link to a chat if you used it. More information on how to share a chat here: https://help.openai.com/en/articles/7925741- chatgpt-shared-links-faq 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 total) . What is the total headcount for division "FD"? (hint: use locations table). The query needs to produce the total headcount. Answer select sum(headcount) from locations where Division="FD"; 2. (10 points total). 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 Answer select max(STR_TO_DATE(Audit_Date,"%m/%d/%Y")) from audits;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help