SQL script to import traffic ticket data into MySQL database.

violations.sql

DROP DATABASE IF EXISTS violations;
CREATE DATABASE violations;
USE violations;

DROP TABLE IF EXISTS _2011;
CREATE table _2011 (
	Violation_Charged_Code VARCHAR(30),
	Violation_Description VARCHAR(80),
	Violation_Month VARCHAR(5),
	Violation_Time VARCHAR(12),
	Age_at_Violation VARCHAR(3),
	State_of_License VARCHAR(50),
	Gender CHAR(2),
	Police_Agency VARCHAR(80),
	Court VARCHAR(80),
	Source VARCHAR(30),
	INDEX (Gender, Age_at_Violation, Police_Agency)
);

DROP TABLE IF EXISTS _2012;
CREATE table _2012 (
	Violation_Charged_Code VARCHAR(30),
	Violation_Description VARCHAR(80),
	Violation_Month VARCHAR(5),
	Violation_Time VARCHAR(12),
	Age_at_Violation VARCHAR(3),
	State_of_License VARCHAR(50),
	Gender CHAR(2),
	Police_Agency VARCHAR(80),
	Court VARCHAR(80),
	Source VARCHAR(30),
	INDEX (Gender, Age_at_Violation, Police_Agency)
);

LOAD DATA INFILE '/tmp/violations2011.csv' INTO TABLE _2011
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(Violation_Charged_Code,Violation_Description,Violation_Month,Violation_Time,Age_at_Violation,State_of_License,Gender,Police_Agency,Court,Source);

LOAD DATA INFILE '/tmp/violations2012.csv' INTO TABLE _2012
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(Violation_Charged_Code,Violation_Description,Violation_Month,Violation_Time,Age_at_Violation,State_of_License,Gender,Police_Agency,Court,Source);