The queries for my Advanced SQL class at NICAR 2015, and a link to download the data.
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
Exploring_OSHA.sql
README.md
accident_injury_layout.csv
accident_layout.csv
inspection_layout.csv

README.md

Data and Materials for Advanced SQL in MySQL at NICAR15

###All the SQL for the class is in this file: Exploring_OSHA.sql

###Download the data https://www.dropbox.com/s/fw09f9kxi0ldir3/adv_sql_data.zip?dl=0 (Note that this download gives you the full files, whereas the class notes work off of a slice from 2000 forward, using the field open_date.

inspection, accident, accident_injury were downloaded from the DOL's OSHA download site on 02/23/2015.

###Record layouts

###What I cover:

  • Truthing data tables and joins
  • DISTINCT
  • CREATE TABLE and LOAD DATA INFILE syntax
  • ALTER TABLE to add columns
  • UPDATE to populate new columns
  • STR_TO_DATE to convert text dates to actual dates
  • DATEDIFF to calculate difference in days between two dates
  • wildcards for more complex filtering
  • aliases in table names
  • outer joins
  • multiple joins in one query
  • subqueries