Chicago Crime and Socioeconomic Analysis using SQL

The Data

City of Chicago Crimes

Chicago Socioecomic Indicators

EXPLORING CHICAGO CRIME DATASET

• DISPLAY THE FIRST 10 RECORDS

SELECT TOP 10 * FROM [dbo].[Crimes];

• DISPLAY ROBBERY THAT OCCURED IN AN APARTMENT

SELECT [Primary Type], [Description], [Location Description]
FROM [dbo].[Crimes]
WHERE [Primary Type] = 'ROBBERY'
AND [Location Description] = 'APARTMENT';

• DISPLAY UNIQUE CRIME IN THE PRIMARY TYPE COLUMN

SELECT DISTINCT [Primary Type] FROM [dbo].[Crimes];

• HOW MANY BURGLARIES WERE REPORTED IN 2018?

SELECT COUNT(*) AS Total_Burglaries
FROM [dbo].[Crimes]
WHERE [Primary Type] = 'BURGLARY';

• HOW MANY HOMICIDES WERE REPORTED IN 2018?

SELECT COUNT(*) AS Total_Homicides
FROM [dbo].[Crimes]
WHERE [Primary Type] = 'HOMICIDE';

• WHAT TYPES OF CRIMES ARE COMMITED MOST FREQUENTLY?

SELECT [Primary Type], COUNT(*) AS Total_Crimes
FROM [dbo].[Crimes]
GROUP BY [Primary Type]
ORDER BY Total_Crimes DESC;

• WHERE ARE THE FREQUENT CRIMES BEING COMMITTED?

SELECT [Location Description], COUNT(*) AS Number_of_Crimes
FROM [dbo].[Crimes]
GROUP BY [Location Description]
ORDER BY Number_of_Crimes DESC;

• HOW MANY CRIMES INVOLVE AN ARREST?

SELECT COUNT(ARREST) AS Arrested
FROM [dbo].[Crimes]
WHERE Arrest = 'TRUE';

• HOW MANY CRIMES INVOLVE WITH NO ARREST?

SELECT COUNT(ARREST) AS Not_Arrested
FROM [dbo].[Crimes]
WHERE Arrest = 'FALSE';

• WHICH UNIQUE TYPES OF CRIMES HAVE BEEN RECORDED AT GAS STATION LOCATIONS?

SELECT DISTINCT [Primary Type]
FROM [dbo].[Crimes]
WHERE [Location Description] = 'GAS STATION';

• DISPLAY ALL TYPES OF CRIMES THAT START WITH LETTER 'A'

SELECT DISTINCT [Primary Type]
FROM [dbo].[Crimes]
WHERE [Primary Type] LIKE 'A%';

EXPLORING CHICAGO SOCIOECONOMIC DATASET

• DISPLAY COMMUNITY AREAS IN CHICAGO WITH A HARDSHIP INDEX GREATER THAN 50.0

SELECT [COMMUNITY AREA NAME]
FROM [dbo].[Socioeconomic_indicators]
WHERE [HARDSHIP INDEX] > 50.0
GROUP BY [COMMUNITY AREA NAME];

• WHICH COMMUNITY AREA HAS 'PARK' IN ITS NAME?

SELECT [COMMUNITY AREA NAME]
FROM [dbo].[Socioeconomic_indicators]
WHERE [COMMUNITY AREA NAME] LIKE '%Park%';

• WHICH COMMUNITY AREA HAS THE HIGHEST PERCENT HOUSEHOLDS BELOW POVERTY?

SELECT DISTINCT [COMMUNITY AREA NAME], [PERCENT HOUSEHOLDS BELOW POVERTY]
FROM [dbo].[Crimes] CR
LEFT JOIN [dbo].[Socioeconomic_indicators] SI
ON SI.[Community Area Number] = CR.[Community Area Number]
ORDER BY [PERCENT HOUSEHOLDS BELOW POVERTY] DESC;

• WHICH COMMUNITY AREA HAS THE MOST CRIMES?

SELECT [COMMUNITY AREA NAME], COUNT([Primary Type]) AS Number_of_Crimes
FROM [dbo].[Crimes] CR
LEFT JOIN [dbo].[Socioeconomic_indicators] SI
ON SI.[Community Area Number] = CR.[Community Area Number]
GROUP BY [COMMUNITY AREA NAME]
ORDER BY Number_of_Crimes DESC;