{ "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 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": "39f68f2a-4743-4d08-ae3e-3947318e4c52", "metadata": { "tags": [] }, "source": [ "