sql.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "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": 4,
   "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": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: pzz71823@BLUDB'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Enter your Db2 credentials in the connection string below\n",
    "# Recall you created Service Credentials in Part III of the first lab of the course in Week 1\n",
    "# i.e. from the uri field in the Service Credentials copy everything after db2:// (but remove the double quote at the end)\n",
    "# for example, if your credentials are as in the screenshot above, you would write:\n",
    "# %sql ibm_db_sa://my-username: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",
    "\n",
    "%sql ibm_db_sa://pzz71823:3kmfnjk22vf8fh%5E9@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### For convenience, we can use %%sql (two %'s instead of one) at the top of a cell to indicate we want the entire cell to be treated as SQL. Let's use this to create a table and fill it with some test data for experimenting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://pzz71823:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
      "Done.\n",
      "99 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (\n",
    "\tcountry VARCHAR(50),\n",
    "\tfirst_name VARCHAR(50),\n",
    "\tlast_name VARCHAR(50),\n",
    "\ttest_score INT\n",
    ");\n",
    "INSERT INTO INTERNATIONAL_STUDENT_TEST_SCORES (country, first_name, last_name, test_score)\n",
    "VALUES\n",
    "('United States', 'Marshall', 'Bernadot', 54),\n",
    "('Ghana', 'Celinda', 'Malkin', 51),\n",
    "('Ukraine', 'Guillermo', 'Furze', 53),\n",
    "('Greece', 'Aharon', 'Tunnow', 48),\n",
    "('Russia', 'Bail', 'Goodwin', 46),\n",
    "('Poland', 'Cole', 'Winteringham', 49),\n",
    "('Sweden', 'Emlyn', 'Erricker', 55),\n",
    "('Russia', 'Cathee', 'Sivewright', 49),\n",
    "('China', 'Barny', 'Ingerson', 57),\n",
    "('Uganda', 'Sharla', 'Papaccio', 55),\n",
    "('China', 'Stella', 'Youens', 51),\n",
    "('Poland', 'Julio', 'Buesden', 48),\n",
    "('United States', 'Tiffie', 'Cosely', 58),\n",
    "('Poland', 'Auroora', 'Stiffell', 45),\n",
    "('China', 'Clarita', 'Huet', 52),\n",
    "('Poland', 'Shannon', 'Goulden', 45),\n",
    "('Philippines', 'Emylee', 'Privost', 50),\n",
    "('France', 'Madelina', 'Burk', 49),\n",
    "('China', 'Saunderson', 'Root', 58),\n",
    "('Indonesia', 'Bo', 'Waring', 55),\n",
    "('China', 'Hollis', 'Domotor', 45),\n",
    "('Russia', 'Robbie', 'Collip', 46),\n",
    "('Philippines', 'Davon', 'Donisi', 46),\n",
    "('China', 'Cristabel', 'Radeliffe', 48),\n",
    "('China', 'Wallis', 'Bartleet', 58),\n",
    "('Moldova', 'Arleen', 'Stailey', 38),\n",
    "('Ireland', 'Mendel', 'Grumble', 58),\n",
    "('China', 'Sallyann', 'Exley', 51),\n",
    "('Mexico', 'Kain', 'Swaite', 46),\n",
    "('Indonesia', 'Alonso', 'Bulteel', 45),\n",
    "('Armenia', 'Anatol', 'Tankus', 51),\n",
    "('Indonesia', 'Coralyn', 'Dawkins', 48),\n",
    "('China', 'Deanne', 'Edwinson', 45),\n",
    "('China', 'Georgiana', 'Epple', 51),\n",
    "('Portugal', 'Bartlet', 'Breese', 56),\n",
    "('Azerbaijan', 'Idalina', 'Lukash', 50),\n",
    "('France', 'Livvie', 'Flory', 54),\n",
    "('Malaysia', 'Nonie', 'Borit', 48),\n",
    "('Indonesia', 'Clio', 'Mugg', 47),\n",
    "('Brazil', 'Westley', 'Measor', 48),\n",
    "('Philippines', 'Katrinka', 'Sibbert', 51),\n",
    "('Poland', 'Valentia', 'Mounch', 50),\n",
    "('Norway', 'Sheilah', 'Hedditch', 53),\n",
    "('Papua New Guinea', 'Itch', 'Jubb', 50),\n",
    "('Latvia', 'Stesha', 'Garnson', 53),\n",
    "('Canada', 'Cristionna', 'Wadmore', 46),\n",
    "('China', 'Lianna', 'Gatward', 43),\n",
    "('Guatemala', 'Tanney', 'Vials', 48),\n",
    "('France', 'Alma', 'Zavittieri', 44),\n",
    "('China', 'Alvira', 'Tamas', 50),\n",
    "('United States', 'Shanon', 'Peres', 45),\n",
    "('Sweden', 'Maisey', 'Lynas', 53),\n",
    "('Indonesia', 'Kip', 'Hothersall', 46),\n",
    "('China', 'Cash', 'Landis', 48),\n",
    "('Panama', 'Kennith', 'Digance', 45),\n",
    "('China', 'Ulberto', 'Riggeard', 48),\n",
    "('Switzerland', 'Judy', 'Gilligan', 49),\n",
    "('Philippines', 'Tod', 'Trevaskus', 52),\n",
    "('Brazil', 'Herold', 'Heggs', 44),\n",
    "('Latvia', 'Verney', 'Note', 50),\n",
    "('Poland', 'Temp', 'Ribey', 50),\n",
    "('China', 'Conroy', 'Egdal', 48),\n",
    "('Japan', 'Gabie', 'Alessandone', 47),\n",
    "('Ukraine', 'Devlen', 'Chaperlin', 54),\n",
    "('France', 'Babbette', 'Turner', 51),\n",
    "('Czech Republic', 'Virgil', 'Scotney', 52),\n",
    "('Tajikistan', 'Zorina', 'Bedow', 49),\n",
    "('China', 'Aidan', 'Rudeyeard', 50),\n",
    "('Ireland', 'Saunder', 'MacLice', 48),\n",
    "('France', 'Waly', 'Brunstan', 53),\n",
    "('China', 'Gisele', 'Enns', 52),\n",
    "('Peru', 'Mina', 'Winchester', 48),\n",
    "('Japan', 'Torie', 'MacShirrie', 50),\n",
    "('Russia', 'Benjamen', 'Kenford', 51),\n",
    "('China', 'Etan', 'Burn', 53),\n",
    "('Russia', 'Merralee', 'Chaperlin', 38),\n",
    "('Indonesia', 'Lanny', 'Malam', 49),\n",
    "('Canada', 'Wilhelm', 'Deeprose', 54),\n",
    "('Czech Republic', 'Lari', 'Hillhouse', 48),\n",
    "('China', 'Ossie', 'Woodley', 52),\n",
    "('Macedonia', 'April', 'Tyer', 50),\n",
    "('Vietnam', 'Madelon', 'Dansey', 53),\n",
    "('Ukraine', 'Korella', 'McNamee', 52),\n",
    "('Jamaica', 'Linnea', 'Cannam', 43),\n",
    "('China', 'Mart', 'Coling', 52),\n",
    "('Indonesia', 'Marna', 'Causbey', 47),\n",
    "('China', 'Berni', 'Daintier', 55),\n",
    "('Poland', 'Cynthia', 'Hassell', 49),\n",
    "('Canada', 'Carma', 'Schule', 49),\n",
    "('Indonesia', 'Malia', 'Blight', 48),\n",
    "('China', 'Paulo', 'Seivertsen', 47),\n",
    "('Niger', 'Kaylee', 'Hearley', 54),\n",
    "('Japan', 'Maure', 'Jandak', 46),\n",
    "('Argentina', 'Foss', 'Feavers', 45),\n",
    "('Venezuela', 'Ron', 'Leggitt', 60),\n",
    "('Russia', 'Flint', 'Gokes', 40),\n",
    "('China', 'Linet', 'Conelly', 52),\n",
    "('Philippines', 'Nikolas', 'Birtwell', 57),\n",
    "('Australia', 'Eduard', 'Leipelt', 53)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using Python Variables in your SQL Statements\n",
    "##### 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": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://pzz71823:***@dashdb-txn-sbox-yp-lon02-07.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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country = \"Canada\"\n",
    "%sql select * from INTERNATIONAL_STUDENT_TEST_SCORES where country = :country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Assigning the Results of Queries to Python Variables"
   ]
  },
  {
   "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": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://pzz71823:***@dashdb-txn-sbox-yp-lon02-07.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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_score_distribution = %sql SELECT test_score as \"Test Score\", count(*) as \"Frequency\" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;\n",
    "test_score_distribution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Converting Query Results to DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### You can easily convert a SQL query result to a pandas dataframe using the `DataFrame()` method. Dataframe objects are much more versatile than SQL query result objects. For example, we can easily graph our test score distribution after converting to a dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8QZhcZAAAWxElEQVR4nO3de7RkZXnn8e8PWhQMiobDRbFtzdKOlxh1WiaJY7wAExSCGMDLaEJE7YyJGI0sxXgBx5gxikEwo6ZFEMVLBMRRiAoY0ckaBRttuepgYqt4o9EYNF4QfOaPvVsPhzrn7KpTl+7e389atc7eu/Z+3qeq3n561768lapCktQfO806AUnSdFn4JalnLPyS1DMWfknqGQu/JPXMqlkn0MWee+5Za9asmXUakrRdufzyy2+sqrmFy7eLwr9mzRo2btw46zQkabuS5KuDlnuoR5J6xsIvST1j4ZeknrHwS1LPWPglqWcs/JLUMxMr/ElOT3JDkqsGPHdckkqy56TalyQNNsk9/ncABy9cmORewEHA1ybYtiRpERMr/FX1KeB7A546GXgx4A8BSNIMTPXO3SSHAd+oqi8kWW7d9cB6gNWrV08hO21LHv/BY0fa7iOHv2nMmUg7nqmd3E2yG/Ay4JVd1q+qDVW1rqrWzc3dbqgJSdKIpnlVz68B9wG+kGQzsB/wuST7TDEHSeq9qR3qqaorgb22zrfFf11V3TitHCRJk72c873Ap4G1Sa5P8qxJtSVJ6m5ie/xV9bRlnl8zqbYlSYvzzl1J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPTOxwp/k9CQ3JLlq3rLXJ/likiuSnJdkj0m1L0kabJJ7/O8ADl6w7CLgwVX1EOD/AS+dYPuSpAEmVvir6lPA9xYsu7CqbmlnPwPsN6n2JUmDzfIY/zHARxZ7Msn6JBuTbNyyZcsU05KkHdtMCn+SlwG3AO9ebJ2q2lBV66pq3dzc3PSSk6Qd3KppN5jkaOBQ4ICqqmm3L0l9N9XCn+Rg4CXAo6vqR9NsW5LUmOTlnO8FPg2sTXJ9kmcBfwfsDlyUZFOSt06qfUnSYBPb46+qpw1Y/PZJtSdJ6sY7dyWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknpmYoU/yelJbkhy1bxld09yUZLr2r93m1T7kqTBJrnH/w7g4AXLjgc+XlX3Az7ezkuSpmhihb+qPgV8b8HiJwJnttNnAodPqn1J0mCrptze3lX1LYCq+laSvRZbMcl6YD3A6tWrp5SedHuHnnv6SNudf8QxY85EGo9t9uRuVW2oqnVVtW5ubm7W6UjSDmPahf87SfYFaP/eMOX2Jan3pl34PwQc3U4fDfzvKbcvSb03ycs53wt8Glib5PokzwJeCxyU5DrgoHZekjRFEzu5W1VPW+SpAybVpiRpedvsyV1J0mRY+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs90KvxJHjzpRCRJ09F1j/+tSS5L8qdJ9phoRpKkiepU+KvqvwBPB+4FbEzyniQHTTQzSdJEdD7GX1XXAS8HXgI8Gjg1yReT/MGkkpMkjV/XY/wPSXIycC3wOOD3q+oB7fTJE8xPkjRmXYdl/jvgbcBfVtWPty6sqm8meflEMpMkTUTXwv8E4MdVdStAkp2AO1XVj6rqXRPLTpI0dl2P8V8M7Dpvfrd2mSRpO9O18N+pqn64daad3m0yKUmSJqlr4f+PJA/fOpPkPwE/XmJ9SdI2qusx/hcAZyf5Zju/L/CUyaQkSZqkToW/qj6b5NeBtUCAL1bVz0ZtNMkLgWcDBVwJPLOqfjJqPElSd8MM0vYI4CHAw4CnJfmjURpMck/g+cC6qnowsDPw1FFiSZKG12mPP8m7gF8DNgG3tosLeOcK2t01yc9oThJ/c5n1JUlj0vUY/zrggVVVK22wqr6R5CTgazQniC+sqgsXrpdkPbAeYPXq1Sttdof2vjN+b6TtnvrMj405k23PIR94w0jbXfAHLxpzJnDo2eeMtN35Rx15m/nDzxntSuoPHnngL6aPOveKkWKcfcRDRtpO25auh3quAvYZR4NJ7gY8EbgPcA/gzkmesXC9qtpQVeuqat3c3Nw4mpYk0X2Pf0/gmiSXAT/durCqDhuhzQOBr1TVFoAkHwB+BzhrhFiSpCF1LfwnjrHNrwG/lWQ3mkM9BwAbxxhfkrSErpdzfjLJvYH7VdXFbdHeeZQGq+rSJOcAnwNuAT4PbBglliRpeF2v6nkOzYnWu9Nc3XNP4K00e+tDq6oTgBNG2VaStDJdT+7+GfBI4Cb4xY+y7DWppCRJk9O18P+0qm7eOpNkFc11/JKk7UzXwv/JJH9Jc9PVQcDZwIcnl5YkaVK6Fv7jgS004+r8CfCPNL+/K0naznS9qufnND+9+LbJpiNJmrSuV/V8hQHH9KvqvmPPSJI0UcOM1bPVnYCjaC7tlCRtZzod46+q7857fKOq3gg8bsK5SZImoOuhnofPm92J5hvA7hPJSJI0UV0P9cwf2/YWYDPw5LFno5l607tHG9752Kfv+MM7SzuSrlf1PHbSiUiSpqProZ6/WOr5qvrb8aQjSZq0Ya7qeQTwoXb+94FPAV+fRFKSpMkZ5odYHl5VPwBIciJwdlU9e1KJSZImo+uQDauBm+fN3wysGXs2kqSJ67rH/y7gsiTn0dzB+yTgnRPLSpI0MV2v6nlNko8Aj2oXPbOqPj+5tCRJk9L1UA/AbsBNVXUKcH2S+0woJ0nSBHUq/ElOAF4CvLRddAfgrEklJUmanK57/E8CDgP+A6CqvolDNkjSdqlr4b+5qop2aOYkd55cSpKkSepa+N+f5O+BPZI8B7iYFfwoS5I9kpyT5ItJrk3y26PGkiQNp+tVPSe1v7V7E7AWeGVVXbSCdk8BPlpVRybZhebEsSRpCpYt/El2Bj5WVQcCKyn2W+PdBfhd4I8BqupmbntzmCRpgpY91FNVtwI/SnLXMbV5X5ofbj8jyeeTnDbonEGS9Uk2Jtm4ZcuWMTUtSep6jP8nwJVJ3p7k1K2PEdtcBTwceEtVPYzmSqHjF65UVRuqal1VrZubmxuxKUnSQl2HbLigfYzD9cD1VXVpO38OAwq/JGkyliz8SVZX1deq6sxxNVhV307y9SRrq+pLwAHANeOKL0la2nKHej64dSLJuWNs91jg3UmuAB4K/PUYY0uSlrDcoZ7Mm77vuBqtqk00P+4iSZqy5fb4a5FpSdJ2ark9/t9MchPNnv+u7TTtfFXVXSaanSRp7JYs/FW187QSkSRNxzDj8UuSdgAWfknqGQu/JPWMhV+SesbCL0k9Y+GXpJ7pOkibJN3Oa8771kjbvexJ+445Ew3DPX5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1zMwKf5Kdk3w+yfmzykGS+miWe/x/Dlw7w/YlqZdmUviT7AccApw2i/Ylqc9mtcf/RuDFwM9n1L4k9dbUh2VOcihwQ1VdnuQxS6y3HlgPsHr16illp5U67pyDR972pCM/OsZMtL344Nk3jrzt4UftOcZMxuc7J28aabu9X/jQMWcy2Cz2+B8JHJZkM/A+4HFJzlq4UlVtqKp1VbVubm5u2jlK0g5r6oW/ql5aVftV1RrgqcA/VdUzpp2HJPWV1/FLUs/M9KcXq+oS4JJZ5iBJfeMevyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9MvfAnuVeSTyS5NsnVSf582jlIUp+tmkGbtwAvqqrPJdkduDzJRVV1zQxykaTemfoef1V9q6o+107/ALgWuOe085CkvprFHv8vJFkDPAy4dMBz64H1AKtXrwZgy1vOGqmduec+Y8QMF3f1mw8babsH/emHbjP/8dMOGSnOAc++YKTtJC3t+pO+PdJ2+x23z5gzmZyZndxN8ivAucALquqmhc9X1YaqWldV6+bm5qafoCTtoGZS+JPcgabov7uqPjCLHCSpr2ZxVU+AtwPXVtXfTrt9Seq7WezxPxL4Q+BxSTa1jyfMIA9J6qWpn9ytqn8GMu12JUkN79yVpJ6x8EtSz1j4JalnLPyS1DMWfknqGQu/JPWMhV+SesbCL0k9Y+GXpJ6Z6bDMs/Dtt7xqpO32ee4JY85E0jhddsYNI223/zP3GnMm43PDmy4aabu9jj1oyefd45eknrHwS1LPWPglqWcs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST1j4ZeknrHwS1LPWPglqWdmUviTHJzkS0m+nOT4WeQgSX019cKfZGfgfwGPBx4IPC3JA6edhyT11Sz2+PcHvlxV/1pVNwPvA544gzwkqZdSVdNtMDkSOLiqnt3O/yHwn6vqeQvWWw+sb2fXAl9aJvSewI0rTG8cMba1OOYy2TjbUi7jimMuk40zzVzuXVVzCxfOYjz+DFh2u/99qmoDsKFz0GRjVa1bUWJjiLGtxTGXycbZlnIZVxxzmWycbSGXWRzquR6417z5/YBvziAPSeqlWRT+zwL3S3KfJLsATwU+NIM8JKmXpn6op6puSfI84GPAzsDpVXX1GEJ3Piw04RjbWhxzmWycbSmXccUxl8nGmXkuUz+5K0maLe/claSesfBLUs9sd4U/yZ2SXJbkC0muTvKqdvlDk3wmyaYkG5Ps3yHWzkk+n+T8dv7uSS5Kcl37924dc1oY59VJrmhzuTDJPUaJM2/5cUkqyZ4j5HJikm+0uWxK8oRRc0lybDvUxtVJXjdKnCT/MC+XzUk2jRBj6M96kTi/meTTSa5M8uEkd+kQY3O7/qYkG9tlQ/WbRWIc1b6vP0/S6RK9ReK8PskX2/53XpI9RowzVB8eFGPec8P030G5DN2HF8tnmD68SC6j9N9BcYbqw0n2SHJO+9lem+S3h+13t1FV29WD5j6AX2mn7wBcCvwWcCHw+Hb5E4BLOsT6C+A9wPnt/OuA49vp44G/6ZjTwjh3mffc84G3jhKnXXYvmhPhXwX2HCGXE4HjRnifF8Z5LHAxcMd2fq9RX9O8594AvHKEXIb+rBeJ81ng0e30McCrO8TYvPBzGLbfLBLjATQ3Kl4CrOv4egbF+a/Aqnb6b7r04UXiDNWHB8UYsf8OymXoPrxInKH68GKvaYT+OyiXofowcCbw7HZ6F2CPYfvd/Md2t8dfjR+2s3doH9U+tu6x3ZVl7g1Ish9wCHDavMVPpHmDaf8evlw+g+JU1U3zVrkzA25Q65gPwMnAi1cYYyiLxHku8Nqq+ilAVd2wknySBHgy8N4RYgz1WS8RZy3wqXb6IuCI5eIsYuh+s1BVXVtVy92d3iXOhVV1Szv7GZr7ZEaJM3QfXkTn/jsFQ/fhxXTtv0vo3Ifbb6K/C7wdoKpurqrvs4J+t90VfvjFV/ZNwA3ARVV1KfAC4PVJvg6cBLx0mTBvpOmQP5+3bO+q+hZA+3evDukMikOS17S5PB145ShxkhwGfKOqvtBh+0VzAZ7Xfm0/vePXwUFx7g88KsmlST6Z5BEryAfgUcB3quq6EWIM+1kvFucq4LB2+ihue2PhYgq4MMnlaYYVgeH7zaAYo1guzjHAR0aNM2Qfvl2MEfrvorkwfB8eFGfYPrzU+9u1/y4WZ5g+fF9gC3BGmkOVpyW5M6PVqzajIb4+bWsPmq87nwAeDJwKHNEufzJw8RLbHQq8uZ1+DL/86v/9Bev92zLtD4yzYJ2XAq8aNg6wG81hrLtWt6+di72mvWnul9gJeA3NfRNDvyaaInkqzaG2/YGv0F4OPMp7A7wFeNGIuXT+rJeJ8+s0X7kvB04Avtuhz92j/bsX8AWaPbFh+83tYsx77hK6H+pZKs7LgPOW+oy6xBmiDw96Xzr332XiDNWHl4gzbB9e6v1dtv8uk8sw9WodcAvNmGYApwCvHrbf3Wbdrituq4/2H+xxwL9v/RDbD/amJbb5nzRDR2wGvg38CDiLZiC4fdt19gW+tEzbA+MsWOfewFUjxDmX5hvN5vZxC/A1YJ8V5LJmxFzOAj4KPGbeev8CzI3y3tDcOPgdYL8Rc+n8WQ/x3twfuGzIvndi2/eG6jeDYsybv4SOhX+xOMDRwKeB3VYSZ5g+PCDGK4bpv0PksmwfXuJzGqoPL/H+duq/y+QyTL3aB9g8b/5RwAUr6nfDJj7rBzAH7NFO7wr8H5o9umu3fqjAAcDlHeM9hl/uAb6e254sed0Qec2Pc795y48FzhklzoLlm+mwxzQgl33nLX8h8L4RX9N/B/5HO31/4Ot02Jsc9JqAg4FPDvm5z89lpM96QJy92r87Ae8Ejllm2zsDu8+b/r/ta+ncbxaLMe/5S+hQ+JfI5WDgGroXtMXidO7Dy72mrv13iVyG6sNLxOnch5d6TcP03yVyGaoP09S5te30iW2fG7lezWJ0zpXaFzgzzQ+67AS8v6rOT/J94JQkq4Cf8MshnYfxWuD9SZ5Fs3dy1Ig5vjbJWprjyV+l6XCz8rokD6U5zrgZ+JMR45wOnJ7kKuBm4Ohqe9wInsroJ8UAnsPKP2tofgToz9rpDwBnLLP+3sB5zXk9VgHvqaqPJvks3fvNYjGeBLyJZsfmgiSbqur3RojzZeCOwEXtc5+pqqX632Jxzh2iDw+MscT6w+byriH78GJxdqF7H17qNQ3TfxfL5YcM14ePBd7dvoZ/BZ5JW/9GqVcO2SBJPbNdXtUjSRqdhV+SesbCL0k9Y+GXpJ6x8EtSz1j4tUNK8qvzRlH8dm47uuMuQ8Q5Jsk+izz3yPb2/03tiImvGN8rkCbHyzm1w0tyIvDDqjpphG3/GXheVd1u+N32mvnDq+qq9r6StVV1zQpz3bmqbl1JDGk57vGrd5IcneY3HTYleXOSnZKsam8UujLJVUmen+QpwEOBf1jkm8IczRAQVNWtW4t+kt2TnNnGuiLJ4e3yZ8yL/9ftslVJvp/kr5JcBuyf5BHtIGKXJ/lIkr2n9uaoF7bHO3elkSV5MPAk4Heq6pYkG2juxPwXmiEFfqNdb4+q+n6SY1lkj59mxM/rknyCZhTMd1Yz5O+JwJaq+o00t2zukWZY6L+iGXDr34GLkxxKM37MXYHPVdXLk9yRZuDBw6rqxiRPpxmQayWjeEq3YeFX3xwIPALY2N5GvyvNmC0fA9YmOQX4R5pRO5dUVSckeRfNj5/8EfCUNv6BtGOjt0MC/FuSxwH/VFU3AiR5D80ojR+lGT7gvDbsA4AH0fzHAM2olNev+FVL81j41TehGdb3didikzwEeDzNL04dQYe97Kr6MvDlJG8Dvpvkrm0bC0+eZYkwP543ZkyAK6rqUcu+EmlEHuNX31wMPDnt77+2V/+sTjJHc7HD2TRDfT+8Xf8HwO6DAiU5pD2UA81ojz9t178QeF67TtL8cMhngMe27a2iObz0yQFhrwHumfY3WJPskuRBK37V0jzu8atXqurKJK+iOZSyE/AzmpEnbwXe3hbyAl7SbnIGcFqSHwP7V9XN88L9MXBykh+1cf5bVf28jf/mdhTIW4FXVNWHkrySZtjlAB+uqgva/wTm5/fTJEcCpybZnebf6BuAq8f/bqivvJxTknrGQz2S1DMWfknqGQu/JPWMhV+SesbCL0k9Y+GXpJ6x8EtSz/x/CUeBs4Y9/QAAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "dataframe = test_score_distribution.DataFrame()\n",
    "\n",
    "%matplotlib inline\n",
    "# uncomment the following line if you get an module error saying seaborn not found\n",
    "# !pip install seaborn\n",
    "import seaborn\n",
    "\n",
    "plot = seaborn.barplot(x='Test Score',y='Frequency', data=dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you know how to work with Db2 from within JupyterLab notebooks using SQL \"magic\"!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://pzz71823:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>country</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>test_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>Marshall</td>\n",
       "        <td>Bernadot</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ghana</td>\n",
       "        <td>Celinda</td>\n",
       "        <td>Malkin</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>Guillermo</td>\n",
       "        <td>Furze</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greece</td>\n",
       "        <td>Aharon</td>\n",
       "        <td>Tunnow</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Bail</td>\n",
       "        <td>Goodwin</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Cole</td>\n",
       "        <td>Winteringham</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sweden</td>\n",
       "        <td>Emlyn</td>\n",
       "        <td>Erricker</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Cathee</td>\n",
       "        <td>Sivewright</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Barny</td>\n",
       "        <td>Ingerson</td>\n",
       "        <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uganda</td>\n",
       "        <td>Sharla</td>\n",
       "        <td>Papaccio</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Stella</td>\n",
       "        <td>Youens</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Julio</td>\n",
       "        <td>Buesden</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>Tiffie</td>\n",
       "        <td>Cosely</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Auroora</td>\n",
       "        <td>Stiffell</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Clarita</td>\n",
       "        <td>Huet</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Shannon</td>\n",
       "        <td>Goulden</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Emylee</td>\n",
       "        <td>Privost</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Madelina</td>\n",
       "        <td>Burk</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Saunderson</td>\n",
       "        <td>Root</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Bo</td>\n",
       "        <td>Waring</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Hollis</td>\n",
       "        <td>Domotor</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Robbie</td>\n",
       "        <td>Collip</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Davon</td>\n",
       "        <td>Donisi</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Cristabel</td>\n",
       "        <td>Radeliffe</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Wallis</td>\n",
       "        <td>Bartleet</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Moldova</td>\n",
       "        <td>Arleen</td>\n",
       "        <td>Stailey</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "        <td>Mendel</td>\n",
       "        <td>Grumble</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Sallyann</td>\n",
       "        <td>Exley</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>Kain</td>\n",
       "        <td>Swaite</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Alonso</td>\n",
       "        <td>Bulteel</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Armenia</td>\n",
       "        <td>Anatol</td>\n",
       "        <td>Tankus</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Coralyn</td>\n",
       "        <td>Dawkins</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Deanne</td>\n",
       "        <td>Edwinson</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Georgiana</td>\n",
       "        <td>Epple</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>Bartlet</td>\n",
       "        <td>Breese</td>\n",
       "        <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Azerbaijan</td>\n",
       "        <td>Idalina</td>\n",
       "        <td>Lukash</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Livvie</td>\n",
       "        <td>Flory</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malaysia</td>\n",
       "        <td>Nonie</td>\n",
       "        <td>Borit</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Clio</td>\n",
       "        <td>Mugg</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Westley</td>\n",
       "        <td>Measor</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Katrinka</td>\n",
       "        <td>Sibbert</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Valentia</td>\n",
       "        <td>Mounch</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norway</td>\n",
       "        <td>Sheilah</td>\n",
       "        <td>Hedditch</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Papua New Guinea</td>\n",
       "        <td>Itch</td>\n",
       "        <td>Jubb</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Latvia</td>\n",
       "        <td>Stesha</td>\n",
       "        <td>Garnson</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Cristionna</td>\n",
       "        <td>Wadmore</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Lianna</td>\n",
       "        <td>Gatward</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala</td>\n",
       "        <td>Tanney</td>\n",
       "        <td>Vials</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Alma</td>\n",
       "        <td>Zavittieri</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Alvira</td>\n",
       "        <td>Tamas</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>Shanon</td>\n",
       "        <td>Peres</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sweden</td>\n",
       "        <td>Maisey</td>\n",
       "        <td>Lynas</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Kip</td>\n",
       "        <td>Hothersall</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Cash</td>\n",
       "        <td>Landis</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama</td>\n",
       "        <td>Kennith</td>\n",
       "        <td>Digance</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Ulberto</td>\n",
       "        <td>Riggeard</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Switzerland</td>\n",
       "        <td>Judy</td>\n",
       "        <td>Gilligan</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Tod</td>\n",
       "        <td>Trevaskus</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Herold</td>\n",
       "        <td>Heggs</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Latvia</td>\n",
       "        <td>Verney</td>\n",
       "        <td>Note</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Temp</td>\n",
       "        <td>Ribey</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Conroy</td>\n",
       "        <td>Egdal</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>Gabie</td>\n",
       "        <td>Alessandone</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>Devlen</td>\n",
       "        <td>Chaperlin</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Babbette</td>\n",
       "        <td>Turner</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>Virgil</td>\n",
       "        <td>Scotney</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tajikistan</td>\n",
       "        <td>Zorina</td>\n",
       "        <td>Bedow</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Aidan</td>\n",
       "        <td>Rudeyeard</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "        <td>Saunder</td>\n",
       "        <td>MacLice</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Waly</td>\n",
       "        <td>Brunstan</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Gisele</td>\n",
       "        <td>Enns</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>Mina</td>\n",
       "        <td>Winchester</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>Torie</td>\n",
       "        <td>MacShirrie</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Benjamen</td>\n",
       "        <td>Kenford</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Etan</td>\n",
       "        <td>Burn</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Merralee</td>\n",
       "        <td>Chaperlin</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Lanny</td>\n",
       "        <td>Malam</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Wilhelm</td>\n",
       "        <td>Deeprose</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>Lari</td>\n",
       "        <td>Hillhouse</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Ossie</td>\n",
       "        <td>Woodley</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macedonia</td>\n",
       "        <td>April</td>\n",
       "        <td>Tyer</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vietnam</td>\n",
       "        <td>Madelon</td>\n",
       "        <td>Dansey</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>Korella</td>\n",
       "        <td>McNamee</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jamaica</td>\n",
       "        <td>Linnea</td>\n",
       "        <td>Cannam</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Mart</td>\n",
       "        <td>Coling</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Marna</td>\n",
       "        <td>Causbey</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Berni</td>\n",
       "        <td>Daintier</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Cynthia</td>\n",
       "        <td>Hassell</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Carma</td>\n",
       "        <td>Schule</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>Malia</td>\n",
       "        <td>Blight</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Paulo</td>\n",
       "        <td>Seivertsen</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Niger</td>\n",
       "        <td>Kaylee</td>\n",
       "        <td>Hearley</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>Maure</td>\n",
       "        <td>Jandak</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>Foss</td>\n",
       "        <td>Feavers</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Venezuela</td>\n",
       "        <td>Ron</td>\n",
       "        <td>Leggitt</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Flint</td>\n",
       "        <td>Gokes</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>Linet</td>\n",
       "        <td>Conelly</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>Nikolas</td>\n",
       "        <td>Birtwell</td>\n",
       "        <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>Eduard</td>\n",
       "        <td>Leipelt</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('United States', 'Marshall', 'Bernadot', 54),\n",
       " ('Ghana', 'Celinda', 'Malkin', 51),\n",
       " ('Ukraine', 'Guillermo', 'Furze', 53),\n",
       " ('Greece', 'Aharon', 'Tunnow', 48),\n",
       " ('Russia', 'Bail', 'Goodwin', 46),\n",
       " ('Poland', 'Cole', 'Winteringham', 49),\n",
       " ('Sweden', 'Emlyn', 'Erricker', 55),\n",
       " ('Russia', 'Cathee', 'Sivewright', 49),\n",
       " ('China', 'Barny', 'Ingerson', 57),\n",
       " ('Uganda', 'Sharla', 'Papaccio', 55),\n",
       " ('China', 'Stella', 'Youens', 51),\n",
       " ('Poland', 'Julio', 'Buesden', 48),\n",
       " ('United States', 'Tiffie', 'Cosely', 58),\n",
       " ('Poland', 'Auroora', 'Stiffell', 45),\n",
       " ('China', 'Clarita', 'Huet', 52),\n",
       " ('Poland', 'Shannon', 'Goulden', 45),\n",
       " ('Philippines', 'Emylee', 'Privost', 50),\n",
       " ('France', 'Madelina', 'Burk', 49),\n",
       " ('China', 'Saunderson', 'Root', 58),\n",
       " ('Indonesia', 'Bo', 'Waring', 55),\n",
       " ('China', 'Hollis', 'Domotor', 45),\n",
       " ('Russia', 'Robbie', 'Collip', 46),\n",
       " ('Philippines', 'Davon', 'Donisi', 46),\n",
       " ('China', 'Cristabel', 'Radeliffe', 48),\n",
       " ('China', 'Wallis', 'Bartleet', 58),\n",
       " ('Moldova', 'Arleen', 'Stailey', 38),\n",
       " ('Ireland', 'Mendel', 'Grumble', 58),\n",
       " ('China', 'Sallyann', 'Exley', 51),\n",
       " ('Mexico', 'Kain', 'Swaite', 46),\n",
       " ('Indonesia', 'Alonso', 'Bulteel', 45),\n",
       " ('Armenia', 'Anatol', 'Tankus', 51),\n",
       " ('Indonesia', 'Coralyn', 'Dawkins', 48),\n",
       " ('China', 'Deanne', 'Edwinson', 45),\n",
       " ('China', 'Georgiana', 'Epple', 51),\n",
       " ('Portugal', 'Bartlet', 'Breese', 56),\n",
       " ('Azerbaijan', 'Idalina', 'Lukash', 50),\n",
       " ('France', 'Livvie', 'Flory', 54),\n",
       " ('Malaysia', 'Nonie', 'Borit', 48),\n",
       " ('Indonesia', 'Clio', 'Mugg', 47),\n",
       " ('Brazil', 'Westley', 'Measor', 48),\n",
       " ('Philippines', 'Katrinka', 'Sibbert', 51),\n",
       " ('Poland', 'Valentia', 'Mounch', 50),\n",
       " ('Norway', 'Sheilah', 'Hedditch', 53),\n",
       " ('Papua New Guinea', 'Itch', 'Jubb', 50),\n",
       " ('Latvia', 'Stesha', 'Garnson', 53),\n",
       " ('Canada', 'Cristionna', 'Wadmore', 46),\n",
       " ('China', 'Lianna', 'Gatward', 43),\n",
       " ('Guatemala', 'Tanney', 'Vials', 48),\n",
       " ('France', 'Alma', 'Zavittieri', 44),\n",
       " ('China', 'Alvira', 'Tamas', 50),\n",
       " ('United States', 'Shanon', 'Peres', 45),\n",
       " ('Sweden', 'Maisey', 'Lynas', 53),\n",
       " ('Indonesia', 'Kip', 'Hothersall', 46),\n",
       " ('China', 'Cash', 'Landis', 48),\n",
       " ('Panama', 'Kennith', 'Digance', 45),\n",
       " ('China', 'Ulberto', 'Riggeard', 48),\n",
       " ('Switzerland', 'Judy', 'Gilligan', 49),\n",
       " ('Philippines', 'Tod', 'Trevaskus', 52),\n",
       " ('Brazil', 'Herold', 'Heggs', 44),\n",
       " ('Latvia', 'Verney', 'Note', 50),\n",
       " ('Poland', 'Temp', 'Ribey', 50),\n",
       " ('China', 'Conroy', 'Egdal', 48),\n",
       " ('Japan', 'Gabie', 'Alessandone', 47),\n",
       " ('Ukraine', 'Devlen', 'Chaperlin', 54),\n",
       " ('France', 'Babbette', 'Turner', 51),\n",
       " ('Czech Republic', 'Virgil', 'Scotney', 52),\n",
       " ('Tajikistan', 'Zorina', 'Bedow', 49),\n",
       " ('China', 'Aidan', 'Rudeyeard', 50),\n",
       " ('Ireland', 'Saunder', 'MacLice', 48),\n",
       " ('France', 'Waly', 'Brunstan', 53),\n",
       " ('China', 'Gisele', 'Enns', 52),\n",
       " ('Peru', 'Mina', 'Winchester', 48),\n",
       " ('Japan', 'Torie', 'MacShirrie', 50),\n",
       " ('Russia', 'Benjamen', 'Kenford', 51),\n",
       " ('China', 'Etan', 'Burn', 53),\n",
       " ('Russia', 'Merralee', 'Chaperlin', 38),\n",
       " ('Indonesia', 'Lanny', 'Malam', 49),\n",
       " ('Canada', 'Wilhelm', 'Deeprose', 54),\n",
       " ('Czech Republic', 'Lari', 'Hillhouse', 48),\n",
       " ('China', 'Ossie', 'Woodley', 52),\n",
       " ('Macedonia', 'April', 'Tyer', 50),\n",
       " ('Vietnam', 'Madelon', 'Dansey', 53),\n",
       " ('Ukraine', 'Korella', 'McNamee', 52),\n",
       " ('Jamaica', 'Linnea', 'Cannam', 43),\n",
       " ('China', 'Mart', 'Coling', 52),\n",
       " ('Indonesia', 'Marna', 'Causbey', 47),\n",
       " ('China', 'Berni', 'Daintier', 55),\n",
       " ('Poland', 'Cynthia', 'Hassell', 49),\n",
       " ('Canada', 'Carma', 'Schule', 49),\n",
       " ('Indonesia', 'Malia', 'Blight', 48),\n",
       " ('China', 'Paulo', 'Seivertsen', 47),\n",
       " ('Niger', 'Kaylee', 'Hearley', 54),\n",
       " ('Japan', 'Maure', 'Jandak', 46),\n",
       " ('Argentina', 'Foss', 'Feavers', 45),\n",
       " ('Venezuela', 'Ron', 'Leggitt', 60),\n",
       " ('Russia', 'Flint', 'Gokes', 40),\n",
       " ('China', 'Linet', 'Conelly', 52),\n",
       " ('Philippines', 'Nikolas', 'Birtwell', 57),\n",
       " ('Australia', 'Eduard', 'Leipelt', 53)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- Feel free to experiment with the data set provided in this notebook for practice:\n",
    "SELECT country, first_name, last_name, test_score FROM INTERNATIONAL_STUDENT_TEST_SCORES;    "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}