Real world data-set using SQL and Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "19739920-967a-4571-bf45-073e4542b762",
   "metadata": {},
   "source": [
    "Connect to the database\n",
    "\n",
    "Let us now load the ipython-sql extension and establish a connection with the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e4b97dc0-237b-47d1-a1c1-1c4a2397932f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# These libraries are pre-installed in SN Labs. If running in another environment please uncomment lines below to install them:\n",
    "# !pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n",
    "# Ensure we don't load_ext with sqlalchemy>=1.4 (incompadible)\n",
    "# !pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24\n",
    "# !pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9f0d9397-467a-4843-9acc-4965bcdd40c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a978939d-782e-49f9-b0bc-d8663afd69d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: prj63437@bludb'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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",
    "\n",
    "%sql ibm_db_sa://prj63437:HHrZHS29hKohRSpb@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb?security=SSL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64b63cb1-187d-4915-b8d6-8de62f17369b",
   "metadata": {},
   "source": [
    "Query the database system catalog to retrieve table metadata\n",
    "\n",
    "You 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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "38dd87f8-15fe-4758-aadd-5a0bf51056d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\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",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>SCHOOLS</td>\n",
       "            <td>PRJ63437</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-04 13:24:31.731323</td>\n",
       "            <td>2022-06-04 13:24:31.731323</td>\n",
       "            <td>2022-06-04 13:24:31.731323</td>\n",
       "            <td>2022-06-04 13:25:15.957140</td>\n",
       "            <td>78</td>\n",
       "            <td>14</td>\n",
       "            <td>5050</td>\n",
       "            <td>566</td>\n",
       "            <td>15</td>\n",
       "            <td>0</td>\n",
       "            <td>16</td>\n",
       "            <td>-1</td>\n",
       "            <td>-1</td>\n",
       "            <td>-1</td>\n",
       "            <td>0</td>\n",
       "            <td>PRJ63437SPACE1</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>0</td>\n",
       "            <td>N</td>\n",
       "            <td>999</td>\n",
       "            <td>0</td>\n",
       "            <td>0.0</td>\n",
       "            <td>843</td>\n",
       "            <td>0.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>0</td>\n",
       "            <td>2022-06-04 13:24:31.731323</td>\n",
       "            <td>0</td>\n",
       "            <td> </td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>N</td>\n",
       "            <td>PRJ63437</td>\n",
       "            <td> </td>\n",
       "            <td> </td>\n",
       "            <td> </td>\n",
       "            <td>2022-06-04</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",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('PRJ63437', 'SCHOOLS', 'PRJ63437', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2022, 6, 4, 13, 24, 31, 731323), datetime.datetime(2022, 6, 4, 13, 24, 31, 731323), datetime.datetime(2022, 6, 4, 13, 24, 31, 731323), datetime.datetime(2022, 6, 4, 13, 25, 15, 957140), 78, 14, 5050, 566, 15, 0, 16, -1, -1, -1, 0, 'PRJ63437SPACE1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', '                                ', None, 'N', ' ', 'F', None, 0, 'N', 999, 0, 0.0, 843, 0.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', 0, datetime.datetime(2022, 6, 4, 13, 24, 31, 731323), 0, ' ', None, None, 'N', 'PRJ63437', ' ', ' ', ' ', datetime.date(2022, 6, 4), ' ', 'N', 'R', 'N', -1.0, None)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#In Db2 the system catalog table called SYSCAT.TABLES contains the table metadata\n",
    "#just query for a specifc table that you want to verify exists in the database\n",
    "\n",
    "%sql select * from SYSCAT.TABLES where TABNAME = 'SCHOOLS'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a7e7b8c2-c02d-4d56-8485-5b543417a24e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>tabschema</th>\n",
       "            <th>tabname</th>\n",
       "            <th>create_time</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>PETSALE</td>\n",
       "            <td>2022-05-24 16:23:49.241192</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>PETRESCUE</td>\n",
       "            <td>2022-05-27 13:40:26.121483</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>INSTRUCTOR</td>\n",
       "            <td>2022-05-29 16:58:09.637538</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>INTERNATIONAL_STUDENT_TEST_SCORES</td>\n",
       "            <td>2022-05-30 16:21:41.121420</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>EMPLOYEES</td>\n",
       "            <td>2022-05-25 13:38:25.259459</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>JOB_HISTORY</td>\n",
       "            <td>2022-05-25 13:38:25.430716</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>JOBS</td>\n",
       "            <td>2022-05-25 13:38:25.680711</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>CHICAGO_SOCIOECONOMIC_DATA</td>\n",
       "            <td>2022-05-31 14:44:25.341772</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>DEPARTMENTS</td>\n",
       "            <td>2022-05-25 13:38:25.882171</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>LOCATIONS</td>\n",
       "            <td>2022-05-25 13:38:26.109664</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRJ63437</td>\n",
       "            <td>SCHOOLS</td>\n",
       "            <td>2022-06-04 13:24:31.731323</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('PRJ63437', 'PETSALE', datetime.datetime(2022, 5, 24, 16, 23, 49, 241192)),\n",
       " ('PRJ63437', 'PETRESCUE', datetime.datetime(2022, 5, 27, 13, 40, 26, 121483)),\n",
       " ('PRJ63437', 'INSTRUCTOR', datetime.datetime(2022, 5, 29, 16, 58, 9, 637538)),\n",
       " ('PRJ63437', 'INTERNATIONAL_STUDENT_TEST_SCORES', datetime.datetime(2022, 5, 30, 16, 21, 41, 121420)),\n",
       " ('PRJ63437', 'EMPLOYEES', datetime.datetime(2022, 5, 25, 13, 38, 25, 259459)),\n",
       " ('PRJ63437', 'JOB_HISTORY', datetime.datetime(2022, 5, 25, 13, 38, 25, 430716)),\n",
       " ('PRJ63437', 'JOBS', datetime.datetime(2022, 5, 25, 13, 38, 25, 680711)),\n",
       " ('PRJ63437', 'CHICAGO_SOCIOECONOMIC_DATA', datetime.datetime(2022, 5, 31, 14, 44, 25, 341772)),\n",
       " ('PRJ63437', 'DEPARTMENTS', datetime.datetime(2022, 5, 25, 13, 38, 25, 882171)),\n",
       " ('PRJ63437', 'LOCATIONS', datetime.datetime(2022, 5, 25, 13, 38, 26, 109664)),\n",
       " ('PRJ63437', 'SCHOOLS', datetime.datetime(2022, 6, 4, 13, 24, 31, 731323))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#%sql select TABSCHEMA, TABNAME, CREATE_TIME from SYSCAT.TABLES where TABSCHEMA='SCHOOLS'\n",
    "\n",
    "#you can retrieve list of all tables where the schema name is not one of the system created ones:\n",
    "\n",
    "%sql select TABSCHEMA, TABNAME, CREATE_TIME from SYSCAT.TABLES \\\n",
    "      where TABSCHEMA not in ('SYSIBM', 'SYSCAT', 'SYSSTAT', 'SYSIBMADM', 'SYSTOOLS', 'SYSPUBLIC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a5fa0377-8522-49fc-97e9-4ee26c5be949",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>1</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>78</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(78,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# type in your query to retrieve the number of columns in the SCHOOLS table\n",
    "#In Db2 the system catalog table called SYSCAT.COLUMNS contains the column metadata\n",
    "\n",
    "%sql SELECT COUNT(*) FROM SYSCAT.COLUMNS WHERE TABNAME='SCHOOLS'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f685aeaf-448a-4e50-b946-9d0751a0eb64",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>colname</th>\n",
       "            <th>typename</th>\n",
       "            <th>length</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "        <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",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('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),\n",
       " ('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)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# type in your query to retrieve all column names in the SCHOOLS table along with their datatypes and length\n",
    "\n",
    "%sql SELECT COLNAME, TYPENAME, LENGTH FROM SYSCAT.COLUMNS WHERE TABNAME='SCHOOLS'\n",
    "\n",
    "#%sql select distinct(NAME), COLTYPE, LENGTH from SYSIBM.SYSCOLUMNS where TBNAME = 'SCHOOLS'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "463c3841-26d5-4531-8936-9509e5628144",
   "metadata": {},
   "source": [
    "1. How many Elementary Schools are in the dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c016029a-b958-4dea-9bb0-e233f89561aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>1</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>462</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(462,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Which column specifies the school type e.g. 'ES', 'MS', 'HS'? (\"Elementary School, Middle School, High School\")\n",
    "\n",
    "#Does the column name have mixed case, spaces or other special characters?\n",
    "#If so, ensure you use double quotes around the \"Name of the Column\"\n",
    "\n",
    "%sql select count(*) from SCHOOLS where \"Elementary, Middle, or High School\" = 'ES'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f6fae51-c279-4ff4-8e6f-69b3389b4f50",
   "metadata": {},
   "source": [
    "What is the highest Safety Score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b2bc782a-d0c7-4436-869a-0cd05373851f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>max_safety_score</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>99</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(99,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Use the MAX() function\n",
    "\n",
    "%sql SELECT MAX(Safety_Score) AS MAX_SAFETY_SCORE FROM SCHOOLS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a4c2ee3-9b54-47a5-a57c-9f001c01bf6e",
   "metadata": {},
   "source": [
    "Which schools have highest Safety Score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1ac1978e-36d9-43b5-9004-05b9cdcaa7a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name_of_school</th>\n",
       "            <th>safety_score</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#In the previous problem we found out that the highest Safety Score is 99, so we can use that as an input in the where clause:\n",
    "\n",
    "%sql SELECT Name_of_School, Safety_Score from SCHOOLS where Safety_Score = 99\n",
    "\n",
    "#%sql select Name_of_School, Safety_Score from SCHOOLS where \\\n",
    "#Safety_Score= (select MAX(Safety_Score) from SCHOOLS)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff963cd5-43bf-419e-945e-a5cc7692afdb",
   "metadata": {},
   "source": [
    "What are the top 10 schools with the highest \"Average Student Attendance\"?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5f04828f-12f6-4ab0-8611-775663a604b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name_of_school</th>\n",
       "            <th>average_student_attendance</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 11,
     "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",
   "id": "a455a695-36af-4eaa-9f98-40051483af13",
   "metadata": {},
   "source": [
    "Retrieve the list of 5 Schools with the lowest Average Student Attendance sorted in ascending order based on attendance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c6d8bbe6-4d0a-4e58-8fdd-e5ce01d23bc7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name_of_school</th>\n",
       "            <th>average_student_attendance</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, Average_Student_Attendance FROM SCHOOLS \\\n",
    "ORDER BY Average_Student_Attendance \\\n",
    "fetch first 5 rows only"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e25fdff7-f8a0-4277-9f4c-e979fdfe2189",
   "metadata": {},
   "source": [
    "Now remove the '%' sign from the above result set for Average Student Attendance column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "909e69c3-4e92-4222-a1db-6bb9f5d8c4a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name_of_school</th>\n",
       "            <th>2</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Use the REPLACE() function to replace '%' with ''\n",
    "\n",
    "%sql SELECT Name_of_School, REPLACE(Average_Student_Attendance, '%', '') \\\n",
    "     from SCHOOLS \\\n",
    "     order by Average_Student_Attendance \\\n",
    "     fetch first 5 rows only"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "342177be-9b7f-4881-829f-035c469ac986",
   "metadata": {},
   "source": [
    "Which Schools have Average Student Attendance lower than 70%?\n",
    "\n",
    "The datatype of the \"Average_Student_Attendance\" column is varchar.\n",
    "So you cannot use it as is in the where clause for a numeric comparison.\n",
    "First use the CAST() function to cast it as a DECIMAL or DOUBLE\n",
    "e.g. CAST(\"Column_Name\" as DOUBLE)\n",
    "or simply: DECIMAL(\"Column_Name\")\n",
    "\n",
    "Don't forget the '%' age sign needs to be removed before casting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "90860fce-a1ff-4185-90dd-62b8fef27486",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name_of_school</th>\n",
       "            <th>average_student_attendance</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, Average_Student_Attendance  \\\n",
    "     from SCHOOLS \\\n",
    "     where CAST ( REPLACE(Average_Student_Attendance, '%', '') AS DOUBLE ) < 70 \\\n",
    "     order by Average_Student_Attendance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "024dd571-3564-44ab-bfe6-aad61b70ee3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name_of_school</th>\n",
       "            <th>average_student_attendance</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, Average_Student_Attendance  \\\n",
    "     from SCHOOLS \\\n",
    "     where DECIMAL ( REPLACE(Average_Student_Attendance, '%', '') ) < 70 \\\n",
    "     order by Average_Student_Attendance"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cfefba1-3ea7-492c-9f6f-0cf7a39b5d42",
   "metadata": {},
   "source": [
    "Get the total College Enrollment for each Community Area\n",
    "\n",
    "Verify the exact name of the Enrollment column in the database\n",
    "Use the SUM() function to add up the Enrollments for each Community Area\n",
    "\n",
    "Don't forget to group by the Community Area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b32f9da6-eddf-4f74-b997-832dbd4302f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_name</th>\n",
       "            <th>total_enrollment</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Community_Area_Name, sum(College_Enrollment) AS TOTAL_ENROLLMENT \\\n",
    "   from SCHOOLS \\\n",
    "   group by Community_Area_Name "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "125d589e-9a86-43d9-8039-a8bed23ee5c5",
   "metadata": {},
   "source": [
    "Get the 5 Community Areas with the least total College Enrollment sorted in ascending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1e7134ea-37ea-4347-bc11-a3a754c82e93",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>community_area_name</th>\n",
       "            <th>total_enrollment</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('OAKLAND', 140),\n",
       " ('FULLER PARK', 531),\n",
       " ('BURNSIDE', 549),\n",
       " ('OHARE', 786),\n",
       " ('LOOP', 871)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Community_Area_Name, sum(College_Enrollment) AS TOTAL_ENROLLMENT \\\n",
    "   from SCHOOLS \\\n",
    "   group by Community_Area_Name \\\n",
    "   order by TOTAL_ENROLLMENT asc \\\n",
    "   fetch first 5 rows only"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f66f8024-b822-48ea-801e-909db11005a2",
   "metadata": {},
   "source": [
    "List 5 schools with lowest safety score."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8d5b2692-f077-498d-bf63-a9ffdd69a5d2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name_of_school</th>\n",
       "            <th>safety_score</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\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",
       "    </tbody>\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": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name_of_school, safety_score \\\n",
    "FROM schools \\\n",
    "ORDER BY safety_score \\\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "258d0288-0b84-4a31-8ad5-c3397151b5f4",
   "metadata": {},
   "source": [
    "Get the hardship index for the community area which has College Enrollment of 4368\n",
    "\n",
    "NOTE: For this solution to work the CHICAGO_SOCIOECONOMIC_DATA table \n",
    "      as created in the last lab of Week 3 should already exist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4d0dfc33-3189-4340-9eaa-3b162b3d3105",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>hardship_index</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>6.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6.0,)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select hardship_index \n",
    "   from chicago_socioeconomic_data CD, schools CPS \n",
    "   where CD.ca = CPS.community_area_number \n",
    "      and college_enrollment = 4368"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80cc20a1-2abd-4d2e-a146-de11d0662218",
   "metadata": {},
   "source": [
    "Get the hardship index for the community area which has the school with the highest enrollment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e1af0270-0fe9-46ca-83c0-0f109da33c65",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://prj63437:***@19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:30699/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ca</th>\n",
       "            <th>community_area_name</th>\n",
       "            <th>hardship_index</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>5.0</td>\n",
       "            <td>North Center</td>\n",
       "            <td>6.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5.0, 'North Center', 6.0)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%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 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "909827c9-9cbc-47ba-abdf-2cf0ecccf635",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}