DB0201EN-Week4-1-1-RealDataPractice-v5_sqlite_Learner.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/Logos/organization_logo/organization_logo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n",
    "</center>\n",
    "\n",
    "# Working with a real world data-set using SQL and Python\n",
    "\n",
    "Estaimted time needed: **30** minutes\n",
    "\n",
    "## Objectives\n",
    "\n",
    "After complting this lab you will be able to:\n",
    "\n",
    "*   Understand the dataset for Chicago Public School level performance\n",
    "*   Store the dataset in SQLite database.\n",
    "*   Retrieve metadata about tables and columns and query data from mixed case columns\n",
    "*   Solve example problems to practice your SQL skills including using built-in database functions\n"
   ]
  },
  {
   "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](https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&cm_mmc=Email_Newsletter-\\_-Developer_Ed%2BTech-\\_-WW_WW-\\_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ)\n",
    "\n",
    "This dataset includes a large number of metrics. Start by familiarizing yourself with the types of metrics in the database: [https://data.cityofchicago.org/api/assets/AAD41A13-BE8A-4E67-B1F5-86E711E09D5F?download=true](https://data.cityofchicago.org/api/assets/AAD41A13-BE8A-4E67-B1F5-86E711E09D5F?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&download=true&cm_mmc=Email_Newsletter-\\_-Developer_Ed%2BTech-\\_-WW_WW-\\_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ)\n",
    "\n",
    "**NOTE**:\n",
    "\n",
    "Do not download the dataset directly from City of Chicago portal. Instead download a static copy which is a more database friendly version from this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoPublicSchools.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\">link</a>.\n",
    "\n",
    "Now review some of its contents.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "\n",
    "Let us now load the ipython-sql  extension and establish a connection with the database\n",
    "\n",
    "##### The syntax for connecting to magic sql using sqllite is\n",
    "\n",
    "**%sql sqlite://DatabaseName**\n",
    "\n",
    "where DatabaseName will be your **.db** file\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"RealWorldData.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @RealWorldData.db'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#connecting to magic sql using sqllite\n",
    "%sql sqlite:///RealWorldData.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store the dataset in a Table\n",
    "\n",
    "##### In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. To analyze the data\n",
    "\n",
    "##### using SQL, it first needs to be stored in the database.\n",
    "\n",
    "##### We will first read the csv files  from the given url  into pandas dataframes\n",
    "\n",
    "##### Next we will be using the  df.to_sql() function to convert each csv file  to a table in sqlite  with the csv data loaded in it.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jupyterlab/conda/envs/python/lib/python3.7/site-packages/pandas/core/generic.py:2615: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  method=method,\n"
     ]
    }
   ],
   "source": [
    "import pandas\n",
    "\n",
    "#read the csv files from the given url into pandas dataframes\n",
    "df = pandas.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCensusData.csv\")\n",
    "#convert each csv file to a table in sqlite with the csv data loaded in it\n",
    "df.to_sql(\"CENSUS_DATA\", con, if_exists='replace', index=False,method=\"multi\")\n",
    "\n",
    "#read the csv files from the given url into pandas dataframes\n",
    "df = pandas.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCrimeData.csv\")\n",
    "#convert each csv file to a table in sqlite with the csv data loaded in it\n",
    "df.to_sql(\"CHICAGO_CRIME_DATA\", con, if_exists='replace', index=False, method=\"multi\")\n",
    "\n",
    "#read the csv files from the given url into pandas dataframes\n",
    "df = pandas.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoPublicSchools.csv\")\n",
    "#convert each csv file to a table in sqlite with the csv data loaded in it\n",
    "df.to_sql(\"CHICAGO_PUBLIC_SCHOOLS_DATA\", con, if_exists='replace', index=False, method=\"multi\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!--\n",
    "\n",
    "import pandas\n",
    "df = pandas.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCensusData.csv\")\n",
    "df.to_sql(\"CENSUS_DATA\", con, if_exists='replace', index=False,method=\"multi\")\n",
    "\n",
    "df = pandas.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCrimeData.csv\")\n",
    "df.to_sql(\"CHICAGO_CRIME_DATA\", con, if_exists='replace', index=False, method=\"multi\")\n",
    "\n",
    "df = pandas.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoPublicSchools.csv\")\n",
    "df.to_sql(\"CHICAGO_PUBLIC_SCHOOLS_DATA\", con, if_exists='replace', index=False, method=\"multi\")\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "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\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>CENSUS_DATA</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHICAGO_CRIME_DATA</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHICAGO_PUBLIC_SCHOOLS_DATA</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CENSUS_DATA',), ('CHICAGO_CRIME_DATA',), ('CHICAGO_PUBLIC_SCHOOLS_DATA',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# type in your query to retrieve list of all tables in the database\n",
    "%sql select name from  sqlite_master where type='table';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for a hint\n",
    "\n",
    "<!--\n",
    "In sqlite the system catalog table called sqlite_master contains the table metadata\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Solution using SQLite:\n",
    "\n",
    "%sql SELECT name FROM sqlite_master WHERE type='table'\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "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?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>count(name)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>78</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(78,)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#type #in your query to retrieve the number of columns #in the SCHOOLS table\n",
    "\n",
    "%sql select count(name) from PRAGMA_TABLE_INFO('CHICAGO_PUBLIC_SCHOOLS_DATA');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Solution SQLite:\n",
    "%sql SELECT count(name) FROM PRAGMA_TABLE_INFO('CHICAGO_PUBLIC_SCHOOLS_DATA');\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now retrieve the the list of columns in SCHOOLS table and their column type (datatype) and length.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name</th>\n",
       "            <th>type</th>\n",
       "            <th>length(type)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>School_ID</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NAME_OF_SCHOOL</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Elementary, Middle, or High School</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Street_Address</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>City</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>State</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ZIP_Code</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Phone_Number</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Link</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Network_Manager</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Collaborative_Name</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Adequate_Yearly_Progress_Made_</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Track_Schedule</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CPS_Performance_Policy_Status</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CPS_Performance_Policy_Level</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HEALTHY_SCHOOL_CERTIFIED</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Safety_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SAFETY_SCORE</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Family_Involvement_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Family_Involvement_Score</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Environment_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Environment_Score</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Instruction_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Instruction_Score</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Leaders_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Leaders_Score</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Teachers_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Teachers_Score</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Parent_Engagement_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Parent_Engagement_Score</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Parent_Environment_Icon</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Parent_Environment_Score</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AVERAGE_STUDENT_ATTENDANCE</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rate_of_Misconducts__per_100_students_</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Average_Teacher_Attendance</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Individualized_Education_Program_Compliance_Rate</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pk_2_Literacy__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Pk_2_Math__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr3_5_Grade_Level_Math__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr3_5_Grade_Level_Read__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr3_5_Keep_Pace_Read__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr3_5_Keep_Pace_Math__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr6_8_Grade_Level_Math__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr6_8_Grade_Level_Read__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr6_8_Keep_Pace_Math_</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr6_8_Keep_Pace_Read__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr_8_Explore_Math__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Gr_8_Explore_Read__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ISAT_Exceeding_Math__</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ISAT_Exceeding_Reading__</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ISAT_Value_Add_Math</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ISAT_Value_Add_Read</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ISAT_Value_Add_Color_Math</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ISAT_Value_Add_Color_Read</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Students_Taking__Algebra__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Students_Passing__Algebra__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9th Grade EXPLORE (2009)</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9th Grade EXPLORE (2010)</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10th Grade PLAN (2009)</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10th Grade PLAN (2010)</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Net_Change_EXPLORE_and_PLAN</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11th Grade Average ACT (2011)</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Net_Change_PLAN_and_ACT</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>College_Eligibility__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Graduation_Rate__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>College_Enrollment_Rate__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>COLLEGE_ENROLLMENT</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>General_Services_Route</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Freshman_on_Track_Rate__</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>X_COORDINATE</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Y_COORDINATE</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latitude</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Longitude</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>COMMUNITY_AREA_NUMBER</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>COMMUNITY_AREA_NAME</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ward</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Police_District</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Location</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('School_ID', 'INTEGER', 7),\n",
       " ('NAME_OF_SCHOOL', 'TEXT', 4),\n",
       " ('Elementary, Middle, or High School', 'TEXT', 4),\n",
       " ('Street_Address', 'TEXT', 4),\n",
       " ('City', 'TEXT', 4),\n",
       " ('State', 'TEXT', 4),\n",
       " ('ZIP_Code', 'INTEGER', 7),\n",
       " ('Phone_Number', 'TEXT', 4),\n",
       " ('Link', 'TEXT', 4),\n",
       " ('Network_Manager', 'TEXT', 4),\n",
       " ('Collaborative_Name', 'TEXT', 4),\n",
       " ('Adequate_Yearly_Progress_Made_', 'TEXT', 4),\n",
       " ('Track_Schedule', 'TEXT', 4),\n",
       " ('CPS_Performance_Policy_Status', 'TEXT', 4),\n",
       " ('CPS_Performance_Policy_Level', 'TEXT', 4),\n",
       " ('HEALTHY_SCHOOL_CERTIFIED', 'TEXT', 4),\n",
       " ('Safety_Icon', 'TEXT', 4),\n",
       " ('SAFETY_SCORE', 'REAL', 4),\n",
       " ('Family_Involvement_Icon', 'TEXT', 4),\n",
       " ('Family_Involvement_Score', 'TEXT', 4),\n",
       " ('Environment_Icon', 'TEXT', 4),\n",
       " ('Environment_Score', 'REAL', 4),\n",
       " ('Instruction_Icon', 'TEXT', 4),\n",
       " ('Instruction_Score', 'REAL', 4),\n",
       " ('Leaders_Icon', 'TEXT', 4),\n",
       " ('Leaders_Score', 'TEXT', 4),\n",
       " ('Teachers_Icon', 'TEXT', 4),\n",
       " ('Teachers_Score', 'TEXT', 4),\n",
       " ('Parent_Engagement_Icon', 'TEXT', 4),\n",
       " ('Parent_Engagement_Score', 'TEXT', 4),\n",
       " ('Parent_Environment_Icon', 'TEXT', 4),\n",
       " ('Parent_Environment_Score', 'TEXT', 4),\n",
       " ('AVERAGE_STUDENT_ATTENDANCE', 'TEXT', 4),\n",
       " ('Rate_of_Misconducts__per_100_students_', 'REAL', 4),\n",
       " ('Average_Teacher_Attendance', 'TEXT', 4),\n",
       " ('Individualized_Education_Program_Compliance_Rate', 'TEXT', 4),\n",
       " ('Pk_2_Literacy__', 'TEXT', 4),\n",
       " ('Pk_2_Math__', 'TEXT', 4),\n",
       " ('Gr3_5_Grade_Level_Math__', 'TEXT', 4),\n",
       " ('Gr3_5_Grade_Level_Read__', 'TEXT', 4),\n",
       " ('Gr3_5_Keep_Pace_Read__', 'TEXT', 4),\n",
       " ('Gr3_5_Keep_Pace_Math__', 'TEXT', 4),\n",
       " ('Gr6_8_Grade_Level_Math__', 'TEXT', 4),\n",
       " ('Gr6_8_Grade_Level_Read__', 'TEXT', 4),\n",
       " ('Gr6_8_Keep_Pace_Math_', 'TEXT', 4),\n",
       " ('Gr6_8_Keep_Pace_Read__', 'TEXT', 4),\n",
       " ('Gr_8_Explore_Math__', 'TEXT', 4),\n",
       " ('Gr_8_Explore_Read__', 'TEXT', 4),\n",
       " ('ISAT_Exceeding_Math__', 'REAL', 4),\n",
       " ('ISAT_Exceeding_Reading__', 'REAL', 4),\n",
       " ('ISAT_Value_Add_Math', 'REAL', 4),\n",
       " ('ISAT_Value_Add_Read', 'REAL', 4),\n",
       " ('ISAT_Value_Add_Color_Math', 'TEXT', 4),\n",
       " ('ISAT_Value_Add_Color_Read', 'TEXT', 4),\n",
       " ('Students_Taking__Algebra__', 'TEXT', 4),\n",
       " ('Students_Passing__Algebra__', 'TEXT', 4),\n",
       " ('9th Grade EXPLORE (2009)', 'TEXT', 4),\n",
       " ('9th Grade EXPLORE (2010)', 'TEXT', 4),\n",
       " ('10th Grade PLAN (2009)', 'TEXT', 4),\n",
       " ('10th Grade PLAN (2010)', 'TEXT', 4),\n",
       " ('Net_Change_EXPLORE_and_PLAN', 'TEXT', 4),\n",
       " ('11th Grade Average ACT (2011)', 'TEXT', 4),\n",
       " ('Net_Change_PLAN_and_ACT', 'TEXT', 4),\n",
       " ('College_Eligibility__', 'TEXT', 4),\n",
       " ('Graduation_Rate__', 'TEXT', 4),\n",
       " ('College_Enrollment_Rate__', 'TEXT', 4),\n",
       " ('COLLEGE_ENROLLMENT', 'INTEGER', 7),\n",
       " ('General_Services_Route', 'INTEGER', 7),\n",
       " ('Freshman_on_Track_Rate__', 'TEXT', 4),\n",
       " ('X_COORDINATE', 'REAL', 4),\n",
       " ('Y_COORDINATE', 'REAL', 4),\n",
       " ('Latitude', 'REAL', 4),\n",
       " ('Longitude', 'REAL', 4),\n",
       " ('COMMUNITY_AREA_NUMBER', 'INTEGER', 7),\n",
       " ('COMMUNITY_AREA_NAME', 'TEXT', 4),\n",
       " ('Ward', 'INTEGER', 7),\n",
       " ('Police_District', 'INTEGER', 7),\n",
       " ('Location', 'TEXT', 4)]"
      ]
     },
     "execution_count": 14,
     "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",
    "%sql select name,type,length(type) from PRAGMA_TABLE_INFO('CHICAGO_PUBLIC_SCHOOLS_DATA');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Solution \n",
    "\n",
    "%sql SELECT name,type,length(type) FROM PRAGMA_TABLE_INFO('CHICAGO_PUBLIC_SCHOOLS_DATA');\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "1.  Is the column name for the \"SCHOOL ID\" attribute in upper or mixed case?\n",
    "2.  What is the name of \"Community Area Name\" column in your table? Does it have spaces?\n",
    "3.  Are there any columns in whose names the spaces and paranthesis (round brackets) have been replaced by the underscore character \"\\_\"?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problems\n",
    "\n",
    "### Problem 1\n",
    "\n",
    "##### How many Elementary Schools are in the dataset?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Elementary, Middle, or High School</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>ES</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MS</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ES',), ('HS',), ('MS',)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct \"Elementary, Middle, or High School\" from CHICAGO_PUBLIC_SCHOOLS_DATA;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>count(*)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>462</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(462,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from CHICAGO_PUBLIC_SCHOOLS_DATA where \"Elementary, Middle, or High School\"='ES';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for a hint\n",
    "\n",
    "<!--\n",
    "Which column specifies the school type e.g. 'ES', 'MS', 'HS'? (\"Elementary School, Middle School, High School\")\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for another hint\n",
    "\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Solution:\n",
    "\n",
    "%sql select count(*) from CHICAGO_PUBLIC_SCHOOLS_DATA where \"Elementary, Middle, or High School\"='ES'\n",
    "\n",
    "Correct answer: 462\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 2\n",
    "\n",
    "##### What is the highest Safety Score?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(99.0,)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "%sql select MAX(\"SAFETY_SCORE\") as  MAX_SAFETY_SCORE from CHICAGO_PUBLIC_SCHOOLS_DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for a hint\n",
    "\n",
    "<!--\n",
    "Use the MAX() function\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Hint:\n",
    "\n",
    "%sql select MAX(Safety_Score) AS MAX_SAFETY_SCORE from CHICAGO_PUBLIC_SCHOOLS_DATA\n",
    "\n",
    "Correct answer: 99\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 3\n",
    "\n",
    "##### Which schools have highest Safety Score?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Alexander Graham Bell Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Annie Keller Elementary Gifted Magnet School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Augustus H Burley Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Edgar Allan Poe Elementary Classical School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Edgebrook Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ellen Mitchell Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>James E McDade Elementary Classical School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>James G Blaine Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LaSalle Elementary Language Academy</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mary E Courtenay Elementary Language Arts Center</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northside College Preparatory High School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northside Learning Center High School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Norwood Park Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oriole Park Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sauganash Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Stephen Decatur Classical Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Talman Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Wildwood Elementary School</td>\n",
       "            <td>99.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Abraham Lincoln Elementary School', 99.0),\n",
       " ('Alexander Graham Bell Elementary School', 99.0),\n",
       " ('Annie Keller Elementary Gifted Magnet School', 99.0),\n",
       " ('Augustus H Burley Elementary School', 99.0),\n",
       " ('Edgar Allan Poe Elementary Classical School', 99.0),\n",
       " ('Edgebrook Elementary School', 99.0),\n",
       " ('Ellen Mitchell Elementary School', 99.0),\n",
       " ('James E McDade Elementary Classical School', 99.0),\n",
       " ('James G Blaine Elementary School', 99.0),\n",
       " ('LaSalle Elementary Language Academy', 99.0),\n",
       " ('Mary E Courtenay Elementary Language Arts Center', 99.0),\n",
       " ('Northside College Preparatory High School', 99.0),\n",
       " ('Northside Learning Center High School', 99.0),\n",
       " ('Norwood Park Elementary School', 99.0),\n",
       " ('Oriole Park Elementary School', 99.0),\n",
       " ('Sauganash Elementary School', 99.0),\n",
       " ('Stephen Decatur Classical Elementary School', 99.0),\n",
       " ('Talman Elementary School', 99.0),\n",
       " ('Wildwood Elementary School', 99.0)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "%sql select \"NAME_OF_SCHOOL\", \"Safety_Score\" from CHICAGO_PUBLIC_SCHOOLS_DATA where \"SAFETY_SCORE\" =(select MAX(\"SAFETY_SCORE\") from CHICAGO_PUBLIC_SCHOOLS_DATA);"
   ]
  },
  {
   "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 CHICAGO_PUBLIC_SCHOOLS_DATA where Safety_Score = 99\n",
    "\n",
    "or, a better way:\n",
    "\n",
    "%sql select Name_of_School, Safety_Score from CHICAGO_PUBLIC_SCHOOLS_DATA where \\\n",
    "  Safety_Score= (select MAX(Safety_Score) from CHICAGO_PUBLIC_SCHOOLS_DATA)\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": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"NAME_OF_SCHOOL\", \"AVERAGE_STUDENT_ATTENDANCE\" from CHICAGO_PUBLIC_SCHOOLS_DATA 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 CHICAGO_PUBLIC_SCHOOLS_DATA \\\n",
    "    order by Average_Student_Attendance desc nulls last limit 10 \n",
    "\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\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"NAME_OF_SCHOOL\", \"AVERAGE_STUDENT_ATTENDANCE\" from CHICAGO_PUBLIC_SCHOOLS_DATA order by Average_Student_Attendance nulls last limit 5;"
   ]
  },
  {
   "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 CHICAGO_PUBLIC_SCHOOLS_DATA \\\n",
    "     order by Average_Student_Attendance \\\n",
    "     LIMIT 5\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\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>NAME_OF_SCHOOL</th>\n",
       "            <th>REPLACE(Average_Student_Attendance, &#x27;%&#x27;, &#x27;&#x27;)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Velma F Thomas Early Childhood Center</td>\n",
       "            <td>None</td>\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",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Velma F Thomas Early Childhood Center', None),\n",
       " ('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')]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, REPLACE(Average_Student_Attendance, '%', '') \\\n",
    "     from CHICAGO_PUBLIC_SCHOOLS_DATA \\\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",
    "\n",
    "-->\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 CHICAGO_PUBLIC_SCHOOLS_DATA \\\n",
    "     order by Average_Student_Attendance \\\n",
    "     LIMIT 5\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 7\n",
    "\n",
    "##### Which Schools have Average Student Attendance lower than 70%?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"Name_of_School\", \"Average_Student_Attendance\" from CHICAGO_PUBLIC_SCHOOLS_DATA where \"Average_Student_Attendance\" <70 order by Average_Student_Attendance;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Name_of_School, Average_Student_Attendance  \\\n",
    "     from CHICAGO_PUBLIC_SCHOOLS_DATA \\\n",
    "     where CAST ( REPLACE(Average_Student_Attendance, '%', '') AS DOUBLE ) < 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",
    "\n",
    "-->\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",
    "-->\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 CHICAGO_PUBLIC_SCHOOLS_DATA \\\n",
    "     where CAST ( REPLACE(Average_Student_Attendance, '%', '') AS DOUBLE ) < 70 \\\n",
    "     order by Average_Student_Attendance\n",
    "     \n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 8\n",
    "\n",
    "##### Get the total College Enrollment for each Community Area\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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",
       "        <tr>\n",
       "            <td>EDISON PARK</td>\n",
       "            <td>910</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HEGEWISCH</td>\n",
       "            <td>963</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MONTCLARE</td>\n",
       "            <td>1317</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NEAR SOUTH SIDE</td>\n",
       "            <td>1378</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FOREST GLEN</td>\n",
       "            <td>1431</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ARMOUR SQUARE</td>\n",
       "            <td>1458</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AVALON PARK</td>\n",
       "            <td>1522</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RIVERDALE</td>\n",
       "            <td>1547</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MCKINLEY PARK</td>\n",
       "            <td>1552</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CALUMET HEIGHTS</td>\n",
       "            <td>1568</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PULLMAN</td>\n",
       "            <td>1620</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BEVERLY</td>\n",
       "            <td>1636</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>JEFFERSON PARK</td>\n",
       "            <td>1755</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SOUTH DEERING</td>\n",
       "            <td>1859</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HYDE PARK</td>\n",
       "            <td>1930</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CLEARING</td>\n",
       "            <td>2085</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MOUNT GREENWOOD</td>\n",
       "            <td>2091</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEST GARFIELD PARK</td>\n",
       "            <td>2622</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WASHINGTON PARK</td>\n",
       "            <td>2648</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GRAND BOULEVARD</td>\n",
       "            <td>2809</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BRIDGEPORT</td>\n",
       "            <td>3167</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEST PULLMAN</td>\n",
       "            <td>3240</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MORGAN PARK</td>\n",
       "            <td>3271</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NEAR NORTH SIDE</td>\n",
       "            <td>3362</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AVONDALE</td>\n",
       "            <td>3640</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEST ELSDON</td>\n",
       "            <td>3700</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HERMOSA</td>\n",
       "            <td>3975</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WASHINGTON HEIGHTS</td>\n",
       "            <td>4006</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SOUTH CHICAGO</td>\n",
       "            <td>4043</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GREATER GRAND CROSSING</td>\n",
       "            <td>4051</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ROGERS PARK</td>\n",
       "            <td>4068</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LINCOLN SQUARE</td>\n",
       "            <td>4132</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AUBURN GRESHAM</td>\n",
       "            <td>4175</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WOODLAWN</td>\n",
       "            <td>4206</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEST LAWN</td>\n",
       "            <td>4207</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NORTH PARK</td>\n",
       "            <td>4210</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>KENWOOD</td>\n",
       "            <td>4287</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UPTOWN</td>\n",
       "            <td>4388</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SOUTH SHORE</td>\n",
       "            <td>4543</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GARFIELD RIDGE</td>\n",
       "            <td>4552</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DUNNING</td>\n",
       "            <td>4568</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EDGEWATER</td>\n",
       "            <td>4600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DOUGLAS</td>\n",
       "            <td>4670</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ARCHER HEIGHTS</td>\n",
       "            <td>4823</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHATHAM</td>\n",
       "            <td>5042</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NORTH LAWNDALE</td>\n",
       "            <td>5146</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EAST SIDE</td>\n",
       "            <td>5305</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EAST GARFIELD PARK</td>\n",
       "            <td>5337</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LINCOLN PARK</td>\n",
       "            <td>5615</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEST ENGLEWOOD</td>\n",
       "            <td>5946</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NORWOOD PARK</td>\n",
       "            <td>6469</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ASHBURN</td>\n",
       "            <td>6483</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ENGLEWOOD</td>\n",
       "            <td>6832</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ALBANY PARK</td>\n",
       "            <td>6864</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PORTAGE PARK</td>\n",
       "            <td>6954</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ROSELAND</td>\n",
       "            <td>7020</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LAKE VIEW</td>\n",
       "            <td>7055</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CHICAGO LAWN</td>\n",
       "            <td>7086</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LOWER WEST SIDE</td>\n",
       "            <td>7257</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LOGAN SQUARE</td>\n",
       "            <td>7351</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NORTH CENTER</td>\n",
       "            <td>7541</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>IRVING PARK</td>\n",
       "            <td>7764</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NEW CITY</td>\n",
       "            <td>7922</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NEAR WEST SIDE</td>\n",
       "            <td>7975</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEST RIDGE</td>\n",
       "            <td>8197</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HUMBOLDT PARK</td>\n",
       "            <td>8620</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WEST TOWN</td>\n",
       "            <td>9429</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BRIGHTON PARK</td>\n",
       "            <td>9647</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>GAGE PARK</td>\n",
       "            <td>9915</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AUSTIN</td>\n",
       "            <td>10933</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BELMONT CRAGIN</td>\n",
       "            <td>14386</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SOUTH LAWNDALE</td>\n",
       "            <td>14793</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('OAKLAND', 140),\n",
       " ('FULLER PARK', 531),\n",
       " ('BURNSIDE', 549),\n",
       " ('OHARE', 786),\n",
       " ('LOOP', 871),\n",
       " ('EDISON PARK', 910),\n",
       " ('HEGEWISCH', 963),\n",
       " ('MONTCLARE', 1317),\n",
       " ('NEAR SOUTH SIDE', 1378),\n",
       " ('FOREST GLEN', 1431),\n",
       " ('ARMOUR SQUARE', 1458),\n",
       " ('AVALON PARK', 1522),\n",
       " ('RIVERDALE', 1547),\n",
       " ('MCKINLEY PARK', 1552),\n",
       " ('CALUMET HEIGHTS', 1568),\n",
       " ('PULLMAN', 1620),\n",
       " ('BEVERLY', 1636),\n",
       " ('JEFFERSON PARK', 1755),\n",
       " ('SOUTH DEERING', 1859),\n",
       " ('HYDE PARK', 1930),\n",
       " ('CLEARING', 2085),\n",
       " ('MOUNT GREENWOOD', 2091),\n",
       " ('WEST GARFIELD PARK', 2622),\n",
       " ('WASHINGTON PARK', 2648),\n",
       " ('GRAND BOULEVARD', 2809),\n",
       " ('BRIDGEPORT', 3167),\n",
       " ('WEST PULLMAN', 3240),\n",
       " ('MORGAN PARK', 3271),\n",
       " ('NEAR NORTH SIDE', 3362),\n",
       " ('AVONDALE', 3640),\n",
       " ('WEST ELSDON', 3700),\n",
       " ('HERMOSA', 3975),\n",
       " ('WASHINGTON HEIGHTS', 4006),\n",
       " ('SOUTH CHICAGO', 4043),\n",
       " ('GREATER GRAND CROSSING', 4051),\n",
       " ('ROGERS PARK', 4068),\n",
       " ('LINCOLN SQUARE', 4132),\n",
       " ('AUBURN GRESHAM', 4175),\n",
       " ('WOODLAWN', 4206),\n",
       " ('WEST LAWN', 4207),\n",
       " ('NORTH PARK', 4210),\n",
       " ('KENWOOD', 4287),\n",
       " ('UPTOWN', 4388),\n",
       " ('SOUTH SHORE', 4543),\n",
       " ('GARFIELD RIDGE', 4552),\n",
       " ('DUNNING', 4568),\n",
       " ('EDGEWATER', 4600),\n",
       " ('DOUGLAS', 4670),\n",
       " ('ARCHER HEIGHTS', 4823),\n",
       " ('CHATHAM', 5042),\n",
       " ('NORTH LAWNDALE', 5146),\n",
       " ('EAST SIDE', 5305),\n",
       " ('EAST GARFIELD PARK', 5337),\n",
       " ('LINCOLN PARK', 5615),\n",
       " ('WEST ENGLEWOOD', 5946),\n",
       " ('NORWOOD PARK', 6469),\n",
       " ('ASHBURN', 6483),\n",
       " ('ENGLEWOOD', 6832),\n",
       " ('ALBANY PARK', 6864),\n",
       " ('PORTAGE PARK', 6954),\n",
       " ('ROSELAND', 7020),\n",
       " ('LAKE VIEW', 7055),\n",
       " ('CHICAGO LAWN', 7086),\n",
       " ('LOWER WEST SIDE', 7257),\n",
       " ('LOGAN SQUARE', 7351),\n",
       " ('NORTH CENTER', 7541),\n",
       " ('IRVING PARK', 7764),\n",
       " ('NEW CITY', 7922),\n",
       " ('NEAR WEST SIDE', 7975),\n",
       " ('WEST RIDGE', 8197),\n",
       " ('HUMBOLDT PARK', 8620),\n",
       " ('WEST TOWN', 9429),\n",
       " ('BRIGHTON PARK', 9647),\n",
       " ('GAGE PARK', 9915),\n",
       " ('AUSTIN', 10933),\n",
       " ('BELMONT CRAGIN', 14386),\n",
       " ('SOUTH LAWNDALE', 14793)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"Community_Area_Name\", SUM(\"COLLEGE_ENROLLMENT\") AS TOTAL_ENROLLMENT from CHICAGO_PUBLIC_SCHOOLS_DATA group by Community_Area_Name order by TOTAL_ENROLLMENT;"
   ]
  },
  {
   "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",
    "-->\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",
    "-->\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 CHICAGO_PUBLIC_SCHOOLS_DATA \\\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\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"Community_Area_Name\", SUM(\"COLLEGE_ENROLLMENT\") AS TOTAL_ENROLLMENT from CHICAGO_PUBLIC_SCHOOLS_DATA group by Community_Area_Name order by TOTAL_ENROLLMENT asc 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",
    "-->\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 CHICAGO_PUBLIC_SCHOOLS_DATA \\\n",
    "   group by Community_Area_Name \\\n",
    "   order by TOTAL_ENROLLMENT asc \\\n",
    "   LIMIT 5 \n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 10\n",
    "\n",
    "##### List 5 schools with lowest safety score.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\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.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke O&#x27;Toole Elementary School</td>\n",
       "            <td>5.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>George W Tilton Elementary School</td>\n",
       "            <td>6.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Foster Park Elementary School</td>\n",
       "            <td>11.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Emil G Hirsch Metropolitan High School</td>\n",
       "            <td>13.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Edmond Burke Elementary School', 1.0),\n",
       " (\"Luke O'Toole Elementary School\", 5.0),\n",
       " ('George W Tilton Elementary School', 6.0),\n",
       " ('Foster Park Elementary School', 11.0),\n",
       " ('Emil G Hirsch Metropolitan High School', 13.0)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"Name_of_School\", \"Safety_Score\" from CHICAGO_PUBLIC_SCHOOLS_DATA where \"safety_score\" !='None' order by \"Safety_Score\" asc nulls last lIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Solution SQLite:\n",
    "\n",
    "%sql SELECT name_of_school, safety_score \\\n",
    "FROM CHICAGO_PUBLIC_SCHOOLS_DATA  where safety_score !='None' \\\n",
    "ORDER BY safety_score \\\n",
    "LIMIT 5\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 11\n",
    "\n",
    "##### Get the hardship index for the community area which has College Enrollment of 4368\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>HARDSHIP_INDEX</th>\n",
       "            <th>COMMUNITY_AREA_NAME</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>6.0</td>\n",
       "            <td>NORTH CENTER</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6.0, 'NORTH CENTER')]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select hardship_index, CPS.COMMUNITY_AREA_NAME from CENSUS_DATA CD, CHICAGO_PUBLIC_SCHOOLS_DATA CPS \n",
    "where CD.community_area_number = CPS.community_area_number \n",
    "and college_enrollment = 4368;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 12\n",
    "\n",
    "##### Get the hardship index for the community area which has the highest value for College Enrollment\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///RealWorldData.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COMMUNITY_AREA_NUMBER</th>\n",
       "            <th>COMMUNITY_AREA_NAME</th>\n",
       "            <th>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": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select community_area_number, community_area_name, hardship_index from CENSUS_DATA \\\n",
    "   where community_area_number in \\\n",
    "   ( select community_area_number from CHICAGO_PUBLIC_SCHOOLS_DATA order by college_enrollment desc limit 1 );"
   ]
  },
  {
   "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.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/ravahuja/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Rav Ahuja</a>\n",
    "\n",
    "## Change Log\n",
    "\n",
    "| Date (YYYY-MM-DD) | Version | Changed By        | Change Description                        |\n",
    "| ----------------- | ------- | ----------------- | ----------------------------------------- |\n",
    "| 2022-03-04        | 2.2     | Lakshmi Holla     | Made changes in markdown cells            |\n",
    "| 2020-11-27        | 2.1     | Sannareddy Ramesh | Modified data sets and added new problems |\n",
    "| 2020-08-28        | 2.0     | Lavanya           | Moved lab to course repo in GitLab        |\n",
    "\n",
    "<hr>\n",
    "\n",
    "## <h3 align=\"center\"> © IBM Corporation 2020. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}