You have been hired by an organization that strives to improve educational outcomes for children and young people in Chicago. Your job is to analyze the census, crime, and school data for a given neighborhood or district. You will identify causes that impact the enrollment, safety, health, environment ratings of schools. You will be required to answer questions similar to what a real-life data analyst or data scientist would be tasked with. You will be assessed both on the correctness of your SQL queries and results.
- Question 1: Find the total number of crimes recorded in the CRIME table.
- Question 2: List community areas with per capita income less than 11000.
- Question 3: List all case numbers for crimes involving minors?
- Question 4: List all kidnapping crimes involving a child (children are not considered minors for the purposes of crime analysis)?
- Question 5: What kind of crimes were recorded at schools?
- Question 6: List the average safety score for all types of schools.
- Question 7: List 5 community areas with highest % of households below poverty line.
- Question 8: Which community area(number) is most crime prone?
- Question 9: Use a sub-query to find the name of the community area with highest hardship index.
- Question 10: Use a sub-query to determine the Community Area Name with most number of crimes?