Created
May 23, 2019 09:49
-
-
Save mde-2590/099aff297d5d3ab63e2ea66bf25eae01 to your computer and use it in GitHub Desktop.
Created on Cognitive Class Labs
This file contains hidden or 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": "code", | |
| "execution_count": 1, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| " %load_ext sql" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 2, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": [ | |
| "'Connected: dfk30111@BLUDB'" | |
| ] | |
| }, | |
| "execution_count": 2, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql ibm_db_sa://dfk30111:b6q01rtcjz%5Eq4sk8@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 3, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>1</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>533</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[(Decimal('533'),)]" | |
| ] | |
| }, | |
| "execution_count": 3, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select count(*) from CHICAGO_CRIME_DATA" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 4, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.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>8/28/2004 17:50</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>2/10/2018 15:50</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>6/26/2004 12:40</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>2/28/2018 15:56</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>4/4/2011 5:45</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>2/10/2018 15:50</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 16:30</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>2/10/2018 15:50</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>2/2/2016 19:30</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>2/10/2018 15:50</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>9/29/2010 7:59</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>2/10/2018 15:50</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 1:15</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>2/10/2018 15:50</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 16:45</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>2/28/2018 15:56</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>1/28/2005 17:00</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>2/28/2018 15:56</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>5/13/2004 14:15</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>2/28/2018 15:56</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', '8/28/2004 17:50', '047XX S KEDZIE AVE', '890', 'THEFT', 'FROM BUILDING', 'SMALL RETAIL STORE', 'FALSE', 'FALSE', 911, 9, 14, 58, '6', 1155838, 1873050, 2004, '2/10/2018 15:50', Decimal('41.80744050'), Decimal('-87.70395585'), '(41.8074405, -87.703955849)'),\n", | |
| " (3406613, 'HK456306', '6/26/2004 12:40', '009XX N CENTRAL PARK AVE', '820', 'THEFT', '$500 AND UNDER', 'OTHER', 'FALSE', 'FALSE', 1112, 11, 27, 23, '6', 1152206, 1906127, 2004, '2/28/2018 15:56', Decimal('41.89827996'), Decimal('-87.71640551'), '(41.898279962, -87.716405505)'),\n", | |
| " (8002131, 'HT233595', '4/4/2011 5:45', '043XX S WABASH AVE', '820', 'THEFT', '$500 AND UNDER', 'NURSING HOME/RETIREMENT HOME', 'FALSE', 'FALSE', 221, 2, 3, 38, '6', 1177436, 1876313, 2011, '2/10/2018 15:50', Decimal('41.81593313'), Decimal('-87.62464213'), '(41.815933131, -87.624642127)'),\n", | |
| " (7903289, 'HT133522', '12/30/2010 16:30', '083XX S KINGSTON AVE', '840', 'THEFT', 'FINANCIAL ID THEFT: OVER $300', 'RESIDENCE', 'FALSE', 'FALSE', 423, 4, 7, 46, '6', 1194622, 1850125, 2010, '2/10/2018 15:50', Decimal('41.74366532'), Decimal('-87.56246276'), '(41.743665322, -87.562462756)'),\n", | |
| " (10402076, 'HZ138551', '2/2/2016 19:30', '033XX W 66TH ST', '820', 'THEFT', '$500 AND UNDER', 'ALLEY', 'FALSE', 'FALSE', 831, 8, 15, 66, '6', 1155240, 1860661, 2016, '2/10/2018 15:50', Decimal('41.77345530'), Decimal('-87.70648047'), '(41.773455295, -87.706480471)'),\n", | |
| " (7732712, 'HS540106', '9/29/2010 7:59', '006XX W CHICAGO AVE', '810', 'THEFT', 'OVER $500', 'PARKING LOT/GARAGE(NON.RESID.)', 'FALSE', 'FALSE', 1323, 12, 27, 24, '6', 1171668, 1905607, 2010, '2/10/2018 15:50', Decimal('41.89644677'), Decimal('-87.64493868'), '(41.896446772, -87.644938678)'),\n", | |
| " (10769475, 'HZ534771', '11/30/2016 1:15', '050XX N KEDZIE AVE', '810', 'THEFT', 'OVER $500', 'STREET', 'FALSE', 'FALSE', 1713, 17, 33, 14, '6', 1154133, 1933314, 2016, '2/10/2018 15:50', Decimal('41.97284491'), Decimal('-87.70860008'), '(41.972844913, -87.708600079)'),\n", | |
| " (4494340, 'HL793243', '12/16/2005 16:45', '005XX E PERSHING RD', '860', 'THEFT', 'RETAIL THEFT', 'GROCERY FOOD STORE', 'TRUE', 'FALSE', 213, 2, 3, 38, '6', 1180448, 1879234, 2005, '2/28/2018 15:56', Decimal('41.82387989'), Decimal('-87.61350386'), '(41.823879885, -87.613503857)'),\n", | |
| " (3778925, 'HL149610', '1/28/2005 17:00', '100XX S WASHTENAW AVE', '810', 'THEFT', 'OVER $500', 'STREET', 'FALSE', 'FALSE', 2211, 22, 19, 72, '6', 1160129, 1838040, 2005, '2/28/2018 15:56', Decimal('41.71128051'), Decimal('-87.68917910'), '(41.711280513, -87.689179097)'),\n", | |
| " (3324217, 'HK361551', '5/13/2004 14:15', '033XX W BELMONT AVE', '820', 'THEFT', '$500 AND UNDER', 'SMALL RETAIL STORE', 'FALSE', 'FALSE', 1733, 17, 35, 21, '6', 1153590, 1921084, 2004, '2/28/2018 15:56', Decimal('41.93929582'), Decimal('-87.71092344'), '(41.939295821, -87.710923442)')]" | |
| ] | |
| }, | |
| "execution_count": 4, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from CHICAGO_CRIME_DATA LIMIT 10" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 5, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>1</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>163</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[(Decimal('163'),)]" | |
| ] | |
| }, | |
| "execution_count": 5, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select count (*) from CHICAGO_CRIME_DATA where ARREST = 'TRUE'" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 6, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>primary_type</th>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[]" | |
| ] | |
| }, | |
| "execution_count": 6, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| " %sql select distinct PRIMARY_TYPE from CHICAGO_CRIME_DATA WHERE LOCATION='GAS STATION'" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 7, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.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>Burnside</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Brighton Park</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Bridgeport</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Beverly</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('Belmont Cragin',),\n", | |
| " ('Burnside',),\n", | |
| " ('Brighton Park',),\n", | |
| " ('Bridgeport',),\n", | |
| " ('Beverly',)]" | |
| ] | |
| }, | |
| "execution_count": 7, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select \"COMMUNITY_AREA_NAME\" from CENSUS_DATA where \"COMMUNITY_AREA_NAME\" like 'B%';" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 9, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>name_of_school</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Rufus M Hitch Elementary School</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('Rufus M Hitch Elementary School',)]" | |
| ] | |
| }, | |
| "execution_count": 9, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select NAME_OF_SCHOOL from CHICAGO_PUBLIC_SCHOOLS \\\n", | |
| "where COMMUNITY_AREA_NUMBER between 10 and 15 \\\n", | |
| "and HEALTHY_SCHOOL_CERTIFIED = 'Yes'" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 10, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>name_of_school</th>\n", | |
| " <th>college_enrollment</th>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Albert G Lane Technical High School</td>\n", | |
| " <td>4368</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Marie Sklodowska Curie Metropolitan High School</td>\n", | |
| " <td>3320</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>William Howard Taft High School</td>\n", | |
| " <td>2922</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Thomas Kelly High School</td>\n", | |
| " <td>2883</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <td>Carl Schurz High School</td>\n", | |
| " <td>2366</td>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('Albert G Lane Technical High School', 4368),\n", | |
| " ('Marie Sklodowska Curie Metropolitan High School', 3320),\n", | |
| " ('William Howard Taft High School', 2922),\n", | |
| " ('Thomas Kelly High School', 2883),\n", | |
| " ('Carl Schurz High School', 2366)]" | |
| ] | |
| }, | |
| "execution_count": 10, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select NAME_OF_SCHOOL, COLLEGE_ENROLLMENT from CHICAGO_PUBLIC_SCHOOLS order by COLLEGE_ENROLLMENT desc limit 5" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 11, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.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": 11, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select COMMUNITY_AREA_NAME, SAFETY_SCORE from CHICAGO_PUBLIC_SCHOOLS \\\n", | |
| "where SAFETY_SCORE =(select min(SAFETY_SCORE) from CHICAGO_PUBLIC_SCHOOLS)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 15, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <tr>\n", | |
| " <th>per_capita_income</th>\n", | |
| " </tr>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[]" | |
| ] | |
| }, | |
| "execution_count": 15, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "%sql select per_capita_income from census_data where community_area_name in (select community_area_name from chicago_public_schools where safety_score = 1)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [] | |
| } | |
| ], | |
| "metadata": { | |
| "kernelspec": { | |
| "display_name": "Python 3", | |
| "language": "python", | |
| "name": "python3" | |
| }, | |
| "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.8" | |
| } | |
| }, | |
| "nbformat": 4, | |
| "nbformat_minor": 2 | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment