Skip to content

Instantly share code, notes, and snippets.

@AakashVasudevan

AakashVasudevan/DB0201EN-Week3-1-3-SQLmagic-v3-py.ipynb

Created December 2, 2020 03:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save AakashVasudevan/ee57195badaf5f356236f016717424bf to your computer and use it in GitHub Desktop.
Save AakashVasudevan/ee57195badaf5f356236f016717424bf to your computer and use it in GitHub Desktop.
Download ZIP
Created on Skills Network Labs
Display the source blob
Display the rendered blob
Raw
DB0201EN-Week3-1-3-SQLmagic-v3-py.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<center>\n",
" <img src=\"https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/Logos/organization_logo/organization_logo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\" />\n",
"</center>\n",
"\n",
"# Accessing Databases with SQL Magic\n",
"\n",
"Estaimted time needed: **15** minutes\n",
"\n",
"## Objectives\n",
"\n",
"After complting this lab you will be able to:\n",
"\n",
"- perform simplified database access using SQL \"magic\"\n"
]
},
{
"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.\n"
]
},
{
"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.\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<a ><img src = \"https://ibm.box.com/shared/static/uy78gy1uq3uj6fkvd4muzy5zcr62tb72.png\" width = 1000, align = \"center\"></a>\n",
"\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>\n"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: lnx60378@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://"
]
},
{
"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.\n"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://lnx60378:***@dashdb-txn-sbox-yp-dal09-12.services.dal.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",
"\n",
"##### You can use python variables in your SQL statements by adding a \":\" prefix to your python variable names.\n",
"\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.\n"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://lnx60378:***@dashdb-txn-sbox-yp-dal09-12.services.dal.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\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### You can use the normal python assignment syntax to assign the results of your queries to python variables.\n",
"\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.\n"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://lnx60378:***@dashdb-txn-sbox-yp-dal09-12.services.dal.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\n"
]
},
{
"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.\n"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAAWkElEQVR4nO3de9Rl95zn8fcnqbiEECZPLi0phUVpLNdi2hiEyEjQCU00C8skqB5aiJZF0E3oZUajO27TdJrEJa5JJEM0EkaiZzWJilQiF5qmEAkJaR23Vh2+88fexZPHeZ5nn1Nnn1OV/X6tddaz9z57f3/fc86vvrXPvvxOqgpJ0nDsMu8EJEmzZeGXpIGx8EvSwFj4JWlgLPySNDBr5p1AF3vttVetW7du3mlI0k7lwgsv/GFVLSxdvlMU/nXr1rFp06Z5pyFJO5Uk3x613EM9kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SB6a3wJzkpyTVJLh3x3LFJKslefbUvSRqtzz3+dwOHLF2Y5ADgYOA7PbYtSVpGb4W/qj4PXDfiqROAlwD+EIAkzcFM79xNchjwvaq6OMlq624ENgKsXbt2BtlpR3LomUdPtN0nH//WKWci3fTM7ORukt2BVwCv7LJ+VZ1YVRuqasPCwu8MNSFJmtAsr+q5C3An4OIkW4D9gS8n2XeGOUjS4M3sUE9VfQXYe9t8W/w3VNUPZ5WDJKnfyzk/CHwBWJ/kyiTP6qstSVJ3ve3xV9VTV3l+XV9tS5KW5527kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgamt8Kf5KQk1yS5dNGyNyT5apJLkpyRZM++2pckjdbnHv+7gUOWLDsHuFdV3Rv4Z+BlPbYvSRqht8JfVZ8Hrluy7OyquqGd/SKwf1/tS5JGm+cx/qOATy73ZJKNSTYl2XTttdfOMC1JummbS+FP8grgBuD9y61TVSdW1Yaq2rCwsDC75CTpJm7NrBtM8kzgccBBVVWzbl+Shm6mhT/JIcBLgYdX1c9n2bYkqdHn5ZwfBL4ArE9yZZJnAW8D9gDOSbI5yTv6al+SNFpve/xV9dQRi9/VV3uSpG68c1eSBsbCL0kDY+GXpIGx8EvSwFj4JWlgLPySNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGx8EvSwFj4JWlgLPySNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGx8EvSwFj4JWlgeiv8SU5Kck2SSxctu32Sc5J8vf17u77alySN1uce/7uBQ5YsOw74bFXdFfhsOy9JmqHeCn9VfR64bsniw4H3tNPvAR7fV/uSpNHWzLi9farqaoCqujrJ3sutmGQjsBFg7dq1M0pP+l2PO/2kibY764lHTTkTaTp22JO7VXViVW2oqg0LCwvzTkeSbjJmXfh/kGQ/gPbvNTNuX5IGb9aF/2PAM9vpZwL/Z8btS9Lg9Xk55weBLwDrk1yZ5FnA64CDk3wdOLidlyTNUG8nd6vqqcs8dVBfbUqSVrfDntyVJPXDwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA9Op8Ce5V9+JSJJmo+se/zuSXJDkeUn27DMhSVK/OhX+qvqvwNOAA4BNST6Q5OBeM5Mk9aLzMf6q+jrw58BLgYcDb0ny1SR/1FdykqTp63qM/95JTgCuAB4J/GFV/X47fUKP+UmSpqzrePxvA/4eeHlV/WLbwqq6Ksmf95KZJKkXXQv/Y4BfVNWvAJLsAtyiqn5eVe/rLTtJ0tR1Pcb/GeCWi+Z3b5dJknYyXQv/Larqp9tm2und+0lJktSnroX/Z0nuv20myQOAX6ywviRpB9X1GP8xwKlJrmrn9wP+eNJGk7wIeDZQwFeAI6vq3yeNJ0nqrlPhr6ovJbk7sB4I8NWq+o9JGkxyB+AFwD2q6hdJPgI8BXj3JPEkSePpuscP8EBgXbvN/ZJQVe/djnZvmeQ/aM4VXLXK+pKkKelU+JO8D7gLsBn4Vbu4gLELf1V9L8kbge/QnCc4u6rOHtHmRmAjwNq1a8dtZlA+dPKjJ9ruKUd+esqZ7Hge+9G/nmi7T/zRi6ecCTzu1NMm2u6sI550o/nHnzbZBXVnPulRv5k+4vRLJopx6hPvPdF22rF03ePfQHNopra3wSS3Aw4H7gT8mObcwdOr6pTF61XVicCJABs2bNjudiVJja5X9VwK7DulNh8FfKuqrm3PE3wU+C9Tii1JWkXXPf69gMuTXAD8ctvCqjpsgja/A/xBkt1pDvUcBGyaII4kaQJdC//x02qwqs5PchrwZeAG4CLaQzqSpP51vZzzvCR3BO5aVZ9p99Z3nbTRqnoV8KpJt5ckTa7rsMzPAU4D/q5ddAfgzJ5ykiT1qOvJ3T8FHgJcD7/5UZa9+0pKktSfroX/l1W1ddtMkjU01/FLknYyXQv/eUleTnO37cHAqcDH+0tLktSXroX/OOBamgHV/gT4B5rf35Uk7WS6XtXza5qfXvz7ftORJPWt61g932LEMf2quvPUM5Ik9WqcsXq2uQVwBHD76acjSepbp2P8VfWjRY/vVdWbgEf2m5okqQ9dD/Xcf9HsLjTfAPboJSPNzVvfP9nwzkc/7aY/vLN0U9L1UM/iQc1vALYAT556NpKk3nW9qucRfSciSZqNrod6/myl56vqb6aTjiSpb+Nc1fNA4GPt/B8Cnwe+20dSkqT+jPNDLPevqp8AJDkeOLWqnt1XYpKkfnQdsmEtsHXR/FZg3dSzkST1ruse//uAC5KcQXMH7xOA9/aWlSSpN12v6nltkk8CD20XHVlVF/WXliSpL10P9QDsDlxfVW8Grkxyp55ykiT1qOtPL74KeCnwsnbRbsApfSUlSepP1z3+JwCHAT8DqKqrcMgGSdopdS38W6uqaIdmTnKr7Wk0yZ5JTkvy1SRXJHnw9sSTJHXXtfB/JMnfAXsmeQ7wGbbvR1neDHyqqu4O3Ae4YjtiSZLGsOpVPUkCfBi4O3A9sB54ZVWdM0mDSW4DPAz47wDtj7hvXWkbSdL0rFr4q6qSnFlVDwAmKvZL3Jnm93tPTnIf4ELghVX1s8UrJdkIbARYu3btFJqVJEH3Qz1fTPLAKbW5Brg/8Paquh/NCePjlq5UVSdW1Yaq2rCwsDClpiVJXQv/I2iK/78kuSTJV5JcMmGbVwJXVtX57fxpNP8RSJJmYMVDPUnWVtV3gEOn1WBVfT/Jd5Osr6qvAQcBl08rviRpZasd4z+TZlTObyc5vaqeOKV2jwben+RmwDeBI6cUV5K0itUKfxZN33lajVbVZpox/iVJM7baMf5aZlqStJNabY//Pkmup9nzv2U7TTtfVXWbXrOTJE3dioW/qnadVSKSpNkYZ1hmSdJNgIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYFYdllmSlvPaM66eaLtXPGG/KWeicbjHL0kDY+GXpIGx8EvSwFj4JWlgLPySNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGZW+FPsmuSi5KcNa8cJGmI5rnH/0Lgijm2L0mDNJfCn2R/4LHAO+fRviQN2bz2+N8EvAT49Zzal6TBmvmwzEkeB1xTVRcmOXCF9TYCGwHWrl07m+S03Y497ZCJt33jkz41xUy0szjz1B9OvO3jj9hriplMzw9O2DzRdvu86L5TzWM589jjfwhwWJItwIeARyY5ZelKVXViVW2oqg0LCwuzzlGSbrJmXvir6mVVtX9VrQOeAvzfqnr6rPOQpKHyOn5JGpi5/vRiVZ0LnDvPHCRpaNzjl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGpiZF/4kByT5XJIrklyW5IWzzkGShmzNHNq8AXhxVX05yR7AhUnOqarL55CLJA3OzPf4q+rqqvpyO/0T4ArgDrPOQ5KGah57/L+RZB1wP+D8Ec9tBDYCrF27FoBr337KRO0sPPfpk6a4rMv+9rCJtrvn8z52o/nPvvOxE8U56NmfmGg7SSu78o3fn2i7/Y/dd8qZ9GduJ3eT3Bo4HTimqq5f+nxVnVhVG6pqw8LCwuwTlKSbqLkU/iS70RT991fVR+eRgyQN1Tyu6gnwLuCKqvqbWbcvSUM3jz3+hwDPAB6ZZHP7eMwc8pCkQZr5yd2q+n9AZt2uJKnhnbuSNDAWfkkaGAu/JA2MhV+SBsbCL0kDY+GXpIGx8EvSwFj4JWlgLPySNDBzHZZ5Hr7/9ldPtN2+z33VlDORNE0XnHzNRNs96Mi9p5zJ9Fzz1nMm2m7vow9e8Xn3+CVpYCz8kjQwFn5JGhgLvyQNjIVfkgbGwi9JA2Phl6SBsfBL0sBY+CVpYCz8kjQwFn5JGpi5FP4khyT5WpJvJDluHjlI0lDNvPAn2RX438ChwD2Apya5x6zzkKShmsce/4OAb1TVN6tqK/Ah4PA55CFJg5Sqmm2DyZOAQ6rq2e38M4D/XFXPX7LeRmBjO7se+NoqofcCfrid6U0jxo4Wx1z6jbMj5TKtOObSb5xZ5nLHqlpYunAe4/FnxLLf+d+nqk4ETuwcNNlUVRu2K7EpxNjR4phLv3F2pFymFcdc+o2zI+Qyj0M9VwIHLJrfH7hqDnlI0iDNo/B/CbhrkjsluRnwFOBjc8hDkgZp5od6quqGJM8HPg3sCpxUVZdNIXTnw0I9x9jR4phLv3F2pFymFcdc+o0z91xmfnJXkjRf3rkrSQNj4ZekgdnpCn+SWyS5IMnFSS5L8up2+X2TfDHJ5iSbkjyoQ6xdk1yU5Kx2/vZJzkny9fbv7TrmtDTOXya5pM3l7CS/N0mcRcuPTVJJ9pogl+OTfK/NZXOSx0yaS5Kj26E2Lkvy+kniJPnwoly2JNk8QYyxP+tl4twnyReSfCXJx5PcpkOMLe36m5NsapeN1W+WiXFE+77+OkmnS/SWifOGJF9t+98ZSfacMM5YfXhUjEXPjdN/R+Uydh9eLp9x+vAyuUzSf0fFGasPJ9kzyWntZ3tFkgeP2+9upKp2qgfNfQC3bqd3A84H/gA4Gzi0Xf4Y4NwOsf4M+ABwVjv/euC4dvo44K865rQ0zm0WPfcC4B2TxGmXHUBzIvzbwF4T5HI8cOwE7/PSOI8APgPcvJ3fe9LXtOi5vwZeOUEuY3/Wy8T5EvDwdvoo4C87xNiy9HMYt98sE+P3aW5UPBfY0PH1jIrz34A17fRfdenDy8QZqw+PijFh/x2Vy9h9eJk4Y/Xh5V7TBP13VC5j9WHgPcCz2+mbAXuO2+8WP3a6Pf5q/LSd3a19VPvYtsd2W1a5NyDJ/sBjgXcuWnw4zRtM+/fxq+UzKk5VXb9olVsx4ga1jvkAnAC8ZDtjjGWZOM8FXldVvwSoqmu2J58kAZ4MfHCCGGN91ivEWQ98vp0+B3jianGWMXa/Waqqrqiq1e5O7xLn7Kq6oZ39Is19MpPEGbsPL6Nz/52Bsfvwcrr23xV07sPtN9GHAe8CqKqtVfVjtqPf7XSFH37zlX0zcA1wTlWdDxwDvCHJd4E3Ai9bJcybaDrkrxct26eqrgZo/+7dIZ1RcUjy2jaXpwGvnCROksOA71XVxR22XzYX4Pnt1/aTOn4dHBXnbsBDk5yf5LwkD9yOfAAeCvygqr4+QYxjGO+zXi7OpcBh7fQR3PjGwuUUcHaSC9MMKwLj95tRMSaxWpyjgE9OGmfMPvw7MSbov8vmwvh9eFSccfvwSu9v1/67XJxj6N6H7wxcC5yc5lDlO5PcisnqVZvRGF+fdrQHzdedzwH3At4CPLFd/mTgMyts9zjgb9vpA/ntV/8fL1nvX1dpf2ScJeu8DHj1uHGA3WkOY922un3tXO417UNzv8QuwGtp7psY+zXRFMm30BxqexDwLdrLgSd5b4C3Ay+eMJfOn/Uqce5O85X7QuBVwI869Lnfa//uDVxMsyc2br/5nRiLnjuX7od6VorzCuCMlT6jLnHG6MOj3pfO/XeVOGP14RXijNuHV3p/V+2/q+QyTr3aANxAM6YZwJuBvxy3391o3a4r7qiP9h/sscC/bfsQ2w/2+hW2+V80Q0dsAb4P/Bw4hWYguP3adfYDvrZK2yPjLFnnjsClE8Q5neYbzZb2cQPwHWDf7chl3YS5nAJ8Cjhw0Xr/AixM8t7Q3Dj4A2D/CXPp/FmP8d7cDbhgzL53fNv3xuo3o2Ismj+XjoV/uTjAM4EvALtvT5xx+vCIGH8xTv8dI5dV+/AKn9NYfXiF97dT/10ll3Hq1b7AlkXzDwU+sV39btzE5/0AFoA92+lbAv9Is0d3xbYPFTgIuLBjvAP57R7gG7jxyZLXj5HX4jh3XbT8aOC0SeIsWb6FDntMI3LZb9HyFwEfmvA1/Q/gNe303YDv0mFvctRrAg4Bzhvzc1+cy0Sf9Yg4e7d/dwHeCxy1yra3AvZYNP1P7Wvp3G+Wi7Ho+XPpUPhXyOUQ4HK6F7Tl4nTuw6u9pq79d4VcxurDK8Tp3IdXek3j9N8VchmrD9PUufXt9PFtn5u4Xs1jdM7ttR/wnjQ/6LIL8JGqOivJj4E3J1kD/Du/HdJ5HK8DPpLkWTR7J0dMmOPrkqynOZ78bZoONy+vT3JfmuOMW4A/mTDOScBJSS4FtgLPrLbHTeApTH5SDOA5bP9nDc2PAP1pO/1R4ORV1t8HOKM5r8ca4ANV9akkX6J7v1kuxhOAt9Ls2HwiyeaqevQEcb4B3Bw4p33ui1W1Uv9bLs7pY/ThkTFWWH/cXN43Zh9eLs7N6N6HV3pN4/Tf5XL5KeP14aOB97ev4ZvAkbT1b5J65ZANkjQwO+VVPZKkyVn4JWlgLPySNDAWfkkaGAu/JA3Mzng5p9RJkv8EfLad3Rf4Fc2t7wAPqqqtq2x/ILC1qv5pxHP70IydcgDNeFFbqqrTyKfSvFn4dZNVVT8C7gvN0L7AT6vqjWOEOBD4Kc1NN0u9hmacqDe38e+9Pbm2MdbUbwdYk3rjoR4NSpIHtAN0XZjk00n2a5e/IMnl7UBgH0qyjuampRe1Y6Y/dEmo/WiGgQCgqi5Z1MZL0oy/fnGS17XLto2/vm2c/Nu1y89N8j+TnAe8cLn8pGnyBi4NQrvH/zPgCcDhVXVtkj8GHl1VRyW5CrhTVf0yyZ5V9eOVviUkeTTwYeAimjHeT66qq5IcSjNOzaOq6udJbl9V1yW5BDi6qs5L8hqa8e6PSXIucHlVPS/JbsB5o/Lr+e3RwHioR0Nyc5qRXLcNZ7ArcHX73CU0t8SfCZy5WqCq+nSSO9OMu3IocFGSewGPovlP4OftetcluS3N+FLntZu/Bzh1UbgPt3/Xr5CfNDUWfg1JgMuq6sEjnnsszXC5hwF/keSeqwWrqutoftHrA2l+zvFhbRvjfo3+WYf8pKnxGL+G5JfAQpIHAyTZLck9k+wCHFBVn6P5sZY9gVsDPwH2GBUoySOT7N5O7wHchWagrLOBoxY9d/uq+jfgXxedJ3gGzSGdpb42Kr8pvG7pRtzj15D8GngS8Jb28Msaml/m+mfglHZZgBPaY/wfB05LcjjN8fl/XBTrAcDbktxAswP1zqr6EjQncoFNSbYC/wC8nGaM/He0/yFsG13xRqpqa5JR+V023bdBQ+fJXUkaGA/1SNLAWPglaWAs/JI0MBZ+SRoYC78kDYyFX5IGxsIvSQPz/wEORZQ7KhIhqwAAAABJRU5ErkJggg==\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\"!\n"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://lnx60378:***@dashdb-txn-sbox-yp-dal09-12.services.dal.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": [
"## Author\n",
"\n",
"<a href=\"https://www.linkedin.com/in/ravahuja/\" target=\"_blank\">Rav Ahuja</a>\n",
"\n",
"## Change Log\n",
"\n",
"| Date (YYYY-MM-DD) | Version | Changed By | Change Description |\n",
"| ----------------- | ------- | ---------- | ---------------------------------- |\n",
"| 2020-07-17 | 2.0 | Lavanya | Moved lab to course repo in GitLab |\n",
"\n",
"<hr>\n",
"\n",
"## <h3 align=\"center\"> © IBM Corporation 2020. All rights reserved. <h3/>\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.11"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment