Computer Science/SQL/Lab3-TCHIBOZO.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TP3 - Views, Updates and Design\n",
    "\n",
    "The objectives for this TP are:\n",
    "\n",
    "1. Create and use SQL Views\n",
    "2. Update database content\n",
    "3. Design the database schema for a Social Network\n",
    "\n",
    "___\n",
    "\n",
    "For the first 2 parts we will use the **`wine.db`** database and the Tables created in TP2.\n",
    "\n",
    "A reminder of the wine database schema:\n",
    "\n",
    "<center>**Master1**</center>\n",
    "\n",
    "|*Attribute*|         *Description*          |\n",
    "| -------   |--------------------------------|\n",
    "| NV        | Wine number                    |\n",
    "| CRU       | Vineyard or group of vineyards |\n",
    "| DEGRE     | Alcohol content                |\n",
    "| MILL      | Vintage year                   |\n",
    "| QTE       | Number of bottles harvested    |\n",
    "| NP        | Producer number                |\n",
    "| NOM       | Producer's last name           |\n",
    "| PRENOM    | Producer's first name          |\n",
    "| REGION    | Production region              |\n",
    "\n",
    "<center>**Master2**</center>\n",
    "\n",
    "|*Attribute*|                         *Description*                  |\n",
    "| -------   |--------------------------------------------------------|\n",
    "| NV        | Wine number                                            |\n",
    "| CRU       | Vineyard or group of vineyards                         |\n",
    "| DEGRE     | Alcohol content                                        |\n",
    "| MILL      | Vintage year                                           |\n",
    "| DATES     | Buying date                                            |\n",
    "| LIEU      | Place where the wine was sold                          |\n",
    "| QTE       | Number of bottles bought                               |\n",
    "| NB        | Client (buveur) number                                 |\n",
    "| NOM       | Client's last name                                     |\n",
    "| PRENOM    | Client's first name                                    |\n",
    "| TYPE      | Type of client by volume of purchases                  |\n",
    "| REGION    | Administrative Region (different to production region) |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def printSchema(connection):\n",
    "    ### Source: http://stackoverflow.com/a/35092773/4765776\n",
    "    for (tableName,) in connection.execute(\n",
    "        \"\"\"\n",
    "        select NAME from SQLITE_MASTER where TYPE='table' order by NAME;\n",
    "        \"\"\"\n",
    "    ):\n",
    "        print(\"{}:\".format(tableName))\n",
    "        for (\n",
    "            columnID, columnName, columnType,\n",
    "            columnNotNull, columnDefault, columnPK,\n",
    "        ) in connection.execute(\"pragma table_info('{}');\".format(tableName)):\n",
    "            print(\"  {id}: {name}({type}){null}{default}{pk}\".format(\n",
    "                id=columnID,\n",
    "                name=columnName,\n",
    "                type=columnType,\n",
    "                null=\" not null\" if columnNotNull else \"\",\n",
    "                default=\" [{}]\".format(columnDefault) if columnDefault else \"\",\n",
    "                pk=\" *{}\".format(columnPK) if columnPK else \"\",\n",
    "            ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database schema:\n",
      "MASTER1:\n",
      "  0: NV(NUM)\n",
      "  1: CRU(TEXT)\n",
      "  2: DEGRE(NUM)\n",
      "  3: MILL(NUM)\n",
      "  4: QTE(NUM)\n",
      "  5: NP(NUM)\n",
      "  6: NOM(TEXT)\n",
      "  7: PRENOM(TEXT)\n",
      "  8: REGION(TEXT)\n",
      "MASTER1sub1:\n",
      "  0: NP(NUM)\n",
      "  1: NOM(TEXT)\n",
      "  2: PRENOM(TEXT)\n",
      "MASTER1sub2:\n",
      "  0: NV(NUM)\n",
      "  1: CRU(TEXT)\n",
      "  2: DEGRE(NUM)\n",
      "  3: MILL(NUM)\n",
      "MASTER1sub3:\n",
      "  0: NP(NUM)\n",
      "  1: NV(NUM)\n",
      "  2: QTE(NUM)\n",
      "  3: REGION(TEXT)\n",
      "MASTER2:\n",
      "  0: NV(NUM)\n",
      "  1: CRU(TEXT)\n",
      "  2: DEGRE(NUM)\n",
      "  3: MILL(NUM)\n",
      "  4: DATES(DATE)\n",
      "  5: LIEU(TEXT)\n",
      "  6: QTE(NUM)\n",
      "  7: NB(NUM)\n",
      "  8: NOM(TEXT)\n",
      "  9: PRENOM(TEXT)\n",
      "  10: TYPE(TEXT)\n",
      "  11: REGION(TEXT)\n",
      "MASTER2sub1:\n",
      "  0: NB(NUM)\n",
      "  1: NOM(TEXT)\n",
      "  2: PRENOM(TEXT)\n",
      "  3: TYPE(TEXT)\n",
      "MASTER2sub2:\n",
      "  0: NV(NUM)\n",
      "  1: CRU(TEXT)\n",
      "  2: DEGRE(NUM)\n",
      "  3: MILL(NUM)\n",
      "MASTER2sub3:\n",
      "  0: LIEU(TEXT)\n",
      "  1: REGION(TEXT)\n",
      "MASTER2sub4:\n",
      "  0: NB(NUM)\n",
      "  1: NV(NUM)\n",
      "  2: LIEU(TEXT)\n",
      "  3: QTE(NUM)\n",
      "  4: DATES(NUM)\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect('wine.db')\n",
    "c = conn.cursor()\n",
    "print(\"Database schema:\")\n",
    "printSchema(conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, we will use **`%%sql`** magic for our queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @wine.db'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite:///wine.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recreate the Normalized Tables from **Master1** and **Master2** as you did in the TP2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "-- Create first subtable of master 1\n",
    "DROP TABLE IF EXISTS MASTER1sub1;\n",
    "CREATE TABLE MASTER1sub1 AS\n",
    "SELECT DISTINCT NP,NOM,PRENOM\n",
    "FROM MASTER1\n",
    "WHERE NP IS NOT NULL;\n",
    "\n",
    "-- Create second subtable of master 1\n",
    "DROP TABLE IF EXISTS MASTER1sub2;\n",
    "CREATE TABLE MASTER1sub2 AS\n",
    "SELECT DISTINCT NV,CRU,DEGRE,MILL\n",
    "FROM MASTER1\n",
    "WHERE NV IS NOT NULL;\n",
    "\n",
    "\n",
    "-- Create third subtable of master 1\n",
    "DROP TABLE IF EXISTS MASTER1sub3;\n",
    "CREATE TABLE MASTER1sub3 AS\n",
    "SELECT DISTINCT NP,NV,QTE,REGION\n",
    "FROM MASTER1\n",
    "WHERE NP IS NOT NULL AND NV IS NOT NULL;\n",
    "\n",
    "\n",
    "-- Create first subtable of master 2\n",
    "DROP TABLE IF EXISTS MASTER2sub1;\n",
    "CREATE TABLE MASTER2sub1 AS\n",
    "SELECT DISTINCT NB,NOM,PRENOM,TYPE\n",
    "FROM MASTER2\n",
    "WHERE NB IS NOT NULL;\n",
    "\n",
    "-- Create second subtable of master 2\n",
    "DROP TABLE IF EXISTS MASTER2sub2;\n",
    "CREATE TABLE MASTER2sub2 AS\n",
    "SELECT DISTINCT NV,CRU,DEGRE,MILL\n",
    "FROM MASTER2\n",
    "WHERE NV IS NOT NULL;\n",
    "\n",
    "\n",
    "-- Create third subtable of master 2\n",
    "DROP TABLE IF EXISTS MASTER2sub3;\n",
    "CREATE TABLE MASTER2sub3 AS\n",
    "SELECT DISTINCT LIEU,REGION\n",
    "FROM MASTER2\n",
    "WHERE LIEU IS NOT NULL;\n",
    "\n",
    "-- Create fourth subtable of master 2\n",
    "DROP TABLE IF EXISTS MASTER2sub4;\n",
    "CREATE TABLE MASTER2sub4 AS\n",
    "SELECT DISTINCT NB,NV,LIEU,QTE,DATES\n",
    "FROM MASTER2\n",
    "WHERE NB IS NOT NULL AND NV IS NOT NULL AND LIEU IS NOT NULL;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___\n",
    "# PART I: CREATE AND USE VIEWS\n",
    "\n",
    "A view is a virtual table based on the result-set of an SQL statement. Views are stored in the database with an associated name.\n",
    "\n",
    "Views are created following the syntax:\n",
    "\n",
    "```mysql\n",
    "CREATE VIEW view_name AS\n",
    "SELECT column1, column2.....\n",
    "FROM table_name\n",
    "WHERE [condition];\n",
    "```\n",
    "\n",
    "An useful command:\n",
    "\n",
    "```mysql\n",
    "DROP VIEW IF EXISTS view_name;\n",
    "```\n",
    "\n",
    "\n",
    "__Warning:__ Use `DROP` with caution (only drop something if you are sure)\n",
    "\n",
    "__Note:__ You will find some cells marked as \"Test\" that will help you check your work. Do NOT modify them. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.1\n",
    "\n",
    "Create a view \"**bons_buveurs**\" with the clients (buveurs) of type 'gros' or 'moyen'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS bons_buveurs;\n",
    "CREATE VIEW bons_buveurs AS\n",
    "SELECT *\n",
    "FROM MASTER2sub1\n",
    "WHERE TYPE = 'gros' OR TYPE= 'moyen';\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Artaud</td>\n",
       "        <td>Antonin</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Aron</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Apollinaire</td>\n",
       "        <td>Guillaume</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Arrabal</td>\n",
       "        <td>Fernando</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Anouilh</td>\n",
       "        <td>Jean</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Aragon</td>\n",
       "        <td>Louis</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Andersen</td>\n",
       "        <td>Yann</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Bataille</td>\n",
       "        <td>Georges</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Barthes</td>\n",
       "        <td>Roland</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Bory</td>\n",
       "        <td>Jean Louis</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Balzac</td>\n",
       "        <td>Honore de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Celine</td>\n",
       "        <td>Louis Ferdinand</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Ciceron</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Chateaubriand</td>\n",
       "        <td>Francois-Rene de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>Cocteau</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Courrier</td>\n",
       "        <td>Paul Louis</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Diderot</td>\n",
       "        <td>Denis</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Dumas</td>\n",
       "        <td>Alexandre</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>Dostoievsky</td>\n",
       "        <td>Fiodor</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Dante</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Eluard</td>\n",
       "        <td>Paul</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>Flaubert</td>\n",
       "        <td>Gustave</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Fromentin</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Montesquieu</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Goethe</td>\n",
       "        <td>Johann Wolfgang von</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Musset</td>\n",
       "        <td>Alfred de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Hugo</td>\n",
       "        <td>Victor</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Hardy</td>\n",
       "        <td>Thomas</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Lautreamont</td>\n",
       "        <td>None</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Lamartine</td>\n",
       "        <td>Alphonse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michelet</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Marx</td>\n",
       "        <td>Karl</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Marivaux</td>\n",
       "        <td>Pierre Carlet de Chamblain de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Ovide</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Proust</td>\n",
       "        <td>Marcel</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Platon</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Perec</td>\n",
       "        <td>Georges</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>Prevert</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Paulhan</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Rabelais</td>\n",
       "        <td>Francois</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>Poe</td>\n",
       "        <td>Allan Edgard</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Ronsard</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Racine</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Sandier</td>\n",
       "        <td>Jules</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Sade</td>\n",
       "        <td>Donatien Alphonse Fran¿¿s de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Sartre</td>\n",
       "        <td>Jean-Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Sophocle</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Sue</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Saint Exupery</td>\n",
       "        <td>Antoine de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Segur</td>\n",
       "        <td>Comtesse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Verlaine</td>\n",
       "        <td>Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Virgile</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>Zola</td>\n",
       "        <td>Emile</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Zorn</td>\n",
       "        <td>Fritz</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Maturin</td>\n",
       "        <td>Robert</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cesar</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Artaud', 'Antonin', 'moyen'),\n",
       " (3, 'Aron', 'Raymond', 'gros'),\n",
       " (4, 'Apollinaire', 'Guillaume', 'moyen'),\n",
       " (6, 'Arrabal', 'Fernando', 'gros'),\n",
       " (7, 'Anouilh', 'Jean', 'moyen'),\n",
       " (8, 'Aragon', 'Louis', 'gros'),\n",
       " (10, 'Andersen', 'Yann', 'gros'),\n",
       " (12, 'Bataille', 'Georges', 'moyen'),\n",
       " (13, 'Barthes', 'Roland', 'moyen'),\n",
       " (14, 'Bory', 'Jean Louis', 'gros'),\n",
       " (16, 'Balzac', 'Honore de', 'moyen'),\n",
       " (18, 'Celine', 'Louis Ferdinand', 'gros'),\n",
       " (19, 'Ciceron', None, 'gros'),\n",
       " (20, 'Chateaubriand', 'Francois-Rene de', 'moyen'),\n",
       " (22, 'Cocteau', 'Jean', 'gros'),\n",
       " (24, 'Courrier', 'Paul Louis', 'moyen'),\n",
       " (26, 'Diderot', 'Denis', 'moyen'),\n",
       " (27, 'Dumas', 'Alexandre', 'gros'),\n",
       " (28, 'Dostoievsky', 'Fiodor', 'gros'),\n",
       " (31, 'Dante', None, 'gros'),\n",
       " (32, 'Eluard', 'Paul', 'moyen'),\n",
       " (33, 'Flaubert', 'Gustave', 'moyen'),\n",
       " (35, 'Fromentin', 'Eugene', 'gros'),\n",
       " (39, 'Montesquieu', None, 'gros'),\n",
       " (42, 'Goethe', 'Johann Wolfgang von', 'moyen'),\n",
       " (43, 'Musset', 'Alfred de', 'gros'),\n",
       " (46, 'Hugo', 'Victor', 'moyen'),\n",
       " (48, 'Hardy', 'Thomas', 'moyen'),\n",
       " (50, 'Lautreamont', None, 'moyen'),\n",
       " (53, 'Lamartine', 'Alphonse de', 'gros'),\n",
       " (55, 'Michelet', 'Jules', 'gros'),\n",
       " (57, 'Marx', 'Karl', 'gros'),\n",
       " (58, 'Marivaux', 'Pierre Carlet de Chamblain de', 'gros'),\n",
       " (60, 'Ovide', None, 'gros'),\n",
       " (62, 'Proust', 'Marcel', 'moyen'),\n",
       " (64, 'Platon', None, 'gros'),\n",
       " (66, 'Perec', 'Georges', 'gros'),\n",
       " (68, 'Prevert', 'Jacques', 'gros'),\n",
       " (70, 'Paulhan', 'Jean', 'gros'),\n",
       " (72, 'Rabelais', 'Francois', 'gros'),\n",
       " (73, 'Poe', 'Allan Edgard', 'moyen'),\n",
       " (75, 'Ronsard', 'Pierre', 'moyen'),\n",
       " (76, 'Racine', 'Jean', 'gros'),\n",
       " (78, 'Sandier', 'Jules', 'moyen'),\n",
       " (79, 'Sade', 'Donatien Alphonse Fran¿¿s de', 'gros'),\n",
       " (81, 'Sartre', 'Jean-Paul', 'gros'),\n",
       " (83, 'Sophocle', None, 'gros'),\n",
       " (84, 'Sue', 'Eugene', 'moyen'),\n",
       " (86, 'Saint Exupery', 'Antoine de', 'gros'),\n",
       " (87, 'Segur', 'Comtesse de', 'gros'),\n",
       " (91, 'Verlaine', 'Paul', 'gros'),\n",
       " (93, 'Virgile', None, 'gros'),\n",
       " (94, 'Zola', 'Emile', 'moyen'),\n",
       " (95, 'Zorn', 'Fritz', 'moyen'),\n",
       " (98, 'Maturin', 'Robert', 'gros'),\n",
       " (99, 'Cesar', 'Jules', 'gros')]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test\n",
    "%sql SELECT * FROM bons_buveurs ORDER BY nb;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.2\n",
    "\n",
    "Create the view \"**buveurs_asec**\" with clients (buveurs) who have not bought any wine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS buveurs_asec;\n",
    "CREATE VIEW buveurs_asec AS\n",
    "SELECT *\n",
    "FROM MASTER2sub1\n",
    "WHERE NB NOT IN (SELECT NB FROM MASTER2sub4);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Breton</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Barthes</td>\n",
       "        <td>Roland</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Balzac</td>\n",
       "        <td>Honore de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Celine</td>\n",
       "        <td>Louis Ferdinand</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Chateaubriand</td>\n",
       "        <td>Francois-Rene de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Corbiere</td>\n",
       "        <td>Tristan</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>Corneille</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>Char</td>\n",
       "        <td>Rene</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Dumas</td>\n",
       "        <td>Alexandre</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>Fournier</td>\n",
       "        <td>Alain</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Eluard</td>\n",
       "        <td>Paul</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>Mauriac</td>\n",
       "        <td>Francois</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Fromentin</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Maurois</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>Freud</td>\n",
       "        <td>Sigmund</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Montesquieu</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Goethe</td>\n",
       "        <td>Johann Wolfgang von</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Musset</td>\n",
       "        <td>Alfred de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Hugo</td>\n",
       "        <td>Victor</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>Gary</td>\n",
       "        <td>Romain</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>Lafargue</td>\n",
       "        <td>Paul</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Lamartine</td>\n",
       "        <td>Alphonse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Aurele</td>\n",
       "        <td>Marc</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michelet</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>Nerval</td>\n",
       "        <td>Gerard de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Marx</td>\n",
       "        <td>Karl</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>Nietzsche</td>\n",
       "        <td>Friedrich</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>63</td>\n",
       "        <td>Maupassant</td>\n",
       "        <td>Guy de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Platon</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Pascal</td>\n",
       "        <td>Blaise</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Perec</td>\n",
       "        <td>Georges</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Nin</td>\n",
       "        <td>Anais</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>Prevert</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>Queneau</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Paulhan</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>Radiguet</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Rabelais</td>\n",
       "        <td>Francois</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>Poe</td>\n",
       "        <td>Allan Edgard</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Rimbaud</td>\n",
       "        <td>Arthur</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Racine</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>Rousseau</td>\n",
       "        <td>Jean-Jacques</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Sandier</td>\n",
       "        <td>Jules</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Sade</td>\n",
       "        <td>Donatien Alphonse Fran¿¿s de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Sartre</td>\n",
       "        <td>Jean-Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Sophocle</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Sue</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Segalen</td>\n",
       "        <td>Victor</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Saint Exupery</td>\n",
       "        <td>Antoine de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Segur</td>\n",
       "        <td>Comtesse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Shakespeare</td>\n",
       "        <td>William</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Stendhal</td>\n",
       "        <td>None</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Vian</td>\n",
       "        <td>Boris</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Verlaine</td>\n",
       "        <td>Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Virgile</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Zorn</td>\n",
       "        <td>Fritz</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Maturin</td>\n",
       "        <td>Robert</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cesar</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(11, 'Breton', 'Andre', 'petit'),\n",
       " (13, 'Barthes', 'Roland', 'moyen'),\n",
       " (16, 'Balzac', 'Honore de', 'moyen'),\n",
       " (18, 'Celine', 'Louis Ferdinand', 'gros'),\n",
       " (20, 'Chateaubriand', 'Francois-Rene de', 'moyen'),\n",
       " (21, 'Corbiere', 'Tristan', 'petit'),\n",
       " (23, 'Corneille', 'Pierre', 'petit'),\n",
       " (25, 'Char', 'Rene', 'petit'),\n",
       " (27, 'Dumas', 'Alexandre', 'gros'),\n",
       " (29, 'Fournier', 'Alain', 'petit'),\n",
       " (32, 'Eluard', 'Paul', 'moyen'),\n",
       " (34, 'Mauriac', 'Francois', 'petit'),\n",
       " (35, 'Fromentin', 'Eugene', 'gros'),\n",
       " (36, 'Maurois', 'Andre', 'petit'),\n",
       " (37, 'Freud', 'Sigmund', 'petit'),\n",
       " (39, 'Montesquieu', None, 'gros'),\n",
       " (42, 'Goethe', 'Johann Wolfgang von', 'moyen'),\n",
       " (43, 'Musset', 'Alfred de', 'gros'),\n",
       " (46, 'Hugo', 'Victor', 'moyen'),\n",
       " (47, 'Gary', 'Romain', 'petit'),\n",
       " (51, 'Lafargue', 'Paul', 'petit'),\n",
       " (53, 'Lamartine', 'Alphonse de', 'gros'),\n",
       " (54, 'Aurele', 'Marc', 'petit'),\n",
       " (55, 'Michelet', 'Jules', 'gros'),\n",
       " (56, 'Nerval', 'Gerard de', 'petit'),\n",
       " (57, 'Marx', 'Karl', 'gros'),\n",
       " (59, 'Nietzsche', 'Friedrich', 'petit'),\n",
       " (63, 'Maupassant', 'Guy de', 'petit'),\n",
       " (64, 'Platon', None, 'gros'),\n",
       " (65, 'Pascal', 'Blaise', 'petit'),\n",
       " (66, 'Perec', 'Georges', 'gros'),\n",
       " (67, 'Nin', 'Anais', 'petit'),\n",
       " (68, 'Prevert', 'Jacques', 'gros'),\n",
       " (69, 'Queneau', 'Raymond', 'petit'),\n",
       " (70, 'Paulhan', 'Jean', 'gros'),\n",
       " (71, 'Radiguet', 'Raymond', 'petit'),\n",
       " (72, 'Rabelais', 'Francois', 'gros'),\n",
       " (73, 'Poe', 'Allan Edgard', 'moyen'),\n",
       " (74, 'Rimbaud', 'Arthur', 'petit'),\n",
       " (76, 'Racine', 'Jean', 'gros'),\n",
       " (77, 'Rousseau', 'Jean-Jacques', 'petit'),\n",
       " (78, 'Sandier', 'Jules', 'moyen'),\n",
       " (79, 'Sade', 'Donatien Alphonse Fran¿¿s de', 'gros'),\n",
       " (81, 'Sartre', 'Jean-Paul', 'gros'),\n",
       " (83, 'Sophocle', None, 'gros'),\n",
       " (84, 'Sue', 'Eugene', 'moyen'),\n",
       " (85, 'Segalen', 'Victor', 'petit'),\n",
       " (86, 'Saint Exupery', 'Antoine de', 'gros'),\n",
       " (87, 'Segur', 'Comtesse de', 'gros'),\n",
       " (88, 'Shakespeare', 'William', 'petit'),\n",
       " (89, 'Stendhal', None, 'petit'),\n",
       " (90, 'Vian', 'Boris', 'petit'),\n",
       " (91, 'Verlaine', 'Paul', 'gros'),\n",
       " (93, 'Virgile', None, 'gros'),\n",
       " (95, 'Zorn', 'Fritz', 'moyen'),\n",
       " (98, 'Maturin', 'Robert', 'gros'),\n",
       " (99, 'Cesar', 'Jules', 'gros')]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test\n",
    "%sql SELECT * FROM buveurs_asec ORDER BY NB;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.3\n",
    "\n",
    "Create the view \"**buveurs_achats**\" complementary to the previous one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "(sqlite3.OperationalError) table buveurs_achats already exists [SQL: 'CREATE VIEW buveurs_achats AS\\nSELECT *\\nFROM MASTER2sub1\\nWHERE NB IN (SELECT NB FROM MASTER2sub4);'] (Background on this error at: http://sqlalche.me/e/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS buveurs_asec;\n",
    "CREATE VIEW buveurs_achats AS\n",
    "SELECT *\n",
    "FROM MASTER2sub1\n",
    "WHERE NB IN (SELECT NB FROM MASTER2sub4);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Aristote</td>\n",
       "        <td>None</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Artaud</td>\n",
       "        <td>Antonin</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Aron</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Apollinaire</td>\n",
       "        <td>Guillaume</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Audiberti</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Arrabal</td>\n",
       "        <td>Fernando</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Anouilh</td>\n",
       "        <td>Jean</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Aragon</td>\n",
       "        <td>Louis</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Ajar</td>\n",
       "        <td>Emile</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Andersen</td>\n",
       "        <td>Yann</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Bataille</td>\n",
       "        <td>Georges</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Bory</td>\n",
       "        <td>Jean Louis</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>Baudelaire</td>\n",
       "        <td>Charles</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Camus</td>\n",
       "        <td>Albert</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Ciceron</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>Cocteau</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Courrier</td>\n",
       "        <td>Paul Louis</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Diderot</td>\n",
       "        <td>Denis</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>Dostoievsky</td>\n",
       "        <td>Fiodor</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>La Fontaine</td>\n",
       "        <td>Jean de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Dante</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>Flaubert</td>\n",
       "        <td>Gustave</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>Montaigne</td>\n",
       "        <td>Michel de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>Moliere</td>\n",
       "        <td>None</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>Gauthier</td>\n",
       "        <td>Theophile</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Gide</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Giono</td>\n",
       "        <td>Jean</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Hardy</td>\n",
       "        <td>Thomas</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>Kafka</td>\n",
       "        <td>Franz</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Lautreamont</td>\n",
       "        <td>None</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Carroll</td>\n",
       "        <td>Lewis</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Marivaux</td>\n",
       "        <td>Pierre Carlet de Chamblain de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Ovide</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>Mallarme</td>\n",
       "        <td>Stephane</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Proust</td>\n",
       "        <td>Marcel</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Ronsard</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Sand</td>\n",
       "        <td>Georges</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Roussel</td>\n",
       "        <td>Rene</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>Verne</td>\n",
       "        <td>Jules</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>Zola</td>\n",
       "        <td>Emile</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "        <td>Wilde</td>\n",
       "        <td>Oscar</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>Vigny</td>\n",
       "        <td>Alfred de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Woolf</td>\n",
       "        <td>Virginia</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Aristote', None, 'petit'),\n",
       " (2, 'Artaud', 'Antonin', 'moyen'),\n",
       " (3, 'Aron', 'Raymond', 'gros'),\n",
       " (4, 'Apollinaire', 'Guillaume', 'moyen'),\n",
       " (5, 'Audiberti', 'Jacques', 'petit'),\n",
       " (6, 'Arrabal', 'Fernando', 'gros'),\n",
       " (7, 'Anouilh', 'Jean', 'moyen'),\n",
       " (8, 'Aragon', 'Louis', 'gros'),\n",
       " (9, 'Ajar', 'Emile', 'petit'),\n",
       " (10, 'Andersen', 'Yann', 'gros'),\n",
       " (12, 'Bataille', 'Georges', 'moyen'),\n",
       " (14, 'Bory', 'Jean Louis', 'gros'),\n",
       " (15, 'Baudelaire', 'Charles', 'petit'),\n",
       " (17, 'Camus', 'Albert', 'petit'),\n",
       " (19, 'Ciceron', None, 'gros'),\n",
       " (22, 'Cocteau', 'Jean', 'gros'),\n",
       " (24, 'Courrier', 'Paul Louis', 'moyen'),\n",
       " (26, 'Diderot', 'Denis', 'moyen'),\n",
       " (28, 'Dostoievsky', 'Fiodor', 'gros'),\n",
       " (30, 'La Fontaine', 'Jean de', 'petit'),\n",
       " (31, 'Dante', None, 'gros'),\n",
       " (33, 'Flaubert', 'Gustave', 'moyen'),\n",
       " (38, 'Montaigne', 'Michel de', 'petit'),\n",
       " (40, 'Moliere', None, 'petit'),\n",
       " (41, 'Gauthier', 'Theophile', 'petit'),\n",
       " (44, 'Gide', 'Andre', 'petit'),\n",
       " (45, 'Giono', 'Jean', 'petit'),\n",
       " (48, 'Hardy', 'Thomas', 'moyen'),\n",
       " (49, 'Kafka', 'Franz', 'petit'),\n",
       " (50, 'Lautreamont', None, 'moyen'),\n",
       " (52, 'Carroll', 'Lewis', 'petit'),\n",
       " (58, 'Marivaux', 'Pierre Carlet de Chamblain de', 'gros'),\n",
       " (60, 'Ovide', None, 'gros'),\n",
       " (61, 'Mallarme', 'Stephane', 'petit'),\n",
       " (62, 'Proust', 'Marcel', 'moyen'),\n",
       " (75, 'Ronsard', 'Pierre', 'moyen'),\n",
       " (80, 'Sand', 'Georges', 'petit'),\n",
       " (82, 'Roussel', 'Rene', 'petit'),\n",
       " (92, 'Verne', 'Jules', 'petit'),\n",
       " (94, 'Zola', 'Emile', 'moyen'),\n",
       " (96, 'Wilde', 'Oscar', 'petit'),\n",
       " (97, 'Vigny', 'Alfred de', 'petit'),\n",
       " (100, 'Woolf', 'Virginia', 'petit')]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test\n",
    "%sql SELECT * FROM buveurs_achats ORDER BY nb;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.4\n",
    "\n",
    "Create the view \"**q83pl**\" (LIEU, CRU, QTE_BUE) that provides by LIEU and CRU the total quantities bought in 1983 by all the clients (buveurs)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "DROP VIEW IF EXISTS q83pl;\n",
    "CREATE VIEW q83pl AS\n",
    "SELECT LIEU,CRU,SUM(QTE) AS QTE_BUE \n",
    "FROM MASTER2sub2 NATURAL JOIN MASTER2sub4\n",
    "WHERE DATES >= '1983-01-01' AND DATES <= '1983-12-31'\n",
    "GROUP BY CRU, LIEU;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>LIEU</th>\n",
       "        <th>CRU</th>\n",
       "        <th>QTE_BUE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PARIS</td>\n",
       "        <td>Coteaux du Tricastin</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RENNES</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LYON</td>\n",
       "        <td>Julienas</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PARIS</td>\n",
       "        <td>Pouilly Vinzelles</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CAEN</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PARIS</td>\n",
       "        <td>Beaujolais Primeur</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LILLE</td>\n",
       "        <td>Pommard</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LYON</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROCQUENCOURT</td>\n",
       "        <td>Saint Amour</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROCQUENCOURT</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>260</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('PARIS', 'Coteaux du Tricastin', 1),\n",
       " ('RENNES', 'Mercurey', 1),\n",
       " ('LYON', 'Julienas', 2),\n",
       " ('PARIS', 'Pouilly Vinzelles', 3),\n",
       " ('CAEN', 'Seyssel', 3),\n",
       " ('PARIS', 'Beaujolais Primeur', 4),\n",
       " ('LILLE', 'Pommard', 5),\n",
       " ('LYON', 'Beaujolais Villages', 10),\n",
       " ('ROCQUENCOURT', 'Saint Amour', 80),\n",
       " ('ROCQUENCOURT', 'Beaujolais Villages', 260)]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test\n",
    "%sql SELECT * FROM q83pl ORDER BY QTE_BUE;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.5\n",
    "\n",
    "Can we define the same view with ascending order over the attribute \"QTE\"? Provide an explanation for your answer."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Answer: \n",
    "Yes we could but that would be not be very wise from a programming point of view. This would mean that all users who access the view would see their data ordered by QTE. It would be computationally better for each user if we didn't order as we create the view. Then, users who need the information ordered by QTE can do so separately by ordering in their own new view using the already created (and not ordered) view, and thus without penalising all users.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___\n",
    "# PART II: UPDATE DATABASE CONTENT\n",
    "\n",
    "The syntax for the `UPDATE` operation is:\n",
    "\n",
    "```sql\n",
    "UPDATE table_name\n",
    "SET column1 = value1, column2 = value2...., columnN = valueN\n",
    "WHERE [condition];\n",
    "```\n",
    "\n",
    "The syntax for the `INSERT` operation is:\n",
    "\n",
    "```sql\n",
    "INSERT INTO table_name [(column1, column2, column3,...columnN)]  \n",
    "VALUES (value1, value2, value3,...valueN);\n",
    "```\n",
    "\n",
    "Database updates are commited automatically in Jupyter/Python. _Transactions_ are an option to control and reverse changes. Additionally we can just reload a backup of the database (NOT an option in deployed systems)\n",
    "\n",
    "__Note:__ Different to other Database Management Systems, SQLite views are read-only and so you can not execute a `DELETE`, `INSERT` or `UPDATE` statement on a view."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.1\n",
    "\n",
    "Create a table \"**RBB**\" with the same schema as \"**bons_buveurs**\" which contains the tuples selected from \"**bons_buveurs**\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "DROP TABLE IF EXISTS RBB;\n",
    "CREATE TABLE RBB AS \n",
    "SELECT *\n",
    "FROM MASTER2sub1\n",
    "WHERE TYPE = 'gros' OR TYPE= 'moyen'\n",
    "ORDER BY NB;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Artaud</td>\n",
       "        <td>Antonin</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Aron</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Apollinaire</td>\n",
       "        <td>Guillaume</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Arrabal</td>\n",
       "        <td>Fernando</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Anouilh</td>\n",
       "        <td>Jean</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Aragon</td>\n",
       "        <td>Louis</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Andersen</td>\n",
       "        <td>Yann</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Bataille</td>\n",
       "        <td>Georges</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Barthes</td>\n",
       "        <td>Roland</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Bory</td>\n",
       "        <td>Jean Louis</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Balzac</td>\n",
       "        <td>Honore de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Celine</td>\n",
       "        <td>Louis Ferdinand</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Ciceron</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Chateaubriand</td>\n",
       "        <td>Francois-Rene de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>Cocteau</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Courrier</td>\n",
       "        <td>Paul Louis</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Diderot</td>\n",
       "        <td>Denis</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Dumas</td>\n",
       "        <td>Alexandre</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>Dostoievsky</td>\n",
       "        <td>Fiodor</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Dante</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Eluard</td>\n",
       "        <td>Paul</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>Flaubert</td>\n",
       "        <td>Gustave</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Fromentin</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Montesquieu</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Goethe</td>\n",
       "        <td>Johann Wolfgang von</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Musset</td>\n",
       "        <td>Alfred de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Hugo</td>\n",
       "        <td>Victor</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Hardy</td>\n",
       "        <td>Thomas</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Lautreamont</td>\n",
       "        <td>None</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Lamartine</td>\n",
       "        <td>Alphonse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michelet</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Marx</td>\n",
       "        <td>Karl</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Marivaux</td>\n",
       "        <td>Pierre Carlet de Chamblain de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Ovide</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Proust</td>\n",
       "        <td>Marcel</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Platon</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Perec</td>\n",
       "        <td>Georges</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>Prevert</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Paulhan</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Rabelais</td>\n",
       "        <td>Francois</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>Poe</td>\n",
       "        <td>Allan Edgard</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Ronsard</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Racine</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Sandier</td>\n",
       "        <td>Jules</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Sade</td>\n",
       "        <td>Donatien Alphonse Fran¿¿s de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Sartre</td>\n",
       "        <td>Jean-Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Sophocle</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Sue</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Saint Exupery</td>\n",
       "        <td>Antoine de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Segur</td>\n",
       "        <td>Comtesse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Verlaine</td>\n",
       "        <td>Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Virgile</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>Zola</td>\n",
       "        <td>Emile</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Zorn</td>\n",
       "        <td>Fritz</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Maturin</td>\n",
       "        <td>Robert</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cesar</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Artaud', 'Antonin', 'moyen'),\n",
       " (3, 'Aron', 'Raymond', 'gros'),\n",
       " (4, 'Apollinaire', 'Guillaume', 'moyen'),\n",
       " (6, 'Arrabal', 'Fernando', 'gros'),\n",
       " (7, 'Anouilh', 'Jean', 'moyen'),\n",
       " (8, 'Aragon', 'Louis', 'gros'),\n",
       " (10, 'Andersen', 'Yann', 'gros'),\n",
       " (12, 'Bataille', 'Georges', 'moyen'),\n",
       " (13, 'Barthes', 'Roland', 'moyen'),\n",
       " (14, 'Bory', 'Jean Louis', 'gros'),\n",
       " (16, 'Balzac', 'Honore de', 'moyen'),\n",
       " (18, 'Celine', 'Louis Ferdinand', 'gros'),\n",
       " (19, 'Ciceron', None, 'gros'),\n",
       " (20, 'Chateaubriand', 'Francois-Rene de', 'moyen'),\n",
       " (22, 'Cocteau', 'Jean', 'gros'),\n",
       " (24, 'Courrier', 'Paul Louis', 'moyen'),\n",
       " (26, 'Diderot', 'Denis', 'moyen'),\n",
       " (27, 'Dumas', 'Alexandre', 'gros'),\n",
       " (28, 'Dostoievsky', 'Fiodor', 'gros'),\n",
       " (31, 'Dante', None, 'gros'),\n",
       " (32, 'Eluard', 'Paul', 'moyen'),\n",
       " (33, 'Flaubert', 'Gustave', 'moyen'),\n",
       " (35, 'Fromentin', 'Eugene', 'gros'),\n",
       " (39, 'Montesquieu', None, 'gros'),\n",
       " (42, 'Goethe', 'Johann Wolfgang von', 'moyen'),\n",
       " (43, 'Musset', 'Alfred de', 'gros'),\n",
       " (46, 'Hugo', 'Victor', 'moyen'),\n",
       " (48, 'Hardy', 'Thomas', 'moyen'),\n",
       " (50, 'Lautreamont', None, 'moyen'),\n",
       " (53, 'Lamartine', 'Alphonse de', 'gros'),\n",
       " (55, 'Michelet', 'Jules', 'gros'),\n",
       " (57, 'Marx', 'Karl', 'gros'),\n",
       " (58, 'Marivaux', 'Pierre Carlet de Chamblain de', 'gros'),\n",
       " (60, 'Ovide', None, 'gros'),\n",
       " (62, 'Proust', 'Marcel', 'moyen'),\n",
       " (64, 'Platon', None, 'gros'),\n",
       " (66, 'Perec', 'Georges', 'gros'),\n",
       " (68, 'Prevert', 'Jacques', 'gros'),\n",
       " (70, 'Paulhan', 'Jean', 'gros'),\n",
       " (72, 'Rabelais', 'Francois', 'gros'),\n",
       " (73, 'Poe', 'Allan Edgard', 'moyen'),\n",
       " (75, 'Ronsard', 'Pierre', 'moyen'),\n",
       " (76, 'Racine', 'Jean', 'gros'),\n",
       " (78, 'Sandier', 'Jules', 'moyen'),\n",
       " (79, 'Sade', 'Donatien Alphonse Fran¿¿s de', 'gros'),\n",
       " (81, 'Sartre', 'Jean-Paul', 'gros'),\n",
       " (83, 'Sophocle', None, 'gros'),\n",
       " (84, 'Sue', 'Eugene', 'moyen'),\n",
       " (86, 'Saint Exupery', 'Antoine de', 'gros'),\n",
       " (87, 'Segur', 'Comtesse de', 'gros'),\n",
       " (91, 'Verlaine', 'Paul', 'gros'),\n",
       " (93, 'Virgile', None, 'gros'),\n",
       " (94, 'Zola', 'Emile', 'moyen'),\n",
       " (95, 'Zorn', 'Fritz', 'moyen'),\n",
       " (98, 'Maturin', 'Robert', 'gros'),\n",
       " (99, 'Cesar', 'Jules', 'gros')]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test\n",
    "%sql SELECT * FROM RBB;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.2\n",
    "\n",
    "Update the table you used to create \"**bons_buveurs**\": Change the \"type\" to 'gros' if the total of quantities bought is over 100."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the instances to update (schema may differ from the one in your table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Artaud</td>\n",
       "        <td>Antonin</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Audiberti</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Ajar</td>\n",
       "        <td>Emile</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Gide</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Artaud', 'Antonin', 'moyen'),\n",
       " (5, 'Audiberti', 'Jacques', 'petit'),\n",
       " (9, 'Ajar', 'Emile', 'petit'),\n",
       " (44, 'Gide', 'Andre', 'petit')]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT NB, NOM, PRENOM, TYPE\n",
    "FROM MASTER2sub1\n",
    "NATURAL JOIN MASTER2sub4\n",
    "WHERE TYPE != 'gros'\n",
    "GROUP BY NB\n",
    "HAVING SUM(QTE) > 100;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Update instances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql UPDATE MASTER2sub1\n",
    "SET TYPE = \"gros\"\n",
    "WHERE NB IN (SELECT NB\n",
    "             FROM MASTER2sub4\n",
    "             GROUP BY NB\n",
    "             HAVING SUM(QTE) > 100)\n",
    "and TYPE != 'gros';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.3\n",
    "\n",
    "Compare the content of _table_ \"**RBB**\" and the _view_ \"**bons_buveurs**\" after the update. What differences do you see? Explain"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Answer: The update has been made for bons_buveurs, but not RBB even though they took the same inital data. This can be problematic as it no longer becomes clear which tables are up to date and which aren't."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Artaud</td>\n",
       "        <td>Antonin</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Artaud', 'Antonin', 'moyen')]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT *\n",
    "FROM RBB\n",
    "WHERE NB IN (2,5,9,44);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Artaud</td>\n",
       "        <td>Antonin</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Gide</td>\n",
       "        <td>Andre</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Ajar</td>\n",
       "        <td>Emile</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Audiberti</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Artaud', 'Antonin', 'gros'),\n",
       " (44, 'Gide', 'Andre', 'gros'),\n",
       " (9, 'Ajar', 'Emile', 'gros'),\n",
       " (5, 'Audiberti', 'Jacques', 'gros')]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT *\n",
    "FROM bons_buveurs\n",
    "WHERE NB IN (2,5,9,44);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.4\n",
    "\n",
    "Create a table \"**RBA**\" with the same schema as \"**buveurs_asec**\" which contains the tuples selected from \"**buveurs_asec**\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql DROP TABLE IF EXISTS RBA;\n",
    "\n",
    "--Create RBA table\n",
    "CREATE TABLE RBA AS\n",
    "SELECT *\n",
    "FROM buveurs_asec;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Breton</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Barthes</td>\n",
       "        <td>Roland</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Balzac</td>\n",
       "        <td>Honore de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Celine</td>\n",
       "        <td>Louis Ferdinand</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Chateaubriand</td>\n",
       "        <td>Francois-Rene de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Corbiere</td>\n",
       "        <td>Tristan</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>Corneille</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>Char</td>\n",
       "        <td>Rene</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Dumas</td>\n",
       "        <td>Alexandre</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>Fournier</td>\n",
       "        <td>Alain</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Eluard</td>\n",
       "        <td>Paul</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>Mauriac</td>\n",
       "        <td>Francois</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Fromentin</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Maurois</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>Freud</td>\n",
       "        <td>Sigmund</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Montesquieu</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Goethe</td>\n",
       "        <td>Johann Wolfgang von</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Musset</td>\n",
       "        <td>Alfred de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Hugo</td>\n",
       "        <td>Victor</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>Gary</td>\n",
       "        <td>Romain</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>Lafargue</td>\n",
       "        <td>Paul</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Lamartine</td>\n",
       "        <td>Alphonse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Aurele</td>\n",
       "        <td>Marc</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michelet</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>Nerval</td>\n",
       "        <td>Gerard de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Marx</td>\n",
       "        <td>Karl</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>Nietzsche</td>\n",
       "        <td>Friedrich</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>63</td>\n",
       "        <td>Maupassant</td>\n",
       "        <td>Guy de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Platon</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Pascal</td>\n",
       "        <td>Blaise</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Perec</td>\n",
       "        <td>Georges</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Nin</td>\n",
       "        <td>Anais</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>Prevert</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>Queneau</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Paulhan</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>Radiguet</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Rabelais</td>\n",
       "        <td>Francois</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>Poe</td>\n",
       "        <td>Allan Edgard</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Rimbaud</td>\n",
       "        <td>Arthur</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Racine</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>Rousseau</td>\n",
       "        <td>Jean-Jacques</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Sandier</td>\n",
       "        <td>Jules</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Sade</td>\n",
       "        <td>Donatien Alphonse Fran¿¿s de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Sartre</td>\n",
       "        <td>Jean-Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Sophocle</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Sue</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Segalen</td>\n",
       "        <td>Victor</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Saint Exupery</td>\n",
       "        <td>Antoine de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Segur</td>\n",
       "        <td>Comtesse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Shakespeare</td>\n",
       "        <td>William</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Stendhal</td>\n",
       "        <td>None</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Vian</td>\n",
       "        <td>Boris</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Verlaine</td>\n",
       "        <td>Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Virgile</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Zorn</td>\n",
       "        <td>Fritz</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Maturin</td>\n",
       "        <td>Robert</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cesar</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(11, 'Breton', 'Andre', 'petit'),\n",
       " (13, 'Barthes', 'Roland', 'moyen'),\n",
       " (16, 'Balzac', 'Honore de', 'moyen'),\n",
       " (18, 'Celine', 'Louis Ferdinand', 'gros'),\n",
       " (20, 'Chateaubriand', 'Francois-Rene de', 'moyen'),\n",
       " (21, 'Corbiere', 'Tristan', 'petit'),\n",
       " (23, 'Corneille', 'Pierre', 'petit'),\n",
       " (25, 'Char', 'Rene', 'petit'),\n",
       " (27, 'Dumas', 'Alexandre', 'gros'),\n",
       " (29, 'Fournier', 'Alain', 'petit'),\n",
       " (32, 'Eluard', 'Paul', 'moyen'),\n",
       " (34, 'Mauriac', 'Francois', 'petit'),\n",
       " (35, 'Fromentin', 'Eugene', 'gros'),\n",
       " (36, 'Maurois', 'Andre', 'petit'),\n",
       " (37, 'Freud', 'Sigmund', 'petit'),\n",
       " (39, 'Montesquieu', None, 'gros'),\n",
       " (42, 'Goethe', 'Johann Wolfgang von', 'moyen'),\n",
       " (43, 'Musset', 'Alfred de', 'gros'),\n",
       " (46, 'Hugo', 'Victor', 'moyen'),\n",
       " (47, 'Gary', 'Romain', 'petit'),\n",
       " (51, 'Lafargue', 'Paul', 'petit'),\n",
       " (53, 'Lamartine', 'Alphonse de', 'gros'),\n",
       " (54, 'Aurele', 'Marc', 'petit'),\n",
       " (55, 'Michelet', 'Jules', 'gros'),\n",
       " (56, 'Nerval', 'Gerard de', 'petit'),\n",
       " (57, 'Marx', 'Karl', 'gros'),\n",
       " (59, 'Nietzsche', 'Friedrich', 'petit'),\n",
       " (63, 'Maupassant', 'Guy de', 'petit'),\n",
       " (64, 'Platon', None, 'gros'),\n",
       " (65, 'Pascal', 'Blaise', 'petit'),\n",
       " (66, 'Perec', 'Georges', 'gros'),\n",
       " (67, 'Nin', 'Anais', 'petit'),\n",
       " (68, 'Prevert', 'Jacques', 'gros'),\n",
       " (69, 'Queneau', 'Raymond', 'petit'),\n",
       " (70, 'Paulhan', 'Jean', 'gros'),\n",
       " (71, 'Radiguet', 'Raymond', 'petit'),\n",
       " (72, 'Rabelais', 'Francois', 'gros'),\n",
       " (73, 'Poe', 'Allan Edgard', 'moyen'),\n",
       " (74, 'Rimbaud', 'Arthur', 'petit'),\n",
       " (76, 'Racine', 'Jean', 'gros'),\n",
       " (77, 'Rousseau', 'Jean-Jacques', 'petit'),\n",
       " (78, 'Sandier', 'Jules', 'moyen'),\n",
       " (79, 'Sade', 'Donatien Alphonse Fran¿¿s de', 'gros'),\n",
       " (81, 'Sartre', 'Jean-Paul', 'gros'),\n",
       " (83, 'Sophocle', None, 'gros'),\n",
       " (84, 'Sue', 'Eugene', 'moyen'),\n",
       " (85, 'Segalen', 'Victor', 'petit'),\n",
       " (86, 'Saint Exupery', 'Antoine de', 'gros'),\n",
       " (87, 'Segur', 'Comtesse de', 'gros'),\n",
       " (88, 'Shakespeare', 'William', 'petit'),\n",
       " (89, 'Stendhal', None, 'petit'),\n",
       " (90, 'Vian', 'Boris', 'petit'),\n",
       " (91, 'Verlaine', 'Paul', 'gros'),\n",
       " (93, 'Virgile', None, 'gros'),\n",
       " (95, 'Zorn', 'Fritz', 'moyen'),\n",
       " (98, 'Maturin', 'Robert', 'gros'),\n",
       " (99, 'Cesar', 'Jules', 'gros')]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test\n",
    "%sql SELECT * FROM RBA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.5\n",
    "\n",
    "Insert a tuple (101, 'your last name', 'your first name', 'your type of purchases(petit, moyen, or gros)') to \"**RBA**\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql INSERT INTO RBA (NB, NOM, PRENOM, TYPE)  \n",
    "VALUES (101, 'Tchibozo', 'Maxime', 'petit');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Breton</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Barthes</td>\n",
       "        <td>Roland</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Balzac</td>\n",
       "        <td>Honore de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Celine</td>\n",
       "        <td>Louis Ferdinand</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Chateaubriand</td>\n",
       "        <td>Francois-Rene de</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Corbiere</td>\n",
       "        <td>Tristan</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>Corneille</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>Char</td>\n",
       "        <td>Rene</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Dumas</td>\n",
       "        <td>Alexandre</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>Fournier</td>\n",
       "        <td>Alain</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Eluard</td>\n",
       "        <td>Paul</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>Mauriac</td>\n",
       "        <td>Francois</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Fromentin</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Maurois</td>\n",
       "        <td>Andre</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>Freud</td>\n",
       "        <td>Sigmund</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Montesquieu</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Goethe</td>\n",
       "        <td>Johann Wolfgang von</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Musset</td>\n",
       "        <td>Alfred de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Hugo</td>\n",
       "        <td>Victor</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>Gary</td>\n",
       "        <td>Romain</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>Lafargue</td>\n",
       "        <td>Paul</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Lamartine</td>\n",
       "        <td>Alphonse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Aurele</td>\n",
       "        <td>Marc</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michelet</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>Nerval</td>\n",
       "        <td>Gerard de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Marx</td>\n",
       "        <td>Karl</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>Nietzsche</td>\n",
       "        <td>Friedrich</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>63</td>\n",
       "        <td>Maupassant</td>\n",
       "        <td>Guy de</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Platon</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Pascal</td>\n",
       "        <td>Blaise</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Perec</td>\n",
       "        <td>Georges</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Nin</td>\n",
       "        <td>Anais</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>Prevert</td>\n",
       "        <td>Jacques</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>Queneau</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Paulhan</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>Radiguet</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Rabelais</td>\n",
       "        <td>Francois</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>Poe</td>\n",
       "        <td>Allan Edgard</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Rimbaud</td>\n",
       "        <td>Arthur</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Racine</td>\n",
       "        <td>Jean</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>Rousseau</td>\n",
       "        <td>Jean-Jacques</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Sandier</td>\n",
       "        <td>Jules</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Sade</td>\n",
       "        <td>Donatien Alphonse Fran¿¿s de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Sartre</td>\n",
       "        <td>Jean-Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Sophocle</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Sue</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Segalen</td>\n",
       "        <td>Victor</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Saint Exupery</td>\n",
       "        <td>Antoine de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Segur</td>\n",
       "        <td>Comtesse de</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Shakespeare</td>\n",
       "        <td>William</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Stendhal</td>\n",
       "        <td>None</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Vian</td>\n",
       "        <td>Boris</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Verlaine</td>\n",
       "        <td>Paul</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Virgile</td>\n",
       "        <td>None</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Zorn</td>\n",
       "        <td>Fritz</td>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Maturin</td>\n",
       "        <td>Robert</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cesar</td>\n",
       "        <td>Jules</td>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Tchibozo</td>\n",
       "        <td>Maxime</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(11, 'Breton', 'Andre', 'petit'),\n",
       " (13, 'Barthes', 'Roland', 'moyen'),\n",
       " (16, 'Balzac', 'Honore de', 'moyen'),\n",
       " (18, 'Celine', 'Louis Ferdinand', 'gros'),\n",
       " (20, 'Chateaubriand', 'Francois-Rene de', 'moyen'),\n",
       " (21, 'Corbiere', 'Tristan', 'petit'),\n",
       " (23, 'Corneille', 'Pierre', 'petit'),\n",
       " (25, 'Char', 'Rene', 'petit'),\n",
       " (27, 'Dumas', 'Alexandre', 'gros'),\n",
       " (29, 'Fournier', 'Alain', 'petit'),\n",
       " (32, 'Eluard', 'Paul', 'moyen'),\n",
       " (34, 'Mauriac', 'Francois', 'petit'),\n",
       " (35, 'Fromentin', 'Eugene', 'gros'),\n",
       " (36, 'Maurois', 'Andre', 'petit'),\n",
       " (37, 'Freud', 'Sigmund', 'petit'),\n",
       " (39, 'Montesquieu', None, 'gros'),\n",
       " (42, 'Goethe', 'Johann Wolfgang von', 'moyen'),\n",
       " (43, 'Musset', 'Alfred de', 'gros'),\n",
       " (46, 'Hugo', 'Victor', 'moyen'),\n",
       " (47, 'Gary', 'Romain', 'petit'),\n",
       " (51, 'Lafargue', 'Paul', 'petit'),\n",
       " (53, 'Lamartine', 'Alphonse de', 'gros'),\n",
       " (54, 'Aurele', 'Marc', 'petit'),\n",
       " (55, 'Michelet', 'Jules', 'gros'),\n",
       " (56, 'Nerval', 'Gerard de', 'petit'),\n",
       " (57, 'Marx', 'Karl', 'gros'),\n",
       " (59, 'Nietzsche', 'Friedrich', 'petit'),\n",
       " (63, 'Maupassant', 'Guy de', 'petit'),\n",
       " (64, 'Platon', None, 'gros'),\n",
       " (65, 'Pascal', 'Blaise', 'petit'),\n",
       " (66, 'Perec', 'Georges', 'gros'),\n",
       " (67, 'Nin', 'Anais', 'petit'),\n",
       " (68, 'Prevert', 'Jacques', 'gros'),\n",
       " (69, 'Queneau', 'Raymond', 'petit'),\n",
       " (70, 'Paulhan', 'Jean', 'gros'),\n",
       " (71, 'Radiguet', 'Raymond', 'petit'),\n",
       " (72, 'Rabelais', 'Francois', 'gros'),\n",
       " (73, 'Poe', 'Allan Edgard', 'moyen'),\n",
       " (74, 'Rimbaud', 'Arthur', 'petit'),\n",
       " (76, 'Racine', 'Jean', 'gros'),\n",
       " (77, 'Rousseau', 'Jean-Jacques', 'petit'),\n",
       " (78, 'Sandier', 'Jules', 'moyen'),\n",
       " (79, 'Sade', 'Donatien Alphonse Fran¿¿s de', 'gros'),\n",
       " (81, 'Sartre', 'Jean-Paul', 'gros'),\n",
       " (83, 'Sophocle', None, 'gros'),\n",
       " (84, 'Sue', 'Eugene', 'moyen'),\n",
       " (85, 'Segalen', 'Victor', 'petit'),\n",
       " (86, 'Saint Exupery', 'Antoine de', 'gros'),\n",
       " (87, 'Segur', 'Comtesse de', 'gros'),\n",
       " (88, 'Shakespeare', 'William', 'petit'),\n",
       " (89, 'Stendhal', None, 'petit'),\n",
       " (90, 'Vian', 'Boris', 'petit'),\n",
       " (91, 'Verlaine', 'Paul', 'gros'),\n",
       " (93, 'Virgile', None, 'gros'),\n",
       " (95, 'Zorn', 'Fritz', 'moyen'),\n",
       " (98, 'Maturin', 'Robert', 'gros'),\n",
       " (99, 'Cesar', 'Jules', 'gros'),\n",
       " (101, 'Tchibozo', 'Maxime', 'petit')]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test\n",
    "%sql SELECT * FROM RBA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.6\n",
    "\n",
    "Compare the content of _table_ \"**RBA**\" and the _view_ \"**buveurs_asec**\". What differences do you see? Explain"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Answer: RBA has been updated, but not buveurs_a_sec.This is because  buveurs_asec is a view of the table MASTER2sub1 and not the table RBA, so it only sees the modifications made on MASTER2sub1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NB</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Tchibozo</td>\n",
       "        <td>Maxime</td>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(101, 'Tchibozo', 'Maxime', 'petit')]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT *\n",
    "FROM RBA\n",
    "WHERE NB = 101"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "(sqlite3.OperationalError) no such table: buveurs_a_sec [SQL: 'SELECT *\\nFROM buveurs_a_sec\\nWHERE NB = 101'] (Background on this error at: http://sqlalche.me/e/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%%sql SELECT *\n",
    "FROM buveurs_a_sec\n",
    "WHERE NB = 101"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___\n",
    "# PART III: Design the database schema for posts in a Social Network\n",
    "\n",
    "In this section your task is to design the database schema for a social network app of a new startup:\n",
    "\n",
    "The new social network will contain users, where each user will have a name, a nickname, an email, date of birth, and an address (Street, City, State, Country, Postal Code). Users can be friends of other users, and can publish posts. Each post can contain a text, date and attachment. Posts can be either original posts or replies so the app needs to handle both scenarios. When users log in, the app needs to display the posts of their friends.\n",
    "\n",
    "**Note:** You can create diagrams of your proposal and insert them as images into this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 3.1\n",
    "\n",
    "Write and explain the design of the relations of your database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Answer: We define the following tables :\n",
    "\n",
    "<center> **Users** </center>\n",
    "\n",
    "|  *Attribute*  |         *Description*          |\n",
    "|---------------|--------------------------------|\n",
    "| ID            | ID of the user                 |\n",
    "| NAME          | Name of the user               |\n",
    "| NICKNAME      | Nickname of the user           |\n",
    "| EMAIL         | Email of the user              |\n",
    "| DATE_OF_BIRTH | Date of Birth of the user      |\n",
    "| STREET        | Street where the user lives    |\n",
    "| POSTAL_CODE   | Postal code of the user's city |\n",
    "\n",
    "The attribute ID is a key for the table Users. \n",
    "\n",
    "Assuming the street with the postal code entirely define the user's adress, we only keep the attributes STREET and POSTAL_CODE in our table Users, and we define a new table Location, with the attributes STREET, CITY, STATE, COUNTRY & POSTAL_CODE. Thus, the attributes (STREET, POSTAL_CODE) of the table Users are a foreign key for the table Location defined below.\n",
    "\n",
    "\n",
    "<center> **Location** </center>\n",
    "\n",
    "|  *Attribute*  |         *Description*          |\n",
    "|---------------|--------------------------------|\n",
    "| STREET        | Street where the user lives    |\n",
    "| CITY          | City where the user lives      |\n",
    "| STATE         | State where the user lives     |\n",
    "| COUNTRY       | Country where the user lives   |\n",
    "| POSTAL_CODE   | Postal code of the user's city |\n",
    "\n",
    "\n",
    "\n",
    "<center> **Friendship** </center>\n",
    "\n",
    "|  *Attribute*  |         *Description*          |\n",
    "|---------------|--------------------------------|\n",
    "| ID1           | ID of the user                 |\n",
    "| ID2           | ID of the user's friend        |\n",
    "\n",
    "\n",
    "<center> **Posts** </center>\n",
    "\n",
    "|*Attribute*|         *Description*          |\n",
    "|-----------|--------------------------------|\n",
    "| ID        | ID of the user                 |\n",
    "| TEXT      | Name of the user               |\n",
    "| DATE      | Nickname of the user           |\n",
    "| ATTACHMENT| Email of the user              |\n",
    "| ORIGINAL  | Date of Birth of the user      |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 3.2\n",
    "\n",
    "Write a view to retrieve the posts to display when a user logs in. Consider that some users may have a lot of friends and you need to limit the number of post to display. How would you select relevant posts to display first? What kind of information would you use/add in the database for this purpose? Explain your answer.\n",
    "\n",
    "__Note:__ Limiting the number of posts just by count is too simplistic, the user could be missing something interesting to him/her."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Answer:"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}