Final_project.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 Graded Assessment</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 SQLIte 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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-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-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-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",
    "\n",
    "[https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2](https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&cm_mmc=Email_Newsletter-\\_-Developer_Ed%2BTech-\\_-WW_WW-\\_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ)\n",
    "\n",
    "### 2. Chicago Public Schools\n",
    "\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",
    "\n",
    "[https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t](https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&cm_mmc=Email_Newsletter-\\_-Developer_Ed%2BTech-\\_-WW_WW-\\_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ)\n",
    "\n",
    "### 3. Chicago Crime Data\n",
    "\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",
    "\n",
    "[https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2](https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&cm_mmc=Email_Newsletter-\\_-Developer_Ed%2BTech-\\_-WW_WW-\\_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "\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. \n",
    "\n",
    "Use the links below to read the data files using the Pandas library. \n",
    "\n",
    "* Chicago Census Data\n",
    "\n",
    "https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCensusData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\n",
    "\n",
    "* Chicago Public Schools\n",
    "\n",
    "https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoPublicSchools.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\n",
    "\n",
    "* Chicago Crime Data\n",
    "\n",
    "https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCrimeData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\n",
    "\n",
    "**NOTE:** Ensure you use the datasets available on 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 loaded into SQLite DB.\n",
    "We will create three tables in as under:\n",
    "\n",
    "1.  **CENSUS_DATA**\n",
    "2.  **CHICAGO_PUBLIC_SCHOOLS**\n",
    "3.  **CHICAGO_CRIME_DATA**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the `pandas` and `sqlite3` libraries and establish a connection to `FinalDB.db`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas, sqlite3, csv\n",
    "\n",
    "con = sqlite3.connect(\"FinalDB.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the SQL magic module\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @FinalDB.db'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "\n",
    "%sql sqlite:///FinalDB.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `Pandas` to load the data available in the links above to dataframes. Use these dataframes to load data on to the database `FinalDB.db` as required tables.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jupyterlab/conda/envs/python/lib/python3.7/site-packages/pandas/core/generic.py:2882: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  method=method,\n"
     ]
    }
   ],
   "source": [
    "df = pandas.read_csv('https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCensusData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01')\n",
    "df.to_sql('CENSUS_DATA', con, if_exists = 'replace', index=False, method='multi')\n",
    "\n",
    "df = pandas.read_csv('https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoPublicSchools.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01')\n",
    "df.to_sql('CHICAGO_PUBLIC_SCHOOLS_DATA', con, if_exists = 'replace', index=False, method='multi')\n",
    "\n",
    "df = pandas.read_csv('https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCrimeData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01')\n",
    "df.to_sql('CHICAGO_CRIME_DATA', con, if_exists = 'replace', index=False, method='multi')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Establish a connection between SQL magic module and the database `FinalDB.db`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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.0</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.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2.0</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.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3.0</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.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4.0</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.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5.0</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.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1.0, 'Rogers Park', 7.7, 23.6, 8.7, 18.2, 27.5, 23939, 39.0),\n",
       " (2.0, 'West Ridge', 7.8, 17.2, 8.8, 20.8, 38.5, 23040, 46.0),\n",
       " (3.0, 'Uptown', 3.8, 24.0, 8.9, 11.8, 22.2, 35787, 20.0),\n",
       " (4.0, 'Lincoln Square', 3.4, 10.9, 8.2, 13.4, 25.5, 37524, 17.0),\n",
       " (5.0, 'North Center', 0.3, 7.5, 5.2, 4.5, 26.2, 57123, 6.0)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CENSUS_DATA LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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.0</td>\n",
       "            <td>Very Strong</td>\n",
       "            <td>99</td>\n",
       "            <td>Strong</td>\n",
       "            <td>74.0</td>\n",
       "            <td>Strong</td>\n",
       "            <td>66.0</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.0</td>\n",
       "            <td>Strong</td>\n",
       "            <td>66</td>\n",
       "            <td>Strong</td>\n",
       "            <td>74.0</td>\n",
       "            <td>Very Strong</td>\n",
       "            <td>84.0</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",
       "        <tr>\n",
       "            <td>610185</td>\n",
       "            <td>Adlai E Stevenson Elementary School</td>\n",
       "            <td>ES</td>\n",
       "            <td>8010 S Kostner Ave</td>\n",
       "            <td>Chicago</td>\n",
       "            <td>IL</td>\n",
       "            <td>60652</td>\n",
       "            <td>(773) 535-2280</td>\n",
       "            <td>http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610185.pdf</td>\n",
       "            <td>Midway Elementary Network</td>\n",
       "            <td>SOUTHWEST SIDE COLLABORATIVE</td>\n",
       "            <td>No</td>\n",
       "            <td>Standard</td>\n",
       "            <td>Not on Probation</td>\n",
       "            <td>Level 2</td>\n",
       "            <td>No</td>\n",
       "            <td>Strong</td>\n",
       "            <td>61.0</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>Average</td>\n",
       "            <td>50.0</td>\n",
       "            <td>Weak</td>\n",
       "            <td>36.0</td>\n",
       "            <td>Weak</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>Average</td>\n",
       "            <td>47</td>\n",
       "            <td>Weak</td>\n",
       "            <td>41</td>\n",
       "            <td>95.70%</td>\n",
       "            <td>2.3</td>\n",
       "            <td>94.70%</td>\n",
       "            <td>98.30%</td>\n",
       "            <td>53.7</td>\n",
       "            <td>26.6</td>\n",
       "            <td>38.3</td>\n",
       "            <td>34.7</td>\n",
       "            <td>43.7</td>\n",
       "            <td>57.3</td>\n",
       "            <td>48.8</td>\n",
       "            <td>39.2</td>\n",
       "            <td>46.8</td>\n",
       "            <td>44</td>\n",
       "            <td>7.5</td>\n",
       "            <td>21.9</td>\n",
       "            <td>18.3</td>\n",
       "            <td>15.5</td>\n",
       "            <td>-0.9</td>\n",
       "            <td>-1.0</td>\n",
       "            <td>Red</td>\n",
       "            <td>Red</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>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>1324</td>\n",
       "            <td>44</td>\n",
       "            <td>NDA</td>\n",
       "            <td>1148427.165</td>\n",
       "            <td>1851012.215</td>\n",
       "            <td>41.74711093</td>\n",
       "            <td>-87.73170248</td>\n",
       "            <td>70</td>\n",
       "            <td>ASHBURN</td>\n",
       "            <td>13</td>\n",
       "            <td>8</td>\n",
       "            <td>(41.74711093, -87.73170248)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>609993</td>\n",
       "            <td>Agustin Lara Elementary Academy</td>\n",
       "            <td>ES</td>\n",
       "            <td>4619 S Wolcott Ave</td>\n",
       "            <td>Chicago</td>\n",
       "            <td>IL</td>\n",
       "            <td>60609</td>\n",
       "            <td>(773) 535-4389</td>\n",
       "            <td>http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_609993.pdf</td>\n",
       "            <td>Pershing Elementary Network</td>\n",
       "            <td>SOUTHWEST 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>56.0</td>\n",
       "            <td>Average</td>\n",
       "            <td>44</td>\n",
       "            <td>Average</td>\n",
       "            <td>45.0</td>\n",
       "            <td>Weak</td>\n",
       "            <td>37.0</td>\n",
       "            <td>Weak</td>\n",
       "            <td>65</td>\n",
       "            <td>Average</td>\n",
       "            <td>48</td>\n",
       "            <td>Average</td>\n",
       "            <td>53</td>\n",
       "            <td>Strong</td>\n",
       "            <td>58</td>\n",
       "            <td>95.50%</td>\n",
       "            <td>10.4</td>\n",
       "            <td>95.80%</td>\n",
       "            <td>100.00%</td>\n",
       "            <td>76.9</td>\n",
       "            <td>NDA</td>\n",
       "            <td>26</td>\n",
       "            <td>24.7</td>\n",
       "            <td>61.8</td>\n",
       "            <td>49.7</td>\n",
       "            <td>39.2</td>\n",
       "            <td>27.2</td>\n",
       "            <td>69.7</td>\n",
       "            <td>60.6</td>\n",
       "            <td>9.1</td>\n",
       "            <td>18.2</td>\n",
       "            <td>11.1</td>\n",
       "            <td>9.6</td>\n",
       "            <td>0.9</td>\n",
       "            <td>2.4</td>\n",
       "            <td>Green</td>\n",
       "            <td>Green</td>\n",
       "            <td>42.9</td>\n",
       "            <td>25</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>556</td>\n",
       "            <td>42</td>\n",
       "            <td>NDA</td>\n",
       "            <td>1164504.29</td>\n",
       "            <td>1873959.199</td>\n",
       "            <td>41.8097569</td>\n",
       "            <td>-87.6721446</td>\n",
       "            <td>61</td>\n",
       "            <td>NEW CITY</td>\n",
       "            <td>20</td>\n",
       "            <td>9</td>\n",
       "            <td>(41.8097569, -87.6721446)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>610513</td>\n",
       "            <td>Air Force Academy High School</td>\n",
       "            <td>HS</td>\n",
       "            <td>3630 S Wells St</td>\n",
       "            <td>Chicago</td>\n",
       "            <td>IL</td>\n",
       "            <td>60609</td>\n",
       "            <td>(773) 535-1590</td>\n",
       "            <td>http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610513.pdf</td>\n",
       "            <td>Southwest Side High School Network</td>\n",
       "            <td>SOUTHWEST SIDE COLLABORATIVE</td>\n",
       "            <td>NDA</td>\n",
       "            <td>Standard</td>\n",
       "            <td>Not on Probation</td>\n",
       "            <td>Not Enough Data</td>\n",
       "            <td>Yes</td>\n",
       "            <td>Average</td>\n",
       "            <td>49.0</td>\n",
       "            <td>Strong</td>\n",
       "            <td>60</td>\n",
       "            <td>Strong</td>\n",
       "            <td>60.0</td>\n",
       "            <td>Average</td>\n",
       "            <td>55.0</td>\n",
       "            <td>Weak</td>\n",
       "            <td>45</td>\n",
       "            <td>Average</td>\n",
       "            <td>54</td>\n",
       "            <td>Average</td>\n",
       "            <td>53</td>\n",
       "            <td>Average</td>\n",
       "            <td>49</td>\n",
       "            <td>93.30%</td>\n",
       "            <td>15.6</td>\n",
       "            <td>96.90%</td>\n",
       "            <td>100.00%</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>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>14.6</td>\n",
       "            <td>14.8</td>\n",
       "            <td>NDA</td>\n",
       "            <td>16</td>\n",
       "            <td>1.4</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>NDA</td>\n",
       "            <td>302</td>\n",
       "            <td>40</td>\n",
       "            <td>91.8</td>\n",
       "            <td>1175177.622</td>\n",
       "            <td>1880745.126</td>\n",
       "            <td>41.82814609</td>\n",
       "            <td>-87.63279369</td>\n",
       "            <td>34</td>\n",
       "            <td>ARMOUR SQUARE</td>\n",
       "            <td>11</td>\n",
       "            <td>9</td>\n",
       "            <td>(41.82814609, -87.63279369)</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.0, 'Very Strong', '99', 'Strong', 74.0, 'Strong', 66.0, 'Weak', '65', 'Strong', '70', 'Strong', '56', 'Average', '47', '96.00%', 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', 69.7, 64.4, 0.2, 0.9, 'Yellow', 'Green', '67.1', '54.5', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 813, 33, 'NDA', 1171699.458, 1915829.428, 41.92449696, -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.0, 'Strong', '66', 'Strong', 74.0, 'Very Strong', 84.0, 'Weak', '63', 'Strong', '76', 'Weak', '46', 'Average', '50', '95.60%', 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', 16.8, 16.5, 0.7, 1.4, 'Green', 'Green', '17.2', '27.3', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 521, 46, 'NDA', 1196129.985, 1856209.466, 41.76032435, -87.55673627, 43, 'SOUTH SHORE', 7, 4, '(41.76032435, -87.55673627)'),\n",
       " (610185, 'Adlai E Stevenson Elementary School', 'ES', '8010 S Kostner Ave', 'Chicago', 'IL', 60652, '(773) 535-2280', 'http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610185.pdf', 'Midway Elementary Network', 'SOUTHWEST SIDE COLLABORATIVE', 'No', 'Standard', 'Not on Probation', 'Level 2', 'No', 'Strong', 61.0, 'NDA', 'NDA', 'Average', 50.0, 'Weak', 36.0, 'Weak', 'NDA', 'NDA', 'NDA', 'Average', '47', 'Weak', '41', '95.70%', 2.3, '94.70%', '98.30%', '53.7', '26.6', '38.3', '34.7', '43.7', '57.3', '48.8', '39.2', '46.8', '44', '7.5', '21.9', 18.3, 15.5, -0.9, -1.0, 'Red', 'Red', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 1324, 44, 'NDA', 1148427.165, 1851012.215, 41.74711093, -87.73170248, 70, 'ASHBURN', 13, 8, '(41.74711093, -87.73170248)'),\n",
       " (609993, 'Agustin Lara Elementary Academy', 'ES', '4619 S Wolcott Ave', 'Chicago', 'IL', 60609, '(773) 535-4389', 'http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_609993.pdf', 'Pershing Elementary Network', 'SOUTHWEST SIDE COLLABORATIVE', 'No', 'Track_E', 'Not on Probation', 'Level 1', 'No', 'Average', 56.0, 'Average', '44', 'Average', 45.0, 'Weak', 37.0, 'Weak', '65', 'Average', '48', 'Average', '53', 'Strong', '58', '95.50%', 10.4, '95.80%', '100.00%', '76.9', 'NDA', '26', '24.7', '61.8', '49.7', '39.2', '27.2', '69.7', '60.6', '9.1', '18.2', 11.1, 9.6, 0.9, 2.4, 'Green', 'Green', '42.9', '25', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 556, 42, 'NDA', 1164504.29, 1873959.199, 41.8097569, -87.6721446, 61, 'NEW CITY', 20, 9, '(41.8097569, -87.6721446)'),\n",
       " (610513, 'Air Force Academy High School', 'HS', '3630 S Wells St', 'Chicago', 'IL', 60609, '(773) 535-1590', 'http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_610513.pdf', 'Southwest Side High School Network', 'SOUTHWEST SIDE COLLABORATIVE', 'NDA', 'Standard', 'Not on Probation', 'Not Enough Data', 'Yes', 'Average', 49.0, 'Strong', '60', 'Strong', 60.0, 'Average', 55.0, 'Weak', '45', 'Average', '54', 'Average', '53', 'Average', '49', '93.30%', 15.6, '96.90%', '100.00%', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', None, None, None, None, 'NDA', 'NDA', 'NDA', 'NDA', '14.6', '14.8', 'NDA', '16', '1.4', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 302, 40, '91.8', 1175177.622, 1880745.126, 41.82814609, -87.63279369, 34, 'ARMOUR SQUARE', 11, 9, '(41.82814609, -87.63279369)')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHICAGO_PUBLIC_SCHOOLS_DATA LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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>0</td>\n",
       "            <td>0</td>\n",
       "            <td>911</td>\n",
       "            <td>9</td>\n",
       "            <td>14.0</td>\n",
       "            <td>58.0</td>\n",
       "            <td>6</td>\n",
       "            <td>1155838.0</td>\n",
       "            <td>1873050.0</td>\n",
       "            <td>2004</td>\n",
       "            <td>41.8074405</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>0</td>\n",
       "            <td>0</td>\n",
       "            <td>1112</td>\n",
       "            <td>11</td>\n",
       "            <td>27.0</td>\n",
       "            <td>23.0</td>\n",
       "            <td>6</td>\n",
       "            <td>1152206.0</td>\n",
       "            <td>1906127.0</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>0</td>\n",
       "            <td>0</td>\n",
       "            <td>221</td>\n",
       "            <td>2</td>\n",
       "            <td>3.0</td>\n",
       "            <td>38.0</td>\n",
       "            <td>6</td>\n",
       "            <td>1177436.0</td>\n",
       "            <td>1876313.0</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>0</td>\n",
       "            <td>0</td>\n",
       "            <td>423</td>\n",
       "            <td>4</td>\n",
       "            <td>7.0</td>\n",
       "            <td>46.0</td>\n",
       "            <td>6</td>\n",
       "            <td>1194622.0</td>\n",
       "            <td>1850125.0</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>0</td>\n",
       "            <td>0</td>\n",
       "            <td>831</td>\n",
       "            <td>8</td>\n",
       "            <td>15.0</td>\n",
       "            <td>66.0</td>\n",
       "            <td>6</td>\n",
       "            <td>1155240.0</td>\n",
       "            <td>1860661.0</td>\n",
       "            <td>2016</td>\n",
       "            <td>41.7734553</td>\n",
       "            <td>-87.70648047</td>\n",
       "            <td>(41.773455295, -87.706480471)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3512276, 'HK587712', '2004-08-28', '047XX S KEDZIE AVE', '890', 'THEFT', 'FROM BUILDING', 'SMALL RETAIL STORE', 0, 0, 911, 9, 14.0, 58.0, '6', 1155838.0, 1873050.0, 2004, 41.8074405, -87.70395585, '(41.8074405, -87.703955849)'),\n",
       " (3406613, 'HK456306', '2004-06-26', '009XX N CENTRAL PARK AVE', '820', 'THEFT', '$500 AND UNDER', 'OTHER', 0, 0, 1112, 11, 27.0, 23.0, '6', 1152206.0, 1906127.0, 2004, 41.89827996, -87.71640551, '(41.898279962, -87.716405505)'),\n",
       " (8002131, 'HT233595', '2011-04-04', '043XX S WABASH AVE', '820', 'THEFT', '$500 AND UNDER', 'NURSING HOME/RETIREMENT HOME', 0, 0, 221, 2, 3.0, 38.0, '6', 1177436.0, 1876313.0, 2011, 41.81593313, -87.62464213, '(41.815933131, -87.624642127)'),\n",
       " (7903289, 'HT133522', '2010-12-30', '083XX S KINGSTON AVE', '840', 'THEFT', 'FINANCIAL ID THEFT: OVER $300', 'RESIDENCE', 0, 0, 423, 4, 7.0, 46.0, '6', 1194622.0, 1850125.0, 2010, 41.74366532, -87.56246276, '(41.743665322, -87.562462756)'),\n",
       " (10402076, 'HZ138551', '2016-02-02', '033XX W 66TH ST', '820', 'THEFT', '$500 AND UNDER', 'ALLEY', 0, 0, 831, 8, 15.0, 66.0, '6', 1155240.0, 1860661.0, 2016, 41.7734553, -87.70648047, '(41.773455295, -87.706480471)')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHICAGO_CRIME_DATA LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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>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>0</td>\n",
       "            <td>0</td>\n",
       "            <td>1533</td>\n",
       "            <td>15</td>\n",
       "            <td>29.0</td>\n",
       "            <td>25.0</td>\n",
       "            <td>20</td>\n",
       "            <td>1143050.0</td>\n",
       "            <td>1897546.0</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",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5276766, 'HN144152', '2007-01-26', '050XX W VAN BUREN ST', '1792', 'KIDNAPPING', 'CHILD ABDUCTION/STRANGER', 'STREET', 0, 0, 1533, 15, 29.0, 25.0, '20', 1143050.0, 1897546.0, 2007, 41.87490841, -87.75024931, '(41.874908413, -87.750249307)')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHICAGO_CRIME_DATA where PRIMARY_TYPE LIKE 'KIDNAP%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now proceed to the the following questions. Please note that a graded assignment will follow this lab and there will be a question on each of the problems stated below. It can be from the answer you received or the code you write for this problem. Therefore, please keep a note of both your codes as well as the response you generate.\n"
   ]
  },
  {
   "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": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>CRIMES_RECORDED</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>533</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(533,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(ID) AS CRIMES_RECORDED FROM CHICAGO_CRIME_DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 2\n",
    "\n",
    "##### List community area names and numbers with per capita income less than 11000.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COMMUNITY_AREA_NAME</th>\n",
       "            <th>COMMUNITY_AREA_NUMBER</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>West Garfield Park</td>\n",
       "            <td>26.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Lawndale</td>\n",
       "            <td>30.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fuller Park</td>\n",
       "            <td>37.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Riverdale</td>\n",
       "            <td>54.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('West Garfield Park', 26.0),\n",
       " ('South Lawndale', 30.0),\n",
       " ('Fuller Park', 37.0),\n",
       " ('Riverdale', 54.0)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COMMUNITY_AREA_NAME, COMMUNITY_AREA_NUMBER FROM CENSUS_DATA 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": 18,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT CASE_NUMBER FROM CHICAGO_CRIME_DATA 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": 19,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>CASE_NUMBER</th>\n",
       "            <th>PRIMARY_TYPE</th>\n",
       "            <th>DESCRIPTION</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>HN144152</td>\n",
       "            <td>KIDNAPPING</td>\n",
       "            <td>CHILD ABDUCTION/STRANGER</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HN144152', 'KIDNAPPING', 'CHILD ABDUCTION/STRANGER')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT CASE_NUMBER,PRIMARY_TYPE, DESCRIPTION FROM CHICAGO_CRIME_DATA WHERE PRIMARY_TYPE LIKE \"KIDNAP%\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 5\n",
    "\n",
    "##### List the kind of crimes that were recorded at schools. (No repetitions)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>PRIMARY_TYPE</th>\n",
       "            <th>LOCATION_DESCRIPTION</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>BATTERY</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BATTERY</td>\n",
       "            <td>SCHOOL, PUBLIC, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL DAMAGE</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NARCOTICS</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NARCOTICS</td>\n",
       "            <td>SCHOOL, PUBLIC, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ASSAULT</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL TRESPASS</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PUBLIC PEACE VIOLATION</td>\n",
       "            <td>SCHOOL, PRIVATE, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PUBLIC PEACE VIOLATION</td>\n",
       "            <td>SCHOOL, PUBLIC, BUILDING</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('BATTERY', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('BATTERY', 'SCHOOL, PUBLIC, BUILDING'),\n",
       " ('CRIMINAL DAMAGE', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('NARCOTICS', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('NARCOTICS', 'SCHOOL, PUBLIC, BUILDING'),\n",
       " ('ASSAULT', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('CRIMINAL TRESPASS', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('PUBLIC PEACE VIOLATION', 'SCHOOL, PRIVATE, BUILDING'),\n",
       " ('PUBLIC PEACE VIOLATION', 'SCHOOL, PUBLIC, BUILDING')]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT(PRIMARY_TYPE),LOCATION_DESCRIPTION FROM CHICAGO_CRIME_DATA WHERE LOCATION_DESCRIPTION LIKE \"SCHOOL%\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 6\n",
    "\n",
    "##### List the type of schools along with the average safety score for each type.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Elementary, Middle, or High School</th>\n",
       "            <th>AVG(SAFETY_SCORE)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>ES</td>\n",
       "            <td>49.52038369304557</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HS</td>\n",
       "            <td>49.62352941176471</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MS</td>\n",
       "            <td>48.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ES', 49.52038369304557), ('HS', 49.62352941176471), ('MS', 48.0)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT \"ELEMENTARY, MIDDLE, OR HIGH SCHOOL\", AVG(SAFETY_SCORE) \\\n",
    "FROM CHICAGO_PUBLIC_SCHOOLS_DATA 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": 21,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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', 56.5),\n",
       " ('Fuller Park', 51.2),\n",
       " ('Englewood', 46.6),\n",
       " ('North Lawndale', 43.1),\n",
       " ('East Garfield Park', 42.4)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COMMUNITY_AREA_NAME, PERCENT_HOUSEHOLDS_BELOW_POVERTY FROM CENSUS_DATA \\\n",
    "ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 8\n",
    "\n",
    "##### Which community area is most crime prone? Display the coumminty area number only.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COMMUNITY_AREA_NUMBER</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>25.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(25.0,)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COMMUNITY_AREA_NUMBER FROM CHICAGO_CRIME_DATA \\\n",
    "GROUP BY COMMUNITY_AREA_NUMBER ORDER BY COUNT(ID) DESC NULLS LAST LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Double-click **here** for a hint\n",
    "\n",
    "<!--\n",
    "Query for the 'community area number' that has most number of incidents\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": 30,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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": 30,
     "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": 38,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\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": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA CD, CHICAGO_CRIME_DATA CCD \\\n",
    "WHERE CD.COMMUNITY_AREA_NUMBER = CCD.COMMUNITY_AREA_NUMBER GROUP BY CCD.COMMUNITY_AREA_NUMBER ORDER BY COUNT(ID) DESC LIMIT 1"
   ]
  },
  {
   "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",
    "<h4>Abhishek Gagneja</h4>\n",
    "\n",
    "## Change log\n",
    "\n",
    "| Date       | Version | Changed by        | Change Description                             |\n",
    "| ---------- | ------- | ----------------- | ---------------------------------------------- |\n",
    "|2023-10-18  | 2.6     | Abhishek Gagneja  | Modified instruction set |\n",
    "| 2022-03-04 | 2.5     | Lakshmi Holla     | Changed markdown.                   |\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 2023. 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
}