Create a New Database
Cleaned Census Data
Ark Pop Money Counties 2014 ACS_14_5YR_DP03 (1)
FBI Data
ARK 2010-2014 Crime Data-Tableau-March4
HOMEWORK
Data CleaningÂ
Clean Up Census Labels for SQL Import
Counties
Labor_force
Employed
Unemployed
Median_household_inc
Avg_household_inc
–Save As, Census, store this as a standalone .csv file
Clean up FBI Labels for SQL Import
Place
Place2
Full_Place_Name
State
Months
Population
Violent_crime_total
Murder
Revised_rape
Robbery
Aggr_assault
Property_Crime_Total
Burglary
Larceny-theft
Motor_vehicle_theft
Violent_Crime_Rate
Murder_Rate
Property_Crime_Rate
–Save As, Crime, store this as a standalone .csv file
Create A New Database in SQL
Go to SQLite. Create a new database
Import the Crime.csv. Assign text as Text and Numbers as Real
Import the Census.csv. Assign text as Text and Numbers as Real
Check to see you imported the same number of rows as in the spreadsheets
Inner Join Crime and Census.
Remember from the Florida data:
SELECT * FROM FL_nativity INNER JOIN FL_poverty ON FL_nativity.Id2 = FL_poverty.Id2;
https://www.w3schools.com/sql/sql_join_inner.asp
Export this table as a .csv file.
Fill in the headers.
By 11:59 pm Sunday, April 16, post on a blog the .csv file that combined both of the tables along with the SQL code to make that happen.
–Bring your work to class Tuesday, and we will head to the next phase