{ "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ées QSpatiaLite concernant les cigognes que nous avons utilisée précédemment\n", "* SQL pour écrire les requêtes sur cette base de données\n", "* le language de programmation Python afin d'interroger cette base de données (exécuter les requêtes)\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 de SQL et leurs effets\n", "* écrire des requêtes SQL réalisant des opérations logiques avec ces opérateurs\n", "* interroger une base de données avec Python\n", "\n", "
\n", "
\n", " Comment utiliser ce notebook?
\n", " Ce notebook est composé de cellules de text et de cellules de code (Python). Les cellules de code doivent être exécutées afin de voir le résultat du programme.
Pour exécuter une cellule, sélectionnez-la puis cliquez simplement sur le bouton \"play\" (►) dans la barre d'outils au dessus du notebook, ou tapez shift + entrée.
Il est important d'exécuter les cellules de code dans l'ordre dans lequel elles apparaissent dans le notebook.\n", "
\n" ] }, { "cell_type": "markdown", "id": "a08280d1-df23-4e8a-ae6f-64b55897c079", "metadata": {}, "source": [ "---\n", "---" ] }, { "cell_type": "markdown", "id": "4c50fb80-2bb2-4b29-8ead-05766a824b17", "metadata": { "tags": [] }, "source": [ "# Accès à la base de données avec Python\n", "\n", "Dans cette première partie, nous vous montrons par l'exemple comment écrire et exécuter vos requêtes sur notre base de données des cigognes avec Python." ] }, { "cell_type": "markdown", "id": "e3d8815d-b67b-4d99-90bb-455a66a8cbfa", "metadata": { "tags": [] }, "source": [ "## Import des libraries Python\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": [ "## Connexion à la base de données \n", "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": "8006f1ac-8e69-48ac-84bb-879ae9ad2ffd", "metadata": {}, "source": [ "## Execution des requêtes SQL\n", "\n", "Pour exécuter une requête SQL sur notre base de données avec Python, on utilise la fonction `read_sql_query` de la librairie `pandas` qui :\n", "* execute la requête\n", "* nous retourne la table résultat\n", "Pour afficher cette table il suffit ensuite de faire un `display`.\n", "\n", "Voici un exemple, qui execute la requête `select * from migration` et affiche le 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 la table résultat\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "810b6945-370b-4f7a-993a-1bb6ed83138e", "metadata": {}, "source": [ "
\n", "\n", "**Question** : modifiez la requête ci-dessus de manière à récupérer le contenu de la table `stork`." ] }, { "cell_type": "markdown", "id": "ecb47a7a-a6dd-44b4-9ce1-28e8d491b7e2", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "df308bbe-b320-4ce9-8724-4b6b27908bc6", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`select * from stork`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "ce2931e6-8935-407c-9f03-2dbdbce7f33c", "metadata": {}, "source": [ "---\n", "---" ] }, { "cell_type": "markdown", "id": "507ced8b-8ac0-4361-91f8-86d57a4b0659", "metadata": {}, "source": [ "# 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", "\n", "Pour rappel, voici ci-dessous le schéma UML de notre base de données sur les cignognes. \n", "Attention, les noms des tables et des colonnes sont *en anglais* dans la base de données !\n", "\n", "\"Schéma" ] }, { "cell_type": "markdown", "id": "cdef6666-e9c7-4614-8d89-95ea6629d98d", "metadata": {}, "source": [ "---\n", "\n", "## Exercice 1\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)\n", "\n", "Voici comment procéder pour tester votre requête : \n", "1. Modifiez la requête dans la cellule ci-dessous puis exécutez la cellule pour voir le résultat. \n", "Vous pouvez modifier et ré-exécuter la cellule autant de fois que nécessaire. \n", "2. Une fois que vous êtes satisfait.e de votre requête, vous pouvez exécuter la cellule de validation ci-dessous pour véfifier si votre requête est correcte ou non. \n", "3. Enfin, vous pouvez ensuite comparer votre requête avec la solution." ] }, { "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": "39f68f2a-4743-4d08-ae3e-3947318e4c52", "metadata": { "tags": [] }, "source": [ "**Besoin d'un indice ?** Cliquez sur les trois petits points \"...\" ci-dessous.\n" ] }, { "cell_type": "markdown", "id": "2cbcdf75-f154-4f3c-86b2-dce797949ff9", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Le résultat de votre requête doit avoir 4 lignes et 1 colonne. \n", "Afin de déterminer le bon opérateur logique à utiliser, il peut être utile de faire un petit schéma sur le papier. \n", "
\n" ] }, { "cell_type": "markdown", "id": "6ac9efb7-ea81-4663-80e2-ab78ac61b5b6", "metadata": {}, "source": [ "**Validation** : 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 SQLFusionEmboitement_v2 import *\n", "question1_validation(query)" ] }, { "cell_type": "markdown", "id": "dc9e0a45-ab04-44d6-94e5-5a38e02db8b1", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "3248b00b-42fc-4043-849d-ce206ef3d94e", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`select name from migration where departureyear=2001 INTERSECT select name from migration where departureyear=2003`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "5ded8691-5cc7-4ffd-8e7a-8b9e68ec9b28", "metadata": {}, "source": [ "---\n", "\n", "## Exercice 2\n", "\n", "**Question** : Donnez le nom de toutes les cigognes qui ont migré en 2001 mais pas en 2003 (en utilisant UNION, EXCEPT ou INTERSECT)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "0eef1a44-4781-4011-8dc2-cea0c48b4ccb", "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": "347940dd-1971-43d5-800a-fac60a9a9d5a", "metadata": { "tags": [] }, "source": [ "**Besoin d'un indice ?** Cliquez sur les trois petits points \"...\" ci-dessous.\n" ] }, { "cell_type": "markdown", "id": "366ec993-8a67-411b-b7fc-2baafb6eb2bd", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Le résultat de votre requête doit avoir 4 lignes et 1 colonne. \n", "Afin de déterminer le bon opérateur logique à utiliser, il peut être utile de faire un petit schéma sur le papier. \n", "
\n" ] }, { "cell_type": "markdown", "id": "e48b0894-c785-4b8e-a87e-e90baac5bdd7", "metadata": {}, "source": [ "**Validation** : 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": "b60cc09c-cbe5-4055-bdbf-dff6ef205faa", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQLFusionEmboitement_v2 import *\n", "question2_validation(query)" ] }, { "cell_type": "markdown", "id": "8764c3f4-57c1-4690-a841-ef558b20e36f", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "74758991-7c8a-49be-8c23-d99f3505b03b", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`select name from migration where departureyear=2001 EXCEPT select name from migration where departureyear=2003`\n", " \n", "
\n" ] } ], "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 }