notebook.ipynb

{
 "cells": [
  {
   "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": {},
   "outputs": [],
   "source": [
    "# !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": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the next cell enter your db2 connection string. 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": {},
   "outputs": [],
   "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://wqw42429:bUzhTdorgRS7cOtV@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB?security=SSL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Problems\n",
    "\n",
    "Now write and execute SQL queries to answer problems\n",
    "\n",
    "### Problem 1\n",
    "\n",
    "##### Find the total number of crimes recorded in the CRIME table.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>533</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(533,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT COUNT(ID) FROM CHICAGO_CRIME_DATA "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Problem 2\n",
    "\n",
    "##### List community areas with per capita income less than 11000.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\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",
       "</table>"
      ],
      "text/plain": [
       "[('West Garfield Park',),\n",
       " ('South Lawndale',),\n",
       " ('Fuller Park',),\n",
       " ('Riverdale',)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA\n",
    "WHERE PER_CAPITA_INCOME < 11000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 3\n",
    "\n",
    "##### List all case numbers for crimes  involving minors?(children are not considered minors for the purposes of crime analysis)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>case_number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HL266884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HK238408</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HL266884',), ('HK238408',)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT CASE_NUMBER FROM CHICAGO_CRIME_DATA\n",
    "WHERE DESCRIPTION LIKE '%MINOR'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 4\n",
    "\n",
    "##### List all kidnapping crimes involving a child?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>case_number</th>\n",
       "        <th>DATE</th>\n",
       "        <th>block</th>\n",
       "        <th>iucr</th>\n",
       "        <th>primary_type</th>\n",
       "        <th>description</th>\n",
       "        <th>location_description</th>\n",
       "        <th>arrest</th>\n",
       "        <th>domestic</th>\n",
       "        <th>beat</th>\n",
       "        <th>district</th>\n",
       "        <th>ward</th>\n",
       "        <th>community_area_number</th>\n",
       "        <th>fbicode</th>\n",
       "        <th>x_coordinate</th>\n",
       "        <th>y_coordinate</th>\n",
       "        <th>YEAR</th>\n",
       "        <th>latitude</th>\n",
       "        <th>longitude</th>\n",
       "        <th>location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5276766</td>\n",
       "        <td>HN144152</td>\n",
       "        <td>2007-01-26</td>\n",
       "        <td>050XX W VAN BUREN ST</td>\n",
       "        <td>1792</td>\n",
       "        <td>KIDNAPPING</td>\n",
       "        <td>CHILD ABDUCTION/STRANGER</td>\n",
       "        <td>STREET</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1533</td>\n",
       "        <td>15</td>\n",
       "        <td>29</td>\n",
       "        <td>25</td>\n",
       "        <td>20</td>\n",
       "        <td>1143050</td>\n",
       "        <td>1897546</td>\n",
       "        <td>2007</td>\n",
       "        <td>41.87490841</td>\n",
       "        <td>-87.75024931</td>\n",
       "        <td>(41.874908413, -87.750249307)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5276766, 'HN144152', datetime.date(2007, 1, 26), '050XX W VAN BUREN ST', '1792', 'KIDNAPPING', 'CHILD ABDUCTION/STRANGER', 'STREET', 'FALSE', 'FALSE', 1533, 15, 29, 25, '20', 1143050, 1897546, 2007, Decimal('41.87490841'), Decimal('-87.75024931'), '(41.874908413, -87.750249307)')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM CHICAGO_CRIME_DATA\n",
    "WHERE PRIMARY_TYPE = 'KIDNAPPING'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 5\n",
    "\n",
    "##### What kinds of crimes were recorded at schools?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>primary_type</th>\n",
       "    </tr>\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 TRESPA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NARCOTICS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PUBLIC PEACE VI</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ASSAULT',),\n",
       " ('BATTERY',),\n",
       " ('CRIMINAL DAMAGE',),\n",
       " ('CRIMINAL TRESPA',),\n",
       " ('NARCOTICS',),\n",
       " ('PUBLIC PEACE VI',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT(PRIMARY_TYPE) FROM CHICAGO_CRIME_DATA\n",
    "WHERE LOCATION_DESCRIPTION LIKE 'SCHOOL%'"
   ]
  },
  {
   "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": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Elementary, Middle, or High School</th>\n",
       "        <th>2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ES</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HS</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MS</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ES', 49), ('HS', 49), ('MS', 48)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Elementary, Middle, or High School\", \n",
    "AVG(SAFETY_SCORE) FROM CHICAGO_PUBLIC_SCHOOLS\n",
    "GROUP BY \"Elementary, Middle, or High School\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 7\n",
    "\n",
    "##### List 5 community areas with highest % of households below poverty line\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Riverdale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fuller Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Englewood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North Lawndale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>East Garfield Park</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Riverdale',),\n",
       " ('Fuller Park',),\n",
       " ('Englewood',),\n",
       " ('North Lawndale',),\n",
       " ('East Garfield Park',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA\n",
    "ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 8\n",
    "\n",
    "##### Which community area is most crime prone?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(25,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COMMUNITY_AREA_NUMBER\n",
    "FROM   CHICAGO_CRIME_DATA\n",
    "GROUP  BY COMMUNITY_AREA_NUMBER\n",
    "ORDER  BY 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": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Riverdale</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Riverdale',)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA\n",
    "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": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wqw42429:***@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austin</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Austin',)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA\n",
    "WHERE COMMUNITY_AREA_NUMBER IN \n",
    "(SELECT COMMUNITY_AREA_NUMBER\n",
    "FROM   CHICAGO_CRIME_DATA\n",
    "GROUP  BY COMMUNITY_AREA_NUMBER\n",
    "ORDER  BY COUNT(*) DESC LIMIT 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ***THE END!!!***"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}