DB0201EN-PeerAssign-v5_SQLite.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",
    "<h1 align=center><font size = 5>Assignment: Notebook for Peer Assignment</font></h1>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "Using this Python notebook you will:\n",
    "\n",
    "1.  Understand three Chicago datasets\n",
    "2.  Load the three datasets into three tables in a sqlite database\n",
    "3.  Execute SQL queries to answer assignment questions\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Understand the datasets\n",
    "\n",
    "To complete the assignment problems in this notebook you will be using three datasets that are available on the city of Chicago's Data Portal:\n",
    "\n",
    "1.  <a href=\"https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\">Socioeconomic Indicators in Chicago</a>\n",
    "2.  <a href=\"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\">Chicago Public Schools</a>\n",
    "3.  <a href=\"https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\">Chicago Crime Data</a>\n",
    "\n",
    "### 1. Socioeconomic Indicators in Chicago\n",
    "\n",
    "This dataset contains a selection of six socioeconomic indicators of public health significance and a “hardship index,” for each Chicago community area, for the years 2008 – 2012.\n",
    "\n",
    "A detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:\n",
    "[https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2](https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2?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",
    "### 2. Chicago Public Schools\n",
    "\n",
    "This dataset shows all school level performance data used to create CPS School Report Cards for the 2011-2012 school year. This dataset is provided by the city of Chicago's Data Portal.\n",
    "\n",
    "A detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:\n",
    "[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",
    "### 3. Chicago Crime Data\n",
    "\n",
    "This dataset reflects reported incidents of crime (with the exception of murders where data exists for each victim) that occurred in the City of Chicago from 2001 to present, minus the most recent seven days.\n",
    "\n",
    "A detailed description of this dataset and the original dataset can be obtained from the Chicago Data Portal at:\n",
    "[https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2](https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2?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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "\n",
    "This assignment requires you to have these three tables populated with a subset of the whole datasets.\n",
    "\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the links below to download and save the datasets (.CSV files):\n",
    "\n",
    "*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCensusData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Chicago Census Data</a>\n",
    "\n",
    "*   <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\" target=\"_blank\">Chicago Public Schools</a>\n",
    "\n",
    "*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/data/ChicagoCrimeData.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Chicago Crime Data</a>\n",
    "\n",
    "**NOTE:** Ensure you have downloaded the datasets using the links above instead of directly from the Chicago Data Portal. The versions linked here are subsets of the original datasets and have some of the column names modified to be more database friendly which will make it easier to complete this assignment.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store the datasets in database tables\n",
    "\n",
    "To analyze the data using SQL, it first needs to be loaded into SQLite DB.\n",
    "We will create three tables in as under:\n",
    "\n",
    "1.  **CENSUS_DATA**\n",
    "2.  **CHICAGO_PUBLIC_SCHOOLS**\n",
    "3.  **CHICAGO_CRIME_DATA**\n",
    "\n",
    "Let us now load the ipython-sql  extension and establish a connection with the database\n",
    "\n",
    "*   Here you will be loading the csv files into the pandas Dataframe and then loading the data into the above mentioned sqlite tables.\n",
    "\n",
    "*   Next you will be connecting to the sqlite database  **FinalDB**.\n",
    "\n",
    "Refer to the previous lab for hints .\n",
    "\n",
    "<a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing_SQLite.ipynb?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\">Hands-on Lab: Analyzing a real World Data Set</a>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"FinalDB.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @FinalDB.db'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#connecting to magic sql using sqllite\n",
    "%sql sqlite:///FinalDB.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the csv files downloaded and into labs into pandas dataframes\n",
    "# Use the df.to_sql() function to convert each csv file to a table in sqlite with the csv data loaded in it.\n",
    "\n",
    "import pandas\n",
    "\n",
    "#read the CENSUS_DATA csv file from the given url into pandas dataframes\n",
    "df = pandas.read_csv(\"CENSUS_DATA.csv\")\n",
    "#convert CENSUS_DATA 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\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the CHICAGO_CRIME_DATA csv file from the given url into pandas dataframes\n",
    "df = pandas.read_csv(\"CHICAGO_CRIME_DATA.csv\")\n",
    "#convert CHICAGO_CRIME_DATA 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\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": [
    "#read the CHICAGO_PUBLIC_SCHOOLS csv file from the given url into pandas dataframes\n",
    "df = pandas.read_csv(\"CHICAGO_PUBLIC_SCHOOLS.csv\")\n",
    "#convert CHICAGO_PUBLIC_SCHOOLS 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\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.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": 9,
     "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": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.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>COMMUNITY_AREA_NUMBER</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</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>PERCENT_OF_HOUSING_CROWDED</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PERCENT_HOUSEHOLDS_BELOW_POVERTY</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PERCENT_AGED_16__UNEMPLOYED</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PERCENT_AGED_25__WITHOUT_HIGH_SCHOOL_DIPLOMA</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PERCENT_AGED_UNDER_18_OR_OVER_64</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PER_CAPITA_INCOME</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HARDSHIP_INDEX</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('COMMUNITY_AREA_NUMBER', 'REAL', 4),\n",
       " ('COMMUNITY_AREA_NAME', 'TEXT', 4),\n",
       " ('PERCENT_OF_HOUSING_CROWDED', 'REAL', 4),\n",
       " ('PERCENT_HOUSEHOLDS_BELOW_POVERTY', 'REAL', 4),\n",
       " ('PERCENT_AGED_16__UNEMPLOYED', 'REAL', 4),\n",
       " ('PERCENT_AGED_25__WITHOUT_HIGH_SCHOOL_DIPLOMA', 'REAL', 4),\n",
       " ('PERCENT_AGED_UNDER_18_OR_OVER_64', 'REAL', 4),\n",
       " ('PER_CAPITA_INCOME', 'INTEGER', 7),\n",
       " ('HARDSHIP_INDEX', 'REAL', 4)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retrieve all column names in the 3 tables along with their datatypes and length\n",
    "%sql select name,type,length(type) from PRAGMA_TABLE_INFO('CENSUS_DATA');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.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>ID</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CASE_NUMBER</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DATE</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BLOCK</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>IUCR</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PRIMARY_TYPE</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DESCRIPTION</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>LOCATION_DESCRIPTION</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ARREST</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DOMESTIC</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BEAT</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DISTRICT</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>WARD</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>COMMUNITY_AREA_NUMBER</td>\n",
       "            <td>REAL</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>FBICODE</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>YEAR</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</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>LOCATION</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ID', 'INTEGER', 7),\n",
       " ('CASE_NUMBER', 'TEXT', 4),\n",
       " ('DATE', 'TEXT', 4),\n",
       " ('BLOCK', 'TEXT', 4),\n",
       " ('IUCR', 'TEXT', 4),\n",
       " ('PRIMARY_TYPE', 'TEXT', 4),\n",
       " ('DESCRIPTION', 'TEXT', 4),\n",
       " ('LOCATION_DESCRIPTION', 'TEXT', 4),\n",
       " ('ARREST', 'INTEGER', 7),\n",
       " ('DOMESTIC', 'INTEGER', 7),\n",
       " ('BEAT', 'INTEGER', 7),\n",
       " ('DISTRICT', 'INTEGER', 7),\n",
       " ('WARD', 'REAL', 4),\n",
       " ('COMMUNITY_AREA_NUMBER', 'REAL', 4),\n",
       " ('FBICODE', 'TEXT', 4),\n",
       " ('X_COORDINATE', 'REAL', 4),\n",
       " ('Y_COORDINATE', 'REAL', 4),\n",
       " ('YEAR', 'INTEGER', 7),\n",
       " ('LATITUDE', 'REAL', 4),\n",
       " ('LONGITUDE', 'REAL', 4),\n",
       " ('LOCATION', 'TEXT', 4)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select name,type,length(type) from PRAGMA_TABLE_INFO('CHICAGO_CRIME_DATA');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select name,type,length(type) from PRAGMA_TABLE_INFO('CHICAGO_PUBLIC_SCHOOLS_DATA');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problems\n",
    "\n",
    "Now write and execute SQL queries to solve assignment problems\n",
    "\n",
    "### Problem 1\n",
    "\n",
    "##### Find the total number of crimes recorded in the CRIME table.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.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>533</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(533,)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from CHICAGO_CRIME_DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 2\n",
    "\n",
    "##### List community areas with per capita income less than 11000.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COMMUNITY_AREA_NAME</th>\n",
       "            <th>PER_CAPITA_INCOME</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Riverdale</td>\n",
       "            <td>8201</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South Lawndale</td>\n",
       "            <td>10402</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fuller Park</td>\n",
       "            <td>10432</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West Garfield Park</td>\n",
       "            <td>10934</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Riverdale', 8201),\n",
       " ('South Lawndale', 10402),\n",
       " ('Fuller Park', 10432),\n",
       " ('West Garfield Park', 10934)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"COMMUNITY_AREA_NAME\", \"PER_CAPITA_INCOME\" from CENSUS_DATA where \"PER_CAPITA_INCOME\" < 11000 order by \"PER_CAPITA_INCOME\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 3\n",
    "\n",
    "##### List all case numbers for crimes  involving minors?(children are not considered minors for the purposes of crime analysis)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>CASE_NUMBER</th>\n",
       "            <th>DESCRIPTION</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>HL266884</td>\n",
       "            <td>SELL/GIVE/DEL LIQUOR TO MINOR</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HK238408</td>\n",
       "            <td>ILLEGAL CONSUMPTION BY MINOR</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HL266884', 'SELL/GIVE/DEL LIQUOR TO MINOR'),\n",
       " ('HK238408', 'ILLEGAL CONSUMPTION BY MINOR')]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select CASE_NUMBER, DESCRIPTION from CHICAGO_CRIME_DATA where DESCRIPTION like '%minor%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 4\n",
    "\n",
    "##### List all kidnapping crimes involving a child?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>PRIMARY_TYPE</th>\n",
       "            <th>DESCRIPTION</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>KIDNAPPING</td>\n",
       "            <td>CHILD ABDUCTION/STRANGER</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('KIDNAPPING', 'CHILD ABDUCTION/STRANGER')]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select PRIMARY_TYPE, DESCRIPTION from CHICAGO_CRIME_DATA where PRIMARY_TYPE='KIDNAPPING' and DESCRIPTION like '%child%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 5\n",
    "\n",
    "##### What kinds of crimes were recorded at schools?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>PRIMARY_TYPE</th>\n",
       "            <th>LOCATION_DESCRIPTION</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>BATTERY</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>BATTERY</td>\n",
       "            <td>SCHOOL, PUBLIC, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL DAMAGE</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NARCOTICS</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NARCOTICS</td>\n",
       "            <td>SCHOOL, PUBLIC, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ASSAULT</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CRIMINAL TRESPASS</td>\n",
       "            <td>SCHOOL, PUBLIC, GROUNDS</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PUBLIC PEACE VIOLATION</td>\n",
       "            <td>SCHOOL, PRIVATE, BUILDING</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PUBLIC PEACE VIOLATION</td>\n",
       "            <td>SCHOOL, PUBLIC, BUILDING</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('BATTERY', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('BATTERY', 'SCHOOL, PUBLIC, BUILDING'),\n",
       " ('CRIMINAL DAMAGE', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('NARCOTICS', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('NARCOTICS', 'SCHOOL, PUBLIC, BUILDING'),\n",
       " ('ASSAULT', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('CRIMINAL TRESPASS', 'SCHOOL, PUBLIC, GROUNDS'),\n",
       " ('PUBLIC PEACE VIOLATION', 'SCHOOL, PRIVATE, BUILDING'),\n",
       " ('PUBLIC PEACE VIOLATION', 'SCHOOL, PUBLIC, BUILDING')]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct PRIMARY_TYPE, LOCATION_DESCRIPTION from CHICAGO_CRIME_DATA where LOCATION_DESCRIPTION like '%school%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 6\n",
    "\n",
    "##### List the average safety score for each type of school.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Elementary, Middle, or High School</th>\n",
       "            <th>SAFETY_SCORE</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>ES</td>\n",
       "            <td>49.52038369304557</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HS</td>\n",
       "            <td>49.62352941176471</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MS</td>\n",
       "            <td>48.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ES', 49.52038369304557), ('HS', 49.62352941176471), ('MS', 48.0)]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"Elementary, Middle, or High School\", AVG(\"SAFETY_SCORE\") AS SAFETY_SCORE from CHICAGO_PUBLIC_SCHOOLS_DATA group by \"Elementary, Middle, or High School\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 7\n",
    "\n",
    "##### List 5 community areas with highest % of households below poverty line\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COMMUNITY_AREA_NAME</th>\n",
       "            <th>PERCENT_HOUSEHOLDS_BELOW_POVERTY</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Riverdale</td>\n",
       "            <td>56.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Fuller Park</td>\n",
       "            <td>51.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Englewood</td>\n",
       "            <td>46.6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>North Lawndale</td>\n",
       "            <td>43.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>East Garfield Park</td>\n",
       "            <td>42.4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Riverdale', 56.5),\n",
       " ('Fuller Park', 51.2),\n",
       " ('Englewood', 46.6),\n",
       " ('North Lawndale', 43.1),\n",
       " ('East Garfield Park', 42.4)]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"COMMUNITY_AREA_NAME\", \"PERCENT_HOUSEHOLDS_BELOW_POVERTY\" from CENSUS_DATA order by \"PERCENT_HOUSEHOLDS_BELOW_POVERTY\" desc nulls last limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 8\n",
    "\n",
    "##### Which community area is most crime prone?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>WARD</th>\n",
       "            <th>COMMUNITY_AREA_NUMBER</th>\n",
       "            <th>count(COMMUNITY_AREA_NUMBER)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>29.0</td>\n",
       "            <td>25.0</td>\n",
       "            <td>43</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(29.0, 25.0, 43)]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select WARD, COMMUNITY_AREA_NUMBER, count(COMMUNITY_AREA_NUMBER) from CHICAGO_CRIME_DATA GROUP BY COMMUNITY_AREA_NUMBER ORDER BY COUNT(COMMUNITY_AREA_NUMBER) DESC LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for a hint\n",
    "\n",
    "<!--\n",
    "Query for the 'community area number' that is most crime prone.\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 9\n",
    "\n",
    "##### Use a sub-query to find the name of the community area with highest hardship index\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COMMUNITY_AREA_NAME</th>\n",
       "            <th>HARDSHIP_INDEX</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Riverdale</td>\n",
       "            <td>98.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Riverdale', 98.0)]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"COMMUNITY_AREA_NAME\", \"HARDSHIP_INDEX\" from CENSUS_DATA \\\n",
    "   where \"COMMUNITY_AREA_NAME\" in \\\n",
    "   ( select \"COMMUNITY_AREA_NAME\" from CENSUS_DATA order by \"HARDSHIP_INDEX\" desc limit 1 );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Problem 10\n",
    "\n",
    "##### Use a sub-query to determine the Community Area Name with most number of crimes?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///FinalDB.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COMMUNITY_AREA_NAME</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>South Shore</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('South Shore',)]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"COMMUNITY_AREA_NAME\" from CENSUS_DATA \\\n",
    "   where \"COMMUNITY_AREA_NUMBER\" in \\\n",
    "   (select count(COMMUNITY_AREA_NUMBER) from CHICAGO_CRIME_DATA GROUP BY COMMUNITY_AREA_NUMBER ORDER BY COUNT(COMMUNITY_AREA_NUMBER) DESC LIMIT 1);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright © 2020 This notebook and its source code are released under the terms of the [MIT License](https://bigdatauniversity.com/mit-license?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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author(s)\n",
    "\n",
    "<h4> Hima Vasudevan </h4>\n",
    "<h4> Rav Ahuja </h4>\n",
    "<h4> Ramesh Sannreddy </h4>\n",
    "\n",
    "## Contribtuor(s)\n",
    "\n",
    "<h4> Malika Singla </h4>\n",
    "\n",
    "## Change log\n",
    "\n",
    "| Date       | Version | Changed by        | Change Description                             |\n",
    "| ---------- | ------- | ----------------- | ---------------------------------------------- |\n",
    "| 2022-03-04 | 2.5     | Lakshmi Holla     | Changed markdown.                              |\n",
    "| 2021-05-19 | 2.4     | Lakshmi Holla     | Updated the question                           |\n",
    "| 2021-04-30 | 2.3     | Malika Singla     | Updated the libraries                          |\n",
    "| 2021-01-15 | 2.2     | Rav Ahuja         | Removed problem 11 and fixed changelog         |\n",
    "| 2020-11-25 | 2.1     | Ramesh Sannareddy | Updated the problem statements, and datasets   |\n",
    "| 2020-09-05 | 2.0     | Malika Singla     | Moved lab to course repo in GitLab             |\n",
    "| 2018-07-18 | 1.0     | Rav Ahuja         | Several updates including loading instructions |\n",
    "| 2018-05-04 | 0.1     | Hima Vasudevan    | Created initial version                        |\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
}