diff --git a/SQL-FusionEmboitement.ipynb b/SQL-FusionEmboitement.ipynb index ec9d0f8..77126e4 100644 --- a/SQL-FusionEmboitement.ipynb +++ b/SQL-FusionEmboitement.ipynb @@ -1,549 +1,225 @@ { "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": 1, + "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": 2, + "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": 3, + "execution_count": null, "id": "949ae7ba-22e4-486d-9343-fd435d53eb8f", "metadata": {}, - "outputs": [ - { - "data": { - "text/html": [ - "
\n", - "\n", - "\n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - "
name
0spatial_ref_sys
1spatialite_history
2sqlite_sequence
3geometry_columns
4views_geometry_columns
5virts_geometry_columns
6geometry_columns_statistics
7views_geometry_columns_statistics
8virts_geometry_columns_statistics
9geometry_columns_field_infos
10views_geometry_columns_field_infos
11virts_geometry_columns_field_infos
12geometry_columns_time
13geometry_columns_auth
14views_geometry_columns_auth
15virts_geometry_columns_auth
16sql_statements_log
17SpatialIndex
18ExcelStork
19Stork
20Migration
21Point
22Member
23MemberStork
24observations
\n", - "
" - ], - "text/plain": [ - " name\n", - "0 spatial_ref_sys\n", - "1 spatialite_history\n", - "2 sqlite_sequence\n", - "3 geometry_columns\n", - "4 views_geometry_columns\n", - "5 virts_geometry_columns\n", - "6 geometry_columns_statistics\n", - "7 views_geometry_columns_statistics\n", - "8 virts_geometry_columns_statistics\n", - "9 geometry_columns_field_infos\n", - "10 views_geometry_columns_field_infos\n", - "11 virts_geometry_columns_field_infos\n", - "12 geometry_columns_time\n", - "13 geometry_columns_auth\n", - "14 views_geometry_columns_auth\n", - "15 virts_geometry_columns_auth\n", - "16 sql_statements_log\n", - "17 SpatialIndex\n", - "18 ExcelStork\n", - "19 Stork\n", - "20 Migration\n", - "21 Point\n", - "22 Member\n", - "23 MemberStork\n", - "24 observations" - ] - }, - "metadata": {}, - "output_type": "display_data" - } - ], + "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": 4, + "execution_count": null, "id": "ba3d8cff-de55-4bed-a726-a52d312a333c", "metadata": {}, - "outputs": [ - { - "data": { - "text/html": [ - "
\n", - "\n", - "\n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - "
namedepartureyear
0Annamarie2001
1Annamarie2002
2Annamarie2003
3Annamarie2004
4Annamarie2005
\n", - "
" - ], - "text/plain": [ - " name departureyear\n", - "0 Annamarie 2001\n", - "1 Annamarie 2002\n", - "2 Annamarie 2003\n", - "3 Annamarie 2004\n", - "4 Annamarie 2005" - ] - }, - "metadata": {}, - "output_type": "display_data" - } - ], + "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": 5, + "execution_count": null, "id": "08fc5f61-202a-4e10-a0f6-c06333274b9b", "metadata": {}, - "outputs": [ - { - "data": { - "text/html": [ - "
\n", - "\n", - "\n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - " \n", - "
name
0Annamarie
1Anneliese
2Christina
3Felix
4Jonas
5Penelope
6Prinzesschen
7Sophia
8Valinka
\n", - "
" - ], - "text/plain": [ - " name\n", - "0 Annamarie\n", - "1 Anneliese\n", - "2 Christina\n", - "3 Felix\n", - "4 Jonas\n", - "5 Penelope\n", - "6 Prinzesschen\n", - "7 Sophia\n", - "8 Valinka" - ] - }, - "metadata": {}, - "output_type": "display_data" - } - ], + "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": 6, + "execution_count": null, "id": "203d28d4-16da-4dea-a0d1-4cfb2d74aa10", "metadata": {}, - "outputs": [ - { - "ename": "AssertionError", - "evalue": "Le résultat de vote requête a 9 ligne, alors que la solution a 4 lignes.", - "output_type": "error", - "traceback": [ - "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", - "\u001b[0;31mAssertionError\u001b[0m Traceback (most recent call last)", - "\u001b[0;32m/tmp/ipykernel_768/2847005286.py\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32massert\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresulttable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m4\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mf'Le résultat de vote requête a {len(resulttable.index)} ligne, alors que la solution a 4 lignes.'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2\u001b[0m \u001b[0;32massert\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresulttable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mf'Le résultat de vote requête a {len(resulttable.columns)} colonnes, alors que la solution a 1 colonne (la colonne \"name\").'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Le résultat de vote requête a le bon nombre de lignes (4) et de colonnes (1), c'est un bon début !\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", - "\u001b[0;31mAssertionError\u001b[0m: Le résultat de vote requête a 9 ligne, alors que la solution a 4 lignes." - ] - } - ], + "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 }