API tools faq
paste
Guest User

Untitled

a guest
Jan 26th, 2019
158
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.13 KB | None | 0 0
raw download clone embed print report
  1. {
  2. "nbformat_minor": 1,
  3. "cells": [
  4. {
  5. "execution_count": 6,
  6. "cell_type": "code",
  7. "metadata": {},
  8. "outputs": [],
  9. "source": "#load SQL magic\n%load_ext sql"
  10. },
  11. {
  12. "execution_count": 7,
  13. "cell_type": "code",
  14. "metadata": {},
  15. "outputs": [
  16. {
  17. "execution_count": 7,
  18. "metadata": {},
  19. "data": {
  20. "text/plain": "'Connected: mgb17542@BLUDB'"
  21. },
  22. "output_type": "execute_result"
  23. }
  24. ],
  25. "source": "# Enter the connection string for Db2 on Cloud database\n%sql ibm_db_sa://mgb17542:dkw5hl3%5Eqm57tf2c@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB"
  26. },
  27. {
  28. "execution_count": 8,
  29. "cell_type": "code",
  30. "metadata": {},
  31. "outputs": [
  32. {
  33. "output_type": "stream",
  34. "name": "stdout",
  35. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  36. },
  37. {
  38. "execution_count": 8,
  39. "metadata": {},
  40. "data": {
  41. "text/html": "<table>\n <tr>\n <th>Rows in Census Data</th>\n </tr>\n <tr>\n <td>78</td>\n </tr>\n</table>",
  42. "text/plain": "[(Decimal('78'),)]"
  43. },
  44. "output_type": "execute_result"
  45. }
  46. ],
  47. "source": "# Rows in Census Data (Socieconimic Indicators)\n%sql select Count(*) as \"Rows in Census Data\" from CENSUS_DATA"
  48. },
  49. {
  50. "execution_count": 9,
  51. "cell_type": "code",
  52. "metadata": {},
  53. "outputs": [
  54. {
  55. "output_type": "stream",
  56. "name": "stdout",
  57. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  58. },
  59. {
  60. "execution_count": 9,
  61. "metadata": {},
  62. "data": {
  63. "text/html": "<table>\n <tr>\n <th>Rows in Public Schools</th>\n </tr>\n <tr>\n <td>566</td>\n </tr>\n</table>",
  64. "text/plain": "[(Decimal('566'),)]"
  65. },
  66. "output_type": "execute_result"
  67. }
  68. ],
  69. "source": "# Rows in Public Schools\n%sql select Count(*) as \"Rows in Public Schools\" from CHICAGO_PUBLIC_SCHOOLS"
  70. },
  71. {
  72. "execution_count": 10,
  73. "cell_type": "code",
  74. "metadata": {},
  75. "outputs": [
  76. {
  77. "output_type": "stream",
  78. "name": "stdout",
  79. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  80. },
  81. {
  82. "execution_count": 10,
  83. "metadata": {},
  84. "data": {
  85. "text/html": "<table>\n <tr>\n <th>Rows in Crime Data</th>\n </tr>\n <tr>\n <td>533</td>\n </tr>\n</table>",
  86. "text/plain": "[(Decimal('533'),)]"
  87. },
  88. "output_type": "execute_result"
  89. }
  90. ],
  91. "source": "# Rows in Crime Data\n%sql select Count(*) as \"Rows in Crime Data\" from CHICAGO_CRIME_DATA"
  92. },
  93. {
  94. "execution_count": 20,
  95. "cell_type": "code",
  96. "metadata": {},
  97. "outputs": [
  98. {
  99. "output_type": "stream",
  100. "name": "stdout",
  101. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  102. },
  103. {
  104. "execution_count": 20,
  105. "metadata": {},
  106. "data": {
  107. "text/html": "<table>\n <tr>\n <th>Avg College Enrollment</th>\n <th>community_area_name</th>\n </tr>\n <tr>\n <td>858.000000</td>\n <td>ALBANY PARK</td>\n </tr>\n <tr>\n <td>2411.500000</td>\n <td>ARCHER HEIGHTS</td>\n </tr>\n <tr>\n <td>486.000000</td>\n <td>ARMOUR SQUARE</td>\n </tr>\n <tr>\n <td>810.375000</td>\n <td>ASHBURN</td>\n </tr>\n <tr>\n <td>417.500000</td>\n <td>AUBURN GRESHAM</td>\n </tr>\n <tr>\n <td>475.347826</td>\n <td>AUSTIN</td>\n </tr>\n <tr>\n <td>507.333333</td>\n <td>AVALON PARK</td>\n </tr>\n <tr>\n <td>910.000000</td>\n <td>AVONDALE</td>\n </tr>\n <tr>\n <td>1198.833333</td>\n <td>BELMONT CRAGIN</td>\n </tr>\n <tr>\n <td>409.000000</td>\n <td>BEVERLY</td>\n </tr>\n</table>",
  108. "text/plain": "[(Decimal('858.000000'), 'ALBANY PARK'),\n (Decimal('2411.500000'), 'ARCHER HEIGHTS'),\n (Decimal('486.000000'), 'ARMOUR SQUARE'),\n (Decimal('810.375000'), 'ASHBURN'),\n (Decimal('417.500000'), 'AUBURN GRESHAM'),\n (Decimal('475.347826'), 'AUSTIN'),\n (Decimal('507.333333'), 'AVALON PARK'),\n (Decimal('910.000000'), 'AVONDALE'),\n (Decimal('1198.833333'), 'BELMONT CRAGIN'),\n (Decimal('409.000000'), 'BEVERLY')]"
  109. },
  110. "output_type": "execute_result"
  111. }
  112. ],
  113. "source": "# Average college enrollments by community area\n%sql select AVG(college_enrollment) as \"Avg College Enrollment\", community_area_name from CHICAGO_PUBLIC_SCHOOLS group by community_area_name fetch first 10 rows only"
  114. },
  115. {
  116. "execution_count": 21,
  117. "cell_type": "code",
  118. "metadata": {},
  119. "outputs": [
  120. {
  121. "output_type": "stream",
  122. "name": "stdout",
  123. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  124. },
  125. {
  126. "execution_count": 21,
  127. "metadata": {},
  128. "data": {
  129. "text/html": "<table>\n <tr>\n <th>1</th>\n </tr>\n <tr>\n <td>16</td>\n </tr>\n</table>",
  130. "text/plain": "[(Decimal('16'),)]"
  131. },
  132. "output_type": "execute_result"
  133. }
  134. ],
  135. "source": "# Number of schools that are healthy school certified in Chicago\n%sql select Count(*) from CHICAGO_PUBLIC_SCHOOLS where healthy_school_certified='Yes'"
  136. },
  137. {
  138. "execution_count": 22,
  139. "cell_type": "code",
  140. "metadata": {},
  141. "outputs": [
  142. {
  143. "output_type": "stream",
  144. "name": "stdout",
  145. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  146. },
  147. {
  148. "execution_count": 22,
  149. "metadata": {},
  150. "data": {
  151. "text/html": "<table>\n <tr>\n <th>1</th>\n </tr>\n <tr>\n <td>6</td>\n </tr>\n</table>",
  152. "text/plain": "[(Decimal('6'),)]"
  153. },
  154. "output_type": "execute_result"
  155. }
  156. ],
  157. "source": "# How many observations have a Location Description value of GAS STATION?\n%sql select Count(*) from CHICAGO_CRIME_DATA where location_description='GAS STATION'"
  158. },
  159. {
  160. "execution_count": 23,
  161. "cell_type": "code",
  162. "metadata": {},
  163. "outputs": [
  164. {
  165. "output_type": "stream",
  166. "name": "stdout",
  167. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  168. },
  169. {
  170. "execution_count": 23,
  171. "metadata": {},
  172. "data": {
  173. "text/html": "<table>\n <tr>\n <th>1</th>\n <th>community_area_name</th>\n </tr>\n <tr>\n <td>23</td>\n <td>AUSTIN</td>\n </tr>\n <tr>\n <td>22</td>\n <td>SOUTH LAWNDALE</td>\n </tr>\n <tr>\n <td>20</td>\n <td>WEST TOWN</td>\n </tr>\n <tr>\n <td>17</td>\n <td>ENGLEWOOD</td>\n </tr>\n <tr>\n <td>16</td>\n <td>NEAR WEST SIDE</td>\n </tr>\n <tr>\n <td>16</td>\n <td>NORTH LAWNDALE</td>\n </tr>\n <tr>\n <td>13</td>\n <td>EAST GARFIELD PARK</td>\n </tr>\n <tr>\n <td>13</td>\n <td>ROSELAND</td>\n </tr>\n <tr>\n <td>13</td>\n <td>NEW CITY</td>\n </tr>\n <tr>\n <td>13</td>\n <td>HUMBOLDT PARK</td>\n </tr>\n</table>",
  174. "text/plain": "[(Decimal('23'), 'AUSTIN'),\n (Decimal('22'), 'SOUTH LAWNDALE'),\n (Decimal('20'), 'WEST TOWN'),\n (Decimal('17'), 'ENGLEWOOD'),\n (Decimal('16'), 'NEAR WEST SIDE'),\n (Decimal('16'), 'NORTH LAWNDALE'),\n (Decimal('13'), 'EAST GARFIELD PARK'),\n (Decimal('13'), 'ROSELAND'),\n (Decimal('13'), 'NEW CITY'),\n (Decimal('13'), 'HUMBOLDT PARK')]"
  175. },
  176. "output_type": "execute_result"
  177. }
  178. ],
  179. "source": "# Retrieve a list of the top 10 community areas which have most number of schools and sorted in descending order\n%sql select Count(*), community_area_name from CHICAGO_PUBLIC_SCHOOLS group by community_area_name order by 1 desc fetch first 10 rows only"
  180. },
  181. {
  182. "execution_count": 24,
  183. "cell_type": "code",
  184. "metadata": {},
  185. "outputs": [
  186. {
  187. "output_type": "stream",
  188. "name": "stdout",
  189. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  190. },
  191. {
  192. "execution_count": 24,
  193. "metadata": {},
  194. "data": {
  195. "text/html": "<table>\n <tr>\n <th>1</th>\n </tr>\n <tr>\n <td>24</td>\n </tr>\n</table>",
  196. "text/plain": "[(Decimal('24'),)]"
  197. },
  198. "output_type": "execute_result"
  199. }
  200. ],
  201. "source": "# How many observations have value MOTOR VEHICLE THEFT in the Primary Type variable?\n%sql select Count(*) from CHICAGO_CRIME_DATA where Primary_Type='MOTOR VEHICLE THEFT'"
  202. },
  203. {
  204. "execution_count": 28,
  205. "cell_type": "code",
  206. "metadata": {},
  207. "outputs": [
  208. {
  209. "output_type": "stream",
  210. "name": "stdout",
  211. "text": " * ibm_db_sa://mgb17542:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\nDone.\n"
  212. },
  213. {
  214. "execution_count": 28,
  215. "metadata": {},
  216. "data": {
  217. "text/html": "<table>\n <tr>\n <th>min_average_student_attendance</th>\n </tr>\n <tr>\n <td>86.10%</td>\n </tr>\n</table>",
  218. "text/plain": "[('86.10%',)]"
  219. },
  220. "output_type": "execute_result"
  221. }
  222. ],
  223. "source": "# Find the minimum \u201cAverage Student Attendance\u201d for the community area where hardship is 96\n%sql select MIN(\"AVERAGE_STUDENT_ATTENDANCE\") as MIN_AVERAGE_STUDENT_ATTENDANCE from CHICAGO_PUBLIC_SCHOOLS INNER JOIN CENSUS_DATA ON CHICAGO_PUBLIC_SCHOOLS.\"COMMUNITY_AREA_NUMBER\"=CENSUS_DATA.\"COMMUNITY_AREA_NUMBER\" Where CENSUS_DATA.\"HARDSHIP_INDEX\"='96' \\\nGroup by \"AVERAGE_STUDENT_ATTENDANCE\" limit 1"
  224. },
  225. {
  226. "execution_count": null,
  227. "cell_type": "code",
  228. "metadata": {},
  229. "outputs": [],
  230. "source": ""
  231. }
  232. ],
  233. "metadata": {
  234. "kernelspec": {
  235. "display_name": "Python 3.5",
  236. "name": "python3",
  237. "language": "python"
  238. },
  239. "language_info": {
  240. "mimetype": "text/x-python",
  241. "nbconvert_exporter": "python",
  242. "version": "3.5.5",
  243. "name": "python",
  244. "file_extension": ".py",
  245. "pygments_lexer": "ipython3",
  246. "codemirror_mode": {
  247. "version": 3,
  248. "name": "ipython"
  249. }
  250. }
  251. },
  252. "nbformat": 4
  253. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!