Accessing Databases with SQL Magic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1 align=center><font size = 5>Accessing Databases with SQL Magic</font></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### After using this notebook, you will know how to perform simplified database access using SQL \"magic\". You will connect to a Db2 database, issue SQL commands to create tables, insert data, and run queries, as well as retrieve results in a Python dataframe. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### To communicate with SQL Databases from within a JupyterLab notebook, we can use the SQL \"magic\" provided by the [ipython-sql](https://github.com/catherinedevlin/ipython-sql) extension. \"Magic\" is JupyterLab's term for special commands that start with \"%\". Below, we'll use the _load_\\__ext_ magic to load the ipython-sql extension. In the lab environemnt provided in the course the ipython-sql extension is already installed and so is the ibm_db_sa driver. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Now we have access to SQL magic. With our first SQL magic command, we'll connect to a Db2 database. However, in order to do that, you'll first need to retrieve or create your credentials to access your Db2 database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a ><img src = \"https://ibm.box.com/shared/static/uy78gy1uq3uj6fkvd4muzy5zcr62tb72.png\" width = 1000, align = \"center\"></a>\n",
    "  <h5 align=center>  This image shows the location of your connection string if you're using Db2 on IBM Cloud. If you're using another host the format is: username:password@hostname:port/database-name\n",
    "  </h5>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: fhh86766@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@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB\n",
    "# Note the ibm_db_sa:// prefix instead of db2://\n",
    "# This is because JupyterLab's ipython-sql extension uses sqlalchemy (a python SQL toolkit)\n",
    "# which in turn uses IBM's sqlalchemy dialect: ibm_db_sa\n",
    "%sql ibm_db_sa://fhh86766:t5%2Bftpnsqmncjcd3@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### For convenience, we can use %%sql (two %'s instead of one) at the top of a cell to indicate we want the entire cell to be treated as SQL. Let's use this to create a table and fill it with some test data for experimenting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://fhh86766:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
      "(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0601N  The name of the object to be created is identical to the existing name \"FHH86766.INTERNATIONAL_STUDENT_TEST_SCORES\" of type \"TABLE\".  SQLSTATE=42710 SQLCODE=-601\n",
      "[SQL: 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",
      "(Background on this error at: http://sqlalche.me/e/f405)\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (\n",
    "\tcountry VARCHAR(50),\n",
    "\tfirst_name VARCHAR(50),\n",
    "\tlast_name VARCHAR(50),\n",
    "\ttest_score INT\n",
    ");\n",
    "INSERT INTO INTERNATIONAL_STUDENT_TEST_SCORES (country, first_name, last_name, test_score)\n",
    "VALUES\n",
    "('United States', 'Marshall', 'Bernadot', 54),\n",
    "('Ghana', 'Celinda', 'Malkin', 51),\n",
    "('Ukraine', 'Guillermo', 'Furze', 53),\n",
    "('Greece', 'Aharon', 'Tunnow', 48),\n",
    "('Russia', 'Bail', 'Goodwin', 46),\n",
    "('Poland', 'Cole', 'Winteringham', 49),\n",
    "('Sweden', 'Emlyn', 'Erricker', 55),\n",
    "('Russia', 'Cathee', 'Sivewright', 49),\n",
    "('China', 'Barny', 'Ingerson', 57),\n",
    "('Uganda', 'Sharla', 'Papaccio', 55),\n",
    "('China', 'Stella', 'Youens', 51),\n",
    "('Poland', 'Julio', 'Buesden', 48),\n",
    "('United States', 'Tiffie', 'Cosely', 58),\n",
    "('Poland', 'Auroora', 'Stiffell', 45),\n",
    "('China', 'Clarita', 'Huet', 52),\n",
    "('Poland', 'Shannon', 'Goulden', 45),\n",
    "('Philippines', 'Emylee', 'Privost', 50),\n",
    "('France', 'Madelina', 'Burk', 49),\n",
    "('China', 'Saunderson', 'Root', 58),\n",
    "('Indonesia', 'Bo', 'Waring', 55),\n",
    "('China', 'Hollis', 'Domotor', 45),\n",
    "('Russia', 'Robbie', 'Collip', 46),\n",
    "('Philippines', 'Davon', 'Donisi', 46),\n",
    "('China', 'Cristabel', 'Radeliffe', 48),\n",
    "('China', 'Wallis', 'Bartleet', 58),\n",
    "('Moldova', 'Arleen', 'Stailey', 38),\n",
    "('Ireland', 'Mendel', 'Grumble', 58),\n",
    "('China', 'Sallyann', 'Exley', 51),\n",
    "('Mexico', 'Kain', 'Swaite', 46),\n",
    "('Indonesia', 'Alonso', 'Bulteel', 45),\n",
    "('Armenia', 'Anatol', 'Tankus', 51),\n",
    "('Indonesia', 'Coralyn', 'Dawkins', 48),\n",
    "('China', 'Deanne', 'Edwinson', 45),\n",
    "('China', 'Georgiana', 'Epple', 51),\n",
    "('Portugal', 'Bartlet', 'Breese', 56),\n",
    "('Azerbaijan', 'Idalina', 'Lukash', 50),\n",
    "('France', 'Livvie', 'Flory', 54),\n",
    "('Malaysia', 'Nonie', 'Borit', 48),\n",
    "('Indonesia', 'Clio', 'Mugg', 47),\n",
    "('Brazil', 'Westley', 'Measor', 48),\n",
    "('Philippines', 'Katrinka', 'Sibbert', 51),\n",
    "('Poland', 'Valentia', 'Mounch', 50),\n",
    "('Norway', 'Sheilah', 'Hedditch', 53),\n",
    "('Papua New Guinea', 'Itch', 'Jubb', 50),\n",
    "('Latvia', 'Stesha', 'Garnson', 53),\n",
    "('Canada', 'Cristionna', 'Wadmore', 46),\n",
    "('China', 'Lianna', 'Gatward', 43),\n",
    "('Guatemala', 'Tanney', 'Vials', 48),\n",
    "('France', 'Alma', 'Zavittieri', 44),\n",
    "('China', 'Alvira', 'Tamas', 50),\n",
    "('United States', 'Shanon', 'Peres', 45),\n",
    "('Sweden', 'Maisey', 'Lynas', 53),\n",
    "('Indonesia', 'Kip', 'Hothersall', 46),\n",
    "('China', 'Cash', 'Landis', 48),\n",
    "('Panama', 'Kennith', 'Digance', 45),\n",
    "('China', 'Ulberto', 'Riggeard', 48),\n",
    "('Switzerland', 'Judy', 'Gilligan', 49),\n",
    "('Philippines', 'Tod', 'Trevaskus', 52),\n",
    "('Brazil', 'Herold', 'Heggs', 44),\n",
    "('Latvia', 'Verney', 'Note', 50),\n",
    "('Poland', 'Temp', 'Ribey', 50),\n",
    "('China', 'Conroy', 'Egdal', 48),\n",
    "('Japan', 'Gabie', 'Alessandone', 47),\n",
    "('Ukraine', 'Devlen', 'Chaperlin', 54),\n",
    "('France', 'Babbette', 'Turner', 51),\n",
    "('Czech Republic', 'Virgil', 'Scotney', 52),\n",
    "('Tajikistan', 'Zorina', 'Bedow', 49),\n",
    "('China', 'Aidan', 'Rudeyeard', 50),\n",
    "('Ireland', 'Saunder', 'MacLice', 48),\n",
    "('France', 'Waly', 'Brunstan', 53),\n",
    "('China', 'Gisele', 'Enns', 52),\n",
    "('Peru', 'Mina', 'Winchester', 48),\n",
    "('Japan', 'Torie', 'MacShirrie', 50),\n",
    "('Russia', 'Benjamen', 'Kenford', 51),\n",
    "('China', 'Etan', 'Burn', 53),\n",
    "('Russia', 'Merralee', 'Chaperlin', 38),\n",
    "('Indonesia', 'Lanny', 'Malam', 49),\n",
    "('Canada', 'Wilhelm', 'Deeprose', 54),\n",
    "('Czech Republic', 'Lari', 'Hillhouse', 48),\n",
    "('China', 'Ossie', 'Woodley', 52),\n",
    "('Macedonia', 'April', 'Tyer', 50),\n",
    "('Vietnam', 'Madelon', 'Dansey', 53),\n",
    "('Ukraine', 'Korella', 'McNamee', 52),\n",
    "('Jamaica', 'Linnea', 'Cannam', 43),\n",
    "('China', 'Mart', 'Coling', 52),\n",
    "('Indonesia', 'Marna', 'Causbey', 47),\n",
    "('China', 'Berni', 'Daintier', 55),\n",
    "('Poland', 'Cynthia', 'Hassell', 49),\n",
    "('Canada', 'Carma', 'Schule', 49),\n",
    "('Indonesia', 'Malia', 'Blight', 48),\n",
    "('China', 'Paulo', 'Seivertsen', 47),\n",
    "('Niger', 'Kaylee', 'Hearley', 54),\n",
    "('Japan', 'Maure', 'Jandak', 46),\n",
    "('Argentina', 'Foss', 'Feavers', 45),\n",
    "('Venezuela', 'Ron', 'Leggitt', 60),\n",
    "('Russia', 'Flint', 'Gokes', 40),\n",
    "('China', 'Linet', 'Conelly', 52),\n",
    "('Philippines', 'Nikolas', 'Birtwell', 57),\n",
    "('Australia', 'Eduard', 'Leipelt', 53)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using Python Variables in your SQL Statements\n",
    "##### You can use python variables in your SQL statements by adding a \":\" prefix to your python variable names.\n",
    "##### For example, if I have a python variable `country` with a value of `\"Canada\"`, I can use this variable in a SQL query to find all the rows of students from Canada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://fhh86766:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>test_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Cristionna</td>\n",
       "        <td>Wadmore</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Wilhelm</td>\n",
       "        <td>Deeprose</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Carma</td>\n",
       "        <td>Schule</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Canada', 'Cristionna', 'Wadmore', 46),\n",
       " ('Canada', 'Wilhelm', 'Deeprose', 54),\n",
       " ('Canada', 'Carma', 'Schule', 49)]"
      ]
     },
     "execution_count": 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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### You can use the normal python assignment syntax to assign the results of your queries to python variables.\n",
    "##### For example, I have a SQL query to retrieve the distribution of test scores (i.e. how many students got each score). I can assign the result of this query to the variable `test_score_distribution` using the `=` operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://fhh86766:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Test Score</th>\n",
       "        <th>Frequency</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(38, Decimal('2')),\n",
       " (40, Decimal('1')),\n",
       " (43, Decimal('2')),\n",
       " (44, Decimal('2')),\n",
       " (45, Decimal('8')),\n",
       " (46, Decimal('7')),\n",
       " (47, Decimal('4')),\n",
       " (48, Decimal('14')),\n",
       " (49, Decimal('8')),\n",
       " (50, Decimal('10')),\n",
       " (51, Decimal('8')),\n",
       " (52, Decimal('8')),\n",
       " (53, Decimal('8')),\n",
       " (54, Decimal('5')),\n",
       " (55, Decimal('4')),\n",
       " (56, Decimal('1')),\n",
       " (57, Decimal('2')),\n",
       " (58, Decimal('4')),\n",
       " (60, Decimal('1'))]"
      ]
     },
     "execution_count": 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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### You can easily convert a SQL query result to a pandas dataframe using the `DataFrame()` method. Dataframe objects are much more versatile than SQL query result objects. For example, we can easily graph our test score distribution after converting to a dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjAsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+17YcXAAAX2UlEQVR4nO3deZwcdZnH8c83CbdAkEwgEMaAYkSRRXZgRRZFDg2HHAICC4ocRmEJiqKAKIeCi4JyucBG7lsSjkUQSEDO13IFCCQQLiVCuBJURASJIc/+UTXQGeaorunqnsnv+369+pWq6qrn93R3zZNf1/FrRQRmZpaOIa1OwMzMmsuF38wsMS78ZmaJceE3M0uMC7+ZWWKGtTqBIkaMGBFjxoxpdRpmZoPKAw888EpEtHVdPigK/5gxY5g2bVqr0zAzG1Qk/bG75T7UY2aWGBd+M7PEuPCbmSXGhd/MLDEu/GZmiXHhNzNLTGWFX9K5kuZKmtnNc4dKCkkjqmrfzMy6V2WP/3xgXNeFktYAtgKerbBtMzPrQWWFPyLuAP7czVMnA98D/EMAZmYt0NQ7dyVtDzwfEQ9L6mvd8cB4gPb29iZkZwPJ1tdMKLXdDTue3uBMzBY/TTu5K2lZ4EjgqCLrR8TEiOiIiI62tvcMNWFmZiU186qeDwJrAg9Lmg2MBh6UtGoTczAzS17TDvVExAxgZOd8Xvw7IuKVZuVgZmbVXs55GXA3MFbSHEn7VdWWmZkVV1mPPyL26OP5MVW1bWZmPfOdu2ZmiXHhNzNLjAu/mVliXPjNzBLjwm9mlhgXfjOzxLjwm5klxoXfzCwxLvxmZolx4TczS4wLv5lZYlz4zcwS48JvZpYYF34zs8S48JuZJcaF38wsMS78ZmaJceE3M0uMC7+ZWWJc+M3MElNZ4Zd0rqS5kmbWLDtR0uOSHpF0taThVbVvZmbdq7LHfz4wrsuyqcC6EbEe8CRwRIXtm5lZNyor/BFxB/DnLsumRMSCfPYeYHRV7ZuZWfdaeYx/X+CGnp6UNF7SNEnT5s2b18S0zMwWby0p/JKOBBYAl/S0TkRMjIiOiOhoa2trXnJmZou5Yc1uUNLewHbAFhERzW7fzCx1TS38ksYBhwGfiYg3mtm2mZllqryc8zLgbmCspDmS9gN+CSwPTJU0XdJZVbVvZmbdq6zHHxF7dLP4nKraMzOzYnznrplZYlz4zcwS48JvZpYYF34zs8S48JuZJcaF38wsMS78ZmaJceE3M0uMC7+ZWWJc+M3MEuPCb2aWGBd+M7PEuPCbmSXGhd/MLDEu/GZmiXHhNzNLjAu/mVliXPjNzBLjwm9mlhgXfjOzxLjwm5klprLCL+lcSXMlzaxZ9n5JUyU9lf+7UlXtm5lZ96rs8Z8PjOuy7HDglohYG7glnzczsyaqrPBHxB3An7ss3gG4IJ++ANixqvbNzKx7w5rc3ioR8SJARLwoaWRPK0oaD4wHaG9vb1J6Zu+13ZXnltruup33bXAmZo0xYE/uRsTEiOiIiI62trZWp2NmtthoduF/WdIogPzfuU1u38wsec0u/NcCe+fTewP/2+T2zcySV+XlnJcBdwNjJc2RtB9wArCVpKeArfJ5MzNrospO7kbEHj08tUVVbZqZWd8G7MldMzOrhgu/mVliXPjNzBLjwm9mlhgXfjOzxLjwm5klxoXfzCwxLvxmZolx4TczS0yhwi9p3aoTMTOz5ija4z9L0n2SDpQ0vNKMzMysUoUKf0T8O7AnsAYwTdKlkraqNDMzM6tE4WP8EfEU8APgMOAzwGmSHpf0xaqSMzOzxit6jH89SScDs4DNgS9ExDr59MkV5mdmZg1WdFjmXwK/Ar4fEW92LoyIFyT9oJLMzMysEkUL/zbAmxHxNoCkIcDSEfFGRFxUWXZmZtZwRY/x3wwsUzO/bL7MzMwGmaKFf+mIeL1zJp9etpqUzMysSkUL/98lbdA5I+lfgTd7Wd/MzAaoosf4vwVMkvRCPj8K2K2alMzMrEqFCn9E3C/pI8BYQMDjEfHPso1KOgTYHwhgBrBPRPyjbDwzMyuunkHaNgTWAz4B7CHpK2UalLQ6cDDQERHrAkOB3cvEMjOz+hXq8Uu6CPggMB14O18cwIX9aHcZSf8kO0n8Qh/rm5lZgxQ9xt8BfDQior8NRsTzkk4CniU7QTwlIqZ0XU/SeGA8QHt7e3+bXaxdft7nS223+z43NTiTgWfbq35earvrv/idBmcC202aXGq763bdZZH5HSeXu5L6ml22fGd61ysfKRVj0s7rldrOBpaih3pmAqs2okFJKwE7AGsCqwHLSdqr63oRMTEiOiKio62trRFNm5kZxXv8I4DHJN0HvNW5MCK2L9HmlsAzETEPQNJVwKeAi0vEMjOzOhUt/Mc0sM1ngU9KWpbsUM8WwLQGxjczs14UvZzzdkkfANaOiJvzoj20TIMRca+kycCDwALgIWBimVhmZla/olf1fI3sROv7ya7uWR04i6y3XreIOBo4usy2ZmbWP0VP7v4nsAnwGrzzoywjq0rKzMyqU7TwvxUR8ztnJA0ju47fzMwGmaKF/3ZJ3ye76WorYBLwm+rSMjOzqhQt/IcD88jG1fk68Fuy3981M7NBpuhVPQvJfnrxV9WmY2ZmVSt6Vc8zdHNMPyLWanhGZmZWqXrG6um0NLAr2aWdZmY2yBQ6xh8Rf6p5PB8RpwCbV5ybmZlVoOihng1qZoeQfQNYvpKMzMysUkUP9dSObbsAmA18qeHZWEudfkm54Z0n7Ln4D+9stjgpelXPZ6tOxMzMmqPooZ5v9/Z8RPyiMemYmVnV6rmqZ0Pg2nz+C8AdwHNVJGVmZtWp54dYNoiIvwFIOgaYFBH7V5WYmZlVo+iQDe3A/Jr5+cCYhmdjZmaVK9rjvwi4T9LVZHfw7gRcWFlWZmZWmaJX9Rwv6QZg03zRPhHxUHVpmZlZVYoe6gFYFngtIk4F5khas6KczMysQoUKv6SjgcOAI/JFSwAXV5WUmZlVp2iPfydge+DvABHxAh6ywcxsUCpa+OdHRJAPzSxpuepSMjOzKhUt/FdI+h9guKSvATfTjx9lkTRc0mRJj0uaJWnjsrHMzKw+Ra/qOSn/rd3XgLHAURExtR/tngrcGBG7SFqS7MSxmZk1QZ+FX9JQ4KaI2BLoT7HvjLcC8GngqwARMZ9Fbw4zM7MK9XmoJyLeBt6QtGKD2lyL7Ifbz5P0kKSzuztnIGm8pGmSps2bN69BTZuZWdFj/P8AZkg6R9JpnY+SbQ4DNgDOjIhPkF0pdHjXlSJiYkR0RERHW1tbyabMzKyrokM2XJ8/GmEOMCci7s3nJ9NN4Tczs2r0WvgltUfEsxFxQaMajIiXJD0naWxEPAFsATzWqPhmZta7vg71XNM5IenKBrY7AbhE0iPA+sBPGhjbzMx60dehHtVMr9WoRiNiOtmPu5iZWZP11eOPHqbNzGyQ6qvH/y+SXiPr+S+TT5PPR0SsUGl2ZmbWcL0W/ogY2qxEzMysOeoZj9/MzBYDLvxmZolx4TczS4wLv5lZYlz4zcwS48JvZpaYooO0mZm9x/FXv1hquyN3GtXgTKwe7vGbmSXGhd/MLDEu/GZmiXHhNzNLjAu/mVliXPjNzBLjwm9mlhgXfjOzxLjwm5klxoXfzCwxLSv8koZKekjSda3KwcwsRa3s8X8TmNXC9s3MktSSwi9pNLAtcHYr2jczS1mrevynAN8DFraofTOzZDV9WGZJ2wFzI+IBSZv1st54YDxAe3t7k7Kz/jp08rjS2560y40NzMQGi2smvVJ62x13HdHATBrn5ZOnl9pulUPWb3Am3WtFj38TYHtJs4HLgc0lXdx1pYiYGBEdEdHR1tbW7BzNzBZbTS/8EXFERIyOiDHA7sDvImKvZudhZpYqX8dvZpaYlv70YkTcBtzWyhzMzFLjHr+ZWWJc+M3MEuPCb2aWGBd+M7PEuPCbmSXGhd/MLDEu/GZmiXHhNzNLjAu/mVliXPjNzBLjwm9mlhgXfjOzxLjwm5klxoXfzCwxLvxmZolx4TczS4wLv5lZYlz4zcwS48JvZpYYF34zs8S48JuZJabphV/SGpJulTRL0qOSvtnsHMzMUjasBW0uAL4TEQ9KWh54QNLUiHisBbmYmSWn6T3+iHgxIh7Mp/8GzAJWb3YeZmapakWP/x2SxgCfAO7t5rnxwHiA9vZ2AOadeXGpdtoO2Ktkhj179IztS233sQOvXWT+lrO3LRVni/2vL7WdmfVuzkkvldpu9KGrNjiT6rTs5K6k9wFXAt+KiNe6Ph8REyOiIyI62tramp+gmdliqiWFX9ISZEX/koi4qhU5mJmlqhVX9Qg4B5gVEb9odvtmZqlrRY9/E+DLwOaSpuePbVqQh5lZkpp+cjci7gLU7HbNzCzjO3fNzBLjwm9mlhgXfjOzxLjwm5klxoXfzCwxLvxmZolx4TczS4wLv5lZYlz4zcwS09JhmVvhpTOPLbXdqgcc3eBMzKyR7jtvbqntNtpnZIMzaZy5p08ttd3ICVv1+rx7/GZmiXHhNzNLjAu/mVliXPjNzBLjwm9mlhgXfjOzxLjwm5klxoXfzCwxLvxmZolx4TczS4wLv5lZYlpS+CWNk/SEpKclHd6KHMzMUtX0wi9pKPDfwNbAR4E9JH202XmYmaWqFT3+jYCnI+IPETEfuBzYoQV5mJklSRHR3AalXYBxEbF/Pv9l4N8i4qAu640HxuezY4En+gg9Aniln+k1IsZAi+Ncqo0zkHJpVBznUm2cZubygYho67qwFePxq5tl7/nfJyImAhMLB5WmRURHvxJrQIyBFse5VBtnIOXSqDjOpdo4AyGXVhzqmQOsUTM/GnihBXmYmSWpFYX/fmBtSWtKWhLYHbi2BXmYmSWp6Yd6ImKBpIOAm4ChwLkR8WgDQhc+LFRxjIEWx7lUG2cg5dKoOM6l2jgtz6XpJ3fNzKy1fOeumVliXPjNzBIz6Aq/pKUl3SfpYUmPSjo2X76FpAclTZd0l6QPFYw3VNJDkq7L59eUdK+kpyT9Oj8BXW+Mc/L8HpE0WdL7yuRSs/x0Sa+XfD3nS3omf1+mS1q/ZBxJOl7Sk5JmSTq4ZJw7a3J5QdI1JWI06rPePI8zU9IFkvo85yVptqQZedvT8mXvlzQ132emSlqpRIxd8/15oaRCl+j1EOdESY/n+97VkoaXjPPjPMZ0SVMkrVZvjJrnDpUUkkaUzOUYSc/X7DfblImTL5+gbLiYRyX9rEQuv67JY7ak6SVf0/qS7ulcJmmjPmIMV1ZLHs///jaud79bREQMqgfZfQDvy6eXAO4FPgk8CayTLz8QOL9gvG8DlwLX5fNXALvn02cBB5SIsULNc78ADi+TS76sA7gIeL3k6zkf2KXE+9w1zj7AhcCQfH5k2ddU89yVwFdK5NLvz5qs0/Mc8OH8uR8B+xWIMRsY0WXZzzo/Y+Bw4KclYqxDdqPibUBHwdfTXZzPAcPy6Z/2lUsvcWr34YOBs+qNkS9fg+xCjj9293zBXI4BDq1z/+0uzmeBm4GliuzDPb2mmud/DhxVMpcpwNb59DbAbX3EuADYP59eEhhe735X+xh0Pf7IdPZ+l8gfkT9WyJevSIF7AySNBrYFzs7nBWwOTM5XuQDYsZ4YeY6v1cRbhm5uUCsSR9m4RicC3+tr+55ilNFDnAOAH0XEQoCImNuffCQtT/Ze99rj7yFGvz9rYGXgrYh4Mp+fCuzcV5we7EC2r0CBfaY7ETErIvq6O71InCkRsSCfvYfsPpkycV6rmV2OAvtwD04m238HwlUkBwAnRMRbUGwf7kn+t/0l4LKSIQrvw5JWAD4NnAMQEfMj4lX6sd8NusIP73xlnw7MBaZGxL3A/sBvJc0BvgycUCDUKWQ75cJ8fmXg1Zo/nDnA6nXG6MzxPOAl4CPA6SVyATgIuDYiXiywfY+5AMfnX9tPlrRUyTgfBHbLv5beIGntfuQDsBNwS5cCUzRGIz7rV4Alag6r7MKiNxb2JIApkh5QNqwIwCqdn1H+78gSMcroK86+wA1l4yg7tPccsCdwVL0xJG0PPB8RDxd5Mb3lAhyU78PnFjyk0V2cDwObKjuUe7ukDUvmArAp8HJEPFUyl28BJ+bv70nAEb1svxYwDzhP2aHKsyUtR/37XU1GdXx9GmgPsq87twLrAleRjfkD8F3g7D623Q44I5/ejOzrfxvZAHK1X1Nn1BOjy/NDgTOAfUrkshpwF+9+be/1UE9PuQCjyA6PLUXWK+j1q2kvcV4HvpNPfxG4s0ycmudvAHYumUu/P+t8emPgTuA+4DjgoQL73Gr5vyOBh8l6Yq92Wecv9caoee42ih/q6S3OkcDV5Jdsl42TLz8COLbE+3IvsGK+fDbFDvV0F2cVsr+lIcDxZPf+lIkzEzgt/3vYCHimt/enj/f3zM6/h5K5nNa5/5N9c7i5l+07gAU1+/ypwI/r3e8WWbfoigP1ARyd//H/vmZZO/BYH9v9F1mPfjZZz/wN4BKynmBnsd0YuKnOGBd3WeczdHN8u0Ccv+TTs/PHQmr+UyqZy2Ylc7kYeBwYk68j4K9l4uTPrQz8CVi6RIzrG/RZd31vPgdcUee+dwxwKNkAgqPyZaOAJ+qNUTN/GwULf09xgL2Bu4Fl+xOnZtkHgJl1xvgh2Tfyzv13AfAssGo/cxlTTy5dPqcbgc1qlv8eaCvx/g4DXgZGl31/gb/y7n1UAl7rZZtVgdk185vmfwfl97t6E2/1g6xXPjyfXoasx7YdWcHuPFG3H3BlHTE3491e4CQWPbl7YD0x8g/xQzUf6EnASWVy6bK80Mndbl7PqJpcTiE7xlkmzgnAvjXL7y/7moBvABfU+bl3vr/DGvhZj8z/XQq4Bdi8j22XA5avmf4/YBzZeZjak2w/qzdGzfO3UaDw95LLOOAxihe0nuKsXbPOBGBy2deUL59NHz3+XnIZVbPOIcDlJeN8g+w8FWSHfZ6jhx5/b68pj3V7P9/fWeT/CQFbAA/0EedOYGw+fUy+zxXe77o+WjE6Z3+NAi7IT3wOIeulXSfpa8CVkhaS9Zb3LRn/MOBySccBD5GfUKmD8vxWyKcfJjup1CqXSGrLc5lOtvOXcUIe6xCywz779yOn3Sl2XP49Ihvyo1Gf9XclbUe2H50ZEb/rY/1VgKuz83oMAy6NiBsl3Q9cIWk/sl7triVi7ER2LqgNuF7S9Ij4fIk4T5P9RzY1f+6eiOjtM+8pzpWSxpJ90/wjve833cboZf16c7lI2WXIQfYfyNdLxlkSOFfSTGA+sHfkVbPO17Q7xU/q9pTL68Cpyi4h/gfvDkHfkwlkf39LAn8gu8puCMX3u0V4yAYzs8QMyqt6zMysPBd+M7PEuPCbmSXGhd/MLDEu/GZmiXHht8WSpJVrRlF8SYuO7tjniKs1cfaVtGoPz22S3/4/PR8x8YeNewVm1fHlnLbYk3QM2Q1wJ5XY9i7goIh4z/C7+TXzO0bEzPy+krER8Vg/cx0aEW/3J4ZZX9zjt+RI2lvZbzpMl3SGpCGShuU3Cs1QNj7/wZJ2A9YHft3DN4U2siEgiIi3O4u+pOWVje8/Ix9YbMd8+V418X+SLxsm6VVJx0m6D9hI0ob5IGIP5APirdK0N8eSMBjv3DUrTdK6ZCODfiq/C3gi2Z2YvycbUuDj+XrDI+JVSRPoocdPNgTGU5JuJRt07sLIhvw9BpgXER9XdsvmcGXDQh9HNuDWX4Gb87uGbyQblvfBiPiBstFTbwW2j4hXJO1JNiBXf0bxNFuEC7+lZktgQ2Bafhv9MmRjttwEjJV0KvBbsh/K6FVEHC3pIrIB3r4C7JbH35J8bPR8SIC/SNoc+F1EvAIg6VKyURpvJBs+4Oo87DrAx8j+Y4BsVMo5/X7VZjVc+C01IhvW9z0nYiWtB2xN9otTO1Oglx0RTwNPS/oV8CdJK+ZtdD15pl7CvFkzZoyARyJi0z5fiVlJPsZvqbkZ+JLy33/Nr/5p7xzILiImkQ31vUG+/t+A5bsLJGnb/FAOZKM9vpWvP4XsR3Q6f6t4JbJfw/ps3t4wssNLt3cT9jFgdeW/wSppSUkf6/erNqvhHr8lJSJmSDqW7FDKEOCfZCNPvg2ckxfyIBulFeA84GxJbwIbRcT8mnBfBU6W9EYe5z8iYmEe/4x8FMi3gR9GxLWSjiIbdlnAbyLienX5gfeIeEvSLsBpyn6echjZb7s+2vh3w1LlyznNzBLjQz1mZolx4TczS4wLv5lZYlz4zcwS48JvZpYYF34zs8S48JuZJeb/Ady2h9538n4UAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "dataframe = test_score_distribution.DataFrame()\n",
    "\n",
    "%matplotlib inline\n",
    "# uncomment the following line if you get an module error saying seaborn not found\n",
    "# !pip install seaborn\n",
    "import seaborn\n",
    "\n",
    "plot = seaborn.barplot(x='Test Score',y='Frequency', data=dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you know how to work with Db2 from within JupyterLab notebooks using SQL \"magic\"!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://fhh86766:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>test_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>Marshall</td>\n",
       "        <td>Bernadot</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ghana</td>\n",
       "        <td>Celinda</td>\n",
       "        <td>Malkin</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>Guillermo</td>\n",
       "        <td>Furze</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greece</td>\n",
       "        <td>Aharon</td>\n",
       "        <td>Tunnow</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Bail</td>\n",
       "        <td>Goodwin</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Cole</td>\n",
       "        <td>Winteringham</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sweden</td>\n",
       "        <td>Emlyn</td>\n",
       "        <td>Erricker</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Cathee</td>\n",
       "        <td>Sivewright</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Barny</td>\n",
       "        <td>Ingerson</td>\n",
       "        <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uganda</td>\n",
       "        <td>Sharla</td>\n",
       "        <td>Papaccio</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Stella</td>\n",
       "        <td>Youens</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Julio</td>\n",
       "        <td>Buesden</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>Tiffie</td>\n",
       "        <td>Cosely</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Auroora</td>\n",
       "        <td>Stiffell</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Clarita</td>\n",
       "        <td>Huet</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Shannon</td>\n",
       "        <td>Goulden</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Emylee</td>\n",
       "        <td>Privost</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Madelina</td>\n",
       "        <td>Burk</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Saunderson</td>\n",
       "        <td>Root</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Bo</td>\n",
       "        <td>Waring</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Hollis</td>\n",
       "        <td>Domotor</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Robbie</td>\n",
       "        <td>Collip</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Davon</td>\n",
       "        <td>Donisi</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Cristabel</td>\n",
       "        <td>Radeliffe</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Wallis</td>\n",
       "        <td>Bartleet</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Moldova</td>\n",
       "        <td>Arleen</td>\n",
       "        <td>Stailey</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "        <td>Mendel</td>\n",
       "        <td>Grumble</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Sallyann</td>\n",
       "        <td>Exley</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>Kain</td>\n",
       "        <td>Swaite</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Alonso</td>\n",
       "        <td>Bulteel</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Armenia</td>\n",
       "        <td>Anatol</td>\n",
       "        <td>Tankus</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Coralyn</td>\n",
       "        <td>Dawkins</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Deanne</td>\n",
       "        <td>Edwinson</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Georgiana</td>\n",
       "        <td>Epple</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>Bartlet</td>\n",
       "        <td>Breese</td>\n",
       "        <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Azerbaijan</td>\n",
       "        <td>Idalina</td>\n",
       "        <td>Lukash</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Livvie</td>\n",
       "        <td>Flory</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malaysia</td>\n",
       "        <td>Nonie</td>\n",
       "        <td>Borit</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Clio</td>\n",
       "        <td>Mugg</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Westley</td>\n",
       "        <td>Measor</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Katrinka</td>\n",
       "        <td>Sibbert</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Valentia</td>\n",
       "        <td>Mounch</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norway</td>\n",
       "        <td>Sheilah</td>\n",
       "        <td>Hedditch</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Papua New Guinea</td>\n",
       "        <td>Itch</td>\n",
       "        <td>Jubb</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Latvia</td>\n",
       "        <td>Stesha</td>\n",
       "        <td>Garnson</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Cristionna</td>\n",
       "        <td>Wadmore</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Lianna</td>\n",
       "        <td>Gatward</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala</td>\n",
       "        <td>Tanney</td>\n",
       "        <td>Vials</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Alma</td>\n",
       "        <td>Zavittieri</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Alvira</td>\n",
       "        <td>Tamas</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>Shanon</td>\n",
       "        <td>Peres</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sweden</td>\n",
       "        <td>Maisey</td>\n",
       "        <td>Lynas</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Kip</td>\n",
       "        <td>Hothersall</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Cash</td>\n",
       "        <td>Landis</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama</td>\n",
       "        <td>Kennith</td>\n",
       "        <td>Digance</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Ulberto</td>\n",
       "        <td>Riggeard</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Switzerland</td>\n",
       "        <td>Judy</td>\n",
       "        <td>Gilligan</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Tod</td>\n",
       "        <td>Trevaskus</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Herold</td>\n",
       "        <td>Heggs</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Latvia</td>\n",
       "        <td>Verney</td>\n",
       "        <td>Note</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Temp</td>\n",
       "        <td>Ribey</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Conroy</td>\n",
       "        <td>Egdal</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>Gabie</td>\n",
       "        <td>Alessandone</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>Devlen</td>\n",
       "        <td>Chaperlin</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Babbette</td>\n",
       "        <td>Turner</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>Virgil</td>\n",
       "        <td>Scotney</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tajikistan</td>\n",
       "        <td>Zorina</td>\n",
       "        <td>Bedow</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Aidan</td>\n",
       "        <td>Rudeyeard</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "        <td>Saunder</td>\n",
       "        <td>MacLice</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Waly</td>\n",
       "        <td>Brunstan</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Gisele</td>\n",
       "        <td>Enns</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>Mina</td>\n",
       "        <td>Winchester</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>Torie</td>\n",
       "        <td>MacShirrie</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Benjamen</td>\n",
       "        <td>Kenford</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Etan</td>\n",
       "        <td>Burn</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Merralee</td>\n",
       "        <td>Chaperlin</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Lanny</td>\n",
       "        <td>Malam</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Wilhelm</td>\n",
       "        <td>Deeprose</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>Lari</td>\n",
       "        <td>Hillhouse</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Ossie</td>\n",
       "        <td>Woodley</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macedonia</td>\n",
       "        <td>April</td>\n",
       "        <td>Tyer</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vietnam</td>\n",
       "        <td>Madelon</td>\n",
       "        <td>Dansey</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>Korella</td>\n",
       "        <td>McNamee</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jamaica</td>\n",
       "        <td>Linnea</td>\n",
       "        <td>Cannam</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Mart</td>\n",
       "        <td>Coling</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Marna</td>\n",
       "        <td>Causbey</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Berni</td>\n",
       "        <td>Daintier</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Cynthia</td>\n",
       "        <td>Hassell</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Carma</td>\n",
       "        <td>Schule</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Malia</td>\n",
       "        <td>Blight</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Paulo</td>\n",
       "        <td>Seivertsen</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Niger</td>\n",
       "        <td>Kaylee</td>\n",
       "        <td>Hearley</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>Maure</td>\n",
       "        <td>Jandak</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>Foss</td>\n",
       "        <td>Feavers</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Venezuela</td>\n",
       "        <td>Ron</td>\n",
       "        <td>Leggitt</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Flint</td>\n",
       "        <td>Gokes</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Linet</td>\n",
       "        <td>Conelly</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Nikolas</td>\n",
       "        <td>Birtwell</td>\n",
       "        <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>Eduard</td>\n",
       "        <td>Leipelt</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('United States', 'Marshall', 'Bernadot', 54),\n",
       " ('Ghana', 'Celinda', 'Malkin', 51),\n",
       " ('Ukraine', 'Guillermo', 'Furze', 53),\n",
       " ('Greece', 'Aharon', 'Tunnow', 48),\n",
       " ('Russia', 'Bail', 'Goodwin', 46),\n",
       " ('Poland', 'Cole', 'Winteringham', 49),\n",
       " ('Sweden', 'Emlyn', 'Erricker', 55),\n",
       " ('Russia', 'Cathee', 'Sivewright', 49),\n",
       " ('China', 'Barny', 'Ingerson', 57),\n",
       " ('Uganda', 'Sharla', 'Papaccio', 55),\n",
       " ('China', 'Stella', 'Youens', 51),\n",
       " ('Poland', 'Julio', 'Buesden', 48),\n",
       " ('United States', 'Tiffie', 'Cosely', 58),\n",
       " ('Poland', 'Auroora', 'Stiffell', 45),\n",
       " ('China', 'Clarita', 'Huet', 52),\n",
       " ('Poland', 'Shannon', 'Goulden', 45),\n",
       " ('Philippines', 'Emylee', 'Privost', 50),\n",
       " ('France', 'Madelina', 'Burk', 49),\n",
       " ('China', 'Saunderson', 'Root', 58),\n",
       " ('Indonesia', 'Bo', 'Waring', 55),\n",
       " ('China', 'Hollis', 'Domotor', 45),\n",
       " ('Russia', 'Robbie', 'Collip', 46),\n",
       " ('Philippines', 'Davon', 'Donisi', 46),\n",
       " ('China', 'Cristabel', 'Radeliffe', 48),\n",
       " ('China', 'Wallis', 'Bartleet', 58),\n",
       " ('Moldova', 'Arleen', 'Stailey', 38),\n",
       " ('Ireland', 'Mendel', 'Grumble', 58),\n",
       " ('China', 'Sallyann', 'Exley', 51),\n",
       " ('Mexico', 'Kain', 'Swaite', 46),\n",
       " ('Indonesia', 'Alonso', 'Bulteel', 45),\n",
       " ('Armenia', 'Anatol', 'Tankus', 51),\n",
       " ('Indonesia', 'Coralyn', 'Dawkins', 48),\n",
       " ('China', 'Deanne', 'Edwinson', 45),\n",
       " ('China', 'Georgiana', 'Epple', 51),\n",
       " ('Portugal', 'Bartlet', 'Breese', 56),\n",
       " ('Azerbaijan', 'Idalina', 'Lukash', 50),\n",
       " ('France', 'Livvie', 'Flory', 54),\n",
       " ('Malaysia', 'Nonie', 'Borit', 48),\n",
       " ('Indonesia', 'Clio', 'Mugg', 47),\n",
       " ('Brazil', 'Westley', 'Measor', 48),\n",
       " ('Philippines', 'Katrinka', 'Sibbert', 51),\n",
       " ('Poland', 'Valentia', 'Mounch', 50),\n",
       " ('Norway', 'Sheilah', 'Hedditch', 53),\n",
       " ('Papua New Guinea', 'Itch', 'Jubb', 50),\n",
       " ('Latvia', 'Stesha', 'Garnson', 53),\n",
       " ('Canada', 'Cristionna', 'Wadmore', 46),\n",
       " ('China', 'Lianna', 'Gatward', 43),\n",
       " ('Guatemala', 'Tanney', 'Vials', 48),\n",
       " ('France', 'Alma', 'Zavittieri', 44),\n",
       " ('China', 'Alvira', 'Tamas', 50),\n",
       " ('United States', 'Shanon', 'Peres', 45),\n",
       " ('Sweden', 'Maisey', 'Lynas', 53),\n",
       " ('Indonesia', 'Kip', 'Hothersall', 46),\n",
       " ('China', 'Cash', 'Landis', 48),\n",
       " ('Panama', 'Kennith', 'Digance', 45),\n",
       " ('China', 'Ulberto', 'Riggeard', 48),\n",
       " ('Switzerland', 'Judy', 'Gilligan', 49),\n",
       " ('Philippines', 'Tod', 'Trevaskus', 52),\n",
       " ('Brazil', 'Herold', 'Heggs', 44),\n",
       " ('Latvia', 'Verney', 'Note', 50),\n",
       " ('Poland', 'Temp', 'Ribey', 50),\n",
       " ('China', 'Conroy', 'Egdal', 48),\n",
       " ('Japan', 'Gabie', 'Alessandone', 47),\n",
       " ('Ukraine', 'Devlen', 'Chaperlin', 54),\n",
       " ('France', 'Babbette', 'Turner', 51),\n",
       " ('Czech Republic', 'Virgil', 'Scotney', 52),\n",
       " ('Tajikistan', 'Zorina', 'Bedow', 49),\n",
       " ('China', 'Aidan', 'Rudeyeard', 50),\n",
       " ('Ireland', 'Saunder', 'MacLice', 48),\n",
       " ('France', 'Waly', 'Brunstan', 53),\n",
       " ('China', 'Gisele', 'Enns', 52),\n",
       " ('Peru', 'Mina', 'Winchester', 48),\n",
       " ('Japan', 'Torie', 'MacShirrie', 50),\n",
       " ('Russia', 'Benjamen', 'Kenford', 51),\n",
       " ('China', 'Etan', 'Burn', 53),\n",
       " ('Russia', 'Merralee', 'Chaperlin', 38),\n",
       " ('Indonesia', 'Lanny', 'Malam', 49),\n",
       " ('Canada', 'Wilhelm', 'Deeprose', 54),\n",
       " ('Czech Republic', 'Lari', 'Hillhouse', 48),\n",
       " ('China', 'Ossie', 'Woodley', 52),\n",
       " ('Macedonia', 'April', 'Tyer', 50),\n",
       " ('Vietnam', 'Madelon', 'Dansey', 53),\n",
       " ('Ukraine', 'Korella', 'McNamee', 52),\n",
       " ('Jamaica', 'Linnea', 'Cannam', 43),\n",
       " ('China', 'Mart', 'Coling', 52),\n",
       " ('Indonesia', 'Marna', 'Causbey', 47),\n",
       " ('China', 'Berni', 'Daintier', 55),\n",
       " ('Poland', 'Cynthia', 'Hassell', 49),\n",
       " ('Canada', 'Carma', 'Schule', 49),\n",
       " ('Indonesia', 'Malia', 'Blight', 48),\n",
       " ('China', 'Paulo', 'Seivertsen', 47),\n",
       " ('Niger', 'Kaylee', 'Hearley', 54),\n",
       " ('Japan', 'Maure', 'Jandak', 46),\n",
       " ('Argentina', 'Foss', 'Feavers', 45),\n",
       " ('Venezuela', 'Ron', 'Leggitt', 60),\n",
       " ('Russia', 'Flint', 'Gokes', 40),\n",
       " ('China', 'Linet', 'Conelly', 52),\n",
       " ('Philippines', 'Nikolas', 'Birtwell', 57),\n",
       " ('Australia', 'Eduard', 'Leipelt', 53)]"
      ]
     },
     "execution_count": 9,
     "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;    "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}