Databases and Python (PeerAssign).ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "<center>\n    <img src=\"https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/Logos/organization_logo/organization_logo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n</center>\n\n<h1 align=center><font size = 5>Assignment: Notebook for Peer Assignment</font></h1>\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "# Introduction\n\nUsing this Python notebook you will:\n\n1.  Understand three Chicago datasets\n2.  Load the three datasets into three tables in a Db2 database\n3.  Execute SQL queries to answer assignment questions\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "## Understand the datasets\n\nTo complete the assignment problems in this notebook you will be using three datasets that are available on the city of Chicago's Data Portal:\n\n1.  <a href=\"https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\">Socioeconomic Indicators in Chicago</a>\n2.  <a href=\"https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\">Chicago Public Schools</a>\n3.  <a href=\"https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\">Chicago Crime Data</a>\n\n### 1. Socioeconomic Indicators in Chicago\n\nThis dataset contains a selection of six socioeconomic indicators of public health significance and a \u201chardship index,\u201d for each Chicago community area, for the years 2008 \u2013 2012.\n\nA detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:\n[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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&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)\n\n### 2. Chicago Public Schools\n\nThis 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.\n\nA detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:\n[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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&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)\n\n### 3. Chicago Crime Data\n\nThis 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.\n\nA detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:\n[https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2](https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&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)\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Download the datasets\n\nThis assignment requires you to have these three tables populated with a subset of the whole datasets.\n\nIn 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):\n\n*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCensusData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Chicago Census Data</a>\n\n*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoPublicSchools.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Chicago Public Schools</a>\n\n*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCrimeData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Chicago Crime Data</a>\n\n**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.\n"
        },
        {
            "cell_type": "code",
            "execution_count": 1,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": "Collecting ibm_db==3.1.0\n  Using cached ibm_db-3.1.0-cp39-cp39-linux_x86_64.whl\nCollecting ibm_db_sa==0.3.3\n  Using cached ibm_db_sa-0.3.3-py3-none-any.whl\nCollecting sqlalchemy>=0.7.3\n  Using cached SQLAlchemy-1.4.32-cp39-cp39-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.6 MB)\nCollecting greenlet!=0.4.17\n  Using cached greenlet-1.1.2-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (153 kB)\nInstalling collected packages: greenlet, sqlalchemy, ibm-db-sa, ibm-db\n  Attempting uninstall: greenlet\n    Found existing installation: greenlet 1.1.2\n    Uninstalling greenlet-1.1.2:\n      Successfully uninstalled greenlet-1.1.2\n  Attempting uninstall: sqlalchemy\n    Found existing installation: SQLAlchemy 1.3.24\n    Uninstalling SQLAlchemy-1.3.24:\n      Successfully uninstalled SQLAlchemy-1.3.24\n  Attempting uninstall: ibm-db-sa\n    Found existing installation: ibm-db-sa 0.3.3\n    Uninstalling ibm-db-sa-0.3.3:\n      Successfully uninstalled ibm-db-sa-0.3.3\n  Attempting uninstall: ibm-db\n    Found existing installation: ibm-db 3.1.0\n    Uninstalling ibm-db-3.1.0:\n      Successfully uninstalled ibm-db-3.1.0\nSuccessfully installed greenlet-1.1.2 ibm-db-3.1.0 ibm-db-sa-0.3.3 sqlalchemy-1.4.32\nFound existing installation: SQLAlchemy 1.4.32\nUninstalling SQLAlchemy-1.4.32:\n  Successfully uninstalled SQLAlchemy-1.4.32\nCollecting sqlalchemy==1.3.24\n  Using cached SQLAlchemy-1.3.24-cp39-cp39-manylinux2010_x86_64.whl (1.3 MB)\nInstalling collected packages: sqlalchemy\nSuccessfully installed sqlalchemy-1.3.24\nRequirement already satisfied: ipython-sql in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (0.4.0)\nRequirement already satisfied: six in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython-sql) (1.15.0)\nRequirement already satisfied: prettytable<1 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython-sql) (0.7.2)\nRequirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython-sql) (0.2.0)\nRequirement already satisfied: sqlparse in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython-sql) (0.4.2)\nRequirement already satisfied: ipython>=1.0 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython-sql) (7.29.0)\nRequirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython-sql) (1.3.24)\nRequirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (58.0.4)\nRequirement already satisfied: pickleshare in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\nRequirement already satisfied: jedi>=0.16 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (0.18.0)\nRequirement already satisfied: backcall in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\nRequirement already satisfied: traitlets>=4.2 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (5.1.1)\nRequirement already satisfied: pexpect>4.3 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\nRequirement already satisfied: pygments in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (2.10.0)\nRequirement already satisfied: decorator in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (5.1.0)\nRequirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (3.0.20)\nRequirement already satisfied: matplotlib-inline in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from ipython>=1.0->ipython-sql) (0.1.2)\nRequirement already satisfied: parso<0.9.0,>=0.8.0 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.8.3)\nRequirement already satisfied: ptyprocess>=0.5 in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql) (0.7.0)\nRequirement already satisfied: wcwidth in /opt/conda/envs/Python-3.9/lib/python3.9/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)\n"
                }
            ],
            "source": "!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n!pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24\n!pip install ipython-sql"
        },
        {
            "cell_type": "code",
            "execution_count": 2,
            "metadata": {},
            "outputs": [],
            "source": "%load_ext sql"
        },
        {
            "cell_type": "code",
            "execution_count": 3,
            "metadata": {},
            "outputs": [],
            "source": "%sql ibm_db_sa://***************************************************************************************************"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "## Problems\n\nNow write and execute SQL queries to solve assignment problems\n\n### Problem 1\n\n##### Find the total number of crimes recorded in the CRIME table.\n"
        },
        {
            "cell_type": "code",
            "execution_count": 4,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>chicago_total_crimes</th>\n    </tr>\n    <tr>\n        <td>533</td>\n    </tr>\n</table>",
                        "text/plain": "[(533,)]"
                    },
                    "execution_count": 4,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect count(primary_type) CHICAGO_TOTAL_CRIMES \n    FROM CHICAGO_CRIME_DATA;"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 2\n\n##### List community areas with per capita income less than 11000.\n"
        },
        {
            "cell_type": "code",
            "execution_count": 5,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>community_area_name</th>\n        <th>per_capita_income</th>\n    </tr>\n    <tr>\n        <td>West Garfield Park</td>\n        <td>10934</td>\n    </tr>\n    <tr>\n        <td>South Lawndale</td>\n        <td>10402</td>\n    </tr>\n    <tr>\n        <td>Fuller Park</td>\n        <td>10432</td>\n    </tr>\n    <tr>\n        <td>Riverdale</td>\n        <td>8201</td>\n    </tr>\n</table>",
                        "text/plain": "[('West Garfield Park', 10934),\n ('South Lawndale', 10402),\n ('Fuller Park', 10432),\n ('Riverdale', 8201)]"
                    },
                    "execution_count": 5,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect community_area_name, per_capita_income\n    from chicago_socioeconomic_data\n    where per_capita_income < 11000;"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 3\n\n##### List all case numbers for crimes  involving minors?(children are not considered minors for the purposes of crime analysis)\n"
        },
        {
            "cell_type": "code",
            "execution_count": 6,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>crimes_involving_minors</th>\n    </tr>\n    <tr>\n        <td>HL266884</td>\n    </tr>\n    <tr>\n        <td>HK238408</td>\n    </tr>\n</table>",
                        "text/plain": "[('HL266884',), ('HK238408',)]"
                    },
                    "execution_count": 6,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect case_number as crimes_involving_minors\n    from chicago_crime_data\n    where description like '%MINOR%';"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 4\n\n##### List all kidnapping crimes involving a child?\n"
        },
        {
            "cell_type": "code",
            "execution_count": 7,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>id</th>\n        <th>case_number</th>\n        <th>DATE</th>\n        <th>block</th>\n        <th>iucr</th>\n        <th>primary_type</th>\n        <th>description</th>\n        <th>location_description</th>\n        <th>arrest</th>\n        <th>domestic</th>\n        <th>beat</th>\n        <th>district</th>\n        <th>ward</th>\n        <th>community_area_number</th>\n        <th>fbicode</th>\n        <th>x_coordinate</th>\n        <th>y_coordinate</th>\n        <th>YEAR</th>\n        <th>latitude</th>\n        <th>longitude</th>\n        <th>location</th>\n    </tr>\n    <tr>\n        <td>5276766</td>\n        <td>HN144152</td>\n        <td>2007-01-26</td>\n        <td>050XX W VAN BUREN ST</td>\n        <td>1792</td>\n        <td>KIDNAPPING</td>\n        <td>CHILD ABDUCTION/STRANGER</td>\n        <td>STREET</td>\n        <td>FALSE</td>\n        <td>FALSE</td>\n        <td>1533</td>\n        <td>15</td>\n        <td>29</td>\n        <td>25</td>\n        <td>20</td>\n        <td>1143050</td>\n        <td>1897546</td>\n        <td>2007</td>\n        <td>41.87490841</td>\n        <td>-87.75024931</td>\n        <td>(41.874908413, -87.750249307)</td>\n    </tr>\n</table>",
                        "text/plain": "[(5276766, 'HN144152', datetime.date(2007, 1, 26), '050XX W VAN BUREN ST', '1792', 'KIDNAPPING', 'CHILD ABDUCTION/STRANGER', 'STREET', 'FALSE', 'FALSE', 1533, 15, 29, 25, '20', 1143050, 1897546, 2007, Decimal('41.87490841'), Decimal('-87.75024931'), '(41.874908413, -87.750249307)')]"
                    },
                    "execution_count": 7,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect *\n    from chicago_crime_data\n    where primary_type = 'KIDNAPPING' AND description like '%CHILD%';"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 5\n\n##### What kinds of crimes were recorded at schools?\n"
        },
        {
            "cell_type": "code",
            "execution_count": 8,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>crimes_in_schools</th>\n        <th>frequency</th>\n    </tr>\n    <tr>\n        <td>ASSAULT</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>BATTERY</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>CRIMINAL DAMAGE</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>CRIMINAL TRESPA</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>NARCOTICS</td>\n        <td>2</td>\n    </tr>\n    <tr>\n        <td>PUBLIC PEACE VI</td>\n        <td>2</td>\n    </tr>\n</table>",
                        "text/plain": "[('ASSAULT', 1),\n ('BATTERY', 5),\n ('CRIMINAL DAMAGE', 1),\n ('CRIMINAL TRESPA', 1),\n ('NARCOTICS', 2),\n ('PUBLIC PEACE VI', 2)]"
                    },
                    "execution_count": 8,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql\\\nselect primary_type as Crimes_in_schools, count(*) as frequency\\\n    from chicago_crime_data\\\n    where location_description like '%SCHOOL%'\\\n    group by primary_type;"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 6\n\n##### List the average safety score for all types of schools.\n"
        },
        {
            "cell_type": "code",
            "execution_count": 9,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>elementary_middle_or_high_school</th>\n        <th>average_safety_score</th>\n    </tr>\n    <tr>\n        <td>ES</td>\n        <td>49</td>\n    </tr>\n    <tr>\n        <td>HS</td>\n        <td>49</td>\n    </tr>\n    <tr>\n        <td>MS</td>\n        <td>48</td>\n    </tr>\n</table>",
                        "text/plain": "[('ES', 49), ('HS', 49), ('MS', 48)]"
                    },
                    "execution_count": 9,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect elementary_middle_or_high_school, avg(safety_score) as average_safety_score \n    from chicago_public_schools \n    group by elementary_middle_or_high_school;"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 7\n\n##### List 5 community areas with highest % of households below poverty line\n"
        },
        {
            "cell_type": "code",
            "execution_count": 10,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>community_area_name</th>\n        <th>percent_households_below_poverty</th>\n    </tr>\n    <tr>\n        <td>Riverdale</td>\n        <td>56.5</td>\n    </tr>\n    <tr>\n        <td>Fuller Park</td>\n        <td>51.2</td>\n    </tr>\n    <tr>\n        <td>Englewood</td>\n        <td>46.6</td>\n    </tr>\n    <tr>\n        <td>North Lawndale</td>\n        <td>43.1</td>\n    </tr>\n    <tr>\n        <td>East Garfield Park</td>\n        <td>42.4</td>\n    </tr>\n</table>",
                        "text/plain": "[('Riverdale', Decimal('56.5')),\n ('Fuller Park', Decimal('51.2')),\n ('Englewood', Decimal('46.6')),\n ('North Lawndale', Decimal('43.1')),\n ('East Garfield Park', Decimal('42.4'))]"
                    },
                    "execution_count": 10,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect COMMUNITY_AREA_NAME, PERCENT_HOUSEHOLDS_BELOW_POVERTY\n    from CHICAGO_SOCIOECONOMIC_DATA\n    order by PERCENT_HOUSEHOLDS_BELOW_POVERTY desc\n    limit 5;"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 8\n\n##### Which community area is most crime prone?\n"
        },
        {
            "cell_type": "code",
            "execution_count": 11,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>most_crime_prone_area</th>\n    </tr>\n    <tr>\n        <td>25</td>\n    </tr>\n</table>",
                        "text/plain": "[(25,)]"
                    },
                    "execution_count": 11,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect community_area_number as most_crime_prone_area\n    from chicago_crime_data\n    group by community_area_number\n    order by count(*) desc\n    limit 1;"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "Double-click **here** for a hint\n\n<!--\nQuery for the 'community area number' that is most crime prone.\n-->\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 9\n\n##### Use a sub-query to find the name of the community area with highest hardship index\n"
        },
        {
            "cell_type": "code",
            "execution_count": 12,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>hardest_community</th>\n    </tr>\n    <tr>\n        <td>Riverdale</td>\n    </tr>\n</table>",
                        "text/plain": "[('Riverdale',)]"
                    },
                    "execution_count": 12,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect community_area_name as hardest_community\n    from chicago_socioeconomic_data\n    where hardship_index = \n        (select max(hardship_index)\n            from chicago_socioeconomic_data);"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Problem 10\n\n##### Use a sub-query to determine the Community Area Name with most number of crimes?\n"
        },
        {
            "cell_type": "code",
            "execution_count": 13,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://xkl26971:***@9938aec0-8105-433e-8bf9-0fbb7e483086.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32459/BLUDB\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>most_crime_prone_area</th>\n    </tr>\n    <tr>\n        <td>Austin</td>\n    </tr>\n</table>",
                        "text/plain": "[('Austin',)]"
                    },
                    "execution_count": 13,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nselect community_area_name as most_crime_prone_area\n    from chicago_socioeconomic_data \n    where community_area_number in\n        (select community_area_number \n            from chicago_crime_data\n            group by community_area_number\n            order by count(*) desc\n            limit 1);"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "Copyright \u00a9 2020 [cognitiveclass.ai](cognitiveclass.ai?utm_source=bducopyrightlink&utm_medium=dswb&utm_campaign=bdu). This notebook and its source code are released under the terms of the [MIT License](https://bigdatauniversity.com/mit-license?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&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).\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "## Author(s)\n\n<h4> Hima Vasudevan </h4>\n<h4> Rav Ahuja </h4>\n<h4> Ramesh Sannreddy </h4>\n\n## Contribtuor(s)\n\n<h4> Malika Singla </h4>\n\n## Change log\n\n| Date       | Version | Changed by        | Change Description                             |\n| ---------- | ------- | ----------------- | ---------------------------------------------- |\n| 2021-11-17 | 2.6     | Lakshmi           | Updated library                                |\n| 2021-05-19 | 2.4     | Lakshmi Holla     | Updated the question                           |\n| 2021-04-30 | 2.3     | Malika Singla     | Updated the libraries                          |\n| 2021-01-15 | 2.2     | Rav Ahuja         | Removed problem 11 and fixed changelog         |\n| 2020-11-25 | 2.1     | Ramesh Sannareddy | Updated the problem statements, and datasets   |\n| 2020-09-05 | 2.0     | Malika Singla     | Moved lab to course repo in GitLab             |\n| 2018-07-18 | 1.0     | Rav Ahuja         | Several updates including loading instructions |\n| 2018-05-04 | 0.1     | Hima Vasudevan    | Created initial version                        |\n\n## <h3 align=\"center\"> \u00a9 IBM Corporation 2020. All rights reserved. <h3/>\n"
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3.9",
            "language": "python",
            "name": "python3"
        },
        "language_info": {
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "file_extension": ".py",
            "mimetype": "text/x-python",
            "name": "python",
            "nbconvert_exporter": "python",
            "pygments_lexer": "ipython3",
            "version": "3.9.7"
        },
        "widgets": {
            "state": {},
            "version": "1.1.2"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 4
}