<jupyter_start><jupyter_text>Analyzing the Census, Crime, and School Data of Chicago, IL  IntroductionYou 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. A Jupyter notebook is provided to help you complete this assignment. Follow the instructions to complete all the problems, then share the Queries and Results with your peers for review.Using this Python notebook you will:1.  Understand three Chicago datasets  2.  Load the three datasets into three tables in a Db2 database3.  Execute SQL queries to answer assignment questions   Understand the datasetsTo complete the assignment problems in this notebook you will be using three datasets that are available on the city of Chicago's Data Portal:1.  Socioeconomic Indicators in Chicago2.  Chicago Public Schools3.  Chicago Crime Data 1. Socioeconomic Indicators in ChicagoThis dataset contains a selection of six socioeconomic indicators of public health significance and a “hardship index,” for each Chicago community area, for the years 2008 – 2012.A detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:[https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2](https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2?cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ) 2. Chicago Public SchoolsThis dataset shows all school level performance data used to create CPS School Report Cards for the 2011-2012 school year. This dataset is provided by the city of Chicago's Data Portal.A detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:[https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t](https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t?cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ) 3. Chicago Crime DataThis dataset reflects reported incidents of crime (with the exception of murders where data exists for each victim) that occurred in the City of Chicago from 2001 to present, minus the most recent seven days. A detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:[https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2](https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2?cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ)  Download the datasetsThis assignment requires you to have these three tables populated with a subset of the whole datasets.In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the links below to download and save the datasets (.CSV files):-   Chicago Census Data-   Chicago Public Schools-   Chicago Crime Data**NOTE:** Ensure you have downloaded the datasets using the links above instead of directly from the Chicago Data Portal. The versions linked here are subsets of the original datasets and have some of the column names modified to be more database friendly which will make it easier to complete this assignment.  Store the datasets in database tablesTo analyze the data using SQL, it first needs to be stored in the database.While it is easier to read the dataset into a Pandas dataframe and then PERSIST it into the database as we saw in Week 3 Lab 3, it results in mapping to default datatypes which may not be optimal for SQL querying. For example a long textual field may map to a CLOB instead of a VARCHAR. Therefore, **it is highly recommended to manually load the table using the database console LOAD tool, as indicated in Week 2 Lab 1 Part II**. The only difference with that lab is that in Step 5 of the instructions you will need to click on create "(+) New Table" and specify the name of the table you want to create and then click "Next".  Now open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the first dataset, Next create a New Table, and then follow the steps on-screen instructions to load the data. Name the new tables as follows:1.  **CENSUS_DATA**2.  **CHICAGO_PUBLIC_SCHOOLS**3.  **CHICAGO_CRIME_DATA**  Connect to the databaseLet us first load the SQL extension and establish a connection with the database<jupyter_code>%load_ext sql<jupyter_output><empty_output><jupyter_text>In the next cell enter your db2 connection string. Recall you created Service Credentials for your Db2 instance in first lab in Week 3. From the **uri** field of your Db2 service credentials copy everything after db2:// (except the double quote at the end) and paste it in the cell below after ibm_db_sa://<jupyter_code># Remember the connection string is of the format:
# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name
# Enter the connection string for your Db2 on Cloud database instance below
%sql ibm_db_sa://mjw47968:175%401940ljjvk<EMAIL>@dash<EMAIL>:50000/BLUDB<jupyter_output><empty_output><jupyter_text>ProblemsNow write and execute SQL queries to solve assignment problems Problem 1 Find the total number of crimes recorded in the CRIME table.<jupyter_code>%%sql
SELECT COUNT(DISTINCT(CASE_NUMBER)) AS TOTAL_NUM_OF_CRIMES
FROM CHICAGO_CRIME_DATA;<jupyter_output>* ibm_db_sa://mjw47968:***@dash<EMAIL>.<EMAIL>:50000/BLUDB
Done.<jupyter_text>Problem 2 List community areas with per capita income less than 11000.<jupyter_code>%%sql
SELECT COMMUNITY_AREA_NAME, PER_CAPITA_INCOME AS INCOME_PER_CAPITA_UNDER_11000
FROM CENSUS_DATA
WHERE PER_CAPITA_INCOME < 11000
ORDER BY PER_CAPITA_INCOME DESC;<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 3 List all case numbers for crimes  involving minors?<jupyter_code>%%sql
SELECT CASE_NUMBER, PRIMARY_TYPE, DESCRIPTION
FROM CHICAGO_CRIME_DATA
WHERE PRIMARY_TYPE LIKE '%CHILD%' OR DESCRIPTION LIKE '%MINOR%';<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 4 List all kidnapping crimes involving a child?<jupyter_code>%%sql
SELECT ID, CASE_NUMBER, DATE, PRIMARY_TYPE, DESCRIPTION, ARREST
FROM CHICAGO_CRIME_DATA
WHERE PRIMARY_TYPE LIKE '%KIDNAPPING%' AND DESCRIPTION LIKE '%CHILD%';<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 5 What kinds of crimes were recorded at schools?<jupyter_code>%%sql
SELECT ID, CASE_NUMBER, DATE, PRIMARY_TYPE, DESCRIPTION, LOCATION_DESCRIPTION, ARREST
FROM CHICAGO_CRIME_DATA
WHERE LOCATION_DESCRIPTION LIKE '%SCHOOL%';<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 6 List the average safety score for all types of schools.<jupyter_code># The first solution lists each type of school along with their average safety score.
%%sql
SELECT "Elementary, Middle, or High School" AS TYPE_OF_SCHOOL, AVG(SAFETY_SCORE) AS AVG_SAFETY_SCORE
FROM CHICAGO_PUBLIC_SCHOOLS
GROUP BY "Elementary, Middle, or High School";
# The second solution simply takes the average safety score of all schools.
%%sql
SELECT AVG(SAFETY_SCORE) AS AVG_SAFETY_SCORE
FROM CHICAGO_PUBLIC_SCHOOLS;<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 7 List 5 community areas with highest % of households below poverty line<jupyter_code>%%sql
SELECT COMMUNITY_AREA_NAME, PERCENT_HOUSEHOLDS_BELOW_POVERTY
FROM CENSUS_DATA
ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC
LIMIT 5;<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 8 Which community area is most crime prone?<jupyter_code>%%sql
SELECT COMMUNITY_AREA_NUMBER, COUNT(CASE_NUMBER) AS NUM_CASES
FROM CHICAGO_CRIME_DATA
GROUP BY COMMUNITY_AREA_NUMBER
ORDER BY 2 DESC
LIMIT 1;<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 9 Use a sub-query to find the name of the community area with highest hardship index<jupyter_code>%%sql
SELECT COMMUNITY_AREA_NAME, HARDSHIP_INDEX FROM CENSUS_DATA
WHERE HARDSHIP_INDEX = (SELECT MAX(HARDSHIP_INDEX) FROM CENSUS_DATA);<jupyter_output>* ibm_db_sa://mjw47968:***@dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net:50000/BLUDB
Done.<jupyter_text>Problem 10 Use a sub-query to determine the Community Area Name with most number of crimes?<jupyter_code>%%sql
SELECT COMMUNITY_AREA_NAME, NUM_OF_CRIMES
FROM (SELECT CD.COMMUNITY_AREA_NAME, COUNT(CD.COMMUNITY_AREA_NAME) NUM_OF_CRIMES
      FROM CENSUS_DATA AS CD, CHICAGO_CRIME_DATA AS CCD
      WHERE CD.COMMUNITY_AREA_NUMBER = CCD.COMMUNITY_AREA_NUMBER
      GROUP BY CD.COMMUNITY_AREA_NAME)
ORDER BY NUM_OF_CRIMES DESC
LIMIT 1;
%%sql
SELECT COMMUNITY_AREA_NAME, COMMUNITY_AREA_NUMBER
FROM CENSUS_DATA
WHERE COMMUNITY_AREA_NUMBER = (SELECT COMMUNITY_AREA_NUMBER
                               FROM CHICAGO_CRIME_DATA
                               GROUP BY COMMUNITY_AREA_NUMBER
                               ORDER BY COUNT(*) DESC
                               LIMIT 1);
# First solution was an implicit join in a sub-query. 
# Second solution is simpler and easier to read, but does not explicitly output the number of crimes alongside the name of the community.
# Both arrive at the same desired outcome.<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup