SQL/biblioTP02-exos.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pymysql.connect(\"localhost\", \"root\", \"1234\", \"biblioTP02\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Database</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>bibliotp02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>cinematp01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>empdepttp03avecfk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>empdepttp03sansfk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>employestp01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>information_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>mysql</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>performance_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>sakila</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>sys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Database\n",
       "0           bibliotp02\n",
       "1           cinematp01\n",
       "2    empdepttp03avecfk\n",
       "3    empdepttp03sansfk\n",
       "4         employestp01\n",
       "5   information_schema\n",
       "6                mysql\n",
       "7   performance_schema\n",
       "8               sakila\n",
       "9                  sys\n",
       "10               world"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Show databases;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tables_in_bibliotp02</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>adherents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>categories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>emprunter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>livres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>livres_complets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>oeuvres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>thematique</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tables_in_bibliotp02\n",
       "0            adherents\n",
       "1           categories\n",
       "2            emprunter\n",
       "3               livres\n",
       "4      livres_complets\n",
       "5              oeuvres\n",
       "6           thematique"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Show tables;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NA</th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "      <th>adr</th>\n",
       "      <th>tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Dupont</td>\n",
       "      <td>Yvan</td>\n",
       "      <td>73 rue Lamarck, 75018 Paris</td>\n",
       "      <td>0163538294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Mercier</td>\n",
       "      <td>Claude</td>\n",
       "      <td>155 avenue Parmentier, 75011 Paris</td>\n",
       "      <td>0136482736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Léger</td>\n",
       "      <td>Marc</td>\n",
       "      <td>90 avenue du Maine, 75014 Paris</td>\n",
       "      <td>0164832947</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NA      nom    prenom                                   adr         tel\n",
       "0   1  Lecoeur  Jeanette  16 rue de la République, 75010 Paris  0145279274\n",
       "1   2  Lecoeur  Philippe  16 rue de la République, 75010 Paris  0145279274\n",
       "2   3   Dupont      Yvan           73 rue Lamarck, 75018 Paris  0163538294\n",
       "3   4  Mercier    Claude    155 avenue Parmentier, 75011 Paris  0136482736\n",
       "4   5    Léger      Marc       90 avenue du Maine, 75014 Paris  0164832947"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM adherents;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NC</th>\n",
       "      <th>categorie</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Roman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Jeunesse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Theatre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Poesie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Mathematiques</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NC      categorie\n",
       "0   1          Roman\n",
       "1   2       Jeunesse\n",
       "2   3        Theatre\n",
       "3   4         Poesie\n",
       "4   5  Mathematiques"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM categories;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-03-08</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-09-20</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax     dateRet  NA\n",
       "0   1  2019-03-07        21  2019-03-08  26\n",
       "1   1  2019-09-20        14  2019-10-01   3\n",
       "2   1  2019-11-06        14  2019-11-11   2\n",
       "3   1  2020-01-23        14  2020-01-28   1\n",
       "4   1  2020-02-10        14        None  28"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM emprunter;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>editeur</th>\n",
       "      <th>NO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>GF</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>HACHETTE</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>GF</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL   editeur  NO\n",
       "0   1        GF   1\n",
       "1   2     FOLIO   2\n",
       "2   3  HACHETTE   3\n",
       "3   4        GF   4\n",
       "4   5     FOLIO   5"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM livres;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NO</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Berenice</td>\n",
       "      <td>Jean RACINE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Prolégomènes à  toute métaphysique future</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Voyage au bout de la nuit</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NO                                      titre                  auteur\n",
       "0   1                       Narcisse et Goldmund           Hermann HESSE\n",
       "1   2                                   Berenice             Jean RACINE\n",
       "2   3  Prolégomènes à  toute métaphysique future           Emmanuel KANT\n",
       "3   4                         Mon coeur mis à nu      Charles BAUDELAIRE\n",
       "4   5                  Voyage au bout de la nuit  Louis-Ferdinand CELINE"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM oeuvres;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NO</th>\n",
       "      <th>NC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NO  NC\n",
       "0   1   1\n",
       "1   5   1\n",
       "2   6   1\n",
       "3   7   1\n",
       "4   8   1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM thematique;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.\tFaites la jointure naturelle de la table des Livres avec la table des Oeuvres. \n",
    "\tOn projette nl, titre, auteur et editeur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nl</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "      <th>editeur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>GF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>FOLIO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>GF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>GF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Berenice</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>FOLIO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nl                 titre         auteur editeur\n",
       "0   1  Narcisse et Goldmund  Hermann HESSE      GF\n",
       "1  17  Narcisse et Goldmund  Hermann HESSE   FOLIO\n",
       "2  25  Narcisse et Goldmund  Hermann HESSE      GF\n",
       "3  29  Narcisse et Goldmund  Hermann HESSE      GF\n",
       "4   2              Berenice    Jean RACINE   FOLIO"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT nl, titre, auteur, editeur FROM   livres NATURAL JOIN oeuvres', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.\tFaites la jointure naturelle de la table Emprunter avec la table des Livres, la table des Oeuvres et la table des Adhérents. \n",
    "\tOn projettera tous les attributs utiles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NA</th>\n",
       "      <th>NL</th>\n",
       "      <th>NO</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "      <th>editeur</th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "      <th>adr</th>\n",
       "      <th>tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>38</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-07-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>Harry Potter, II : Harry Potter et la Chambre ...</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>Harry Potter, II : Harry Potter et la Chambre ...</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>31</td>\n",
       "      <td>38</td>\n",
       "      <td>24</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>Harry Potter, II : Harry Potter et la Chambre ...</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>Dos Santos</td>\n",
       "      <td>Olivia</td>\n",
       "      <td>58 rue Montmartre, 75009 Paris</td>\n",
       "      <td>0634353049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>31</td>\n",
       "      <td>39</td>\n",
       "      <td>25</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>Harry Potter, III : Harry Potter et le prisonn...</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>Dos Santos</td>\n",
       "      <td>Olivia</td>\n",
       "      <td>58 rue Montmartre, 75009 Paris</td>\n",
       "      <td>0634353049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>31</td>\n",
       "      <td>40</td>\n",
       "      <td>26</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>Harry Potter, IV : Harry Potter et la Coupe de...</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>Dos Santos</td>\n",
       "      <td>Olivia</td>\n",
       "      <td>58 rue Montmartre, 75009 Paris</td>\n",
       "      <td>0634353049</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    NA  NL  NO      datEmp  dureeMax     dateRet  \\\n",
       "40   2  38  24  2019-07-09        14  2019-08-04   \n",
       "41   1  38  24  2019-11-12        14  2019-11-22   \n",
       "42  31  38  24  2020-02-17        14        None   \n",
       "43  31  39  25  2020-02-17        14        None   \n",
       "44  31  40  26  2020-02-17        14        None   \n",
       "\n",
       "                                                titre  \\\n",
       "40  Harry Potter, II : Harry Potter et la Chambre ...   \n",
       "41  Harry Potter, II : Harry Potter et la Chambre ...   \n",
       "42  Harry Potter, II : Harry Potter et la Chambre ...   \n",
       "43  Harry Potter, III : Harry Potter et le prisonn...   \n",
       "44  Harry Potter, IV : Harry Potter et la Coupe de...   \n",
       "\n",
       "                     auteur editeur         nom    prenom  \\\n",
       "40  Joanne Kathleen ROWLING   FOLIO     Lecoeur  Philippe   \n",
       "41  Joanne Kathleen ROWLING   FOLIO     Lecoeur  Jeanette   \n",
       "42  Joanne Kathleen ROWLING   FOLIO  Dos Santos    Olivia   \n",
       "43  Joanne Kathleen ROWLING   FOLIO  Dos Santos    Olivia   \n",
       "44  Joanne Kathleen ROWLING   FOLIO  Dos Santos    Olivia   \n",
       "\n",
       "                                     adr         tel  \n",
       "40  16 rue de la République, 75010 Paris  0145279274  \n",
       "41  16 rue de la République, 75010 Paris  0145279274  \n",
       "42        58 rue Montmartre, 75009 Paris  0634353049  \n",
       "43        58 rue Montmartre, 75009 Paris  0634353049  \n",
       "44        58 rue Montmartre, 75009 Paris  0634353049  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM Emprunter NATURAL JOIN oeuvres NATURAL JOIN livres NATURAL JOIN adherents', db).tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "a•\tComptez manuellement le nombre de tuples dans la table résultat ? \n",
    "\n",
    "45"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "b•\tQuelle est la clé primaire ?\n",
    "\n",
    "    NL et dateEmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "c•\tTriez les résultats par date d'emprunt. Interprétez le résultat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-03-08</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-04-16</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-04-08</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-05-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax     dateRet  NA\n",
       "0  29  2019-01-21        14        None  27\n",
       "1   1  2019-03-07        21  2019-03-08  26\n",
       "2   4  2019-04-03        21  2019-04-16   4\n",
       "3  26  2019-04-11        21  2019-04-08   9\n",
       "4  25  2019-04-15        21  2019-05-03   1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM emprunter ORDER BY datEmp ASC;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "d•\tTriez les résultats par adhérents et date d''emprunt. Interprétez le résultat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NA</th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>Carre</td>\n",
       "      <td>Stéphane</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>Logique sans peine</td>\n",
       "      <td>Lewis CAROLL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>Carre</td>\n",
       "      <td>Stéphane</td>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>Voyage au bout de la nuit</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>Cornu</td>\n",
       "      <td>Sylvain</td>\n",
       "      <td>2019-01-21</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "      <td>Cornu</td>\n",
       "      <td>Sylvain</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>Crozier</td>\n",
       "      <td>Martine</td>\n",
       "      <td>2019-04-26</td>\n",
       "      <td>Logique sans peine</td>\n",
       "      <td>Lewis CAROLL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NA      nom    prenom      datEmp                      titre  \\\n",
       "0  16    Carre  Stéphane  2020-02-10         Logique sans peine   \n",
       "1  16    Carre  Stéphane  2020-02-15  Voyage au bout de la nuit   \n",
       "2  27    Cornu   Sylvain  2019-01-21       Narcisse et Goldmund   \n",
       "3  27    Cornu   Sylvain  2019-05-13         Mon coeur mis à nu   \n",
       "4   7  Crozier   Martine  2019-04-26         Logique sans peine   \n",
       "\n",
       "                   auteur  \n",
       "0            Lewis CAROLL  \n",
       "1  Louis-Ferdinand CELINE  \n",
       "2           Hermann HESSE  \n",
       "3      Charles BAUDELAIRE  \n",
       "4            Lewis CAROLL  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Select NA,nom,prenom , datEmp , titre, auteur from livres natural join emprunter natural join adherents natural join oeuvres order by nom ,datEmp;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "e•  Triez les résultats par auteur et oeuvres et date d''emprunt. Interprétez le résultat."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select NA, datEmp , titre, auteur from livres\n",
    "    natural join emprunter\n",
    "    natural join oeuvres\n",
    "    order by  auteur, titre, datEmp;\n",
    "    \n",
    "    \n",
    "SELECT   auteur,\n",
    "         COUNT(* )\n",
    "FROM     oeuvres\n",
    "GROUP BY auteur;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NA</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>2019-07-03</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2019-11-09</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NA      datEmp               titre              auteur\n",
       "0   4  2019-04-03  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "1  27  2019-05-13  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "2  12  2019-07-03  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "3   5  2019-11-09  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "4   9  2020-01-27  Mon coeur mis à nu  Charles BAUDELAIRE"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Select NA, datEmp , titre, auteur from livres natural join emprunter natural join oeuvres order by  auteur, titre, datEmp;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "f.  Afficher le nombre d'emprunts par auteur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>auteur</th>\n",
       "      <th>COUNT(* )</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>STENDHAL</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Jacqueline de ROMILLY</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Paul VALERY</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Kenneth WHITE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>Rainer Maria RILKE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Lewis CAROLL</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>Baruch SPINOZA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>Sigmund FREUD</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>Gottlob FREGE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>Gilbert HOTTOIS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>Joseph CONRAD</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>Xavier CHANET et Patrick VERT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>Jacques VELU</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>Philippe THIRY</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>François LEPAGE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>Jean-Luc CHABERT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>Charles DICKENS</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>Jean-Claude MOURLEVAT</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>Thomas MANN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>Marcel PROUST</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           auteur  COUNT(* )\n",
       "0                   Hermann HESSE          1\n",
       "1                     Jean RACINE          1\n",
       "2                   Emmanuel KANT          1\n",
       "3              Charles BAUDELAIRE          1\n",
       "4          Louis-Ferdinand CELINE          1\n",
       "5               Fedor DOSTOIEVSKI          1\n",
       "6                        STENDHAL          1\n",
       "7           Jacqueline de ROMILLY          1\n",
       "8                     Paul VALERY          1\n",
       "9                   Kenneth WHITE          1\n",
       "10             Rainer Maria RILKE          1\n",
       "11                   Lewis CAROLL          1\n",
       "12                 Baruch SPINOZA          1\n",
       "13                  Sigmund FREUD          1\n",
       "14                  Gottlob FREGE          1\n",
       "15                Gilbert HOTTOIS          1\n",
       "16                  Joseph CONRAD          1\n",
       "17  Xavier CHANET et Patrick VERT          1\n",
       "18                   Jacques VELU          1\n",
       "19                 Philippe THIRY          1\n",
       "20                François LEPAGE          1\n",
       "21               Jean-Luc CHABERT          1\n",
       "22        Joanne Kathleen ROWLING          7\n",
       "23                Charles DICKENS          2\n",
       "24          Jean-Claude MOURLEVAT          2\n",
       "25                    Thomas MANN          1\n",
       "26                  Marcel PROUST          1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT auteur, COUNT(* ) FROM oeuvres GROUP BY auteur;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tables_in_bibliotp02</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>adherents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>categories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>emprunter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>livres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>livres_complets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>oeuvres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>thematique</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tables_in_bibliotp02\n",
       "0            adherents\n",
       "1           categories\n",
       "2            emprunter\n",
       "3               livres\n",
       "4      livres_complets\n",
       "5              oeuvres\n",
       "6           thematique"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('show tables;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6.  Creez une vue permettant d'obtenir les livres avec les informations d'auteurs.\n",
    "\tOn appellera la vue \"livres_complets\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.read_sql_query('CREATE VIEW livres_complets AS SELECT * FROM livres NATURAL JOIN oeuvres;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tables_in_bibliotp02</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>adherents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>categories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>emprunter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>livres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>livres_complets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>oeuvres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>thematique</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tables_in_bibliotp02\n",
       "0            adherents\n",
       "1           categories\n",
       "2            emprunter\n",
       "3               livres\n",
       "4      livres_complets\n",
       "5              oeuvres\n",
       "6           thematique"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('show tables;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7.  Refaites les requête 5.e et 5.f avec la vue livres_complets de l'exercice 6."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "e•  Triez les résultats par auteur et oeuvres et date d''emprunt. Interprétez le résultat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NO</th>\n",
       "      <th>NL</th>\n",
       "      <th>editeur</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>GF</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>GF</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NO  NL editeur                 titre         auteur\n",
       "0   1   1      GF  Narcisse et Goldmund  Hermann HESSE\n",
       "1   1  17   FOLIO  Narcisse et Goldmund  Hermann HESSE\n",
       "2   1  25      GF  Narcisse et Goldmund  Hermann HESSE"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pd.read_sql_query('Select NA, datEmp , titre, auteur from livres natural join emprunter natural join oeuvres order by  auteur, titre, datEmp;', db).head()\n",
    "\n",
    "pd.read_sql_query('SELECT * FROM livres_complets;', db).head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NO</th>\n",
       "      <th>NA</th>\n",
       "      <th>editeur</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>GF</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>GF</td>\n",
       "      <td>2019-07-03</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>GF</td>\n",
       "      <td>2019-11-09</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NO  NA editeur      datEmp               titre              auteur\n",
       "0   4   4      GF  2019-04-03  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "1   4  27   FOLIO  2019-05-13  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "2   4  12      GF  2019-07-03  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "3   4   5      GF  2019-11-09  Mon coeur mis à nu  Charles BAUDELAIRE\n",
       "4   4   9   FOLIO  2020-01-27  Mon coeur mis à nu  Charles BAUDELAIRE"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Select NO, NA, editeur, datEmp , titre, auteur from livres natural join emprunter natural join livres_complets order by  auteur, titre, datEmp;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "f. Afficher le nombre d'emprunts par auteur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>auteur</th>\n",
       "      <th>COUNT(* )</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   auteur  COUNT(* )\n",
       "0           Hermann HESSE          1\n",
       "1             Jean RACINE          1\n",
       "2           Emmanuel KANT          1\n",
       "3      Charles BAUDELAIRE          1\n",
       "4  Louis-Ferdinand CELINE          1"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT auteur, COUNT(* ) FROM oeuvres GROUP BY auteur;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SERIE 2 : La bibliothèque - Requêtes\n",
    "\n",
    "1.\tQuels sont les livres actuellement empruntés ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-03-08</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-09-20</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax     dateRet  NA\n",
       "0   1  2019-03-07        21  2019-03-08  26\n",
       "1   1  2019-09-20        14  2019-10-01   3\n",
       "2   1  2019-11-06        14  2019-11-11   2\n",
       "3   1  2020-01-23        14  2020-01-28   1\n",
       "4   1  2020-02-10        14        None  28"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM emprunter;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax dateRet  NA\n",
       "0   1  2020-02-10        14    None  28\n",
       "1   2  2020-02-10        14    None  28\n",
       "2   3  2020-01-21        14    None  22\n",
       "3   5  2020-02-15        14    None  16\n",
       "4   9  2020-02-10        14    None  28"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pd.read_sql_query('SELECT dateRet FROM emprunter WHERE text LIKE '2019-02-12' ', db).head(3)\n",
    "\n",
    "pd.read_sql_query(' SELECT * FROM emprunter WHERE dateRet is null', db).head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.\tQuels sont les livres empruntés par Jeannette Lecoeur ? Vérifier dans la réponse qu’il n’y a pas d’homonymes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NA</th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "      <th>adr</th>\n",
       "      <th>tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Dupont</td>\n",
       "      <td>Yvan</td>\n",
       "      <td>73 rue Lamarck, 75018 Paris</td>\n",
       "      <td>0163538294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Mercier</td>\n",
       "      <td>Claude</td>\n",
       "      <td>155 avenue Parmentier, 75011 Paris</td>\n",
       "      <td>0136482736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Léger</td>\n",
       "      <td>Marc</td>\n",
       "      <td>90 avenue du Maine, 75014 Paris</td>\n",
       "      <td>0164832947</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NA      nom    prenom                                   adr         tel\n",
       "0   1  Lecoeur  Jeanette  16 rue de la République, 75010 Paris  0145279274\n",
       "1   2  Lecoeur  Philippe  16 rue de la République, 75010 Paris  0145279274\n",
       "2   3   Dupont      Yvan           73 rue Lamarck, 75018 Paris  0163538294\n",
       "3   4  Mercier    Claude    155 avenue Parmentier, 75011 Paris  0136482736\n",
       "4   5    Léger      Marc       90 avenue du Maine, 75014 Paris  0164832947"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM adherents;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NA</th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "      <th>adr</th>\n",
       "      <th>tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-05-03</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-05-21</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NA  NL      datEmp  dureeMax     dateRet      nom    prenom  \\\n",
       "0   1   1  2020-01-23        14  2020-01-28  Lecoeur  Jeanette   \n",
       "1   1  14  2020-02-11        14        None  Lecoeur  Jeanette   \n",
       "2   1  16  2020-02-11        14        None  Lecoeur  Jeanette   \n",
       "3   1  25  2019-04-15        21  2019-05-03  Lecoeur  Jeanette   \n",
       "4   1  37  2019-05-16        14  2019-05-21  Lecoeur  Jeanette   \n",
       "5   1  37  2019-11-12        14  2019-11-22  Lecoeur  Jeanette   \n",
       "6   1  38  2019-11-12        14  2019-11-22  Lecoeur  Jeanette   \n",
       "\n",
       "                                    adr         tel  \n",
       "0  16 rue de la République, 75010 Paris  0145279274  \n",
       "1  16 rue de la République, 75010 Paris  0145279274  \n",
       "2  16 rue de la République, 75010 Paris  0145279274  \n",
       "3  16 rue de la République, 75010 Paris  0145279274  \n",
       "4  16 rue de la République, 75010 Paris  0145279274  \n",
       "5  16 rue de la République, 75010 Paris  0145279274  \n",
       "6  16 rue de la République, 75010 Paris  0145279274  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#pd.read_sql_query('SELECT nom, prenom, auteur, NA, NL FROM livres NATURAL JOIN oeuvres NATURAL JOIN adherents WHERE nom is LECOEUR AND prenom is JEANETTE', db).head()\n",
    "querey = 'SELECT * \\\n",
    "            FROM emprunter natural join adherents \\\n",
    "            WHERE prenom = \"Jeanette\" AND nom = \"Lecoeur\"'\n",
    "            \n",
    "\n",
    "pd.read_sql_query(querey, db)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.\tQuels sont tous les livres empruntés le mois dernier ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "      <th>NO</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-12</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>Berenice</td>\n",
       "      <td>Jean RACINE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-12</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>Prolégomènes à  toute métaphysique future</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-12</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>Mon coeur mis à nu</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-12</td>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>Voyage au bout de la nuit</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax     dateRet  NA  NO  \\\n",
       "0   8  2019-12-06        14  2019-12-12  18   1   \n",
       "1   8  2019-12-06        14  2019-12-12  18   2   \n",
       "2   8  2019-12-06        14  2019-12-12  18   3   \n",
       "3   8  2019-12-06        14  2019-12-12  18   4   \n",
       "4   8  2019-12-06        14  2019-12-12  18   5   \n",
       "\n",
       "                                       titre                  auteur  \n",
       "0                       Narcisse et Goldmund           Hermann HESSE  \n",
       "1                                   Berenice             Jean RACINE  \n",
       "2  Prolégomènes à  toute métaphysique future           Emmanuel KANT  \n",
       "3                         Mon coeur mis à nu      Charles BAUDELAIRE  \n",
       "4                  Voyage au bout de la nuit  Louis-Ferdinand CELINE  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(' SELECT * FROM emprunter natural join oeuvres WHERE year(datEmp) = 2019 AND month(datemp) = 12', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4.\tTous les adhérents qui ont emprunté un livre de Fedor Dostoievski."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NA</th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "      <th>adr</th>\n",
       "      <th>tel</th>\n",
       "      <th>NO</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "      <td>6</td>\n",
       "      <td>Les possédés</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>Philippe</td>\n",
       "      <td>16 rue de la République, 75010 Paris</td>\n",
       "      <td>0145279274</td>\n",
       "      <td>6</td>\n",
       "      <td>Les possédés</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Dupont</td>\n",
       "      <td>Yvan</td>\n",
       "      <td>73 rue Lamarck, 75018 Paris</td>\n",
       "      <td>0163538294</td>\n",
       "      <td>6</td>\n",
       "      <td>Les possédés</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Mercier</td>\n",
       "      <td>Claude</td>\n",
       "      <td>155 avenue Parmentier, 75011 Paris</td>\n",
       "      <td>0136482736</td>\n",
       "      <td>6</td>\n",
       "      <td>Les possédés</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Léger</td>\n",
       "      <td>Marc</td>\n",
       "      <td>90 avenue du Maine, 75014 Paris</td>\n",
       "      <td>0164832947</td>\n",
       "      <td>6</td>\n",
       "      <td>Les possédés</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NA      nom    prenom                                   adr         tel  \\\n",
       "0   1  Lecoeur  Jeanette  16 rue de la République, 75010 Paris  0145279274   \n",
       "1   2  Lecoeur  Philippe  16 rue de la République, 75010 Paris  0145279274   \n",
       "2   3   Dupont      Yvan           73 rue Lamarck, 75018 Paris  0163538294   \n",
       "3   4  Mercier    Claude    155 avenue Parmentier, 75011 Paris  0136482736   \n",
       "4   5    Léger      Marc       90 avenue du Maine, 75014 Paris  0164832947   \n",
       "\n",
       "   NO         titre             auteur  \n",
       "0   6  Les possédés  Fedor DOSTOIEVSKI  \n",
       "1   6  Les possédés  Fedor DOSTOIEVSKI  \n",
       "2   6  Les possédés  Fedor DOSTOIEVSKI  \n",
       "3   6  Les possédés  Fedor DOSTOIEVSKI  \n",
       "4   6  Les possédés  Fedor DOSTOIEVSKI  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM adherents natural join oeuvres WHERE auteur = \"Fedor Dostoievski\"', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.\tQuels sont le ou les auteurs du titre « Voyage au bout de la nuit »"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   auteur\n",
       "0  Louis-Ferdinand CELINE"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT auteur FROM oeuvres WHERE titre = \"Voyage au bout de la nuit\"', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6.\tQuels sont le ou les éditeurs du titre « Narcisse et Goldmund »"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>editeur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>GF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>FOLIO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>GF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>GF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  editeur\n",
       "0      GF\n",
       "1   FOLIO\n",
       "2      GF\n",
       "3      GF"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT editeur FROM oeuvres NATURAL JOIN livres WHERE titre = \"Narcisse et Goldmund\"', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7.\tQuels sont les adhérents actuellement en retard ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "      <th>na</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Rihour</td>\n",
       "      <td>Cécile</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Tanrhori</td>\n",
       "      <td>Mustafa</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Cornu</td>\n",
       "      <td>Sylvain</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        nom   prenom  na\n",
       "0    Rihour   Cécile  22\n",
       "1  Tanrhori  Mustafa  33\n",
       "2     Cornu  Sylvain  27"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select distinct nom, prenom, na from emprunter NATURAL JOIN adherents where TIMESTAMPADD(DAY ,dureeMax, datEmp) < curdate() and dateRet is null; ', db).head(40)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "8.\tQuels sont les livres actuellement en retard ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nl\n",
       "0   3\n",
       "1  10\n",
       "2  11\n",
       "3  18\n",
       "4  19"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(' select nl from emprunter where TIMESTAMPADD(DAY ,dureeMax, datEmp) < curdate() and dateRet is null;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "9.\tQuels sont les adhérents en retard avec le nombre de livre en retard et la moyenne du nombre de jour de retard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>na</th>\n",
       "      <th>nb_livres</th>\n",
       "      <th>moy_retard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>2</td>\n",
       "      <td>17.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>382.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>33</td>\n",
       "      <td>3</td>\n",
       "      <td>8.6667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   na  nb_livres  moy_retard\n",
       "0  22          2     17.0000\n",
       "1  27          1    382.0000\n",
       "2  33          3      8.6667"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select na, count(*) as nb_livres, avg(TIMESTAMPDIFF(DAY , TIMESTAMPADD(DAY ,dureeMax, datEmp), curdate())) as moy_retard from emprunter where TIMESTAMPADD(DAY ,dureeMax, datEmp) < curdate() and dateRet is null group by na;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "10.\tNombre de livres empruntées par auteur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>auteur</th>\n",
       "      <th>nl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Sigmund FREUD</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Gilbert HOTTOIS</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>Kenneth WHITE</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>Lewis CAROLL</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>Kenneth WHITE</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>Kenneth WHITE</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>Gottlob FREGE</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>Jacqueline de ROMILLY</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>Lewis CAROLL</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>Jacqueline de ROMILLY</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>Joseph CONRAD</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>Rainer Maria RILKE</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>Jacqueline de ROMILLY</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>Paul VALERY</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>Kenneth WHITE</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     auteur  nl\n",
       "0             Hermann HESSE   1\n",
       "1             Sigmund FREUD  14\n",
       "2           Gilbert HOTTOIS  16\n",
       "3             Hermann HESSE  25\n",
       "4   Joanne Kathleen ROWLING  37\n",
       "5   Joanne Kathleen ROWLING  37\n",
       "6   Joanne Kathleen ROWLING  38\n",
       "7             Hermann HESSE   1\n",
       "8   Joanne Kathleen ROWLING  38\n",
       "9             Hermann HESSE   1\n",
       "10            Emmanuel KANT   3\n",
       "11        Fedor DOSTOIEVSKI   6\n",
       "12              Jean RACINE  18\n",
       "13              Jean RACINE   2\n",
       "14       Charles BAUDELAIRE   4\n",
       "15       Charles BAUDELAIRE   4\n",
       "16            Kenneth WHITE  10\n",
       "17             Lewis CAROLL  12\n",
       "18            Kenneth WHITE  10\n",
       "19       Charles BAUDELAIRE  20\n",
       "20            Kenneth WHITE  26\n",
       "21            Gottlob FREGE  15\n",
       "22       Charles BAUDELAIRE   4\n",
       "23    Jacqueline de ROMILLY   8\n",
       "24   Louis-Ferdinand CELINE  21\n",
       "25   Louis-Ferdinand CELINE   5\n",
       "26             Lewis CAROLL  28\n",
       "27    Jacqueline de ROMILLY   8\n",
       "28   Louis-Ferdinand CELINE  21\n",
       "29            Joseph CONRAD  31\n",
       "30            Emmanuel KANT   3\n",
       "31       Rainer Maria RILKE  11\n",
       "32            Hermann HESSE   1\n",
       "33    Jacqueline de ROMILLY  24\n",
       "34       Charles BAUDELAIRE  20\n",
       "35            Hermann HESSE  29\n",
       "36            Hermann HESSE   1\n",
       "37              Jean RACINE   2\n",
       "38              Paul VALERY   9\n",
       "39  Joanne Kathleen ROWLING  38\n",
       "40  Joanne Kathleen ROWLING  39\n",
       "41  Joanne Kathleen ROWLING  40\n",
       "42            Kenneth WHITE  10\n",
       "43              Jean RACINE  18\n",
       "44              Jean RACINE  19"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select oeuvres.auteur, emprunter.nl from emprunter inner join livres on livres.nl = emprunter.nl inner join oeuvres on oeuvres.no = livres.no;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>auteur</th>\n",
       "      <th>count(titre)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Sigmund FREUD</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Gilbert HOTTOIS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Fedor DOSTOIEVSKI</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Kenneth WHITE</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Lewis CAROLL</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>Gottlob FREGE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Jacqueline de ROMILLY</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>Louis-Ferdinand CELINE</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>Joseph CONRAD</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>Rainer Maria RILKE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>Paul VALERY</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     auteur  count(titre)\n",
       "0             Hermann HESSE             7\n",
       "1             Sigmund FREUD             1\n",
       "2           Gilbert HOTTOIS             1\n",
       "3   Joanne Kathleen ROWLING             7\n",
       "4             Emmanuel KANT             2\n",
       "5         Fedor DOSTOIEVSKI             1\n",
       "6               Jean RACINE             5\n",
       "7        Charles BAUDELAIRE             5\n",
       "8             Kenneth WHITE             4\n",
       "9              Lewis CAROLL             2\n",
       "10            Gottlob FREGE             1\n",
       "11    Jacqueline de ROMILLY             3\n",
       "12   Louis-Ferdinand CELINE             3\n",
       "13            Joseph CONRAD             1\n",
       "14       Rainer Maria RILKE             1\n",
       "15              Paul VALERY             1"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT auteur,count(titre) FROM emprunter NATURAL JOIN livres_complets GROUP BY auteur; ', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "11.\tNombre de livres empruntés par éditeur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>editeur</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>GF</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>HACHETTE</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    editeur  count(*)\n",
       "0        GF        15\n",
       "1     FOLIO        22\n",
       "2  HACHETTE         8"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Select editeur, count(*) From emprunter NATURAL JOIN livres NATURAL JOIN oeuvres Group by editeur;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "11. bis : Nombre de livres empruntés par auteur et editeur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>auteur</th>\n",
       "      <th>editeur</th>\n",
       "      <th>nb_emprunts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>GF</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Sigmund FREUD</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Gilbert HOTTOIS</td>\n",
       "      <td>HACHETTE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "      <td>FOLIO</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>HACHETTE</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    auteur   editeur  nb_emprunts\n",
       "0            Hermann HESSE        GF            7\n",
       "1            Sigmund FREUD     FOLIO            1\n",
       "2          Gilbert HOTTOIS  HACHETTE            1\n",
       "3  Joanne Kathleen ROWLING     FOLIO            7\n",
       "4            Emmanuel KANT  HACHETTE            2"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select oeuvres.auteur, livres.editeur, count(*) as nb_emprunts from emprunter inner join livres on livres.nl = emprunter.nl inner join oeuvres on oeuvres.no = livres.no group by oeuvres.auteur, livres.editeur;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "11. ter : with rollup : Nombre de livres empruntés pas auteur et editeur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>auteur</th>\n",
       "      <th>count(titre)</th>\n",
       "      <th>editeur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>2</td>\n",
       "      <td>FOLIO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>3</td>\n",
       "      <td>GF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Charles BAUDELAIRE</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>2</td>\n",
       "      <td>HACHETTE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               auteur  count(titre)   editeur\n",
       "0  Charles BAUDELAIRE             2     FOLIO\n",
       "1  Charles BAUDELAIRE             3        GF\n",
       "2  Charles BAUDELAIRE             5      None\n",
       "3       Emmanuel KANT             2  HACHETTE\n",
       "4       Emmanuel KANT             2      None"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Select auteur, count(titre), editeur From emprunter NATURAL JOIN livres NATURAL JOIN oeuvres Group by auteur, editeur WITH ROLLUP;', db).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "12.\tQuelle est la durée des emprunts rendus ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nl</th>\n",
       "      <th>duree</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nl  duree\n",
       "0   1      1\n",
       "1   1     11\n",
       "2   1      5\n",
       "3   1      5\n",
       "4   2      9"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT nl, TIMESTAMPDIFF(DAY ,datEmp ,dateRet) AS duree FROM emprunter WHERE dateRet IS NOT NULL;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "13. Quelle est la durée moyenne des emprunts rendus."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "avg(datediff(dateRet , datEmp))    9.8\n",
       "dtype: float64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select avg(datediff(dateRet , datEmp)) from emprunter where  dateRet is not null  and datEmp < dateRet;', db).mean().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "14.\tQuelle est la durée moyenne des retards pour l’ensemble des emprunts.\n",
    "\n",
    "La fonction « if » permet de tester une valeur et de renvoyer ce qu’on souhaite selon la vérité ou la fausseté de la valeur testée.\n",
    "\n",
    "if(a<0, 0, a) : permet de ramener les valeurs négatives de a à 0."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-- Liste des retards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-03-08</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-09-20</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-04-16</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-03</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-07-07</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-11-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-05</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-10</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-21</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-12</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-05-23</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-06-15</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-09-04</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-09-16</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>2019-04-26</td>\n",
       "      <td>21</td>\n",
       "      <td>2016-08-09</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>15</td>\n",
       "      <td>2019-09-27</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-05</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>20</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-05-14</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-12</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-07-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-05-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>26</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-04-08</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>31</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-05-21</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>38</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>39</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    NL      datEmp  dureeMax     dateRet  NA\n",
       "0    1  2019-03-07        21  2019-03-08  26\n",
       "1    1  2019-09-20        14  2019-10-01   3\n",
       "2    1  2019-11-06        14  2019-11-11   2\n",
       "3    1  2020-01-23        14  2020-01-28   1\n",
       "4    1  2020-02-10        14        None  28\n",
       "5    2  2020-01-14        14  2020-01-23   4\n",
       "6    2  2020-02-10        14        None  28\n",
       "7    3  2020-01-21        14        None  22\n",
       "8    3  2020-01-30        14  2020-02-03   3\n",
       "9    4  2019-04-03        21  2019-04-16   4\n",
       "10   4  2019-07-03        14  2019-07-07  12\n",
       "11   4  2019-11-09        14  2019-11-19   5\n",
       "12   5  2020-02-15        14        None  16\n",
       "13   6  2019-08-05        14  2019-08-10   3\n",
       "14   8  2019-08-03        14  2019-08-21  13\n",
       "15   8  2019-12-06        14  2019-12-12  18\n",
       "16   9  2020-02-10        14        None  28\n",
       "17  10  2019-05-23        21  2019-06-15   7\n",
       "18  10  2019-09-04        14  2019-09-16   8\n",
       "19  10  2020-01-26        14        None  33\n",
       "20  11  2020-01-21        14        None  22\n",
       "21  12  2019-04-26        21  2016-08-09   7\n",
       "22  14  2020-02-11        14        None   1\n",
       "23  15  2019-09-27        14  2019-10-05  10\n",
       "24  16  2020-02-11        14        None   1\n",
       "25  18  2019-11-26        14  2019-12-03   3\n",
       "26  18  2020-01-31        14        None  33\n",
       "27  19  2020-01-31        14        None  33\n",
       "28  20  2019-05-13        21  2019-05-14  27\n",
       "29  20  2020-01-27        14  2020-02-12   9\n",
       "30  21  2020-01-28        14  2020-02-09  14\n",
       "31  21  2020-02-19        14        None  20\n",
       "32  24  2019-07-09        14  2019-07-15  26\n",
       "33  25  2019-04-15        21  2019-05-03   1\n",
       "34  26  2019-04-11        21  2019-04-08   9\n",
       "35  28  2020-02-10        14        None  16\n",
       "36  29  2019-01-21        14        None  27\n",
       "37  31  2020-02-19        14        None  20\n",
       "38  37  2019-05-16        14  2019-05-21   1\n",
       "39  37  2019-11-12        14  2019-11-22   1\n",
       "40  38  2019-07-09        14  2019-08-04   2\n",
       "41  38  2019-11-12        14  2019-11-22   1\n",
       "42  38  2020-02-17        14        None  31\n",
       "43  39  2020-02-17        14        None  31\n",
       "44  40  2020-02-17        14        None  31"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from emprunter;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nl</th>\n",
       "      <th>retour_prevu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-10-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-02-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-02-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-11-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-09-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-02-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>2020-02-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>2019-05-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>15</td>\n",
       "      <td>2019-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "      <td>2020-02-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>2019-12-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>2020-02-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-02-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>20</td>\n",
       "      <td>2019-06-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-02-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-02-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-03-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-07-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-05-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>26</td>\n",
       "      <td>2019-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-02-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>31</td>\n",
       "      <td>2020-03-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-05-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-11-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-11-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>38</td>\n",
       "      <td>2020-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>39</td>\n",
       "      <td>2020-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>2020-03-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    nl retour_prevu\n",
       "0    1   2019-03-28\n",
       "1    1   2019-10-04\n",
       "2    1   2019-11-20\n",
       "3    1   2020-02-06\n",
       "4    1   2020-02-24\n",
       "5    2   2020-01-28\n",
       "6    2   2020-02-24\n",
       "7    3   2020-02-04\n",
       "8    3   2020-02-13\n",
       "9    4   2019-04-24\n",
       "10   4   2019-07-17\n",
       "11   4   2019-11-23\n",
       "12   5   2020-02-29\n",
       "13   6   2019-08-19\n",
       "14   8   2019-08-17\n",
       "15   8   2019-12-20\n",
       "16   9   2020-02-24\n",
       "17  10   2019-06-13\n",
       "18  10   2019-09-18\n",
       "19  10   2020-02-09\n",
       "20  11   2020-02-04\n",
       "21  12   2019-05-17\n",
       "22  14   2020-02-25\n",
       "23  15   2019-10-11\n",
       "24  16   2020-02-25\n",
       "25  18   2019-12-10\n",
       "26  18   2020-02-14\n",
       "27  19   2020-02-14\n",
       "28  20   2019-06-03\n",
       "29  20   2020-02-10\n",
       "30  21   2020-02-11\n",
       "31  21   2020-03-04\n",
       "32  24   2019-07-23\n",
       "33  25   2019-05-06\n",
       "34  26   2019-05-02\n",
       "35  28   2020-02-24\n",
       "36  29   2019-02-04\n",
       "37  31   2020-03-04\n",
       "38  37   2019-05-30\n",
       "39  37   2019-11-26\n",
       "40  38   2019-07-23\n",
       "41  38   2019-11-26\n",
       "42  38   2020-03-02\n",
       "43  39   2020-03-02\n",
       "44  40   2020-03-02"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Date de retour escompté\n",
    "\n",
    "pd.read_sql_query('select nl,TIMESTAMPADD(day, dureeMax, datEmp) as retour_prevu from emprunter;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nl</th>\n",
       "      <th>retour_prevu</th>\n",
       "      <th>dateRet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-28</td>\n",
       "      <td>2019-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-10-04</td>\n",
       "      <td>2019-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>2019-11-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>2020-01-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>2020-01-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>2020-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-24</td>\n",
       "      <td>2019-04-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-17</td>\n",
       "      <td>2019-07-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-11-23</td>\n",
       "      <td>2019-11-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-29</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>2019-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-08-17</td>\n",
       "      <td>2019-08-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-20</td>\n",
       "      <td>2019-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-06-13</td>\n",
       "      <td>2019-06-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-09-18</td>\n",
       "      <td>2019-09-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>2016-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-25</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>15</td>\n",
       "      <td>2019-10-11</td>\n",
       "      <td>2019-10-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "      <td>2020-02-25</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>2019-12-10</td>\n",
       "      <td>2019-12-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>2020-02-14</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-02-14</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>20</td>\n",
       "      <td>2019-06-03</td>\n",
       "      <td>2019-05-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>2020-02-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>2020-02-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-07-23</td>\n",
       "      <td>2019-07-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-05-06</td>\n",
       "      <td>2019-05-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>26</td>\n",
       "      <td>2019-05-02</td>\n",
       "      <td>2019-04-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-02-04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>31</td>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-05-30</td>\n",
       "      <td>2019-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>2019-11-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-23</td>\n",
       "      <td>2019-08-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>2019-11-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>38</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>39</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    nl retour_prevu     dateRet\n",
       "0    1   2019-03-28  2019-03-08\n",
       "1    1   2019-10-04  2019-10-01\n",
       "2    1   2019-11-20  2019-11-11\n",
       "3    1   2020-02-06  2020-01-28\n",
       "4    1   2020-02-24        None\n",
       "5    2   2020-01-28  2020-01-23\n",
       "6    2   2020-02-24        None\n",
       "7    3   2020-02-04        None\n",
       "8    3   2020-02-13  2020-02-03\n",
       "9    4   2019-04-24  2019-04-16\n",
       "10   4   2019-07-17  2019-07-07\n",
       "11   4   2019-11-23  2019-11-19\n",
       "12   5   2020-02-29        None\n",
       "13   6   2019-08-19  2019-08-10\n",
       "14   8   2019-08-17  2019-08-21\n",
       "15   8   2019-12-20  2019-12-12\n",
       "16   9   2020-02-24        None\n",
       "17  10   2019-06-13  2019-06-15\n",
       "18  10   2019-09-18  2019-09-16\n",
       "19  10   2020-02-09        None\n",
       "20  11   2020-02-04        None\n",
       "21  12   2019-05-17  2016-08-09\n",
       "22  14   2020-02-25        None\n",
       "23  15   2019-10-11  2019-10-05\n",
       "24  16   2020-02-25        None\n",
       "25  18   2019-12-10  2019-12-03\n",
       "26  18   2020-02-14        None\n",
       "27  19   2020-02-14        None\n",
       "28  20   2019-06-03  2019-05-14\n",
       "29  20   2020-02-10  2020-02-12\n",
       "30  21   2020-02-11  2020-02-09\n",
       "31  21   2020-03-04        None\n",
       "32  24   2019-07-23  2019-07-15\n",
       "33  25   2019-05-06  2019-05-03\n",
       "34  26   2019-05-02  2019-04-08\n",
       "35  28   2020-02-24        None\n",
       "36  29   2019-02-04        None\n",
       "37  31   2020-03-04        None\n",
       "38  37   2019-05-30  2019-05-21\n",
       "39  37   2019-11-26  2019-11-22\n",
       "40  38   2019-07-23  2019-08-04\n",
       "41  38   2019-11-26  2019-11-22\n",
       "42  38   2020-03-02        None\n",
       "43  39   2020-03-02        None\n",
       "44  40   2020-03-02        None"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Date de retour escompté avec le champ dateRet\n",
    "pd.read_sql_query('select nl,TIMESTAMPADD(day, dureeMax, datEmp) as retour_prevu, dateRet from emprunter;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nl</th>\n",
       "      <th>retour_prevu</th>\n",
       "      <th>date_test</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-28</td>\n",
       "      <td>2019-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-10-04</td>\n",
       "      <td>2019-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>2019-11-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>2020-01-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>2020-01-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>2020-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-24</td>\n",
       "      <td>2019-04-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-17</td>\n",
       "      <td>2019-07-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-11-23</td>\n",
       "      <td>2019-11-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-29</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>2019-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-08-17</td>\n",
       "      <td>2019-08-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-20</td>\n",
       "      <td>2019-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-06-13</td>\n",
       "      <td>2019-06-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-09-18</td>\n",
       "      <td>2019-09-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>2016-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-25</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>15</td>\n",
       "      <td>2019-10-11</td>\n",
       "      <td>2019-10-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "      <td>2020-02-25</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>2019-12-10</td>\n",
       "      <td>2019-12-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>2020-02-14</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-02-14</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>20</td>\n",
       "      <td>2019-06-03</td>\n",
       "      <td>2019-05-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>2020-02-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>2020-02-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-07-23</td>\n",
       "      <td>2019-07-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-05-06</td>\n",
       "      <td>2019-05-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>26</td>\n",
       "      <td>2019-05-02</td>\n",
       "      <td>2019-04-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-02-24</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-02-04</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>31</td>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-05-30</td>\n",
       "      <td>2019-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>2019-11-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-23</td>\n",
       "      <td>2019-08-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>2019-11-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>38</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>39</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-02-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    nl retour_prevu   date_test\n",
       "0    1   2019-03-28  2019-03-08\n",
       "1    1   2019-10-04  2019-10-01\n",
       "2    1   2019-11-20  2019-11-11\n",
       "3    1   2020-02-06  2020-01-28\n",
       "4    1   2020-02-24  2020-02-21\n",
       "5    2   2020-01-28  2020-01-23\n",
       "6    2   2020-02-24  2020-02-21\n",
       "7    3   2020-02-04  2020-02-21\n",
       "8    3   2020-02-13  2020-02-03\n",
       "9    4   2019-04-24  2019-04-16\n",
       "10   4   2019-07-17  2019-07-07\n",
       "11   4   2019-11-23  2019-11-19\n",
       "12   5   2020-02-29  2020-02-21\n",
       "13   6   2019-08-19  2019-08-10\n",
       "14   8   2019-08-17  2019-08-21\n",
       "15   8   2019-12-20  2019-12-12\n",
       "16   9   2020-02-24  2020-02-21\n",
       "17  10   2019-06-13  2019-06-15\n",
       "18  10   2019-09-18  2019-09-16\n",
       "19  10   2020-02-09  2020-02-21\n",
       "20  11   2020-02-04  2020-02-21\n",
       "21  12   2019-05-17  2016-08-09\n",
       "22  14   2020-02-25  2020-02-21\n",
       "23  15   2019-10-11  2019-10-05\n",
       "24  16   2020-02-25  2020-02-21\n",
       "25  18   2019-12-10  2019-12-03\n",
       "26  18   2020-02-14  2020-02-21\n",
       "27  19   2020-02-14  2020-02-21\n",
       "28  20   2019-06-03  2019-05-14\n",
       "29  20   2020-02-10  2020-02-12\n",
       "30  21   2020-02-11  2020-02-09\n",
       "31  21   2020-03-04  2020-02-21\n",
       "32  24   2019-07-23  2019-07-15\n",
       "33  25   2019-05-06  2019-05-03\n",
       "34  26   2019-05-02  2019-04-08\n",
       "35  28   2020-02-24  2020-02-21\n",
       "36  29   2019-02-04  2020-02-21\n",
       "37  31   2020-03-04  2020-02-21\n",
       "38  37   2019-05-30  2019-05-21\n",
       "39  37   2019-11-26  2019-11-22\n",
       "40  38   2019-07-23  2019-08-04\n",
       "41  38   2019-11-26  2019-11-22\n",
       "42  38   2020-03-02  2020-02-21\n",
       "43  39   2020-03-02  2020-02-21\n",
       "44  40   2020-03-02  2020-02-21"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On met la date courante pour les valeurs NULL de dateRet\n",
    "pd.read_sql_query('select nl,TIMESTAMPADD(day, dureeMax, datEmp) as retour_prevu, IFNULL(dateRet, curdate()) as date_test from emprunter;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "      <th>retard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "      <td>-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-21</td>\n",
       "      <td>13</td>\n",
       "      <td>-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-05-23</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-06-15</td>\n",
       "      <td>7</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "      <td>-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "      <td>-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "      <td>-7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "      <td>-7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-12</td>\n",
       "      <td>9</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>27</td>\n",
       "      <td>-382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>2</td>\n",
       "      <td>-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax     dateRet  NA  retard\n",
       "0   3  2020-01-21        14        None  22     -17\n",
       "1   8  2019-08-03        14  2019-08-21  13      -4\n",
       "2  10  2019-05-23        21  2019-06-15   7      -2\n",
       "3  10  2020-01-26        14        None  33     -12\n",
       "4  11  2020-01-21        14        None  22     -17\n",
       "5  18  2020-01-31        14        None  33      -7\n",
       "6  19  2020-01-31        14        None  33      -7\n",
       "7  20  2020-01-27        14  2020-02-12   9      -2\n",
       "8  29  2019-01-21        14        None  27    -382\n",
       "9  38  2019-07-09        14  2019-08-04   2     -12"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Si retour_prevu > date_test on comptablise la ligne \n",
    "pd.read_sql_query('select *, TIMESTAMPDIFF(DAY ,IFNULL(dateRet, curdate()), TIMESTAMPADD(day, dureeMax, datEmp)) as retard from emprunter where TIMESTAMPDIFF(DAY ,IFNULL(dateRet, curdate()), TIMESTAMPADD(day, dureeMax, datEmp)) < 0;', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>moyenne_retard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>46.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   moyenne_retard\n",
       "0            46.2"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# La moyenne\n",
    "pd.read_sql_query('select AVG(TIMESTAMPDIFF(DAY ,TIMESTAMPADD(day, dureeMax, datEmp), IFNULL(dateRet, curdate()))) as moyenne_retard from emprunter where TIMESTAMPDIFF(DAY ,IFNULL(dateRet, curdate()), TIMESTAMPADD(day, dureeMax, datEmp)) < 0;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "15.\tDurée moyenne des retards parmi les seuls retardataires."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>DATEDIFF( dateRet, \"2020-01-27\")</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [datEmp, dateRet, DATEDIFF( dateRet, \"2020-01-27\")]\n",
       "Index: []"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select datEmp, dateRet, DATEDIFF( dateRet, \"2020-01-27\") From emprunter Where datEmp is null;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SERIE 4 : La bibliothèque - Requêtes avec Thématiques\n",
    "\n",
    "1.\tQuels sont les livres \"jeunesse\" actuellement empruntés ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-53-88a41185ea92>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-53-88a41185ea92>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    pd.read_sql_query('SELECT emprunter.* FROM emprunter INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE categories.categorie = 'Jeunesse' AND emprunter.dateRet IS NULL;', db)\u001b[0m\n\u001b[1;37m                                                                                                                                                                                                                                                                                                    ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT emprunter.* FROM emprunter INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE categories.categorie = 'Jeunesse' AND emprunter.dateRet IS NULL;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.\tAfficher les catégories des livres actuellement empruntés."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>categorie</th>\n",
       "      <th>dateRet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Roman</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Jeunesse</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Theatre</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Poesie</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Mathematiques</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Sorciers</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Informatique</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Logique</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>philosophie</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>sciences</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>roman étranger</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>psychologie</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         categorie dateRet\n",
       "0            Roman    None\n",
       "1         Jeunesse    None\n",
       "2          Theatre    None\n",
       "3           Poesie    None\n",
       "4    Mathematiques    None\n",
       "5         Sorciers    None\n",
       "6     Informatique    None\n",
       "7          Logique    None\n",
       "8      philosophie    None\n",
       "9         sciences    None\n",
       "10  roman étranger    None\n",
       "11     psychologie    None"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT categorie, dateRet From categories INNER JOIN emprunter WHERE dateRet is null GROUP BY categorie ;', db)\n",
    "\n",
    "#SELECT DISTINCT categories.categorie FROM emprunter INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE emprunter.dateRet IS NULL;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.\tAfficher la liste des catégories par oeuvre."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NO</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "      <th>NO</th>\n",
       "      <th>NC</th>\n",
       "      <th>NC</th>\n",
       "      <th>categorie</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Roman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>roman étranger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Berenice</td>\n",
       "      <td>Jean RACINE</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Theatre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Prolégomènes à  toute métaphysique future</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Prolégomènes à  toute métaphysique future</td>\n",
       "      <td>Emmanuel KANT</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>philosophie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>69</td>\n",
       "      <td>33</td>\n",
       "      <td>Terrienne</td>\n",
       "      <td>Jean-Claude MOURLEVAT</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Jeunesse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>70</td>\n",
       "      <td>33</td>\n",
       "      <td>Terrienne</td>\n",
       "      <td>Jean-Claude MOURLEVAT</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Roman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>71</td>\n",
       "      <td>34</td>\n",
       "      <td>La Montagne magique</td>\n",
       "      <td>Thomas MANN</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Roman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>72</td>\n",
       "      <td>34</td>\n",
       "      <td>La Montagne magique</td>\n",
       "      <td>Thomas MANN</td>\n",
       "      <td>34</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>roman étranger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>73</td>\n",
       "      <td>35</td>\n",
       "      <td>A la recherche du temps perdu, I : Du côté de ...</td>\n",
       "      <td>Marcel PROUST</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Roman</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    NO                                              titre  \\\n",
       "0    1                               Narcisse et Goldmund   \n",
       "1    1                               Narcisse et Goldmund   \n",
       "2    2                                           Berenice   \n",
       "3    3          Prolégomènes à  toute métaphysique future   \n",
       "4    3          Prolégomènes à  toute métaphysique future   \n",
       "..  ..                                                ...   \n",
       "69  33                                          Terrienne   \n",
       "70  33                                          Terrienne   \n",
       "71  34                                La Montagne magique   \n",
       "72  34                                La Montagne magique   \n",
       "73  35  A la recherche du temps perdu, I : Du côté de ...   \n",
       "\n",
       "                   auteur  NO  NC  NC       categorie  \n",
       "0           Hermann HESSE   1   1   1           Roman  \n",
       "1           Hermann HESSE   1  11  11  roman étranger  \n",
       "2             Jean RACINE   2   3   3         Theatre  \n",
       "3           Emmanuel KANT   3  10  10        sciences  \n",
       "4           Emmanuel KANT   3   9   9     philosophie  \n",
       "..                    ...  ..  ..  ..             ...  \n",
       "69  Jean-Claude MOURLEVAT  33   2   2        Jeunesse  \n",
       "70  Jean-Claude MOURLEVAT  33   1   1           Roman  \n",
       "71            Thomas MANN  34   1   1           Roman  \n",
       "72            Thomas MANN  34  11  11  roman étranger  \n",
       "73          Marcel PROUST  35   1   1           Roman  \n",
       "\n",
       "[74 rows x 7 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM oeuvres INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC ORDER BY oeuvres.no;', db).head(80)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4.\tQuels sont les adhérents ayant empruntés des livres \"jeunesse\" ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.read_sql_query('SELECT DISTINCT adherents.NA FROM adherents INNER JOIN emprunter ON emprunter.NA = adherents.NA INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE categories.categorie = 'Jeunesse'; ', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-03-08</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-09-20</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-04-16</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-03</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-07-07</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-11-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-05</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-10</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-21</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-12</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-05-23</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-06-15</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-09-04</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-09-16</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>2019-04-26</td>\n",
       "      <td>21</td>\n",
       "      <td>2016-08-09</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>15</td>\n",
       "      <td>2019-09-27</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-05</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "      <td>2020-02-11</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-12-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>20</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-05-14</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-12</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-07-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-05-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>26</td>\n",
       "      <td>2019-04-11</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-04-08</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>31</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-05-21</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-09</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>38</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>39</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    NL      datEmp  dureeMax     dateRet  NA\n",
       "0    1  2019-03-07        21  2019-03-08  26\n",
       "1    1  2019-09-20        14  2019-10-01   3\n",
       "2    1  2019-11-06        14  2019-11-11   2\n",
       "3    1  2020-01-23        14  2020-01-28   1\n",
       "4    1  2020-02-10        14        None  28\n",
       "5    2  2020-01-14        14  2020-01-23   4\n",
       "6    2  2020-02-10        14        None  28\n",
       "7    3  2020-01-21        14        None  22\n",
       "8    3  2020-01-30        14  2020-02-03   3\n",
       "9    4  2019-04-03        21  2019-04-16   4\n",
       "10   4  2019-07-03        14  2019-07-07  12\n",
       "11   4  2019-11-09        14  2019-11-19   5\n",
       "12   5  2020-02-15        14        None  16\n",
       "13   6  2019-08-05        14  2019-08-10   3\n",
       "14   8  2019-08-03        14  2019-08-21  13\n",
       "15   8  2019-12-06        14  2019-12-12  18\n",
       "16   9  2020-02-10        14        None  28\n",
       "17  10  2019-05-23        21  2019-06-15   7\n",
       "18  10  2019-09-04        14  2019-09-16   8\n",
       "19  10  2020-01-26        14        None  33\n",
       "20  11  2020-01-21        14        None  22\n",
       "21  12  2019-04-26        21  2016-08-09   7\n",
       "22  14  2020-02-11        14        None   1\n",
       "23  15  2019-09-27        14  2019-10-05  10\n",
       "24  16  2020-02-11        14        None   1\n",
       "25  18  2019-11-26        14  2019-12-03   3\n",
       "26  18  2020-01-31        14        None  33\n",
       "27  19  2020-01-31        14        None  33\n",
       "28  20  2019-05-13        21  2019-05-14  27\n",
       "29  20  2020-01-27        14  2020-02-12   9\n",
       "30  21  2020-01-28        14  2020-02-09  14\n",
       "31  21  2020-02-19        14        None  20\n",
       "32  24  2019-07-09        14  2019-07-15  26\n",
       "33  25  2019-04-15        21  2019-05-03   1\n",
       "34  26  2019-04-11        21  2019-04-08   9\n",
       "35  28  2020-02-10        14        None  16\n",
       "36  29  2019-01-21        14        None  27\n",
       "37  31  2020-02-19        14        None  20\n",
       "38  37  2019-05-16        14  2019-05-21   1\n",
       "39  37  2019-11-12        14  2019-11-22   1\n",
       "40  38  2019-07-09        14  2019-08-04   2\n",
       "41  38  2019-11-12        14  2019-11-22   1\n",
       "42  38  2020-02-17        14        None  31\n",
       "43  39  2020-02-17        14        None  31\n",
       "44  40  2020-02-17        14        None  31"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from emprunter;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.\tCombien de titres \"jeunesse\" ont été empruntés par adhérents ? Commencer par lister les emprunts \"jeunesse\". Attention à compter les titres et pas les emprunts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-69-fd1ea635d281>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-69-fd1ea635d281>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    pd.read_sql_query('SELECT DISTINCT  adherents.NA, oeuvres.titre FROM adherents INNER JOIN emprunter ON emprunter.NA = adherents.NA INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN     oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE categories.categorie = 'Jeunesse';', db)\u001b[0m\n\u001b[1;37m                                                                                                                                                                                                                                                                                                                                                                                      ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT DISTINCT  adherents.NA, oeuvres.titre FROM adherents INNER JOIN emprunter ON emprunter.NA = adherents.NA INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN     oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE categories.categorie = 'Jeunesse';', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6.\tDans la requete précédente, afficher en plus la liste des titres empruntés."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-71-688e9817f00b>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-71-688e9817f00b>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    pd.read_sql_query('SELECT DISTINCT adherents.NA, oeuvres.titre FROM adherents INNER JOIN emprunter ON emprunter.NA = adherents.NA INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE categories.categorie = 'Jeunesse' ORDER BY adherents.NA;', db)\u001b[0m\n\u001b[1;37m                                                                                                                                                                                                                                                                                                                                                                                 ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT DISTINCT adherents.NA, oeuvres.titre FROM adherents INNER JOIN emprunter ON emprunter.NA = adherents.NA INNER JOIN livres ON livres.NL = emprunter.NL INNER JOIN oeuvres ON oeuvres.NO = livres.NO INNER JOIN thematique ON oeuvres.NO = thematique.NO INNER JOIN categories ON thematique.NC = categories.NC WHERE categories.categorie = 'Jeunesse' ORDER BY adherents.NA;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7.\tAfficher le nombre d''emprunts par catégorie."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>categorie</th>\n",
       "      <th>count(datEmp)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Roman</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Jeunesse</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Theatre</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Poesie</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Mathematiques</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Sorciers</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Informatique</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Logique</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>philosophie</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>sciences</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>roman étranger</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>psychologie</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         categorie  count(datEmp)\n",
       "0            Roman             45\n",
       "1         Jeunesse             45\n",
       "2          Theatre             45\n",
       "3           Poesie             45\n",
       "4    Mathematiques             45\n",
       "5         Sorciers             45\n",
       "6     Informatique             45\n",
       "7          Logique             45\n",
       "8      philosophie             45\n",
       "9         sciences             45\n",
       "10  roman étranger             45\n",
       "11     psychologie             45"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT categorie, count(datEmp) From categories INNER JOIN emprunter Group by categorie;', db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "8.\tAfficher la durée moyenne des emprunts par catégorie."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DATEDIFF( dateRet, datEmp )    1.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select categorie, DATEDIFF( dateRet, datEmp ) From categories NATURAL JOIN emprunter Group by categorie;', db).mean().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SERIE 5 : La bibliothèque - Jointures externes et/ou requêtes imbriquées"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.\tQuels sont les adhérents qui n’ont jamais emprunté de livres ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nom</th>\n",
       "      <th>prenom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Martin</td>\n",
       "      <td>Laure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Dufour</td>\n",
       "      <td>Stéphanie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Grecault</td>\n",
       "      <td>Philippe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>Astrid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Espelette</td>\n",
       "      <td>Jean-Jacques</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         nom        prenom\n",
       "0     Martin         Laure\n",
       "1     Dufour     Stéphanie\n",
       "2   Grecault      Philippe\n",
       "3    Johnson        Astrid\n",
       "4  Espelette  Jean-Jacques"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select nom, prenom from adherents left join emprunter on adherents.NA = emprunter.NA where emprunter.NA is null;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.\tQuels sont les livres qui n’ont jamais été empruntés ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.read_sql_query('select NL, editeur from livres left join emprunter on livres.NL = emprunter.NL where emprunter.NL is null;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.\tQuels sont les oeuvres qui n’ont jamais été empruntés ?\n",
    "3bis.\tQuels sont les livres qui n’ont jamais été empruntés : afficher l'oeuvre"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>titre</th>\n",
       "      <th>NO</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Le Rouge et le Noir</td>\n",
       "      <td>7</td>\n",
       "      <td>STENDHAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>L'éthique</td>\n",
       "      <td>13</td>\n",
       "      <td>Baruch SPINOZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Au coeur des ténèbres</td>\n",
       "      <td>17</td>\n",
       "      <td>Joseph CONRAD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Histoire d'algorithmes</td>\n",
       "      <td>22</td>\n",
       "      <td>Jean-Luc CHABERT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Harry Potter, I : Harry Potter à l'école des s...</td>\n",
       "      <td>23</td>\n",
       "      <td>Joanne Kathleen ROWLING</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               titre  NO  \\\n",
       "0                                Le Rouge et le Noir   7   \n",
       "1                                          L'éthique  13   \n",
       "2                              Au coeur des ténèbres  17   \n",
       "3                             Histoire d'algorithmes  22   \n",
       "4  Harry Potter, I : Harry Potter à l'école des s...  23   \n",
       "\n",
       "                    auteur  \n",
       "0                 STENDHAL  \n",
       "1           Baruch SPINOZA  \n",
       "2            Joseph CONRAD  \n",
       "3         Jean-Luc CHABERT  \n",
       "4  Joanne Kathleen ROWLING  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select titre, NO, auteur from oeuvres left join emprunter on oeuvres.NO = emprunter.NL where emprunter.NL is null;', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4.\tCombien d’exemplaires du titre : « Narcisse et Goldmund » sont disponibles ? \n",
    "\n",
    "a) On commencera par afficher tous les Narcisse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "      <th>NO</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-03-08</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-09-20</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax     dateRet  NA  NO                 titre  \\\n",
       "0   1  2019-03-07        21  2019-03-08  26   1  Narcisse et Goldmund   \n",
       "1   1  2019-09-20        14  2019-10-01   3   1  Narcisse et Goldmund   \n",
       "2   1  2019-11-06        14  2019-11-11   2   1  Narcisse et Goldmund   \n",
       "3   1  2020-01-23        14  2020-01-28   1   1  Narcisse et Goldmund   \n",
       "4   1  2020-02-10        14        None  28   1  Narcisse et Goldmund   \n",
       "\n",
       "          auteur  \n",
       "0  Hermann HESSE  \n",
       "1  Hermann HESSE  \n",
       "2  Hermann HESSE  \n",
       "3  Hermann HESSE  \n",
       "4  Hermann HESSE  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM emprunter natural join oeuvres WHERE titre = \"Narcisse et Goldmund\"', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "b) Puis tous les Narcisses actuellement empruntés"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "      <th>NO</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL      datEmp  dureeMax dateRet  NA  NO                 titre  \\\n",
       "0   1  2020-02-10        14    None  28   1  Narcisse et Goldmund   \n",
       "1   2  2020-02-10        14    None  28   1  Narcisse et Goldmund   \n",
       "2   3  2020-01-21        14    None  22   1  Narcisse et Goldmund   \n",
       "3   5  2020-02-15        14    None  16   1  Narcisse et Goldmund   \n",
       "4   9  2020-02-10        14    None  28   1  Narcisse et Goldmund   \n",
       "\n",
       "          auteur  \n",
       "0  Hermann HESSE  \n",
       "1  Hermann HESSE  \n",
       "2  Hermann HESSE  \n",
       "3  Hermann HESSE  \n",
       "4  Hermann HESSE  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM emprunter natural join oeuvres WHERE titre = \"Narcisse et Goldmund\" AND dateRet is null', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "c) Puis les Narcisses disponibles avec une requête imbriquée"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-1-a34928b5a6b0>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-1-a34928b5a6b0>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    pd.read_sql_query('SELECT * FROM livres, oeuvres WHERE livres.NO = oeuvres.NO and upper(oeuvres.titre) = \"Narcisse et Goldmund\" and livres.NL not IN (select livres.NL) FROM emprunter, livres, oeuvres Where emprunter.NL = livres.NO = oeuvres.NO AND upper(oeuvres.titre) = \"Narcisse et Goldmund\";'), db).head(30)\u001b[0m\n\u001b[1;37m                                                                                                                                                                                                                                                                                                                ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM livres, oeuvres WHERE livres.NO = oeuvres.NO and upper(oeuvres.titre) = \"Narcisse et Goldmund\" and livres.NL not IN (select livres.NL) FROM emprunter, livres, oeuvres Where emprunter.NL = livres.NO = oeuvres.NO AND upper(oeuvres.titre) = \"Narcisse et Goldmund\";'), db).head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "d) Enfin les Narcisse disponibles avec une jointure externes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NO</th>\n",
       "      <th>titre</th>\n",
       "      <th>auteur</th>\n",
       "      <th>NL</th>\n",
       "      <th>datEmp</th>\n",
       "      <th>dureeMax</th>\n",
       "      <th>dateRet</th>\n",
       "      <th>NA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-03-08</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-09-20</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>Hermann HESSE</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NO                 titre         auteur  NL      datEmp  dureeMax  \\\n",
       "0   1  Narcisse et Goldmund  Hermann HESSE   1  2019-03-07        21   \n",
       "1   1  Narcisse et Goldmund  Hermann HESSE   1  2019-09-20        14   \n",
       "2   1  Narcisse et Goldmund  Hermann HESSE   1  2019-11-06        14   \n",
       "3   1  Narcisse et Goldmund  Hermann HESSE   1  2020-01-23        14   \n",
       "\n",
       "      dateRet  NA  \n",
       "0  2019-03-08  26  \n",
       "1  2019-10-01   3  \n",
       "2  2019-11-11   2  \n",
       "3  2020-01-28   1  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * FROM oeuvres LEFT JOIN emprunter ON oeuvres.NO = emprunter.NL WHERE titre = \"Narcisse et Goldmund\" AND dateRet is not null;', db).head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SERIE 6 : La bibliothèque - Requêtes avancées"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.\tPour le titre « Narcisse et Goldmund » afficher dans une même requête : \n",
    "\t\t- le nombre d’exemplaires total, \n",
    "\t\t- le nombre d’exemplaires disponibles \n",
    "\t\t- et le nombre d’exemplaires actuellement empruntés."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.read_sql_query(';', db).head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.\tQuelle est la moyenne du nombre de livres empruntés par adhérent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NL</th>\n",
       "      <th>titre</th>\n",
       "      <th>NA</th>\n",
       "      <th>nom</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>1</td>\n",
       "      <td>Lecoeur</td>\n",
       "      <td>3150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>3</td>\n",
       "      <td>Dupont</td>\n",
       "      <td>1575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>4</td>\n",
       "      <td>Mercier</td>\n",
       "      <td>1575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>5</td>\n",
       "      <td>Léger</td>\n",
       "      <td>1575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Narcisse et Goldmund</td>\n",
       "      <td>6</td>\n",
       "      <td>Martin</td>\n",
       "      <td>1575</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NL                 titre  NA      nom  count(*)\n",
       "0   1  Narcisse et Goldmund   1  Lecoeur      3150\n",
       "1   1  Narcisse et Goldmund   3   Dupont      1575\n",
       "2   1  Narcisse et Goldmund   4  Mercier      1575\n",
       "3   1  Narcisse et Goldmund   5    Léger      1575\n",
       "4   1  Narcisse et Goldmund   6   Martin      1575"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('Select E.NL, O.titre, A.NA, A.nom, count(*) From emprunter E, livres L, adherents A, oeuvres O Where E.NL = L.NL Group by A.nom; ', db).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.\tRefaire la question 2 pour tous les titres actuellement sortis au moins une fois."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****************************************************************************\n",
    "****************************************************************************\n",
    "****************************************************************************\n",
    "****************************************************************************"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}