DB-TP2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TP2 - DB Normalization and Querying\n",
    "\n",
    "The objectives of this TP are:\n",
    "1. Apply normalization 1NF -> 2NF -> 3NF\n",
    "2. Perform SQL queries on the normalized database\n",
    "\n",
    "In this TP, we will use a database **`wine.db`** (available in the course's website) containing wine information related to 'production' and 'sales'. \n",
    "\n",
    "> Production <---> Wine <---> Sales\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "### Working with db files in Jupyter\n",
    "- Python provides an interface for SQLite through the *sqlite3* module\n",
    "- The **`%%sql`** magic builds upon it (and other tools) to enable the usage of SQL commands within a Jupyter Notebook as in common SQL clients.\n",
    "- Before proceeding, make sure that **`wine.db`** is in the same path as this notebook.\n",
    "  - If **`wine.db`** is not in the same path, an empty **`wine.db`** file will be created, resulting in errors in later steps of the TP.\n",
    "- The SQLite module in Python commits transactions automatically, this means that any change in the DB is immediately written to the file, e.g. creating/deleting tables.\n",
    "  -  For this reason, it is recommended to have a backup of **`wine.db`** as it is provided in the course's website.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`wine.db`** contains the following unnormalized tables:\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",
    "From wikipedia:\n",
    "\n",
    "__Cru__: Often used to indicate a specifically named and legally defined vineyard or ensemble of vineyards and the vines \"which grow on [such] a reputed terroir; by extension of good quality.\" The term is also used to refer to the wine produced from such vines.\n",
    "\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": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3    # Python interface for SQLite databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def printSchema(connection):\n",
    "    # Function to print the DB schema\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": 13,
   "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",
      "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",
      "customer:\n",
      "  0: NB(NUM)\n",
      "  1: NOM(TEXT)\n",
      "  2: PRENOM(TEXT)\n",
      "  3: TYPE(TEXT)\n",
      "location:\n",
      "  0: LIEU(TEXT)\n",
      "  1: REGION(TEXT)\n",
      "producteur:\n",
      "  0: NP(NUM)\n",
      "  1: NOM(TEXT)\n",
      "  2: PRENOM(TEXT)\n",
      "  3: REGION(TEXT)\n",
      "production:\n",
      "  0: NV(NUM)\n",
      "  1: NP(NUM)\n",
      "  2: QTE(NUM)\n",
      "ventes:\n",
      "  0: NV(NUM)\n",
      "  1: NB(NUM)\n",
      "  2: DATES(NUM)\n",
      "  3: LIEU(TEXT)\n",
      "  4: QTE(NUM)\n",
      "wine:\n",
      "  0: NV(NUM)\n",
      "  1: CRU(TEXT)\n",
      "  2: DEGRE(NUM)\n",
      "  3: MILL(NUM)\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect('wine.db')\n",
    "c = conn.cursor()\n",
    "print(\"Database schema:\")\n",
    "printSchema(conn)           # An usefull way to visualize the content of the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From this point we will use __%%sql__ magic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @wine.db'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite:///wine.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PART I: Database normalization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first task on this TP is the normalization of the wine data. In its current state both tables **Master1** and **Master2** are in the First Normal Form (1NF).\n",
    "\n",
    "By inspecting the content of these tables we can see that multiple tuples have NULL values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NV</th>\n",
       "        <th>CRU</th>\n",
       "        <th>DEGRE</th>\n",
       "        <th>MILL</th>\n",
       "        <th>QTE</th>\n",
       "        <th>NP</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>REGION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3</td>\n",
       "        <td>Six</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>Marmagne</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>8</td>\n",
       "        <td>Lioger d&#x27;Harduy</td>\n",
       "        <td>Gabriel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>16</td>\n",
       "        <td>Barbin</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>17</td>\n",
       "        <td>Faiveley</td>\n",
       "        <td>Guy</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>18</td>\n",
       "        <td>Tramier</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>19</td>\n",
       "        <td>Dupaquier</td>\n",
       "        <td>Roger</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>20</td>\n",
       "        <td>Lamy</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>21</td>\n",
       "        <td>Cornu</td>\n",
       "        <td>Edmond</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>26</td>\n",
       "        <td>Violot</td>\n",
       "        <td>Gilbert</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, None, None, None, 3, 'Six', 'Paul', 'Alsace'),\n",
       " (None, None, None, None, None, 6, 'Marmagne', 'Bernard', 'Bourgogne'),\n",
       " (None, None, None, None, None, 8, \"Lioger d'Harduy\", 'Gabriel', 'Bourgogne'),\n",
       " (None, None, None, None, None, 16, 'Barbin', 'Bernard', 'Bourgogne'),\n",
       " (None, None, None, None, None, 17, 'Faiveley', 'Guy', 'Bourgogne'),\n",
       " (None, None, None, None, None, 18, 'Tramier', 'Jean', 'Bourgogne'),\n",
       " (None, None, None, None, None, 19, 'Dupaquier', 'Roger', 'Bourgogne'),\n",
       " (None, None, None, None, None, 20, 'Lamy', 'Jean', 'Bourgogne'),\n",
       " (None, None, None, None, None, 21, 'Cornu', 'Edmond', 'Bourgogne'),\n",
       " (None, None, None, None, None, 26, 'Violot', 'Gilbert', 'Bourgogne')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT *\n",
    "FROM Master1\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Notice that Jupyter *displays* 'None' instead of 'NULL'. \n",
    "  - This is only to comply with python notation.\n",
    "* To account for NULL values, your SQL queries must test explicitly for 'NULL'.\n",
    "\n",
    "Another problem in **Master1** and **Master2** is data redundancy, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NV</th>\n",
       "        <th>CRU</th>\n",
       "        <th>DEGRE</th>\n",
       "        <th>MILL</th>\n",
       "        <th>QTE</th>\n",
       "        <th>NP</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>REGION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "        <td>90</td>\n",
       "        <td>2</td>\n",
       "        <td>Boxler</td>\n",
       "        <td>Albert</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "        <td>912</td>\n",
       "        <td>67</td>\n",
       "        <td>Descombes</td>\n",
       "        <td>Jean Ernest</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "        <td>98</td>\n",
       "        <td>71</td>\n",
       "        <td>Chalandard</td>\n",
       "        <td>Danile</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "        <td>540</td>\n",
       "        <td>78</td>\n",
       "        <td>Michlel</td>\n",
       "        <td>Pierre Emile</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "        <td>450</td>\n",
       "        <td>86</td>\n",
       "        <td>Dumazet</td>\n",
       "        <td>Marc</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45, 'Chiroubles', None, 1983, 90, 2, 'Boxler', 'Albert', 'Alsace'),\n",
       " (45, 'Chiroubles', None, 1983, 912, 67, 'Descombes', 'Jean Ernest', 'Beaujolais'),\n",
       " (45, 'Chiroubles', None, 1983, 98, 71, 'Chalandard', 'Danile', 'Jura'),\n",
       " (45, 'Chiroubles', None, 1983, 540, 78, 'Michlel', 'Pierre Emile', 'Jura'),\n",
       " (45, 'Chiroubles', None, 1983, 450, 86, 'Dumazet', 'Marc', 'Rhone')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT *\n",
    "FROM Master1\n",
    "WHERE NV = 45;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Additional resource for Normalization:\n",
    "\n",
    "https://www.youtube.com/watch?v=UrYLYV7WSHM\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.1\n",
    "\n",
    "Convert table **Master1** to the Second Normal Form (2NF) or Third Normal Form (3NF) as applicable.\n",
    "* Explain your answer\n",
    "* List main functional dependencies (not all of them)\n",
    "* Describe the schema of new tables and how they relate\n",
    "  * You can write Tables as above or you can insert images in the notebook.\n",
    "  \n",
    "Remember that **`wine.db`** contains information related to wine 'production' and 'sells'.\n",
    "\n",
    "> Production <---> Wine <---> Sales\n",
    "\n",
    "A good start point is to look for the 'Wine' attributes.\n",
    "\n",
    "**Hint:** Look for redundant information between the master tables."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "To convert table Master1 to a Third Normal Form (3NF), we can split the table between 3 tables ( Wine, Sales, and Production) in order to remove repeating groups, remove partial dependencies, and remove transitive dependencies we have:\n",
    "1. WINE['NV', 'CRU', 'DEGRE', 'MILL']\n",
    "2. PRODUCTION['NV', 'NP', 'QTE']\n",
    "3. PRODUCTEUR['NP', 'NOM', 'PRENOM', 'REGION']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, lets have a look to the first table, Wine :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NV</th>\n",
       "        <th>CRU</th>\n",
       "        <th>DEGRE</th>\n",
       "        <th>MILL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>11.5</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>11.5</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Julienas</td>\n",
       "        <td>11.3</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>10.9</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Pommard</td>\n",
       "        <td>11.7</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>11.2</td>\n",
       "        <td>1981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Grands Echezeaux</td>\n",
       "        <td>11.7</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Cotes de Beaune Villages</td>\n",
       "        <td>12.3</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Chapelle Chambertin</td>\n",
       "        <td>11.9</td>\n",
       "        <td>1973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>11.8</td>\n",
       "        <td>1979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Sylvaner</td>\n",
       "        <td>11.2</td>\n",
       "        <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Gentil</td>\n",
       "        <td>10.7</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Gentil</td>\n",
       "        <td>10.7</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Gentil</td>\n",
       "        <td>10.7</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Cotes du Rhone</td>\n",
       "        <td>12.3</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Chateau Chalon</td>\n",
       "        <td>10.9</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>Cote de Brouilly</td>\n",
       "        <td>11.9</td>\n",
       "        <td>1973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Meursault</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Meursault</td>\n",
       "        <td>11.4</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Chambolle Musigny</td>\n",
       "        <td>12.3</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Clos du roi</td>\n",
       "        <td>11.2</td>\n",
       "        <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Cote de Brouilly</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Cotes de Nuits</td>\n",
       "        <td>None</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>None</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>Pommard</td>\n",
       "        <td>None</td>\n",
       "        <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Cotes de Beaune Villages</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>Chapelle Chambertin</td>\n",
       "        <td>10.5</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Chateau Corton Grancey</td>\n",
       "        <td>None</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Puligny Montrachet</td>\n",
       "        <td>11</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>Volnay</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>Corton</td>\n",
       "        <td>11.6</td>\n",
       "        <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>Bourgogne Passe Tout Grains</td>\n",
       "        <td>12</td>\n",
       "        <td>1979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Monthelie</td>\n",
       "        <td>11.4</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Pouilly Vinzelles</td>\n",
       "        <td>12.3</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Pouilly Vinzelles</td>\n",
       "        <td>12.3</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>Santenay</td>\n",
       "        <td>12</td>\n",
       "        <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>Montagny</td>\n",
       "        <td>11.2</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Auxey Duresses</td>\n",
       "        <td>11.3</td>\n",
       "        <td>1914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Auxey Duresses</td>\n",
       "        <td>11.3</td>\n",
       "        <td>1914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Chorey les Beaunes</td>\n",
       "        <td>11</td>\n",
       "        <td>1955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>11</td>\n",
       "        <td>1977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>None</td>\n",
       "        <td>1973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>Richebourg</td>\n",
       "        <td>12</td>\n",
       "        <td>1943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>Clairette de Die</td>\n",
       "        <td>12</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Beaujolais Primeur</td>\n",
       "        <td>12</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Fleurie</td>\n",
       "        <td>11.4</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Fleurie</td>\n",
       "        <td>11.4</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Fleurie</td>\n",
       "        <td>11.4</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Moulin a vent</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>None</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>11</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>Chiroubles</td>\n",
       "        <td>11</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Cote de Brouilly</td>\n",
       "        <td>12</td>\n",
       "        <td>1899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>Morgon</td>\n",
       "        <td>12</td>\n",
       "        <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Chenas</td>\n",
       "        <td>11.5</td>\n",
       "        <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>Chenas</td>\n",
       "        <td>10</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Brouilly</td>\n",
       "        <td>None</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>None</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>10.5</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>10.5</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>10.5</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>10.5</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>Saint Amour</td>\n",
       "        <td>None</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Crozes Hermitage</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Crozes Hermitage</td>\n",
       "        <td>12.5</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>Crozes - Hermitage</td>\n",
       "        <td>12.3</td>\n",
       "        <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Crozes-Hermitage</td>\n",
       "        <td>11</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Crozes-Hermitage</td>\n",
       "        <td>11</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>Hermitage</td>\n",
       "        <td>12</td>\n",
       "        <td>1979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Gigondas</td>\n",
       "        <td>11</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>63</td>\n",
       "        <td>Coteaux du Tricastin</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Coteaux du Tricastin</td>\n",
       "        <td>11</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Coteaux du Tricastin</td>\n",
       "        <td>11</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Rochgrande</td>\n",
       "        <td>10.8</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Rochgrande</td>\n",
       "        <td>10.8</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Tavel</td>\n",
       "        <td>11</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Gewurztraminer</td>\n",
       "        <td>12.5</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Gewurztraminer</td>\n",
       "        <td>12.5</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Gewurztraminer</td>\n",
       "        <td>12.5</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>Riesling</td>\n",
       "        <td>14</td>\n",
       "        <td>1962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>Tokay d&#x27;Alsace</td>\n",
       "        <td>11</td>\n",
       "        <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>Tokay d&#x27;Alsace</td>\n",
       "        <td>11</td>\n",
       "        <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>Tokay d&#x27;Alsace</td>\n",
       "        <td>11</td>\n",
       "        <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Sylvaner</td>\n",
       "        <td>11</td>\n",
       "        <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>Palette</td>\n",
       "        <td>11</td>\n",
       "        <td>1981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Cassis</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>Bellet</td>\n",
       "        <td>11</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Arbois</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Arbois</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Arbois</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Arbois</td>\n",
       "        <td>11</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Chateau Chalon</td>\n",
       "        <td>12</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Chateau Chalon</td>\n",
       "        <td>12</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>Etoile</td>\n",
       "        <td>11</td>\n",
       "        <td>1979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Etoile</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Etoile</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Etoile</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Etoile</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Etoile</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Cornas</td>\n",
       "        <td>11.1</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Cornas</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Cornas</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Saint Amour</td>\n",
       "        <td>10</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Rasteau</td>\n",
       "        <td>10</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Tavel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Tavel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>None</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>None</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>None</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>10</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>11</td>\n",
       "        <td>1969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>11</td>\n",
       "        <td>1969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>11</td>\n",
       "        <td>1969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>11</td>\n",
       "        <td>1969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>11</td>\n",
       "        <td>1969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Palette</td>\n",
       "        <td>11</td>\n",
       "        <td>1969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Palette</td>\n",
       "        <td>11</td>\n",
       "        <td>1969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Cassis</td>\n",
       "        <td>11</td>\n",
       "        <td>1979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>Coteaux de Pierevert</td>\n",
       "        <td>None</td>\n",
       "        <td>1967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Blanquette de Limoux</td>\n",
       "        <td>None</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>Cotes du Rousillon</td>\n",
       "        <td>None</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>Cotes du Rousillon</td>\n",
       "        <td>None</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Cotes du Rousillon</td>\n",
       "        <td>12</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "        <td>Saint Chinian</td>\n",
       "        <td>11</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>Saint Chinian</td>\n",
       "        <td>11</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Corbieres</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Corbieres</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Corbieres</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Corbieres</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Corbieres</td>\n",
       "        <td>10</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cote Rotie</td>\n",
       "        <td>13</td>\n",
       "        <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cote Rotie</td>\n",
       "        <td>13</td>\n",
       "        <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>11</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Saint Veran</td>\n",
       "        <td>12.5</td>\n",
       "        <td>1973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>107</td>\n",
       "        <td>Sancerre</td>\n",
       "        <td>14</td>\n",
       "        <td>1998</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Mercurey', 11.5, 1980),\n",
       " (1, 'Mercurey', 11.5, 1980),\n",
       " (2, 'Julienas', 11.3, 1974),\n",
       " (3, 'Savigny les Beaunes', 12.1, 1978),\n",
       " (4, 'Mercurey', 10.9, 1980),\n",
       " (5, 'Pommard', 11.7, 1976),\n",
       " (6, 'Mercurey', 11.2, 1981),\n",
       " (7, 'Grands Echezeaux', 11.7, 1968),\n",
       " (8, 'Cotes de Beaune Villages', 12.3, 1975),\n",
       " (9, 'Chapelle Chambertin', 11.9, 1973),\n",
       " (10, 'Beaujolais Villages', 11.8, 1979),\n",
       " (11, 'Sylvaner', 11.2, 1982),\n",
       " (12, 'Gentil', 10.7, 1975),\n",
       " (12, 'Gentil', 10.7, 1975),\n",
       " (12, 'Gentil', 10.7, 1975),\n",
       " (13, 'Cotes du Rhone', 12.3, 1968),\n",
       " (14, 'Chateau Chalon', 10.9, 1978),\n",
       " (15, 'Cote de Brouilly', 11.9, 1973),\n",
       " (16, 'Meursault', 12.1, 1980),\n",
       " (17, 'Meursault', 11.4, 1975),\n",
       " (18, 'Chambolle Musigny', 12.3, 1968),\n",
       " (19, 'Clos du roi', 11.2, 1972),\n",
       " (20, 'Cote de Brouilly', 12.1, 1980),\n",
       " (21, 'Cotes de Nuits', None, 1978),\n",
       " (22, 'Savigny les Beaunes', None, 1974),\n",
       " (23, 'Pommard', None, 1972),\n",
       " (24, 'Cotes de Beaune Villages', 12.1, 1971),\n",
       " (25, 'Chapelle Chambertin', 10.5, 1968),\n",
       " (26, 'Chateau Corton Grancey', None, 1980),\n",
       " (27, 'Puligny Montrachet', 11, 1974),\n",
       " (28, 'Volnay', 11, 1980),\n",
       " (29, 'Corton', 11.6, 1972),\n",
       " (30, 'Bourgogne Passe Tout Grains', 12, 1979),\n",
       " (31, 'Monthelie', 11.4, 1968),\n",
       " (32, 'Pouilly Vinzelles', 12.3, 1968),\n",
       " (32, 'Pouilly Vinzelles', 12.3, 1968),\n",
       " (33, 'Santenay', 12, 1972),\n",
       " (34, 'Montagny', 11.2, 1978),\n",
       " (35, 'Auxey Duresses', 11.3, 1914),\n",
       " (35, 'Auxey Duresses', 11.3, 1914),\n",
       " (36, 'Chorey les Beaunes', 11, 1955),\n",
       " (37, 'Savigny les Beaunes', 10, 1978),\n",
       " (38, 'Savigny les Beaunes', 11, 1977),\n",
       " (39, 'Savigny les Beaunes', None, 1973),\n",
       " (40, 'Richebourg', 12, 1943),\n",
       " (41, 'Clairette de Die', 12, 1976),\n",
       " (42, 'Beaujolais Primeur', 12, 1983),\n",
       " (43, 'Fleurie', 11.4, 1980),\n",
       " (43, 'Fleurie', 11.4, 1980),\n",
       " (43, 'Fleurie', 11.4, 1980),\n",
       " (44, 'Moulin a vent', None, 1983),\n",
       " (45, 'Chiroubles', None, 1983),\n",
       " (45, 'Chiroubles', None, 1983),\n",
       " (45, 'Chiroubles', None, 1983),\n",
       " (45, 'Chiroubles', None, 1983),\n",
       " (45, 'Chiroubles', None, 1983),\n",
       " (46, 'Chiroubles', 11, 1983),\n",
       " (47, 'Chiroubles', 11, 1978),\n",
       " (48, 'Cote de Brouilly', 12, 1899),\n",
       " (49, 'Morgon', 12, 1984),\n",
       " (50, 'Chenas', 11.5, 1984),\n",
       " (51, 'Chenas', 10, 1975),\n",
       " (52, 'Brouilly', None, 1975),\n",
       " (53, 'Beaujolais Villages', None, 1975),\n",
       " (54, 'Beaujolais Villages', 10.5, 1976),\n",
       " (54, 'Beaujolais Villages', 10.5, 1976),\n",
       " (54, 'Beaujolais Villages', 10.5, 1976),\n",
       " (54, 'Beaujolais Villages', 10.5, 1976),\n",
       " (55, 'Beaujolais Villages', 12.1, 1978),\n",
       " (56, 'Saint Amour', None, 1974),\n",
       " (57, 'Crozes Hermitage', 12.1, 1974),\n",
       " (58, 'Crozes Hermitage', 12.5, 1975),\n",
       " (59, 'Crozes - Hermitage', 12.3, 1972),\n",
       " (60, 'Crozes-Hermitage', 11, 1983),\n",
       " (60, 'Crozes-Hermitage', 11, 1983),\n",
       " (61, 'Hermitage', 12, 1979),\n",
       " (62, 'Gigondas', 11, 1978),\n",
       " (63, 'Coteaux du Tricastin', 10, 1978),\n",
       " (64, 'Coteaux du Tricastin', 11, 1974),\n",
       " (64, 'Coteaux du Tricastin', 11, 1974),\n",
       " (65, 'Rochgrande', 10.8, 1968),\n",
       " (65, 'Rochgrande', 10.8, 1968),\n",
       " (66, 'Tavel', 11, 1968),\n",
       " (67, 'Gewurztraminer', 12.5, 1968),\n",
       " (67, 'Gewurztraminer', 12.5, 1968),\n",
       " (67, 'Gewurztraminer', 12.5, 1968),\n",
       " (68, 'Riesling', 14, 1962),\n",
       " (69, \"Tokay d'Alsace\", 11, 1982),\n",
       " (69, \"Tokay d'Alsace\", 11, 1982),\n",
       " (69, \"Tokay d'Alsace\", 11, 1982),\n",
       " (70, 'Sylvaner', 11, 1982),\n",
       " (71, 'Palette', 11, 1981),\n",
       " (72, 'Cassis', 10, 1978),\n",
       " (73, 'Bellet', 11, 1976),\n",
       " (74, 'Arbois', 12, 1980),\n",
       " (74, 'Arbois', 12, 1980),\n",
       " (74, 'Arbois', 12, 1980),\n",
       " (75, 'Arbois', 11, 1976),\n",
       " (76, 'Chateau Chalon', 12, 1976),\n",
       " (76, 'Chateau Chalon', 12, 1976),\n",
       " (77, 'Etoile', 11, 1979),\n",
       " (78, 'Etoile', 12, 1980),\n",
       " (78, 'Etoile', 12, 1980),\n",
       " (78, 'Etoile', 12, 1980),\n",
       " (78, 'Etoile', 12, 1980),\n",
       " (78, 'Etoile', 12, 1980),\n",
       " (79, 'Seyssel', 11, 1980),\n",
       " (79, 'Seyssel', 11, 1980),\n",
       " (79, 'Seyssel', 11, 1980),\n",
       " (80, 'Seyssel', 11, 1980),\n",
       " (81, 'Cornas', 11.1, 1980),\n",
       " (82, 'Cornas', 11, 1980),\n",
       " (82, 'Cornas', 11, 1980),\n",
       " (83, 'Saint Amour', 10, 1980),\n",
       " (84, 'Rasteau', 10, 1980),\n",
       " (85, 'Tavel', 11, 1980),\n",
       " (86, 'Tavel', 11, 1980),\n",
       " (87, 'Cotes de Provence', None, 1980),\n",
       " (87, 'Cotes de Provence', None, 1980),\n",
       " (87, 'Cotes de Provence', None, 1980),\n",
       " (88, 'Cotes de Provence', 10, 1976),\n",
       " (89, 'Cotes de Provence', 11, 1969),\n",
       " (89, 'Cotes de Provence', 11, 1969),\n",
       " (89, 'Cotes de Provence', 11, 1969),\n",
       " (89, 'Cotes de Provence', 11, 1969),\n",
       " (89, 'Cotes de Provence', 11, 1969),\n",
       " (90, 'Palette', 11, 1969),\n",
       " (90, 'Palette', 11, 1969),\n",
       " (91, 'Cassis', 11, 1979),\n",
       " (92, 'Coteaux de Pierevert', None, 1967),\n",
       " (93, 'Blanquette de Limoux', None, 1978),\n",
       " (94, 'Cotes du Rousillon', None, 1978),\n",
       " (94, 'Cotes du Rousillon', None, 1978),\n",
       " (95, 'Cotes du Rousillon', 12, 1978),\n",
       " (96, 'Saint Chinian', 11, 1976),\n",
       " (97, 'Saint Chinian', 11, 1976),\n",
       " (98, 'Corbieres', 10, 1978),\n",
       " (98, 'Corbieres', 10, 1978),\n",
       " (98, 'Corbieres', 10, 1978),\n",
       " (98, 'Corbieres', 10, 1978),\n",
       " (98, 'Corbieres', 10, 1978),\n",
       " (99, 'Cote Rotie', 13, 1994),\n",
       " (99, 'Cote Rotie', 13, 1994),\n",
       " (100, 'Seyssel', 11, 1974),\n",
       " (101, 'Saint Veran', 12.5, 1973),\n",
       " (107, 'Sancerre', 14, 1998)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT nv, cru, degre, mill\n",
    "FROM Master1 \n",
    "WHERE nv != \"NONE\" \n",
    "ORDER BY nv ASC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can conclude that in the table WINE, the attribute 'NV' is key => 3NF\n",
    "\n",
    "Then, lets have a look to the second table, PRODUCTION :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NP</th>\n",
       "        <th>NV</th>\n",
       "        <th>QTE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>9</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>40</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>54</td>\n",
       "        <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>63</td>\n",
       "        <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>66</td>\n",
       "        <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>89</td>\n",
       "        <td>980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>54</td>\n",
       "        <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>45</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>49</td>\n",
       "        <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>50</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>54</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>34</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>78</td>\n",
       "        <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>11</td>\n",
       "        <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>21</td>\n",
       "        <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>67</td>\n",
       "        <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>99</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>12</td>\n",
       "        <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>19</td>\n",
       "        <td>986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>32</td>\n",
       "        <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>69</td>\n",
       "        <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>4</td>\n",
       "        <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>35</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>67</td>\n",
       "        <td>890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>92</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>94</td>\n",
       "        <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>65</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>68</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>69</td>\n",
       "        <td>320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>13</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>43</td>\n",
       "        <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>54</td>\n",
       "        <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>55</td>\n",
       "        <td>450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>51</td>\n",
       "        <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>89</td>\n",
       "        <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>30</td>\n",
       "        <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>35</td>\n",
       "        <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>56</td>\n",
       "        <td>790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>98</td>\n",
       "        <td>986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>65</td>\n",
       "        <td>560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>100</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>78</td>\n",
       "        <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>94</td>\n",
       "        <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>5</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>78</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>98</td>\n",
       "        <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>24</td>\n",
       "        <td>859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>16</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>70</td>\n",
       "        <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>71</td>\n",
       "        <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>73</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>75</td>\n",
       "        <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>87</td>\n",
       "        <td>630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>18</td>\n",
       "        <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>52</td>\n",
       "        <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>53</td>\n",
       "        <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>57</td>\n",
       "        <td>940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>6</td>\n",
       "        <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>89</td>\n",
       "        <td>980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>12</td>\n",
       "        <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>26</td>\n",
       "        <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>82</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>58</td>\n",
       "        <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>59</td>\n",
       "        <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>87</td>\n",
       "        <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>76</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>20</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>28</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>29</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>31</td>\n",
       "        <td>320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>60</td>\n",
       "        <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>38</td>\n",
       "        <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>39</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>47</td>\n",
       "        <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>72</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>37</td>\n",
       "        <td>830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>85</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>88</td>\n",
       "        <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>89</td>\n",
       "        <td>723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>90</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>96</td>\n",
       "        <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>33</td>\n",
       "        <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>60</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>62</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>46</td>\n",
       "        <td>760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>45</td>\n",
       "        <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>78</td>\n",
       "        <td>980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>69</td>\n",
       "        <td>690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>74</td>\n",
       "        <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>97</td>\n",
       "        <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>101</td>\n",
       "        <td>213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>45</td>\n",
       "        <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>32</td>\n",
       "        <td>890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>43</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>64</td>\n",
       "        <td>870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>74</td>\n",
       "        <td>850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>87</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>79</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>98</td>\n",
       "        <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>41</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>98</td>\n",
       "        <td>890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>45</td>\n",
       "        <td>540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>79</td>\n",
       "        <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>91</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>93</td>\n",
       "        <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>15</td>\n",
       "        <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>78</td>\n",
       "        <td>970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>82</td>\n",
       "        <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>79</td>\n",
       "        <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>42</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>67</td>\n",
       "        <td>760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>74</td>\n",
       "        <td>675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>43</td>\n",
       "        <td>983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>64</td>\n",
       "        <td>980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>76</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>77</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>14</td>\n",
       "        <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>22</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>23</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>45</td>\n",
       "        <td>450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>98</td>\n",
       "        <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>12</td>\n",
       "        <td>310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>89</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>8</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>61</td>\n",
       "        <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>80</td>\n",
       "        <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>81</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>36</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>44</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>48</td>\n",
       "        <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>86</td>\n",
       "        <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>17</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>25</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>83</td>\n",
       "        <td>423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>84</td>\n",
       "        <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>90</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>95</td>\n",
       "        <td>900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>7</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>10</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>27</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>99</td>\n",
       "        <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>102</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>104</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>105</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>106</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>107</td>\n",
       "        <td>107</td>\n",
       "        <td>7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>108</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>109</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>110</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>111</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>112</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>113</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>114</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>115</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>116</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>117</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>118</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>119</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>121</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>122</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>123</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>124</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>125</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>126</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>127</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>129</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, 9, None),\n",
       " (None, 40, 90),\n",
       " (None, 54, 210),\n",
       " (None, 63, 136),\n",
       " (None, 66, 124),\n",
       " (None, 89, 980),\n",
       " (1, 1, 300),\n",
       " (1, 3, 400),\n",
       " (1, 54, 500),\n",
       " (2, 45, 90),\n",
       " (2, 49, 150),\n",
       " (2, 50, 100),\n",
       " (2, 54, 900),\n",
       " (3, None, None),\n",
       " (4, 34, None),\n",
       " (4, 78, 300),\n",
       " (5, 2, 100),\n",
       " (5, 11, 200),\n",
       " (5, 21, 150),\n",
       " (5, 67, 125),\n",
       " (5, 99, 100),\n",
       " (6, None, None),\n",
       " (7, 12, 31),\n",
       " (7, 19, 986),\n",
       " (8, None, None),\n",
       " (9, 32, 500),\n",
       " (9, 69, 96),\n",
       " (10, 4, 35),\n",
       " (10, 35, 900),\n",
       " (10, 67, 890),\n",
       " (11, 92, 900),\n",
       " (11, 94, 500),\n",
       " (12, 65, 10),\n",
       " (12, 68, None),\n",
       " (12, 69, 320),\n",
       " (13, 13, 13),\n",
       " (13, 43, 500),\n",
       " (14, 54, 500),\n",
       " (14, 55, 450),\n",
       " (15, 51, 36),\n",
       " (15, 89, 150),\n",
       " (16, None, None),\n",
       " (17, None, None),\n",
       " (18, None, None),\n",
       " (19, None, None),\n",
       " (20, None, None),\n",
       " (21, None, None),\n",
       " (22, 30, 350),\n",
       " (23, 35, 350),\n",
       " (24, 56, 790),\n",
       " (24, 98, 986),\n",
       " (25, 65, 560),\n",
       " (25, 100, 100),\n",
       " (26, None, None),\n",
       " (27, 78, 360),\n",
       " (27, 94, 180),\n",
       " (28, None, None),\n",
       " (29, None, None),\n",
       " (30, 5, 46),\n",
       " (30, 78, 900),\n",
       " (31, None, None),\n",
       " (32, None, None),\n",
       " (33, None, None),\n",
       " (34, None, None),\n",
       " (35, 98, 400),\n",
       " (36, 24, 859),\n",
       " (37, None, None),\n",
       " (38, 16, 100),\n",
       " (38, 70, 120),\n",
       " (38, 71, 132),\n",
       " (38, 73, 45),\n",
       " (38, 75, 69),\n",
       " (38, 87, 630),\n",
       " (39, None, None),\n",
       " (40, 18, 120),\n",
       " (40, 52, 500),\n",
       " (40, 53, 800),\n",
       " (40, 57, 940),\n",
       " (41, None, None),\n",
       " (42, 6, 300),\n",
       " (42, 89, 980),\n",
       " (43, 12, 200),\n",
       " (43, 26, 70),\n",
       " (43, 82, 100),\n",
       " (44, None, None),\n",
       " (45, 58, 70),\n",
       " (45, 59, 160),\n",
       " (45, 87, 680),\n",
       " (46, 76, 100),\n",
       " (47, None, None),\n",
       " (48, None, None),\n",
       " (49, None, None),\n",
       " (50, 20, 100),\n",
       " (50, 28, 10),\n",
       " (50, 29, 50),\n",
       " (50, 31, 320),\n",
       " (50, 60, 300),\n",
       " (51, None, None),\n",
       " (52, 38, 125),\n",
       " (52, 39, 80),\n",
       " (52, 47, 75),\n",
       " (52, 72, 34),\n",
       " (53, None, None),\n",
       " (54, 37, 830),\n",
       " (55, 85, 46),\n",
       " (55, 88, 129),\n",
       " (55, 89, 723),\n",
       " (56, 90, 900),\n",
       " (57, None, None),\n",
       " (58, None, None),\n",
       " (59, 96, 370),\n",
       " (60, 33, 70),\n",
       " (60, 60, 60),\n",
       " (60, 62, 900),\n",
       " (61, None, None),\n",
       " (62, None, None),\n",
       " (64, 46, 760),\n",
       " (66, None, None),\n",
       " (67, 45, 912),\n",
       " (68, 78, 980),\n",
       " (69, 69, 690),\n",
       " (70, 74, 650),\n",
       " (70, 97, 101),\n",
       " (70, 101, 213),\n",
       " (71, 45, 98),\n",
       " (72, 32, 890),\n",
       " (72, 43, 900),\n",
       " (72, 64, 870),\n",
       " (72, 74, 850),\n",
       " (72, 87, 900),\n",
       " (73, 1, 1),\n",
       " (74, 79, 100),\n",
       " (75, 98, 150),\n",
       " (76, 41, 25),\n",
       " (76, 98, 890),\n",
       " (77, None, None),\n",
       " (78, 45, 540),\n",
       " (79, 79, 79),\n",
       " (79, 91, 24),\n",
       " (79, 93, 110),\n",
       " (80, 15, 290),\n",
       " (80, 78, 970),\n",
       " (80, 82, 800),\n",
       " (81, 79, 800),\n",
       " (82, 42, 80),\n",
       " (82, 67, 760),\n",
       " (83, None, None),\n",
       " (84, 74, 675),\n",
       " (85, 43, 983),\n",
       " (85, 64, 980),\n",
       " (85, 76, 900),\n",
       " (85, 77, 100),\n",
       " (86, 14, 180),\n",
       " (86, 22, 50),\n",
       " (86, 23, 15),\n",
       " (86, 45, 450),\n",
       " (88, 98, 340),\n",
       " (89, 12, 310),\n",
       " (89, 89, 100),\n",
       " (90, 8, 12),\n",
       " (90, 61, 120),\n",
       " (90, 80, 79),\n",
       " (90, 81, 24),\n",
       " (91, None, None),\n",
       " (92, None, None),\n",
       " (93, 36, 50),\n",
       " (93, 44, 40),\n",
       " (93, 48, 70),\n",
       " (93, 86, 500),\n",
       " (94, None, None),\n",
       " (95, 17, 50),\n",
       " (95, 25, 40),\n",
       " (95, 83, 423),\n",
       " (95, 84, 146),\n",
       " (95, 90, 40),\n",
       " (95, 95, 900),\n",
       " (96, None, None),\n",
       " (97, None, None),\n",
       " (98, 7, 60),\n",
       " (98, 10, 100),\n",
       " (99, 27, 60),\n",
       " (99, 99, 199),\n",
       " (100, None, None),\n",
       " (101, None, None),\n",
       " (102, None, None),\n",
       " (103, None, None),\n",
       " (104, None, None),\n",
       " (105, None, None),\n",
       " (106, None, None),\n",
       " (107, 107, 7000),\n",
       " (108, None, None),\n",
       " (109, None, None),\n",
       " (110, None, None),\n",
       " (111, None, None),\n",
       " (112, None, None),\n",
       " (113, None, None),\n",
       " (114, None, None),\n",
       " (115, None, None),\n",
       " (116, None, None),\n",
       " (117, None, None),\n",
       " (118, None, None),\n",
       " (119, None, None),\n",
       " (121, None, None),\n",
       " (122, None, None),\n",
       " (123, None, None),\n",
       " (124, None, None),\n",
       " (125, None, None),\n",
       " (126, None, None),\n",
       " (127, None, None),\n",
       " (129, None, None)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT np, nv, qte \n",
    "FROM Master1 \n",
    "ORDER BY np, nv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can conclude that in the table PRODUCTION , the attribute {'NP','NV'} is key => 3NF\n",
    "\n",
    "Finally, lets have a look to the third table, PRODUCTEUR :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NP</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>REGION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Bohn</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Bohn</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Bohn</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Boxler</td>\n",
       "        <td>Albert</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Boxler</td>\n",
       "        <td>Albert</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Boxler</td>\n",
       "        <td>Albert</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Boxler</td>\n",
       "        <td>Albert</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Six</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Stentz</td>\n",
       "        <td>Fernand</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Stentz</td>\n",
       "        <td>Fernand</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Joudeat</td>\n",
       "        <td>Lucien</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Joudeat</td>\n",
       "        <td>Lucien</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Joudeat</td>\n",
       "        <td>Lucien</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Joudeat</td>\n",
       "        <td>Lucien</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Joudeat</td>\n",
       "        <td>Lucien</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Marmagne</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Lasnier</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Lasnier</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Lioger d&#x27;Harduy</td>\n",
       "        <td>Gabriel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Moniot</td>\n",
       "        <td>Gilbert</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Moniot</td>\n",
       "        <td>Gilbert</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Fournier</td>\n",
       "        <td>Jean Claude</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Fournier</td>\n",
       "        <td>Jean Claude</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Fournier</td>\n",
       "        <td>Jean Claude</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Clerget</td>\n",
       "        <td>Anne</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Clerget</td>\n",
       "        <td>Anne</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Tortochot</td>\n",
       "        <td>Gabriel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Tortochot</td>\n",
       "        <td>Gabriel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Tortochot</td>\n",
       "        <td>Gabriel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Grivot</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Grivot</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Jayer</td>\n",
       "        <td>Henri</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Jayer</td>\n",
       "        <td>Henri</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>Drouhin</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>Drouhin</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Barbin</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Faiveley</td>\n",
       "        <td>Guy</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Tramier</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Dupaquier</td>\n",
       "        <td>Roger</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Lamy</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Cornu</td>\n",
       "        <td>Edmond</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>Dubreuil Fontaine</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>Laleure</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Guillemot</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Guillemot</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>Valentin Bouchotte</td>\n",
       "        <td>Roger</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>Valentin Bouchotte</td>\n",
       "        <td>Roger</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Violot</td>\n",
       "        <td>Gilbert</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Glantenay</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>Glantenay</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>Monnier</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>de Suremain</td>\n",
       "        <td>Robert</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>Desbois</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>Desbois</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Prudhon</td>\n",
       "        <td>Henri</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Mestre</td>\n",
       "        <td>Philippe</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>Prieur</td>\n",
       "        <td>Guy</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>Monnier</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>Roulot</td>\n",
       "        <td>Guy</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Sauzet</td>\n",
       "        <td>Etienne</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>Delagrange Bachelet</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>de Launay</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>de Launay</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>de Launay</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>de Launay</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>de Launay</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>de Launay</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>de Suremin</td>\n",
       "        <td>Hugues</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>Genelot</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>Genelot</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>Genelot</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>Genelot</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>Juillot</td>\n",
       "        <td>Michel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Juillot</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Juillot</td>\n",
       "        <td>Maurice</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Menand</td>\n",
       "        <td>Jean Claude</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Menand</td>\n",
       "        <td>Jean Claude</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Menand</td>\n",
       "        <td>Jean Claude</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Feyveley</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Jeannin</td>\n",
       "        <td>Andre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Jeannin</td>\n",
       "        <td>Andre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Jeannin</td>\n",
       "        <td>Andre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Monassier</td>\n",
       "        <td>Armand</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>Thenard</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Flamand</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>Guillot</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Chagny</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Chagny</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Chagny</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Chagny</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Chagny</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>Simonet</td>\n",
       "        <td>Leon</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Lapierre</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Lapierre</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Lapierre</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Lapierre</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michon</td>\n",
       "        <td>None</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michon</td>\n",
       "        <td>None</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Michon</td>\n",
       "        <td>None</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>Coutelle</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Desmules</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Joubert</td>\n",
       "        <td>Claude</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>La Thuilliere</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Vernus</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Vernus</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>Vernus</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>Champier</td>\n",
       "        <td>Charles</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Champagnon</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>Marius Bel Air</td>\n",
       "        <td>Morin</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Descombes</td>\n",
       "        <td>Jean Ernest</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>Savoye</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>Janin</td>\n",
       "        <td>Marcel</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Trichard</td>\n",
       "        <td>Benoit</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Trichard</td>\n",
       "        <td>Benoit</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>Trichard</td>\n",
       "        <td>Benoit</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>Chalandard</td>\n",
       "        <td>Danile</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Reverchon</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Reverchon</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Reverchon</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Reverchon</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Reverchon</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>Bourguignon</td>\n",
       "        <td>Emile</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Grand</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Loye</td>\n",
       "        <td>Camille</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Maire</td>\n",
       "        <td>Henri</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>Maire</td>\n",
       "        <td>Henri</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>Tissot</td>\n",
       "        <td>Andre</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Michlel</td>\n",
       "        <td>Pierre Emile</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Petit</td>\n",
       "        <td>Gerard</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Petit</td>\n",
       "        <td>Gerard</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Petit</td>\n",
       "        <td>Gerard</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Devaux</td>\n",
       "        <td>Jean Paul</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Devaux</td>\n",
       "        <td>Jean Paul</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Devaux</td>\n",
       "        <td>Jean Paul</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Peltier</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Mollex</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Savoie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Mollex</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Savoie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Goy</td>\n",
       "        <td>None</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Metral</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Champet</td>\n",
       "        <td>Emile</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Champet</td>\n",
       "        <td>Emile</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Champet</td>\n",
       "        <td>Emile</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Champet</td>\n",
       "        <td>Emile</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Dumazet</td>\n",
       "        <td>Marc</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Dumazet</td>\n",
       "        <td>Marc</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Dumazet</td>\n",
       "        <td>Marc</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Dumazet</td>\n",
       "        <td>Marc</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Begot</td>\n",
       "        <td>Albert</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Michelas</td>\n",
       "        <td>Robert</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>Michelas</td>\n",
       "        <td>Robert</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Roure</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Roure</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Roure</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Roure</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Chave</td>\n",
       "        <td>Gerard</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>De Boissieu</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Marsanne</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Marsanne</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Marsanne</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>Marsanne</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>Grippat</td>\n",
       "        <td>Jean Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Verset</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Verset</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Verset</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Verset</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Verset</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>Verset</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "        <td>Boisson</td>\n",
       "        <td>Jean Pierre</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>Quiot</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Berard</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Berard</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Verdan</td>\n",
       "        <td>Antoine</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>102</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>Coulouvrat</td>\n",
       "        <td>Michel</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>104</td>\n",
       "        <td>Rabasse Chavarin</td>\n",
       "        <td>Abel</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>105</td>\n",
       "        <td>Roux</td>\n",
       "        <td>Charles</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>106</td>\n",
       "        <td>Alary</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>107</td>\n",
       "        <td>Meunier</td>\n",
       "        <td>Laurent</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>108</td>\n",
       "        <td>Archimbaud</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>109</td>\n",
       "        <td>Rey</td>\n",
       "        <td>Marcel</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>110</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>111</td>\n",
       "        <td>Zafiropulo</td>\n",
       "        <td>Costa</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>112</td>\n",
       "        <td>Prebost</td>\n",
       "        <td>Gaston</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>113</td>\n",
       "        <td>Gomez</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>114</td>\n",
       "        <td>Brunet</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>115</td>\n",
       "        <td>Bordonado</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>116</td>\n",
       "        <td>Imbert</td>\n",
       "        <td>Christian</td>\n",
       "        <td>Corse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>117</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Languedoc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>118</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Languedoc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>119</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>121</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>122</td>\n",
       "        <td>Dupuy</td>\n",
       "        <td>Charles</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>123</td>\n",
       "        <td>Laurent</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>124</td>\n",
       "        <td>Daurat Fort</td>\n",
       "        <td>Robert</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>125</td>\n",
       "        <td>Baldes</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>126</td>\n",
       "        <td>Jouffreau</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>127</td>\n",
       "        <td>Brumont</td>\n",
       "        <td>Alban</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>129</td>\n",
       "        <td>Barrere</td>\n",
       "        <td>Alfred</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, None, None),\n",
       " (None, None, None, None),\n",
       " (None, None, None, None),\n",
       " (None, None, None, None),\n",
       " (None, None, None, None),\n",
       " (None, None, None, None),\n",
       " (1, 'Bohn', 'Rene', 'Alsace'),\n",
       " (1, 'Bohn', 'Rene', 'Alsace'),\n",
       " (1, 'Bohn', 'Rene', 'Alsace'),\n",
       " (2, 'Boxler', 'Albert', 'Alsace'),\n",
       " (2, 'Boxler', 'Albert', 'Alsace'),\n",
       " (2, 'Boxler', 'Albert', 'Alsace'),\n",
       " (2, 'Boxler', 'Albert', 'Alsace'),\n",
       " (3, 'Six', 'Paul', 'Alsace'),\n",
       " (4, 'Stentz', 'Fernand', 'Alsace'),\n",
       " (4, 'Stentz', 'Fernand', 'Alsace'),\n",
       " (5, 'Joudeat', 'Lucien', 'Bourgogne'),\n",
       " (5, 'Joudeat', 'Lucien', 'Bourgogne'),\n",
       " (5, 'Joudeat', 'Lucien', 'Bourgogne'),\n",
       " (5, 'Joudeat', 'Lucien', 'Bourgogne'),\n",
       " (5, 'Joudeat', 'Lucien', 'Bourgogne'),\n",
       " (6, 'Marmagne', 'Bernard', 'Bourgogne'),\n",
       " (7, 'Lasnier', 'Pierre', 'Bourgogne'),\n",
       " (7, 'Lasnier', 'Pierre', 'Bourgogne'),\n",
       " (8, \"Lioger d'Harduy\", 'Gabriel', 'Bourgogne'),\n",
       " (9, 'Moniot', 'Gilbert', 'Bourgogne'),\n",
       " (9, 'Moniot', 'Gilbert', 'Bourgogne'),\n",
       " (10, 'Fournier', 'Jean Claude', 'Bourgogne'),\n",
       " (10, 'Fournier', 'Jean Claude', 'Bourgogne'),\n",
       " (10, 'Fournier', 'Jean Claude', 'Bourgogne'),\n",
       " (11, 'Clerget', 'Anne', 'Bourgogne'),\n",
       " (11, 'Clerget', 'Anne', 'Bourgogne'),\n",
       " (12, 'Tortochot', 'Gabriel', 'Bourgogne'),\n",
       " (12, 'Tortochot', 'Gabriel', 'Bourgogne'),\n",
       " (12, 'Tortochot', 'Gabriel', 'Bourgogne'),\n",
       " (13, 'Grivot', 'Jean', 'Bourgogne'),\n",
       " (13, 'Grivot', 'Jean', 'Bourgogne'),\n",
       " (14, 'Jayer', 'Henri', 'Bourgogne'),\n",
       " (14, 'Jayer', 'Henri', 'Bourgogne'),\n",
       " (15, 'Drouhin', 'Joseph', 'Bourgogne'),\n",
       " (15, 'Drouhin', 'Joseph', 'Bourgogne'),\n",
       " (16, 'Barbin', 'Bernard', 'Bourgogne'),\n",
       " (17, 'Faiveley', 'Guy', 'Bourgogne'),\n",
       " (18, 'Tramier', 'Jean', 'Bourgogne'),\n",
       " (19, 'Dupaquier', 'Roger', 'Bourgogne'),\n",
       " (20, 'Lamy', 'Jean', 'Bourgogne'),\n",
       " (21, 'Cornu', 'Edmond', 'Bourgogne'),\n",
       " (22, 'Dubreuil Fontaine', 'Pierre', 'Bourgogne'),\n",
       " (23, 'Laleure', None, 'Bourgogne'),\n",
       " (24, 'Guillemot', 'Pierre', 'Bourgogne'),\n",
       " (24, 'Guillemot', 'Pierre', 'Bourgogne'),\n",
       " (25, 'Valentin Bouchotte', 'Roger', 'Bourgogne'),\n",
       " (25, 'Valentin Bouchotte', 'Roger', 'Bourgogne'),\n",
       " (26, 'Violot', 'Gilbert', 'Bourgogne'),\n",
       " (27, 'Glantenay', 'Louis', 'Bourgogne'),\n",
       " (27, 'Glantenay', 'Louis', 'Bourgogne'),\n",
       " (28, 'Monnier', 'Rene', 'Bourgogne'),\n",
       " (29, 'de Suremain', 'Robert', 'Bourgogne'),\n",
       " (30, 'Desbois', 'Maurice', 'Bourgogne'),\n",
       " (30, 'Desbois', 'Maurice', 'Bourgogne'),\n",
       " (31, 'Prudhon', 'Henri', 'Bourgogne'),\n",
       " (32, 'Mestre', 'Philippe', 'Bourgogne'),\n",
       " (33, 'Prieur', 'Guy', 'Bourgogne'),\n",
       " (34, 'Monnier', 'Rene', 'Bourgogne'),\n",
       " (35, 'Roulot', 'Guy', 'Bourgogne'),\n",
       " (36, 'Sauzet', 'Etienne', 'Bourgogne'),\n",
       " (37, 'Delagrange Bachelet', None, 'Bourgogne'),\n",
       " (38, 'de Launay', 'Paul', 'Bourgogne'),\n",
       " (38, 'de Launay', 'Paul', 'Bourgogne'),\n",
       " (38, 'de Launay', 'Paul', 'Bourgogne'),\n",
       " (38, 'de Launay', 'Paul', 'Bourgogne'),\n",
       " (38, 'de Launay', 'Paul', 'Bourgogne'),\n",
       " (38, 'de Launay', 'Paul', 'Bourgogne'),\n",
       " (39, 'de Suremin', 'Hugues', 'Bourgogne'),\n",
       " (40, 'Genelot', 'Maurice', 'Bourgogne'),\n",
       " (40, 'Genelot', 'Maurice', 'Bourgogne'),\n",
       " (40, 'Genelot', 'Maurice', 'Bourgogne'),\n",
       " (40, 'Genelot', 'Maurice', 'Bourgogne'),\n",
       " (41, 'Juillot', 'Michel', 'Bourgogne'),\n",
       " (42, 'Juillot', 'Maurice', 'Bourgogne'),\n",
       " (42, 'Juillot', 'Maurice', 'Bourgogne'),\n",
       " (43, 'Menand', 'Jean Claude', 'Bourgogne'),\n",
       " (43, 'Menand', 'Jean Claude', 'Bourgogne'),\n",
       " (43, 'Menand', 'Jean Claude', 'Bourgogne'),\n",
       " (44, 'Feyveley', None, 'Bourgogne'),\n",
       " (45, 'Jeannin', 'Andre', 'Bourgogne'),\n",
       " (45, 'Jeannin', 'Andre', 'Bourgogne'),\n",
       " (45, 'Jeannin', 'Andre', 'Bourgogne'),\n",
       " (46, 'Monassier', 'Armand', 'Bourgogne'),\n",
       " (47, 'Thenard', None, 'Bourgogne'),\n",
       " (48, 'Flamand', None, 'Bourgogne'),\n",
       " (49, 'Guillot', 'Pierre', 'Bourgogne'),\n",
       " (50, 'Chagny', 'Georges', 'Bourgogne'),\n",
       " (50, 'Chagny', 'Georges', 'Bourgogne'),\n",
       " (50, 'Chagny', 'Georges', 'Bourgogne'),\n",
       " (50, 'Chagny', 'Georges', 'Bourgogne'),\n",
       " (50, 'Chagny', 'Georges', 'Bourgogne'),\n",
       " (51, 'Simonet', 'Leon', 'Bourgogne'),\n",
       " (52, 'Lapierre', 'Jean', 'Bourgogne'),\n",
       " (52, 'Lapierre', 'Jean', 'Bourgogne'),\n",
       " (52, 'Lapierre', 'Jean', 'Bourgogne'),\n",
       " (52, 'Lapierre', 'Jean', 'Bourgogne'),\n",
       " (53, 'Cooperative', None, 'Bourgogne'),\n",
       " (54, 'Cooperative', None, 'Bourgogne'),\n",
       " (55, 'Michon', None, 'Beaujolais'),\n",
       " (55, 'Michon', None, 'Beaujolais'),\n",
       " (55, 'Michon', None, 'Beaujolais'),\n",
       " (56, 'Coutelle', 'Jean', 'Beaujolais'),\n",
       " (57, 'Desmules', 'Pierre', 'Beaujolais'),\n",
       " (58, 'Joubert', 'Claude', 'Beaujolais'),\n",
       " (59, 'La Thuilliere', 'Jean', 'Beaujolais'),\n",
       " (60, 'Vernus', 'Georges', 'Beaujolais'),\n",
       " (60, 'Vernus', 'Georges', 'Beaujolais'),\n",
       " (60, 'Vernus', 'Georges', 'Beaujolais'),\n",
       " (61, 'Champier', 'Charles', 'Beaujolais'),\n",
       " (62, 'Champagnon', 'Louis', 'Beaujolais'),\n",
       " (64, 'Marius Bel Air', 'Morin', 'Beaujolais'),\n",
       " (66, 'Cooperative', None, 'Beaujolais'),\n",
       " (67, 'Descombes', 'Jean Ernest', 'Beaujolais'),\n",
       " (68, 'Savoye', 'Louis', 'Beaujolais'),\n",
       " (69, 'Janin', 'Marcel', 'Beaujolais'),\n",
       " (70, 'Trichard', 'Benoit', 'Beaujolais'),\n",
       " (70, 'Trichard', 'Benoit', 'Beaujolais'),\n",
       " (70, 'Trichard', 'Benoit', 'Beaujolais'),\n",
       " (71, 'Chalandard', 'Danile', 'Jura'),\n",
       " (72, 'Reverchon', 'Joseph', 'Jura'),\n",
       " (72, 'Reverchon', 'Joseph', 'Jura'),\n",
       " (72, 'Reverchon', 'Joseph', 'Jura'),\n",
       " (72, 'Reverchon', 'Joseph', 'Jura'),\n",
       " (72, 'Reverchon', 'Joseph', 'Jura'),\n",
       " (73, 'Bourguignon', 'Emile', 'Jura'),\n",
       " (74, 'Grand', 'Rene', 'Jura'),\n",
       " (75, 'Loye', 'Camille', 'Jura'),\n",
       " (76, 'Maire', 'Henri', 'Jura'),\n",
       " (76, 'Maire', 'Henri', 'Jura'),\n",
       " (77, 'Tissot', 'Andre', 'Jura'),\n",
       " (78, 'Michlel', 'Pierre Emile', 'Jura'),\n",
       " (79, 'Petit', 'Gerard', 'Jura'),\n",
       " (79, 'Petit', 'Gerard', 'Jura'),\n",
       " (79, 'Petit', 'Gerard', 'Jura'),\n",
       " (80, 'Devaux', 'Jean Paul', 'Jura'),\n",
       " (80, 'Devaux', 'Jean Paul', 'Jura'),\n",
       " (80, 'Devaux', 'Jean Paul', 'Jura'),\n",
       " (81, 'Peltier', 'Pierre', 'Jura'),\n",
       " (82, 'Mollex', 'Georges', 'Savoie'),\n",
       " (82, 'Mollex', 'Georges', 'Savoie'),\n",
       " (83, 'Goy', None, 'Jura'),\n",
       " (84, 'Metral', 'Jean', 'Jura'),\n",
       " (85, 'Champet', 'Emile', 'Rhone'),\n",
       " (85, 'Champet', 'Emile', 'Rhone'),\n",
       " (85, 'Champet', 'Emile', 'Rhone'),\n",
       " (85, 'Champet', 'Emile', 'Rhone'),\n",
       " (86, 'Dumazet', 'Marc', 'Rhone'),\n",
       " (86, 'Dumazet', 'Marc', 'Rhone'),\n",
       " (86, 'Dumazet', 'Marc', 'Rhone'),\n",
       " (86, 'Dumazet', 'Marc', 'Rhone'),\n",
       " (88, 'Begot', 'Albert', 'Rhone'),\n",
       " (89, 'Michelas', 'Robert', 'Rhone'),\n",
       " (89, 'Michelas', 'Robert', 'Rhone'),\n",
       " (90, 'Roure', 'Raymond', 'Rhone'),\n",
       " (90, 'Roure', 'Raymond', 'Rhone'),\n",
       " (90, 'Roure', 'Raymond', 'Rhone'),\n",
       " (90, 'Roure', 'Raymond', 'Rhone'),\n",
       " (91, 'Chave', 'Gerard', 'Rhone'),\n",
       " (92, 'De Boissieu', None, 'Rhone'),\n",
       " (93, 'Marsanne', 'Jean', 'Rhone'),\n",
       " (93, 'Marsanne', 'Jean', 'Rhone'),\n",
       " (93, 'Marsanne', 'Jean', 'Rhone'),\n",
       " (93, 'Marsanne', 'Jean', 'Rhone'),\n",
       " (94, 'Grippat', 'Jean Louis', 'Rhone'),\n",
       " (95, 'Verset', 'Louis', 'Rhone'),\n",
       " (95, 'Verset', 'Louis', 'Rhone'),\n",
       " (95, 'Verset', 'Louis', 'Rhone'),\n",
       " (95, 'Verset', 'Louis', 'Rhone'),\n",
       " (95, 'Verset', 'Louis', 'Rhone'),\n",
       " (95, 'Verset', 'Louis', 'Rhone'),\n",
       " (96, 'Boisson', 'Jean Pierre', 'Rhone'),\n",
       " (97, 'Quiot', 'Pierre', 'Rhone'),\n",
       " (98, 'Berard', None, 'Rhone'),\n",
       " (98, 'Berard', None, 'Rhone'),\n",
       " (99, 'Cooperative', None, 'Rhone'),\n",
       " (99, 'Cooperative', None, 'Rhone'),\n",
       " (100, 'Bernard', 'Georges', 'Rhone'),\n",
       " (101, 'Verdan', 'Antoine', 'Rhone'),\n",
       " (102, 'Cooperative', None, 'Rhone'),\n",
       " (103, 'Coulouvrat', 'Michel', 'Rhone'),\n",
       " (104, 'Rabasse Chavarin', 'Abel', 'Rhone'),\n",
       " (105, 'Roux', 'Charles', 'Rhone'),\n",
       " (106, 'Alary', None, 'Rhone'),\n",
       " (107, 'Meunier', 'Laurent', 'Rhone'),\n",
       " (108, 'Archimbaud', 'Jean', 'Rhone'),\n",
       " (109, 'Rey', 'Marcel', 'Rhone'),\n",
       " (110, 'Cooperative', None, 'Rhone'),\n",
       " (111, 'Zafiropulo', 'Costa', 'Provence'),\n",
       " (112, 'Prebost', 'Gaston', 'Provence'),\n",
       " (113, 'Gomez', 'Rene', 'Provence'),\n",
       " (114, 'Brunet', 'Georges', 'Provence'),\n",
       " (115, 'Bordonado', 'Raymond', 'Provence'),\n",
       " (116, 'Imbert', 'Christian', 'Corse'),\n",
       " (117, 'Cooperative', None, 'Languedoc'),\n",
       " (118, 'Cooperative', None, 'Languedoc'),\n",
       " (119, 'Cooperative', None, 'Pyrenees'),\n",
       " (121, 'Cooperative', None, 'Pyrenees'),\n",
       " (122, 'Dupuy', 'Charles', 'Pyrenees'),\n",
       " (123, 'Laurent', 'Pierre', 'Pyrenees'),\n",
       " (124, 'Daurat Fort', 'Robert', 'Pyrenees'),\n",
       " (125, 'Baldes', 'Jean', 'Sud'),\n",
       " (126, 'Jouffreau', 'Jean', 'Sud'),\n",
       " (127, 'Brumont', 'Alban', 'Sud'),\n",
       " (129, 'Barrere', 'Alfred', 'Sud')]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT np, nom, prenom, region FROM Master1 ORDER BY np ASC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can conclude that in the table PRODUCTEUR, the attribute 'NP' is key => 3NF\n",
    "\n",
    "Finally we have 3 tables :\n",
    "1. WINE['NV', 'CRU', 'DEGRE', 'MILL'] with'NV' as a key\n",
    "2. PRODUCTION ['NV', 'NP', 'QTE'] with'NV','NP' as a key\n",
    "3. PRODUCTEUR['NP', 'NOM', 'PRENOM', 'REGION'] with'NP' as a key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.2\n",
    "\n",
    "Convert table **Master2** to the Second Normal Form (2NF) or Third Normal Form (3NF) as applicable.\n",
    "* Explain your answer\n",
    "* List main functional dependencies (not all of them)\n",
    "* Describe the schema of new tables and how they relate\n",
    "  * You can write Tables as above or you can insert images in the notebook.\n",
    "\n",
    "**Note:** For this part, consider that a wine can be bought in multiple locations and multiple times per day."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "To convert table Master2 to a Third Normal Form (3NF), we can split the table between 4 tables ( Wine, Customers, Sales, and Location) in order to remove repeating groups, remove partial dependencies, and remove transitive dependencies we have:\n",
    "1. WINE['NV', 'CRU', 'DEGRE', 'MILL']\n",
    "2. CUSTOMERS['NB', 'NOM', 'PRENOM', 'TYPE']\n",
    "3. VENTES['NV', 'NB', 'DATES', 'LIEU', 'QTE']\n",
    "4. LOCATION['LIEU', 'REGION']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-10-0486dfa50116>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-10-0486dfa50116>\"\u001b[0;36m, line \u001b[0;32m1\u001b[0m\n\u001b[0;31m    First, lets have a look to the first table, WINE :\u001b[0m\n\u001b[0m                   ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "First, lets have a look to the first table, WINE :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql SELECT nv, cru, degre, mill \n",
    "FROM Master2 \n",
    "WHERE nv != 'None'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can conclude that in the table WINE, the attribute 'NV' is key => 3NF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, lets have a look to the first table, CUSTOMER :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql SELECT nb, nom, prenom, type \n",
    "FROM Master2 \n",
    "WHERE nb != \"None\" \n",
    "ORDER BY nb\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can conclude that in the table CUSTOMER, the attribute 'NB' is key => 3NF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, lets have a look to the first table, VENTES :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "SELECT nv, nb, dates, lieu, qte \n",
    "FROM Master2\n",
    "WHERE nb != 'None' and nv != 'None' and dates != 'None' and lieu != 'None'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can conclude that in the table VENTES, the attribute {'NV','NB', 'DATE', 'LIEU'} is key => 3NF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, lets have a look to the fourth table, LOCATION :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "SELECT lieu, region\n",
    "FROM Master2\n",
    "WHERE lieu != 'None'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can conclude that in the table LOCATION, the attribute 'LIEU' is key => 3NF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally we have 4 tables :\n",
    "1. WINE['NV', 'CRU', 'DEGRE', 'MILL'] with 'NV' as a key\n",
    "2. CUSTOMERS['NB', 'NOM', 'PRENOM', 'TYPE'] with 'NB' as a key\n",
    "3. VENTES['NV', 'NB', 'DATES', 'LIEU', 'QTE'] with {'NV','NB','DATES','LIEU'} as a key\n",
    "4. LOCATION['LIEU', 'REGION'] with 'LIEU' as a key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have defined the 2NF or 3NF (as applicable) we need to split the data into new tables.\n",
    "\n",
    "A table can be created from the result of a query.\n",
    "\n",
    "In the following example we will create a new table \"dummy\" to store the different values of alcohol content."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql DROP TABLE IF EXISTS dummy;\n",
    "\n",
    "-- Create dummy table\n",
    "CREATE TABLE dummy AS\n",
    "SELECT DISTINCT DEGRE\n",
    "FROM MASTER1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\nContent of the database\")\n",
    "printSchema(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM dummy;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking into \"dummy\", we notice that our query includes NULL. This is not allowed if we were to use DEGRE as key for a table.\n",
    "\n",
    "To correct this, we need to change the query to explicitly test for NULL as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql DROP TABLE IF EXISTS dummy;\n",
    "\n",
    "-- Create dummy table\n",
    "CREATE TABLE dummy AS\n",
    "SELECT DISTINCT DEGRE\n",
    "FROM MASTER1\n",
    "WHERE DEGRE IS NOT NULL;\n",
    "\n",
    "SELECT *\n",
    "FROM dummy;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we use `NULL` given that `None` is only used for display."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove \"dummy\" table\n",
    "%sql DROP TABLE IF EXISTS dummy;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.3\n",
    "\n",
    "Create the new tables from Master1:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "DROP TABLE IF EXISTS wine;\n",
    "DROP TABLE IF EXISTS production;\n",
    "DROP TABLE IF EXISTS producteur;\n",
    "\n",
    "-- Create wine table\n",
    "CREATE TABLE wine AS\n",
    "SELECT DISTINCT NV,CRU,DEGRE,MILL\n",
    "FROM MASTER1\n",
    "WHERE NV IS NOT NULL;\n",
    "\n",
    "-- Create production table\n",
    "CREATE TABLE production AS\n",
    "SELECT DISTINCT NV,NP,QTE\n",
    "FROM MASTER1\n",
    "WHERE NP IS NOT NULL AND NV IS NOT NULL;\n",
    "\n",
    "-- Create producteur table\n",
    "CREATE TABLE producteur AS\n",
    "SELECT DISTINCT NP,NOM,PRENOM,REGION\n",
    "FROM MASTER1\n",
    "WHERE NP IS NOT NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 1.4\n",
    "\n",
    "Create the new tables from Master2:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "DROP TABLE IF EXISTS customer;\n",
    "DROP TABLE IF EXISTS ventes;\n",
    "DROP TABLE IF EXISTS location;\n",
    "\n",
    "-- Create customers table\n",
    "CREATE TABLE customer AS\n",
    "SELECT DISTINCT NB,NOM,PRENOM,TYPE\n",
    "FROM MASTER2\n",
    "WHERE NB IS NOT NULL;\n",
    "\n",
    "-- Create ventes table\n",
    "CREATE TABLE ventes AS\n",
    "SELECT DISTINCT NV,NB,DATES,LIEU,QTE\n",
    "FROM MASTER2\n",
    "WHERE NV IS NOT NULL AND NB IS NOT NULL AND DATES IS NOT NULL AND LIEU IS NOT NULL;\n",
    "\n",
    "-- Create location table\n",
    "CREATE TABLE location AS\n",
    "SELECT DISTINCT LIEU,REGION\n",
    "FROM MASTER2\n",
    "WHERE LIEU IS NOT NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\nContent of the database\")\n",
    "printSchema(conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PART II: SQL QUERIES\n",
    "\n",
    "In the second part of this TP you will create SQL queries to retrieve information from the database.\n",
    "\n",
    "**Important:**\n",
    "\n",
    "- You MUST use the normalized tables created in previous steps.\n",
    "  - The normalized tables will also be used in TP3.\n",
    "- Do NOT use **Master1** and **Master2** in your queries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.1\n",
    "\n",
    "What are the different types of clients (buveurs) by volume of purchases?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>TYPE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>petit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>moyen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>gros</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('petit',), ('moyen',), ('gros',)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT type\n",
    "FROM customer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.2\n",
    "\n",
    "What regions produce Pommard or Brouilly?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>REGION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bourgogne',), ('Rhone',)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT region \n",
    "FROM wine\n",
    "INNER JOIN production ON wine.NV = production.NV\n",
    "INNER JOIN producteur p ON p.NP = production.NP\n",
    "WHERE wine.CRU = 'Pommard' OR wine.CRU = 'Brouilly'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.3\n",
    "\n",
    "What regions produce Pommard and Brouilly?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>REGION</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT REGION\n",
    "FROM wine w\n",
    "INNER JOIN production pn ON w.NV = pn.NV\n",
    "INNER JOIN producteur pr ON pr.NP = pn.NP\n",
    "WHERE w.CRU = 'Pommard' AND w.CRU = 'Brouilly'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.4\n",
    "\n",
    "Get the number of wines bought by CRU and Millésime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CRU</th>\n",
       "        <th>MILL</th>\n",
       "        <th>count(cru)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Arbois</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Auxey Duresses</td>\n",
       "        <td>1914</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beaujolais Primeur</td>\n",
       "        <td>1983</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>1975</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>1976</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>1978</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>1979</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chapelle Chambertin</td>\n",
       "        <td>1973</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chateau Corton Grancey</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chenas</td>\n",
       "        <td>1984</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clairette de Die</td>\n",
       "        <td>1976</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cornas</td>\n",
       "        <td>1980</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cote de Brouilly</td>\n",
       "        <td>1980</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Coteaux du Tricastin</td>\n",
       "        <td>1974</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Coteaux du Tricastin</td>\n",
       "        <td>1978</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cotes de Beaune Villages</td>\n",
       "        <td>1971</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cotes de Beaune Villages</td>\n",
       "        <td>1975</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cotes de Nuits</td>\n",
       "        <td>1978</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Etoile</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fleurie</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gentil</td>\n",
       "        <td>1975</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gigondas</td>\n",
       "        <td>1978</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grands Echezeaux</td>\n",
       "        <td>1968</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Julienas</td>\n",
       "        <td>1974</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mercurey</td>\n",
       "        <td>1980</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mercurey</td>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Meursault</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monthelie</td>\n",
       "        <td>1968</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pommard</td>\n",
       "        <td>1972</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pommard</td>\n",
       "        <td>1976</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pouilly Vinzelles</td>\n",
       "        <td>1968</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rasteau</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richebourg</td>\n",
       "        <td>1943</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Amour</td>\n",
       "        <td>1974</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Amour</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>1974</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>1977</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Savigny les Beaunes</td>\n",
       "        <td>1978</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Seyssel</td>\n",
       "        <td>1974</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Seyssel</td>\n",
       "        <td>1980</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tavel</td>\n",
       "        <td>1980</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Volnay</td>\n",
       "        <td>1980</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Arbois', 1980, 1),\n",
       " ('Auxey Duresses', 1914, 1),\n",
       " ('Beaujolais Primeur', 1983, 2),\n",
       " ('Beaujolais Villages', 1975, 1),\n",
       " ('Beaujolais Villages', 1976, 1),\n",
       " ('Beaujolais Villages', 1978, 1),\n",
       " ('Beaujolais Villages', 1979, 3),\n",
       " ('Chapelle Chambertin', 1973, 3),\n",
       " ('Chateau Corton Grancey', 1980, 1),\n",
       " ('Chenas', 1984, 1),\n",
       " ('Clairette de Die', 1976, 2),\n",
       " ('Cornas', 1980, 2),\n",
       " ('Cote de Brouilly', 1980, 2),\n",
       " ('Coteaux du Tricastin', 1974, 1),\n",
       " ('Coteaux du Tricastin', 1978, 1),\n",
       " ('Cotes de Beaune Villages', 1971, 1),\n",
       " ('Cotes de Beaune Villages', 1975, 3),\n",
       " ('Cotes de Nuits', 1978, 1),\n",
       " ('Cotes de Provence', 1980, 1),\n",
       " ('Etoile', 1980, 1),\n",
       " ('Fleurie', 1980, 1),\n",
       " ('Gentil', 1975, 1),\n",
       " ('Gigondas', 1978, 1),\n",
       " ('Grands Echezeaux', 1968, 2),\n",
       " ('Julienas', 1974, 1),\n",
       " ('Mercurey', 1980, 6),\n",
       " ('Mercurey', 1981, 1),\n",
       " ('Meursault', 1980, 1),\n",
       " ('Monthelie', 1968, 2),\n",
       " ('Pommard', 1972, 1),\n",
       " ('Pommard', 1976, 2),\n",
       " ('Pouilly Vinzelles', 1968, 2),\n",
       " ('Rasteau', 1980, 1),\n",
       " ('Richebourg', 1943, 5),\n",
       " ('Saint Amour', 1974, 1),\n",
       " ('Saint Amour', 1980, 1),\n",
       " ('Savigny les Beaunes', 1974, 2),\n",
       " ('Savigny les Beaunes', 1977, 2),\n",
       " ('Savigny les Beaunes', 1978, 2),\n",
       " ('Seyssel', 1974, 3),\n",
       " ('Seyssel', 1980, 2),\n",
       " ('Tavel', 1980, 2),\n",
       " ('Volnay', 1980, 1)]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT cru, mill, count(cru)\n",
    "FROM wine w\n",
    "INNER JOIN ventes v ON w.NV = v.NV\n",
    "Group by cru, mill\n",
    "ORDER BY cru"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.5\n",
    "\n",
    "Retrieve the wine number (NV) of wines produced by more than three producers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NV</th>\n",
       "        <th>nbr_of_producer</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12, 3),\n",
       " (43, 3),\n",
       " (45, 5),\n",
       " (54, 3),\n",
       " (67, 3),\n",
       " (69, 3),\n",
       " (74, 3),\n",
       " (78, 5),\n",
       " (79, 3),\n",
       " (87, 3),\n",
       " (89, 4),\n",
       " (98, 5)]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT w.NV, count(pr.NP) nbr_of_producer\n",
    "FROM wine w\n",
    "INNER JOIN production pn ON w.NV = pn.NV\n",
    "INNER JOIN producteur pr ON pn.NP = pr.NP\n",
    "GROUP BY w.NV\n",
    "Having nbr_of_producer >=3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.6\n",
    "\n",
    "Which producers have not produced any wine?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NP</th>\n",
       "        <th>NOM</th>\n",
       "        <th>PRENOM</th>\n",
       "        <th>REGION</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Six</td>\n",
       "        <td>Paul</td>\n",
       "        <td>Alsace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Marmagne</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Lioger d&#x27;Harduy</td>\n",
       "        <td>Gabriel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Barbin</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Faiveley</td>\n",
       "        <td>Guy</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Tramier</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Dupaquier</td>\n",
       "        <td>Roger</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Lamy</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Cornu</td>\n",
       "        <td>Edmond</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Violot</td>\n",
       "        <td>Gilbert</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>Monnier</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>de Suremain</td>\n",
       "        <td>Robert</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Prudhon</td>\n",
       "        <td>Henri</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Mestre</td>\n",
       "        <td>Philippe</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>Prieur</td>\n",
       "        <td>Guy</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>Monnier</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>Delagrange Bachelet</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>de Suremin</td>\n",
       "        <td>Hugues</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>Juillot</td>\n",
       "        <td>Michel</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Feyveley</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>Thenard</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Flamand</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>Guillot</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>Simonet</td>\n",
       "        <td>Leon</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Bourgogne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>Desmules</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Joubert</td>\n",
       "        <td>Claude</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>Champier</td>\n",
       "        <td>Charles</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Champagnon</td>\n",
       "        <td>Louis</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Beaujolais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>Tissot</td>\n",
       "        <td>Andre</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Goy</td>\n",
       "        <td>None</td>\n",
       "        <td>Jura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>Chave</td>\n",
       "        <td>Gerard</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>De Boissieu</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>Grippat</td>\n",
       "        <td>Jean Louis</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "        <td>Boisson</td>\n",
       "        <td>Jean Pierre</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>Quiot</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Bernard</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Verdan</td>\n",
       "        <td>Antoine</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>102</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>Coulouvrat</td>\n",
       "        <td>Michel</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>104</td>\n",
       "        <td>Rabasse Chavarin</td>\n",
       "        <td>Abel</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>105</td>\n",
       "        <td>Roux</td>\n",
       "        <td>Charles</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>106</td>\n",
       "        <td>Alary</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>108</td>\n",
       "        <td>Archimbaud</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>109</td>\n",
       "        <td>Rey</td>\n",
       "        <td>Marcel</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>110</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Rhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>111</td>\n",
       "        <td>Zafiropulo</td>\n",
       "        <td>Costa</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>112</td>\n",
       "        <td>Prebost</td>\n",
       "        <td>Gaston</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>113</td>\n",
       "        <td>Gomez</td>\n",
       "        <td>Rene</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>114</td>\n",
       "        <td>Brunet</td>\n",
       "        <td>Georges</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>115</td>\n",
       "        <td>Bordonado</td>\n",
       "        <td>Raymond</td>\n",
       "        <td>Provence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>116</td>\n",
       "        <td>Imbert</td>\n",
       "        <td>Christian</td>\n",
       "        <td>Corse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>117</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Languedoc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>118</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Languedoc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>119</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>121</td>\n",
       "        <td>Cooperative</td>\n",
       "        <td>None</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>122</td>\n",
       "        <td>Dupuy</td>\n",
       "        <td>Charles</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>123</td>\n",
       "        <td>Laurent</td>\n",
       "        <td>Pierre</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>124</td>\n",
       "        <td>Daurat Fort</td>\n",
       "        <td>Robert</td>\n",
       "        <td>Pyrenees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>125</td>\n",
       "        <td>Baldes</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>126</td>\n",
       "        <td>Jouffreau</td>\n",
       "        <td>Jean</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>127</td>\n",
       "        <td>Brumont</td>\n",
       "        <td>Alban</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>129</td>\n",
       "        <td>Barrere</td>\n",
       "        <td>Alfred</td>\n",
       "        <td>Sud</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3, 'Six', 'Paul', 'Alsace'),\n",
       " (6, 'Marmagne', 'Bernard', 'Bourgogne'),\n",
       " (8, \"Lioger d'Harduy\", 'Gabriel', 'Bourgogne'),\n",
       " (16, 'Barbin', 'Bernard', 'Bourgogne'),\n",
       " (17, 'Faiveley', 'Guy', 'Bourgogne'),\n",
       " (18, 'Tramier', 'Jean', 'Bourgogne'),\n",
       " (19, 'Dupaquier', 'Roger', 'Bourgogne'),\n",
       " (20, 'Lamy', 'Jean', 'Bourgogne'),\n",
       " (21, 'Cornu', 'Edmond', 'Bourgogne'),\n",
       " (26, 'Violot', 'Gilbert', 'Bourgogne'),\n",
       " (28, 'Monnier', 'Rene', 'Bourgogne'),\n",
       " (29, 'de Suremain', 'Robert', 'Bourgogne'),\n",
       " (31, 'Prudhon', 'Henri', 'Bourgogne'),\n",
       " (32, 'Mestre', 'Philippe', 'Bourgogne'),\n",
       " (33, 'Prieur', 'Guy', 'Bourgogne'),\n",
       " (34, 'Monnier', 'Rene', 'Bourgogne'),\n",
       " (37, 'Delagrange Bachelet', None, 'Bourgogne'),\n",
       " (39, 'de Suremin', 'Hugues', 'Bourgogne'),\n",
       " (41, 'Juillot', 'Michel', 'Bourgogne'),\n",
       " (44, 'Feyveley', None, 'Bourgogne'),\n",
       " (47, 'Thenard', None, 'Bourgogne'),\n",
       " (48, 'Flamand', None, 'Bourgogne'),\n",
       " (49, 'Guillot', 'Pierre', 'Bourgogne'),\n",
       " (51, 'Simonet', 'Leon', 'Bourgogne'),\n",
       " (53, 'Cooperative', None, 'Bourgogne'),\n",
       " (57, 'Desmules', 'Pierre', 'Beaujolais'),\n",
       " (58, 'Joubert', 'Claude', 'Beaujolais'),\n",
       " (61, 'Champier', 'Charles', 'Beaujolais'),\n",
       " (62, 'Champagnon', 'Louis', 'Beaujolais'),\n",
       " (66, 'Cooperative', None, 'Beaujolais'),\n",
       " (77, 'Tissot', 'Andre', 'Jura'),\n",
       " (83, 'Goy', None, 'Jura'),\n",
       " (91, 'Chave', 'Gerard', 'Rhone'),\n",
       " (92, 'De Boissieu', None, 'Rhone'),\n",
       " (94, 'Grippat', 'Jean Louis', 'Rhone'),\n",
       " (96, 'Boisson', 'Jean Pierre', 'Rhone'),\n",
       " (97, 'Quiot', 'Pierre', 'Rhone'),\n",
       " (100, 'Bernard', 'Georges', 'Rhone'),\n",
       " (101, 'Verdan', 'Antoine', 'Rhone'),\n",
       " (102, 'Cooperative', None, 'Rhone'),\n",
       " (103, 'Coulouvrat', 'Michel', 'Rhone'),\n",
       " (104, 'Rabasse Chavarin', 'Abel', 'Rhone'),\n",
       " (105, 'Roux', 'Charles', 'Rhone'),\n",
       " (106, 'Alary', None, 'Rhone'),\n",
       " (108, 'Archimbaud', 'Jean', 'Rhone'),\n",
       " (109, 'Rey', 'Marcel', 'Rhone'),\n",
       " (110, 'Cooperative', None, 'Rhone'),\n",
       " (111, 'Zafiropulo', 'Costa', 'Provence'),\n",
       " (112, 'Prebost', 'Gaston', 'Provence'),\n",
       " (113, 'Gomez', 'Rene', 'Provence'),\n",
       " (114, 'Brunet', 'Georges', 'Provence'),\n",
       " (115, 'Bordonado', 'Raymond', 'Provence'),\n",
       " (116, 'Imbert', 'Christian', 'Corse'),\n",
       " (117, 'Cooperative', None, 'Languedoc'),\n",
       " (118, 'Cooperative', None, 'Languedoc'),\n",
       " (119, 'Cooperative', None, 'Pyrenees'),\n",
       " (121, 'Cooperative', None, 'Pyrenees'),\n",
       " (122, 'Dupuy', 'Charles', 'Pyrenees'),\n",
       " (123, 'Laurent', 'Pierre', 'Pyrenees'),\n",
       " (124, 'Daurat Fort', 'Robert', 'Pyrenees'),\n",
       " (125, 'Baldes', 'Jean', 'Sud'),\n",
       " (126, 'Jouffreau', 'Jean', 'Sud'),\n",
       " (127, 'Brumont', 'Alban', 'Sud'),\n",
       " (129, 'Barrere', 'Alfred', 'Sud')]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM producteur pr\n",
    "WHERE pr.NP NOT IN (\n",
    "    SELECT production.NP\n",
    "    FROM production)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.7\n",
    "\n",
    "What clients (buveurs) have bought at least one wine from 1980?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>PRENOM</th>\n",
       "        <th>NOM</th>\n",
       "        <th>MILL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Antonin</td>\n",
       "        <td>Artaud</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andre</td>\n",
       "        <td>Gide</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jean</td>\n",
       "        <td>Giono</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Louis</td>\n",
       "        <td>Aragon</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Lautreamont</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stephane</td>\n",
       "        <td>Mallarme</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Antonin', 'Artaud', 1980),\n",
       " ('Andre', 'Gide', 1980),\n",
       " ('Jean', 'Giono', 1980),\n",
       " ('Louis', 'Aragon', 1980),\n",
       " (None, 'Lautreamont', 1980),\n",
       " ('Stephane', 'Mallarme', 1980)]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT c.prenom, c.nom, mill\n",
    "FROM customer c\n",
    "INNER JOIN ventes v ON c.NB = v.NB\n",
    "INNER JOIN wine w ON v.NV = w.NV\n",
    "WHERE mill = 1980\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.8\n",
    "\n",
    "What clients (buveurs) have NOT bought any wine from 1980?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>PRENOM</th>\n",
       "        <th>NOM</th>\n",
       "        <th>nbr80</th>\n",
       "        <th>MILL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Emile</td>\n",
       "        <td>Ajar</td>\n",
       "        <td>0</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Yann</td>\n",
       "        <td>Andersen</td>\n",
       "        <td>0</td>\n",
       "        <td>1973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jean</td>\n",
       "        <td>Anouilh</td>\n",
       "        <td>0</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guillaume</td>\n",
       "        <td>Apollinaire</td>\n",
       "        <td>0</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Aristote</td>\n",
       "        <td>0</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Raymond</td>\n",
       "        <td>Aron</td>\n",
       "        <td>0</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fernando</td>\n",
       "        <td>Arrabal</td>\n",
       "        <td>0</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jacques</td>\n",
       "        <td>Audiberti</td>\n",
       "        <td>0</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Georges</td>\n",
       "        <td>Bataille</td>\n",
       "        <td>0</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Charles</td>\n",
       "        <td>Baudelaire</td>\n",
       "        <td>0</td>\n",
       "        <td>1943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jean Louis</td>\n",
       "        <td>Bory</td>\n",
       "        <td>0</td>\n",
       "        <td>1977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Albert</td>\n",
       "        <td>Camus</td>\n",
       "        <td>0</td>\n",
       "        <td>1943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lewis</td>\n",
       "        <td>Carroll</td>\n",
       "        <td>0</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Ciceron</td>\n",
       "        <td>0</td>\n",
       "        <td>1943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jean</td>\n",
       "        <td>Cocteau</td>\n",
       "        <td>0</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paul Louis</td>\n",
       "        <td>Courrier</td>\n",
       "        <td>0</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Dante</td>\n",
       "        <td>0</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Denis</td>\n",
       "        <td>Diderot</td>\n",
       "        <td>0</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fiodor</td>\n",
       "        <td>Dostoievsky</td>\n",
       "        <td>0</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gustave</td>\n",
       "        <td>Flaubert</td>\n",
       "        <td>0</td>\n",
       "        <td>1943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Theophile</td>\n",
       "        <td>Gauthier</td>\n",
       "        <td>0</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thomas</td>\n",
       "        <td>Hardy</td>\n",
       "        <td>0</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Franz</td>\n",
       "        <td>Kafka</td>\n",
       "        <td>0</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jean de</td>\n",
       "        <td>La Fontaine</td>\n",
       "        <td>0</td>\n",
       "        <td>1914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pierre Carlet de Chamblain de</td>\n",
       "        <td>Marivaux</td>\n",
       "        <td>0</td>\n",
       "        <td>1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Moliere</td>\n",
       "        <td>0</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Michel de</td>\n",
       "        <td>Montaigne</td>\n",
       "        <td>0</td>\n",
       "        <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Ovide</td>\n",
       "        <td>0</td>\n",
       "        <td>1975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Marcel</td>\n",
       "        <td>Proust</td>\n",
       "        <td>0</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pierre</td>\n",
       "        <td>Ronsard</td>\n",
       "        <td>0</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rene</td>\n",
       "        <td>Roussel</td>\n",
       "        <td>0</td>\n",
       "        <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Georges</td>\n",
       "        <td>Sand</td>\n",
       "        <td>0</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jules</td>\n",
       "        <td>Verne</td>\n",
       "        <td>0</td>\n",
       "        <td>1943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alfred de</td>\n",
       "        <td>Vigny</td>\n",
       "        <td>0</td>\n",
       "        <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oscar</td>\n",
       "        <td>Wilde</td>\n",
       "        <td>0</td>\n",
       "        <td>1978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Virginia</td>\n",
       "        <td>Woolf</td>\n",
       "        <td>0</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Emile</td>\n",
       "        <td>Zola</td>\n",
       "        <td>0</td>\n",
       "        <td>1983</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Emile', 'Ajar', 0, 1978),\n",
       " ('Yann', 'Andersen', 0, 1973),\n",
       " ('Jean', 'Anouilh', 0, 1978),\n",
       " ('Guillaume', 'Apollinaire', 0, 1968),\n",
       " (None, 'Aristote', 0, 1975),\n",
       " ('Raymond', 'Aron', 0, 1978),\n",
       " ('Fernando', 'Arrabal', 0, 1975),\n",
       " ('Jacques', 'Audiberti', 0, 1974),\n",
       " ('Georges', 'Bataille', 0, 1975),\n",
       " ('Charles', 'Baudelaire', 0, 1943),\n",
       " ('Jean Louis', 'Bory', 0, 1977),\n",
       " ('Albert', 'Camus', 0, 1943),\n",
       " ('Lewis', 'Carroll', 0, 1976),\n",
       " (None, 'Ciceron', 0, 1943),\n",
       " ('Jean', 'Cocteau', 0, 1968),\n",
       " ('Paul Louis', 'Courrier', 0, 1968),\n",
       " (None, 'Dante', 0, 1976),\n",
       " ('Denis', 'Diderot', 0, 1968),\n",
       " ('Fiodor', 'Dostoievsky', 0, 1968),\n",
       " ('Gustave', 'Flaubert', 0, 1943),\n",
       " ('Theophile', 'Gauthier', 0, 1974),\n",
       " ('Thomas', 'Hardy', 0, 1974),\n",
       " ('Franz', 'Kafka', 0, 1978),\n",
       " ('Jean de', 'La Fontaine', 0, 1914),\n",
       " ('Pierre Carlet de Chamblain de', 'Marivaux', 0, 1968),\n",
       " (None, 'Moliere', 0, 1974),\n",
       " ('Michel de', 'Montaigne', 0, 1984),\n",
       " (None, 'Ovide', 0, 1975),\n",
       " ('Marcel', 'Proust', 0, 1978),\n",
       " ('Pierre', 'Ronsard', 0, 1974),\n",
       " ('Rene', 'Roussel', 0, 1972),\n",
       " ('Georges', 'Sand', 0, 1974),\n",
       " ('Jules', 'Verne', 0, 1943),\n",
       " ('Alfred de', 'Vigny', 0, 1974),\n",
       " ('Oscar', 'Wilde', 0, 1978),\n",
       " ('Virginia', 'Woolf', 0, 1983),\n",
       " ('Emile', 'Zola', 0, 1983)]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT c.prenom, c.nom, sum(CASE WHEN w.mill = 1980 THEN 1 ELSE 0 END) nbr80, mill\n",
    "FROM customer c\n",
    "INNER JOIN ventes v ON c.NB = v.NB\n",
    "INNER JOIN wine w ON v.NV = w.NV\n",
    "GROUP BY c.nom, c.prenom\n",
    "HAVING nbr80 = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.9\n",
    "\n",
    "What clients (buveurs) have bought ONLY wines from 1980?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>PRENOM</th>\n",
       "        <th>NOM</th>\n",
       "        <th>nbr80</th>\n",
       "        <th>nbr_nom</th>\n",
       "        <th>nbr_prenom</th>\n",
       "        <th>MILL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andre</td>\n",
       "        <td>Gide</td>\n",
       "        <td>18</td>\n",
       "        <td>18</td>\n",
       "        <td>18</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jean</td>\n",
       "        <td>Giono</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Lautreamont</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Andre', 'Gide', 18, 18, 18, 1980),\n",
       " ('Jean', 'Giono', 1, 1, 1, 1980),\n",
       " (None, 'Lautreamont', 1, 1, 0, 1980)]"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT c.prenom, c.nom, sum(CASE WHEN w.mill = 1980 THEN 1 ELSE 0 END) nbr80, COUNT(c.nom) nbr_nom, COUNT(c.prenom) nbr_prenom, mill\n",
    "FROM customer c\n",
    "INNER JOIN ventes v ON c.NB = v.NB\n",
    "INNER JOIN wine w ON v.NV = w.NV\n",
    "GROUP BY c.nom, c.prenom\n",
    "HAVING (nbr80 = nbr_nom OR nbr80 = nbr_prenom AND nbr80 != 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.10\n",
    "\n",
    "List all wines from 1980"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///wine.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>NV</th>\n",
       "        <th>CRU</th>\n",
       "        <th>DEGRE</th>\n",
       "        <th>MILL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>11.5</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>10.9</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Meursault</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Cote de Brouilly</td>\n",
       "        <td>12.1</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Chateau Corton Grancey</td>\n",
       "        <td>None</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>Volnay</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Fleurie</td>\n",
       "        <td>11.4</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Arbois</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Etoile</td>\n",
       "        <td>12</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Seyssel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Cornas</td>\n",
       "        <td>11.1</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Cornas</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>Saint Amour</td>\n",
       "        <td>10</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>Rasteau</td>\n",
       "        <td>10</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>Tavel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>Tavel</td>\n",
       "        <td>11</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>Cotes de Provence</td>\n",
       "        <td>None</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Mercurey', 11.5, 1980),\n",
       " (4, 'Mercurey', 10.9, 1980),\n",
       " (16, 'Meursault', 12.1, 1980),\n",
       " (20, 'Cote de Brouilly', 12.1, 1980),\n",
       " (26, 'Chateau Corton Grancey', None, 1980),\n",
       " (28, 'Volnay', 11, 1980),\n",
       " (43, 'Fleurie', 11.4, 1980),\n",
       " (74, 'Arbois', 12, 1980),\n",
       " (78, 'Etoile', 12, 1980),\n",
       " (79, 'Seyssel', 11, 1980),\n",
       " (80, 'Seyssel', 11, 1980),\n",
       " (81, 'Cornas', 11.1, 1980),\n",
       " (82, 'Cornas', 11, 1980),\n",
       " (83, 'Saint Amour', 10, 1980),\n",
       " (84, 'Rasteau', 10, 1980),\n",
       " (85, 'Tavel', 11, 1980),\n",
       " (86, 'Tavel', 11, 1980),\n",
       " (87, 'Cotes de Provence', None, 1980)]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT *\n",
    "FROM wine\n",
    "WHERE mill = 1980"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.11\n",
    "\n",
    "What are the wines from 1980 bought by NB=2?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "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>CRU</th>\n",
       "        <th>DEGRE</th>\n",
       "        <th>MILL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Mercurey</td>\n",
       "        <td>11.5</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Pommard</td>\n",
       "        <td>11.7</td>\n",
       "        <td>1976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Beaujolais Villages</td>\n",
       "        <td>11.8</td>\n",
       "        <td>1979</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Mercurey', 11.5, 1980),\n",
       " (2, 'Pommard', 11.7, 1976),\n",
       " (2, 'Beaujolais Villages', 11.8, 1979)]"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT c.NB, cru, degre, mill\n",
    "FROM wine w\n",
    "INNER JOIN ventes v ON w.NV = v.NV\n",
    "INNER JOIN customer c ON v.NB = c.NB\n",
    "Where c.NB = 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise 2.12\n",
    "\n",
    "What clients (buveurs) have bought ALL the wines from 1980?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "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>QTE</th>\n",
       "        <th>MILL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Gide</td>\n",
       "        <td>Andre</td>\n",
       "        <td>18</td>\n",
       "        <td>1980</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(44, 'Gide', 'Andre', 18, 1980)]"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT c.NB, c.NOM, c.PRENOM, QTE, MILL\n",
    "FROM customer c\n",
    "INNER JOIN ventes v ON c.NB = v.NB\n",
    "INNER JOIN wine w ON v.NV = w.NV\n",
    "WHERE w.MILL =1980\n",
    "GROUP BY c.NB\n",
    "HAVING COUNT(*) = (SELECT COUNT(DISTINCT w.NV) FROM wine w WHERE w.MILL=1980)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}