Cities are complex systems where crime, education, and socioeconomic conditions intersect.
This project investigates those intersections using real-world datasets from the City of Chicago, structured into a relational database and queried using SQL.
The goal was not visualization or prediction, but building a clean analytical pipeline:
raw CSV files → relational tables → SQL-driven answers to real civic questions.
Urban decision-makers need to answer questions such as:
- Which communities experience the highest crime burden?
- How does poverty relate to crime concentration?
- What kinds of crimes occur around schools?
- Which communities face the highest socioeconomic hardship?
Answering these requires integrated data, not isolated spreadsheets.
Official Chicago open datasets (subset versions prepared for SQL analysis):
- Chicago Census Data
Socioeconomic indicators and hardship index by community area - Chicago Public Schools Data
School-level performance, safety, and attendance metrics - Chicago Crime Data
Reported crime incidents by type, location, year, and community area
- Python (Pandas, sqlite3)
- SQLite
- SQL
- Jupyter Notebook
- ipython-sql
Dependencies are listed in requirements.txt.
1️⃣ CENSUS_DATA
| COMMUNITY_AREA_NUMBER | COMMUNITY_AREA_NAME | PERCENT_OF_HOUSING_CROWDED | PERCENT_HOUSEHOLDS_BELOW_POVERTY | PERCENT_AGED_16__UNEMPLOYED | PERCENT_AGED_25__WITHOUT_HIGH_SCHOOL_DIPLOMA | PERCENT_AGED_UNDER_18_OR_OVER_64 | PER_CAPITA_INCOME | HARDSHIP_INDEX |
|---|---|---|---|---|---|---|---|---|
| 1.0 | Rogers Park | 7.7 | 23.6 | 8.7 | 18.2 | 27.5 | 23939 | 39.0 |
| 2.0 | West Ridge | 7.8 | 17.2 | 8.8 | 20.8 | 38.5 | 23040 | 46.0 |
| 3.0 | Uptown | 3.8 | 24.0 | 8.9 | 11.8 | 22.2 | 35787 | 20.0 |
| 4.0 | Lincoln Square | 3.4 | 10.9 | 8.2 | 13.4 | 25.5 | 37524 | 17.0 |
| 5.0 | North Center | 0.3 | 7.5 | 5.2 | 4.5 | 26.2 | 57123 | 6.0 |
| School_ID | NAME_OF_SCHOOL | Elementary, Middle, or High School | Street_Address | City | State | ZIP_Code | Phone_Number | Link | Network_Manager | Collaborative_Name | Adequate_Yearly_Progress_Made_ | Track_Schedule | CPS_Performance_Policy_Status | CPS_Performance_Policy_Level | HEALTHY_SCHOOL_CERTIFIED | Safety_Icon | SAFETY_SCORE | Family_Involvement_Icon | Family_Involvement_Score | Environment_Icon | Environment_Score | Instruction_Icon | Instruction_Score | Leaders_Icon | Leaders_Score | Teachers_Icon | Teachers_Score | Parent_Engagement_Icon | Parent_Engagement_Score | Parent_Environment_Icon | Parent_Environment_Score | AVERAGE_STUDENT_ATTENDANCE | Rate_of_Misconducts__per_100_students_ | Average_Teacher_Attendance | Individualized_Education_Program_Compliance_Rate | Pk_2_Literacy__ | Pk_2_Math__ | Gr3_5_Grade_Level_Math__ | Gr3_5_Grade_Level_Read__ | Gr3_5_Keep_Pace_Read__ | Gr3_5_Keep_Pace_Math__ | Gr6_8_Grade_Level_Math__ | Gr6_8_Grade_Level_Read__ | Gr6_8_Keep_Pace_Math_ | Gr6_8_Keep_Pace_Read__ | Gr_8_Explore_Math__ | Gr_8_Explore_Read__ | ISAT_Exceeding_Math__ | ISAT_Exceeding_Reading__ | ISAT_Value_Add_Math | ISAT_Value_Add_Read | ISAT_Value_Add_Color_Math | ISAT_Value_Add_Color_Read | Students_Taking__Algebra__ | Students_Passing__Algebra__ | 9th Grade EXPLORE (2009) | 9th Grade EXPLORE (2010) | 10th Grade PLAN (2009) | 10th Grade PLAN (2010) | Net_Change_EXPLORE_and_PLAN | 11th Grade Average ACT (2011) | Net_Change_PLAN_and_ACT | College_Eligibility__ | Graduation_Rate__ | College_Enrollment_Rate__ | COLLEGE_ENROLLMENT | General_Services_Route | Freshman_on_Track_Rate__ | X_COORDINATE | Y_COORDINATE | Latitude | Longitude | COMMUNITY_AREA_NUMBER | COMMUNITY_AREA_NAME | Ward | Police_District | Location |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 610038 | Abraham Lincoln Elementary School | ES | 615 W Kemper Pl | Chicago | IL | 60614 | (773) 534-5720 | http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610038.pdf | Fullerton Elementary Network | NORTH-NORTHWEST SIDE COLLABORATIVE | No | Standard | Not on Probation | Level 1 | Yes | Very Strong | 99.0 | Very Strong | 99 | Strong | 74.0 | Strong | 66.0 | Weak | 65 | Strong | 70 | Strong | 56 | Average | 47 | 96.00% | 2.0 | 96.40% | 95.80% | 80.1 | 43.3 | 89.6 | 84.9 | 60.7 | 62.6 | 81.9 | 85.2 | 52 | 62.4 | 66.3 | 77.9 | 69.7 | 64.4 | 0.2 | 0.9 | Yellow | Green | 67.1 | 54.5 | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | 813 | 33 | NDA | 1171699.458 | 1915829.428 | 41.92449696 | -87.64452163 | 7 | LINCOLN PARK | 43 | 18 | (41.92449696, -87.64452163) |
| 610281 | Adam Clayton Powell Paideia Community Academy Elementary School | ES | 7511 S South Shore Dr | Chicago | IL | 60649 | (773) 535-6650 | http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610281.pdf | Skyway Elementary Network | SOUTH SIDE COLLABORATIVE | No | Track_E | Not on Probation | Level 1 | No | Average | 54.0 | Strong | 66 | Strong | 74.0 | Very Strong | 84.0 | Weak | 63 | Strong | 76 | Weak | 46 | Average | 50 | 95.60% | 15.7 | 95.30% | 100.00% | 62.4 | 51.7 | 21.9 | 15.1 | 29 | 42.8 | 38.5 | 27.4 | 44.8 | 42.7 | 14.1 | 34.4 | 16.8 | 16.5 | 0.7 | 1.4 | Green | Green | 17.2 | 27.3 | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | 521 | 46 | NDA | 1196129.985 | 1856209.466 | 41.76032435 | -87.55673627 | 43 | SOUTH SHORE | 7 | 4 | (41.76032435, -87.55673627) |
| 610185 | Adlai E Stevenson Elementary School | ES | 8010 S Kostner Ave | Chicago | IL | 60652 | (773) 535-2280 | http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610185.pdf | Midway Elementary Network | SOUTHWEST SIDE COLLABORATIVE | No | Standard | Not on Probation | Level 2 | No | Strong | 61.0 | NDA | NDA | Average | 50.0 | Weak | 36.0 | Weak | NDA | NDA | NDA | Average | 47 | Weak | 41 | 95.70% | 2.3 | 94.70% | 98.30% | 53.7 | 26.6 | 38.3 | 34.7 | 43.7 | 57.3 | 48.8 | 39.2 | 46.8 | 44 | 7.5 | 21.9 | 18.3 | 15.5 | -0.9 | -1.0 | Red | Red | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | 1324 | 44 | NDA | 1148427.165 | 1851012.215 | 41.74711093 | -87.73170248 | 70 | ASHBURN | 13 | 8 | (41.74711093, -87.73170248) |
| 609993 | Agustin Lara Elementary Academy | ES | 4619 S Wolcott Ave | Chicago | IL | 60609 | (773) 535-4389 | http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_609993.pdf | Pershing Elementary Network | SOUTHWEST SIDE COLLABORATIVE | No | Track_E | Not on Probation | Level 1 | No | Average | 56.0 | Average | 44 | Average | 45.0 | Weak | 37.0 | Weak | 65 | Average | 48 | Average | 53 | Strong | 58 | 95.50% | 10.4 | 95.80% | 100.00% | 76.9 | NDA | 26 | 24.7 | 61.8 | 49.7 | 39.2 | 27.2 | 69.7 | 60.6 | 9.1 | 18.2 | 11.1 | 9.6 | 0.9 | 2.4 | Green | Green | 42.9 | 25 | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | 556 | 42 | NDA | 1164504.29 | 1873959.199 | 41.8097569 | -87.6721446 | 61 | NEW CITY | 20 | 9 | (41.8097569, -87.6721446) |
| 610513 | Air Force Academy High School | HS | 3630 S Wells St | Chicago | IL | 60609 | (773) 535-1590 | http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610513.pdf | Southwest Side High School Network | SOUTHWEST SIDE COLLABORATIVE | NDA | Standard | Not on Probation | Not Enough Data | Yes | Average | 49.0 | Strong | 60 | Strong | 60.0 | Average | 55.0 | Weak | 45 | Average | 54 | Average | 53 | Average | 49 | 93.30% | 15.6 | 96.90% | 100.00% | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | NDA | None | None | None | None | NDA | NDA | NDA | NDA | 14.6 | 14.8 | NDA | 16 | 1.4 | NDA | NDA | NDA | NDA | NDA | 302 | 40 | 91.8 | 1175177.622 | 1880745.126 | 41.82814609 | -87.63279369 | 34 | ARMOUR SQUARE | 11 | 9 | (41.82814609, -87.63279369) |
| ID | CASE_NUMBER | DATE | BLOCK | IUCR | PRIMARY_TYPE | DESCRIPTION | LOCATION_DESCRIPTION | ARREST | DOMESTIC | BEAT | DISTRICT | WARD | COMMUNITY_AREA_NUMBER | FBICODE | X_COORDINATE | Y_COORDINATE | YEAR | LATITUDE | LONGITUDE | LOCATION |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 3512276 | HK587712 | 2004-08-28 | 047XX S KEDZIE AVE | 890 | THEFT | FROM BUILDING | SMALL RETAIL STORE | 0 | 0 | 911 | 9 | 14.0 | 58.0 | 6 | 1155838.0 | 1873050.0 | 2004 | 41.8074405 | -87.70395585 | (41.8074405, -87.703955849) |
| 3406613 | HK456306 | 2004-06-26 | 009XX N CENTRAL PARK AVE | 820 | THEFT | $500 AND UNDER | OTHER | 0 | 0 | 1112 | 11 | 27.0 | 23.0 | 6 | 1152206.0 | 1906127.0 | 2004 | 41.89827996 | -87.71640551 | (41.898279962, -87.716405505) |
| 8002131 | HT233595 | 2011-04-04 | 043XX S WABASH AVE | 820 | THEFT | $500 AND UNDER | NURSING HOME/RETIREMENT HOME | 0 | 0 | 221 | 2 | 3.0 | 38.0 | 6 | 1177436.0 | 1876313.0 | 2011 | 41.81593313 | -87.62464213 | (41.815933131, -87.624642127) |
| 7903289 | HT133522 | 2010-12-30 | 083XX S KINGSTON AVE | 840 | THEFT | FINANCIAL ID THEFT: OVER $300 | RESIDENCE | 0 | 0 | 423 | 4 | 7.0 | 46.0 | 6 | 1194622.0 | 1850125.0 | 2010 | 41.74366532 | -87.56246276 | (41.743665322, -87.562462756) |
| 10402076 | HZ138551 | 2016-02-02 | 033XX W 66TH ST | 820 | THEFT | $500 AND UNDER | ALLEY | 0 | 0 | 831 | 8 | 15.0 | 66.0 | 6 | 1155240.0 | 1860661.0 | 2016 | 41.7734553 | -87.70648047 | (41.773455295, -87.706480471) |
- Install dependencies:
pip install -r requirements.txt- Run the following in a Jupyter Notebook (or Python script):
import pandas as pd
import sqlite3
import prettytable
prettytable.DEFAULT = 'DEFAULT'
con = sqlite3.connect("FinalDB.db")
cur = con.cursor()
df = pd.read_csv("ChicagoCensusData.csv")
df.to_sql("CENSUS_DATA", con, if_exists='replace', index=False, method="multi")
df = pd.read_csv("ChicagoPublicSchools.csv")
df.to_sql("CHICAGO_PUBLIC_SCHOOLS", con, if_exists='replace', index=False, method="multi")
df = pd.read_csv("ChicagoCrimeData.csv")
df.to_sql("CHICAGO_CRIME_DATA", con, if_exists='replace', index=False, method="multi")- Using magic commands to connect to the SQLite database with prefixed code,
*%%sql*for cell magic and*%sql*for line magic as shown below:
%load_ext sql
%sql sqlite:///FinalDB.db%%sql
SELECT COUNT(*)
FROM CHICAGO_CRIME_DATA;| COUNT(*) |
|---|
| 533 |
Interpretation: There were 533 crime records in the dataset.
%%sql
SELECT COMMUNITY_AREA_NAME, COMMUNITY_AREA_NUMBER, PER_CAPITA_INCOME
FROM CENSUS_DATA
WHERE PER_CAPITA_INCOME < 11000;| COMMUNITY_AREA_NAME | COMMUNITY_AREA_NUMBER | PER_CAPITA_INCOME |
|---|---|---|
| West Garfield Park | 26.0 | 10934 |
| South Lawndale | 30.0 | 10402 |
| Fuller Park | 37.0 | 10432 |
| Riverdale | 54.0 | 8201 |
Interpretation: West Garfield Park, South Lawndale, Fuller Park, and Riverdale have per capita incomes below $11,000, indicating significant economic challenges in these communities.
%%sql
SELECT CASE_NUMBER, DESCRIPTION
FROM CHICAGO_CRIME_DATA
WHERE DESCRIPTION LIKE '%MINOR%';| CASE_NUMBER | DESCRIPTION |
|---|---|
| HL266884 | SELL/GIVE/DEL LIQUOR TO MINOR |
| HK238408 | ILLEGAL CONSUMPTION BY MINOR |
Interpretation: Two crimes involving minors; one related to selling/giving liquor to a minor and another related to illegal consumption by a minor.
%%sql
SELECT CASE_NUMBER, PRIMARY_TYPE, DESCRIPTION
FROM CHICAGO_CRIME_DATA
WHERE PRIMARY_TYPE = 'KIDNAPPING'
AND DESCRIPTION LIKE '%CHILD%';| CASE_NUMBER | PRIMARY_TYPE | DESCRIPTION |
|---|---|---|
| HN144152 | KIDNAPPING | CHILD ABDUCTION/STRANGER |
Interpretation: One kidnapping case involving a child, specifically a child abduction by a stranger.
%%sql
SELECT DISTINCT PRIMARY_TYPE, DESCRIPTION, LOCATION_DESCRIPTION
FROM CHICAGO_CRIME_DATA
WHERE LOCATION_DESCRIPTION LIKE '%SCHOOL%';| CASE_NUMBER | PRIMARY_TYPE | DESCRIPTION | LOCATION_DESCRIPTION |
|---|---|---|---|
| HL353697 | BATTERY | SIMPLE | SCHOOL, PUBLIC, GROUNDS |
| HL725506 | BATTERY | PRO EMP HANDS NO/MIN INJURY | SCHOOL, PUBLIC, BUILDING |
| HP716225 | BATTERY | SIMPLE | SCHOOL, PUBLIC, BUILDING |
| HH639427 | BATTERY | SIMPLE | SCHOOL, PUBLIC, BUILDING |
| JA460432 | BATTERY | SIMPLE | SCHOOL, PUBLIC, GROUNDS |
| HS200939 | CRIMINAL DAMAGE | TO VEHICLE | SCHOOL, PUBLIC, GROUNDS |
| HK577020 | NARCOTICS | POSS: HEROIN(WHITE) | SCHOOL, PUBLIC, GROUNDS |
| HS305355 | NARCOTICS | MANU/DEL:CANNABIS 10GM OR LESS | SCHOOL, PUBLIC, BUILDING |
| HT315369 | ASSAULT | PRO EMP HANDS NO/MIN INJURY | SCHOOL, PUBLIC, GROUNDS |
| HR585012 | CRIMINAL TRESPASS | TO LAND | SCHOOL, PUBLIC, GROUNDS |
| HH292682 | PUBLIC PEACE VIOLATION | BOMB THREAT | SCHOOL, PRIVATE, BUILDING |
| G635735 | PUBLIC PEACE VIOLATION | BOMB THREAT | SCHOOL, PUBLIC, BUILDING |
Interpretation: Crimes at schools include various types of battery, criminal damage, narcotics offenses, assault, trespassing, and bomb threats. Both public and private school grounds/buildings are affected.
%%sql
SELECT `Elementary, Middle, or High School`, AVG(SAFETY_SCORE)
FROM CHICAGO_PUBLIC_SCHOOLS
GROUP BY `Elementary, Middle, or High School`;| Elementary, Middle, or High School | AVG(SAFETY_SCORE) |
|---|---|
| ES | 49.52038369304557 |
| HS | 49.62352941176471 |
| MS | 48.0 |
Interpretation: High schools have a slightly higher average safety score compared to elementary and middle schools, but the differences are minimal.
%%sql
SELECT COMMUNITY_AREA_NAME, PERCENT_HOUSEHOLDS_BELOW_POVERTY
FROM CENSUS_DATA
ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC
LIMIT 5;| COMMUNITY_AREA_NAME | PERCENT_HOUSEHOLDS_BELOW_POVERTY |
|---|---|
| Riverdale | 56.5 |
| Fuller Park | 51.2 |
| Englewood | 46.6 |
| North Lawndale | 43.1 |
| East Garfield Park | 42.4 |
Interpretation: Riverdale, Fuller Park, Englewood, North Lawndale, East Garfield Park have the highest poverty rates.
%%sql
SELECT COMMUNITY_AREA_NAME
FROM CENSUS_DATA C, (SELECT COMMUNITY_AREA_NUMBER, COUNT(COMMUNITY_AREA_NUMBER) AS NUMBER_OF_CRIME
FROM CHICAGO_CRIME_DATA
GROUP BY COMMUNITY_AREA_NUMBER
ORDER BY COUNT(COMMUNITY_AREA_NUMBER) DESC) D
WHERE C.COMMUNITY_AREA_NUMBER=D.COMMUNITY_AREA_NUMBER
ORDER BY NUMBER_OF_CRIME DESC
LIMIT 1;| COMMUNITY_AREA_NUMBER |
|---|
| 25.0 |
Interpretation: Community area number 25.0 has the highest number of recorded crimes in the dataset.
%%sql
SELECT COMMUNITY_AREA_NAME
FROM CENSUS_DATA
WHERE HARDSHIP_INDEX = (
SELECT MAX(HARDSHIP_INDEX)
FROM CENSUS_DATA
);| COMMUNITY_AREA_NAME |
|---|
| Riverdale |
Interpretation: Riverdale has the highest hardship index, indicating it faces significant socioeconomic challenges.
%%sql
SELECT COMMUNITY_AREA_NAME
FROM CENSUS_DATA C, (SELECT COMMUNITY_AREA_NUMBER, COUNT(COMMUNITY_AREA_NUMBER) AS NUMBER_OF_CRIME
FROM CHICAGO_CRIME_DATA
GROUP BY COMMUNITY_AREA_NUMBER
ORDER BY COUNT(COMMUNITY_AREA_NUMBER) DESC) D
WHERE C.COMMUNITY_AREA_NUMBER=D.COMMUNITY_AREA_NUMBER
ORDER BY NUMBER_OF_CRIME DESC
LIMIT 1;| COMMUNITY_AREA_NAME |
|---|
| Austin |
Interpretation: Austin is the community area with the most recorded crimes in the dataset.
- Crime is unevenly distributed across Chicago communities
- High poverty and hardship indices strongly correlate with crime concentration
- Schools are common locations for various crime types
- Relational databases + SQL enable powerful cross-domain insights
- Clean data pipelines are essential for urban analytics
- This project showcases how integrated data analysis can inform urban policy and community interventions.
This project shows end-to-end analytical ownership:
Data ingestion → Database design → SQL analysis → Interpretation — all using real urban data.
- LinkedIn: linkedin.com/in/emycodes
- Role Interests: Data Analyst, Business Intelligence Analyst, Product Analyst.
"Data is most powerful when it serves as a clear, honest bridge between raw numbers and strategic growth."
©️ EmyCodes | 2026