Created
February 5, 2019 17:06
-
-
Save abhiray92/f7091b7b8f3cfbac20d7dd1d672961be 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": 4, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/plain": [ | |
"'Connected: svb70790@BLUDB'" | |
] | |
}, | |
"execution_count": 4, | |
"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://svb70790:50gxtzm8-w1tn24k@dashdb-txn-sbox-yp-lon02-01.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": 5, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://svb70790:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
"Done.\n", | |
"99 rows affected.\n" | |
] | |
}, | |
{ | |
"data": { | |
"text/plain": [ | |
"[]" | |
] | |
}, | |
"execution_count": 5, | |
"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": 6, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://svb70790:***@dashdb-txn-sbox-yp-lon02-01.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": 6, | |
"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": 7, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://svb70790:***@dashdb-txn-sbox-yp-lon02-01.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": 7, | |
"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": 9, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAEKCAYAAAAfGVI8AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4xLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvDW2N/gAAGApJREFUeJzt3XmYJHWd5/H3B1oUFESlOBTbVsfBa7y2ZcdxHRVEURlEAcX1YDjsGV3xGBnF0RGc0V1HGfGYVbdFDgEvQFwGBGlUPJ5VkKORU/FAQVTAC08Q+O4fES1J0VWVFVWZ2U28X89TT2VERnx/38yMqm/+4vhFqgpJUn9tMOkEJEmTZSGQpJ6zEEhSz1kIJKnnLASS1HMWAknqOQuBJPWchUCSes5CIEk9t2TSCQxjiy22qGXLlk06DUlar5x33nnXV9XUXMutF4Vg2bJlnHvuuZNOQ5LWK0l+MMxy7hqSpJ6zEEhSz1kIJKnnLASS1HMWAknquZEVgiRHJLk2ycVree7AJJVki1G1L0kazih7BEcBO0+fmeT+wE7AD0fYtiRpSCMrBFX1ZeDna3nqMOD1gPfIlKR1wFiPESTZFfhRVV04znYlSTMb25XFSTYB3gQ8fcjlVwArAJYuXTrCzLQueuZnDui03mm7vX+RM5Hu/MbZI3gw8EDgwiRXAtsC5yfZem0LV9XKqlpeVcunpuYcKkOS1NHYegRVdRGw5Zrpthgsr6rrx5WDJOmORnn66MeBrwHbJbk6yX6jakuS1N3IegRV9cI5nl82qrYlScPzymJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSeG1khSHJEkmuTXDww711JLk/yzSQnJdl8VO1LkoYzyh7BUcDO0+atAh5ZVY8Cvg28cYTtS5KGMLJCUFVfBn4+bd4ZVXVzO/l1YNtRtS9JGs4kjxHsC5w205NJViQ5N8m511133RjTkqR+mUghSPIm4GbguJmWqaqVVbW8qpZPTU2NLzlJ6pkl424wyd7ALsCOVVXjbl+SdHtjLQRJdgbeADy5qn43zrYlSWs3ytNHPw58DdguydVJ9gP+A9gUWJVkdZIPjap9SdJwRtYjqKoXrmX2R0bVniSpG68slqSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzIysESY5Icm2Siwfm3TvJqiRXtL/vNar2JUnDGWWP4Chg52nzDgI+X1UPAT7fTkuSJmhkhaCqvgz8fNrs5wBHt4+PBnYbVfuSpOGM+xjBVlX1Y4D295Zjbl+SNM2SSScwkyQrgBUAS5cunXA26rNdTjyi03qn7L7vImcijca4ewQ/TbINQPv72pkWrKqVVbW8qpZPTU2NLUFJ6ptxF4KTgb3bx3sD/3fM7UuSphnl6aMfB74GbJfk6iT7Ae8AdkpyBbBTOy1JmqCRHSOoqhfO8NSOo2pTkjR/XlksST1nIZCknrMQSFLPWQgkqecsBJLUcxYCSeo5C4Ek9ZyFQJJ6zkIgST1nIZCknhuqECR55KgTkSRNxrA9gg8lOSfJK5JsPtKMJEljNVQhqKr/BrwIuD9wbpKPJdlppJlJksZi6GMEVXUF8GbgDcCTgfcluTzJ80aVnCRp9IY9RvCoJIcBlwE7AH9TVQ9rHx82wvwkSSM27P0I/gP4MPBPVfX7NTOr6pokbx5JZpKksRi2EDwL+H1V3QKQZAPgblX1u6o6ZmTZSZJGbthjBGcCGw9Mb9LOkySt54YtBHerqt+smWgfbzKalCRJ4zRsIfhtksetmUjyX4Dfz7K8JGk9MewxgtcAxye5pp3eBnjBaFKSJI3TUIWgqr6R5KHAdkCAy6vqj10bTfJaYH+ggIuAfarqD13jSZK6m8+gc48HHgU8Fnhhkpd2aTDJ/YBXAcur6pHAhsBeXWJJkhZuqB5BkmOABwOrgVva2QV8dAHtbpzkjzQHna+ZY3lJ0ogMe4xgOfDwqqqFNlhVP0pyKPBDmgPOZ1TVGdOXS7ICWAGwdOnShTZ7p/aJI5/Rab299vncImey7nn2p/+903qnPu91i5wJ7HL8CZ3WO2XPPW43vdsJ3c7c/sweT/vT4z1P/GanGMfv/qhO62ndNuyuoYuBrRejwST3Ap4DPBC4L3D3JC+evlxVrayq5VW1fGpqajGaliStxbA9gi2AS5OcA9y4ZmZV7dqhzacB36+q6wCSfBr4K+DYDrEkSQs0bCE4ZBHb/CHwl0k2odk1tCNw7iLGlyTNw7Cnj34pyQOAh1TVme0/8Q27NFhVZyc5ATgfuBm4AFjZJZYkaeGGPWvoZTQHbu9Nc/bQ/YAP0Xybn7eqOhg4uMu6kqTFNezB4v8BPBG4Af50k5otR5WUJGl8hi0EN1bVTWsmkiyhuY5AkrSeG7YQfCnJP9FcBLYTcDzwn6NLS5I0LsMWgoOA62jGBfo74LM09y+WJK3nhj1r6FaaW1V+eLTpSJLGbdizhr7PWo4JVNWDFj0jSdJYzWesoTXuBuxJcyqpJGk9N9Qxgqr62cDPj6rqPcAOI85NkjQGw+4aetzA5AY0PYRNR5KRJGmsht01NDiW783AlcDzFz0bTdT7j+s2nPUBL7rzD2ct3ZkNe9bQU0ediCRpMobdNfQPsz1fVe9enHQkSeM2n7OGHg+c3E7/DfBl4KpRJCVJGp/53JjmcVX1a4AkhwDHV9X+o0pMkjQeww4xsRS4aWD6JmDZomcjSRq7YXsExwDnJDmJ5grj5wIfHVlWkqSxGfasobcnOQ14Ujtrn6q6YHRpSZLGZdhdQwCbADdU1XuBq5M8cEQ5SZLGaKhCkORg4A3AG9tZdwGOHVVSkqTxGbZH8FxgV+C3AFV1DQ4xIUl3CsMWgpuqqmiHok5y94U0mmTzJCckuTzJZUmesJB4kqTuhi0En0ryf4DNk7wMOJOF3aTmvcDpVfVQ4NHAZQuIJUlagGHPGjq0vVfxDcB2wFuqalWXBpNsBvw18Ldt7Ju4/TUKkqQxmrMQJNkQ+FxVPQ3o9M9/mgfR3P/4yCSPBs4DXl1Vv12E2JKkeZpz11BV3QL8Lsk9F6nNJcDjgA9W1WNpDkAfNH2hJCuSnJvk3Ouuu26RmpYkTTfslcV/AC5Ksor2zCGAqnpVhzavBq6uqrPb6RNYSyGoqpXASoDly5ff4X7JkqTFMWwhOLX9WbCq+kmSq5JsV1XfAnYELl2M2JKk+Zu1ECRZWlU/rKqjF7ndA4DjkmwEfA/YZ5HjS5KGNFeP4DM0+/NJcmJV7b4YjVbVapp7HEiSJmyug8UZePygUSYiSZqMuQpBzfBYknQnMdeuoUcnuYGmZ7Bx+5h2uqpqs5FmJ0kauVkLQVVtOK5EJEmTMZ/7EUiS7oQsBJLUcxYCSeo5C4Ek9ZyFQJJ6zkIgST037KBzknQHbz/px53We9Nzt1nkTLQQ9ggkqecsBJLUcxYCSeo5C4Ek9ZyFQJJ6zkIgST1nIZCknrMQSFLPWQgkqecsBJLUcxMrBEk2THJBklMmlYMkabI9glcDl02wfUkSEyoESbYFng0cPon2JUm3mVSP4D3A64FbJ9S+JKk19mGok+wCXFtV5yV5yizLrQBWACxdunRM2WmhDjxh587rHrrH6YuYidYXnzn++s7r7rbnFouYyeL56WGrO6231Wsfs8iZDGcSPYInArsmuRL4BLBDkmOnL1RVK6tqeVUtn5qaGneOktQbYy8EVfXGqtq2qpYBewFfqKoXjzsPSVLD6wgkqecmeqvKqjoLOGuSOUhS39kjkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6rmxF4Ik90/yxSSXJbkkyavHnYMk6TZLJtDmzcDrqur8JJsC5yVZVVWXTiAXSeq9sfcIqurHVXV++/jXwGXA/cadhySpMYkewZ8kWQY8Fjh7Lc+tAFYALF26FIDrPnhsp3amXv7ijhnO7JIP7NppvUe84uTbTX/+8Gd3irPj/qd2Wk/S7K4+9Ced1tv2wK0XOZPxmdjB4iT3AE4EXlNVN0x/vqpWVtXyqlo+NTU1/gQlqScmUgiS3IWmCBxXVZ+eRA6SpMYkzhoK8BHgsqp697jblyTd3iR6BE8EXgLskGR1+/OsCeQhSWICB4ur6qtAxt2uJGntvLJYknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknpuosNQT8JPPvjWTutt/fKDFzkTSYvpnCOv7bTe9vtsuciZLJ5r37+q03pbHrDTvJa3RyBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSem0ghSLJzkm8l+U6SgyaRgySpMfZCkGRD4H8DzwQeDrwwycPHnYckqTGJHsH2wHeq6ntVdRPwCeA5E8hDksRkCsH9gKsGpq9u50mSJiBVNd4Gkz2BZ1TV/u30S4Dtq+qAacutAFa0k9sB35oj9BbA9QtMbzFirGtxzGW0cdalXBYrjrmMNs44c3lAVU3NFWgS9yO4Grj/wPS2wDXTF6qqlcDKYYMmObeqli8kscWIsa7FMZfRxlmXclmsOOYy2jjrUi5rTGLX0DeAhyR5YJKNgL2AkyeQhySJCfQIqurmJK8EPgdsCBxRVZeMOw9JUmMit6qsqs8Cn13ksEPvRhpxjHUtjrmMNs66lMtixTGX0cZZl3IBJnCwWJK0bnGICUnqufWuECS5W5JzklyY5JIkb23n75jk/CSrk3w1yZ8NGW/DJBckOaWdfmCSs5NckeST7QHt+cb4SJvfN5OckOQeXXIZmP/+JL/p+HqOSvL99n1ZneQxHeMkyduTfDvJZUle1THOVwZyuSbJZzrEWKzPeoc2zsVJjk4y567SJFcmuaht+9x23r2TrGq3mVVJ7tUhxp7t9nxrkqHOBJkhzruSXN5ueycl2bxjnH9tY6xOckaS+843xsBzByapJFt0zOWQJD8a2G6e1SVOO/+ANMPbXJLknR1y+eRAHlcmWd3xNT0mydfXzEuy/RwxNk/zv+Ty9u/vCfPd7mZVVevVDxDgHu3juwBnA38JfBt4WDv/FcBRQ8b7B+BjwCnt9KeAvdrHHwJe3iHGZgPPvRs4qEsu7bzlwDHAbzq+nqOAPTq8z9Pj7AN8FNignd6y62saeO5E4KUdclnwZ03zJegq4M/b5/4F2G+IGFcCW0yb9841nzFwEPBvHWI8jOZ6mbOA5UO+nrXFeTqwpH38b3PlMkucwW34VcCH5hujnX9/mhNDfrC254fM5RDgwHluv2uL81TgTOCuw2zDM72mgef/HXhLx1zOAJ7ZPn4WcNYcMY4G9m8fbwRsPt/tbraf9a5HUI01347v0v5U+7NZO/+erOXahOmSbAs8Gzi8nQ6wA3BCu8jRwG7zidHmeMNAvI3b3OaVSztvQ+BdwOvnWn+mGF3MEOflwL9U1a0AVXXtQvJJsinNez1rj2CGGAv+rIH7ADdW1bfb6VXA7nPFmcFzaLYVGGKbWZuquqyq5rpocpg4Z1TVze3k12mu0+kS54aBybszxDY8g8Nott914WDky4F3VNWNMNw2PJP2b/v5wMc7hhh6G06yGfDXwEcAquqmqvoli7DdrbHeFQL4Uxd/NXAtsKqqzgb2Bz6b5GrgJcA7hgj1HpqN9NZ2+j7ALwf+kIYZ/mJ6jDU5Hgn8BHgo8P4OuQC8Eji5qn48xPoz5gK8ve3mH5bkrh3jPBh4QduNPS3JQxaQD8Bzgc9P+4czbIzF+KyvB+4ysBtmD25/oeNMCjgjyXlprn4H2GrNZ9T+3rJDjC7mirMvcFrXOGl2BV4FvAh4y3xjJNkV+FFVXTjMi5ktF+CV7TZ8xJC7QNYW58+BJ6XZ9fulJI/vmAvAk4CfVtUVHXN5DfCu9v09FHjjLOs/CLgOODLNrs3Dk9yd+W93s2TYsSuxLvzQdI++CDwS+DTwX9v5/wgcPse6uwAfaB8/hWZ3wRTNgHiD3dqL5hNj2vMbAh8A9umQy32Br3JbN3/WXUMz5QJsQ7M77a403xpm7crOEuc3wOvax88DvtIlzsDzpwG7d8xlwZ91+/gJwFeAc4C3ARcMsc3dt/29JXAhzTe1X05b5hfzjTHw3FkMv2totjhvAk6iPTOwa5x2/huBt3Z4X84G7tnOv5Lhdg2tLc5WNH9LGwBvp7n2qEuci4H3tX8P2wPfn+39meP9/eCav4eOubxvzfZP07M4c5b1lwM3D2zz7wX+db7b3aw5dl1xXfkBDm7/GXx3YN5S4NI51vtfNN/4r6T55v474Diab4pr/vk+AfjcPGMcO22ZJ7OW/eNDxPlF+/jK9udWBopUx1ye0jGXY4HLgWXtMgF+1SVO+9x9gJ8Bd+sQ49RF+qynvzdPBz41z23vEOBAmnGwtmnnbQN8a74xBqbPYshCMFMcYG/ga8AmC4kzMO8BwMXzjPHPND32NdvvzcAPga0XmMuy+eQy7XM6HXjKwPzvAlMd3t8lwE+Bbbu+v8CvuO30/QA3zLLO1sCVA9NPav8OOm93d2ij64qT+qH51r55+3hjmm90u9D8A19z4G8/4MR5xHwKt31LPJ7bHyx+xXxitB/qnw18wIcCh3bJZdr8oQ4Wr+X1bDOQy3to9pF2ifMOYN+B+d/o+pqAvweOnufnvub9XbKIn/WW7e+7Ap8Hdphj3bsDmw48/n/AzjTHcQYP2r1zvjEGnj+LIQrBLLnsDFzK8P/gZorzkIFlDgBO6Pqa2vlXMkePYJZcthlY5rXAJzrG+Xua41zQ7Ca6ihl6BLO9pjbWlxb4/l5GW5SAHYHz5ojzFWC79vEh7TY39HY3189ErixeoG2Ao9sDqRvQfIs7JcnLgBOT3ErzbXrfjvHfAHwiyduAC2gP0MxD2vw2ax9fSHOQalKOSzLV5rKa5o+hi3e0sV5Ls5to/wXktBfD7de/g2qGKFmsz/ofk+xCsx19sKq+MMfyWwEnNccJWQJ8rKpOT/IN4FNJ9qP51rtnhxjPpTmWNAWcmmR1VT2jQ5zv0BS2Ve1zX6+q2T7zmeKcmGQ7mp7oD5h9u1lrjFmWn28ux6Q57bloCsrfdYyzEXBEkouBm4C9q/0vOs/XtBfDHySeKZffAO9Nc8ryH7htpOWZHEDz97cR8D2as/g2YPjtblZeWSxJPbdenjUkSVo8FgJJ6jkLgST1nIVAknrOQiBJPWch0J1SkvsMjBL5k9x+9Mo5R5QdiLNvkq1neO6J7XAFq9sRIf958V6BND6ePqo7vSSH0FyQd2iHdb8KvLKq7jDccHvO/m5VdXF7Xct2VXXpAnPdsKpuWUgMab7sEah3kuyd5p4Wq5N8IMkGSZa0Fy5dlOb+BK9K8gLgMcAnZ+hJTNEMWUFV3bKmCCTZNM39DS5qB0rbrZ3/4oH4/7OdtyTJL5O8Lck5wPZJHt8OinZeO8DfVmN7c9RL6+OVxVJnSR5JM/LpX7VXKa+kuVL0uzRDIPxFu9zmVfXLJAcwQ4+AZsiOK5J8kWYQvY9WM8TxIcB1VfUXaS4p3TzNMNhvoxlA7FfAme1VzafTDEN8flW9Oc3osF8Edq2q65O8iGaAsYWMUirNykKgvnka8Hjg3Pay/41pxpz5HLBdkvcCn6W5ccisqurgJMfQDFj3UuAFbfyn0Y4N3w5h8IskOwBfqKrrAZJ8jGYUytNphjs4qQ37MOARNIUCmlE3r17wq5ZmYSFQ34RmGOM7HNhN8ijgmTR35NqdIb6FV9V3gO8k+TDwsyT3bNuYfvAts4T5/cCYNwG+WVVPmvOVSIvEYwTqmzOB56e9f257dtHSNQPzVdXxNEObP65d/tfApmsLlOTZ7a4faEazvLFd/gyamwqtudfzvWjuFvbUtr0lNLujvrSWsJcC90t7D9skGyV5xIJftTQLewTqlaq6KMlbaXa9bAD8kWZkzVuAj7T/2ItmFFqAI4HDk/we2L6qbhoI97fAYUl+18b571V1axv/A+0ol7cA/1xVJyd5C80w0wH+s6pObYvCYH43JtkDeF+a23kuobk37iWL/25IDU8flaSec9eQJPWchUCSes5CIEk9ZyGQpJ6zEEhSz1kIJKnnLASS1HMWAknquf8Pi0XDHPzru+kAAAAASUVORK5CYII=\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": 10, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
" * ibm_db_sa://svb70790:***@dashdb-txn-sbox-yp-lon02-01.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": 10, | |
"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 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.6" | |
} | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 2 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment