{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3f467ad7-4bdd-4ce9-86f6-738f90481072",
   "metadata": {},
   "source": [
    "# Exercice SQL – fusion et emboitement de requêtes\n",
    "\n",
    "Cette série d'exercices a pour but de vous permettre de **pratiquer l'écriture de requêtes SQL avec les opérateurs ensemblistes**.  \n",
    "Nous allons utiliser :\n",
    "* la base de donnée QSpatiaLite concernant les cigognes que nous avons utilisée précédemment\n",
    "* le language de programmation Python afin d'interroger cette base de données.\n",
    "\n",
    "## Objectifs\n",
    "\n",
    "A la fin de cette série d'exercices vous devriez être en mesure de :\n",
    "* distinguer les différents opérateurs ensemblistes et leurs effets\n",
    "* écrire des requêtes réalisant des opérations logiques avec ces opérateurs\n",
    "* interroger une base de données avec Python\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "301c618a-d9d4-47a5-bdb3-16a29f350a28",
   "metadata": {
    "tags": []
   },
   "source": [
    "# 1. Accès à la base de données\n",
    "Pour manipuler une base de donnée avec Python nous avons besoin des librairies `sqlite3` et `pandas`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1661fb65-c207-4d64-8163-d2e648744eeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing libraries\n",
    "import sqlite3\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83333b57-f511-4ccf-8c65-9819d4a29d5e",
   "metadata": {},
   "source": [
    "D'abord on établit une connexion à la base de données :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ae2db69-8429-4dea-949f-8457ccf0b6e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Etablir la connexion à la base de données\n",
    "con = sqlite3.connect(\"stork-mooc.sqlite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "217ca30f-8d8a-41f4-a4c0-1819d81c6f14",
   "metadata": {},
   "source": [
    "Pour lister toutes les tables disponibles, on peut utiliser la table `sqlite_master` avec la requete suivante :\n",
    "\n",
    "```SELECT name FROM sqlite_master WHERE type='table';```\n",
    "\n",
    "Pour exécuter cette requête avec Python, on utilise la librairie `pandas` et sa fonction `read_sql_query` qui nous retourne directement une table.\n",
    "Pour afficher cette table il suffit ensuite de faire un `display`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "949ae7ba-22e4-486d-9343-fd435d53eb8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Donner la requête à executer\n",
    "query = \"SELECT name FROM sqlite_master WHERE type='table';\"\n",
    "\n",
    "# Executer la requête et récupérer la table résultat\n",
    "resulttable = pandas.read_sql_query(query, con)\n",
    "\n",
    "# Afficher la table résultat\n",
    "display(resulttable)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b3f5582-f7e5-4857-92b5-fb8cab5b26e5",
   "metadata": {},
   "source": [
    "De la même manière on peut afficher le détail de la table \"Migration\".  \n",
    "Si on veut limiter l'affichage aux 5 premières lignes, il suffit d'ajouter `.head()` sur la table résultat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba3d8cff-de55-4bed-a726-a52d312a333c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Donner la requête à executer\n",
    "query = \"select * from migration\"\n",
    "\n",
    "# Executer la requête et récupérer la table résultat\n",
    "resulttable = pandas.read_sql_query(query, con)\n",
    "\n",
    "# Afficher les 5 premières lignes de la table résultat\n",
    "display(resulttable.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef5254fa-db75-4232-b380-8db6a581bebb",
   "metadata": {},
   "source": [
    "# 2. Exercices avec SQL\n",
    "\n",
    "Pour tous les exercices suivants, **donnez la syntaxe SQL pour les requêtes demandées et testez vos requêtes.**  \n",
    "Afin que vos réponses puissent être corrigées adéquatement, veuillez suivre les quelques règles suivantes :\n",
    "* Quand plusieurs attributs sont demandés, gardez l’ordre dans lequel ils apparaissent dans la question\n",
    "* N’utilisez pas d’alias du nom de colonne ou table (sauf lorsque c’est demandé)\n",
    "* Ne rajoutez pas de clause telle que WHERE, GROUP BY, ORDER BY, LIMIT\n",
    "\n",
    "## Exercice 1\n",
    "\n",
    "**Question**: Donnez le nom de toutes les cigognes qui ont migré à la fois en 2001 et en 2003 (en utilisant UNION, EXCEPT ou INTERSECT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08fc5f61-202a-4e10-a0f6-c06333274b9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Donner la requête à executer\n",
    "query = \"select * from migration\" # TODO: MODIFIER LA REQUETE ICI\n",
    "\n",
    "# Executer la requête et récupérer la table résultat\n",
    "resulttable = pandas.read_sql_query(query, con)\n",
    "\n",
    "# Afficher la table résultat\n",
    "display(resulttable)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99f9d5ab-f4d6-41c2-b636-607bb0c1afdc",
   "metadata": {},
   "source": [
    "**Self-checks**\n",
    "\n",
    "Vous pouvez executer la cellule suivante qui va vérifier automatiquement quelques caractéristiques du résultat de votre requête.  \n",
    "Ces tests ne sont pas suffisants pour assurer que vous avez la bonne réponse, mais ils vont vous permettre de savoir si vous êtes sur la bonne voie."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "203d28d4-16da-4dea-a0d1-4cfb2d74aa10",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(resulttable.index) == 4, f'Le résultat de vote requête a {len(resulttable.index)} ligne, alors que la solution a 4 lignes.'\n",
    "assert len(resulttable.columns) == 1, f'Le résultat de vote requête a {len(resulttable.columns)} colonnes, alors que la solution a 1 colonne (la colonne \"name\").'\n",
    "print(\"Le résultat de vote requête a le bon nombre de lignes (4) et de colonnes (1), c'est un bon début !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ac9efb7-ea81-4663-80e2-ab78ac61b5b6",
   "metadata": {},
   "source": [
    "**Validation**\n",
    "\n",
    "Executez la cellule suivante afin de savoir si votre requête est correcte ou non.  \n",
    "Lisez attentivement les éventuels messages d'erreur afin de corriger votre requête."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "839e19f7-bdc8-494d-9fae-b41e3035e4a1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from ExerciceSQL import *\n",
    "question1_validation(query)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}