Partie 3 · Requêtes LoveBook

Modifier : DELETE, UPDATE, INSERT

Les rubriques DELETE, UPDATE, INSERT du TP : supprimer les photos d'un événement annulé, les utilisateurs sans participation, les groupes vides ; renommer un groupe, reporter une date ; ajouter un utilisateur, un événement, un album.

Un DELETE ne fait pas de jointure : on filtre avec une sous-requête (WHERE album_id IN (SELECT …)).
NOT IN + sous-requête qui renvoie un NULL ne supprime rien : écarte-les (IS NOT NULL).
Dans une chaîne SQL, l'apostrophe se double : 'Amis de l''environnement'.
Une colonne UNIQUE (email) refuse les doublons à l'insertion.

À toi de coder

Supprimer les photos d'un événement annulé

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 2.a : « supprimez toutes les photos qui ont été prises lors d'un événement annulé ». Le lien photo → événement passe par l'album. Un DELETE ne contient pas de jointure : on utilise une sous-requête avec IN.

Complète. (Vérifie toujours avec un SELECT équivalent avant de supprimer : la suppression est définitive.)

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);
""")
con.execute("""DELETE FROM photos
WHERE album_id IN (SELECT a.id FROM albums a JOIN evenements e ON e.id = a.evenement_id
                   WHERE e.annule = ___)""")
restantes = [r[0] for r in con.execute("SELECT id FROM photos ORDER BY id")]

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);
""")
con.execute("""DELETE FROM photos
WHERE album_id IN (SELECT a.id FROM albums a JOIN evenements e ON e.id = a.evenement_id
                   WHERE e.annule = ___)""")
restantes = [r[0] for r in con.execute("SELECT id FROM photos ORDER BY id")]

Ctrl/⌘+Entrée

Vérifie que tu as compris

DELETE FROM groupes WHERE id NOT IN (SELECT groupe_id FROM evenements) ne supprime rien alors qu'un groupe est vide. Pourquoi ?

À toi de coder

Renommer un groupe (apostrophe)

Question 3.a : « mettez à jour le nom du groupe « Amis de la nature » en « Amis de l'environnement » ». Dans une chaîne SQL, l'apostrophe se double : 'Amis de l''environnement'. Garde un WHERE : sans lui, tous les groupes seraient renommés.

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);
""")
con.execute("UPDATE groupes SET nom = 'Amis de l''environnement' WHERE nom = '___'")
noms = [r[0] for r in con.execute("SELECT nom FROM groupes ORDER BY id")]

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);
""")
con.execute("UPDATE groupes SET nom = 'Amis de l''environnement' WHERE nom = '___'")
noms = [r[0] for r in con.execute("SELECT nom FROM groupes ORDER BY id")]

Ctrl/⌘+Entrée

Vérifie que tu as compris

Comment écrire l'apostrophe dans 'Amis de l'environnement' ?