06. SQL Assignment - Chicago.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/images/SN_web_lightmode.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",
    "\n",
    "Using this Python notebook you will:\n",
    "\n",
    "1.  Understand three Chicago datasets  \n",
    "2.  Load the three datasets into three tables in a Db2 database\n",
    "3.  Execute SQL queries to answer assignment questions \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Understand the datasets\n",
    "\n",
    "To 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",
    "\n",
    "1.  <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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork22-2022-01-01\">Socioeconomic Indicators in Chicago</a>\n",
    "2.  <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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork22-2022-01-01\">Chicago Public Schools</a>\n",
    "3.  <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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork22-2022-01-01\">Chicago Crime Data</a>\n",
    "\n",
    "### 1. Socioeconomic Indicators in Chicago\n",
    "\n",
    "This 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.\n",
    "\n",
    "A 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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork22-2022-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",
    "\n",
    "This 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",
    "\n",
    "A 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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork22-2022-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",
    "\n",
    "This 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",
    "\n",
    "A 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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork22-2022-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",
    "\n",
    "This assignment requires you to have these three tables populated with a subset of the whole datasets.\n",
    "\n",
    "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):\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\" 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\" 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\" target=\"_blank\">Chicago Crime Data</a>\n",
    "\n",
    "**NOTE**: For the learners who are encountering issues with loading from .csv in DB2 on Firefox, you can download the .txt files and load the data with those:\n",
    "\n",
    "- <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCensusData.txt\" 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.txt\" 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.txt\" 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": "markdown",
   "metadata": {},
   "source": [
    "### Store the datasets in database tables\n",
    "\n",
    "To analyze the data using SQL, it first needs to be stored in the database.\n",
    "\n",
    "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. \n",
    "\n",
    "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\". \n",
    "\n",
    "<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/images/LoadingData.png\">\n",
    "\n",
    "##### 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:\n",
    "\n",
    "1.  **CENSUS_DATA**\n",
    "2.  **CHICAGO_PUBLIC_SCHOOLS**\n",
    "3.  **CHICAGO_CRIME_DATA**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "\n",
    "Let us first load the SQL extension and establish a connection with the database\n",
    "\n",
    "The following required modules are pre-installed in the Skills Network Labs environment. However if you run this notebook commands in a different Jupyter environment (e.g. Watson Studio or Ananconda) you may need to install these libraries by removing the `#` sign before `!pip` in the code cell below.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting ibm_db==3.1.0\n",
      "  Using cached ibm_db-3.1.0-cp37-cp37m-linux_x86_64.whl\n",
      "Collecting ibm_db_sa==0.3.3\n",
      "  Using cached ibm_db_sa-0.3.3-py3-none-any.whl\n",
      "Collecting sqlalchemy>=0.7.3 (from ibm_db_sa==0.3.3)\n",
      "  Downloading SQLAlchemy-2.0.21-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.9 MB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.9/2.9 MB\u001b[0m \u001b[31m75.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m:00:01\u001b[0m\n",
      "\u001b[?25hCollecting typing-extensions>=4.2.0 (from sqlalchemy>=0.7.3->ibm_db_sa==0.3.3)\n",
      "  Downloading typing_extensions-4.7.1-py3-none-any.whl (33 kB)\n",
      "Collecting greenlet!=0.4.17 (from sqlalchemy>=0.7.3->ibm_db_sa==0.3.3)\n",
      "  Downloading greenlet-3.0.0-cp37-cp37m-manylinux_2_24_x86_64.manylinux_2_28_x86_64.whl (568 kB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m568.8/568.8 kB\u001b[0m \u001b[31m55.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hCollecting importlib-metadata (from sqlalchemy>=0.7.3->ibm_db_sa==0.3.3)\n",
      "  Downloading importlib_metadata-6.7.0-py3-none-any.whl (22 kB)\n",
      "Collecting zipp>=0.5 (from importlib-metadata->sqlalchemy>=0.7.3->ibm_db_sa==0.3.3)\n",
      "  Downloading zipp-3.15.0-py3-none-any.whl (6.8 kB)\n",
      "Installing collected packages: ibm_db, zipp, typing-extensions, greenlet, importlib-metadata, sqlalchemy, ibm_db_sa\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\n",
      "  Attempting uninstall: zipp\n",
      "    Found existing installation: zipp 3.15.0\n",
      "    Uninstalling zipp-3.15.0:\n",
      "      Successfully uninstalled zipp-3.15.0\n",
      "  Attempting uninstall: typing-extensions\n",
      "    Found existing installation: typing_extensions 4.5.0\n",
      "    Uninstalling typing_extensions-4.5.0:\n",
      "      Successfully uninstalled typing_extensions-4.5.0\n",
      "  Attempting uninstall: importlib-metadata\n",
      "    Found existing installation: importlib-metadata 4.11.4\n",
      "    Uninstalling importlib-metadata-4.11.4:\n",
      "      Successfully uninstalled importlib-metadata-4.11.4\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",
      "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
      "dash 2.9.3 requires dash-core-components==2.0.0, which is not installed.\n",
      "dash 2.9.3 requires dash-html-components==2.0.0, which is not installed.\n",
      "dash 2.9.3 requires dash-table==5.0.0, which is not installed.\u001b[0m\u001b[31m\n",
      "\u001b[0mSuccessfully installed greenlet-3.0.0 ibm_db-3.1.0 ibm_db_sa-0.3.3 importlib-metadata-6.7.0 sqlalchemy-2.0.21 typing-extensions-4.7.1 zipp-3.15.0\n",
      "Found existing installation: SQLAlchemy 2.0.21\n",
      "Uninstalling SQLAlchemy-2.0.21:\n",
      "  Successfully uninstalled SQLAlchemy-2.0.21\n",
      "Collecting sqlalchemy==1.3.24\n",
      "  Using cached SQLAlchemy-1.3.24-cp37-cp37m-manylinux2010_x86_64.whl (1.3 MB)\n",
      "Installing collected packages: sqlalchemy\n",
      "Successfully installed sqlalchemy-1.3.24\n",
      "Requirement already satisfied: ipython-sql in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (0.3.9)\n",
      "Requirement already satisfied: prettytable in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (3.7.0)\n",
      "Requirement already satisfied: ipython>=1.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (7.33.0)\n",
      "Requirement already satisfied: sqlalchemy>=0.6.7 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (1.3.24)\n",
      "Requirement already satisfied: sqlparse in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (0.4.4)\n",
      "Requirement already satisfied: six in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: ipython-genutils>=0.1.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: setuptools>=18.5 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (67.7.2)\n",
      "Requirement already satisfied: jedi>=0.16 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.18.2)\n",
      "Requirement already satisfied: decorator in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: pickleshare in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: traitlets>=4.2 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (5.9.0)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (3.0.38)\n",
      "Requirement already satisfied: pygments in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (2.15.1)\n",
      "Requirement already satisfied: backcall in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: matplotlib-inline in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.1.6)\n",
      "Requirement already satisfied: pexpect>4.3 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\n",
      "Requirement already satisfied: importlib-metadata in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from prettytable->ipython-sql) (6.7.0)\n",
      "Requirement already satisfied: wcwidth in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from prettytable->ipython-sql) (0.2.6)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.8.3)\n",
      "Requirement already satisfied: ptyprocess>=0.5 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql) (0.7.0)\n",
      "Requirement already satisfied: zipp>=0.5 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from importlib-metadata->prettytable->ipython-sql) (3.15.0)\n",
      "Requirement already satisfied: typing-extensions>=3.6.4 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from importlib-metadata->prettytable->ipython-sql) (4.7.1)\n"
     ]
    }
   ],
   "source": [
    "# These libraries are pre-installed in SN Labs. If running in another environment please uncomment lines below to install them:\n",
    "!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n",
    "# Ensure we don't load_ext with sqlalchemy>=1.4 (incompadible)\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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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 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://\n",
    "\n",
    "<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/images/details.png\">\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: qmp97616@bludb'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remember the connection string is of the format:\n",
    "# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name?security=SSL\n",
    "# Enter the connection string for your Db2 on Cloud database instance below\n",
    "%sql ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb?security=SSL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problems\n",
    "\n",
    "Now 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": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>count_of_crimes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>533</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(533,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rows in Crime table\n",
    "%sql SELECT COUNT(*) as count_of_crimes 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": 11,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_number</th>\n",
       "            <th>community_area_name</th>\n",
       "            <th>percent_of_housing_crowded</th>\n",
       "            <th>percent_households_below_poverty</th>\n",
       "            <th>percent_aged_16__unemployed</th>\n",
       "            <th>percent_aged_25__without_high_school_diploma</th>\n",
       "            <th>percent_aged_under_18_or_over_64</th>\n",
       "            <th>per_capita_income</th>\n",
       "            <th>hardship_index</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>Rogers Park</td>\n",
       "            <td>7.7</td>\n",
       "            <td>23.6</td>\n",
       "            <td>8.7</td>\n",
       "            <td>18.2</td>\n",
       "            <td>27.5</td>\n",
       "            <td>23939</td>\n",
       "            <td>39</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>West Ridge</td>\n",
       "            <td>7.8</td>\n",
       "            <td>17.2</td>\n",
       "            <td>8.8</td>\n",
       "            <td>20.8</td>\n",
       "            <td>38.5</td>\n",
       "            <td>23040</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Uptown</td>\n",
       "            <td>3.8</td>\n",
       "            <td>24.0</td>\n",
       "            <td>8.9</td>\n",
       "            <td>11.8</td>\n",
       "            <td>22.2</td>\n",
       "            <td>35787</td>\n",
       "            <td>20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Lincoln Square</td>\n",
       "            <td>3.4</td>\n",
       "            <td>10.9</td>\n",
       "            <td>8.2</td>\n",
       "            <td>13.4</td>\n",
       "            <td>25.5</td>\n",
       "            <td>37524</td>\n",
       "            <td>17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>North Center</td>\n",
       "            <td>0.3</td>\n",
       "            <td>7.5</td>\n",
       "            <td>5.2</td>\n",
       "            <td>4.5</td>\n",
       "            <td>26.2</td>\n",
       "            <td>57123</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rogers Park', Decimal('7.7'), Decimal('23.6'), Decimal('8.7'), Decimal('18.2'), Decimal('27.5'), 23939, 39),\n",
       " (2, 'West Ridge', Decimal('7.8'), Decimal('17.2'), Decimal('8.8'), Decimal('20.8'), Decimal('38.5'), 23040, 46),\n",
       " (3, 'Uptown', Decimal('3.8'), Decimal('24.0'), Decimal('8.9'), Decimal('11.8'), Decimal('22.2'), 35787, 20),\n",
       " (4, 'Lincoln Square', Decimal('3.4'), Decimal('10.9'), Decimal('8.2'), Decimal('13.4'), Decimal('25.5'), 37524, 17),\n",
       " (5, 'North Center', Decimal('0.3'), Decimal('7.5'), Decimal('5.2'), Decimal('4.5'), Decimal('26.2'), 57123, 6)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CENSUS_DATA LIMIT 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_name</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>West Garfield Park</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Lawndale</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fuller Park</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Riverdale</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('West Garfield Park',),\n",
       " ('South Lawndale',),\n",
       " ('Fuller Park',),\n",
       " ('Riverdale',)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name FROM CENSUS_DATA WHERE per_capita_income<=11000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\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",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>3512276</td>\n",
       "            <td>HK587712</td>\n",
       "            <td>2004-08-28</td>\n",
       "            <td>047XX S KEDZIE AVE</td>\n",
       "            <td>890</td>\n",
       "            <td>THEFT</td>\n",
       "            <td>FROM BUILDING</td>\n",
       "            <td>SMALL RETAIL STORE</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>911</td>\n",
       "            <td>9</td>\n",
       "            <td>14</td>\n",
       "            <td>58</td>\n",
       "            <td>6</td>\n",
       "            <td>1155838</td>\n",
       "            <td>1873050</td>\n",
       "            <td>2004</td>\n",
       "            <td>41.80744050</td>\n",
       "            <td>-87.70395585</td>\n",
       "            <td>(41.8074405, -87.703955849)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3406613</td>\n",
       "            <td>HK456306</td>\n",
       "            <td>2004-06-26</td>\n",
       "            <td>009XX N CENTRAL PARK AVE</td>\n",
       "            <td>820</td>\n",
       "            <td>THEFT</td>\n",
       "            <td>$500 AND UNDER</td>\n",
       "            <td>OTHER</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>1112</td>\n",
       "            <td>11</td>\n",
       "            <td>27</td>\n",
       "            <td>23</td>\n",
       "            <td>6</td>\n",
       "            <td>1152206</td>\n",
       "            <td>1906127</td>\n",
       "            <td>2004</td>\n",
       "            <td>41.89827996</td>\n",
       "            <td>-87.71640551</td>\n",
       "            <td>(41.898279962, -87.716405505)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8002131</td>\n",
       "            <td>HT233595</td>\n",
       "            <td>2011-04-04</td>\n",
       "            <td>043XX S WABASH AVE</td>\n",
       "            <td>820</td>\n",
       "            <td>THEFT</td>\n",
       "            <td>$500 AND UNDER</td>\n",
       "            <td>NURSING HOME/RETIREMENT HOME</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>221</td>\n",
       "            <td>2</td>\n",
       "            <td>3</td>\n",
       "            <td>38</td>\n",
       "            <td>6</td>\n",
       "            <td>1177436</td>\n",
       "            <td>1876313</td>\n",
       "            <td>2011</td>\n",
       "            <td>41.81593313</td>\n",
       "            <td>-87.62464213</td>\n",
       "            <td>(41.815933131, -87.624642127)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7903289</td>\n",
       "            <td>HT133522</td>\n",
       "            <td>2010-12-30</td>\n",
       "            <td>083XX S KINGSTON AVE</td>\n",
       "            <td>840</td>\n",
       "            <td>THEFT</td>\n",
       "            <td>FINANCIAL ID THEFT: OVER $300</td>\n",
       "            <td>RESIDENCE</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>423</td>\n",
       "            <td>4</td>\n",
       "            <td>7</td>\n",
       "            <td>46</td>\n",
       "            <td>6</td>\n",
       "            <td>1194622</td>\n",
       "            <td>1850125</td>\n",
       "            <td>2010</td>\n",
       "            <td>41.74366532</td>\n",
       "            <td>-87.56246276</td>\n",
       "            <td>(41.743665322, -87.562462756)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10402076</td>\n",
       "            <td>HZ138551</td>\n",
       "            <td>2016-02-02</td>\n",
       "            <td>033XX W 66TH ST</td>\n",
       "            <td>820</td>\n",
       "            <td>THEFT</td>\n",
       "            <td>$500 AND UNDER</td>\n",
       "            <td>ALLEY</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>FALSE</td>\n",
       "            <td>831</td>\n",
       "            <td>8</td>\n",
       "            <td>15</td>\n",
       "            <td>66</td>\n",
       "            <td>6</td>\n",
       "            <td>1155240</td>\n",
       "            <td>1860661</td>\n",
       "            <td>2016</td>\n",
       "            <td>41.77345530</td>\n",
       "            <td>-87.70648047</td>\n",
       "            <td>(41.773455295, -87.706480471)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3512276, 'HK587712', datetime.date(2004, 8, 28), '047XX S KEDZIE AVE', '890', 'THEFT', 'FROM BUILDING', 'SMALL RETAIL STORE', 'FALSE', 'FALSE', 911, 9, 14, 58, '6', 1155838, 1873050, 2004, Decimal('41.80744050'), Decimal('-87.70395585'), '(41.8074405, -87.703955849)'),\n",
       " (3406613, 'HK456306', datetime.date(2004, 6, 26), '009XX N CENTRAL PARK AVE', '820', 'THEFT', '$500 AND UNDER', 'OTHER', 'FALSE', 'FALSE', 1112, 11, 27, 23, '6', 1152206, 1906127, 2004, Decimal('41.89827996'), Decimal('-87.71640551'), '(41.898279962, -87.716405505)'),\n",
       " (8002131, 'HT233595', datetime.date(2011, 4, 4), '043XX S WABASH AVE', '820', 'THEFT', '$500 AND UNDER', 'NURSING HOME/RETIREMENT HOME', 'FALSE', 'FALSE', 221, 2, 3, 38, '6', 1177436, 1876313, 2011, Decimal('41.81593313'), Decimal('-87.62464213'), '(41.815933131, -87.624642127)'),\n",
       " (7903289, 'HT133522', datetime.date(2010, 12, 30), '083XX S KINGSTON AVE', '840', 'THEFT', 'FINANCIAL ID THEFT: OVER $300', 'RESIDENCE', 'FALSE', 'FALSE', 423, 4, 7, 46, '6', 1194622, 1850125, 2010, Decimal('41.74366532'), Decimal('-87.56246276'), '(41.743665322, -87.562462756)'),\n",
       " (10402076, 'HZ138551', datetime.date(2016, 2, 2), '033XX W 66TH ST', '820', 'THEFT', '$500 AND UNDER', 'ALLEY', 'FALSE', 'FALSE', 831, 8, 15, 66, '6', 1155240, 1860661, 2016, Decimal('41.77345530'), Decimal('-87.70648047'), '(41.773455295, -87.706480471)')]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHICAGO_CRIME_DATA LIMIT 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>primary_type</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>ARSON</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ASSAULT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BATTERY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BURGLARY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CONCEALED CARRY LICENSE VIOLATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIM SEXUAL ASSAULT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL DAMAGE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL TRESPASS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DECEPTIVE PRACTICE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DOMESTIC VIOLENCE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GAMBLING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HOMICIDE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HUMAN TRAFFICKING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>INTERFERENCE WITH PUBLIC OFFICER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>INTIMIDATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>KIDNAPPING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LIQUOR LAW VIOLATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MOTOR VEHICLE THEFT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NARCOTICS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NON - CRIMINAL</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NON-CRIMINAL</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NON-CRIMINAL (SUBJECT SPECIFIED)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OBSCENITY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OFFENSE INVOLVING CHILDREN</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OTHER NARCOTIC VIOLATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OTHER OFFENSE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PROSTITUTION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PUBLIC INDECENCY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PUBLIC PEACE VIOLATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RITUALISM</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ROBBERY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SEX OFFENSE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>STALKING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>THEFT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEAPONS VIOLATION</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ARSON',),\n",
       " ('ASSAULT',),\n",
       " ('BATTERY',),\n",
       " ('BURGLARY',),\n",
       " ('CONCEALED CARRY LICENSE VIOLATION',),\n",
       " ('CRIM SEXUAL ASSAULT',),\n",
       " ('CRIMINAL DAMAGE',),\n",
       " ('CRIMINAL TRESPASS',),\n",
       " ('DECEPTIVE PRACTICE',),\n",
       " ('DOMESTIC VIOLENCE',),\n",
       " ('GAMBLING',),\n",
       " ('HOMICIDE',),\n",
       " ('HUMAN TRAFFICKING',),\n",
       " ('INTERFERENCE WITH PUBLIC OFFICER',),\n",
       " ('INTIMIDATION',),\n",
       " ('KIDNAPPING',),\n",
       " ('LIQUOR LAW VIOLATION',),\n",
       " ('MOTOR VEHICLE THEFT',),\n",
       " ('NARCOTICS',),\n",
       " ('NON - CRIMINAL',),\n",
       " ('NON-CRIMINAL',),\n",
       " ('NON-CRIMINAL (SUBJECT SPECIFIED)',),\n",
       " ('OBSCENITY',),\n",
       " ('OFFENSE INVOLVING CHILDREN',),\n",
       " ('OTHER NARCOTIC VIOLATION',),\n",
       " ('OTHER OFFENSE',),\n",
       " ('PROSTITUTION',),\n",
       " ('PUBLIC INDECENCY',),\n",
       " ('PUBLIC PEACE VIOLATION',),\n",
       " ('RITUALISM',),\n",
       " ('ROBBERY',),\n",
       " ('SEX OFFENSE',),\n",
       " ('STALKING',),\n",
       " ('THEFT',),\n",
       " ('WEAPONS VIOLATION',)]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " %sql SELECT DISTINCT primary_type FROM CHICAGO_CRIME_DATA;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>location_description</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>AIRPORT/AIRCRAFT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ALLEY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>APARTMENT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ATHLETIC CLUB</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BANK</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BAR OR TAVERN</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHA APARTMENT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHA HALLWAY/STAIRWELL/ELEVATOR</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHA PARKING LOT/GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHURCH/SYNAGOGUE/PLACE OF WORSHIP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CLEANING STORE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>COLLEGE/UNIVERSITY GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>COMMERCIAL / BUSINESS OFFICE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CONSTRUCTION SITE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CONVENIENCE STORE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CTA BUS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CTA PLATFORM</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CTA TRAIN</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DEPARTMENT STORE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DRUG STORE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FACTORY/MANUFACTURING BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GAS STATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GOVERNMENT BUILDING/PROPERTY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GROCERY FOOD STORE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HOSPITAL BUILDING/GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HOUSE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LIBRARY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MOVIE HOUSE/THEATER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NURSING HOME/RETIREMENT HOME</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OTHER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OTHER COMMERCIAL TRANSPORTATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PARKING LOT/GARAGE(NON.RESID.)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>POLICE FACILITY/VEH PARKING LOT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RESIDENCE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RESIDENCE PORCH/HALLWAY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RESIDENCE-GARAGE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RESIDENTIAL YARD (FRONT/BACK)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RESTAURANT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SCHOOL, PRIVATE, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SCHOOL, PUBLIC, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SIDEWALK</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SMALL RETAIL STORE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>STREET</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TAVERN/LIQUOR STORE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>VACANT LOT/LAND</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>VEHICLE NON-COMMERCIAL</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WAREHOUSE</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AIRPORT/AIRCRAFT',),\n",
       " ('ALLEY',),\n",
       " ('APARTMENT',),\n",
       " ('ATHLETIC CLUB',),\n",
       " ('BANK',),\n",
       " ('BAR OR TAVERN',),\n",
       " ('CHA APARTMENT',),\n",
       " ('CHA HALLWAY/STAIRWELL/ELEVATOR',),\n",
       " ('CHA PARKING LOT/GROUNDS',),\n",
       " ('CHURCH/SYNAGOGUE/PLACE OF WORSHIP',),\n",
       " ('CLEANING STORE',),\n",
       " ('COLLEGE/UNIVERSITY GROUNDS',),\n",
       " ('COMMERCIAL / BUSINESS OFFICE',),\n",
       " ('CONSTRUCTION SITE',),\n",
       " ('CONVENIENCE STORE',),\n",
       " ('CTA BUS',),\n",
       " ('CTA PLATFORM',),\n",
       " ('CTA TRAIN',),\n",
       " ('DEPARTMENT STORE',),\n",
       " ('DRUG STORE',),\n",
       " ('FACTORY/MANUFACTURING BUILDING',),\n",
       " ('GAS STATION',),\n",
       " ('GOVERNMENT BUILDING/PROPERTY',),\n",
       " ('GROCERY FOOD STORE',),\n",
       " ('HOSPITAL BUILDING/GROUNDS',),\n",
       " ('HOUSE',),\n",
       " ('LIBRARY',),\n",
       " ('MOVIE HOUSE/THEATER',),\n",
       " ('NURSING HOME/RETIREMENT HOME',),\n",
       " ('OTHER',),\n",
       " ('OTHER COMMERCIAL TRANSPORTATION',),\n",
       " ('PARKING LOT/GARAGE(NON.RESID.)',),\n",
       " ('POLICE FACILITY/VEH PARKING LOT',),\n",
       " ('RESIDENCE',),\n",
       " ('RESIDENCE PORCH/HALLWAY',),\n",
       " ('RESIDENCE-GARAGE',),\n",
       " ('RESIDENTIAL YARD (FRONT/BACK)',),\n",
       " ('RESTAURANT',),\n",
       " ('SCHOOL, PRIVATE, BUILDING',),\n",
       " ('SCHOOL, PUBLIC, BUILDING',),\n",
       " ('SCHOOL, PUBLIC, GROUNDS',),\n",
       " ('SIDEWALK',),\n",
       " ('SMALL RETAIL STORE',),\n",
       " ('STREET',),\n",
       " ('TAVERN/LIQUOR STORE',),\n",
       " ('VACANT LOT/LAND',),\n",
       " ('VEHICLE NON-COMMERCIAL',),\n",
       " ('WAREHOUSE',)]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " %sql SELECT DISTINCT location_description FROM CHICAGO_CRIME_DATA;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>description</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>$500 AND UNDER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGG CRIM SEX ABUSE FAM MEMBER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGG CRIMINAL SEXUAL ABUSE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGG PO HANDS NO/MIN INJURY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGG RIT MUT: HANDS/FIST/FEET SERIOUS INJURY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGG SEX ASSLT OF CHILD FAM MBR</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGG: FINANCIAL ID THEFT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED DOMESTIC BATTERY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED DOMESTIC BATTERY: OTHER DANG WEAPON</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED PO: OTHER DANG WEAP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED VEHICULAR HIJACKING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED: HANDGUN</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED: OTHER DANG WEAPON</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AGGRAVATED:KNIFE/CUTTING INSTR</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ARMED: HANDGUN</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ARMED:KNIFE/CUTTING INSTRUMENT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ATT: AUTOMOBILE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ATTEMPT NON-AGGRAVATED</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ATTEMPT POSSESSION NARCOTICS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ATTEMPT THEFT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AUTOMOBILE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BOGUS CHECK</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BOMB THREAT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BY FIRE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CALL OPERATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHILD ABDUCTION/STRANGER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CREDIT CARD FRAUD</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIM SEX ABUSE BY FAM MEMBER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL DEFACEMENT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CYBERSTALKING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DOMESTIC BATTERY SIMPLE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DOMESTIC VIOLENCE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ESCAPE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FALSE/STOLEN/ALTERED TRP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FINANCIAL ID THEFT: OVER $300</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FINANCIAL IDENTITY THEFT OVER $ 300</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FIRST DEGREE MURDER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FOID - REVOCATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FORCIBLE ENTRY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FORGERY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FOUND SUSPECT NARCOTICS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FRAUD OR CONFIDENCE GAME</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FROM BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GAME/DICE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HARASSMENT BY ELECTRONIC MEANS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HARASSMENT BY TELEPHONE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ILLEGAL CONSUMPTION BY MINOR</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ILLEGAL USE CASH CARD</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>INTIMIDATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>INTOXICATING COMPOUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>INVOLUNTARY SERVITUDE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LICENSED PREMISE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LOST PASSPORT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MANU/DEL:CANNABIS 10GM OR LESS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MANU/DELIVER: HEROIN (WHITE)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MANU/DELIVER: METHAMPHETAMINES</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MANU/DELIVER:CRACK</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NON-AGGRAVATED</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NOTIFICATION OF CIVIL NO CONTACT ORDER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OBSCENE MATTER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OBSCENE TELEPHONE CALLS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OTHER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OTHER CRIME AGAINST PERSON</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OTHER VEHICLE OFFENSE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>OVER $500</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PAROLE VIOLATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>POCKET-PICKING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>POSS: CANNABIS 30GMS OR LESS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>POSS: COCAINE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>POSS: CRACK</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>POSS: HEROIN(WHITE)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRO EMP HANDS NO/MIN INJURY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PROBATION VIOLATION</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PURSE-SNATCHING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RECKLESS CONDUCT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RESIST/OBSTRUCT/DISARM OFFICER</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RETAIL THEFT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SELL/GIVE/DEL LIQUOR TO MINOR</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SEX ASSLT OF CHILD BY FAM MBR</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SEX OFFENDER: FAIL REG NEW ADD</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SIMPLE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SOLICIT FOR BUSINESS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SOLICIT NARCOTICS ON PUBLICWAY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SOLICIT ON PUBLIC WAY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>STRONGARM - NO WEAPON</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TELEPHONE THREAT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>THEFT BY LESSEE,MOTOR VEH</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>THEFT OF LABOR/SERVICES</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>THEFT OF LOST/MISLAID PROP</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>THEFT/RECOVERY: AUTOMOBILE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TO CITY OF CHICAGO PROPERTY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TO LAND</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TO PROPERTY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TO RESIDENCE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TO STATE SUP LAND</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TO VEHICLE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TRUCK, BUS, MOTOR HOME</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UNLAWFUL ENTRY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UNLAWFUL POSS OF HANDGUN</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UNLAWFUL POSS OTHER FIREARM</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>VEHICULAR HIJACKING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>VIOLATE ORDER OF PROTECTION</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('$500 AND UNDER',),\n",
       " ('AGG CRIM SEX ABUSE FAM MEMBER',),\n",
       " ('AGG CRIMINAL SEXUAL ABUSE',),\n",
       " ('AGG PO HANDS NO/MIN INJURY',),\n",
       " ('AGG RIT MUT: HANDS/FIST/FEET SERIOUS INJURY',),\n",
       " ('AGG SEX ASSLT OF CHILD FAM MBR',),\n",
       " ('AGG: FINANCIAL ID THEFT',),\n",
       " ('AGGRAVATED',),\n",
       " ('AGGRAVATED DOMESTIC BATTERY',),\n",
       " ('AGGRAVATED DOMESTIC BATTERY: OTHER DANG WEAPON',),\n",
       " ('AGGRAVATED PO: OTHER DANG WEAP',),\n",
       " ('AGGRAVATED VEHICULAR HIJACKING',),\n",
       " ('AGGRAVATED: HANDGUN',),\n",
       " ('AGGRAVATED: OTHER DANG WEAPON',),\n",
       " ('AGGRAVATED:KNIFE/CUTTING INSTR',),\n",
       " ('ARMED: HANDGUN',),\n",
       " ('ARMED:KNIFE/CUTTING INSTRUMENT',),\n",
       " ('ATT: AUTOMOBILE',),\n",
       " ('ATTEMPT NON-AGGRAVATED',),\n",
       " ('ATTEMPT POSSESSION NARCOTICS',),\n",
       " ('ATTEMPT THEFT',),\n",
       " ('AUTOMOBILE',),\n",
       " ('BOGUS CHECK',),\n",
       " ('BOMB THREAT',),\n",
       " ('BY FIRE',),\n",
       " ('CALL OPERATION',),\n",
       " ('CHILD ABDUCTION/STRANGER',),\n",
       " ('CREDIT CARD FRAUD',),\n",
       " ('CRIM SEX ABUSE BY FAM MEMBER',),\n",
       " ('CRIMINAL DEFACEMENT',),\n",
       " ('CYBERSTALKING',),\n",
       " ('DOMESTIC BATTERY SIMPLE',),\n",
       " ('DOMESTIC VIOLENCE',),\n",
       " ('ESCAPE',),\n",
       " ('FALSE/STOLEN/ALTERED TRP',),\n",
       " ('FINANCIAL ID THEFT: OVER $300',),\n",
       " ('FINANCIAL IDENTITY THEFT OVER $ 300',),\n",
       " ('FIRST DEGREE MURDER',),\n",
       " ('FOID - REVOCATION',),\n",
       " ('FORCIBLE ENTRY',),\n",
       " ('FORGERY',),\n",
       " ('FOUND SUSPECT NARCOTICS',),\n",
       " ('FRAUD OR CONFIDENCE GAME',),\n",
       " ('FROM BUILDING',),\n",
       " ('GAME/DICE',),\n",
       " ('HARASSMENT BY ELECTRONIC MEANS',),\n",
       " ('HARASSMENT BY TELEPHONE',),\n",
       " ('ILLEGAL CONSUMPTION BY MINOR',),\n",
       " ('ILLEGAL USE CASH CARD',),\n",
       " ('INTIMIDATION',),\n",
       " ('INTOXICATING COMPOUNDS',),\n",
       " ('INVOLUNTARY SERVITUDE',),\n",
       " ('LICENSED PREMISE',),\n",
       " ('LOST PASSPORT',),\n",
       " ('MANU/DEL:CANNABIS 10GM OR LESS',),\n",
       " ('MANU/DELIVER: HEROIN (WHITE)',),\n",
       " ('MANU/DELIVER: METHAMPHETAMINES',),\n",
       " ('MANU/DELIVER:CRACK',),\n",
       " ('NON-AGGRAVATED',),\n",
       " ('NOTIFICATION OF CIVIL NO CONTACT ORDER',),\n",
       " ('OBSCENE MATTER',),\n",
       " ('OBSCENE TELEPHONE CALLS',),\n",
       " ('OTHER',),\n",
       " ('OTHER CRIME AGAINST PERSON',),\n",
       " ('OTHER VEHICLE OFFENSE',),\n",
       " ('OVER $500',),\n",
       " ('PAROLE VIOLATION',),\n",
       " ('POCKET-PICKING',),\n",
       " ('POSS: CANNABIS 30GMS OR LESS',),\n",
       " ('POSS: COCAINE',),\n",
       " ('POSS: CRACK',),\n",
       " ('POSS: HEROIN(WHITE)',),\n",
       " ('PRO EMP HANDS NO/MIN INJURY',),\n",
       " ('PROBATION VIOLATION',),\n",
       " ('PURSE-SNATCHING',),\n",
       " ('RECKLESS CONDUCT',),\n",
       " ('RESIST/OBSTRUCT/DISARM OFFICER',),\n",
       " ('RETAIL THEFT',),\n",
       " ('SELL/GIVE/DEL LIQUOR TO MINOR',),\n",
       " ('SEX ASSLT OF CHILD BY FAM MBR',),\n",
       " ('SEX OFFENDER: FAIL REG NEW ADD',),\n",
       " ('SIMPLE',),\n",
       " ('SOLICIT FOR BUSINESS',),\n",
       " ('SOLICIT NARCOTICS ON PUBLICWAY',),\n",
       " ('SOLICIT ON PUBLIC WAY',),\n",
       " ('STRONGARM - NO WEAPON',),\n",
       " ('TELEPHONE THREAT',),\n",
       " ('THEFT BY LESSEE,MOTOR VEH',),\n",
       " ('THEFT OF LABOR/SERVICES',),\n",
       " ('THEFT OF LOST/MISLAID PROP',),\n",
       " ('THEFT/RECOVERY: AUTOMOBILE',),\n",
       " ('TO CITY OF CHICAGO PROPERTY',),\n",
       " ('TO LAND',),\n",
       " ('TO PROPERTY',),\n",
       " ('TO RESIDENCE',),\n",
       " ('TO STATE SUP LAND',),\n",
       " ('TO VEHICLE',),\n",
       " ('TRUCK, BUS, MOTOR HOME',),\n",
       " ('UNLAWFUL ENTRY',),\n",
       " ('UNLAWFUL POSS OF HANDGUN',),\n",
       " ('UNLAWFUL POSS OTHER FIREARM',),\n",
       " ('VEHICULAR HIJACKING',),\n",
       " ('VIOLATE ORDER OF PROTECTION',)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT description FROM CHICAGO_CRIME_DATA;"
   ]
  },
  {
   "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",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>case_number</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>HL266884</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HK238408</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HL266884',), ('HK238408',)]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT case_number FROM CHICAGO_CRIME_DATA WHERE description LIKE '%MINOR%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT COUNT(column_name)\n",
    "FROM table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 4\n",
    "\n",
    "##### List all kidnapping crimes involving a child?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>case_number</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>HN144152</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HN144152',)]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT case_number FROM CHICAGO_CRIME_DATA WHERE primary_type='KIDNAPPING' AND description LIKE '%C%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 5\n",
    "\n",
    "##### What kinds of crimes were recorded at schools?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>primary_type</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>ASSAULT</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BATTERY</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL DAMAGE</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL TRESPASS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NARCOTICS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PUBLIC PEACE VIOLATION</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ASSAULT',),\n",
       " ('BATTERY',),\n",
       " ('CRIMINAL DAMAGE',),\n",
       " ('CRIMINAL TRESPASS',),\n",
       " ('NARCOTICS',),\n",
       " ('PUBLIC PEACE VIOLATION',)]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT primary_type FROM CHICAGO_CRIME_DATA WHERE location_description LIKE '%SCHOOL%';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>school_id</th>\n",
       "            <th>name_of_school</th>\n",
       "            <th>elementary__middle__or_high_school</th>\n",
       "            <th>street_address</th>\n",
       "            <th>city</th>\n",
       "            <th>state</th>\n",
       "            <th>zip_code</th>\n",
       "            <th>phone_number</th>\n",
       "            <th>link</th>\n",
       "            <th>network_manager</th>\n",
       "            <th>collaborative_name</th>\n",
       "            <th>adequate_yearly_progress_made_</th>\n",
       "            <th>track_schedule</th>\n",
       "            <th>cps_performance_policy_status</th>\n",
       "            <th>cps_performance_policy_level</th>\n",
       "            <th>healthy_school_certified</th>\n",
       "            <th>safety_icon</th>\n",
       "            <th>safety_score</th>\n",
       "            <th>family_involvement_icon</th>\n",
       "            <th>family_involvement_score</th>\n",
       "            <th>environment_icon</th>\n",
       "            <th>environment_score</th>\n",
       "            <th>instruction_icon</th>\n",
       "            <th>instruction_score</th>\n",
       "            <th>leaders_icon</th>\n",
       "            <th>leaders_score</th>\n",
       "            <th>teachers_icon</th>\n",
       "            <th>teachers_score</th>\n",
       "            <th>parent_engagement_icon</th>\n",
       "            <th>parent_engagement_score</th>\n",
       "            <th>parent_environment_icon</th>\n",
       "            <th>parent_environment_score</th>\n",
       "            <th>average_student_attendance</th>\n",
       "            <th>rate_of_misconducts__per_100_students_</th>\n",
       "            <th>average_teacher_attendance</th>\n",
       "            <th>individualized_education_program_compliance_rate</th>\n",
       "            <th>pk_2_literacy__</th>\n",
       "            <th>pk_2_math__</th>\n",
       "            <th>gr3_5_grade_level_math__</th>\n",
       "            <th>gr3_5_grade_level_read__</th>\n",
       "            <th>gr3_5_keep_pace_read__</th>\n",
       "            <th>gr3_5_keep_pace_math__</th>\n",
       "            <th>gr6_8_grade_level_math__</th>\n",
       "            <th>gr6_8_grade_level_read__</th>\n",
       "            <th>gr6_8_keep_pace_math_</th>\n",
       "            <th>gr6_8_keep_pace_read__</th>\n",
       "            <th>gr_8_explore_math__</th>\n",
       "            <th>gr_8_explore_read__</th>\n",
       "            <th>isat_exceeding_math__</th>\n",
       "            <th>isat_exceeding_reading__</th>\n",
       "            <th>isat_value_add_math</th>\n",
       "            <th>isat_value_add_read</th>\n",
       "            <th>isat_value_add_color_math</th>\n",
       "            <th>isat_value_add_color_read</th>\n",
       "            <th>students_taking__algebra__</th>\n",
       "            <th>students_passing__algebra__</th>\n",
       "            <th>9th Grade EXPLORE (2009)</th>\n",
       "            <th>9th Grade EXPLORE (2010)</th>\n",
       "            <th>10th Grade PLAN (2009)</th>\n",
       "            <th>10th Grade PLAN (2010)</th>\n",
       "            <th>net_change_explore_and_plan</th>\n",
       "            <th>11th Grade Average ACT (2011)</th>\n",
       "            <th>net_change_plan_and_act</th>\n",
       "            <th>college_eligibility__</th>\n",
       "            <th>graduation_rate__</th>\n",
       "            <th>college_enrollment_rate__</th>\n",
       "            <th>college_enrollment</th>\n",
       "            <th>general_services_route</th>\n",
       "            <th>freshman_on_track_rate__</th>\n",
       "            <th>x_coordinate</th>\n",
       "            <th>y_coordinate</th>\n",
       "            <th>latitude</th>\n",
       "            <th>longitude</th>\n",
       "            <th>community_area_number</th>\n",
       "            <th>community_area_name</th>\n",
       "            <th>ward</th>\n",
       "            <th>police_district</th>\n",
       "            <th>location</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>610038</td>\n",
       "            <td>Abraham Lincoln Elementary School</td>\n",
       "            <td>ES</td>\n",
       "            <td>615 W Kemper Pl</td>\n",
       "            <td>Chicago</td>\n",
       "            <td>IL</td>\n",
       "            <td>60614</td>\n",
       "            <td>(773) 534-5720</td>\n",
       "            <td>http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610038.pdf</td>\n",
       "            <td>Fullerton Elementary Network</td>\n",
       "            <td>NORTH-NORTHWEST SIDE COLLABORATIVE</td>\n",
       "            <td>No</td>\n",
       "            <td>Standard</td>\n",
       "            <td>Not on Probation</td>\n",
       "            <td>Level 1</td>\n",
       "            <td>Yes</td>\n",
       "            <td>Very Strong</td>\n",
       "            <td>99</td>\n",
       "            <td>Very Strong</td>\n",
       "            <td>99</td>\n",
       "            <td>Strong</td>\n",
       "            <td>74</td>\n",
       "            <td>Strong</td>\n",
       "            <td>66</td>\n",
       "            <td>Weak</td>\n",
       "            <td>65</td>\n",
       "            <td>Strong</td>\n",
       "            <td>70</td>\n",
       "            <td>Strong</td>\n",
       "            <td>56</td>\n",
       "            <td>Average</td>\n",
       "            <td>47</td>\n",
       "            <td>96.00%</td>\n",
       "            <td>2.0</td>\n",
       "            <td>96.40%</td>\n",
       "            <td>95.80%</td>\n",
       "            <td>80.1</td>\n",
       "            <td>43.3</td>\n",
       "            <td>89.6</td>\n",
       "            <td>84.9</td>\n",
       "            <td>60.7</td>\n",
       "            <td>62.6</td>\n",
       "            <td>81.9</td>\n",
       "            <td>85.2</td>\n",
       "            <td>52</td>\n",
       "            <td>62.4</td>\n",
       "            <td>66.3</td>\n",
       "            <td>77.9</td>\n",
       "            <td>69.7</td>\n",
       "            <td>64.4</td>\n",
       "            <td>0.2</td>\n",
       "            <td>0.9</td>\n",
       "            <td>Yellow</td>\n",
       "            <td>Green</td>\n",
       "            <td>67.1</td>\n",
       "            <td>54.5</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>813</td>\n",
       "            <td>33</td>\n",
       "            <td>NDA</td>\n",
       "            <td>1171699.458</td>\n",
       "            <td>1915829.428</td>\n",
       "            <td>41.92449696</td>\n",
       "            <td>-87.64452163</td>\n",
       "            <td>7</td>\n",
       "            <td>LINCOLN PARK</td>\n",
       "            <td>43</td>\n",
       "            <td>18</td>\n",
       "            <td>(41.92449696, -87.64452163)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>610281</td>\n",
       "            <td>Adam Clayton Powell Paideia Community Academy Elementary School</td>\n",
       "            <td>ES</td>\n",
       "            <td>7511 S South Shore Dr</td>\n",
       "            <td>Chicago</td>\n",
       "            <td>IL</td>\n",
       "            <td>60649</td>\n",
       "            <td>(773) 535-6650</td>\n",
       "            <td>http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610281.pdf</td>\n",
       "            <td>Skyway Elementary Network</td>\n",
       "            <td>SOUTH SIDE COLLABORATIVE</td>\n",
       "            <td>No</td>\n",
       "            <td>Track_E</td>\n",
       "            <td>Not on Probation</td>\n",
       "            <td>Level 1</td>\n",
       "            <td>No</td>\n",
       "            <td>Average</td>\n",
       "            <td>54</td>\n",
       "            <td>Strong</td>\n",
       "            <td>66</td>\n",
       "            <td>Strong</td>\n",
       "            <td>74</td>\n",
       "            <td>Very Strong</td>\n",
       "            <td>84</td>\n",
       "            <td>Weak</td>\n",
       "            <td>63</td>\n",
       "            <td>Strong</td>\n",
       "            <td>76</td>\n",
       "            <td>Weak</td>\n",
       "            <td>46</td>\n",
       "            <td>Average</td>\n",
       "            <td>50</td>\n",
       "            <td>95.60%</td>\n",
       "            <td>15.7</td>\n",
       "            <td>95.30%</td>\n",
       "            <td>100.00%</td>\n",
       "            <td>62.4</td>\n",
       "            <td>51.7</td>\n",
       "            <td>21.9</td>\n",
       "            <td>15.1</td>\n",
       "            <td>29</td>\n",
       "            <td>42.8</td>\n",
       "            <td>38.5</td>\n",
       "            <td>27.4</td>\n",
       "            <td>44.8</td>\n",
       "            <td>42.7</td>\n",
       "            <td>14.1</td>\n",
       "            <td>34.4</td>\n",
       "            <td>16.8</td>\n",
       "            <td>16.5</td>\n",
       "            <td>0.7</td>\n",
       "            <td>1.4</td>\n",
       "            <td>Green</td>\n",
       "            <td>Green</td>\n",
       "            <td>17.2</td>\n",
       "            <td>27.3</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>521</td>\n",
       "            <td>46</td>\n",
       "            <td>NDA</td>\n",
       "            <td>1196129.985</td>\n",
       "            <td>1856209.466</td>\n",
       "            <td>41.76032435</td>\n",
       "            <td>-87.55673627</td>\n",
       "            <td>43</td>\n",
       "            <td>SOUTH SHORE</td>\n",
       "            <td>7</td>\n",
       "            <td>4</td>\n",
       "            <td>(41.76032435, -87.55673627)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(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, 'Very Strong', '99', 'Strong', 74, 'Strong', 66, 'Weak', '65', 'Strong', '70', 'Strong', '56', 'Average', '47', '96.00%', Decimal('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', Decimal('69.7'), Decimal('64.4'), Decimal('0.2'), Decimal('0.9'), 'Yellow', 'Green', '67.1', '54.5', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 813, 33, 'NDA', Decimal('1171699.458'), Decimal('1915829.428'), Decimal('41.92449696'), Decimal('-87.64452163'), 7, 'LINCOLN PARK', 43, 18, '(41.92449696, -87.64452163)'),\n",
       " (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, 'Strong', '66', 'Strong', 74, 'Very Strong', 84, 'Weak', '63', 'Strong', '76', 'Weak', '46', 'Average', '50', '95.60%', Decimal('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', Decimal('16.8'), Decimal('16.5'), Decimal('0.7'), Decimal('1.4'), 'Green', 'Green', '17.2', '27.3', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 521, 46, 'NDA', Decimal('1196129.985'), Decimal('1856209.466'), Decimal('41.76032435'), Decimal('-87.55673627'), 43, 'SOUTH SHORE', 7, 4, '(41.76032435, -87.55673627)')]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHICAGO_PUBLIC_SCHOOLS LIMIT 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 6\n",
    "\n",
    "##### List the average safety score for each type of school.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>elementary__middle__or_high_school</th>\n",
       "            <th>2</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ES', 49), ('HS', 49), ('MS', 48)]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT elementary__middle__or_high_school, ROUND(AVG(safety_score),3) FROM CHICAGO_PUBLIC_SCHOOLS GROUP BY elementary__middle__or_high_school"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_number</th>\n",
       "            <th>community_area_name</th>\n",
       "            <th>percent_of_housing_crowded</th>\n",
       "            <th>percent_households_below_poverty</th>\n",
       "            <th>percent_aged_16__unemployed</th>\n",
       "            <th>percent_aged_25__without_high_school_diploma</th>\n",
       "            <th>percent_aged_under_18_or_over_64</th>\n",
       "            <th>per_capita_income</th>\n",
       "            <th>hardship_index</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>Rogers Park</td>\n",
       "            <td>7.7</td>\n",
       "            <td>23.6</td>\n",
       "            <td>8.7</td>\n",
       "            <td>18.2</td>\n",
       "            <td>27.5</td>\n",
       "            <td>23939</td>\n",
       "            <td>39</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>West Ridge</td>\n",
       "            <td>7.8</td>\n",
       "            <td>17.2</td>\n",
       "            <td>8.8</td>\n",
       "            <td>20.8</td>\n",
       "            <td>38.5</td>\n",
       "            <td>23040</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rogers Park', Decimal('7.7'), Decimal('23.6'), Decimal('8.7'), Decimal('18.2'), Decimal('27.5'), 23939, 39),\n",
       " (2, 'West Ridge', Decimal('7.8'), Decimal('17.2'), Decimal('8.8'), Decimal('20.8'), Decimal('38.5'), 23040, 46)]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CENSUS_DATA LIMIT 2"
   ]
  },
  {
   "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": 97,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_name</th>\n",
       "            <th>percent_households_below_poverty</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name, percent_households_below_poverty FROM CENSUS_DATA ORDER BY percent_households_below_poverty DESC LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Problem 8\n",
    "\n",
    "##### Which community area is most crime prone?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_number</th>\n",
       "            <th>crime_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>25</td>\n",
       "            <td>43</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(25, 43)]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_number, COUNT(*) as crime_count FROM CHICAGO_CRIME_DATA GROUP BY community_area_number ORDER BY crime_count DESC LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for a hint\n",
    "\n",
    "<!--\n",
    "Query 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": 82,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_name</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Riverdale</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Riverdale',)]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name FROM CENSUS_DATA WHERE hardship_index = (SELECT MAX(hardship_index) FROM CENSUS_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": 85,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://qmp97616:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_name</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Austin</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Austin',)]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name 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);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright © 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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork22-2022-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\"> © IBM Corporation 2020. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}