Partie 3 · Requêtes LoveBook

Compter et regrouper : COUNT, SUM, GROUP BY, HAVING

Le TP demande des chiffres : participants par événement (1.c), photos par album (1.b), billets vendus et prix total (1.e), chiffre d'affaires (1.f). Il faut des fonctions d'agrégation : COUNT, SUM… appliquées à des groupes formés par GROUP BY.

Clause	Rôle
`WHERE`	filtre les lignes, avant le regroupement ; ne peut pas contenir `COUNT`
`GROUP BY e.id`	un résultat par événement
`HAVING COUNT(*) >= 4`	filtre les groupes, après le regroupement

À toi de coder

Événements avec au moins 4 participants

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).

Question 1.c : « les événements organisés avec au moins plus de 10 participants » (ici, comme la base est petite, au moins 4). Il faut compter par événement : GROUP BY regroupe les lignes, et HAVING filtre ensuite les groupes (WHERE filtre les lignes avant le regroupement, il ne peut pas voir un COUNT).

Renvoie les titres, triés, 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 = [r[0] for r in con.execute("""SELECT e.titre
FROM evenements e JOIN participations p ON p.evenement_id = e.id
GROUP BY e.id
___ COUNT(*) >= 4
ORDER BY e.titre""")]

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 = [r[0] for r in con.execute("""SELECT e.titre
FROM evenements e JOIN participations p ON p.evenement_id = e.id
GROUP BY e.id
___ COUNT(*) >= 4
ORDER BY e.titre""")]

Ctrl/⌘+Entrée

Vérifie que tu as compris

Où place-t-on la condition « au moins 4 participants » ?

À toi de coder

Billets vendus et chiffre d'affaires par événement

Question 1.e : « le nombre de billets vendus pour chaque événement et le prix total ». Chaque billet pointe vers un type de billet, qui porte le montant et l'événement : evenements → types_billets → billets.

Renvoie, dans res, une liste de tuples (titre, nombre de billets, total en €) triée par titre.

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 e.titre, COUNT(b.id), ___(t.montant)
FROM evenements e
JOIN types_billets t ON t.evenement_id = e.id
JOIN billets b ON b.type_billet_id = t.id
GROUP BY e.id
ORDER BY e.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 e.titre, COUNT(b.id), ___(t.montant)
FROM evenements e
JOIN types_billets t ON t.evenement_id = e.id
JOIN billets b ON b.type_billet_id = t.id
GROUP BY e.id
ORDER BY e.titre""").fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Chaque billet pointe vers un type de billet. Pour connaître l'événement d'un billet, il faut…