Partie 3 · Requêtes LoveBook

Joindre les tables

La section « JOINT » du TP : nom de l'utilisateur et titre de l'événement (5.a, 5.b), photos par album (5.c), covoiturages par événement (5.d)… Une jointure relie deux tables par leurs clés : JOIN evenements e ON e.id = p.evenement_id. Le cours de la partie 2 écrivait FROM A, B WHERE A.x = B.x ; JOIN … ON est la forme moderne équivalente.

Jointure	Résultat
`JOIN` (interne)	seulement les lignes qui ont une correspondance des deux côtés
`LEFT JOIN`	toutes les lignes de gauche, avec `NULL` à droite quand rien ne correspond

Pour compter « 0 » quand il n'y a rien, utilise LEFT JOIN et COUNT(colonne_de_droite) : COUNT(*) compterait 1 la ligne vide.

À toi de coder

Qui a participé à quoi, à Paris

La base LoveBook est déjà créée dans le code (tables au pluriel : utilisateurs, groupes, evenements, participations, albums, photos, types_billets, billets, covoiturages ; voir la leçon 1).

Questions 5.a et 5.b : « le nom de l'utilisateur et le titre de l'événement pour tous les événements auxquels l'utilisateur a participé, qui ont lieu dans la ville de Paris ». La table participations relie les deux entités : c'est une CIM devenue table (partie 1). Il faut donc joindre trois tables.

Renvoie des tuples (nom, titre), triés par titre puis par nom, dans res.

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE utilisateurs (id INTEGER PRIMARY KEY, nom TEXT, prenom TEXT, email TEXT UNIQUE, ville TEXT);
CREATE TABLE groupes (id INTEGER PRIMARY KEY, nom TEXT, type TEXT);
CREATE TABLE evenements (id INTEGER PRIMARY KEY, titre TEXT, ville TEXT, date_debut TEXT, annule INTEGER DEFAULT 0, groupe_id INTEGER REFERENCES groupes(id));
CREATE TABLE participations (utilisateur_id INTEGER, evenement_id INTEGER, role TEXT, note INTEGER, PRIMARY KEY (utilisateur_id, evenement_id));
CREATE TABLE albums (id INTEGER PRIMARY KEY, evenement_id INTEGER, titre TEXT);
CREATE TABLE photos (id INTEGER PRIMARY KEY, album_id INTEGER, utilisateur_id INTEGER, legende TEXT);
CREATE TABLE types_billets (id INTEGER PRIMARY KEY, evenement_id INTEGER, nom TEXT, montant REAL, quantite INTEGER);
CREATE TABLE billets (id INTEGER PRIMARY KEY, type_billet_id INTEGER, nom TEXT, prenom TEXT, date_achat TEXT);
CREATE TABLE covoiturages (id INTEGER PRIMARY KEY, evenement_id INTEGER, utilisateur_id INTEGER, ville_depart TEXT, places INTEGER);
INSERT INTO utilisateurs VALUES (1,'Dubois','Camille','camille@ex.fr','Paris'),(2,'Dupont','Jean','jean@ex.fr','Lyon'),(3,'Martin','Alice','alice@ex.fr','Paris'),(4,'Leroy','Sam','sam@ex.fr','Lille'),(5,'Moreau','Zoé','zoe@ex.fr','Paris'),(6,'Petit','Hugo','hugo@ex.fr','Lille');
INSERT INTO groupes VALUES (1,'Amis de la nature','public'),(2,'Cinéphiles','prive'),(3,'Randonneurs oubliés','secret');
INSERT INTO evenements VALUES (1,'Fête de la musique','Paris','2021-06-21',0,NULL),(2,'Randonnée du printemps','Lyon','2022-04-10',0,1),(3,'Festival de la bière','Lille','2023-09-02',0,NULL),(4,'Soirée karaoké','Paris','2022-11-05',1,2),(5,'Sortie vélo','Lyon','2021-05-15',0,1);
INSERT INTO participations VALUES (1,1,'organisateur',NULL),(2,1,'participant',5),(3,1,'participant',4),(4,1,'participant',2),(5,1,'participant',4),
 (1,2,'organisateur',NULL),(2,2,'participant',4),(3,2,'participant',3),(4,2,'participant',5),
 (2,3,'organisateur',NULL),(3,3,'participant',4),(5,3,'participant',3),
 (3,4,'organisateur',NULL),(4,4,'participant',NULL),(1,4,'participant',NULL),
 (1,5,'organisateur',NULL),(2,5,'participant',3),(3,5,'participant',5);
