SQL 1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "This notebook shows how to work with a real world dataset using SQL and Python. In this lab you will:\n",
    "1. Understand the dataset for Chicago Public School level performance \n",
    "1. Store the dataset in an Db2 database on IBM Cloud instance\n",
    "1. Retrieve metadata about tables and columns and query data from mixed case columns\n",
    "1. Solve example problems to practice your SQL skills including using built-in database functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Chicago Public Schools - Progress Report Cards (2011-2012) \n",
    "\n",
    "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\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\n",
    "\n",
    "__NOTE__: Do not download the dataset directly from City of Chicago portal. Instead download a more database friendly version from the link below.\n",
    "Now download a static copy of this database and review some of its contents:\n",
    "https://ibm.box.com/shared/static/f9gjvj1gjmxxzycdhplzt01qtz0s7ew7.csv\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store the dataset in a Table\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 as we saw in the previous lab, 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, as indicated in Week 2 Lab 1 Part II__. The only difference with that lab is that in Step 5 of the instructions you will need to click on create \"(+) New Table\" and specify the name of the table you want to create and then click \"Next\". \n",
    "\n",
    "##### Now 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",
    "\n",
    "<a href=\"https://cognitiveclass.ai\"><img src = \"https://ibm.box.com/shared/static/uc4xjh1uxcc78ks1i18v668simioz4es.jpg\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "Let us now load the ipython-sql  extension and establish a connection with the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: wzr90563@BLUDB'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql ibm_db_sa://wzr90563:txzch8klz1m%40rn2s@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/BLUDB"
   ]
  },
  {
   "cell_type": "markdown",
   "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": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>tabschema</th>\n",
       "        <th>tabname</th>\n",
       "        <th>create_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WZR90563</td>\n",
       "        <td>INSTRUCTOR</td>\n",
       "        <td>2020-04-28 12:22:44.745760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WZR90563</td>\n",
       "        <td>JOBS</td>\n",
       "        <td>2020-04-29 09:39:57.497108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WZR90563</td>\n",
       "        <td>DEPARTMENTS</td>\n",
       "        <td>2020-04-29 09:39:57.617248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WZR90563</td>\n",
       "        <td>LOCATIONS</td>\n",
       "        <td>2020-04-29 09:39:57.743860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WZR90563</td>\n",
       "        <td>EMPLOYEES</td>\n",
       "        <td>2020-04-29 09:39:57.255291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WZR90563</td>\n",
       "        <td>JOB_HISTORY</td>\n",
       "        <td>2020-04-29 09:39:57.380645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WZR90563</td>\n",
       "        <td>SCHOOL</td>\n",
       "        <td>2020-05-10 16:35:18.252567</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('WZR90563', 'INSTRUCTOR', datetime.datetime(2020, 4, 28, 12, 22, 44, 745760)),\n",
       " ('WZR90563', 'JOBS', datetime.datetime(2020, 4, 29, 9, 39, 57, 497108)),\n",
       " ('WZR90563', 'DEPARTMENTS', datetime.datetime(2020, 4, 29, 9, 39, 57, 617248)),\n",
       " ('WZR90563', 'LOCATIONS', datetime.datetime(2020, 4, 29, 9, 39, 57, 743860)),\n",
       " ('WZR90563', 'EMPLOYEES', datetime.datetime(2020, 4, 29, 9, 39, 57, 255291)),\n",
       " ('WZR90563', 'JOB_HISTORY', datetime.datetime(2020, 4, 29, 9, 39, 57, 380645)),\n",
       " ('WZR90563', 'SCHOOL', datetime.datetime(2020, 5, 10, 16, 35, 18, 252567))]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select TABSCHEMA, TABNAME, CREATE_TIME from SYSCAT.TABLES \\\n",
    "      where TABSCHEMA not in ('SYSIBM', 'SYSCAT', 'SYSSTAT', 'SYSIBMADM', 'SYSTOOLS', 'SYSPUBLIC')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query the database system catalog to retrieve column metadata\n",
    "\n",
    "##### The SCHOOLS table contains a large number of columns. How many columns does this table have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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": [
       "[(Decimal('78'),)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count (*) from SYSCAT.COLUMNS where TABNAME = 'SCHOOL'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now retrieve the the list of columns in SCHOOLS table and their column type (datatype) and length."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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>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>65</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>30</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>11</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",
       "</table>"
      ],
      "text/plain": [
       "[('School_ID', 'INTEGER', 4),\n",
       " ('NAME_OF_SCHOOL', 'VARCHAR', 65),\n",
       " ('Elementary, Middle, or High School', 'VARCHAR', 2),\n",
       " ('Street_Address', 'VARCHAR', 30),\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', 11),\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": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select COLNAME, TYPENAME, LENGTH from SYSCAT.COLUMNS where TABNAME = 'SCHOOL'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "1. Is the column name for the \"SCHOOL ID\" attribute in upper or mixed case?\n",
    "1. What is the name of \"Community Area Name\" column in your table? Does it have spaces?\n",
    "1. Are there any columns in whose names the spaces and paranthesis (round brackets) have been replaced by the underscore character \"_\"?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problems\n",
    "\n",
    "### Problem 1\n",
    "\n",
    "##### How many Elementary Schools are in the dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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": [
       "[(Decimal('462'),)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from SCHOOL where \"Elementary, Middle, or High School\" = 'ES'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 2\n",
    "\n",
    "##### What is the highest Safety Score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</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) from SCHOOL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 3\n",
    "\n",
    "##### Which schools have highest Safety Score?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Name_of_School, Safety_Score from SCHOOL where Safety_Score= (select MAX(Safety_Score) from SCHOOL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\n",
    "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",
    "or, a better way:\n",
    "\n",
    "%sql select Name_of_School, Safety_Score from SCHOOLS where \\\n",
    "  Safety_Score= (select MAX(Safety_Score) from SCHOOLS)\n",
    "\n",
    "\n",
    "Correct answer: several schools with with Safety Score of 99.\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 4\n",
    "\n",
    "##### What are the top 10 schools with the highest \"Average Student Attendance\"?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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>Rachel Carson Elementary 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>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",
       " ('Rachel Carson Elementary School', '97.60%'),\n",
       " ('Orozco Fine Arts & Sciences 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": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Name_of_School, Average_Student_Attendance from SCHOOL order by Average_Student_Attendance desc nulls last limit 10 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\n",
    "\n",
    "%sql select Name_of_School, Average_Student_Attendance from SCHOOLS \\\n",
    "    order by Average_Student_Attendance desc nulls last limit 10 \n",
    "\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 5\n",
    "\n",
    "##### Retrieve the list of 5 Schools with the lowest Average Student Attendance sorted in ascending order based on attendance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, Average_Student_Attendance  \\\n",
    "     from SCHOOL \\\n",
    "     order by Average_Student_Attendance \\\n",
    "     fetch first 5 rows only"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\n",
    "\n",
    "%sql SELECT Name_of_School, Average_Student_Attendance  \\\n",
    "     from SCHOOLS \\\n",
    "     order by Average_Student_Attendance \\\n",
    "     fetch first 5 rows only\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 6\n",
    "\n",
    "##### Now remove the '%' sign from the above result set for Average Student Attendance column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name_of_school</th>\n",
       "        <th>2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richard T Crane Technical Preparatory High School</td>\n",
       "        <td>57.90TATTI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbara Vick Early Childhood &amp; Family Center</td>\n",
       "        <td>60.90TATTI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dyett High School</td>\n",
       "        <td>62.50TATTI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wendell Phillips Academy High School</td>\n",
       "        <td>63.00TATTI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orr Academy High School</td>\n",
       "        <td>66.30TATTI</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Richard T Crane Technical Preparatory High School', '57.90TATTI'),\n",
       " ('Barbara Vick Early Childhood & Family Center', '60.90TATTI'),\n",
       " ('Dyett High School', '62.50TATTI'),\n",
       " ('Wendell Phillips Academy High School', '63.00TATTI'),\n",
       " ('Orr Academy High School', '66.30TATTI')]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, REPLACE(Average_Student_Attendance, '%', 'TATTI') \\\n",
    "     from SCHOOL \\\n",
    "     order by Average_Student_Attendance \\\n",
    "     limit 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for a hint\n",
    "\n",
    "<!--\n",
    "Use the REPLACE() function to replace '%' with ''\n",
    "See documentation for this function at:\n",
    "https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000843.html\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Hint:\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\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 7\n",
    "\n",
    "##### Which Schools have Average Student Attendance lower than 70%?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, Average_Student_Attendance  \\\n",
    "     from SCHOOL \\\n",
    "     where INT ( REPLACE(Average_Student_Attendance, '%', '') ) < 70 \\\n",
    "     order by Average_Student_Attendance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for a hint\n",
    "\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",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for another hint\n",
    "\n",
    "<!--\n",
    "Don't forget the '%' age sign needs to be removed before casting\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\n",
    "\n",
    "%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\n",
    "     \n",
    "or,\n",
    "\n",
    "%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\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 8\n",
    "\n",
    "##### Get the total College Enrollment for each Community Area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/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": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Community_Area_Name, SUM(College_Enrollment) as Total_College_Enrollment from SCHOOL group by Community_Area_Name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for a hint\n",
    "\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",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for another hint\n",
    "\n",
    "<!--\n",
    "Don't forget to group by the Community Area\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\n",
    "\n",
    "%sql select Community_Area_Name, sum(College_Enrollment) AS TOTAL_ENROLLMENT \\\n",
    "   from SCHOOLS \\\n",
    "   group by Community_Area_Name \n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 9\n",
    "\n",
    "##### Get the 5 Community Areas with the least total College Enrollment  sorted in ascending order "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>community_area_name</th>\n",
       "        <th>total_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": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Community_Area_Name, sum(College_Enrollment) AS TOTAL_ENROLLMENT \\\n",
    "   from SCHOOL \\\n",
    "   group by Community_Area_Name \\\n",
    "   order by TOTAL_ENROLLMENT \\\n",
    "   limit 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for a hint\n",
    "\n",
    "<!--\n",
    "Order the previous query and limit the number of rows you fetch\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\n",
    "\n",
    "%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\n",
    "\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 10\n",
    "\n",
    "##### Get the hardship index for the community area which has College Enrollment of 4638"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://wzr90563:***@dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net:50000/BLUDB\n",
      "(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"WZR90563.CHICAGO_SOCIOECONOMIC_DATA\" is an undefined name.  SQLSTATE=42704 SQLCODE=-204\n",
      "[SQL: select hardship_index \n",
      "   from chicago_socioeconomic_data CD, school CPS \n",
      "   where CD.ca = CPS.community_area_number \n",
      "      and college_enrollment = 4368]\n",
      "(Background on this error at: http://sqlalche.me/e/f405)\n"
     ]
    }
   ],
   "source": [
    "%%sql \n",
    "select hardship_index \n",
    "   from chicago_socioeconomic_data CD, school CPS \n",
    "   where CD.ca = CPS.community_area_number \n",
    "      and college_enrollment = 4368\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\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\n",
    "\n",
    "%%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\n",
    "\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 11\n",
    "\n",
    "##### Get the hardship index for the community area which has the highest value for College Enrollment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "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": "markdown",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "source": [
    "Double-click __here__ for the solution.\n",
    "\n",
    "<!-- Solution:\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\n",
    "\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 )\n",
    "\n",
    "-->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "##### In this lab you learned how to work with a real word dataset using SQL and Python. You learned how to query columns with spaces or special characters in their names and with mixed case names. You also used built in database functions and practiced how to sort, limit, and order result sets, as well as used sub-queries and worked with multiple tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright &copy; 2018 [cognitiveclass.ai](cognitiveclass.ai?utm_source=bducopyrightlink&utm_medium=dswb&utm_campaign=bdu). This notebook and its source code are released under the terms of the [MIT License](https://bigdatauniversity.com/mit-license/).\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.11"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}