Created
March 19, 2021 19:21
-
-
Save mabarm/76e55a0a9525cc0b744f1ea05bcaaef1 to your computer and use it in GitHub Desktop.
Created on Skills Network 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": [ | |
| "<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", | |
| "Estimated time needed: **15** minutes\n", | |
| "\n", | |
| "## Objectives\n", | |
| "\n", | |
| "After completing 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": 5, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": [ | |
| "'Connected: fhj65755@BLUDB'" | |
| ] | |
| }, | |
| "execution_count": 5, | |
| "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:[email protected]: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://fhj65755:5j%40tlpxnwqk23t28@dashdb-txn-sbox-yp-lon02-07.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.\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 6, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://fhj65755:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n", | |
| "99 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/plain": [ | |
| "[]" | |
| ] | |
| }, | |
| "execution_count": 6, | |
| "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": 7, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://fhj65755:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <thead>\n", | |
| " <tr>\n", | |
| " <th>country</th>\n", | |
| " <th>first_name</th>\n", | |
| " <th>last_name</th>\n", | |
| " <th>test_score</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\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", | |
| " </tbody>\n", | |
| "</table>" | |
| ], | |
| "text/plain": [ | |
| "[('Canada', 'Cristionna', 'Wadmore', 46),\n", | |
| " ('Canada', 'Wilhelm', 'Deeprose', 54),\n", | |
| " ('Canada', 'Carma', 'Schule', 49)]" | |
| ] | |
| }, | |
| "execution_count": 7, | |
| "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": 8, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://fhj65755:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <thead>\n", | |
| " <tr>\n", | |
| " <th>Test Score</th>\n", | |
| " <th>Frequency</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\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", | |
| " </tbody>\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": 8, | |
| "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": 10, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/Il7ecAAAACXBIWXMAAAsTAAALEwEAmpwYAAAWkElEQVR4nO3de9Rl95zn8fcnqbiEECZPLi0phUVpLNdi2hiEyEjQCU00C8skqB5aiJZF0E3oZUajO27TdJrEJa5JJEM0EkaiZzWJilQiF5qmEAkJaR23Vh2+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": 11, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": [ | |
| " * ibm_db_sa://fhj65755:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n", | |
| "Done.\n" | |
| ] | |
| }, | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<table>\n", | |
| " <thead>\n", | |
| " <tr>\n", | |
| " <th>country</th>\n", | |
| " <th>first_name</th>\n", | |
| " <th>last_name</th>\n", | |
| " <th>test_score</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\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", | |
| " </tbody>\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": 11, | |
| "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.12" | |
| } | |
| }, | |
| "nbformat": 4, | |
| "nbformat_minor": 4 | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment