DB0201EN-Week3-1-3-SQLmagic_SQlite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\" target=\"_blank\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  />\n",
    "    </a>\n",
    "</p>\n",
    "\n",
    "# Accessing Databases with SQL Magic\n",
    "\n",
    "Estimated time needed: **15** minutes\n",
    "\n",
    "## Objectives\n",
    "\n",
    "After completing this lab you will be able to:\n",
    "\n",
    "*   Perform simplified database access using SQL \"magic\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### To communicate with SQL Databases from within a JupyterLab notebook, we can use the SQL \"magic\" provided by the [ipython-sql](https://github.com/catherinedevlin/ipython-sql) extension. \"Magic\" is JupyterLab's term for special commands that start with \"%\". Below, we'll use the *load*\\_*ext* magic to load the ipython-sql extension. In the lab environemnt provided in the course the ipython-sql extension is already installed and so is the ibm_db_sa driver.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Here  you will  be creating and connecting to a new SQlite database SQLiteMagic.\n",
    "\n",
    "##### The syntax for connecting to magic sql using sqllite is\n",
    "\n",
    "**%sql sqlite://DatabaseName**\n",
    "\n",
    "where DatabaseName will be your **.db** file\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"SQLiteMagic.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @SQLiteMagic.db'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///SQLiteMagic.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### For convenience, we can use %%sql (two %'s instead of one) at the top of a cell to indicate we want the entire cell to be treated as SQL. Let's use this to create a table and fill it with some test data for experimenting.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///SQLiteMagic.db\n",
      "Done.\n",
      "99 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (\n",
    "\tcountry VARCHAR(50),\n",
    "\tfirst_name VARCHAR(50),\n",
    "\tlast_name VARCHAR(50),\n",
    "\ttest_score INT\n",
    ");\n",
    "INSERT INTO INTERNATIONAL_STUDENT_TEST_SCORES (country, first_name, last_name, test_score)\n",
    "VALUES\n",
    "('United States', 'Marshall', 'Bernadot', 54),\n",
    "('Ghana', 'Celinda', 'Malkin', 51),\n",
    "('Ukraine', 'Guillermo', 'Furze', 53),\n",
    "('Greece', 'Aharon', 'Tunnow', 48),\n",
    "('Russia', 'Bail', 'Goodwin', 46),\n",
    "('Poland', 'Cole', 'Winteringham', 49),\n",
    "('Sweden', 'Emlyn', 'Erricker', 55),\n",
    "('Russia', 'Cathee', 'Sivewright', 49),\n",
    "('China', 'Barny', 'Ingerson', 57),\n",
    "('Uganda', 'Sharla', 'Papaccio', 55),\n",
    "('China', 'Stella', 'Youens', 51),\n",
    "('Poland', 'Julio', 'Buesden', 48),\n",
    "('United States', 'Tiffie', 'Cosely', 58),\n",
    "('Poland', 'Auroora', 'Stiffell', 45),\n",
    "('China', 'Clarita', 'Huet', 52),\n",
    "('Poland', 'Shannon', 'Goulden', 45),\n",
    "('Philippines', 'Emylee', 'Privost', 50),\n",
    "('France', 'Madelina', 'Burk', 49),\n",
    "('China', 'Saunderson', 'Root', 58),\n",
    "('Indonesia', 'Bo', 'Waring', 55),\n",
    "('China', 'Hollis', 'Domotor', 45),\n",
    "('Russia', 'Robbie', 'Collip', 46),\n",
    "('Philippines', 'Davon', 'Donisi', 46),\n",
    "('China', 'Cristabel', 'Radeliffe', 48),\n",
    "('China', 'Wallis', 'Bartleet', 58),\n",
    "('Moldova', 'Arleen', 'Stailey', 38),\n",
    "('Ireland', 'Mendel', 'Grumble', 58),\n",
    "('China', 'Sallyann', 'Exley', 51),\n",
    "('Mexico', 'Kain', 'Swaite', 46),\n",
    "('Indonesia', 'Alonso', 'Bulteel', 45),\n",
    "('Armenia', 'Anatol', 'Tankus', 51),\n",
    "('Indonesia', 'Coralyn', 'Dawkins', 48),\n",
    "('China', 'Deanne', 'Edwinson', 45),\n",
    "('China', 'Georgiana', 'Epple', 51),\n",
    "('Portugal', 'Bartlet', 'Breese', 56),\n",
    "('Azerbaijan', 'Idalina', 'Lukash', 50),\n",
    "('France', 'Livvie', 'Flory', 54),\n",
    "('Malaysia', 'Nonie', 'Borit', 48),\n",
    "('Indonesia', 'Clio', 'Mugg', 47),\n",
    "('Brazil', 'Westley', 'Measor', 48),\n",
    "('Philippines', 'Katrinka', 'Sibbert', 51),\n",
    "('Poland', 'Valentia', 'Mounch', 50),\n",
    "('Norway', 'Sheilah', 'Hedditch', 53),\n",
    "('Papua New Guinea', 'Itch', 'Jubb', 50),\n",
    "('Latvia', 'Stesha', 'Garnson', 53),\n",
    "('Canada', 'Cristionna', 'Wadmore', 46),\n",
    "('China', 'Lianna', 'Gatward', 43),\n",
    "('Guatemala', 'Tanney', 'Vials', 48),\n",
    "('France', 'Alma', 'Zavittieri', 44),\n",
    "('China', 'Alvira', 'Tamas', 50),\n",
    "('United States', 'Shanon', 'Peres', 45),\n",
    "('Sweden', 'Maisey', 'Lynas', 53),\n",
    "('Indonesia', 'Kip', 'Hothersall', 46),\n",
    "('China', 'Cash', 'Landis', 48),\n",
    "('Panama', 'Kennith', 'Digance', 45),\n",
    "('China', 'Ulberto', 'Riggeard', 48),\n",
    "('Switzerland', 'Judy', 'Gilligan', 49),\n",
    "('Philippines', 'Tod', 'Trevaskus', 52),\n",
    "('Brazil', 'Herold', 'Heggs', 44),\n",
    "('Latvia', 'Verney', 'Note', 50),\n",
    "('Poland', 'Temp', 'Ribey', 50),\n",
    "('China', 'Conroy', 'Egdal', 48),\n",
    "('Japan', 'Gabie', 'Alessandone', 47),\n",
    "('Ukraine', 'Devlen', 'Chaperlin', 54),\n",
    "('France', 'Babbette', 'Turner', 51),\n",
    "('Czech Republic', 'Virgil', 'Scotney', 52),\n",
    "('Tajikistan', 'Zorina', 'Bedow', 49),\n",
    "('China', 'Aidan', 'Rudeyeard', 50),\n",
    "('Ireland', 'Saunder', 'MacLice', 48),\n",
    "('France', 'Waly', 'Brunstan', 53),\n",
    "('China', 'Gisele', 'Enns', 52),\n",
    "('Peru', 'Mina', 'Winchester', 48),\n",
    "('Japan', 'Torie', 'MacShirrie', 50),\n",
    "('Russia', 'Benjamen', 'Kenford', 51),\n",
    "('China', 'Etan', 'Burn', 53),\n",
    "('Russia', 'Merralee', 'Chaperlin', 38),\n",
    "('Indonesia', 'Lanny', 'Malam', 49),\n",
    "('Canada', 'Wilhelm', 'Deeprose', 54),\n",
    "('Czech Republic', 'Lari', 'Hillhouse', 48),\n",
    "('China', 'Ossie', 'Woodley', 52),\n",
    "('Macedonia', 'April', 'Tyer', 50),\n",
    "('Vietnam', 'Madelon', 'Dansey', 53),\n",
    "('Ukraine', 'Korella', 'McNamee', 52),\n",
    "('Jamaica', 'Linnea', 'Cannam', 43),\n",
    "('China', 'Mart', 'Coling', 52),\n",
    "('Indonesia', 'Marna', 'Causbey', 47),\n",
    "('China', 'Berni', 'Daintier', 55),\n",
    "('Poland', 'Cynthia', 'Hassell', 49),\n",
    "('Canada', 'Carma', 'Schule', 49),\n",
    "('Indonesia', 'Malia', 'Blight', 48),\n",
    "('China', 'Paulo', 'Seivertsen', 47),\n",
    "('Niger', 'Kaylee', 'Hearley', 54),\n",
    "('Japan', 'Maure', 'Jandak', 46),\n",
    "('Argentina', 'Foss', 'Feavers', 45),\n",
    "('Venezuela', 'Ron', 'Leggitt', 60),\n",
    "('Russia', 'Flint', 'Gokes', 40),\n",
    "('China', 'Linet', 'Conelly', 52),\n",
    "('Philippines', 'Nikolas', 'Birtwell', 57),\n",
    "('Australia', 'Eduard', 'Leipelt', 53)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using Python Variables in your SQL Statements\n",
    "\n",
    "##### You can use python variables in your SQL statements by adding a \":\" prefix to your python variable names.\n",
    "\n",
    "##### For example, if I have a python variable `country` with a value of `\"Canada\"`, I can use this variable in a SQL query to find all the rows of students from Canada.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///SQLiteMagic.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>country</th>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>test_score</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "            <td>Cristionna</td>\n",
       "            <td>Wadmore</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "            <td>Wilhelm</td>\n",
       "            <td>Deeprose</td>\n",
       "            <td>54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "            <td>Carma</td>\n",
       "            <td>Schule</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Canada', 'Cristionna', 'Wadmore', 46),\n",
       " ('Canada', 'Wilhelm', 'Deeprose', 54),\n",
       " ('Canada', 'Carma', 'Schule', 49)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country = \"Canada\"\n",
    "%sql select * from INTERNATIONAL_STUDENT_TEST_SCORES where country = :country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Assigning the Results of Queries to Python Variables\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### You can use the normal python assignment syntax to assign the results of your queries to python variables.\n",
    "\n",
    "##### For example, I have a SQL query to retrieve the distribution of test scores (i.e. how many students got each score). I can assign the result of this query to the variable `test_score_distribution` using the `=` operator.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///SQLiteMagic.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Test_Score</th>\n",
       "            <th>Frequency</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>38</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>40</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>43</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>44</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>45</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>46</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>47</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>48</td>\n",
       "            <td>14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>49</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>50</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>51</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>52</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>53</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>54</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>55</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>56</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>57</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>58</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>60</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(38, 2),\n",
       " (40, 1),\n",
       " (43, 2),\n",
       " (44, 2),\n",
       " (45, 8),\n",
       " (46, 7),\n",
       " (47, 4),\n",
       " (48, 14),\n",
       " (49, 8),\n",
       " (50, 10),\n",
       " (51, 8),\n",
       " (52, 8),\n",
       " (53, 8),\n",
       " (54, 5),\n",
       " (55, 4),\n",
       " (56, 1),\n",
       " (57, 2),\n",
       " (58, 4),\n",
       " (60, 1)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_score_distribution = %sql SELECT test_score as \"Test_Score\", count(*) as \"Frequency\" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;\n",
    "test_score_distribution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Converting Query Results to DataFrames\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### You can easily convert a SQL query result to a pandas dataframe using the `DataFrame()` method. Dataframe objects are much more versatile than SQL query result objects. For example, we can easily graph our test score distribution after converting to a dataframe.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEHCAYAAACp9y31AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/NK7nSAAAACXBIWXMAAAsTAAALEwEAmpwYAAAW2klEQVR4nO3de7QlZXnn8e+PiyiKQcNBiNA2ZrSNOl5bR2OMKGJQCGoUo6MuAmonJqIYGYVoFONijRcSvGSidpSL4rVBiOKotEYgGQUEbW6Cl2iLgArqMHgnLc/8UdV4OJxL7X32pbvr+1lrr1NVu+p5n73320/Xrqr9VqoKSVJ/bDftBCRJk2Xhl6SesfBLUs9Y+CWpZyz8ktQzO0w7gS522223Wrly5bTTkKStysUXX/zDqpqZu3yrKPwrV67koosumnYakrRVSfKd+ZZ7qEeSesbCL0k9Y+GXpJ6x8EtSz1j4JalnLPyS1DNjK/xJTkxyfZLL53nuqCSVZLdxtS9Jmt849/hPBg6YuzDJ3sD+wNVjbFuStICxFf6qOg/48TxPnQC8EvBGAJI0BRP95W6Sg4Frq+qSJEutuwZYA7BixYoJZKctyZPPPGKo7T71tHeMOBNp2zOxk7tJdgZeDby2y/pVtbaqVlfV6pmZ2w01IUka0iSv6vldYB/gkiQbgb2ALyfZY4I5SFLvTexQT1VdBuy+eb4t/qur6oeTykGSNN7LOT8EfBFYleSaJC8YV1uSpO7GtsdfVc9Z4vmV42pbkrQwf7krST1j4ZeknrHwS1LPWPglqWcs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST1j4ZeknrHwS1LPWPglqWcs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST1j4ZeknrHwS1LPWPglqWfGVviTnJjk+iSXz1r2liRXJbk0yRlJdh1X+5Kk+Y1zj/9k4IA5y9YDD6yqBwFfB44ZY/uSpHmMrfBX1XnAj+csO7uqNrWz5wN7jat9SdL8pnmM/3DgUws9mWRNkouSXHTDDTdMMC1J2rZNpfAneTWwCfjAQutU1dqqWl1Vq2dmZiaXnCRt43aYdINJDgUOAvarqpp0+5LUdxMt/EkOAF4FPK6qfj7JtiVJjXFezvkh4IvAqiTXJHkB8I/ALsD6JBuSvGtc7UuS5je2Pf6qes48i987rvYkSd34y11J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SeGVvhT3JikuuTXD5r2d2TrE/yjfbv3cbVviRpfuPc4z8ZOGDOsqOBz1XVfYDPtfOSpAkaW+GvqvOAH89Z/FTglHb6FOBp42pfkjS/HSbc3j2q6nsAVfW9JLsvtGKSNcAagBUrVkwoPen2Djr9xKG2O+sZh484E2k0ttiTu1W1tqpWV9XqmZmZaacjSduMSRf+HyTZE6D9e/2E25ek3pt04f84cGg7fSjwLxNuX5J6b5yXc34I+CKwKsk1SV4AvBHYP8k3gP3beUnSBI3t5G5VPWeBp/YbV5uSpKVtsSd3JUnjYeGXpJ6x8EtSz1j4JalnLPyS1DMWfknqGQu/JPWMhV+SesbCL0k9Y+GXpJ7pVPiTPHDciUiSJqPrHv+7klyY5C+T7DrOhCRJ49Wp8FfVHwDPBfYGLkrywST7jzUzSdJYdD7GX1XfAF4DvAp4HPD2JFcl+ZNxJSdJGr2ux/gflOQE4ErgCcAfV9XvtdMnjDE/SdKIdR2P/x+Bfwb+pqp+sXlhVV2X5DVjyUySNBZdC/9TgF9U1a8BkmwH3LGqfl5V7x9bdpKkket6jP+zwJ1mze/cLpMkbWW6Fv47VtVPN8+00zuPJyVJ0jh1Lfw/S/KwzTNJHg78YpH1JUlbqK7H+I8E1iW5rp3fE/jTYRtN8nLghUABlwGHVdUvh40nSequU+Gvqi8luR+wCghwVVX95zANJrkn8FLg/lX1iyQfBZ4NnDxMPEnSYLru8QM8AljZbvPQJFTV+5bR7p2S/CfNuYLrllhfkjQinQp/kvcDvwtsAH7dLi5g4MJfVdcmOR64muY8wdlVdfagcSRJw+m6x7+a5tBMLbfBJHcDngrsA9xIc+7geVV16pz11gBrAFasWLHcZrdpHz7pj4ba7tmHfWbEmWx5DvzY3w+13Sf/5BUjzgQOWnfaUNuddcgzbzP/tNOGu5L6zGc+8dbpQ06/dKgY657xoKG205al61U9lwN7jKjNJwLfrqob2vMEHwN+f+5KVbW2qlZX1eqZmZkRNS1J6rrHvxvw1SQXAr/avLCqDh6izauBRyXZmeZQz37ARUPEkSQNoWvhP3ZUDVbVBUlOA74MbAK+AqwdVXxJ0uK6Xs55bpJ7Afepqs+2e+vbD9toVb0OeN2w20uShtd1WOYXAacB724X3RM4c0w5SZLGqOvJ3b8CHgPcBLfelGX3cSUlSRqfroX/V1V18+aZJDvQXMcvSdrKdC385yb5G5pf2+4PrAM+Mb60JEnj0rXwHw3cQDOg2p8D/5vm/ruSpK1M16t6bqG59eI/jzcdSdK4dR2r59vMc0y/qu498owkSWM1yFg9m90ROAS4++jTkSSNW6dj/FX1o1mPa6vqrcATxpuaJGkcuh7qedis2e1ovgHsMpaMNDXv+MBwo3we8dxtf5RPaVvS9VDP7LFtNwEbgWeNPBtJ0th1varn8eNORJI0GV0P9fz1Ys9X1T+MJh1J0rgNclXPI4CPt/N/DJwHfHccSUmSxmeQG7E8rKp+ApDkWGBdVb1wXIlJksaj65ANK4CbZ83fDKwceTaSpLHrusf/fuDCJGfQ/IL36cD7xpaVJGlsul7Vc1ySTwGPbRcdVlVfGV9akqRx6XqoB2Bn4KaqehtwTZJ9xpSTJGmMut568XXAq4Bj2kU7AqeOKylJ0vh03eN/OnAw8DOAqroOh2yQpK1S18J/c1UV7dDMSe68nEaT7JrktCRXJbkyyaOXE0+S1F3Xwv/RJO8Gdk3yIuCzLO+mLG8DPl1V9wMeDFy5jFiSpAEseVVPkgAfAe4H3ASsAl5bVeuHaTDJXYE/BP4MoL2J+82LbSNJGp0lC39VVZIzq+rhwFDFfo5709y/96QkDwYuBl5WVT+bvVKSNcAagBUrVoygWUkSdD/Uc36SR4yozR2AhwHvrKqH0pwwPnruSlW1tqpWV9XqmZmZETUtSepa+B9PU/z/I8mlSS5LcumQbV4DXFNVF7Tzp9H8RyBJmoBFD/UkWVFVVwNPHlWDVfX9JN9NsqqqvgbsB3x1VPElSYtb6hj/mTSjcn4nyelV9YwRtXsE8IEkdwC+BRw2oriSpCUsVfgza/reo2q0qjbQjPEvSZqwpY7x1wLTkqSt1FJ7/A9OchPNnv+d2mna+aqqu441O0nSyC1a+Ktq+0klIkmajEGGZZYkbQMs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST2z5LDMkrSQ48743lDbvfrpe444Ew3CPX5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6hkLvyT1zNQKf5Ltk3wlyVnTykGS+miae/wvA66cYvuS1EtTKfxJ9gIOBN4zjfYlqc+mtcf/VuCVwC1Tal+SemviwzInOQi4vqouTrLvIuutAdYArFixYjLJadmOOu2Aobc9/pmfHmEm2lqcue6HQ2/7tEN2G2Emo/ODEzYMtd09Xv6QkeaxkGns8T8GODjJRuDDwBOSnDp3papaW1Wrq2r1zMzMpHOUpG3WxAt/VR1TVXtV1Urg2cC/VtXzJp2HJPWV1/FLUs9M9daLVXUOcM40c5CkvnGPX5J6xsIvST1j4ZeknrHwS1LPWPglqWcs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST1j4ZeknrHwS1LPWPglqWcs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST1j4ZeknrHwS1LPWPglqWcmXviT7J3k80muTHJFkpdNOgdJ6rMdptDmJuAVVfXlJLsAFydZX1VfnUIuktQ7E9/jr6rvVdWX2+mfAFcC95x0HpLUV9PY479VkpXAQ4EL5nluDbAGYMWKFQDc8M5Th2pn5sXPGzbFBV3xTwcPtd0D/vLjt5n/3HsOHCrOfi/85FDbSVrcNcd/f6jt9jpqjxFnMj5TO7mb5C7A6cCRVXXT3Oeram1Vra6q1TMzM5NPUJK2UVMp/El2pCn6H6iqj00jB0nqq2lc1RPgvcCVVfUPk25fkvpuGnv8jwGeDzwhyYb28ZQp5CFJvTTxk7tV9e9AJt2uJKnhL3clqWcs/JLUMxZ+SeoZC78k9YyFX5J6xsIvST1j4ZeknrHwS1LPWPglqWemOizzNHz/na8fars9Xvy6EWciaZQuPOn6obZ75GG7jziT0bn+HeuH2m73I/Zf9Hn3+CWpZyz8ktQzFn5J6hkLvyT1jIVfknrGwi9JPWPhl6SesfBLUs9Y+CWpZyz8ktQzFn5J6pmpFP4kByT5WpJvJjl6GjlIUl9NvPAn2R74X8CTgfsDz0ly/0nnIUl9NY09/kcC36yqb1XVzcCHgadOIQ9J6qVU1WQbTJ4JHFBVL2znnw/8t6p6yZz11gBr2tlVwNeWCL0b8MNlpjeKGFtaHHMZb5wtKZdRxTGX8caZZC73qqqZuQunMR5/5ll2u/99qmotsLZz0OSiqlq9rMRGEGNLi2Mu442zJeUyqjjmMt44W0Iu0zjUcw2w96z5vYDrppCHJPXSNAr/l4D7JNknyR2AZwMfn0IektRLEz/UU1WbkrwE+AywPXBiVV0xgtCdDwuNOcaWFsdcxhtnS8plVHHMZbxxpp7LxE/uSpKmy1/uSlLPWPglqWe2usKf5I5JLkxySZIrkry+Xf6QJOcn2ZDkoiSP7BBr+yRfSXJWO3/3JOuTfKP9e7eOOc2N84Ykl7a5nJ3kd4aJM2v5UUkqyW5D5HJskmvbXDYkecqwuSQ5oh1q44okbx4mTpKPzMplY5INQ8QY+LNeIM6Dk3wxyWVJPpHkrh1ibGzX35DkonbZQP1mgRiHtO/rLUk6XaK3QJy3JLmq7X9nJNl1yDgD9eH5Ysx6bpD+O18uA/fhhfIZpA8vkMsw/Xe+OAP14SS7Jjmt/WyvTPLoQfvdbVTVVvWg+R3AXdrpHYELgEcBZwNPbpc/BTinQ6y/Bj4InNXOvxk4up0+GnhTx5zmxrnrrOdeCrxrmDjtsr1pToR/B9htiFyOBY4a4n2eG+fxwGeBndr53Yd9TbOe+3vgtUPkMvBnvUCcLwGPa6cPB97QIcbGuZ/DoP1mgRi/R/NDxXOA1R1fz3xxngTs0E6/qUsfXiDOQH14vhhD9t/5chm4Dy8QZ6A+vNBrGqL/zpfLQH0YOAV4YTt9B2DXQfvd7MdWt8dfjZ+2szu2j2ofm/fYfoslfhuQZC/gQOA9sxY/leYNpv37tKXymS9OVd00a5U7M88P1DrmA3AC8MplxhjIAnFeDLyxqn4FUFXXLyefJAGeBXxoiBgDfdaLxFkFnNdOrweesVScBQzcb+aqqiuraqlfp3eJc3ZVbWpnz6f5ncwwcQbuwwvo3H8nYOA+vJCu/XcRnftw+030D4H3AlTVzVV1I8vod1td4Ydbv7JvAK4H1lfVBcCRwFuSfBc4HjhmiTBvpemQt8xado+q+h5A+3f3DunMF4ckx7W5PBd47TBxkhwMXFtVl3TYfsFcgJe0X9tP7Ph1cL449wUem+SCJOcmecQy8gF4LPCDqvrGEDGOZLDPeqE4lwMHt9OHcNsfFi6kgLOTXJxmWBEYvN/MF2MYS8U5HPjUsHEG7MO3izFE/10wFwbvw/PFGbQPL/b+du2/C8U5ku59+N7ADcBJaQ5VvifJnRmuXrUZDfD1aUt70Hzd+TzwQODtwDPa5c8CPrvIdgcB/9RO78tvvvrfOGe9/7tE+/PGmbPOMcDrB40D7ExzGOu3qtvXzoVe0z1ofi+xHXAcze8mBn5NNEXy7TSH2h4JfJv2cuBh3hvgncArhsyl82e9RJz70Xzlvhh4HfCjDn3ud9q/uwOX0OyJDdpvbhdj1nPn0P1Qz2JxXg2csdhn1CXOAH14vvelc/9dIs5AfXiROIP24cXe3yX77xK5DFKvVgObaMY0A3gb8IZB+91t1u264pb6aP/BHgX8v80fYvvB3rTINv+TZuiIjcD3gZ8Dp9IMBLdnu86ewNeWaHveOHPWuRdw+RBxTqf5RrOxfWwCrgb2WEYuK4fM5VTg08C+s9b7D2BmmPeG5oeDPwD2GjKXzp/1AO/NfYELB+x7x7Z9b6B+M1+MWfPn0LHwLxQHOBT4IrDzcuIM0ofnifG3g/TfAXJZsg8v8jkN1IcXeX879d8lchmkXu0BbJw1/1jgk8vqd4MmPu0HMAPs2k7fCfg3mj26Kzd/qMB+wMUd4+3Lb/YA38JtT5a8eYC8Zse5z6zlRwCnDRNnzvKNdNhjmieXPWctfznw4SFf018Af9dO3xf4Lh32Jud7TcABwLkDfu6zcxnqs54nzu7t3+2A9wGHL7HtnYFdZk1/oX0tnfvNQjFmPX8OHQr/IrkcAHyV7gVtoTid+/BSr6lr/10kl4H68CJxOvfhxV7TIP13kVwG6sM0dW5VO31s2+eGrlfTGJ1zufYETklzQ5ftgI9W1VlJbgTelmQH4Jf8ZkjnQbwR+GiSF9DsnRwyZI5vTLKK5njyd2g63LS8OclDaI4zbgT+fMg4JwInJrkcuBk4tNoeN4RnM/xJMYAXsfzPGpqbAP1VO/0x4KQl1r8HcEZzXo8dgA9W1aeTfInu/WahGE8H3kGzY/PJJBuq6o+GiPNNYCdgffvc+VW1WP9bKM7pA/TheWMssv6gubx/wD68UJw70L0PL/aaBum/C+XyUwbrw0cAH2hfw7eAw2jr3zD1yiEbJKlntsqreiRJw7PwS1LPWPglqWcs/JLUMxZ+SeoZC78k9czWeB2/tKQkvw18rp3dA/g1zXgnAI+sqpuX2H5f4Oaq+sIi66wC3k0zdMhOwL9V1XLG3pEmwsKvbVJV/Qh4CDTjuQM/rarjBwixL/BTml9aLuTtwAlV9S9tO/91mFxnS7J9Vf16uXGkxXioR72R5OHtqIwXJ/lMkj3b5S9N8tV29McPJ1lJ80vVl7c3ynjsAiH3pBkDCICquqyNt32S49PcfOPSJEe0y/drR1e8rB1lcqd2+cYkr03y78AhSZ6U5gYxX06yLsldxveuqI/c41dfhGZIhKdW1Q1J/pRmpMfDacY52aeqfpVk16q6Mcm7WPpbwgnAvyb5As0onydVM076GmAf4KFVtSnNnZLuCJwM7FdVX0/yPprx4d/axvplVf1BmrtUfQx4YlX9LMmraG4g83ejfDPUb+7xqy92ohm+e32aezm8ht/cpORSmnFQnkczimQnVXUSzZ2z1tEcGjq/3Yt/Is0dqza16/2Y5qYv366qr7ebn0IzPO9mH2n/Pgq4P/B/2jwPpRkdUxoZ9/jVFwGuqKpHz/PcgTRF+GDgb5M8oGvQqrqO2w5g98C2rbmDYGWJUD+btd76qnpO1xykQbnHr774FTCT5NEASXZM8oAk2wF7V9Xnae7QtStwF+AnwC6LBUxyQJId2+k9gN8GrqU57PMX7ciLJLk7cBWwMsl/aTd/PnDuPGHPBx6zeb0kOye57/AvW7o9C7/64hbgmcCbklwCbAB+n+bOTqcmuQz4Cs1VOjcCnwCevsTJ3ScBl7fxPgP8j6r6Ps19fa8GLm2f++9V9UuaoXTXtW3dArxrbsCqugH4M+BDSS6l+Y/gfiN4/dKtHJZZknrGPX5J6hlP7kpLSPJqbn93o3VVddw08pGWy0M9ktQzHuqRpJ6x8EtSz1j4JalnLPyS1DP/H3czkePMMM2bAAAAAElFTkSuQmCC\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==0.9.0\n",
    "import seaborn\n",
    "\n",
    "plot = seaborn.barplot(x='Test_Score',y='Frequency', data=dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you know how to work with Db2 from within JupyterLab notebooks using SQL \"magic\"!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///SQLiteMagic.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>country</th>\n",
       "            <th>first_name</th>\n",
       "            <th>last_name</th>\n",
       "            <th>test_score</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>United States</td>\n",
       "            <td>Marshall</td>\n",
       "            <td>Bernadot</td>\n",
       "            <td>54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ghana</td>\n",
       "            <td>Celinda</td>\n",
       "            <td>Malkin</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ukraine</td>\n",
       "            <td>Guillermo</td>\n",
       "            <td>Furze</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Greece</td>\n",
       "            <td>Aharon</td>\n",
       "            <td>Tunnow</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Russia</td>\n",
       "            <td>Bail</td>\n",
       "            <td>Goodwin</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Poland</td>\n",
       "            <td>Cole</td>\n",
       "            <td>Winteringham</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sweden</td>\n",
       "            <td>Emlyn</td>\n",
       "            <td>Erricker</td>\n",
       "            <td>55</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Russia</td>\n",
       "            <td>Cathee</td>\n",
       "            <td>Sivewright</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Barny</td>\n",
       "            <td>Ingerson</td>\n",
       "            <td>57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Uganda</td>\n",
       "            <td>Sharla</td>\n",
       "            <td>Papaccio</td>\n",
       "            <td>55</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Stella</td>\n",
       "            <td>Youens</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Poland</td>\n",
       "            <td>Julio</td>\n",
       "            <td>Buesden</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>United States</td>\n",
       "            <td>Tiffie</td>\n",
       "            <td>Cosely</td>\n",
       "            <td>58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Poland</td>\n",
       "            <td>Auroora</td>\n",
       "            <td>Stiffell</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Clarita</td>\n",
       "            <td>Huet</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Poland</td>\n",
       "            <td>Shannon</td>\n",
       "            <td>Goulden</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Philippines</td>\n",
       "            <td>Emylee</td>\n",
       "            <td>Privost</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>France</td>\n",
       "            <td>Madelina</td>\n",
       "            <td>Burk</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Saunderson</td>\n",
       "            <td>Root</td>\n",
       "            <td>58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Bo</td>\n",
       "            <td>Waring</td>\n",
       "            <td>55</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Hollis</td>\n",
       "            <td>Domotor</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Russia</td>\n",
       "            <td>Robbie</td>\n",
       "            <td>Collip</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Philippines</td>\n",
       "            <td>Davon</td>\n",
       "            <td>Donisi</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Cristabel</td>\n",
       "            <td>Radeliffe</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Wallis</td>\n",
       "            <td>Bartleet</td>\n",
       "            <td>58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Moldova</td>\n",
       "            <td>Arleen</td>\n",
       "            <td>Stailey</td>\n",
       "            <td>38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ireland</td>\n",
       "            <td>Mendel</td>\n",
       "            <td>Grumble</td>\n",
       "            <td>58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Sallyann</td>\n",
       "            <td>Exley</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mexico</td>\n",
       "            <td>Kain</td>\n",
       "            <td>Swaite</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Alonso</td>\n",
       "            <td>Bulteel</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Armenia</td>\n",
       "            <td>Anatol</td>\n",
       "            <td>Tankus</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Coralyn</td>\n",
       "            <td>Dawkins</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Deanne</td>\n",
       "            <td>Edwinson</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Georgiana</td>\n",
       "            <td>Epple</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Portugal</td>\n",
       "            <td>Bartlet</td>\n",
       "            <td>Breese</td>\n",
       "            <td>56</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Azerbaijan</td>\n",
       "            <td>Idalina</td>\n",
       "            <td>Lukash</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>France</td>\n",
       "            <td>Livvie</td>\n",
       "            <td>Flory</td>\n",
       "            <td>54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Malaysia</td>\n",
       "            <td>Nonie</td>\n",
       "            <td>Borit</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Clio</td>\n",
       "            <td>Mugg</td>\n",
       "            <td>47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Brazil</td>\n",
       "            <td>Westley</td>\n",
       "            <td>Measor</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Philippines</td>\n",
       "            <td>Katrinka</td>\n",
       "            <td>Sibbert</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Poland</td>\n",
       "            <td>Valentia</td>\n",
       "            <td>Mounch</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Norway</td>\n",
       "            <td>Sheilah</td>\n",
       "            <td>Hedditch</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Papua New Guinea</td>\n",
       "            <td>Itch</td>\n",
       "            <td>Jubb</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latvia</td>\n",
       "            <td>Stesha</td>\n",
       "            <td>Garnson</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "            <td>Cristionna</td>\n",
       "            <td>Wadmore</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Lianna</td>\n",
       "            <td>Gatward</td>\n",
       "            <td>43</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Guatemala</td>\n",
       "            <td>Tanney</td>\n",
       "            <td>Vials</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>France</td>\n",
       "            <td>Alma</td>\n",
       "            <td>Zavittieri</td>\n",
       "            <td>44</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Alvira</td>\n",
       "            <td>Tamas</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>United States</td>\n",
       "            <td>Shanon</td>\n",
       "            <td>Peres</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sweden</td>\n",
       "            <td>Maisey</td>\n",
       "            <td>Lynas</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Kip</td>\n",
       "            <td>Hothersall</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Cash</td>\n",
       "            <td>Landis</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Panama</td>\n",
       "            <td>Kennith</td>\n",
       "            <td>Digance</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Ulberto</td>\n",
       "            <td>Riggeard</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Switzerland</td>\n",
       "            <td>Judy</td>\n",
       "            <td>Gilligan</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Philippines</td>\n",
       "            <td>Tod</td>\n",
       "            <td>Trevaskus</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Brazil</td>\n",
       "            <td>Herold</td>\n",
       "            <td>Heggs</td>\n",
       "            <td>44</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latvia</td>\n",
       "            <td>Verney</td>\n",
       "            <td>Note</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Poland</td>\n",
       "            <td>Temp</td>\n",
       "            <td>Ribey</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Conroy</td>\n",
       "            <td>Egdal</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Japan</td>\n",
       "            <td>Gabie</td>\n",
       "            <td>Alessandone</td>\n",
       "            <td>47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ukraine</td>\n",
       "            <td>Devlen</td>\n",
       "            <td>Chaperlin</td>\n",
       "            <td>54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>France</td>\n",
       "            <td>Babbette</td>\n",
       "            <td>Turner</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Czech Republic</td>\n",
       "            <td>Virgil</td>\n",
       "            <td>Scotney</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tajikistan</td>\n",
       "            <td>Zorina</td>\n",
       "            <td>Bedow</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Aidan</td>\n",
       "            <td>Rudeyeard</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ireland</td>\n",
       "            <td>Saunder</td>\n",
       "            <td>MacLice</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>France</td>\n",
       "            <td>Waly</td>\n",
       "            <td>Brunstan</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Gisele</td>\n",
       "            <td>Enns</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Peru</td>\n",
       "            <td>Mina</td>\n",
       "            <td>Winchester</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Japan</td>\n",
       "            <td>Torie</td>\n",
       "            <td>MacShirrie</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Russia</td>\n",
       "            <td>Benjamen</td>\n",
       "            <td>Kenford</td>\n",
       "            <td>51</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Etan</td>\n",
       "            <td>Burn</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Russia</td>\n",
       "            <td>Merralee</td>\n",
       "            <td>Chaperlin</td>\n",
       "            <td>38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Lanny</td>\n",
       "            <td>Malam</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "            <td>Wilhelm</td>\n",
       "            <td>Deeprose</td>\n",
       "            <td>54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Czech Republic</td>\n",
       "            <td>Lari</td>\n",
       "            <td>Hillhouse</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Ossie</td>\n",
       "            <td>Woodley</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Macedonia</td>\n",
       "            <td>April</td>\n",
       "            <td>Tyer</td>\n",
       "            <td>50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Vietnam</td>\n",
       "            <td>Madelon</td>\n",
       "            <td>Dansey</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ukraine</td>\n",
       "            <td>Korella</td>\n",
       "            <td>McNamee</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jamaica</td>\n",
       "            <td>Linnea</td>\n",
       "            <td>Cannam</td>\n",
       "            <td>43</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Mart</td>\n",
       "            <td>Coling</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Marna</td>\n",
       "            <td>Causbey</td>\n",
       "            <td>47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Berni</td>\n",
       "            <td>Daintier</td>\n",
       "            <td>55</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Poland</td>\n",
       "            <td>Cynthia</td>\n",
       "            <td>Hassell</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canada</td>\n",
       "            <td>Carma</td>\n",
       "            <td>Schule</td>\n",
       "            <td>49</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Indonesia</td>\n",
       "            <td>Malia</td>\n",
       "            <td>Blight</td>\n",
       "            <td>48</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Paulo</td>\n",
       "            <td>Seivertsen</td>\n",
       "            <td>47</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Niger</td>\n",
       "            <td>Kaylee</td>\n",
       "            <td>Hearley</td>\n",
       "            <td>54</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Japan</td>\n",
       "            <td>Maure</td>\n",
       "            <td>Jandak</td>\n",
       "            <td>46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Argentina</td>\n",
       "            <td>Foss</td>\n",
       "            <td>Feavers</td>\n",
       "            <td>45</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Venezuela</td>\n",
       "            <td>Ron</td>\n",
       "            <td>Leggitt</td>\n",
       "            <td>60</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Russia</td>\n",
       "            <td>Flint</td>\n",
       "            <td>Gokes</td>\n",
       "            <td>40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>China</td>\n",
       "            <td>Linet</td>\n",
       "            <td>Conelly</td>\n",
       "            <td>52</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Philippines</td>\n",
       "            <td>Nikolas</td>\n",
       "            <td>Birtwell</td>\n",
       "            <td>57</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Australia</td>\n",
       "            <td>Eduard</td>\n",
       "            <td>Leipelt</td>\n",
       "            <td>53</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('United States', 'Marshall', 'Bernadot', 54),\n",
       " ('Ghana', 'Celinda', 'Malkin', 51),\n",
       " ('Ukraine', 'Guillermo', 'Furze', 53),\n",
       " ('Greece', 'Aharon', 'Tunnow', 48),\n",
       " ('Russia', 'Bail', 'Goodwin', 46),\n",
       " ('Poland', 'Cole', 'Winteringham', 49),\n",
       " ('Sweden', 'Emlyn', 'Erricker', 55),\n",
       " ('Russia', 'Cathee', 'Sivewright', 49),\n",
       " ('China', 'Barny', 'Ingerson', 57),\n",
       " ('Uganda', 'Sharla', 'Papaccio', 55),\n",
       " ('China', 'Stella', 'Youens', 51),\n",
       " ('Poland', 'Julio', 'Buesden', 48),\n",
       " ('United States', 'Tiffie', 'Cosely', 58),\n",
       " ('Poland', 'Auroora', 'Stiffell', 45),\n",
       " ('China', 'Clarita', 'Huet', 52),\n",
       " ('Poland', 'Shannon', 'Goulden', 45),\n",
       " ('Philippines', 'Emylee', 'Privost', 50),\n",
       " ('France', 'Madelina', 'Burk', 49),\n",
       " ('China', 'Saunderson', 'Root', 58),\n",
       " ('Indonesia', 'Bo', 'Waring', 55),\n",
       " ('China', 'Hollis', 'Domotor', 45),\n",
       " ('Russia', 'Robbie', 'Collip', 46),\n",
       " ('Philippines', 'Davon', 'Donisi', 46),\n",
       " ('China', 'Cristabel', 'Radeliffe', 48),\n",
       " ('China', 'Wallis', 'Bartleet', 58),\n",
       " ('Moldova', 'Arleen', 'Stailey', 38),\n",
       " ('Ireland', 'Mendel', 'Grumble', 58),\n",
       " ('China', 'Sallyann', 'Exley', 51),\n",
       " ('Mexico', 'Kain', 'Swaite', 46),\n",
       " ('Indonesia', 'Alonso', 'Bulteel', 45),\n",
       " ('Armenia', 'Anatol', 'Tankus', 51),\n",
       " ('Indonesia', 'Coralyn', 'Dawkins', 48),\n",
       " ('China', 'Deanne', 'Edwinson', 45),\n",
       " ('China', 'Georgiana', 'Epple', 51),\n",
       " ('Portugal', 'Bartlet', 'Breese', 56),\n",
       " ('Azerbaijan', 'Idalina', 'Lukash', 50),\n",
       " ('France', 'Livvie', 'Flory', 54),\n",
       " ('Malaysia', 'Nonie', 'Borit', 48),\n",
       " ('Indonesia', 'Clio', 'Mugg', 47),\n",
       " ('Brazil', 'Westley', 'Measor', 48),\n",
       " ('Philippines', 'Katrinka', 'Sibbert', 51),\n",
       " ('Poland', 'Valentia', 'Mounch', 50),\n",
       " ('Norway', 'Sheilah', 'Hedditch', 53),\n",
       " ('Papua New Guinea', 'Itch', 'Jubb', 50),\n",
       " ('Latvia', 'Stesha', 'Garnson', 53),\n",
       " ('Canada', 'Cristionna', 'Wadmore', 46),\n",
       " ('China', 'Lianna', 'Gatward', 43),\n",
       " ('Guatemala', 'Tanney', 'Vials', 48),\n",
       " ('France', 'Alma', 'Zavittieri', 44),\n",
       " ('China', 'Alvira', 'Tamas', 50),\n",
       " ('United States', 'Shanon', 'Peres', 45),\n",
       " ('Sweden', 'Maisey', 'Lynas', 53),\n",
       " ('Indonesia', 'Kip', 'Hothersall', 46),\n",
       " ('China', 'Cash', 'Landis', 48),\n",
       " ('Panama', 'Kennith', 'Digance', 45),\n",
       " ('China', 'Ulberto', 'Riggeard', 48),\n",
       " ('Switzerland', 'Judy', 'Gilligan', 49),\n",
       " ('Philippines', 'Tod', 'Trevaskus', 52),\n",
       " ('Brazil', 'Herold', 'Heggs', 44),\n",
       " ('Latvia', 'Verney', 'Note', 50),\n",
       " ('Poland', 'Temp', 'Ribey', 50),\n",
       " ('China', 'Conroy', 'Egdal', 48),\n",
       " ('Japan', 'Gabie', 'Alessandone', 47),\n",
       " ('Ukraine', 'Devlen', 'Chaperlin', 54),\n",
       " ('France', 'Babbette', 'Turner', 51),\n",
       " ('Czech Republic', 'Virgil', 'Scotney', 52),\n",
       " ('Tajikistan', 'Zorina', 'Bedow', 49),\n",
       " ('China', 'Aidan', 'Rudeyeard', 50),\n",
       " ('Ireland', 'Saunder', 'MacLice', 48),\n",
       " ('France', 'Waly', 'Brunstan', 53),\n",
       " ('China', 'Gisele', 'Enns', 52),\n",
       " ('Peru', 'Mina', 'Winchester', 48),\n",
       " ('Japan', 'Torie', 'MacShirrie', 50),\n",
       " ('Russia', 'Benjamen', 'Kenford', 51),\n",
       " ('China', 'Etan', 'Burn', 53),\n",
       " ('Russia', 'Merralee', 'Chaperlin', 38),\n",
       " ('Indonesia', 'Lanny', 'Malam', 49),\n",
       " ('Canada', 'Wilhelm', 'Deeprose', 54),\n",
       " ('Czech Republic', 'Lari', 'Hillhouse', 48),\n",
       " ('China', 'Ossie', 'Woodley', 52),\n",
       " ('Macedonia', 'April', 'Tyer', 50),\n",
       " ('Vietnam', 'Madelon', 'Dansey', 53),\n",
       " ('Ukraine', 'Korella', 'McNamee', 52),\n",
       " ('Jamaica', 'Linnea', 'Cannam', 43),\n",
       " ('China', 'Mart', 'Coling', 52),\n",
       " ('Indonesia', 'Marna', 'Causbey', 47),\n",
       " ('China', 'Berni', 'Daintier', 55),\n",
       " ('Poland', 'Cynthia', 'Hassell', 49),\n",
       " ('Canada', 'Carma', 'Schule', 49),\n",
       " ('Indonesia', 'Malia', 'Blight', 48),\n",
       " ('China', 'Paulo', 'Seivertsen', 47),\n",
       " ('Niger', 'Kaylee', 'Hearley', 54),\n",
       " ('Japan', 'Maure', 'Jandak', 46),\n",
       " ('Argentina', 'Foss', 'Feavers', 45),\n",
       " ('Venezuela', 'Ron', 'Leggitt', 60),\n",
       " ('Russia', 'Flint', 'Gokes', 40),\n",
       " ('China', 'Linet', 'Conelly', 52),\n",
       " ('Philippines', 'Nikolas', 'Birtwell', 57),\n",
       " ('Australia', 'Eduard', 'Leipelt', 53)]"
      ]
     },
     "execution_count": 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;    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/ravahuja/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\" target=\"_blank\">Rav Ahuja</a>\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/lakshmi-holla-b39062149/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\" target=\"_blank\">Lakshmi Holla</a>\n",
    "\n",
    "## Other Contributor(s)\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/malika-goyal-04798622/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2022-01-01\" target=\"_blank\">Malika</a>\n",
    "\n",
    "## Change Log\n",
    "\n",
    "| Date (YYYY-MM-DD) | Version | Changed By    | Change Description        |\n",
    "| ----------------- | ------- | ------------- | ------------------------- |\n",
    "| 2022-03-04        | 1.0     | Lakshmi Holla | Created lab using sqlite3 |\n",
    "\n",
    "<hr>\n",
    "\n",
    "## <h3 align=\"center\"> © IBM Corporation 2022. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}