c06-databases-and-sql-for-data-science-with-python/res/DB0201EN-Week3-1-3-SQLmagic-v3-py.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\" target=\"_blank\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  />\n",
    "    </a>\n",
    "</p>\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",
    "\n",
    "The following required modules are pre-installed in the Skills Network Labs environment. However if you run this notebook commands in a different Jupyter environment (e.g. Watson Studio or Ananconda) you may need to install these libraries by removing the `#` sign before `!pip` in the code cell below.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# These libraries are pre-installed in SN Labs. If running in another environment please uncomment lines below to install them:\n",
    "# !pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n",
    "# Ensure we don't load_ext with sqlalchemy>=1.4 (incompadible)\n",
    "# !pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24\n",
    "# !pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/images/details.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?security=SSL\n",
    "  </h5>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: cnv39734@BLUDB'"
      ]
     },
     "execution_count": 3,
     "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@hostname:port/BLUDB?security=SSL\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",
    "\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": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cnv39734:***@1bbf73c5-d84a-4bb0-85b9-ab1a4348f4a4.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:32286/BLUDB\n",
      "Done.\n",
      "99 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "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": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cnv39734:***@1bbf73c5-d84a-4bb0-85b9-ab1a4348f4a4.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:32286/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": 5,
     "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": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cnv39734:***@1bbf73c5-d84a-4bb0-85b9-ab1a4348f4a4.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:32286/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, 2),\n",
       " (40, 1),\n",
       " (43, 2),\n",
       " (44, 2),\n",
       " (45, 8),\n",
       " (46, 7),\n",
       " (47, 4),\n",
       " (48, 14),\n",
       " (49, 8),\n",
       " (50, 10),\n",
       " (51, 8),\n",
       " (52, 8),\n",
       " (53, 8),\n",
       " (54, 5),\n",
       " (55, 4),\n",
       " (56, 1),\n",
       " (57, 2),\n",
       " (58, 4),\n",
       " (60, 1)]"
      ]
     },
     "execution_count": 6,
     "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": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAAAWkElEQVR4nO3de9Rl95zn8fcnqbiEECZPLi0phUVpLNdi2hiEyEjQCU00C8skqB5aiJZF0E3oZUajO27TdJrEJa5JJEM0EkaiZzWJilQiF5qmEAkJaR23Vh2+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==",
      "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==0.9.0\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": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cnv39734:***@1bbf73c5-d84a-4bb0-85b9-ab1a4348f4a4.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:32286/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": 8,
     "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/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\" target=\"_blank\">Rav Ahuja</a>\n",
    "\n",
    "## Change Log\n",
    "\n",
    "| Date (YYYY-MM-DD) | Version | Changed By    | Change Description                                 |\n",
    "| ----------------- | ------- | ------------- | -------------------------------------------------- |\n",
    "| 2021-11-17        | 2.3     | Lakshmi       | Updated library                                    |\n",
    "| 2021-07-09        | 2.2     | Malika        | Updated sql magic connection string and screenshot |\n",
    "| 2021-05-06        | 2.1     | Malika Singla | Added libraries                                    |\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 3.8.9 64-bit",
   "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.8.9"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}