INSERT INTO albums VALUES (1,1,'Fête 2021'),(2,2,'Rando photos'),(3,2,'Panoramas'),(4,4,'Karaoké'),(5,3,'Bières');
INSERT INTO photos VALUES (1,1,2,'Scène'),(2,1,3,'Foule'),(3,1,3,'Feu d''artifice'),(4,2,2,'Sommet'),(5,2,4,'Pique-nique'),(6,3,1,'Vue'),(7,4,4,'Micro'),(8,4,1,'Duo'),(9,4,1,'Final'),(10,NULL,5,'Orpheline');
INSERT INTO types_billets VALUES (1,1,'Pass',10.0,500),(2,3,'Standard',25.0,100),(3,3,'VIP',60.0,20);
INSERT INTO billets VALUES (1,1,'Roux','Léa','2021-06-01'),(2,1,'Roux','Tom','2021-06-01'),(3,1,'Blanc','Eva','2021-06-02'),(4,1,'Noir','Max','2021-06-03'),(5,1,'Vert','Ana','2021-06-04'),(6,1,'Gris','Lou','2021-06-05'),
 (7,2,'Roux','Léa','2023-08-01'),(8,2,'Blanc','Eva','2023-08-02'),(9,2,'Noir','Max','2023-08-03'),(10,2,'Vert','Ana','2023-08-04'),
 (11,3,'Gris','Lou','2023-08-05'),(12,3,'Rose','Sam','2023-08-06');
INSERT INTO covoiturages VALUES (1,3,3,'Paris',3),(2,3,5,'Paris',2),(3,1,4,'Lille',2),(4,4,1,'Paris',3);
""")
res = con.execute("""SELECT u.nom, e.titre
FROM utilisateurs u
JOIN participations p ON p.utilisateur_id = u.id
JOIN evenements e ON e.id = p.___
WHERE e.ville = 'Paris'
ORDER BY e.titre, u.nom""").fetchall()

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE utilisateurs (id INTEGER PRIMARY KEY, nom TEXT, prenom TEXT, email TEXT UNIQUE, ville TEXT);
CREATE TABLE groupes (id INTEGER PRIMARY KEY, nom TEXT, type TEXT);
CREATE TABLE evenements (id INTEGER PRIMARY KEY, titre TEXT, ville TEXT, date_debut TEXT, annule INTEGER DEFAULT 0, groupe_id INTEGER REFERENCES groupes(id));
CREATE TABLE participations (utilisateur_id INTEGER, evenement_id INTEGER, role TEXT, note INTEGER, PRIMARY KEY (utilisateur_id, evenement_id));
CREATE TABLE albums (id INTEGER PRIMARY KEY, evenement_id INTEGER, titre TEXT);
CREATE TABLE photos (id INTEGER PRIMARY KEY, album_id INTEGER, utilisateur_id INTEGER, legende TEXT);
CREATE TABLE types_billets (id INTEGER PRIMARY KEY, evenement_id INTEGER, nom TEXT, montant REAL, quantite INTEGER);
CREATE TABLE billets (id INTEGER PRIMARY KEY, type_billet_id INTEGER, nom TEXT, prenom TEXT, date_achat TEXT);
CREATE TABLE covoiturages (id INTEGER PRIMARY KEY, evenement_id INTEGER, utilisateur_id INTEGER, ville_depart TEXT, places INTEGER);
INSERT INTO utilisateurs VALUES (1,'Dubois','Camille','camille@ex.fr','Paris'),(2,'Dupont','Jean','jean@ex.fr','Lyon'),(3,'Martin','Alice','alice@ex.fr','Paris'),(4,'Leroy','Sam','sam@ex.fr','Lille'),(5,'Moreau','Zoé','zoe@ex.fr','Paris'),(6,'Petit','Hugo','hugo@ex.fr','Lille');
INSERT INTO groupes VALUES (1,'Amis de la nature','public'),(2,'Cinéphiles','prive'),(3,'Randonneurs oubliés','secret');
INSERT INTO evenements VALUES (1,'Fête de la musique','Paris','2021-06-21',0,NULL),(2,'Randonnée du printemps','Lyon','2022-04-10',0,1),(3,'Festival de la bière','Lille','2023-09-02',0,NULL),(4,'Soirée karaoké','Paris','2022-11-05',1,2),(5,'Sortie vélo','Lyon','2021-05-15',0,1);
INSERT INTO participations VALUES (1,1,'organisateur',NULL),(2,1,'participant',5),(3,1,'participant',4),(4,1,'participant',2),(5,1,'participant',4),
 (1,2,'organisateur',NULL),(2,2,'participant',4),(3,2,'participant',3),(4,2,'participant',5),
 (2,3,'organisateur',NULL),(3,3,'participant',4),(5,3,'participant',3),
 (3,4,'organisateur',NULL),(4,4,'participant',NULL),(1,4,'participant',NULL),
 (1,5,'organisateur',NULL),(2,5,'participant',3),(3,5,'participant',5);
INSERT INTO albums VALUES (1,1,'Fête 2021'),(2,2,'Rando photos'),(3,2,'Panoramas'),(4,4,'Karaoké'),(5,3,'Bières');
INSERT INTO photos VALUES (1,1,2,'Scène'),(2,1,3,'Foule'),(3,1,3,'Feu d''artifice'),(4,2,2,'Sommet'),(5,2,4,'Pique-nique'),(6,3,1,'Vue'),(7,4,4,'Micro'),(8,4,1,'Duo'),(9,4,1,'Final'),(10,NULL,5,'Orpheline');
INSERT INTO types_billets VALUES (1,1,'Pass',10.0,500),(2,3,'Standard',25.0,100),(3,3,'VIP',60.0,20);
INSERT INTO billets VALUES (1,1,'Roux','Léa','2021-06-01'),(2,1,'Roux','Tom','2021-06-01'),(3,1,'Blanc','Eva','2021-06-02'),(4,1,'Noir','Max','2021-06-03'),(5,1,'Vert','Ana','2021-06-04'),(6,1,'Gris','Lou','2021-06-05'),
 (7,2,'Roux','Léa','2023-08-01'),(8,2,'Blanc','Eva','2023-08-02'),(9,2,'Noir','Max','2023-08-03'),(10,2,'Vert','Ana','2023-08-04'),
 (11,3,'Gris','Lou','2023-08-05'),(12,3,'Rose','Sam','2023-08-06');
INSERT INTO covoiturages VALUES (1,3,3,'Paris',3),(2,3,5,'Paris',2),(3,1,4,'Lille',2),(4,4,1,'Paris',3);
""")
res = con.execute("""SELECT u.nom, e.titre
FROM utilisateurs u
JOIN participations p ON p.utilisateur_id = u.id
JOIN evenements e ON e.id = p.___
WHERE e.ville = 'Paris'
ORDER BY e.titre, u.nom""").fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Tu veux tous les albums, y compris sans photo, avec leur nombre de photos. Quelle jointure ?

