Created
January 21, 2020 00:07
-
-
Save colmblueeyes/494ab83ac0b6b00fed8c17081a805c15 to your computer and use it in GitHub Desktop.
Created on Cognitive Class Labs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "cells": [ | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "<a href=\"https://cognitiveclass.ai\"><img src = \"https://ibm.box.com/shared/static/ugcqz6ohbvff804xp84y4kqnvvk3bq1g.png\" width = 300, align = \"center\"></a>\n", | |
| "\n", | |
| "<h1 align=center><font size = 5>Assignment: Notebook for Peer Assignment</font></h1>" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "# Introduction\n", | |
| "\n", | |
| "Using this Python notebook you will:\n", | |
| "1. Understand 3 Chicago datasets \n", | |
| "1. Load the 3 datasets into 3 tables in a Db2 database\n", | |
| "1. Execute SQL queries to answer assignment questions " | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "## Understand the datasets \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", | |
| "1. <a href=\"https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2\">Socioeconomic Indicators in Chicago</a>\n", | |
| "1. <a href=\"https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t\">Chicago Public Schools</a>\n", | |
| "1. <a href=\"https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2\">Chicago Crime Data</a>\n", | |
| "\n", | |
| "### 1. Socioeconomic Indicators in Chicago\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", | |
| "For this assignment you will use a snapshot of this dataset which can be downloaded from:\n", | |
| "https://ibm.box.com/shared/static/05c3415cbfbtfnr2fx4atenb2sd361ze.csv\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\n", | |
| "\n", | |
| "\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", | |
| "For this assignment you will use a snapshot of this dataset which can be downloaded from:\n", | |
| "https://ibm.box.com/shared/static/f9gjvj1gjmxxzycdhplzt01qtz0s7ew7.csv\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\n", | |
| "\n", | |
| "\n", | |
| "\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", | |
| "This dataset is quite large - over 1.5GB in size with over 6.5 million rows. For the purposes of this assignment we will use a much smaller sample of this dataset which can be downloaded from:\n", | |
| "https://ibm.box.com/shared/static/svflyugsr9zbqy5bmowgswqemfpm1x7f.csv\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\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Download the datasets\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", | |
| "1. __CENSUS_DATA:__ https://ibm.box.com/shared/static/05c3415cbfbtfnr2fx4atenb2sd361ze.csv\n", | |
| "1. __CHICAGO_PUBLIC_SCHOOLS__ https://ibm.box.com/shared/static/f9gjvj1gjmxxzycdhplzt01qtz0s7ew7.csv\n", | |
| "1. __CHICAGO_CRIME_DATA:__ https://ibm.box.com/shared/static/svflyugsr9zbqy5bmowgswqemfpm1x7f.csv\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." | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Store the datasets in database tables\n", | |
| "To analyze the data using SQL, it first needs to be stored in the database.\n", | |
| "\n", | |
| "While it is easier to read the dataset into a Pandas dataframe and then PERSIST it into the database as we saw in Week 3 Lab 3, it results in mapping to default datatypes which may not be optimal for SQL querying. For example a long textual field may map to a CLOB instead of a VARCHAR. \n", | |
| "\n", | |
| "Therefore, __it is highly recommended to manually load the table using the database console LOAD tool, as indicated in Week 2 Lab 1 Part II__. The only difference with that lab is that in Step 5 of the instructions you will need to click on create \"(+) New Table\" and specify the name of the table you want to create and then click \"Next\". \n", | |
| "\n", | |
| "<img src = \"https://ibm.box.com/shared/static/uc4xjh1uxcc78ks1i18v668simioz4es.jpg\">\n", | |
| "\n", | |
| "##### Now open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the first dataset, Next create a New Table, and then follow the steps on-screen instructions to load the data. Name the new tables as folows:\n", | |
| "1. __CENSUS_DATA__\n", | |
| "1. __CHICAGO_PUBLIC_SCHOOLS__\n", | |
| "1. __CHICAGO_CRIME_DATA__" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Connect to the database \n", | |
| "Let us first load the SQL extension and establish a connection with the database" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 13, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| "The sql extension is already loaded. To reload it, use:\n", | |
| " %reload_ext sql\n" | |
| ] | |
| } | |
| ], | |
| "source": [ | |
| "%load_ext sql" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "In the next cell enter your db2 connection string. Recall you created Service Credentials for your Db2 instance in first lab in Week 3. From the __uri__ field of your Db2 service credentials copy everything after db2:// (except the double quote at the end) and paste it in the cell below after ibm_db_sa://\n", | |
| "\n", | |
| "<img src =\"https://ibm.box.com/shared/static/hzhkvdyinpupm2wfx49lkr71q9swbpec.jpg\">" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 14, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": [ | |
| "'Connected: xpc61594@BLUDB'" | |
| ] | |
| }, | |
| "execution_count": 14, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "# Remember the connection string is of the format:\n", | |
| "# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name\n", | |
| "# Enter the connection string for your Db2 on Cloud database instance below\n", | |
| "%sql ibm_db_sa://xpc61594:h%405hjn6p7lwnsj57@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "## Problems\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" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 18, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>1</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>533</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[(Decimal('533'),)]" | |
| ] | |
| }, | |
| "execution_count": 18, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "# Rows in Crime table\n", | |
| "%sql select count(*) from CRIME" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 2\n", | |
| "\n", | |
| "##### Retrieve first 10 rows from the CRIME table\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 35, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>id</th>\n", | |
| " <th>case_number</th>\n", | |
| " <th>DATE</th>\n", | |
| " <th>block</th>\n", | |
| " <th>iucr</th>\n", | |
| " <th>primary_type</th>\n", | |
| " <th>description</th>\n", | |
| " <th>location_description</th>\n", | |
| " <th>arrest</th>\n", | |
| " <th>domestic</th>\n", | |
| " <th>beat</th>\n", | |
| " <th>district</th>\n", | |
| " <th>ward</th>\n", | |
| " <th>community_area_number</th>\n", | |
| " <th>fbicode</th>\n", | |
| " <th>x_coordinate</th>\n", | |
| " <th>y_coordinate</th>\n", | |
| " <th>YEAR</th>\n", | |
| " <th>updatedon</th>\n", | |
| " <th>latitude</th>\n", | |
| " <th>longitude</th>\n", | |
| " <th>location</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>3512276</td>\n", | |
| " <td>HK587712</td>\n", | |
| " <td>08/28/2004 05:50:56 PM</td>\n", | |
| " <td>047XX S KEDZIE AVE</td>\n", | |
| " <td>890</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>FROM BUILDING</td>\n", | |
| " <td>SMALL RETAIL STORE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>911</td>\n", | |
| " <td>9</td>\n", | |
| " <td>14</td>\n", | |
| " <td>58</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1155838</td>\n", | |
| " <td>1873050</td>\n", | |
| " <td>2004</td>\n", | |
| " <td>2018-02-10 15:50:01</td>\n", | |
| " <td>41.80744050</td>\n", | |
| " <td>-87.70395585</td>\n", | |
| " <td>(41.8074405, -87.703955849)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>3406613</td>\n", | |
| " <td>HK456306</td>\n", | |
| " <td>06/26/2004 12:40:00 PM</td>\n", | |
| " <td>009XX N CENTRAL PARK AVE</td>\n", | |
| " <td>820</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>$500 AND UNDER</td>\n", | |
| " <td>OTHER</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>1112</td>\n", | |
| " <td>11</td>\n", | |
| " <td>27</td>\n", | |
| " <td>23</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1152206</td>\n", | |
| " <td>1906127</td>\n", | |
| " <td>2004</td>\n", | |
| " <td>2018-02-28 15:56:25</td>\n", | |
| " <td>41.89827996</td>\n", | |
| " <td>-87.71640551</td>\n", | |
| " <td>(41.898279962, -87.716405505)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>8002131</td>\n", | |
| " <td>HT233595</td>\n", | |
| " <td>04/04/2011 05:45:00 AM</td>\n", | |
| " <td>043XX S WABASH AVE</td>\n", | |
| " <td>820</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>$500 AND UNDER</td>\n", | |
| " <td>NURSING HOME/RETIREMENT HOME</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>221</td>\n", | |
| " <td>2</td>\n", | |
| " <td>3</td>\n", | |
| " <td>38</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1177436</td>\n", | |
| " <td>1876313</td>\n", | |
| " <td>2011</td>\n", | |
| " <td>2018-02-10 15:50:01</td>\n", | |
| " <td>41.81593313</td>\n", | |
| " <td>-87.62464213</td>\n", | |
| " <td>(41.815933131, -87.624642127)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>7903289</td>\n", | |
| " <td>HT133522</td>\n", | |
| " <td>12/30/2010 04:30:00 PM</td>\n", | |
| " <td>083XX S KINGSTON AVE</td>\n", | |
| " <td>840</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>FINANCIAL ID THEFT: OVER $300</td>\n", | |
| " <td>RESIDENCE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>423</td>\n", | |
| " <td>4</td>\n", | |
| " <td>7</td>\n", | |
| " <td>46</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1194622</td>\n", | |
| " <td>1850125</td>\n", | |
| " <td>2010</td>\n", | |
| " <td>2018-02-10 15:50:01</td>\n", | |
| " <td>41.74366532</td>\n", | |
| " <td>-87.56246276</td>\n", | |
| " <td>(41.743665322, -87.562462756)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>10402076</td>\n", | |
| " <td>HZ138551</td>\n", | |
| " <td>02/02/2016 07:30:00 PM</td>\n", | |
| " <td>033XX W 66TH ST</td>\n", | |
| " <td>820</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>$500 AND UNDER</td>\n", | |
| " <td>ALLEY</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>831</td>\n", | |
| " <td>8</td>\n", | |
| " <td>15</td>\n", | |
| " <td>66</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1155240</td>\n", | |
| " <td>1860661</td>\n", | |
| " <td>2016</td>\n", | |
| " <td>2018-02-10 15:50:01</td>\n", | |
| " <td>41.77345530</td>\n", | |
| " <td>-87.70648047</td>\n", | |
| " <td>(41.773455295, -87.706480471)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>7732712</td>\n", | |
| " <td>HS540106</td>\n", | |
| " <td>09/29/2010 07:59:00 AM</td>\n", | |
| " <td>006XX W CHICAGO AVE</td>\n", | |
| " <td>810</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>OVER $500</td>\n", | |
| " <td>PARKING LOT/GARAGE(NON.RESID.)</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>1323</td>\n", | |
| " <td>12</td>\n", | |
| " <td>27</td>\n", | |
| " <td>24</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1171668</td>\n", | |
| " <td>1905607</td>\n", | |
| " <td>2010</td>\n", | |
| " <td>2018-02-10 15:50:01</td>\n", | |
| " <td>41.89644677</td>\n", | |
| " <td>-87.64493868</td>\n", | |
| " <td>(41.896446772, -87.644938678)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>10769475</td>\n", | |
| " <td>HZ534771</td>\n", | |
| " <td>11/30/2016 01:15:00 AM</td>\n", | |
| " <td>050XX N KEDZIE AVE</td>\n", | |
| " <td>810</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>OVER $500</td>\n", | |
| " <td>STREET</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>1713</td>\n", | |
| " <td>17</td>\n", | |
| " <td>33</td>\n", | |
| " <td>14</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1154133</td>\n", | |
| " <td>1933314</td>\n", | |
| " <td>2016</td>\n", | |
| " <td>2018-02-10 15:50:01</td>\n", | |
| " <td>41.97284491</td>\n", | |
| " <td>-87.70860008</td>\n", | |
| " <td>(41.972844913, -87.708600079)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>4494340</td>\n", | |
| " <td>HL793243</td>\n", | |
| " <td>12/16/2005 04:45:00 PM</td>\n", | |
| " <td>005XX E PERSHING RD</td>\n", | |
| " <td>860</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>RETAIL THEFT</td>\n", | |
| " <td>GROCERY FOOD STORE</td>\n", | |
| " <td>TRUE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>213</td>\n", | |
| " <td>2</td>\n", | |
| " <td>3</td>\n", | |
| " <td>38</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1180448</td>\n", | |
| " <td>1879234</td>\n", | |
| " <td>2005</td>\n", | |
| " <td>2018-02-28 15:56:25</td>\n", | |
| " <td>41.82387989</td>\n", | |
| " <td>-87.61350386</td>\n", | |
| " <td>(41.823879885, -87.613503857)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>3778925</td>\n", | |
| " <td>HL149610</td>\n", | |
| " <td>01/28/2005 05:00:00 PM</td>\n", | |
| " <td>100XX S WASHTENAW AVE</td>\n", | |
| " <td>810</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>OVER $500</td>\n", | |
| " <td>STREET</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>2211</td>\n", | |
| " <td>22</td>\n", | |
| " <td>19</td>\n", | |
| " <td>72</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1160129</td>\n", | |
| " <td>1838040</td>\n", | |
| " <td>2005</td>\n", | |
| " <td>2018-02-28 15:56:25</td>\n", | |
| " <td>41.71128051</td>\n", | |
| " <td>-87.68917910</td>\n", | |
| " <td>(41.711280513, -87.689179097)</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>3324217</td>\n", | |
| " <td>HK361551</td>\n", | |
| " <td>05/13/2004 02:15:00 PM</td>\n", | |
| " <td>033XX W BELMONT AVE</td>\n", | |
| " <td>820</td>\n", | |
| " <td>THEFT</td>\n", | |
| " <td>$500 AND UNDER</td>\n", | |
| " <td>SMALL RETAIL STORE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>FALSE</td>\n", | |
| " <td>1733</td>\n", | |
| " <td>17</td>\n", | |
| " <td>35</td>\n", | |
| " <td>21</td>\n", | |
| " <td>6</td>\n", | |
| " <td>1153590</td>\n", | |
| " <td>1921084</td>\n", | |
| " <td>2004</td>\n", | |
| " <td>2018-02-28 15:56:25</td>\n", | |
| " <td>41.93929582</td>\n", | |
| " <td>-87.71092344</td>\n", | |
| " <td>(41.939295821, -87.710923442)</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[(3512276, 'HK587712', '08/28/2004 05:50:56 PM', '047XX S KEDZIE AVE', '890', 'THEFT', 'FROM BUILDING', 'SMALL RETAIL STORE', 'FALSE', 'FALSE', 911, 9, 14, 58, '6', 1155838, 1873050, 2004, datetime.datetime(2018, 2, 10, 15, 50, 1), Decimal('41.80744050'), Decimal('-87.70395585'), '(41.8074405, -87.703955849)'),\n", | |
| " (3406613, 'HK456306', '06/26/2004 12:40:00 PM', '009XX N CENTRAL PARK AVE', '820', 'THEFT', '$500 AND UNDER', 'OTHER', 'FALSE', 'FALSE', 1112, 11, 27, 23, '6', 1152206, 1906127, 2004, datetime.datetime(2018, 2, 28, 15, 56, 25), Decimal('41.89827996'), Decimal('-87.71640551'), '(41.898279962, -87.716405505)'),\n", | |
| " (8002131, 'HT233595', '04/04/2011 05:45:00 AM', '043XX S WABASH AVE', '820', 'THEFT', '$500 AND UNDER', 'NURSING HOME/RETIREMENT HOME', 'FALSE', 'FALSE', 221, 2, 3, 38, '6', 1177436, 1876313, 2011, datetime.datetime(2018, 2, 10, 15, 50, 1), Decimal('41.81593313'), Decimal('-87.62464213'), '(41.815933131, -87.624642127)'),\n", | |
| " (7903289, 'HT133522', '12/30/2010 04:30:00 PM', '083XX S KINGSTON AVE', '840', 'THEFT', 'FINANCIAL ID THEFT: OVER $300', 'RESIDENCE', 'FALSE', 'FALSE', 423, 4, 7, 46, '6', 1194622, 1850125, 2010, datetime.datetime(2018, 2, 10, 15, 50, 1), Decimal('41.74366532'), Decimal('-87.56246276'), '(41.743665322, -87.562462756)'),\n", | |
| " (10402076, 'HZ138551', '02/02/2016 07:30:00 PM', '033XX W 66TH ST', '820', 'THEFT', '$500 AND UNDER', 'ALLEY', 'FALSE', 'FALSE', 831, 8, 15, 66, '6', 1155240, 1860661, 2016, datetime.datetime(2018, 2, 10, 15, 50, 1), Decimal('41.77345530'), Decimal('-87.70648047'), '(41.773455295, -87.706480471)'),\n", | |
| " (7732712, 'HS540106', '09/29/2010 07:59:00 AM', '006XX W CHICAGO AVE', '810', 'THEFT', 'OVER $500', 'PARKING LOT/GARAGE(NON.RESID.)', 'FALSE', 'FALSE', 1323, 12, 27, 24, '6', 1171668, 1905607, 2010, datetime.datetime(2018, 2, 10, 15, 50, 1), Decimal('41.89644677'), Decimal('-87.64493868'), '(41.896446772, -87.644938678)'),\n", | |
| " (10769475, 'HZ534771', '11/30/2016 01:15:00 AM', '050XX N KEDZIE AVE', '810', 'THEFT', 'OVER $500', 'STREET', 'FALSE', 'FALSE', 1713, 17, 33, 14, '6', 1154133, 1933314, 2016, datetime.datetime(2018, 2, 10, 15, 50, 1), Decimal('41.97284491'), Decimal('-87.70860008'), '(41.972844913, -87.708600079)'),\n", | |
| " (4494340, 'HL793243', '12/16/2005 04:45:00 PM', '005XX E PERSHING RD', '860', 'THEFT', 'RETAIL THEFT', 'GROCERY FOOD STORE', 'TRUE', 'FALSE', 213, 2, 3, 38, '6', 1180448, 1879234, 2005, datetime.datetime(2018, 2, 28, 15, 56, 25), Decimal('41.82387989'), Decimal('-87.61350386'), '(41.823879885, -87.613503857)'),\n", | |
| " (3778925, 'HL149610', '01/28/2005 05:00:00 PM', '100XX S WASHTENAW AVE', '810', 'THEFT', 'OVER $500', 'STREET', 'FALSE', 'FALSE', 2211, 22, 19, 72, '6', 1160129, 1838040, 2005, datetime.datetime(2018, 2, 28, 15, 56, 25), Decimal('41.71128051'), Decimal('-87.68917910'), '(41.711280513, -87.689179097)'),\n", | |
| " (3324217, 'HK361551', '05/13/2004 02:15:00 PM', '033XX W BELMONT AVE', '820', 'THEFT', '$500 AND UNDER', 'SMALL RETAIL STORE', 'FALSE', 'FALSE', 1733, 17, 35, 21, '6', 1153590, 1921084, 2004, datetime.datetime(2018, 2, 28, 15, 56, 25), Decimal('41.93929582'), Decimal('-87.71092344'), '(41.939295821, -87.710923442)')]" | |
| ] | |
| }, | |
| "execution_count": 35, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from CRIME limit 10" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 3\n", | |
| "\n", | |
| "##### How many crimes involve an arrest?" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 33, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>1</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>163</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[(Decimal('163'),)]" | |
| ] | |
| }, | |
| "execution_count": 33, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select count(*) from CRIME where arrest = TRUE" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 4\n", | |
| "\n", | |
| "##### Which unique types of crimes have been recorded at GAS STATION locations?\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 47, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>primary_type</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>CRIMINAL TRESPASS</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>NARCOTICS</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>ROBBERY</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>THEFT</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('CRIMINAL TRESPASS',), ('NARCOTICS',), ('ROBBERY',), ('THEFT',)]" | |
| ] | |
| }, | |
| "execution_count": 47, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select distinct primary_type from CRIME where location_description = 'GAS STATION'" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "Hint: Which column lists types of crimes e.g. THEFT?" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 5\n", | |
| "\n", | |
| "##### In the CENUS_DATA table list all Community Areas whose names start with the letter ‘B’." | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 54, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>community_area_name</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Belmont Cragin</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Beverly</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Bridgeport</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Brighton Park</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Burnside</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('Belmont Cragin',),\n", | |
| " ('Beverly',),\n", | |
| " ('Bridgeport',),\n", | |
| " ('Brighton Park',),\n", | |
| " ('Burnside',)]" | |
| ] | |
| }, | |
| "execution_count": 54, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select distinct community_area_name from CENSUS where community_area_name like 'B%'" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 6\n", | |
| "\n", | |
| "##### Which schools in Community Areas 10 to 15 are healthy school certified?" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 87, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>School_ID</th>\n", | |
| " <th>name_of_school</th>\n", | |
| " <th>Elementary, Middle, or High School</th>\n", | |
| " <th>Street_Address</th>\n", | |
| " <th>City</th>\n", | |
| " <th>State</th>\n", | |
| " <th>ZIP_Code</th>\n", | |
| " <th>Phone_Number</th>\n", | |
| " <th>Link</th>\n", | |
| " <th>Network_Manager</th>\n", | |
| " <th>Collaborative_Name</th>\n", | |
| " <th>Adequate_Yearly_Progress_Made_</th>\n", | |
| " <th>Track_Schedule</th>\n", | |
| " <th>CPS_Performance_Policy_Status</th>\n", | |
| " <th>CPS_Performance_Policy_Level</th>\n", | |
| " <th>healthy_school_certified</th>\n", | |
| " <th>Safety_Icon</th>\n", | |
| " <th>safety_score</th>\n", | |
| " <th>Family_Involvement_Icon</th>\n", | |
| " <th>Family_Involvement_Score</th>\n", | |
| " <th>Environment_Icon</th>\n", | |
| " <th>Environment_Score</th>\n", | |
| " <th>Instruction_Icon</th>\n", | |
| " <th>Instruction_Score</th>\n", | |
| " <th>Leaders_Icon</th>\n", | |
| " <th>Leaders_Score</th>\n", | |
| " <th>Teachers_Icon</th>\n", | |
| " <th>Teachers_Score</th>\n", | |
| " <th>Parent_Engagement_Icon</th>\n", | |
| " <th>Parent_Engagement_Score</th>\n", | |
| " <th>Parent_Environment_Icon</th>\n", | |
| " <th>Parent_Environment_Score</th>\n", | |
| " <th>average_student_attendance</th>\n", | |
| " <th>Rate_of_Misconducts__per_100_students_</th>\n", | |
| " <th>Average_Teacher_Attendance</th>\n", | |
| " <th>Individualized_Education_Program_Compliance_Rate</th>\n", | |
| " <th>Pk_2_Literacy__</th>\n", | |
| " <th>Pk_2_Math__</th>\n", | |
| " <th>Gr3_5_Grade_Level_Math__</th>\n", | |
| " <th>Gr3_5_Grade_Level_Read__</th>\n", | |
| " <th>Gr3_5_Keep_Pace_Read__</th>\n", | |
| " <th>Gr3_5_Keep_Pace_Math__</th>\n", | |
| " <th>Gr6_8_Grade_Level_Math__</th>\n", | |
| " <th>Gr6_8_Grade_Level_Read__</th>\n", | |
| " <th>Gr6_8_Keep_Pace_Math_</th>\n", | |
| " <th>Gr6_8_Keep_Pace_Read__</th>\n", | |
| " <th>Gr_8_Explore_Math__</th>\n", | |
| " <th>Gr_8_Explore_Read__</th>\n", | |
| " <th>ISAT_Exceeding_Math__</th>\n", | |
| " <th>ISAT_Exceeding_Reading__</th>\n", | |
| " <th>ISAT_Value_Add_Math</th>\n", | |
| " <th>ISAT_Value_Add_Read</th>\n", | |
| " <th>ISAT_Value_Add_Color_Math</th>\n", | |
| " <th>ISAT_Value_Add_Color_Read</th>\n", | |
| " <th>Students_Taking__Algebra__</th>\n", | |
| " <th>Students_Passing__Algebra__</th>\n", | |
| " <th>9th Grade EXPLORE (2009)</th>\n", | |
| " <th>9th Grade EXPLORE (2010)</th>\n", | |
| " <th>10th Grade PLAN (2009)</th>\n", | |
| " <th>10th Grade PLAN (2010)</th>\n", | |
| " <th>Net_Change_EXPLORE_and_PLAN</th>\n", | |
| " <th>11th Grade Average ACT (2011)</th>\n", | |
| " <th>Net_Change_PLAN_and_ACT</th>\n", | |
| " <th>College_Eligibility__</th>\n", | |
| " <th>Graduation_Rate__</th>\n", | |
| " <th>College_Enrollment_Rate__</th>\n", | |
| " <th>college_enrollment</th>\n", | |
| " <th>General_Services_Route</th>\n", | |
| " <th>Freshman_on_Track_Rate__</th>\n", | |
| " <th>x_coordinate</th>\n", | |
| " <th>y_coordinate</th>\n", | |
| " <th>Latitude</th>\n", | |
| " <th>Longitude</th>\n", | |
| " <th>community_area_number</th>\n", | |
| " <th>community_area_name</th>\n", | |
| " <th>Ward</th>\n", | |
| " <th>Police_District</th>\n", | |
| " <th>Location</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>609995</td>\n", | |
| " <td>Rufus M Hitch Elementary School</td>\n", | |
| " <td>ES</td>\n", | |
| " <td>5625 N McVicker Ave</td>\n", | |
| " <td>Chicago</td>\n", | |
| " <td>IL</td>\n", | |
| " <td>60646</td>\n", | |
| " <td>(773) 534-1189</td>\n", | |
| " <td>http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_609995.pdf</td>\n", | |
| " <td>O'Hare Elementary Network</td>\n", | |
| " <td>NORTH-NORTHWEST SIDE COLLABORATIVE</td>\n", | |
| " <td>No</td>\n", | |
| " <td>Standard</td>\n", | |
| " <td>Not on Probation</td>\n", | |
| " <td>Level 1</td>\n", | |
| " <td>Yes</td>\n", | |
| " <td>Strong</td>\n", | |
| " <td>64</td>\n", | |
| " <td>Strong</td>\n", | |
| " <td>77</td>\n", | |
| " <td>Average</td>\n", | |
| " <td>55</td>\n", | |
| " <td>Average</td>\n", | |
| " <td>58</td>\n", | |
| " <td>Strong</td>\n", | |
| " <td>65</td>\n", | |
| " <td>Strong</td>\n", | |
| " <td>70</td>\n", | |
| " <td>Strong</td>\n", | |
| " <td>55</td>\n", | |
| " <td>Strong</td>\n", | |
| " <td>54</td>\n", | |
| " <td>95.10%</td>\n", | |
| " <td>3.7</td>\n", | |
| " <td>96.60%</td>\n", | |
| " <td>98.60%</td>\n", | |
| " <td>73.7</td>\n", | |
| " <td>27.6</td>\n", | |
| " <td>62.2</td>\n", | |
| " <td>51.7</td>\n", | |
| " <td>50</td>\n", | |
| " <td>67.8</td>\n", | |
| " <td>56.5</td>\n", | |
| " <td>54.8</td>\n", | |
| " <td>52</td>\n", | |
| " <td>63.3</td>\n", | |
| " <td>22.9</td>\n", | |
| " <td>37.5</td>\n", | |
| " <td>29.5</td>\n", | |
| " <td>24.7</td>\n", | |
| " <td>-0.1</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>Yellow</td>\n", | |
| " <td>Yellow</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>542</td>\n", | |
| " <td>30</td>\n", | |
| " <td>NDA</td>\n", | |
| " <td>1134940.195</td>\n", | |
| " <td>1937129.571</td>\n", | |
| " <td>41.98367756</td>\n", | |
| " <td>-87.77908614</td>\n", | |
| " <td>10</td>\n", | |
| " <td>NORWOOD PARK</td>\n", | |
| " <td>45</td>\n", | |
| " <td>16</td>\n", | |
| " <td>(41.98367756, -87.77908614)</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[(609995, 'Rufus M Hitch Elementary School', 'ES', '5625 N McVicker Ave', 'Chicago', 'IL', 60646, '(773) 534-1189', 'http://schoolreports.cps.edu/SchoolProgressReport_Eng/Spring2011Eng_609995.pdf', \"O'Hare Elementary Network\", 'NORTH-NORTHWEST SIDE COLLABORATIVE', 'No', 'Standard', 'Not on Probation', 'Level 1', 'Yes', 'Strong', 64, 'Strong', '77', 'Average', 55, 'Average', 58, 'Strong', '65', 'Strong', '70', 'Strong', '55', 'Strong', '54', '95.10%', Decimal('3.7'), '96.60%', '98.60%', '73.7', '27.6', '62.2', '51.7', '50', '67.8', '56.5', '54.8', '52', '63.3', '22.9', '37.5', Decimal('29.5'), Decimal('24.7'), Decimal('-0.1'), Decimal('0.0'), 'Yellow', 'Yellow', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 'NDA', 542, 30, 'NDA', Decimal('1134940.195'), Decimal('1937129.571'), Decimal('41.98367756'), Decimal('-87.77908614'), 10, 'NORWOOD PARK', 45, 16, '(41.98367756, -87.77908614)')]" | |
| ] | |
| }, | |
| "execution_count": 87, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from SCHOOLS \\\n", | |
| " where community_area_number between 10 and 15 \\\n", | |
| " and healthy_school_certified = 'Yes'" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 7\n", | |
| "\n", | |
| "##### What is the average school Safety Score? " | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 68, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>1</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>49.504873</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[(Decimal('49.504873'),)]" | |
| ] | |
| }, | |
| "execution_count": 68, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select avg(safety_score) from SCHOOLS" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 8\n", | |
| "\n", | |
| "##### List the top 5 Community Areas by average College Enrollment [number of students] " | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 86, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>community_area_name</th>\n", | |
| " <th>ae</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>ARCHER HEIGHTS</td>\n", | |
| " <td>2411.500000</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>MONTCLARE</td>\n", | |
| " <td>1317.000000</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>WEST ELSDON</td>\n", | |
| " <td>1233.333333</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>BRIGHTON PARK</td>\n", | |
| " <td>1205.875000</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>BELMONT CRAGIN</td>\n", | |
| " <td>1198.833333</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('ARCHER HEIGHTS', Decimal('2411.500000')),\n", | |
| " ('MONTCLARE', Decimal('1317.000000')),\n", | |
| " ('WEST ELSDON', Decimal('1233.333333')),\n", | |
| " ('BRIGHTON PARK', Decimal('1205.875000')),\n", | |
| " ('BELMONT CRAGIN', Decimal('1198.833333'))]" | |
| ] | |
| }, | |
| "execution_count": 86, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select community_area_name, avg(college_enrollment) ae from SCHOOLS \\\n", | |
| " group by community_area_name order by ae desc \\\n", | |
| " limit 5" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 9\n", | |
| "\n", | |
| "##### Use a sub-query to determine which Community Area has the least value for school Safety Score? " | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 93, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>community_area_name</th>\n", | |
| " <th>safety_score</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>WASHINGTON PARK</td>\n", | |
| " <td>1</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('WASHINGTON PARK', 1)]" | |
| ] | |
| }, | |
| "execution_count": 93, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select community_area_name, safety_score from SCHOOLS \\\n", | |
| " where safety_score = (select min(safety_score) from SCHOOLS)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "### Problem 10\n", | |
| "\n", | |
| "##### [Without using an explicit JOIN operator] Find the Per Capita Income of the Community Area which has a school Safety Score of 1." | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 106, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>community_area_name</th>\n", | |
| " <th>per_capita_income</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Washington Park</td>\n", | |
| " <td>13785</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('Washington Park', 13785)]" | |
| ] | |
| }, | |
| "execution_count": 106, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select community_area_name , per_capita_income from CENSUS \\\n", | |
| " where community_area_name = 'Washington Park'\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": {}, | |
| "source": [ | |
| "Copyright © 2018 [cognitiveclass.ai](cognitiveclass.ai?utm_source=bducopyrightlink&utm_medium=dswb&utm_campaign=bdu). This notebook and its source code are released under the terms of the [MIT License](https://bigdatauniversity.com/mit-license/).\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 111, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://xpc61594:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>tabschema</th>\n", | |
| " <th>tabname</th>\n", | |
| " <th>owner</th>\n", | |
| " <th>ownertype</th>\n", | |
| " <th>TYPE</th>\n", | |
| " <th>status</th>\n", | |
| " <th>base_tabschema</th>\n", | |
| " <th>base_tabname</th>\n", | |
| " <th>rowtypeschema</th>\n", | |
| " <th>rowtypename</th>\n", | |
| " <th>create_time</th>\n", | |
| " <th>alter_time</th>\n", | |
| " <th>invalidate_time</th>\n", | |
| " <th>stats_time</th>\n", | |
| " <th>colcount</th>\n", | |
| " <th>tableid</th>\n", | |
| " <th>tbspaceid</th>\n", | |
| " <th>card</th>\n", | |
| " <th>npages</th>\n", | |
| " <th>mpages</th>\n", | |
| " <th>fpages</th>\n", | |
| " <th>npartitions</th>\n", | |
| " <th>nfiles</th>\n", | |
| " <th>tablesize</th>\n", | |
| " <th>overflow</th>\n", | |
| " <th>tbspace</th>\n", | |
| " <th>index_tbspace</th>\n", | |
| " <th>long_tbspace</th>\n", | |
| " <th>parents</th>\n", | |
| " <th>children</th>\n", | |
| " <th>selfrefs</th>\n", | |
| " <th>keycolumns</th>\n", | |
| " <th>keyindexid</th>\n", | |
| " <th>keyunique</th>\n", | |
| " <th>checkcount</th>\n", | |
| " <th>datacapture</th>\n", | |
| " <th>const_checked</th>\n", | |
| " <th>pmap_id</th>\n", | |
| " <th>partition_mode</th>\n", | |
| " <th>log_attribute</th>\n", | |
| " <th>pctfree</th>\n", | |
| " <th>append_mode</th>\n", | |
| " <th>REFRESH</th>\n", | |
| " <th>refresh_time</th>\n", | |
| " <th>LOCKSIZE</th>\n", | |
| " <th>VOLATILE</th>\n", | |
| " <th>row_format</th>\n", | |
| " <th>property</th>\n", | |
| " <th>statistics_profile</th>\n", | |
| " <th>compression</th>\n", | |
| " <th>rowcompmode</th>\n", | |
| " <th>access_mode</th>\n", | |
| " <th>clustered</th>\n", | |
| " <th>active_blocks</th>\n", | |
| " <th>droprule</th>\n", | |
| " <th>maxfreespacesearch</th>\n", | |
| " <th>avgcompressedrowsize</th>\n", | |
| " <th>avgrowcompressionratio</th>\n", | |
| " <th>avgrowsize</th>\n", | |
| " <th>pctrowscompressed</th>\n", | |
| " <th>logindexbuild</th>\n", | |
| " <th>codepage</th>\n", | |
| " <th>collationschema</th>\n", | |
| " <th>collationname</th>\n", | |
| " <th>collationschema_orderby</th>\n", | |
| " <th>collationname_orderby</th>\n", | |
| " <th>encoding_scheme</th>\n", | |
| " <th>pctpagessaved</th>\n", | |
| " <th>last_regen_time</th>\n", | |
| " <th>secpolicyid</th>\n", | |
| " <th>protectiongranularity</th>\n", | |
| " <th>auditpolicyid</th>\n", | |
| " <th>auditpolicyname</th>\n", | |
| " <th>auditexceptionenabled</th>\n", | |
| " <th>definer</th>\n", | |
| " <th>oncommit</th>\n", | |
| " <th>logged</th>\n", | |
| " <th>onrollback</th>\n", | |
| " <th>lastused</th>\n", | |
| " <th>control</th>\n", | |
| " <th>temporaltype</th>\n", | |
| " <th>tableorg</th>\n", | |
| " <th>extended_row_size</th>\n", | |
| " <th>pctextendedrows</th>\n", | |
| " <th>remarks</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>COH_TAB1</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>U</td>\n", | |
| " <td>T</td>\n", | |
| " <td>N</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>2020-01-20 18:09:50.289689</td>\n", | |
| " <td>2020-01-20 18:09:50.289689</td>\n", | |
| " <td>2020-01-20 18:09:50.289689</td>\n", | |
| " <td>2020-01-20 18:23:28.575289</td>\n", | |
| " <td>5</td>\n", | |
| " <td>4</td>\n", | |
| " <td>770</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>0</td>\n", | |
| " <td>xpc61594space1</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n", | |
| " <td>1</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>R</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>F</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>999</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>None</td>\n", | |
| " <td>1208</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>2020-01-20 18:09:50.289689</td>\n", | |
| " <td>0</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td>2020-01-20</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td>R</td>\n", | |
| " <td>N</td>\n", | |
| " <td>-1.0</td>\n", | |
| " <td>None</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>CENSUS</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>U</td>\n", | |
| " <td>T</td>\n", | |
| " <td>N</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>2020-01-20 22:09:15.638673</td>\n", | |
| " <td>2020-01-20 22:09:15.638673</td>\n", | |
| " <td>2020-01-20 22:09:15.638673</td>\n", | |
| " <td>2020-01-20 22:18:28.809679</td>\n", | |
| " <td>9</td>\n", | |
| " <td>7</td>\n", | |
| " <td>770</td>\n", | |
| " <td>78</td>\n", | |
| " <td>1</td>\n", | |
| " <td>0</td>\n", | |
| " <td>2</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>0</td>\n", | |
| " <td>xpc61594space1</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n", | |
| " <td>1</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>R</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>F</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>999</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>57</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>None</td>\n", | |
| " <td>1208</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>2020-01-20 22:09:15.638673</td>\n", | |
| " <td>0</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td>2020-01-20</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td>R</td>\n", | |
| " <td>N</td>\n", | |
| " <td>-1.0</td>\n", | |
| " <td>None</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>CRIME</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>U</td>\n", | |
| " <td>T</td>\n", | |
| " <td>N</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>2020-01-20 22:21:41.602870</td>\n", | |
| " <td>2020-01-20 22:21:41.602870</td>\n", | |
| " <td>2020-01-20 22:21:41.602870</td>\n", | |
| " <td>2020-01-20 22:28:29.090495</td>\n", | |
| " <td>22</td>\n", | |
| " <td>5</td>\n", | |
| " <td>770</td>\n", | |
| " <td>533</td>\n", | |
| " <td>5</td>\n", | |
| " <td>0</td>\n", | |
| " <td>6</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>0</td>\n", | |
| " <td>xpc61594space1</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n", | |
| " <td>1</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>R</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>F</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>999</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>257</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>None</td>\n", | |
| " <td>1208</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>2020-01-20 22:21:41.602870</td>\n", | |
| " <td>0</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td>0001-01-01</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td>R</td>\n", | |
| " <td>N</td>\n", | |
| " <td>-1.0</td>\n", | |
| " <td>None</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>SCHOOLS</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td>U</td>\n", | |
| " <td>T</td>\n", | |
| " <td>N</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>2020-01-20 22:22:23.168970</td>\n", | |
| " <td>2020-01-20 22:22:23.168970</td>\n", | |
| " <td>2020-01-20 22:22:23.168970</td>\n", | |
| " <td>2020-01-20 22:23:28.928220</td>\n", | |
| " <td>78</td>\n", | |
| " <td>6</td>\n", | |
| " <td>770</td>\n", | |
| " <td>566</td>\n", | |
| " <td>15</td>\n", | |
| " <td>0</td>\n", | |
| " <td>16</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>0</td>\n", | |
| " <td>xpc61594space1</td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY</td>\n", | |
| " <td>1</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>-1</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>R</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td> </td>\n", | |
| " <td>F</td>\n", | |
| " <td>None</td>\n", | |
| " <td>0</td>\n", | |
| " <td>N</td>\n", | |
| " <td>999</td>\n", | |
| " <td>0</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>844</td>\n", | |
| " <td>0.0</td>\n", | |
| " <td>None</td>\n", | |
| " <td>1208</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td>SYSIBM</td>\n", | |
| " <td>IDENTITY</td>\n", | |
| " <td> </td>\n", | |
| " <td>0</td>\n", | |
| " <td>2020-01-20 22:22:23.168970</td>\n", | |
| " <td>0</td>\n", | |
| " <td> </td>\n", | |
| " <td>None</td>\n", | |
| " <td>None</td>\n", | |
| " <td>N</td>\n", | |
| " <td>XPC61594</td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td> </td>\n", | |
| " <td>0001-01-01</td>\n", | |
| " <td> </td>\n", | |
| " <td>N</td>\n", | |
| " <td>R</td>\n", | |
| " <td>N</td>\n", | |
| " <td>-1.0</td>\n", | |
| " <td>None</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('XPC61594', 'COH_TAB1', 'XPC61594', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2020, 1, 20, 18, 9, 50, 289689), datetime.datetime(2020, 1, 20, 18, 9, 50, 289689), datetime.datetime(2020, 1, 20, 18, 9, 50, 289689), datetime.datetime(2020, 1, 20, 18, 23, 28, 575289), 5, 4, 770, 0, 0, 0, 1, -1, -1, -1, 0, 'xpc61594space1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', ' ', None, 'N', ' ', 'F', None, 0, 'N', 999, 0, 0.0, 0, 0.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', 0, datetime.datetime(2020, 1, 20, 18, 9, 50, 289689), 0, ' ', None, None, 'N', 'XPC61594', ' ', ' ', ' ', datetime.date(2020, 1, 20), ' ', 'N', 'R', 'N', -1.0, None),\n", | |
| " ('XPC61594', 'CENSUS', 'XPC61594', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2020, 1, 20, 22, 9, 15, 638673), datetime.datetime(2020, 1, 20, 22, 9, 15, 638673), datetime.datetime(2020, 1, 20, 22, 9, 15, 638673), datetime.datetime(2020, 1, 20, 22, 18, 28, 809679), 9, 7, 770, 78, 1, 0, 2, -1, -1, -1, 0, 'xpc61594space1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', ' ', None, 'N', ' ', 'F', None, 0, 'N', 999, 0, 0.0, 57, 0.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', 0, datetime.datetime(2020, 1, 20, 22, 9, 15, 638673), 0, ' ', None, None, 'N', 'XPC61594', ' ', ' ', ' ', datetime.date(2020, 1, 20), ' ', 'N', 'R', 'N', -1.0, None),\n", | |
| " ('XPC61594', 'CRIME', 'XPC61594', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2020, 1, 20, 22, 21, 41, 602870), datetime.datetime(2020, 1, 20, 22, 21, 41, 602870), datetime.datetime(2020, 1, 20, 22, 21, 41, 602870), datetime.datetime(2020, 1, 20, 22, 28, 29, 90495), 22, 5, 770, 533, 5, 0, 6, -1, -1, -1, 0, 'xpc61594space1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', ' ', None, 'N', ' ', 'F', None, 0, 'N', 999, 0, 0.0, 257, 0.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', 0, datetime.datetime(2020, 1, 20, 22, 21, 41, 602870), 0, ' ', None, None, 'N', 'XPC61594', ' ', ' ', ' ', datetime.date(1, 1, 1), ' ', 'N', 'R', 'N', -1.0, None),\n", | |
| " ('XPC61594', 'SCHOOLS', 'XPC61594', 'U', 'T', 'N', None, None, None, None, datetime.datetime(2020, 1, 20, 22, 22, 23, 168970), datetime.datetime(2020, 1, 20, 22, 22, 23, 168970), datetime.datetime(2020, 1, 20, 22, 22, 23, 168970), datetime.datetime(2020, 1, 20, 22, 23, 28, 928220), 78, 6, 770, 566, 15, 0, 16, -1, -1, -1, 0, 'xpc61594space1', None, None, 0, 0, 0, 0, 0, 0, 0, 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', 1, ' ', '0', -1, 'N', ' ', None, 'R', ' ', 'N', ' ', None, 'N', ' ', 'F', None, 0, 'N', 999, 0, 0.0, 844, 0.0, None, 1208, 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', 0, datetime.datetime(2020, 1, 20, 22, 22, 23, 168970), 0, ' ', None, None, 'N', 'XPC61594', ' ', ' ', ' ', datetime.date(1, 1, 1), ' ', 'N', 'R', 'N', -1.0, None)]" | |
| ] | |
| }, | |
| "execution_count": 111, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from syscat.tables where tabschema = 'XPC61594'" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [] | |
| } | |
| ], | |
| "metadata": { | |
| "kernelspec": { | |
| "display_name": "Python", | |
| "language": "python", | |
| "name": "conda-env-python-py" | |
| }, | |
| "language_info": { | |
| "codemirror_mode": { | |
| "name": "ipython", | |
| "version": 3 | |
| }, | |
| "file_extension": ".py", | |
| "mimetype": "text/x-python", | |
| "name": "python", | |
| "nbconvert_exporter": "python", | |
| "pygments_lexer": "ipython3", | |
| "version": "3.6.7" | |
| }, | |
| "widgets": { | |
| "state": {}, | |
| "version": "1.1.2" | |
| } | |
| }, | |
| "nbformat": 4, | |
| "nbformat_minor": 4 | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment