Created
February 21, 2020 19:00
-
-
Save bazsapeter/c48995205c0a2ca36227bde63fc61ac8 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://www.bigdatauniversity.com\"><img src = \"https://ibm.box.com/shared/static/ugcqz6ohbvff804xp84y4kqnvvk3bq1g.png\" width = 300, align = \"center\"></a>\n", | |
"\n", | |
"<h1 align=center><font size = 5>Accessing Databases with SQL Magic</font></h1>" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"#### After using this notebook, you will know how to perform simplified database access using SQL \"magic\". You will connect to a Db2 database, issue SQL commands to create tables, insert data, and run queries, as well as retrieve results in a Python dataframe. " | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"##### To communicate with SQL Databases from within a JupyterLab notebook, we can use the SQL \"magic\" provided by the [ipython-sql](https://github.com/catherinedevlin/ipython-sql) extension. \"Magic\" is JupyterLab's term for special commands that start with \"%\". Below, we'll use the _load_\\__ext_ magic to load the ipython-sql extension. In the lab environemnt provided in the course the ipython-sql extension is already installed and so is the ibm_db_sa driver. " | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 1, | |
"metadata": {}, | |
"outputs": [], | |
"source": [ | |
"%load_ext sql" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"##### Now we have access to SQL magic. With our first SQL magic command, we'll connect to a Db2 database. However, in order to do that, you'll first need to retrieve or create your credentials to access your Db2 database." | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<a ><img src = \"https://ibm.box.com/shared/static/uy78gy1uq3uj6fkvd4muzy5zcr62tb72.png\" width = 1000, align = \"center\"></a>\n", | |
" <h5 align=center> This image shows the location of your connection string if you're using Db2 on IBM Cloud. If you're using another host the format is: username:password@hostname:port/database-name\n", | |
" </h5>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 2, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/plain": [ | |
"'Connected: djs45175@BLUDB'" | |
] | |
}, | |
"execution_count": 2, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"# Enter your Db2 credentials in the connection string below\n", | |
"# Recall you created Service Credentials in Part III of the first lab of the course in Week 1\n", | |
"# i.e. from the uri field in the Service Credentials copy everything after db2:// (but remove the double quote at the end)\n", | |
"# for example, if your credentials are as in the screenshot above, you would write:\n", | |
"# %sql ibm_db_sa://my-username:my-password@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB\n", | |
"# Note the ibm_db_sa:// prefix instead of db2://\n", | |
"# This is because JupyterLab's ipython-sql extension uses sqlalchemy (a python SQL toolkit)\n", | |
"# which in turn uses IBM's sqlalchemy dialect: ibm_db_sa\n", | |
"%sql ibm_db_sa://djs45175:z8nq7v0-0dghjmq5@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"##### For convenience, we can use %%sql (two %'s instead of one) at the top of a cell to indicate we want the entire cell to be treated as SQL. Let's use this to create a table and fill it with some test data for experimenting." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 3, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://djs45175:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
"Done.\n", | |
"99 rows affected.\n" | |
] | |
}, | |
{ | |
"data": { | |
"text/plain": [ | |
"[]" | |
] | |
}, | |
"execution_count": 3, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"%%sql\n", | |
"\n", | |
"CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (\n", | |
"\tcountry VARCHAR(50),\n", | |
"\tfirst_name VARCHAR(50),\n", | |
"\tlast_name VARCHAR(50),\n", | |
"\ttest_score INT\n", | |
");\n", | |
"INSERT INTO INTERNATIONAL_STUDENT_TEST_SCORES (country, first_name, last_name, test_score)\n", | |
"VALUES\n", | |
"('United States', 'Marshall', 'Bernadot', 54),\n", | |
"('Ghana', 'Celinda', 'Malkin', 51),\n", | |
"('Ukraine', 'Guillermo', 'Furze', 53),\n", | |
"('Greece', 'Aharon', 'Tunnow', 48),\n", | |
"('Russia', 'Bail', 'Goodwin', 46),\n", | |
"('Poland', 'Cole', 'Winteringham', 49),\n", | |
"('Sweden', 'Emlyn', 'Erricker', 55),\n", | |
"('Russia', 'Cathee', 'Sivewright', 49),\n", | |
"('China', 'Barny', 'Ingerson', 57),\n", | |
"('Uganda', 'Sharla', 'Papaccio', 55),\n", | |
"('China', 'Stella', 'Youens', 51),\n", | |
"('Poland', 'Julio', 'Buesden', 48),\n", | |
"('United States', 'Tiffie', 'Cosely', 58),\n", | |
"('Poland', 'Auroora', 'Stiffell', 45),\n", | |
"('China', 'Clarita', 'Huet', 52),\n", | |
"('Poland', 'Shannon', 'Goulden', 45),\n", | |
"('Philippines', 'Emylee', 'Privost', 50),\n", | |
"('France', 'Madelina', 'Burk', 49),\n", | |
"('China', 'Saunderson', 'Root', 58),\n", | |
"('Indonesia', 'Bo', 'Waring', 55),\n", | |
"('China', 'Hollis', 'Domotor', 45),\n", | |
"('Russia', 'Robbie', 'Collip', 46),\n", | |
"('Philippines', 'Davon', 'Donisi', 46),\n", | |
"('China', 'Cristabel', 'Radeliffe', 48),\n", | |
"('China', 'Wallis', 'Bartleet', 58),\n", | |
"('Moldova', 'Arleen', 'Stailey', 38),\n", | |
"('Ireland', 'Mendel', 'Grumble', 58),\n", | |
"('China', 'Sallyann', 'Exley', 51),\n", | |
"('Mexico', 'Kain', 'Swaite', 46),\n", | |
"('Indonesia', 'Alonso', 'Bulteel', 45),\n", | |
"('Armenia', 'Anatol', 'Tankus', 51),\n", | |
"('Indonesia', 'Coralyn', 'Dawkins', 48),\n", | |
"('China', 'Deanne', 'Edwinson', 45),\n", | |
"('China', 'Georgiana', 'Epple', 51),\n", | |
"('Portugal', 'Bartlet', 'Breese', 56),\n", | |
"('Azerbaijan', 'Idalina', 'Lukash', 50),\n", | |
"('France', 'Livvie', 'Flory', 54),\n", | |
"('Malaysia', 'Nonie', 'Borit', 48),\n", | |
"('Indonesia', 'Clio', 'Mugg', 47),\n", | |
"('Brazil', 'Westley', 'Measor', 48),\n", | |
"('Philippines', 'Katrinka', 'Sibbert', 51),\n", | |
"('Poland', 'Valentia', 'Mounch', 50),\n", | |
"('Norway', 'Sheilah', 'Hedditch', 53),\n", | |
"('Papua New Guinea', 'Itch', 'Jubb', 50),\n", | |
"('Latvia', 'Stesha', 'Garnson', 53),\n", | |
"('Canada', 'Cristionna', 'Wadmore', 46),\n", | |
"('China', 'Lianna', 'Gatward', 43),\n", | |
"('Guatemala', 'Tanney', 'Vials', 48),\n", | |
"('France', 'Alma', 'Zavittieri', 44),\n", | |
"('China', 'Alvira', 'Tamas', 50),\n", | |
"('United States', 'Shanon', 'Peres', 45),\n", | |
"('Sweden', 'Maisey', 'Lynas', 53),\n", | |
"('Indonesia', 'Kip', 'Hothersall', 46),\n", | |
"('China', 'Cash', 'Landis', 48),\n", | |
"('Panama', 'Kennith', 'Digance', 45),\n", | |
"('China', 'Ulberto', 'Riggeard', 48),\n", | |
"('Switzerland', 'Judy', 'Gilligan', 49),\n", | |
"('Philippines', 'Tod', 'Trevaskus', 52),\n", | |
"('Brazil', 'Herold', 'Heggs', 44),\n", | |
"('Latvia', 'Verney', 'Note', 50),\n", | |
"('Poland', 'Temp', 'Ribey', 50),\n", | |
"('China', 'Conroy', 'Egdal', 48),\n", | |
"('Japan', 'Gabie', 'Alessandone', 47),\n", | |
"('Ukraine', 'Devlen', 'Chaperlin', 54),\n", | |
"('France', 'Babbette', 'Turner', 51),\n", | |
"('Czech Republic', 'Virgil', 'Scotney', 52),\n", | |
"('Tajikistan', 'Zorina', 'Bedow', 49),\n", | |
"('China', 'Aidan', 'Rudeyeard', 50),\n", | |
"('Ireland', 'Saunder', 'MacLice', 48),\n", | |
"('France', 'Waly', 'Brunstan', 53),\n", | |
"('China', 'Gisele', 'Enns', 52),\n", | |
"('Peru', 'Mina', 'Winchester', 48),\n", | |
"('Japan', 'Torie', 'MacShirrie', 50),\n", | |
"('Russia', 'Benjamen', 'Kenford', 51),\n", | |
"('China', 'Etan', 'Burn', 53),\n", | |
"('Russia', 'Merralee', 'Chaperlin', 38),\n", | |
"('Indonesia', 'Lanny', 'Malam', 49),\n", | |
"('Canada', 'Wilhelm', 'Deeprose', 54),\n", | |
"('Czech Republic', 'Lari', 'Hillhouse', 48),\n", | |
"('China', 'Ossie', 'Woodley', 52),\n", | |
"('Macedonia', 'April', 'Tyer', 50),\n", | |
"('Vietnam', 'Madelon', 'Dansey', 53),\n", | |
"('Ukraine', 'Korella', 'McNamee', 52),\n", | |
"('Jamaica', 'Linnea', 'Cannam', 43),\n", | |
"('China', 'Mart', 'Coling', 52),\n", | |
"('Indonesia', 'Marna', 'Causbey', 47),\n", | |
"('China', 'Berni', 'Daintier', 55),\n", | |
"('Poland', 'Cynthia', 'Hassell', 49),\n", | |
"('Canada', 'Carma', 'Schule', 49),\n", | |
"('Indonesia', 'Malia', 'Blight', 48),\n", | |
"('China', 'Paulo', 'Seivertsen', 47),\n", | |
"('Niger', 'Kaylee', 'Hearley', 54),\n", | |
"('Japan', 'Maure', 'Jandak', 46),\n", | |
"('Argentina', 'Foss', 'Feavers', 45),\n", | |
"('Venezuela', 'Ron', 'Leggitt', 60),\n", | |
"('Russia', 'Flint', 'Gokes', 40),\n", | |
"('China', 'Linet', 'Conelly', 52),\n", | |
"('Philippines', 'Nikolas', 'Birtwell', 57),\n", | |
"('Australia', 'Eduard', 'Leipelt', 53)\n" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"#### Using Python Variables in your SQL Statements\n", | |
"##### You can use python variables in your SQL statements by adding a \":\" prefix to your python variable names.\n", | |
"##### For example, if I have a python variable `country` with a value of `\"Canada\"`, I can use this variable in a SQL query to find all the rows of students from Canada." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 4, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://djs45175:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
"Done.\n" | |
] | |
}, | |
{ | |
"data": { | |
"text/html": [ | |
"<table>\n", | |
" <tr>\n", | |
" <th>country</th>\n", | |
" <th>first_name</th>\n", | |
" <th>last_name</th>\n", | |
" <th>test_score</th>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Canada</td>\n", | |
" <td>Cristionna</td>\n", | |
" <td>Wadmore</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Canada</td>\n", | |
" <td>Wilhelm</td>\n", | |
" <td>Deeprose</td>\n", | |
" <td>54</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Canada</td>\n", | |
" <td>Carma</td>\n", | |
" <td>Schule</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
"</table>" | |
], | |
"text/plain": [ | |
"[('Canada', 'Cristionna', 'Wadmore', 46),\n", | |
" ('Canada', 'Wilhelm', 'Deeprose', 54),\n", | |
" ('Canada', 'Carma', 'Schule', 49)]" | |
] | |
}, | |
"execution_count": 4, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"country = \"Canada\"\n", | |
"%sql select * from INTERNATIONAL_STUDENT_TEST_SCORES where country = :country" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"#### Assigning the Results of Queries to Python Variables" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"##### You can use the normal python assignment syntax to assign the results of your queries to python variables.\n", | |
"##### For example, I have a SQL query to retrieve the distribution of test scores (i.e. how many students got each score). I can assign the result of this query to the variable `test_score_distribution` using the `=` operator." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 5, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://djs45175:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
"Done.\n" | |
] | |
}, | |
{ | |
"data": { | |
"text/html": [ | |
"<table>\n", | |
" <tr>\n", | |
" <th>Test Score</th>\n", | |
" <th>Frequency</th>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>38</td>\n", | |
" <td>2</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>40</td>\n", | |
" <td>1</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>43</td>\n", | |
" <td>2</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>44</td>\n", | |
" <td>2</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>45</td>\n", | |
" <td>8</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>46</td>\n", | |
" <td>7</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>47</td>\n", | |
" <td>4</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>48</td>\n", | |
" <td>14</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>49</td>\n", | |
" <td>8</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>50</td>\n", | |
" <td>10</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>51</td>\n", | |
" <td>8</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>52</td>\n", | |
" <td>8</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>53</td>\n", | |
" <td>8</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>54</td>\n", | |
" <td>5</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>55</td>\n", | |
" <td>4</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>56</td>\n", | |
" <td>1</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>57</td>\n", | |
" <td>2</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>58</td>\n", | |
" <td>4</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>60</td>\n", | |
" <td>1</td>\n", | |
" </tr>\n", | |
"</table>" | |
], | |
"text/plain": [ | |
"[(38, Decimal('2')),\n", | |
" (40, Decimal('1')),\n", | |
" (43, Decimal('2')),\n", | |
" (44, Decimal('2')),\n", | |
" (45, Decimal('8')),\n", | |
" (46, Decimal('7')),\n", | |
" (47, Decimal('4')),\n", | |
" (48, Decimal('14')),\n", | |
" (49, Decimal('8')),\n", | |
" (50, Decimal('10')),\n", | |
" (51, Decimal('8')),\n", | |
" (52, Decimal('8')),\n", | |
" (53, Decimal('8')),\n", | |
" (54, Decimal('5')),\n", | |
" (55, Decimal('4')),\n", | |
" (56, Decimal('1')),\n", | |
" (57, Decimal('2')),\n", | |
" (58, Decimal('4')),\n", | |
" (60, Decimal('1'))]" | |
] | |
}, | |
"execution_count": 5, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"test_score_distribution = %sql SELECT test_score as \"Test Score\", count(*) as \"Frequency\" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;\n", | |
"test_score_distribution" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"#### Converting Query Results to DataFrames" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"##### You can easily convert a SQL query result to a pandas dataframe using the `DataFrame()` method. Dataframe objects are much more versatile than SQL query result objects. For example, we can easily graph our test score distribution after converting to a dataframe." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 6, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8QZhcZAAAWxElEQVR4nO3de7RkZXnn8e8PWhQMiobDRbFtzdKOlxh1WiaJY7wAExSCGMDLaEJE7YyJGI0sxXgBx5gxikEwo6ZFEMVLBMRRiAoY0ckaBRttuepgYqt4o9EYNF4QfOaPvVsPhzrn7KpTl+7e389atc7eu/Z+3qeq3n561768lapCktQfO806AUnSdFn4JalnLPyS1DMWfknqGQu/JPXMqlkn0MWee+5Za9asmXUakrRdufzyy2+sqrmFy7eLwr9mzRo2btw46zQkabuS5KuDlnuoR5J6xsIvST1j4ZeknrHwS1LPWPglqWcs/JLUMxMr/ElOT3JDkqsGPHdckkqy56TalyQNNsk9/ncABy9cmORewEHA1ybYtiRpERMr/FX1KeB7A546GXgx4A8BSNIMTPXO3SSHAd+oqi8kWW7d9cB6gNWrV08hO21LHv/BY0fa7iOHv2nMmUg7nqmd3E2yG/Ay4JVd1q+qDVW1rqrWzc3dbqgJSdKIpnlVz68B9wG+kGQzsB/wuST7TDEHSeq9qR3qqaorgb22zrfFf11V3TitHCRJk72c873Ap4G1Sa5P8qxJtSVJ6m5ie/xV9bRlnl8zqbYlSYvzzl1J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPTOxwp/k9CQ3JLlq3rLXJ/likiuSnJdkj0m1L0kabJJ7/O8ADl6w7CLgwVX1EOD/AS+dYPuSpAEmVvir6lPA9xYsu7CqbmlnPwPsN6n2JUmDzfIY/zHARxZ7Msn6JBuTbNyyZcsU05KkHdtMCn+SlwG3AO9ebJ2q2lBV66pq3dzc3PSSk6Qd3KppN5jkaOBQ4ICqqmm3L0l9N9XCn+Rg4CXAo6vqR9NsW5LUmOTlnO8FPg2sTXJ9kmcBfwfsDlyUZFOSt06qfUnSYBPb46+qpw1Y/PZJtSdJ6sY7dyWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknpmYoU/yelJbkhy1bxld09yUZLr2r93m1T7kqTBJrnH/w7g4AXLjgc+XlX3Az7ezkuSpmhihb+qPgV8b8HiJwJnttNnAodPqn1J0mCrptze3lX1LYCq+laSvRZbMcl6YD3A6tWrp5SedHuHnnv6SNudf8QxY85EGo9t9uRuVW2oqnVVtW5ubm7W6UjSDmPahf87SfYFaP/eMOX2Jan3pl34PwQc3U4fDfzvKbcvSb03ycs53wt8Glib5PokzwJeCxyU5DrgoHZekjRFEzu5W1VPW+SpAybVpiRpedvsyV1J0mRY+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs90KvxJHjzpRCRJ09F1j/+tSS5L8qdJ9phoRpKkiepU+KvqvwBPB+4FbEzyniQHTTQzSdJEdD7GX1XXAS8HXgI8Gjg1yReT/MGkkpMkjV/XY/wPSXIycC3wOOD3q+oB7fTJE8xPkjRmXYdl/jvgbcBfVtWPty6sqm8meflEMpMkTUTXwv8E4MdVdStAkp2AO1XVj6rqXRPLTpI0dl2P8V8M7Dpvfrd2mSRpO9O18N+pqn64daad3m0yKUmSJqlr4f+PJA/fOpPkPwE/XmJ9SdI2qusx/hcAZyf5Zju/L/CUyaQkSZqkToW/qj6b5NeBtUCAL1bVz0ZtNMkLgWcDBVwJPLOqfjJqPElSd8MM0vYI4CHAw4CnJfmjURpMck/g+cC6qnowsDPw1FFiSZKG12mPP8m7gF8DNgG3tosLeOcK2t01yc9oThJ/c5n1JUlj0vUY/zrggVVVK22wqr6R5CTgazQniC+sqgsXrpdkPbAeYPXq1Sttdof2vjN+b6TtnvrMj405k23PIR94w0jbXfAHLxpzJnDo2eeMtN35Rx15m/nDzxntSuoPHnngL6aPOveKkWKcfcRDRtpO25auh3quAvYZR4NJ7gY8EbgPcA/gzkmesXC9qtpQVeuqat3c3Nw4mpYk0X2Pf0/gmiSXAT/durCqDhuhzQOBr1TVFoAkHwB+BzhrhFiSpCF1LfwnjrHNrwG/lWQ3mkM9BwAbxxhfkrSErpdzfjLJvYH7VdXFbdHeeZQGq+rSJOcAnwNuAT4PbBglliRpeF2v6nkOzYnWu9Nc3XNP4K00e+tDq6oTgBNG2VaStDJdT+7+GfBI4Cb4xY+y7DWppCRJk9O18P+0qm7eOpNkFc11/JKk7UzXwv/JJH9Jc9PVQcDZwIcnl5YkaVK6Fv7jgS004+r8CfCPNL+/K0naznS9qufnND+9+LbJpiNJmrSuV/V8hQHH9KvqvmPPSJI0UcOM1bPVnYCjaC7tlCRtZzod46+q7857fKOq3gg8bsK5SZImoOuhnofPm92J5hvA7hPJSJI0UV0P9cwf2/YWYDPw5LFno5l607tHG9752Kfv+MM7SzuSrlf1PHbSiUiSpqProZ6/WOr5qvrb8aQjSZq0Ya7qeQTwoXb+94FPAV+fRFKSpMkZ5odYHl5VPwBIciJwdlU9e1KJSZImo+uQDauBm+fN3wysGXs2kqSJ67rH/y7gsiTn0dzB+yTgnRPLSpI0MV2v6nlNko8Aj2oXPbOqPj+5tCRJk9L1UA/AbsBNVXUKcH2S+0woJ0nSBHUq/ElOAF4CvLRddAfgrEklJUmanK57/E8CDgP+A6CqvolDNkjSdqlr4b+5qop2aOYkd55cSpKkSepa+N+f5O+BPZI8B7iYFfwoS5I9kpyT5ItJrk3y26PGkiQNp+tVPSe1v7V7E7AWeGVVXbSCdk8BPlpVRybZhebEsSRpCpYt/El2Bj5WVQcCKyn2W+PdBfhd4I8BqupmbntzmCRpgpY91FNVtwI/SnLXMbV5X5ofbj8jyeeTnDbonEGS9Uk2Jtm4ZcuWMTUtSep6jP8nwJVJ3p7k1K2PEdtcBTwceEtVPYzmSqHjF65UVRuqal1VrZubmxuxKUnSQl2HbLigfYzD9cD1VXVpO38OAwq/JGkyliz8SVZX1deq6sxxNVhV307y9SRrq+pLwAHANeOKL0la2nKHej64dSLJuWNs91jg3UmuAB4K/PUYY0uSlrDcoZ7Mm77vuBqtqk00P+4iSZqy5fb4a5FpSdJ2ark9/t9MchPNnv+u7TTtfFXVXSaanSRp7JYs/FW187QSkSRNxzDj8UuSdgAWfknqGQu/JPWMhV+SesbCL0k9Y+GXpJ7pOkibJN3Oa8771kjbvexJ+445Ew3DPX5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1zMwKf5Kdk3w+yfmzykGS+miWe/x/Dlw7w/YlqZdmUviT7AccApw2i/Ylqc9mtcf/RuDFwM9n1L4k9dbUh2VOcihwQ1VdnuQxS6y3HlgPsHr16illp5U67pyDR972pCM/OsZMtL344Nk3jrzt4UftOcZMxuc7J28aabu9X/jQMWcy2Cz2+B8JHJZkM/A+4HFJzlq4UlVtqKp1VbVubm5u2jlK0g5r6oW/ql5aVftV1RrgqcA/VdUzpp2HJPWV1/FLUs/M9KcXq+oS4JJZ5iBJfeMevyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9MvfAnuVeSTyS5NsnVSf582jlIUp+tmkGbtwAvqqrPJdkduDzJRVV1zQxykaTemfoef1V9q6o+107/ALgWuOe085CkvprFHv8vJFkDPAy4dMBz64H1AKtXrwZgy1vOGqmduec+Y8QMF3f1mw8babsH/emHbjP/8dMOGSnOAc++YKTtJC3t+pO+PdJ2+x23z5gzmZyZndxN8ivAucALquqmhc9X1YaqWldV6+bm5qafoCTtoGZS+JPcgabov7uqPjCLHCSpr2ZxVU+AtwPXVtXfTrt9Seq7WezxPxL4Q+BxSTa1jyfMIA9J6qWpn9ytqn8GMu12JUkN79yVpJ6x8EtSz1j4JalnLPyS1DMWfknqGQu/JPWMhV+SesbCL0k9Y+GXpJ6Z6bDMs/Dtt7xqpO32ee4JY85E0jhddsYNI223/zP3GnMm43PDmy4aabu9jj1oyefd45eknrHwS1LPWPglqWcs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST1j4ZeknrHwS1LPWPglqWdmUviTHJzkS0m+nOT4WeQgSX019cKfZGfgfwGPBx4IPC3JA6edhyT11Sz2+PcHvlxV/1pVNwPvA544gzwkqZdSVdNtMDkSOLiqnt3O/yHwn6vqeQvWWw+sb2fXAl9aJvSewI0rTG8cMba1OOYy2TjbUi7jimMuk40zzVzuXVVzCxfOYjz+DFh2u/99qmoDsKFz0GRjVa1bUWJjiLGtxTGXycbZlnIZVxxzmWycbSGXWRzquR6417z5/YBvziAPSeqlWRT+zwL3S3KfJLsATwU+NIM8JKmXpn6op6puSfI84GPAzsDpVXX1GEJ3Piw04RjbWhxzmWycbSmXccUxl8nGmXkuUz+5K0maLe/claSesfBLUs9sd4U/yZ2SXJbkC0muTvKqdvlDk3wmyaYkG5Ps3yHWzkk+n+T8dv7uSS5Kcl37924dc1oY59VJrmhzuTDJPUaJM2/5cUkqyZ4j5HJikm+0uWxK8oRRc0lybDvUxtVJXjdKnCT/MC+XzUk2jRBj6M96kTi/meTTSa5M8uEkd+kQY3O7/qYkG9tlQ/WbRWIc1b6vP0/S6RK9ReK8PskX2/53XpI9RowzVB8eFGPec8P030G5DN2HF8tnmD68SC6j9N9BcYbqw0n2SHJO+9lem+S3h+13t1FV29WD5j6AX2mn7wBcCvwWcCHw+Hb5E4BLOsT6C+A9wPnt/OuA49vp44G/6ZjTwjh3mffc84G3jhKnXXYvmhPhXwX2HCGXE4HjRnifF8Z5LHAxcMd2fq9RX9O8594AvHKEXIb+rBeJ81ng0e30McCrO8TYvPBzGLbfLBLjATQ3Kl4CrOv4egbF+a/Aqnb6b7r04UXiDNWHB8UYsf8OymXoPrxInKH68GKvaYT+OyiXofowcCbw7HZ6F2CPYfvd/Md2t8dfjR+2s3doH9U+tu6x3ZVl7g1Ish9wCHDavMVPpHmDaf8evlw+g+JU1U3zVrkzA25Q65gPwMnAi1cYYyiLxHku8Nqq+ilAVd2wknySBHgy8N4RYgz1WS8RZy3wqXb6IuCI5eIsYuh+s1BVXVtVy92d3iXOhVV1Szv7GZr7ZEaJM3QfXkTn/jsFQ/fhxXTtv0vo3Ifbb6K/C7wdoKpurqrvs4J+t90VfvjFV/ZNwA3ARVV1KfAC4PVJvg6cBLx0mTBvpOmQP5+3bO+q+hZA+3evDukMikOS17S5PB145ShxkhwGfKOqvtBh+0VzAZ7Xfm0/vePXwUFx7g88KsmlST6Z5BEryAfgUcB3quq6EWIM+1kvFucq4LB2+ihue2PhYgq4MMnlaYYVgeH7zaAYo1guzjHAR0aNM2Qfvl2MEfrvorkwfB8eFGfYPrzU+9u1/y4WZ5g+fF9gC3BGmkOVpyW5M6PVqzajIb4+bWsPmq87nwAeDJwKHNEufzJw8RLbHQq8uZ1+DL/86v/9Bev92zLtD4yzYJ2XAq8aNg6wG81hrLtWt6+di72mvWnul9gJeA3NfRNDvyaaInkqzaG2/YGv0F4OPMp7A7wFeNGIuXT+rJeJ8+s0X7kvB04Avtuhz92j/bsX8AWaPbFh+83tYsx77hK6H+pZKs7LgPOW+oy6xBmiDw96Xzr332XiDNWHl4gzbB9e6v1dtv8uk8sw9WodcAvNmGYApwCvHrbf3Wbdrituq4/2H+xxwL9v/RDbD/amJbb5nzRDR2wGvg38CDiLZiC4fdt19gW+tEzbA+MsWOfewFUjxDmX5hvN5vZxC/A1YJ8V5LJmxFzOAj4KPGbeev8CzI3y3tDcOPgdYL8Rc+n8WQ/x3twfuGzIvndi2/eG6jeDYsybv4SOhX+xOMDRwKeB3VYSZ5g+PCDGK4bpv0PksmwfXuJzGqoPL/H+duq/y+QyTL3aB9g8b/5RwAUr6nfDJj7rBzAH7NFO7wr8H5o9umu3fqjAAcDlHeM9hl/uAb6e254sed0Qec2Pc795y48FzhklzoLlm+mwxzQgl33nLX8h8L4RX9N/B/5HO31/4Ot02Jsc9JqAg4FPDvm5z89lpM96QJy92r87Ae8Ejllm2zsDu8+b/r/ta+ncbxaLMe/5S+hQ+JfI5WDgGroXtMXidO7Dy72mrv13iVyG6sNLxOnch5d6TcP03yVyGaoP09S5te30iW2fG7lezWJ0zpXaFzgzzQ+67AS8v6rOT/J94JQkq4Cf8MshnYfxWuD9SZ5Fs3dy1Ig5vjbJWprjyV+l6XCz8rokD6U5zrgZ+JMR45wOnJ7kKuBm4Ohqe9wInsroJ8UAnsPKP2tofgToz9rpDwBnLLP+3sB5zXk9VgHvqaqPJvks3fvNYjGeBLyJZsfmgiSbqur3RojzZeCOwEXtc5+pqqX632Jxzh2iDw+MscT6w+byriH78GJxdqF7H17qNQ3TfxfL5YcM14ePBd7dvoZ/BZ5JW/9GqVcO2SBJPbNdXtUjSRqdhV+SesbCL0k9Y+GXpJ6x8EtSz1j4tUNK8qvzRlH8dm47uuMuQ8Q5Jsk+izz3yPb2/03tiImvGN8rkCbHyzm1w0tyIvDDqjpphG3/GXheVd1u+N32mvnDq+qq9r6StVV1zQpz3bmqbl1JDGk57vGrd5IcneY3HTYleXOSnZKsam8UujLJVUmen+QpwEOBf1jkm8IczRAQVNWtW4t+kt2TnNnGuiLJ4e3yZ8yL/9ftslVJvp/kr5JcBuyf5BHtIGKXJ/lIkr2n9uaoF7bHO3elkSV5MPAk4Heq6pYkG2juxPwXmiEFfqNdb4+q+n6SY1lkj59mxM/rknyCZhTMd1Yz5O+JwJaq+o00t2zukWZY6L+iGXDr34GLkxxKM37MXYHPVdXLk9yRZuDBw6rqxiRPpxmQayWjeEq3YeFX3xwIPALY2N5GvyvNmC0fA9YmOQX4R5pRO5dUVSckeRfNj5/8EfCUNv6BtGOjt0MC/FuSxwH/VFU3AiR5D80ojR+lGT7gvDbsA4AH0fzHAM2olNev+FVL81j41TehGdb3didikzwEeDzNL04dQYe97Kr6MvDlJG8Dvpvkrm0bC0+eZYkwP543ZkyAK6rqUcu+EmlEHuNX31wMPDnt77+2V/+sTjJHc7HD2TRDfT+8Xf8HwO6DAiU5pD2UA81ojz9t178QeF67TtL8cMhngMe27a2iObz0yQFhrwHumfY3WJPskuRBK37V0jzu8atXqurKJK+iOZSyE/AzmpEnbwXe3hbyAl7SbnIGcFqSHwP7V9XN88L9MXBykh+1cf5bVf28jf/mdhTIW4FXVNWHkrySZtjlAB+uqgva/wTm5/fTJEcCpybZnebf6BuAq8f/bqivvJxTknrGQz2S1DMWfknqGQu/JPWMhV+SesbCL0k9Y+GXpJ6x8EtSz/x/CUeBs4Y9/QAAAAAASUVORK5CYII=\n", | |
"text/plain": [ | |
"<Figure size 432x288 with 1 Axes>" | |
] | |
}, | |
"metadata": { | |
"needs_background": "light" | |
}, | |
"output_type": "display_data" | |
} | |
], | |
"source": [ | |
"dataframe = test_score_distribution.DataFrame()\n", | |
"\n", | |
"%matplotlib inline\n", | |
"# uncomment the following line if you get an module error saying seaborn not found\n", | |
"# !pip install seaborn\n", | |
"import seaborn\n", | |
"\n", | |
"plot = seaborn.barplot(x='Test Score',y='Frequency', data=dataframe)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Now you know how to work with Db2 from within JupyterLab notebooks using SQL \"magic\"!" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 7, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://djs45175:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
"Done.\n" | |
] | |
}, | |
{ | |
"data": { | |
"text/html": [ | |
"<table>\n", | |
" <tr>\n", | |
" <th>country</th>\n", | |
" <th>first_name</th>\n", | |
" <th>last_name</th>\n", | |
" <th>test_score</th>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>United States</td>\n", | |
" <td>Marshall</td>\n", | |
" <td>Bernadot</td>\n", | |
" <td>54</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Ghana</td>\n", | |
" <td>Celinda</td>\n", | |
" <td>Malkin</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Ukraine</td>\n", | |
" <td>Guillermo</td>\n", | |
" <td>Furze</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Greece</td>\n", | |
" <td>Aharon</td>\n", | |
" <td>Tunnow</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Russia</td>\n", | |
" <td>Bail</td>\n", | |
" <td>Goodwin</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Poland</td>\n", | |
" <td>Cole</td>\n", | |
" <td>Winteringham</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Sweden</td>\n", | |
" <td>Emlyn</td>\n", | |
" <td>Erricker</td>\n", | |
" <td>55</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Russia</td>\n", | |
" <td>Cathee</td>\n", | |
" <td>Sivewright</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Barny</td>\n", | |
" <td>Ingerson</td>\n", | |
" <td>57</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Uganda</td>\n", | |
" <td>Sharla</td>\n", | |
" <td>Papaccio</td>\n", | |
" <td>55</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Stella</td>\n", | |
" <td>Youens</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Poland</td>\n", | |
" <td>Julio</td>\n", | |
" <td>Buesden</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>United States</td>\n", | |
" <td>Tiffie</td>\n", | |
" <td>Cosely</td>\n", | |
" <td>58</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Poland</td>\n", | |
" <td>Auroora</td>\n", | |
" <td>Stiffell</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Clarita</td>\n", | |
" <td>Huet</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Poland</td>\n", | |
" <td>Shannon</td>\n", | |
" <td>Goulden</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Philippines</td>\n", | |
" <td>Emylee</td>\n", | |
" <td>Privost</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>France</td>\n", | |
" <td>Madelina</td>\n", | |
" <td>Burk</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Saunderson</td>\n", | |
" <td>Root</td>\n", | |
" <td>58</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Bo</td>\n", | |
" <td>Waring</td>\n", | |
" <td>55</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Hollis</td>\n", | |
" <td>Domotor</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Russia</td>\n", | |
" <td>Robbie</td>\n", | |
" <td>Collip</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Philippines</td>\n", | |
" <td>Davon</td>\n", | |
" <td>Donisi</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Cristabel</td>\n", | |
" <td>Radeliffe</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Wallis</td>\n", | |
" <td>Bartleet</td>\n", | |
" <td>58</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Moldova</td>\n", | |
" <td>Arleen</td>\n", | |
" <td>Stailey</td>\n", | |
" <td>38</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Ireland</td>\n", | |
" <td>Mendel</td>\n", | |
" <td>Grumble</td>\n", | |
" <td>58</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Sallyann</td>\n", | |
" <td>Exley</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Mexico</td>\n", | |
" <td>Kain</td>\n", | |
" <td>Swaite</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Alonso</td>\n", | |
" <td>Bulteel</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Armenia</td>\n", | |
" <td>Anatol</td>\n", | |
" <td>Tankus</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Coralyn</td>\n", | |
" <td>Dawkins</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Deanne</td>\n", | |
" <td>Edwinson</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Georgiana</td>\n", | |
" <td>Epple</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Portugal</td>\n", | |
" <td>Bartlet</td>\n", | |
" <td>Breese</td>\n", | |
" <td>56</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Azerbaijan</td>\n", | |
" <td>Idalina</td>\n", | |
" <td>Lukash</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>France</td>\n", | |
" <td>Livvie</td>\n", | |
" <td>Flory</td>\n", | |
" <td>54</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Malaysia</td>\n", | |
" <td>Nonie</td>\n", | |
" <td>Borit</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Clio</td>\n", | |
" <td>Mugg</td>\n", | |
" <td>47</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Brazil</td>\n", | |
" <td>Westley</td>\n", | |
" <td>Measor</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Philippines</td>\n", | |
" <td>Katrinka</td>\n", | |
" <td>Sibbert</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Poland</td>\n", | |
" <td>Valentia</td>\n", | |
" <td>Mounch</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Norway</td>\n", | |
" <td>Sheilah</td>\n", | |
" <td>Hedditch</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Papua New Guinea</td>\n", | |
" <td>Itch</td>\n", | |
" <td>Jubb</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Latvia</td>\n", | |
" <td>Stesha</td>\n", | |
" <td>Garnson</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Canada</td>\n", | |
" <td>Cristionna</td>\n", | |
" <td>Wadmore</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Lianna</td>\n", | |
" <td>Gatward</td>\n", | |
" <td>43</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Guatemala</td>\n", | |
" <td>Tanney</td>\n", | |
" <td>Vials</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>France</td>\n", | |
" <td>Alma</td>\n", | |
" <td>Zavittieri</td>\n", | |
" <td>44</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Alvira</td>\n", | |
" <td>Tamas</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>United States</td>\n", | |
" <td>Shanon</td>\n", | |
" <td>Peres</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Sweden</td>\n", | |
" <td>Maisey</td>\n", | |
" <td>Lynas</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Kip</td>\n", | |
" <td>Hothersall</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Cash</td>\n", | |
" <td>Landis</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Panama</td>\n", | |
" <td>Kennith</td>\n", | |
" <td>Digance</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Ulberto</td>\n", | |
" <td>Riggeard</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Switzerland</td>\n", | |
" <td>Judy</td>\n", | |
" <td>Gilligan</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Philippines</td>\n", | |
" <td>Tod</td>\n", | |
" <td>Trevaskus</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Brazil</td>\n", | |
" <td>Herold</td>\n", | |
" <td>Heggs</td>\n", | |
" <td>44</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Latvia</td>\n", | |
" <td>Verney</td>\n", | |
" <td>Note</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Poland</td>\n", | |
" <td>Temp</td>\n", | |
" <td>Ribey</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Conroy</td>\n", | |
" <td>Egdal</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Japan</td>\n", | |
" <td>Gabie</td>\n", | |
" <td>Alessandone</td>\n", | |
" <td>47</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Ukraine</td>\n", | |
" <td>Devlen</td>\n", | |
" <td>Chaperlin</td>\n", | |
" <td>54</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>France</td>\n", | |
" <td>Babbette</td>\n", | |
" <td>Turner</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Czech Republic</td>\n", | |
" <td>Virgil</td>\n", | |
" <td>Scotney</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Tajikistan</td>\n", | |
" <td>Zorina</td>\n", | |
" <td>Bedow</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Aidan</td>\n", | |
" <td>Rudeyeard</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Ireland</td>\n", | |
" <td>Saunder</td>\n", | |
" <td>MacLice</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>France</td>\n", | |
" <td>Waly</td>\n", | |
" <td>Brunstan</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Gisele</td>\n", | |
" <td>Enns</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Peru</td>\n", | |
" <td>Mina</td>\n", | |
" <td>Winchester</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Japan</td>\n", | |
" <td>Torie</td>\n", | |
" <td>MacShirrie</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Russia</td>\n", | |
" <td>Benjamen</td>\n", | |
" <td>Kenford</td>\n", | |
" <td>51</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Etan</td>\n", | |
" <td>Burn</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Russia</td>\n", | |
" <td>Merralee</td>\n", | |
" <td>Chaperlin</td>\n", | |
" <td>38</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Lanny</td>\n", | |
" <td>Malam</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Canada</td>\n", | |
" <td>Wilhelm</td>\n", | |
" <td>Deeprose</td>\n", | |
" <td>54</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Czech Republic</td>\n", | |
" <td>Lari</td>\n", | |
" <td>Hillhouse</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Ossie</td>\n", | |
" <td>Woodley</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Macedonia</td>\n", | |
" <td>April</td>\n", | |
" <td>Tyer</td>\n", | |
" <td>50</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Vietnam</td>\n", | |
" <td>Madelon</td>\n", | |
" <td>Dansey</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Ukraine</td>\n", | |
" <td>Korella</td>\n", | |
" <td>McNamee</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Jamaica</td>\n", | |
" <td>Linnea</td>\n", | |
" <td>Cannam</td>\n", | |
" <td>43</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Mart</td>\n", | |
" <td>Coling</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Marna</td>\n", | |
" <td>Causbey</td>\n", | |
" <td>47</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Berni</td>\n", | |
" <td>Daintier</td>\n", | |
" <td>55</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Poland</td>\n", | |
" <td>Cynthia</td>\n", | |
" <td>Hassell</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Canada</td>\n", | |
" <td>Carma</td>\n", | |
" <td>Schule</td>\n", | |
" <td>49</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Indonesia</td>\n", | |
" <td>Malia</td>\n", | |
" <td>Blight</td>\n", | |
" <td>48</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Paulo</td>\n", | |
" <td>Seivertsen</td>\n", | |
" <td>47</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Niger</td>\n", | |
" <td>Kaylee</td>\n", | |
" <td>Hearley</td>\n", | |
" <td>54</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Japan</td>\n", | |
" <td>Maure</td>\n", | |
" <td>Jandak</td>\n", | |
" <td>46</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Argentina</td>\n", | |
" <td>Foss</td>\n", | |
" <td>Feavers</td>\n", | |
" <td>45</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Venezuela</td>\n", | |
" <td>Ron</td>\n", | |
" <td>Leggitt</td>\n", | |
" <td>60</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Russia</td>\n", | |
" <td>Flint</td>\n", | |
" <td>Gokes</td>\n", | |
" <td>40</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>China</td>\n", | |
" <td>Linet</td>\n", | |
" <td>Conelly</td>\n", | |
" <td>52</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Philippines</td>\n", | |
" <td>Nikolas</td>\n", | |
" <td>Birtwell</td>\n", | |
" <td>57</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>Australia</td>\n", | |
" <td>Eduard</td>\n", | |
" <td>Leipelt</td>\n", | |
" <td>53</td>\n", | |
" </tr>\n", | |
"</table>" | |
], | |
"text/plain": [ | |
"[('United States', 'Marshall', 'Bernadot', 54),\n", | |
" ('Ghana', 'Celinda', 'Malkin', 51),\n", | |
" ('Ukraine', 'Guillermo', 'Furze', 53),\n", | |
" ('Greece', 'Aharon', 'Tunnow', 48),\n", | |
" ('Russia', 'Bail', 'Goodwin', 46),\n", | |
" ('Poland', 'Cole', 'Winteringham', 49),\n", | |
" ('Sweden', 'Emlyn', 'Erricker', 55),\n", | |
" ('Russia', 'Cathee', 'Sivewright', 49),\n", | |
" ('China', 'Barny', 'Ingerson', 57),\n", | |
" ('Uganda', 'Sharla', 'Papaccio', 55),\n", | |
" ('China', 'Stella', 'Youens', 51),\n", | |
" ('Poland', 'Julio', 'Buesden', 48),\n", | |
" ('United States', 'Tiffie', 'Cosely', 58),\n", | |
" ('Poland', 'Auroora', 'Stiffell', 45),\n", | |
" ('China', 'Clarita', 'Huet', 52),\n", | |
" ('Poland', 'Shannon', 'Goulden', 45),\n", | |
" ('Philippines', 'Emylee', 'Privost', 50),\n", | |
" ('France', 'Madelina', 'Burk', 49),\n", | |
" ('China', 'Saunderson', 'Root', 58),\n", | |
" ('Indonesia', 'Bo', 'Waring', 55),\n", | |
" ('China', 'Hollis', 'Domotor', 45),\n", | |
" ('Russia', 'Robbie', 'Collip', 46),\n", | |
" ('Philippines', 'Davon', 'Donisi', 46),\n", | |
" ('China', 'Cristabel', 'Radeliffe', 48),\n", | |
" ('China', 'Wallis', 'Bartleet', 58),\n", | |
" ('Moldova', 'Arleen', 'Stailey', 38),\n", | |
" ('Ireland', 'Mendel', 'Grumble', 58),\n", | |
" ('China', 'Sallyann', 'Exley', 51),\n", | |
" ('Mexico', 'Kain', 'Swaite', 46),\n", | |
" ('Indonesia', 'Alonso', 'Bulteel', 45),\n", | |
" ('Armenia', 'Anatol', 'Tankus', 51),\n", | |
" ('Indonesia', 'Coralyn', 'Dawkins', 48),\n", | |
" ('China', 'Deanne', 'Edwinson', 45),\n", | |
" ('China', 'Georgiana', 'Epple', 51),\n", | |
" ('Portugal', 'Bartlet', 'Breese', 56),\n", | |
" ('Azerbaijan', 'Idalina', 'Lukash', 50),\n", | |
" ('France', 'Livvie', 'Flory', 54),\n", | |
" ('Malaysia', 'Nonie', 'Borit', 48),\n", | |
" ('Indonesia', 'Clio', 'Mugg', 47),\n", | |
" ('Brazil', 'Westley', 'Measor', 48),\n", | |
" ('Philippines', 'Katrinka', 'Sibbert', 51),\n", | |
" ('Poland', 'Valentia', 'Mounch', 50),\n", | |
" ('Norway', 'Sheilah', 'Hedditch', 53),\n", | |
" ('Papua New Guinea', 'Itch', 'Jubb', 50),\n", | |
" ('Latvia', 'Stesha', 'Garnson', 53),\n", | |
" ('Canada', 'Cristionna', 'Wadmore', 46),\n", | |
" ('China', 'Lianna', 'Gatward', 43),\n", | |
" ('Guatemala', 'Tanney', 'Vials', 48),\n", | |
" ('France', 'Alma', 'Zavittieri', 44),\n", | |
" ('China', 'Alvira', 'Tamas', 50),\n", | |
" ('United States', 'Shanon', 'Peres', 45),\n", | |
" ('Sweden', 'Maisey', 'Lynas', 53),\n", | |
" ('Indonesia', 'Kip', 'Hothersall', 46),\n", | |
" ('China', 'Cash', 'Landis', 48),\n", | |
" ('Panama', 'Kennith', 'Digance', 45),\n", | |
" ('China', 'Ulberto', 'Riggeard', 48),\n", | |
" ('Switzerland', 'Judy', 'Gilligan', 49),\n", | |
" ('Philippines', 'Tod', 'Trevaskus', 52),\n", | |
" ('Brazil', 'Herold', 'Heggs', 44),\n", | |
" ('Latvia', 'Verney', 'Note', 50),\n", | |
" ('Poland', 'Temp', 'Ribey', 50),\n", | |
" ('China', 'Conroy', 'Egdal', 48),\n", | |
" ('Japan', 'Gabie', 'Alessandone', 47),\n", | |
" ('Ukraine', 'Devlen', 'Chaperlin', 54),\n", | |
" ('France', 'Babbette', 'Turner', 51),\n", | |
" ('Czech Republic', 'Virgil', 'Scotney', 52),\n", | |
" ('Tajikistan', 'Zorina', 'Bedow', 49),\n", | |
" ('China', 'Aidan', 'Rudeyeard', 50),\n", | |
" ('Ireland', 'Saunder', 'MacLice', 48),\n", | |
" ('France', 'Waly', 'Brunstan', 53),\n", | |
" ('China', 'Gisele', 'Enns', 52),\n", | |
" ('Peru', 'Mina', 'Winchester', 48),\n", | |
" ('Japan', 'Torie', 'MacShirrie', 50),\n", | |
" ('Russia', 'Benjamen', 'Kenford', 51),\n", | |
" ('China', 'Etan', 'Burn', 53),\n", | |
" ('Russia', 'Merralee', 'Chaperlin', 38),\n", | |
" ('Indonesia', 'Lanny', 'Malam', 49),\n", | |
" ('Canada', 'Wilhelm', 'Deeprose', 54),\n", | |
" ('Czech Republic', 'Lari', 'Hillhouse', 48),\n", | |
" ('China', 'Ossie', 'Woodley', 52),\n", | |
" ('Macedonia', 'April', 'Tyer', 50),\n", | |
" ('Vietnam', 'Madelon', 'Dansey', 53),\n", | |
" ('Ukraine', 'Korella', 'McNamee', 52),\n", | |
" ('Jamaica', 'Linnea', 'Cannam', 43),\n", | |
" ('China', 'Mart', 'Coling', 52),\n", | |
" ('Indonesia', 'Marna', 'Causbey', 47),\n", | |
" ('China', 'Berni', 'Daintier', 55),\n", | |
" ('Poland', 'Cynthia', 'Hassell', 49),\n", | |
" ('Canada', 'Carma', 'Schule', 49),\n", | |
" ('Indonesia', 'Malia', 'Blight', 48),\n", | |
" ('China', 'Paulo', 'Seivertsen', 47),\n", | |
" ('Niger', 'Kaylee', 'Hearley', 54),\n", | |
" ('Japan', 'Maure', 'Jandak', 46),\n", | |
" ('Argentina', 'Foss', 'Feavers', 45),\n", | |
" ('Venezuela', 'Ron', 'Leggitt', 60),\n", | |
" ('Russia', 'Flint', 'Gokes', 40),\n", | |
" ('China', 'Linet', 'Conelly', 52),\n", | |
" ('Philippines', 'Nikolas', 'Birtwell', 57),\n", | |
" ('Australia', 'Eduard', 'Leipelt', 53)]" | |
] | |
}, | |
"execution_count": 7, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"%%sql \n", | |
"\n", | |
"-- Feel free to experiment with the data set provided in this notebook for practice:\n", | |
"SELECT country, first_name, last_name, test_score FROM INTERNATIONAL_STUDENT_TEST_SCORES; " | |
] | |
}, | |
{ | |
"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" | |
] | |
} | |
], | |
"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" | |
} | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 4 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment