capstone.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing Libraries and Creating Engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pypandoc.pandoc_download import download_pandoc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-1-5eb444c6e708>, line 6)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-1-5eb444c6e708>\"\u001b[1;36m, line \u001b[1;32m6\u001b[0m\n\u001b[1;33m    print highlight(text, lexer, formatters.TerminalFormatter())\u001b[0m\n\u001b[1;37m          ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import mysql.connector\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine(\"mysql+mysqlconnector://username:passwd@host/database\")\n",
    "\n",
    "engine.connect()\n",
    "\n",
    "print(engine)\n",
    "\n",
    "print(\"***engine_running***\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading SQL Extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "\n",
    "%sql mysql+mysqlconnector://username:passwd@host/database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Showing Tables in the Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Tables_in_capstone</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>chicago_census_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>chicago_crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>chicago_public_schools</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('chicago_census_data',), ('chicago_crime',), ('chicago_public_schools',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SHOW TABLES;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Capstone Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 1: Find the total number of crimes recorded in the crime table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count(ID)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>503</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(503,)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "select count(ID) from chicago_crime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 2: Retrieve first 10 rows from the CRIME table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "10 rows affected.\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>ARRST</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>UPDATEDON</th>\n",
       "        <th>LATITUDE</th>\n",
       "        <th>LONGITUDE</th>\n",
       "        <th>LOCATION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10003235</td>\n",
       "        <td>HY192694</td>\n",
       "        <td>2015-03-21</td>\n",
       "        <td>064XX S OAKLEY AVE</td>\n",
       "        <td>486</td>\n",
       "        <td>BATTERY</td>\n",
       "        <td>DOMESTIC BATTERY SIMPLE</td>\n",
       "        <td>APARTMENT</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>825</td>\n",
       "        <td>8</td>\n",
       "        <td>15</td>\n",
       "        <td>66</td>\n",
       "        <td>08B</td>\n",
       "        <td>1162182</td>\n",
       "        <td>1861826</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.77651055</td>\n",
       "        <td>-87.68100012</td>\n",
       "        <td>(41.776510546, -87.681000121)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10012707</td>\n",
       "        <td>HY202462</td>\n",
       "        <td>2015-03-28</td>\n",
       "        <td>056XX W BYRON ST</td>\n",
       "        <td>810</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>OVER $500</td>\n",
       "        <td>STREET</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1633</td>\n",
       "        <td>16</td>\n",
       "        <td>38</td>\n",
       "        <td>15</td>\n",
       "        <td>6</td>\n",
       "        <td>1138080</td>\n",
       "        <td>1925360</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.95132446</td>\n",
       "        <td>-87.76782401</td>\n",
       "        <td>(41.95132446, -87.767824012)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10015483</td>\n",
       "        <td>HY204891</td>\n",
       "        <td>2015-03-31</td>\n",
       "        <td>011XX N PULASKI RD</td>\n",
       "        <td>4625</td>\n",
       "        <td>OTHER OFFENSE</td>\n",
       "        <td>PAROLE VIOLATION</td>\n",
       "        <td>SIDEWALK</td>\n",
       "        <td>TRUE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1112</td>\n",
       "        <td>11</td>\n",
       "        <td>27</td>\n",
       "        <td>23</td>\n",
       "        <td>26</td>\n",
       "        <td>1149510</td>\n",
       "        <td>1907365</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.90172993</td>\n",
       "        <td>-87.7262756</td>\n",
       "        <td>(41.901729932, -87.726275604)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10025545</td>\n",
       "        <td>HY215032</td>\n",
       "        <td>2015-04-08</td>\n",
       "        <td>039XX W LEXINGTON ST</td>\n",
       "        <td>2014</td>\n",
       "        <td>NARCOTICS</td>\n",
       "        <td>MANU/DELIVER: HEROIN (WHITE)</td>\n",
       "        <td>SIDEWALK</td>\n",
       "        <td>TRUE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1132</td>\n",
       "        <td>11</td>\n",
       "        <td>24</td>\n",
       "        <td>26</td>\n",
       "        <td>18</td>\n",
       "        <td>1150205</td>\n",
       "        <td>1896407</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.87164648</td>\n",
       "        <td>-87.72400857</td>\n",
       "        <td>(41.871646482, -87.724008569)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10046030</td>\n",
       "        <td>HY234744</td>\n",
       "        <td>2015-04-24</td>\n",
       "        <td>008XX N MICHIGAN AVE</td>\n",
       "        <td>860</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>RETAIL THEFT</td>\n",
       "        <td>DEPARTMENT STORE</td>\n",
       "        <td>TRUE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1833</td>\n",
       "        <td>18</td>\n",
       "        <td>42</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>1177338</td>\n",
       "        <td>1906213</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.89798294</td>\n",
       "        <td>-87.62409563</td>\n",
       "        <td>(41.897982937, -87.624095634)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10070513</td>\n",
       "        <td>HY259084</td>\n",
       "        <td>2015-05-13</td>\n",
       "        <td>056XX S WOOD ST</td>\n",
       "        <td>3710</td>\n",
       "        <td>INTERFERENCE WITH PUBLIC OFFICER</td>\n",
       "        <td>RESIST/OBSTRUCT/DISARM OFFICER</td>\n",
       "        <td>STREET</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>715</td>\n",
       "        <td>7</td>\n",
       "        <td>15</td>\n",
       "        <td>67</td>\n",
       "        <td>24</td>\n",
       "        <td>1165309</td>\n",
       "        <td>1867385</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.79169949</td>\n",
       "        <td>-87.6693793</td>\n",
       "        <td>(41.791699487, -87.669379304)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10079280</td>\n",
       "        <td>HY267896</td>\n",
       "        <td>2015-05-20</td>\n",
       "        <td>115XX S STEWART AVE</td>\n",
       "        <td>1320</td>\n",
       "        <td>CRIMINAL DAMAGE</td>\n",
       "        <td>TO VEHICLE</td>\n",
       "        <td>RESIDENTIAL YARD (FRONT/BACK)</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>TRUE</td>\n",
       "        <td>522</td>\n",
       "        <td>5</td>\n",
       "        <td>34</td>\n",
       "        <td>53</td>\n",
       "        <td>14</td>\n",
       "        <td>1175695</td>\n",
       "        <td>1828405</td>\n",
       "        <td>2015</td>\n",
       "        <td>2015-08-17</td>\n",
       "        <td>41.68450696</td>\n",
       "        <td>-87.63245993</td>\n",
       "        <td>(41.684506962, -87.632459932)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10084732</td>\n",
       "        <td>HY273740</td>\n",
       "        <td>2015-05-24</td>\n",
       "        <td>004XX N STATE ST</td>\n",
       "        <td>820</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>$500 AND UNDER</td>\n",
       "        <td>BAR OR TAVERN</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>1831</td>\n",
       "        <td>18</td>\n",
       "        <td>42</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>1176302</td>\n",
       "        <td>1903096</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.88945317</td>\n",
       "        <td>-87.62799483</td>\n",
       "        <td>(41.889453169, -87.627994833)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10102551</td>\n",
       "        <td>HY291329</td>\n",
       "        <td>2015-06-06</td>\n",
       "        <td>028XX W DEVON AVE</td>\n",
       "        <td>860</td>\n",
       "        <td>THEFT</td>\n",
       "        <td>RETAIL THEFT</td>\n",
       "        <td>DRUG STORE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>2413</td>\n",
       "        <td>24</td>\n",
       "        <td>50</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>1156278</td>\n",
       "        <td>1942331</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.9975448</td>\n",
       "        <td>-87.70046744</td>\n",
       "        <td>(41.997544797, -87.700467444)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10177662</td>\n",
       "        <td>HY365876</td>\n",
       "        <td>2015-08-02</td>\n",
       "        <td>041XX W 87TH ST</td>\n",
       "        <td>1811</td>\n",
       "        <td>NARCOTICS</td>\n",
       "        <td>POSS: CANNABIS 30GMS OR LESS</td>\n",
       "        <td>STREET</td>\n",
       "        <td>TRUE</td>\n",
       "        <td>FALSE</td>\n",
       "        <td>834</td>\n",
       "        <td>8</td>\n",
       "        <td>18</td>\n",
       "        <td>70</td>\n",
       "        <td>18</td>\n",
       "        <td>1150504</td>\n",
       "        <td>1846544</td>\n",
       "        <td>2015</td>\n",
       "        <td>2018-02-10</td>\n",
       "        <td>41.73480925</td>\n",
       "        <td>-87.72420818</td>\n",
       "        <td>(41.734809253, -87.724208178)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('10003235', 'HY192694', datetime.date(2015, 3, 21), '064XX S OAKLEY AVE', 486, 'BATTERY', 'DOMESTIC BATTERY SIMPLE', 'APARTMENT', 'FALSE', 'FALSE', '825', '8', '15', '66', '08B', '1162182', '1861826', '2015', datetime.date(2018, 2, 10), 41.77651055, -87.68100012, '(41.776510546, -87.681000121)'),\n",
       " ('10012707', 'HY202462', datetime.date(2015, 3, 28), '056XX W BYRON ST', 810, 'THEFT', 'OVER $500', 'STREET', 'FALSE', 'FALSE', '1633', '16', '38', '15', '6', '1138080', '1925360', '2015', datetime.date(2018, 2, 10), 41.95132446, -87.76782401, '(41.95132446, -87.767824012)'),\n",
       " ('10015483', 'HY204891', datetime.date(2015, 3, 31), '011XX N PULASKI RD', 4625, 'OTHER OFFENSE', 'PAROLE VIOLATION', 'SIDEWALK', 'TRUE', 'FALSE', '1112', '11', '27', '23', '26', '1149510', '1907365', '2015', datetime.date(2018, 2, 10), 41.90172993, -87.7262756, '(41.901729932, -87.726275604)'),\n",
       " ('10025545', 'HY215032', datetime.date(2015, 4, 8), '039XX W LEXINGTON ST', 2014, 'NARCOTICS', 'MANU/DELIVER: HEROIN (WHITE)', 'SIDEWALK', 'TRUE', 'FALSE', '1132', '11', '24', '26', '18', '1150205', '1896407', '2015', datetime.date(2018, 2, 10), 41.87164648, -87.72400857, '(41.871646482, -87.724008569)'),\n",
       " ('10046030', 'HY234744', datetime.date(2015, 4, 24), '008XX N MICHIGAN AVE', 860, 'THEFT', 'RETAIL THEFT', 'DEPARTMENT STORE', 'TRUE', 'FALSE', '1833', '18', '42', '8', '6', '1177338', '1906213', '2015', datetime.date(2018, 2, 10), 41.89798294, -87.62409563, '(41.897982937, -87.624095634)'),\n",
       " ('10070513', 'HY259084', datetime.date(2015, 5, 13), '056XX S WOOD ST', 3710, 'INTERFERENCE WITH PUBLIC OFFICER', 'RESIST/OBSTRUCT/DISARM OFFICER', 'STREET', 'FALSE', 'FALSE', '715', '7', '15', '67', '24', '1165309', '1867385', '2015', datetime.date(2018, 2, 10), 41.79169949, -87.6693793, '(41.791699487, -87.669379304)'),\n",
       " ('10079280', 'HY267896', datetime.date(2015, 5, 20), '115XX S STEWART AVE', 1320, 'CRIMINAL DAMAGE', 'TO VEHICLE', 'RESIDENTIAL YARD (FRONT/BACK)', 'FALSE', 'TRUE', '522', '5', '34', '53', '14', '1175695', '1828405', '2015', datetime.date(2015, 8, 17), 41.68450696, -87.63245993, '(41.684506962, -87.632459932)'),\n",
       " ('10084732', 'HY273740', datetime.date(2015, 5, 24), '004XX N STATE ST', 820, 'THEFT', '$500 AND UNDER', 'BAR OR TAVERN', 'FALSE', 'FALSE', '1831', '18', '42', '8', '6', '1176302', '1903096', '2015', datetime.date(2018, 2, 10), 41.88945317, -87.62799483, '(41.889453169, -87.627994833)'),\n",
       " ('10102551', 'HY291329', datetime.date(2015, 6, 6), '028XX W DEVON AVE', 860, 'THEFT', 'RETAIL THEFT', 'DRUG STORE', 'FALSE', 'FALSE', '2413', '24', '50', '2', '6', '1156278', '1942331', '2015', datetime.date(2018, 2, 10), 41.9975448, -87.70046744, '(41.997544797, -87.700467444)'),\n",
       " ('10177662', 'HY365876', datetime.date(2015, 8, 2), '041XX W 87TH ST', 1811, 'NARCOTICS', 'POSS: CANNABIS 30GMS OR LESS', 'STREET', 'TRUE', 'FALSE', '834', '8', '18', '70', '18', '1150504', '1846544', '2015', datetime.date(2018, 2, 10), 41.73480925, -87.72420818, '(41.734809253, -87.724208178)')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "select * from chicago_crime limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 3: How many crimes involve an arrest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>involve_arrest</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>153</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(153,)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "select count(ID) as involve_arrest from chicago_crime where ARRST = 'True'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 4: Which unique types of crimes (e.g. THEFT) have been recorded at a GAS STATION locations?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>PR_TYP_GAS_STN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>THEFT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CRIMINAL TRESPASS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NARCOTICS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROBBERY</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('THEFT',), ('CRIMINAL TRESPASS',), ('NARCOTICS',), ('ROBBERY',)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "select DISTINCT(PRIMARY_TYPE) AS PR_TYP_GAS_STN from chicago_crime where LOCATION_DESCRIPTION = 'GAS STATION'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 5: In the CENUS_DATA table list all community areas whose names start with the letter ‘B’."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COMMUNITY_AREA_NAME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belmont Cragin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burnside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brighton Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bridgeport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beverly</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Belmont Cragin',),\n",
       " ('Burnside',),\n",
       " ('Brighton Park',),\n",
       " ('Bridgeport',),\n",
       " ('Beverly',)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "#SELECT * FROM chicago_census_data limit 2\n",
    "\n",
    "select COMMUNITY_AREA_NAME from chicago_census_data where COMMUNITY_AREA_NAME like 'B%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 6: List the schools in community areas 10 to 15 that are healthy school certified."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>school_id</th>\n",
       "        <th>name_of_school</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>609995</td>\n",
       "        <td>Rufus M Hitch Elementary School</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(609995, 'Rufus M Hitch Elementary School')]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "#List the schools in community areas 10 to 15 that are healthy school certified.\n",
    "\n",
    "\n",
    "#select * from chicago_public_schools limit 2\n",
    "\n",
    "select school_id, name_of_school from chicago_public_schools\n",
    "    where community_area_number >=10 and community_area_number <=15 and healthy_school_certified = 'YES'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 7: What is the average school Safety Score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Avg(safety_score)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49.50487329434698</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(49.50487329434698,)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "#select * from chicago_public_schools limit 2\n",
    "\n",
    "select Avg(safety_score) from chicago_public_schools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 8: Find the top 5 Community Areas by average College Enrollment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LINCOLN PARK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOUTH SHORE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ASHBURN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NEW CITY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARMOUR SQUARE</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LINCOLN PARK',),\n",
       " ('SOUTH SHORE',),\n",
       " ('ASHBURN',),\n",
       " ('NEW CITY',),\n",
       " ('ARMOUR SQUARE',)]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "#Find the top 5 Community Areas by average College Enrollment [number of students].\n",
    "\n",
    "select community_area_name from chicago_public_schools\n",
    "    order by (select avg(college_enrollment) from chicago_public_schools) DESC limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 9: Use a sub-query to determine which Community Area has the least value for school Safety Score."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_number</th>\n",
       "        <th>community_area_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>WASHINGTON PARK</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('40', 'WASHINGTON PARK')]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "#Use a sub-query to determine which Community Area has the least value for school Safety Score?\n",
    "\n",
    "select community_area_number, community_area_name from chicago_public_schools\n",
    "    where safety_score = (select min(safety_score) from chicago_public_schools)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem 10: Without using an explicit JOIN operator, find the Per Capita Income of the Community Area which has a school Safety Score of 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+mysqlconnector://root:***@127.0.0.1/capstone\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SAFETY_SCORE</th>\n",
       "        <th>COMMUNITY_AREA_NUMBER</th>\n",
       "        <th>COMMUNITY_AREA_NAME</th>\n",
       "        <th>PER_CAPITA_INCOMe</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>40</td>\n",
       "        <td>WASHINGTON PARK</td>\n",
       "        <td>13785</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('1', '40', 'WASHINGTON PARK', 13785)]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "select cps.SAFETY_SCORE, cps.COMMUNITY_AREA_NUMBER, cps.COMMUNITY_AREA_NAME, ccd.PER_CAPITA_INCOMe\n",
    "from chicago_public_schools cps, chicago_census_data ccd\n",
    "    where cps.COMMUNITY_AREA_NUMBER = ccd.COMMUNITY_AREA_NUMBER\n",
    "    and cps.SAFETY_SCORE = 1\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PROJECT COMPLETED!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}