diff --git a/SQLFusionEmboitement_v2.ipynb b/SQLFusionEmboitement_v2.ipynb
index 864ac51..bebecd0 100644
--- a/SQLFusionEmboitement_v2.ipynb
+++ b/SQLFusionEmboitement_v2.ipynb
@@ -1,738 +1,430 @@
{
"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": 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": [
"## Connexion à la base de données \n",
"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": "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": 6,
+ "execution_count": null,
"id": "ba3d8cff-de55-4bed-a726-a52d312a333c",
"metadata": {},
- "outputs": [
- {
- "data": {
- "text/html": [
- "\n",
- "\n",
- "
\n",
- " \n",
- " \n",
- " | \n",
- " name | \n",
- " departureyear | \n",
- "
\n",
- " \n",
- " \n",
- " \n",
- " 0 | \n",
- " Annamarie | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 1 | \n",
- " Annamarie | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 2 | \n",
- " Annamarie | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 3 | \n",
- " Annamarie | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 4 | \n",
- " Annamarie | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " 5 | \n",
- " Annamarie | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " 6 | \n",
- " Anneliese | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 7 | \n",
- " Anneliese | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 8 | \n",
- " Christina | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 9 | \n",
- " Christina | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 10 | \n",
- " Donna | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 11 | \n",
- " Donna | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 12 | \n",
- " Felix | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 13 | \n",
- " Felix | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 14 | \n",
- " Felix | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 15 | \n",
- " Felix | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 16 | \n",
- " Johann | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 17 | \n",
- " Jonas | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 18 | \n",
- " Jonas | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 19 | \n",
- " Jonas | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 20 | \n",
- " Jonas | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 21 | \n",
- " Louka | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 22 | \n",
- " Louka | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 23 | \n",
- " Max | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 24 | \n",
- " Moritz | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 25 | \n",
- " Penelope | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 26 | \n",
- " Penelope | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 27 | \n",
- " Penelope | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 28 | \n",
- " Penelope | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 29 | \n",
- " Peterchen | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 30 | \n",
- " Peterchen | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 31 | \n",
- " Peterchen | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 32 | \n",
- " Prinzesschen | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 33 | \n",
- " Prinzesschen | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 34 | \n",
- " Prinzesschen | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 35 | \n",
- " Prinzesschen | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 36 | \n",
- " Prinzesschen | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " 37 | \n",
- " Prinzesschen | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " 38 | \n",
- " Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 39 | \n",
- " Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 40 | \n",
- " Sohn von Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 41 | \n",
- " Sohn von Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 42 | \n",
- " Sophia | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 43 | \n",
- " Sophia | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 44 | \n",
- " Valinka | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- "
\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\n",
- "5 Annamarie 2006\n",
- "6 Anneliese 2000\n",
- "7 Anneliese 2001\n",
- "8 Christina 2002\n",
- "9 Christina 2003\n",
- "10 Donna 1999\n",
- "11 Donna 2000\n",
- "12 Felix 2001\n",
- "13 Felix 2002\n",
- "14 Felix 2003\n",
- "15 Felix 2004\n",
- "16 Johann 2002\n",
- "17 Jonas 2001\n",
- "18 Jonas 2002\n",
- "19 Jonas 2003\n",
- "20 Jonas 2004\n",
- "21 Louka 1999\n",
- "22 Louka 2000\n",
- "23 Max 2000\n",
- "24 Moritz 2000\n",
- "25 Penelope 1998\n",
- "26 Penelope 1999\n",
- "27 Penelope 2000\n",
- "28 Penelope 2001\n",
- "29 Peterchen 1998\n",
- "30 Peterchen 1999\n",
- "31 Peterchen 2000\n",
- "32 Prinzesschen 2001\n",
- "33 Prinzesschen 2002\n",
- "34 Prinzesschen 2003\n",
- "35 Prinzesschen 2004\n",
- "36 Prinzesschen 2005\n",
- "37 Prinzesschen 2006\n",
- "38 Prinzessin 1998\n",
- "39 Prinzessin 1999\n",
- "40 Sohn von Prinzessin 1998\n",
- "41 Sohn von Prinzessin 1999\n",
- "42 Sophia 2001\n",
- "43 Sophia 2002\n",
- "44 Valinka 2001"
- ]
- },
- "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 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",
""
]
},
{
"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
}