À toi de coder

Nombre de photos par album, albums vides compris

Question 5.c : « le titre de l'album et le nombre de photos associées à chaque album ». Un album sans photo existe (« Bières »). Une jointure ordinaire (JOIN) l'éliminerait ; LEFT JOIN garde toutes les lignes de la table de gauche, avec NULL quand rien ne correspond. COUNT(p.id) ne compte pas les NULL (COUNT(*) compterait 1 pour l'album vide).

Renvoie des tuples (titre, nombre) triés par titre dans res.

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE utilisateurs (id INTEGER PRIMARY KEY, nom TEXT, prenom TEXT, email TEXT UNIQUE, ville TEXT);
CREATE TABLE groupes (id INTEGER PRIMARY KEY, nom TEXT, type TEXT);
CREATE TABLE evenements (id INTEGER PRIMARY KEY, titre TEXT, ville TEXT, date_debut TEXT, annule INTEGER DEFAULT 0, groupe_id INTEGER REFERENCES groupes(id));
CREATE TABLE participations (utilisateur_id INTEGER, evenement_id INTEGER, role TEXT, note INTEGER, PRIMARY KEY (utilisateur_id, evenement_id));
CREATE TABLE albums (id INTEGER PRIMARY KEY, evenement_id INTEGER, titre TEXT);
CREATE TABLE photos (id INTEGER PRIMARY KEY, album_id INTEGER, utilisateur_id INTEGER, legende TEXT);
CREATE TABLE types_billets (id INTEGER PRIMARY KEY, evenement_id INTEGER, nom TEXT, montant REAL, quantite INTEGER);
CREATE TABLE billets (id INTEGER PRIMARY KEY, type_billet_id INTEGER, nom TEXT, prenom TEXT, date_achat TEXT);
CREATE TABLE covoiturages (id INTEGER PRIMARY KEY, evenement_id INTEGER, utilisateur_id INTEGER, ville_depart TEXT, places INTEGER);
INSERT INTO utilisateurs VALUES (1,'Dubois','Camille','camille@ex.fr','Paris'),(2,'Dupont','Jean','jean@ex.fr','Lyon'),(3,'Martin','Alice','alice@ex.fr','Paris'),(4,'Leroy','Sam','sam@ex.fr','Lille'),(5,'Moreau','Zoé','zoe@ex.fr','Paris'),(6,'Petit','Hugo','hugo@ex.fr','Lille');
INSERT INTO groupes VALUES (1,'Amis de la nature','public'),(2,'Cinéphiles','prive'),(3,'Randonneurs oubliés','secret');
INSERT INTO evenements VALUES (1,'Fête de la musique','Paris','2021-06-21',0,NULL),(2,'Randonnée du printemps','Lyon','2022-04-10',0,1),(3,'Festival de la bière','Lille','2023-09-02',0,NULL),(4,'Soirée karaoké','Paris','2022-11-05',1,2),(5,'Sortie vélo','Lyon','2021-05-15',0,1);
INSERT INTO participations VALUES (1,1,'organisateur',NULL),(2,1,'participant',5),(3,1,'participant',4),(4,1,'participant',2),(5,1,'participant',4),
 (1,2,'organisateur',NULL),(2,2,'participant',4),(3,2,'participant',3),(4,2,'participant',5),
 (2,3,'organisateur',NULL),(3,3,'participant',4),(5,3,'participant',3),
 (3,4,'organisateur',NULL),(4,4,'participant',NULL),(1,4,'participant',NULL),
 (1,5,'organisateur',NULL),(2,5,'participant',3),(3,5,'participant',5);
INSERT INTO albums VALUES (1,1,'Fête 2021'),(2,2,'Rando photos'),(3,2,'Panoramas'),(4,4,'Karaoké'),(5,3,'Bières');
INSERT INTO photos VALUES (1,1,2,'Scène'),(2,1,3,'Foule'),(3,1,3,'Feu d''artifice'),(4,2,2,'Sommet'),(5,2,4,'Pique-nique'),(6,3,1,'Vue'),(7,4,4,'Micro'),(8,4,1,'Duo'),(9,4,1,'Final'),(10,NULL,5,'Orpheline');
INSERT INTO types_billets VALUES (1,1,'Pass',10.0,500),(2,3,'Standard',25.0,100),(3,3,'VIP',60.0,20);
INSERT INTO billets VALUES (1,1,'Roux','Léa','2021-06-01'),(2,1,'Roux','Tom','2021-06-01'),(3,1,'Blanc','Eva','2021-06-02'),(4,1,'Noir','Max','2021-06-03'),(5,1,'Vert','Ana','2021-06-04'),(6,1,'Gris','Lou','2021-06-05'),
 (7,2,'Roux','Léa','2023-08-01'),(8,2,'Blanc','Eva','2023-08-02'),(9,2,'Noir','Max','2023-08-03'),(10,2,'Vert','Ana','2023-08-04'),
 (11,3,'Gris','Lou','2023-08-05'),(12,3,'Rose','Sam','2023-08-06');
INSERT INTO covoiturages VALUES (1,3,3,'Paris',3),(2,3,5,'Paris',2),(3,1,4,'Lille',2),(4,4,1,'Paris',3);
""")
res = con.execute("""SELECT a.titre, COUNT(p.id)
FROM albums a
___ JOIN photos p ON p.album_id = a.id
GROUP BY a.id
ORDER BY a.titre""").fetchall()

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE utilisateurs (id INTEGER PRIMARY KEY, nom TEXT, prenom TEXT, email TEXT UNIQUE, ville TEXT);
CREATE TABLE groupes (id INTEGER PRIMARY KEY, nom TEXT, type TEXT);
CREATE TABLE evenements (id INTEGER PRIMARY KEY, titre TEXT, ville TEXT, date_debut TEXT, annule INTEGER DEFAULT 0, groupe_id INTEGER REFERENCES groupes(id));
CREATE TABLE participations (utilisateur_id INTEGER, evenement_id INTEGER, role TEXT, note INTEGER, PRIMARY KEY (utilisateur_id, evenement_id));
CREATE TABLE albums (id INTEGER PRIMARY KEY, evenement_id INTEGER, titre TEXT);
CREATE TABLE photos (id INTEGER PRIMARY KEY, album_id INTEGER, utilisateur_id INTEGER, legende TEXT);
CREATE TABLE types_billets (id INTEGER PRIMARY KEY, evenement_id INTEGER, nom TEXT, montant REAL, quantite INTEGER);
CREATE TABLE billets (id INTEGER PRIMARY KEY, type_billet_id INTEGER, nom TEXT, prenom TEXT, date_achat TEXT);
CREATE TABLE covoiturages (id INTEGER PRIMARY KEY, evenement_id INTEGER, utilisateur_id INTEGER, ville_depart TEXT, places INTEGER);
INSERT INTO utilisateurs VALUES (1,'Dubois','Camille','camille@ex.fr','Paris'),(2,'Dupont','Jean','jean@ex.fr','Lyon'),(3,'Martin','Alice','alice@ex.fr','Paris'),(4,'Leroy','Sam','sam@ex.fr','Lille'),(5,'Moreau','Zoé','zoe@ex.fr','Paris'),(6,'Petit','Hugo','hugo@ex.fr','Lille');
INSERT INTO groupes VALUES (1,'Amis de la nature','public'),(2,'Cinéphiles','prive'),(3,'Randonneurs oubliés','secret');
INSERT INTO evenements VALUES (1,'Fête de la musique','Paris','2021-06-21',0,NULL),(2,'Randonnée du printemps','Lyon','2022-04-10',0,1),(3,'Festival de la bière','Lille','2023-09-02',0,NULL),(4,'Soirée karaoké','Paris','2022-11-05',1,2),(5,'Sortie vélo','Lyon','2021-05-15',0,1);
INSERT INTO participations VALUES (1,1,'organisateur',NULL),(2,1,'participant',5),(3,1,'participant',4),(4,1,'participant',2),(5,1,'participant',4),
 (1,2,'organisateur',NULL),(2,2,'participant',4),(3,2,'participant',3),(4,2,'participant',5),
 (2,3,'organisateur',NULL),(3,3,'participant',4),(5,3,'participant',3),
 (3,4,'organisateur',NULL),(4,4,'participant',NULL),(1,4,'participant',NULL),
 (1,5,'organisateur',NULL),(2,5,'participant',3),(3,5,'participant',5);
INSERT INTO albums VALUES (1,1,'Fête 2021'),(2,2,'Rando photos'),(3,2,'Panoramas'),(4,4,'Karaoké'),(5,3,'Bières');
INSERT INTO photos VALUES (1,1,2,'Scène'),(2,1,3,'Foule'),(3,1,3,'Feu d''artifice'),(4,2,2,'Sommet'),(5,2,4,'Pique-nique'),(6,3,1,'Vue'),(7,4,4,'Micro'),(8,4,1,'Duo'),(9,4,1,'Final'),(10,NULL,5,'Orpheline');
INSERT INTO types_billets VALUES (1,1,'Pass',10.0,500),(2,3,'Standard',25.0,100),(3,3,'VIP',60.0,20);
INSERT INTO billets VALUES (1,1,'Roux','Léa','2021-06-01'),(2,1,'Roux','Tom','2021-06-01'),(3,1,'Blanc','Eva','2021-06-02'),(4,1,'Noir','Max','2021-06-03'),(5,1,'Vert','Ana','2021-06-04'),(6,1,'Gris','Lou','2021-06-05'),
 (7,2,'Roux','Léa','2023-08-01'),(8,2,'Blanc','Eva','2023-08-02'),(9,2,'Noir','Max','2023-08-03'),(10,2,'Vert','Ana','2023-08-04'),
 (11,3,'Gris','Lou','2023-08-05'),(12,3,'Rose','Sam','2023-08-06');
INSERT INTO covoiturages VALUES (1,3,3,'Paris',3),(2,3,5,'Paris',2),(3,1,4,'Lille',2),(4,4,1,'Paris',3);
""")
res = con.execute("""SELECT a.titre, COUNT(p.id)
FROM albums a
___ JOIN photos p ON p.album_id = a.id
GROUP BY a.id
ORDER BY a.titre""").fetchall()

Ctrl/⌘+Entrée

Le plus difficile : « tous les ». La question 1.k (utilisateurs ayant participé à tous les événements d'un groupe) se résout en comptant, par utilisateur, les événements distincts du groupe et en comparant au nombre total d'événements du groupe (sous-requête) ; tu la retrouves dans S'exercer.