Chicago Public Schools.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/Logos/organization_logo/organization_logo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n",
    "</center>\n",
    "\n",
    "# Chicago Public Schools\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The city of Chicago released a dataset showing all school level performance data used to create School Report Cards for the 2011-2012 school year. The dataset is available from the Chicago Data Portal: [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-2022-01-01&cm_mmc=Email_Newsletter-\\_-Developer_Ed%2BTech-\\_-WW_WW-\\_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ)\n",
    "\n",
    "This dataset includes a large number of metrics. Start by familiarizing yourself with the types of metrics in the database: [https://data.cityofchicago.org/api/assets/AAD41A13-BE8A-4E67-B1F5-86E711E09D5F?download=true](https://data.cityofchicago.org/api/assets/AAD41A13-BE8A-4E67-B1F5-86E711E09D5F?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01&download=true&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",
    "**NOTE**:\n",
    "\n",
    "Do not download the dataset directly from City of Chicago portal. Instead download a static copy which is a more database friendly version from this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoPublicSchools.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\">link</a>.\n",
    "\n",
    "Now review some of its contents.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store the dataset in a Table\n",
    "\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. To analyze the data using SQL, it first needs to be stored in the database.\n",
    "\n",
    "While it is easier to read the dataset into a Pandas dataframe and then PERSIST it into the database, it results in mapping to default datatypes which may not be optimal for SQL querying. For example a long textual field may map to a CLOB instead of a VARCHAR.\n",
    "\n",
    "Therefore, **it is highly recommended to manually load the table using the database console LOAD tool**. \n",
    "\n",
    "##### Open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the CHICAGO PUBLIC SCHOOLS dataset and load the dataset into a new table called **SCHOOLS**.\n",
    "<center>\n",
    "    <img src=\"https://dl2.boxcloud.com/d/1/b1!DWngfqYnR2y3D17bNoF7DVQ0E9g91V_lHR-COwM3i428RE8MMYYXL49T6fn6j50EaGldn3Xr32G51_cFSKwAScxSWEz2o-IEibb6XfDLvt0OUYIn0s-nHpBbsTwhP7nBu7j-wfShsJaSKybgnPcfrV5jS8C71K2YjBR8BIJbnBsGiZPtvE8o8Q4Nxzy2IPsbmfFuaOsYaoPNU3zLUG10SaJT6hpE3P4-1WkOIvGZbHGjRzH1zuiE6PPU2QgDr4rz9Tl2P-4NxbDQw-MEIFeDu1dKF5mOGgDfl4X3upB-VDRXpE3jF63NRvPzdNZJ8AinGqw3m5rRB8srwqWZrAo_2g7xv84v-QVnnUjPj0e9sZaVVK35A8Jacns2eB_DW-qBehPT430w684NRY51XF4WHp6KA9pIlpfgL8GYCkAv0w-HACjTrdmKJNNwe7VB7z8vkjM6Z18EgLW2l7ml-4l9JXcjgN877CDCowbELrAQEEHOFZKH8CsEg6zr2KfzXnD5FKfKtN2TW-aCF7h9vp2Dvw3CI84jy3bTrhDwftK5XTggF5Dr-8Dx6uj3ojAvrXzF6rT5y1ffBMlCEcysJ08gEUYtMDweTaEBaae6rXuTTTR1CV-cEV0g4UV4yfNeOhaRjjljnNX5A-9Jg-NWqFhM2hhMSsZQGjFwe8lZrWyqjj3Y0S7k8VHYYzGfjR64E62D7KbqxKqk12XTw7RAp8XzrqUQsjXoYKGpYqqw0T5y5thQACzZrxPHY6p7fos35balUt_bFQ5DxxFKhOuJkVqugE79ZgYosgWREc-E8nCXw3y1IqS85b6blb-MAE8AzRIPEFFj-vjAgjgSmOuIsBK78xBsE36cvAuI_isJjM604fj_6CHVB5EzuOUPIKtKvU3V3Q-Wv4trTOloYpV1c5XTif18upt5cr1psFH8xf64CDA_4CZCj1LM5b_nz6uKktEkcDjjXO_bQOV8tdLgDoRewJJ5l6hAS2vpksp8KG6V4JYgnAiUv-uypW9CECGWPnFlUn8ckuxJhUuY0p6n42-7EKZ0_EsIs5mbzc7wy0YB6L3U_ywKTatk6ZdXFHwgjO2GKoiJd0aTXFWDSfPa4X_MBEP7cdP5-q0HLDGCPUQQfgoHKGLxHRGFou_sEHvRxhSvJWBlcmtqJ2484VsBNP8HDrgzxdhXlL1wxovP7Qj6uVi4Grm9-D_PsMgV50mnQ6FpPedZGlF5tv5voFQHtoPQn9hgx8lqMm8pzQzyqiQ-yrlk73t1bPupSqtqahHJiCPGiELKdKrBRx55sUoclhzTEd9-qDRpXjZd1k361JwS1A1xdhytDnUMg9-8_8NJNZbv9Qr1j5ag6uzUJDkbxsH4teCAX_zR8LNBzml_ILRQ1rodE6-EnirFY4pNSz2U9I8SYuqmDdOf6W-Dyjb2Lhcr9Ri_kw../download\" /> \n",
    "</center>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "\n",
    "Let us now load the ipython-sql  extension and establish a connection with the database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Enter the connection string for your Db2 on Cloud database instance below\n",
    "# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name?security=SSL\n",
    "%sql ibm_db_sa://hhf49271:Rrplpk5JraUR4qsg@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb?security=SSL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can verify that the table creation was successful by retrieving the list of all tables in your schema and checking whether the SCHOOLS table was created\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>tabschema</th>\n",
       "        <th>tabname</th>\n",
       "        <th>owner</th>\n",
       "        <th>ownertype</th>\n",
       "        <th>TYPE</th>\n",
       "        <th>status</th>\n",
       "        <th>base_tabschema</th>\n",
       "        <th>base_tabname</th>\n",
       "        <th>rowtypeschema</th>\n",
       "        <th>rowtypename</th>\n",
       "        <th>create_time</th>\n",
       "        <th>alter_time</th>\n",
       "        <th>invalidate_time</th>\n",
       "        <th>stats_time</th>\n",
       "        <th>colcount</th>\n",
       "        <th>tableid</th>\n",
       "        <th>tbspaceid</th>\n",
       "        <th>card</th>\n",
       "        <th>npages</th>\n",
       "        <th>mpages</th>\n",
       "        <th>fpages</th>\n",
       "        <th>npartitions</th>\n",
       "        <th>nfiles</th>\n",
       "        <th>tablesize</th>\n",
       "        <th>overflow</th>\n",
       "        <th>tbspace</th>\n",
       "        <th>index_tbspace</th>\n",
       "        <th>long_tbspace</th>\n",
       "        <th>parents</th>\n",
       "        <th>children</th>\n",
       "        <th>selfrefs</th>\n",
       "        <th>keycolumns</th>\n",
       "        <th>keyindexid</th>\n",
       "        <th>keyunique</th>\n",
       "        <th>checkcount</th>\n",
       "        <th>datacapture</th>\n",
       "        <th>const_checked</th>\n",
       "        <th>pmap_id</th>\n",
       "        <th>partition_mode</th>\n",
       "        <th>log_attribute</th>\n",
       "        <th>pctfree</th>\n",
       "        <th>append_mode</th>\n",
       "        <th>REFRESH</th>\n",
       "        <th>refresh_time</th>\n",
       "        <th>LOCKSIZE</th>\n",
       "        <th>VOLATILE</th>\n",
       "        <th>row_format</th>\n",
       "        <th>property</th>\n",
       "        <th>statistics_profile</th>\n",
       "        <th>compression</th>\n",
       "        <th>rowcompmode</th>\n",
       "        <th>access_mode</th>\n",
       "        <th>clustered</th>\n",
       "        <th>active_blocks</th>\n",
       "        <th>droprule</th>\n",
       "        <th>maxfreespacesearch</th>\n",
       "        <th>avgcompressedrowsize</th>\n",
       "        <th>avgrowcompressionratio</th>\n",
       "        <th>avgrowsize</th>\n",
       "        <th>pctrowscompressed</th>\n",
       "        <th>logindexbuild</th>\n",
       "        <th>codepage</th>\n",
       "        <th>collationschema</th>\n",
       "        <th>collationname</th>\n",
       "        <th>collationschema_orderby</th>\n",
       "        <th>collationname_orderby</th>\n",
       "        <th>encoding_scheme</th>\n",
       "        <th>pctpagessaved</th>\n",
       "        <th>last_regen_time</th>\n",
       "        <th>secpolicyid</th>\n",
       "        <th>protectiongranularity</th>\n",
       "        <th>auditpolicyid</th>\n",
       "        <th>auditpolicyname</th>\n",
       "        <th>auditexceptionenabled</th>\n",
       "        <th>definer</th>\n",
       "        <th>oncommit</th>\n",
       "        <th>logged</th>\n",
       "        <th>onrollback</th>\n",
       "        <th>lastused</th>\n",
       "        <th>control</th>\n",
       "        <th>temporaltype</th>\n",
       "        <th>tableorg</th>\n",
       "        <th>extended_row_size</th>\n",
       "        <th>pctextendedrows</th>\n",
       "        <th>remarks</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>PETSALE</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-05-30 23:19:15.470029</td>\n",
       "        <td>2022-05-30 23:47:08.037038</td>\n",
       "        <td>2022-05-30 23:47:08.037046</td>\n",
       "        <td>None</td>\n",
       "        <td>5</td>\n",
       "        <td>4</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-05-30 23:19:15.470029</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-05-30</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>EMPLOYEES</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-05-31 02:00:39.664406</td>\n",
       "        <td>2022-05-31 02:00:39.837056</td>\n",
       "        <td>2022-05-31 02:00:39.837064</td>\n",
       "        <td>None</td>\n",
       "        <td>11</td>\n",
       "        <td>5</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-05-31 02:00:39.664406</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-06-01</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>JOB_HISTORY</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-05-31 02:00:39.854394</td>\n",
       "        <td>2022-05-31 02:00:40.034023</td>\n",
       "        <td>2022-05-31 02:00:40.034031</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-05-31 02:00:39.854394</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-05-31</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>JOBS</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-05-31 02:00:40.048448</td>\n",
       "        <td>2022-05-31 02:00:40.225434</td>\n",
       "        <td>2022-05-31 02:00:40.225442</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>7</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-05-31 02:00:40.048448</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-06-02</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>DEPARTMENTS</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-05-31 02:00:40.233142</td>\n",
       "        <td>2022-05-31 02:00:40.459309</td>\n",
       "        <td>2022-05-31 02:00:40.459320</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>8</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-05-31 02:00:40.233142</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-05-31</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>LOCATIONS</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-05-31 02:00:40.469861</td>\n",
       "        <td>2022-05-31 02:00:40.647919</td>\n",
       "        <td>2022-05-31 02:00:40.647930</td>\n",
       "        <td>None</td>\n",
       "        <td>2</td>\n",
       "        <td>9</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-05-31 02:00:40.469861</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-05-31</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>PETRESCUE</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-06-01 23:46:40.031028</td>\n",
       "        <td>2022-06-01 23:46:40.185380</td>\n",
       "        <td>2022-06-01 23:46:40.185387</td>\n",
       "        <td>None</td>\n",
       "        <td>5</td>\n",
       "        <td>10</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-06-01 23:46:40.031028</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-06-01</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>INTERNATIONAL_STUDENT_TEST_SCORES</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-06-05 18:13:18.401179</td>\n",
       "        <td>2022-06-05 18:13:18.401179</td>\n",
       "        <td>2022-06-05 18:13:18.401179</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-06-05 18:13:18.401179</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-06-08</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>CHICAGO_SOCIOECONOMIC_DATA</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-06-08 02:25:21.426917</td>\n",
       "        <td>2022-06-08 02:25:21.426917</td>\n",
       "        <td>2022-06-08 02:25:22.061634</td>\n",
       "        <td>None</td>\n",
       "        <td>10</td>\n",
       "        <td>13</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-06-08 02:25:21.426917</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-06-08</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>INSTRUCTOR</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-06-08 01:00:45.874407</td>\n",
       "        <td>2022-06-08 01:00:46.028956</td>\n",
       "        <td>2022-06-08 01:00:46.028963</td>\n",
       "        <td>None</td>\n",
       "        <td>5</td>\n",
       "        <td>11</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-06-08 01:00:45.874407</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>2022-06-08</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HHF49271</td>\n",
       "        <td>SCHOOLS</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td>U</td>\n",
       "        <td>T</td>\n",
       "        <td>N</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2022-06-09 00:46:44.924549</td>\n",
       "        <td>2022-06-09 00:46:44.924549</td>\n",
       "        <td>2022-06-09 00:46:44.924549</td>\n",
       "        <td>None</td>\n",
       "        <td>78</td>\n",
       "        <td>14</td>\n",
       "        <td>149</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1</td>\n",
       "        <td>HHF49271SPACE1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>N</td>\n",
       "        <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n",
       "        <td>1</td>\n",
       "        <td> </td>\n",
       "        <td>0</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>R</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td> </td>\n",
       "        <td>F</td>\n",
       "        <td>None</td>\n",
       "        <td>-1</td>\n",
       "        <td>N</td>\n",
       "        <td>999</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>-1</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1208</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td>SYSIBM</td>\n",
       "        <td>IDENTITY</td>\n",
       "        <td> </td>\n",
       "        <td>-1</td>\n",
       "        <td>2022-06-09 00:46:44.924549</td>\n",
       "        <td>0</td>\n",
       "        <td> </td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>N</td>\n",
       "        <td>HHF49271</td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td> </td>\n",
       "        <td>0001-01-01</td>\n",
       "        <td> </td>\n",
       "        <td>N</td>\n",
       "        <td>R</td>\n",
       "        <td>N</td>\n",
       "        <td>-1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HHF49271', 'PETSALE', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 5, 30, 23, 19, 15, 470029), datetime.datetime(2022, 5, 30, 23, 47, 8, 37038), datetime.datetime(2022, 5, 30, 23, 47, 8, 37046), None, 5, 4, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 5, 30, 23, 19, 15, 470029), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 5, 30), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'EMPLOYEES', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 5, 31, 2, 0, 39, 664406), datetime.datetime(2022, 5, 31, 2, 0, 39, 837056), datetime.datetime(2022, 5, 31, 2, 0, 39, 837064), None, 11, 5, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 1, 1, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 5, 31, 2, 0, 39, 664406), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 6, 1), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'JOB_HISTORY', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 5, 31, 2, 0, 39, 854394), datetime.datetime(2022, 5, 31, 2, 0, 40, 34023), datetime.datetime(2022, 5, 31, 2, 0, 40, 34031), None, 4, 6, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 2, 1, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 5, 31, 2, 0, 39, 854394), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 5, 31), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'JOBS', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 5, 31, 2, 0, 40, 48448), datetime.datetime(2022, 5, 31, 2, 0, 40, 225434), datetime.datetime(2022, 5, 31, 2, 0, 40, 225442), None, 4, 7, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 1, 1, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 5, 31, 2, 0, 40, 48448), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 6, 2), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'DEPARTMENTS', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 5, 31, 2, 0, 40, 233142), datetime.datetime(2022, 5, 31, 2, 0, 40, 459309), datetime.datetime(2022, 5, 31, 2, 0, 40, 459320), None, 4, 8, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 1, 1, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 5, 31, 2, 0, 40, 233142), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 5, 31), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'LOCATIONS', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 5, 31, 2, 0, 40, 469861), datetime.datetime(2022, 5, 31, 2, 0, 40, 647919), datetime.datetime(2022, 5, 31, 2, 0, 40, 647930), None, 2, 9, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 2, 1, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 5, 31, 2, 0, 40, 469861), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 5, 31), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'PETRESCUE', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 6, 1, 23, 46, 40, 31028), datetime.datetime(2022, 6, 1, 23, 46, 40, 185380), datetime.datetime(2022, 6, 1, 23, 46, 40, 185387), None, 5, 10, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 1, 1, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 6, 1, 23, 46, 40, 31028), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 6, 1), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'INTERNATIONAL_STUDENT_TEST_SCORES', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 6, 5, 18, 13, 18, 401179), datetime.datetime(2022, 6, 5, 18, 13, 18, 401179), datetime.datetime(2022, 6, 5, 18, 13, 18, 401179), None, 4, 12, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 6, 5, 18, 13, 18, 401179), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 6, 8), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'CHICAGO_SOCIOECONOMIC_DATA', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 6, 8, 2, 25, 21, 426917), datetime.datetime(2022, 6, 8, 2, 25, 21, 426917), datetime.datetime(2022, 6, 8, 2, 25, 22, 61634), None, 10, 13, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 6, 8, 2, 25, 21, 426917), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 6, 8), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'INSTRUCTOR', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 6, 8, 1, 0, 45, 874407), datetime.datetime(2022, 6, 8, 1, 0, 46, 28956), datetime.datetime(2022, 6, 8, 1, 0, 46, 28963), None, 5, 11, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 1, 1, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 6, 8, 1, 0, 45, 874407), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(2022, 6, 8), ' ', 'N', 'R', 'N', -1.0, None),\n",
       " ('HHF49271', 'SCHOOLS', 'HHF49271', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 6, 9, 0, 46, 44, 924549), datetime.datetime(2022, 6, 9, 0, 46, 44, 924549), datetime.datetime(2022, 6, 9, 0, 46, 44, 924549), None, 78, 14, 149, -1, -1, -1, -1, -1, -1, -1, -1, 'HHF49271SPACE1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, -1, 'N', 999, -1, -1.0, -1, -1.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', -1, datetime.datetime(2022, 6, 9, 0, 46, 44, 924549), 0, ' ', None, None, 'N', 'HHF49271', ' ', ' ', ' ', datetime.date(1, 1, 1), ' ', 'N', 'R', 'N', -1.0, None)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# type in your query to retrieve list of all tables in the database for your db2 schema (username)\n",
    "username = 'HHF49271'\n",
    "%sql SELECT * FROM syscat.tables WHERE tabschema = :username;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The SCHOOLS table contains a large number of columns. How many columns does this table have?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n",
      "+---------+----------+\n",
      "| tabname | colcount |\n",
      "+---------+----------+\n",
      "| SCHOOLS |    78    |\n",
      "+---------+----------+\n",
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(78,)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# type in your query to retrieve the number of columns in the SCHOOLS table\n",
    "sql = %sql SELECT tabname, colcount FROM syscat.tables WHERE tabname = 'SCHOOLS';\n",
    "print(sql);\n",
    "%sql SELECT COUNT(*) FROM syscat.columns WHERE tabname = 'SCHOOLS';\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now retrieve the the list of columns in SCHOOLS table and their column type (datatype) and length.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>colname</th>\n",
       "        <th>typename</th>\n",
       "        <th>length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR6_8_KEEP_PACE_READ__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR_8_EXPLORE_MATH__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR_8_EXPLORE_READ__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ISAT_EXCEEDING_MATH__</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ISAT_EXCEEDING_READING__</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ISAT_VALUE_ADD_MATH</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ISAT_VALUE_ADD_READ</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ISAT_VALUE_ADD_COLOR_MATH</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ISAT_VALUE_ADD_COLOR_READ</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>STUDENTS_TAKING__ALGEBRA__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>STUDENTS_PASSING__ALGEBRA__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9th Grade EXPLORE (2009)</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9th Grade EXPLORE (2010)</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10th Grade PLAN (2009)</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10th Grade PLAN (2010)</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NET_CHANGE_EXPLORE_AND_PLAN</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11th Grade Average ACT (2011)</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NET_CHANGE_PLAN_AND_ACT</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COLLEGE_ELIGIBILITY__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GRADUATION_RATE__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COLLEGE_ENROLLMENT_RATE__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COLLEGE_ENROLLMENT</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GENERAL_SERVICES_ROUTE</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FRESHMAN_ON_TRACK_RATE__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>X_COORDINATE</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Y_COORDINATE</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LATITUDE</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LONGITUDE</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COMMUNITY_AREA_NUMBER</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COMMUNITY_AREA_NAME</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WARD</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>POLICE_DISTRICT</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOCATION</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SCHOOL_ID</td>\n",
       "        <td>INTEGER</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NAME_OF_SCHOOL</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elementary Middle or High School</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>STREET_ADDRESS</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CITY</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>STATE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ZIP_CODE</td>\n",
       "        <td>INTEGER</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PHONE_NUMBER</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LINK</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NETWORK_MANAGER</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COLLABORATIVE_NAME</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ADEQUATE_YEARLY_PROGRESS_MADE_</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TRACK_SCHEDULE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CPS_PERFORMANCE_POLICY_STATUS</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CPS_PERFORMANCE_POLICY_LEVEL</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HEALTHY_SCHOOL_CERTIFIED</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SAFETY_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SAFETY_SCORE</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FAMILY_INVOLVEMENT_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FAMILY_INVOLVEMENT_SCORE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ENVIRONMENT_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ENVIRONMENT_SCORE</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INSTRUCTION_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INSTRUCTION_SCORE</td>\n",
       "        <td>SMALLINT</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LEADERS_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LEADERS_SCORE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TEACHERS_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TEACHERS_SCORE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PARENT_ENGAGEMENT_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PARENT_ENGAGEMENT_SCORE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PARENT_ENVIRONMENT_ICON</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PARENT_ENVIRONMENT_SCORE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AVERAGE_STUDENT_ATTENDANCE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RATE_OF_MISCONDUCTS__PER_100_STUDENTS_</td>\n",
       "        <td>DECIMAL</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AVERAGE_TEACHER_ATTENDANCE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INDIVIDUALIZED_EDUCATION_PROGRAM_COMPLIANCE_RATE</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PK_2_LITERACY__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PK_2_MATH__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR3_5_GRADE_LEVEL_MATH__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR3_5_GRADE_LEVEL_READ__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR3_5_KEEP_PACE_READ__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR3_5_KEEP_PACE_MATH__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR6_8_GRADE_LEVEL_MATH__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR6_8_GRADE_LEVEL_READ__</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GR6_8_KEEP_PACE_MATH_</td>\n",
       "        <td>VARCHAR</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('GR6_8_KEEP_PACE_READ__', 'VARCHAR', 4),\n",
       " ('GR_8_EXPLORE_MATH__', 'VARCHAR', 4),\n",
       " ('GR_8_EXPLORE_READ__', 'VARCHAR', 4),\n",
       " ('ISAT_EXCEEDING_MATH__', 'DECIMAL', 4),\n",
       " ('ISAT_EXCEEDING_READING__', 'DECIMAL', 4),\n",
       " ('ISAT_VALUE_ADD_MATH', 'DECIMAL', 3),\n",
       " ('ISAT_VALUE_ADD_READ', 'DECIMAL', 3),\n",
       " ('ISAT_VALUE_ADD_COLOR_MATH', 'VARCHAR', 6),\n",
       " ('ISAT_VALUE_ADD_COLOR_READ', 'VARCHAR', 6),\n",
       " ('STUDENTS_TAKING__ALGEBRA__', 'VARCHAR', 4),\n",
       " ('STUDENTS_PASSING__ALGEBRA__', 'VARCHAR', 4),\n",
       " ('9th Grade EXPLORE (2009)', 'VARCHAR', 4),\n",
       " ('9th Grade EXPLORE (2010)', 'VARCHAR', 4),\n",
       " ('10th Grade PLAN (2009)', 'VARCHAR', 4),\n",
       " ('10th Grade PLAN (2010)', 'VARCHAR', 4),\n",
       " ('NET_CHANGE_EXPLORE_AND_PLAN', 'VARCHAR', 3),\n",
       " ('11th Grade Average ACT (2011)', 'VARCHAR', 4),\n",
       " ('NET_CHANGE_PLAN_AND_ACT', 'VARCHAR', 3),\n",
       " ('COLLEGE_ELIGIBILITY__', 'VARCHAR', 4),\n",
       " ('GRADUATION_RATE__', 'VARCHAR', 4),\n",
       " ('COLLEGE_ENROLLMENT_RATE__', 'VARCHAR', 4),\n",
       " ('COLLEGE_ENROLLMENT', 'SMALLINT', 2),\n",
       " ('GENERAL_SERVICES_ROUTE', 'SMALLINT', 2),\n",
       " ('FRESHMAN_ON_TRACK_RATE__', 'VARCHAR', 4),\n",
       " ('X_COORDINATE', 'DECIMAL', 13),\n",
       " ('Y_COORDINATE', 'DECIMAL', 13),\n",
       " ('LATITUDE', 'DECIMAL', 18),\n",
       " ('LONGITUDE', 'DECIMAL', 18),\n",
       " ('COMMUNITY_AREA_NUMBER', 'SMALLINT', 2),\n",
       " ('COMMUNITY_AREA_NAME', 'VARCHAR', 22),\n",
       " ('WARD', 'SMALLINT', 2),\n",
       " ('POLICE_DISTRICT', 'SMALLINT', 2),\n",
       " ('LOCATION', 'VARCHAR', 27),\n",
       " ('SCHOOL_ID', 'INTEGER', 4),\n",
       " ('NAME_OF_SCHOOL', 'VARCHAR', 64),\n",
       " ('Elementary Middle or High School', 'VARCHAR', 2),\n",
       " ('STREET_ADDRESS', 'VARCHAR', 29),\n",
       " ('CITY', 'VARCHAR', 7),\n",
       " ('STATE', 'VARCHAR', 2),\n",
       " ('ZIP_CODE', 'INTEGER', 4),\n",
       " ('PHONE_NUMBER', 'VARCHAR', 14),\n",
       " ('LINK', 'VARCHAR', 78),\n",
       " ('NETWORK_MANAGER', 'VARCHAR', 40),\n",
       " ('COLLABORATIVE_NAME', 'VARCHAR', 34),\n",
       " ('ADEQUATE_YEARLY_PROGRESS_MADE_', 'VARCHAR', 3),\n",
       " ('TRACK_SCHEDULE', 'VARCHAR', 12),\n",
       " ('CPS_PERFORMANCE_POLICY_STATUS', 'VARCHAR', 16),\n",
       " ('CPS_PERFORMANCE_POLICY_LEVEL', 'VARCHAR', 15),\n",
       " ('HEALTHY_SCHOOL_CERTIFIED', 'VARCHAR', 3),\n",
       " ('SAFETY_ICON', 'VARCHAR', 11),\n",
       " ('SAFETY_SCORE', 'SMALLINT', 2),\n",
       " ('FAMILY_INVOLVEMENT_ICON', 'VARCHAR', 11),\n",
       " ('FAMILY_INVOLVEMENT_SCORE', 'VARCHAR', 3),\n",
       " ('ENVIRONMENT_ICON', 'VARCHAR', 11),\n",
       " ('ENVIRONMENT_SCORE', 'SMALLINT', 2),\n",
       " ('INSTRUCTION_ICON', 'VARCHAR', 11),\n",
       " ('INSTRUCTION_SCORE', 'SMALLINT', 2),\n",
       " ('LEADERS_ICON', 'VARCHAR', 4),\n",
       " ('LEADERS_SCORE', 'VARCHAR', 3),\n",
       " ('TEACHERS_ICON', 'VARCHAR', 11),\n",
       " ('TEACHERS_SCORE', 'VARCHAR', 3),\n",
       " ('PARENT_ENGAGEMENT_ICON', 'VARCHAR', 7),\n",
       " ('PARENT_ENGAGEMENT_SCORE', 'VARCHAR', 3),\n",
       " ('PARENT_ENVIRONMENT_ICON', 'VARCHAR', 7),\n",
       " ('PARENT_ENVIRONMENT_SCORE', 'VARCHAR', 3),\n",
       " ('AVERAGE_STUDENT_ATTENDANCE', 'VARCHAR', 6),\n",
       " ('RATE_OF_MISCONDUCTS__PER_100_STUDENTS_', 'DECIMAL', 5),\n",
       " ('AVERAGE_TEACHER_ATTENDANCE', 'VARCHAR', 6),\n",
       " ('INDIVIDUALIZED_EDUCATION_PROGRAM_COMPLIANCE_RATE', 'VARCHAR', 7),\n",
       " ('PK_2_LITERACY__', 'VARCHAR', 4),\n",
       " ('PK_2_MATH__', 'VARCHAR', 4),\n",
       " ('GR3_5_GRADE_LEVEL_MATH__', 'VARCHAR', 4),\n",
       " ('GR3_5_GRADE_LEVEL_READ__', 'VARCHAR', 4),\n",
       " ('GR3_5_KEEP_PACE_READ__', 'VARCHAR', 4),\n",
       " ('GR3_5_KEEP_PACE_MATH__', 'VARCHAR', 4),\n",
       " ('GR6_8_GRADE_LEVEL_MATH__', 'VARCHAR', 4),\n",
       " ('GR6_8_GRADE_LEVEL_READ__', 'VARCHAR', 4),\n",
       " ('GR6_8_KEEP_PACE_MATH_', 'VARCHAR', 4)]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT colname, typename, length FROM syscat.columns WHERE tabname = 'SCHOOLS'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### How many Elementary Schools are in the dataset?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>462</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(462,)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM schools WHERE \\\"Elementary, Middle, or High School\\\" = 'ES'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### What is the highest Safety Score?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>max_safety_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(99,)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT MAX(safety_score) as MAX_SAFETY_SCORE FROM schools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Which schools have highest Safety Score?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>safety_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Abraham Lincoln Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alexander Graham Bell Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Annie Keller Elementary Gifted Magnet School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Augustus H Burley Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Edgar Allan Poe Elementary Classical School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Edgebrook Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ellen Mitchell Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James E McDade Elementary Classical School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James G Blaine Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LaSalle Elementary Language Academy</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mary E Courtenay Elementary Language Arts Center</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Northside College Preparatory High School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Northside Learning Center High School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norwood Park Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oriole Park Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sauganash Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stephen Decatur Classical Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Talman Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wildwood Elementary School</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Abraham Lincoln Elementary School', 99),\n",
       " ('Alexander Graham Bell Elementary School', 99),\n",
       " ('Annie Keller Elementary Gifted Magnet School', 99),\n",
       " ('Augustus H Burley Elementary School', 99),\n",
       " ('Edgar Allan Poe Elementary Classical School', 99),\n",
       " ('Edgebrook Elementary School', 99),\n",
       " ('Ellen Mitchell Elementary School', 99),\n",
       " ('James E McDade Elementary Classical School', 99),\n",
       " ('James G Blaine Elementary School', 99),\n",
       " ('LaSalle Elementary Language Academy', 99),\n",
       " ('Mary E Courtenay Elementary Language Arts Center', 99),\n",
       " ('Northside College Preparatory High School', 99),\n",
       " ('Northside Learning Center High School', 99),\n",
       " ('Norwood Park Elementary School', 99),\n",
       " ('Oriole Park Elementary School', 99),\n",
       " ('Sauganash Elementary School', 99),\n",
       " ('Stephen Decatur Classical Elementary School', 99),\n",
       " ('Talman Elementary School', 99),\n",
       " ('Wildwood Elementary School', 99)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name_of_school, safety_score FROM schools \\\n",
    "    WHERE safety_score = (SELECT MAX(safety_score) FROM schools)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### What are the top 10 schools with the highest \"Average Student Attendance\"?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>average_student_attendance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Charles Haines Elementary School</td>\n",
       "        <td>98.40%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James Ward Elementary School</td>\n",
       "        <td>97.80%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Edgar Allan Poe Elementary Classical School</td>\n",
       "        <td>97.60%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orozco Fine Arts &amp; Sciences Elementary School</td>\n",
       "        <td>97.60%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rachel Carson Elementary School</td>\n",
       "        <td>97.60%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Annie Keller Elementary Gifted Magnet School</td>\n",
       "        <td>97.50%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andrew Jackson Elementary Language Academy</td>\n",
       "        <td>97.40%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lenart Elementary Regional Gifted Center</td>\n",
       "        <td>97.40%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Disney II Magnet School</td>\n",
       "        <td>97.30%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John H Vanderpoel Elementary Magnet School</td>\n",
       "        <td>97.20%</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('John Charles Haines Elementary School', '98.40%'),\n",
       " ('James Ward Elementary School', '97.80%'),\n",
       " ('Edgar Allan Poe Elementary Classical School', '97.60%'),\n",
       " ('Orozco Fine Arts & Sciences Elementary School', '97.60%'),\n",
       " ('Rachel Carson Elementary School', '97.60%'),\n",
       " ('Annie Keller Elementary Gifted Magnet School', '97.50%'),\n",
       " ('Andrew Jackson Elementary Language Academy', '97.40%'),\n",
       " ('Lenart Elementary Regional Gifted Center', '97.40%'),\n",
       " ('Disney II Magnet School', '97.30%'),\n",
       " ('John H Vanderpoel Elementary Magnet School', '97.20%')]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name_of_school, average_student_attendance FROM schools \\\n",
    "        ORDER BY average_student_attendance DESC NULLS LAST LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Retrieve the list of 5 Schools with the lowest Average Student Attendance sorted in ascending order based on attendance\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>average_student_attendance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richard T Crane Technical Preparatory High School</td>\n",
       "        <td>57.90%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbara Vick Early Childhood &amp; Family Center</td>\n",
       "        <td>60.90%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dyett High School</td>\n",
       "        <td>62.50%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wendell Phillips Academy High School</td>\n",
       "        <td>63.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orr Academy High School</td>\n",
       "        <td>66.30%</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Richard T Crane Technical Preparatory High School', '57.90%'),\n",
       " ('Barbara Vick Early Childhood & Family Center', '60.90%'),\n",
       " ('Dyett High School', '62.50%'),\n",
       " ('Wendell Phillips Academy High School', '63.00%'),\n",
       " ('Orr Academy High School', '66.30%')]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name_of_school, average_student_attendance FROM schools \\\n",
    "        ORDER BY average_student_attendance LIMIT 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>average_student_attendance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richard T Crane Technical Preparatory High School</td>\n",
       "        <td>57.90%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbara Vick Early Childhood &amp; Family Center</td>\n",
       "        <td>60.90%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dyett High School</td>\n",
       "        <td>62.50%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wendell Phillips Academy High School</td>\n",
       "        <td>63.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orr Academy High School</td>\n",
       "        <td>66.30%</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Richard T Crane Technical Preparatory High School', '57.90%'),\n",
       " ('Barbara Vick Early Childhood & Family Center', '60.90%'),\n",
       " ('Dyett High School', '62.50%'),\n",
       " ('Wendell Phillips Academy High School', '63.00%'),\n",
       " ('Orr Academy High School', '66.30%')]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Another solution\n",
    "%sql SELECT Name_of_School, Average_Student_Attendance  \\\n",
    "     from SCHOOLS \\\n",
    "     order by Average_Student_Attendance \\\n",
    "     fetch first 5 rows only"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Remove the '%' sign from the above result set for Average Student Attendance column\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>average_student_attendance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richard T Crane Technical Preparatory High School</td>\n",
       "        <td>57.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbara Vick Early Childhood &amp; Family Center</td>\n",
       "        <td>60.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dyett High School</td>\n",
       "        <td>62.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wendell Phillips Academy High School</td>\n",
       "        <td>63.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orr Academy High School</td>\n",
       "        <td>66.30</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Richard T Crane Technical Preparatory High School', '57.90'),\n",
       " ('Barbara Vick Early Childhood & Family Center', '60.90'),\n",
       " ('Dyett High School', '62.50'),\n",
       " ('Wendell Phillips Academy High School', '63.00'),\n",
       " ('Orr Academy High School', '66.30')]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name_of_school, REPLACE(average_student_attendance,'%','') as average_student_attendance FROM schools \\\n",
    "        ORDER BY average_student_attendance LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Which Schools have Average Student Attendance lower than 70%?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>average_student_attendance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richard T Crane Technical Preparatory High School</td>\n",
       "        <td>57.90%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbara Vick Early Childhood &amp; Family Center</td>\n",
       "        <td>60.90%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dyett High School</td>\n",
       "        <td>62.50%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wendell Phillips Academy High School</td>\n",
       "        <td>63.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orr Academy High School</td>\n",
       "        <td>66.30%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Manley Career Academy High School</td>\n",
       "        <td>66.80%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chicago Vocational Career Academy High School</td>\n",
       "        <td>68.80%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Roberto Clemente Community Academy High School</td>\n",
       "        <td>69.60%</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Richard T Crane Technical Preparatory High School', '57.90%'),\n",
       " ('Barbara Vick Early Childhood & Family Center', '60.90%'),\n",
       " ('Dyett High School', '62.50%'),\n",
       " ('Wendell Phillips Academy High School', '63.00%'),\n",
       " ('Orr Academy High School', '66.30%'),\n",
       " ('Manley Career Academy High School', '66.80%'),\n",
       " ('Chicago Vocational Career Academy High School', '68.80%'),\n",
       " ('Roberto Clemente Community Academy High School', '69.60%')]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name_of_school, average_student_attendance FROM schools\\\n",
    "        WHERE DECIMAL(REPLACE(average_student_attendance, '%','')) < '70'\\\n",
    "        ORDER BY average_student_attendance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Get the total College Enrollment for each Community Area\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "        <th>total_college_enrollment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ALBANY PARK</td>\n",
       "        <td>6864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARCHER HEIGHTS</td>\n",
       "        <td>4823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARMOUR SQUARE</td>\n",
       "        <td>1458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ASHBURN</td>\n",
       "        <td>6483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AUBURN GRESHAM</td>\n",
       "        <td>4175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AUSTIN</td>\n",
       "        <td>10933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AVALON PARK</td>\n",
       "        <td>1522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AVONDALE</td>\n",
       "        <td>3640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BELMONT CRAGIN</td>\n",
       "        <td>14386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BEVERLY</td>\n",
       "        <td>1636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BRIDGEPORT</td>\n",
       "        <td>3167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BRIGHTON PARK</td>\n",
       "        <td>9647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BURNSIDE</td>\n",
       "        <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CALUMET HEIGHTS</td>\n",
       "        <td>1568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CHATHAM</td>\n",
       "        <td>5042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CHICAGO LAWN</td>\n",
       "        <td>7086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CLEARING</td>\n",
       "        <td>2085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DOUGLAS</td>\n",
       "        <td>4670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DUNNING</td>\n",
       "        <td>4568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EAST GARFIELD PARK</td>\n",
       "        <td>5337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EAST SIDE</td>\n",
       "        <td>5305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EDGEWATER</td>\n",
       "        <td>4600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EDISON PARK</td>\n",
       "        <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ENGLEWOOD</td>\n",
       "        <td>6832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FOREST GLEN</td>\n",
       "        <td>1431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FULLER PARK</td>\n",
       "        <td>531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GAGE PARK</td>\n",
       "        <td>9915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GARFIELD RIDGE</td>\n",
       "        <td>4552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GRAND BOULEVARD</td>\n",
       "        <td>2809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GREATER GRAND CROSSING</td>\n",
       "        <td>4051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HEGEWISCH</td>\n",
       "        <td>963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HERMOSA</td>\n",
       "        <td>3975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HUMBOLDT PARK</td>\n",
       "        <td>8620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HYDE PARK</td>\n",
       "        <td>1930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>IRVING PARK</td>\n",
       "        <td>7764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JEFFERSON PARK</td>\n",
       "        <td>1755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KENWOOD</td>\n",
       "        <td>4287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LAKE VIEW</td>\n",
       "        <td>7055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LINCOLN PARK</td>\n",
       "        <td>5615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LINCOLN SQUARE</td>\n",
       "        <td>4132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOGAN SQUARE</td>\n",
       "        <td>7351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOOP</td>\n",
       "        <td>871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOWER WEST SIDE</td>\n",
       "        <td>7257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MCKINLEY PARK</td>\n",
       "        <td>1552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MONTCLARE</td>\n",
       "        <td>1317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MORGAN PARK</td>\n",
       "        <td>3271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MOUNT GREENWOOD</td>\n",
       "        <td>2091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NEAR NORTH SIDE</td>\n",
       "        <td>3362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NEAR SOUTH SIDE</td>\n",
       "        <td>1378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NEAR WEST SIDE</td>\n",
       "        <td>7975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NEW CITY</td>\n",
       "        <td>7922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NORTH CENTER</td>\n",
       "        <td>7541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NORTH LAWNDALE</td>\n",
       "        <td>5146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NORTH PARK</td>\n",
       "        <td>4210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NORWOOD PARK</td>\n",
       "        <td>6469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>OAKLAND</td>\n",
       "        <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>OHARE</td>\n",
       "        <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PORTAGE PARK</td>\n",
       "        <td>6954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PULLMAN</td>\n",
       "        <td>1620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RIVERDALE</td>\n",
       "        <td>1547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROGERS PARK</td>\n",
       "        <td>4068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROSELAND</td>\n",
       "        <td>7020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOUTH CHICAGO</td>\n",
       "        <td>4043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOUTH DEERING</td>\n",
       "        <td>1859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOUTH LAWNDALE</td>\n",
       "        <td>14793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOUTH SHORE</td>\n",
       "        <td>4543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>UPTOWN</td>\n",
       "        <td>4388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WASHINGTON HEIGHTS</td>\n",
       "        <td>4006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WASHINGTON PARK</td>\n",
       "        <td>2648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEST ELSDON</td>\n",
       "        <td>3700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEST ENGLEWOOD</td>\n",
       "        <td>5946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEST GARFIELD PARK</td>\n",
       "        <td>2622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEST LAWN</td>\n",
       "        <td>4207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEST PULLMAN</td>\n",
       "        <td>3240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEST RIDGE</td>\n",
       "        <td>8197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEST TOWN</td>\n",
       "        <td>9429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WOODLAWN</td>\n",
       "        <td>4206</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ALBANY PARK', 6864),\n",
       " ('ARCHER HEIGHTS', 4823),\n",
       " ('ARMOUR SQUARE', 1458),\n",
       " ('ASHBURN', 6483),\n",
       " ('AUBURN GRESHAM', 4175),\n",
       " ('AUSTIN', 10933),\n",
       " ('AVALON PARK', 1522),\n",
       " ('AVONDALE', 3640),\n",
       " ('BELMONT CRAGIN', 14386),\n",
       " ('BEVERLY', 1636),\n",
       " ('BRIDGEPORT', 3167),\n",
       " ('BRIGHTON PARK', 9647),\n",
       " ('BURNSIDE', 549),\n",
       " ('CALUMET HEIGHTS', 1568),\n",
       " ('CHATHAM', 5042),\n",
       " ('CHICAGO LAWN', 7086),\n",
       " ('CLEARING', 2085),\n",
       " ('DOUGLAS', 4670),\n",
       " ('DUNNING', 4568),\n",
       " ('EAST GARFIELD PARK', 5337),\n",
       " ('EAST SIDE', 5305),\n",
       " ('EDGEWATER', 4600),\n",
       " ('EDISON PARK', 910),\n",
       " ('ENGLEWOOD', 6832),\n",
       " ('FOREST GLEN', 1431),\n",
       " ('FULLER PARK', 531),\n",
       " ('GAGE PARK', 9915),\n",
       " ('GARFIELD RIDGE', 4552),\n",
       " ('GRAND BOULEVARD', 2809),\n",
       " ('GREATER GRAND CROSSING', 4051),\n",
       " ('HEGEWISCH', 963),\n",
       " ('HERMOSA', 3975),\n",
       " ('HUMBOLDT PARK', 8620),\n",
       " ('HYDE PARK', 1930),\n",
       " ('IRVING PARK', 7764),\n",
       " ('JEFFERSON PARK', 1755),\n",
       " ('KENWOOD', 4287),\n",
       " ('LAKE VIEW', 7055),\n",
       " ('LINCOLN PARK', 5615),\n",
       " ('LINCOLN SQUARE', 4132),\n",
       " ('LOGAN SQUARE', 7351),\n",
       " ('LOOP', 871),\n",
       " ('LOWER WEST SIDE', 7257),\n",
       " ('MCKINLEY PARK', 1552),\n",
       " ('MONTCLARE', 1317),\n",
       " ('MORGAN PARK', 3271),\n",
       " ('MOUNT GREENWOOD', 2091),\n",
       " ('NEAR NORTH SIDE', 3362),\n",
       " ('NEAR SOUTH SIDE', 1378),\n",
       " ('NEAR WEST SIDE', 7975),\n",
       " ('NEW CITY', 7922),\n",
       " ('NORTH CENTER', 7541),\n",
       " ('NORTH LAWNDALE', 5146),\n",
       " ('NORTH PARK', 4210),\n",
       " ('NORWOOD PARK', 6469),\n",
       " ('OAKLAND', 140),\n",
       " ('OHARE', 786),\n",
       " ('PORTAGE PARK', 6954),\n",
       " ('PULLMAN', 1620),\n",
       " ('RIVERDALE', 1547),\n",
       " ('ROGERS PARK', 4068),\n",
       " ('ROSELAND', 7020),\n",
       " ('SOUTH CHICAGO', 4043),\n",
       " ('SOUTH DEERING', 1859),\n",
       " ('SOUTH LAWNDALE', 14793),\n",
       " ('SOUTH SHORE', 4543),\n",
       " ('UPTOWN', 4388),\n",
       " ('WASHINGTON HEIGHTS', 4006),\n",
       " ('WASHINGTON PARK', 2648),\n",
       " ('WEST ELSDON', 3700),\n",
       " ('WEST ENGLEWOOD', 5946),\n",
       " ('WEST GARFIELD PARK', 2622),\n",
       " ('WEST LAWN', 4207),\n",
       " ('WEST PULLMAN', 3240),\n",
       " ('WEST RIDGE', 8197),\n",
       " ('WEST TOWN', 9429),\n",
       " ('WOODLAWN', 4206)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name, SUM(college_enrollment) as total_college_enrollment FROM schools\\\n",
    "        GROUP BY community_area_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Get the 5 Community Areas with the least total College Enrollment  sorted in ascending order\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "        <th>total_college_enrollment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>OAKLAND</td>\n",
       "        <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FULLER PARK</td>\n",
       "        <td>531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BURNSIDE</td>\n",
       "        <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>OHARE</td>\n",
       "        <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOOP</td>\n",
       "        <td>871</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('OAKLAND', 140),\n",
       " ('FULLER PARK', 531),\n",
       " ('BURNSIDE', 549),\n",
       " ('OHARE', 786),\n",
       " ('LOOP', 871)]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT community_area_name, SUM(college_enrollment) as total_college_enrollment FROM schools\\\n",
    "        GROUP BY community_area_name \\\n",
    "        ORDER BY total_college_enrollment LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### List 5 schools with lowest safety score.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>safety_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Edmond Burke Elementary School</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Luke O&#x27;Toole Elementary School</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>George W Tilton Elementary School</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Foster Park Elementary School</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Emil G Hirsch Metropolitan High School</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Edmond Burke Elementary School', 1),\n",
       " (\"Luke O'Toole Elementary School\", 5),\n",
       " ('George W Tilton Elementary School', 6),\n",
       " ('Foster Park Elementary School', 11),\n",
       " ('Emil G Hirsch Metropolitan High School', 13)]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name_of_school, safety_score FROM schools \\\n",
    "        ORDER BY safety_score LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Get the hardship index for the community area which has College Enrollment of 4368\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>colname</th>\n",
       "        <th>typename</th>\n",
       "        <th>length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>index</td>\n",
       "        <td>BIGINT</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CA</td>\n",
       "        <td>DOUBLE</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COMMUNITY_AREA_NAME</td>\n",
       "        <td>CLOB</td>\n",
       "        <td>1048576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PERCENT_OF_HOUSING_CROWDED</td>\n",
       "        <td>DOUBLE</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PERCENT_HOUSEHOLDS_BELOW_POVERTY</td>\n",
       "        <td>DOUBLE</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PERCENT_AGED_16_UNEMPLOYED</td>\n",
       "        <td>DOUBLE</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PERCENT_AGED_25_WITHOUT_HIGH_SCHOOL_DIPLOMA</td>\n",
       "        <td>DOUBLE</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PERCENT_AGED_UNDER_18_OR_OVER_64</td>\n",
       "        <td>DOUBLE</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PER_CAPITA_INCOME_</td>\n",
       "        <td>BIGINT</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HARDSHIP_INDEX</td>\n",
       "        <td>DOUBLE</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('index', 'BIGINT', 8),\n",
       " ('CA', 'DOUBLE', 8),\n",
       " ('COMMUNITY_AREA_NAME', 'CLOB', 1048576),\n",
       " ('PERCENT_OF_HOUSING_CROWDED', 'DOUBLE', 8),\n",
       " ('PERCENT_HOUSEHOLDS_BELOW_POVERTY', 'DOUBLE', 8),\n",
       " ('PERCENT_AGED_16_UNEMPLOYED', 'DOUBLE', 8),\n",
       " ('PERCENT_AGED_25_WITHOUT_HIGH_SCHOOL_DIPLOMA', 'DOUBLE', 8),\n",
       " ('PERCENT_AGED_UNDER_18_OR_OVER_64', 'DOUBLE', 8),\n",
       " ('PER_CAPITA_INCOME_', 'BIGINT', 8),\n",
       " ('HARDSHIP_INDEX', 'DOUBLE', 8)]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT colname, typename, length FROM syscat.columns WHERE tabname = 'CHICAGO_SOCIOECONOMIC_DATA'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "        <th>hardship_index</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NORTH CENTER</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('NORTH CENTER', 6.0)]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SCH.community_area_name, SOC.hardship_index FROM schools SCH, chicago_socioeconomic_data SOC\\\n",
    "        WHERE SCH.community_area_number IN SOC.ca AND SCH.college_enrollment = '4368'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Get the hardship index for the community area which has the school with the  highest enrollment.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "        <th>hardship_index</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NORTH CENTER</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('NORTH CENTER', 6.0)]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SCH.community_area_name, SOC.hardship_index FROM schools SCH, chicago_socioeconomic_data SOC\\\n",
    "        WHERE SCH.community_area_number IN SOC.ca AND SCH.college_enrollment =(SELECT MAX(college_enrollment) FROM schools)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://hhf49271:***@54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:32733/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ca</th>\n",
       "        <th>community_area_name</th>\n",
       "        <th>hardship_index</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5.0</td>\n",
       "        <td>North Center</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5.0, 'North Center', 6.0)]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Anoher solution\n",
    "%sql select ca, community_area_name, hardship_index from chicago_socioeconomic_data \\\n",
    "   where ca in \\\n",
    "   ( select community_area_number from schools order by college_enrollment desc limit 1 )"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Raw Cell Format",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}