US Crime

This data has been gathered from https://www.kaggle.com - it shows crimes rates alongside some socio/economic indicators for each state for the year 2005 to 2015.

uscrime

State	Year	Crime_Count_homicide	population
Alabama	2005	374	4548330
Alabama	2006	382	4599030
Alabama	2007	412	4627850
Alabama	2008	351	4661900
Alabama	2009	322	4708710
Alabama	2010	275	4785400
Alabama	2011	299	4803690
Alabama	2012	342	4817530
Alabama	2013	346	4834000
Alabama	2014	276	4846410
Alabama	2015	348	4853880
Alaska	2005	32	663253
Alaska	2006	36	670053
Alaska	2007	43	683478
...
Alaska	2015	59	737709
Arizona	2005	445	5953010
Arizona	2006	533	6166320
Arizona	2007	548	6338760
Arizona	2007	548	6338760
...

+------------------------------------+-------------+------+-----+---------+
| Field                              | Type        | Null | Key | Default |
+------------------------------------+-------------+------+-----+---------+
| State                              | varchar(20) | NO   | PRI | NULL    |
| Year                               | int(11)     | NO   | PRI | NULL    |
| Crime_Count_aggravated_assault     | float       | YES  |     | NULL    |
| Crime_Count_burglary               | float       | YES  |     | NULL    |
| Crime_Count_homicide               | float       | YES  |     | NULL    |
| Crime_Count_larceny                | float       | YES  |     | NULL    |
| Crime_Count_motor_vehicle_theft    | float       | YES  |     | NULL    |
| Crime_Count_robbery                | float       | YES  |     | NULL    |
| Crime_Rate_aggravated_assault      | float       | YES  |     | NULL    |
| Crime_Rate_burglary                | float       | YES  |     | NULL    |
| Crime_Rate_homicide                | float       | YES  |     | NULL    |
| Crime_Rate_larceny                 | float       | YES  |     | NULL    |
| Crime_Rate_motor_vehicle_theft     | float       | YES  |     | NULL    |
| Crime_Rate_robbery                 | float       | YES  |     | NULL    |
| State_Abbr                         | varchar(2)  | YES  |     | NULL    |
| Population                         | float       | YES  |     | NULL    |
| Violent_Crime                      | float       | YES  |     | NULL    |
| Property_Crime                     | float       | YES  |     | NULL    |
| Rape_Legacy                        | float       | YES  |     | NULL    |
| Rape_Revised                       | float       | YES  |     | NULL    |
| Location                           | varchar(10) | YES  |     | NULL    |
| Age                                | float       | YES  |     | NULL    |
| Average_household_size             | float       | YES  |     | NULL    |
| Black_white                        | float       | YES  |     | NULL    |
| GDP_per_capita                     | float       | YES  |     | NULL    |
| Gini_coefficient                   | float       | YES  |     | NULL    |
| Hispanic_white                     | float       | YES  |     | NULL    |
| Labor_force_participation          | float       | YES  |     | NULL    |
| Log_of_GDP_per_capita              | float       | YES  |     | NULL    |
| Corruption_convictions_per_1000000 | float       | YES  |     | NULL    |
| People_with_less                   | float       | YES  |     | NULL    |
| Poverty_rate                       | float       | YES  |     | NULL    |
| Unemployment_rate                  | float       | YES  |     | NULL    |
+------------------------------------+-------------+------+-----+---------+

Number of Homicides in Indiana

Use Crime_Count_homicide to show the number of homicides for each year in Indiana.

SELECT Year, Crime_Count_homicide FROM uscrime WHERE State='Iowa'

SELECT Year, Crime_Count_homicide FROM uscrime WHERE State='Indiana'

Notice that the figure for 2015 (370) is greater than that for 2005 (356), that shows an increase for the state of Indiana - but is that refected across the whole country?

Homicides across the whole US

Show the total number of homicides for each year, also show the total population and the homicide rate per million as a whole number.

SELECT Year, Crime_Count_homicide

 FROM uscrime
WHERE State='Iowa'

SELECT Year,

SUM(Crime_Count_homicide) h,SUM(population) p,
ROUND(1000000*SUM(Crime_Count_homicide)/SUM(population)) hr

FROM uscrime GROUP BY Year

Notice the small decrease in homicides between 2005 and 2015 despite the small increase in the population over that period.

Homicides across the whole US

Show the total number of homicides for each year, also show the total population.

Notice the small decrease in homicides between 2005 and 2015 despite the small increase in the population over that period.

SELECT Year, Crime_Count_homicide FROM uscrime WHERE State='Iowa'

SELECT Year,

      SUM(Crime_Count_Homicide) hom,
      SUM(population) pop

FROM uscrime GROUP BY Year

Rows to columns

The default query shows the number of homicides for 2005 and 2015 and the population for 2005 for each state.

Change it to add the 2015 population as the fifth column.

SELECT State,

 SUM(CASE WHEN Year=2005 THEN Crime_Count_Homicide END) h2005,
 SUM(CASE WHEN Year=2015 THEN Crime_Count_Homicide END) h2015,
 SUM(CASE WHEN Year=2005 THEN population END) p2005
FROM uscrime

WHERE Year IN (2005,2015) GROUP BY State

SELECT State,

 SUM(CASE WHEN Year=2005 THEN Crime_Count_Homicide END) h2005,
 SUM(CASE WHEN Year=2015 THEN Crime_Count_Homicide END) h2015,
 SUM(CASE WHEN Year=2005 THEN population END) p2005,
 SUM(CASE WHEN Year=2015 THEN population END) p2015
FROM uscrime

WHERE Year IN (2005,2015) GROUP BY State

Which States saw an increase in Homicide Rates

Identify the states that saw an increase in the homicide rate between 2005 and 2015.

SELECT Year, Crime_Count_homicide FROM uscrime WHERE State='Iowa'

SELECT State FROM ( SELECT State, Crime_Rate_Homicide h2005,0 h2015 FROM uscrime WHERE Year=2005 UNION SELECT State, 0, Crime_Rate_Homicide FROM uscrime WHERE Year=2015) as c GROUP BY State HAVING SUM(h2015)>SUM(h2005)