Partie 3 · Requêtes LoveBook

Filtrer : WHERE, DISTINCT et dates

Les questions les plus simples du TP sont des filtres : « les événements dans la ville de Paris » (1.a), « les villes où au moins un événement a eu lieu » (1.i), « les événements organisés en 2021 » (1.j).

Besoin	Outil
garder des lignes	`WHERE ville = 'Paris'`
sans doublon	`SELECT DISTINCT ville`
intervalle (bornes incluses)	`BETWEEN '2021-01-01' AND '2021-12-31'`
plusieurs conditions	`AND`, `OR`
motif de texte	`LIKE '%paris%'`

Une date au format AAAA-MM-JJ se compare comme un texte : l'ordre alphabétique est l'ordre chronologique.

À toi de coder

Les événements à 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).

Question 1.a de l'énoncé : « sélectionnez tous les événements qui ont lieu dans la ville de Paris ». Écris la requête qui renvoie les titres, triés par ordre alphabétique, dans res (une liste de titres).

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 titre FROM evenements WHERE ville = ___ ORDER BY 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 titre FROM evenements WHERE ville = ___ ORDER BY titre")]

Ctrl/⌘+Entrée

Vérifie que tu as compris

Pour obtenir chaque ville une seule fois, on écrit…

À toi de coder

Les événements organisés en 2021

Question 1.j : « sélectionnez les événements qui ont été organisés en 2021 ». La date est un texte au format AAAA-MM-JJ : comparer deux dates de ce format revient à comparer des chaînes. BETWEEN a AND b inclut les deux bornes.

Renvoie les titres des événements de 2021, du plus ancien au plus récent, 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 titre FROM evenements WHERE date_debut BETWEEN '2021-01-01' AND ___ ORDER BY date_debut")]

Ctrl/⌘+Entrée

Vérifie que tu as compris

date_debut BETWEEN '2021-01-01' AND '2021-12-31' inclut-il le 31 décembre ?