Corrigé des exercices — SQL

---

Exercice 1 : Requêtes SELECT simples

1. Tous les livres de la table LIVRES.

   SELECT * FROM LIVRES;
   

2. Les titres et années de publication de tous les livres.

   SELECT titre, ann_publi FROM LIVRES;
   

3. Les noms et prénoms de tous les auteurs.

   SELECT nom, prenom FROM AUTEURS;
   

4. Les titres des livres publiés après 1950.

   SELECT titre FROM LIVRES WHERE ann_publi > 1950;
   

5. Les titres des livres du genre "dystopie".

   SELECT titre FROM LIVRES WHERE genre = 'dystopie';
   

6. Les auteurs de nationalité française.

   SELECT * FROM AUTEURS WHERE nationalite = 'français';
   

---

Exercice 2 : Filtres et conditions

1. Les livres publiés entre 1940 et 1960 (inclus).

   SELECT * FROM LIVRES WHERE ann_publi BETWEEN 1940 AND 1960;
   -- ou
   SELECT * FROM LIVRES WHERE ann_publi >= 1940 AND ann_publi <= 1960;
   

2. Les livres ayant plus de 300 pages.

   SELECT * FROM LIVRES WHERE nb_pages > 300;
   

3. Les auteurs nés après 1900 et de nationalité américaine.

   SELECT * FROM AUTEURS
   WHERE ann_naissance > 1900 AND nationalite = 'américain';
   

4. Les livres dont le titre contient le mot "Terre".

   SELECT * FROM LIVRES WHERE titre LIKE '%Terre%';
   

5. Les livres qui ne sont pas du genre "SF".

   SELECT * FROM LIVRES WHERE genre <> 'SF';
   -- ou
   SELECT * FROM LIVRES WHERE genre != 'SF';
   -- ou
   SELECT * FROM LIVRES WHERE NOT genre = 'SF';
   

6. Les auteurs dont le prénom commence par la lettre "R".

   SELECT * FROM AUTEURS WHERE prenom LIKE 'R%';
   

---

Exercice 3 : Tri et limitation

1. Tous les livres triés par année de publication (du plus ancien au plus récent).

   SELECT * FROM LIVRES ORDER BY ann_publi ASC;
   -- ou simplement
   SELECT * FROM LIVRES ORDER BY ann_publi;
   

2. Les 5 livres les plus récents.

   SELECT * FROM LIVRES ORDER BY ann_publi DESC LIMIT 5;
   

3. Les auteurs triés par année de naissance décroissante.

   SELECT * FROM AUTEURS ORDER BY ann_naissance DESC;
   

4. Les 3 livres ayant le plus de pages.

   SELECT * FROM LIVRES ORDER BY nb_pages DESC LIMIT 3;
   

5. Les livres triés par genre, puis par titre alphabétique.

   SELECT * FROM LIVRES ORDER BY genre ASC, titre ASC;
   

6. La liste des genres sans doublons.

   SELECT DISTINCT genre FROM LIVRES;
   

---

Exercice 4 : Fonctions d'agrégation

1. Le nombre total de livres dans la base.

   SELECT COUNT(*) FROM LIVRES;
   -- Résultat : 12
   

2. Le nombre de pages du livre le plus long.

   SELECT MAX(nb_pages) FROM LIVRES;
   -- Résultat : 896 (Dune)
   

3. L'année de publication du livre le plus ancien.

   SELECT MIN(ann_publi) FROM LIVRES;
   -- Résultat : 1865 (De la Terre à la Lune)
   

4. Le nombre moyen de pages des livres.

   SELECT AVG(nb_pages) FROM LIVRES;
   -- Résultat : environ 333.25
   

5. Le nombre de livres par genre.

   SELECT genre, COUNT(*) AS nb_livres
   FROM LIVRES
   GROUP BY genre;
   

   Résultat : | genre | nb_livres | |-------|-----------| | aventure | 2 | | dystopie | 3 | | post-apo | 1 | | SF | 4 | | space opera | 2 |

6. Le nombre total de pages de tous les livres de Jules Verne (id_auteur = 8).

   SELECT SUM(nb_pages) FROM LIVRES WHERE id_auteur = 8;
   -- Résultat : 672 (192 + 480)
   

---

Exercice 5 : GROUP BY et HAVING

1. Le nombre de livres écrits par chaque auteur.

   SELECT id_auteur, COUNT(*) AS nb_livres
   FROM LIVRES
   GROUP BY id_auteur;
   

2. Le nombre moyen de pages par genre.

   SELECT genre, AVG(nb_pages) AS moy_pages
   FROM LIVRES
   GROUP BY genre;
   

3. Les genres ayant plus de 2 livres.

   SELECT genre, COUNT(*) AS nb_livres
   FROM LIVRES
   GROUP BY genre
   HAVING COUNT(*) > 2;
   

   Résultat : dystopie (3), SF (4)

4. Les auteurs ayant écrit au moins 2 livres.

   SELECT id_auteur, COUNT(*) AS nb_livres
   FROM LIVRES
   GROUP BY id_auteur
   HAVING COUNT(*) >= 2;
   

   Résultat : id_auteur 3, 5, 7, 8 (Asimov, Bradbury, Barjavel, Verne)

5. L'année de publication du premier livre de chaque auteur.

   SELECT id_auteur, MIN(ann_publi) AS premier_livre
   FROM LIVRES
   GROUP BY id_auteur;
   

6. Les genres dont la moyenne de pages dépasse 250.

   SELECT genre, AVG(nb_pages) AS moy_pages
   FROM LIVRES
   GROUP BY genre
   HAVING AVG(nb_pages) > 250;
   

---

Exercice 6 : Jointures

1. Les titres des livres avec le nom et prénom de leur auteur.

   SELECT L.titre, A.nom, A.prenom
   FROM LIVRES L
   INNER JOIN AUTEURS A ON L.id_auteur = A.id;
   

2. Les titres des livres écrits par des auteurs français.

   SELECT L.titre
   FROM LIVRES L
   INNER JOIN AUTEURS A ON L.id_auteur = A.id
   WHERE A.nationalite = 'français';
   

   Résultat : La nuit des temps, Ravage, De la Terre à la Lune, Vingt mille lieues sous les mers

3. Les titres et auteurs des livres du genre "dystopie", triés par année.

   SELECT L.titre, A.nom, A.prenom, L.ann_publi
   FROM LIVRES L
   INNER JOIN AUTEURS A ON L.id_auteur = A.id
   WHERE L.genre = 'dystopie'
   ORDER BY L.ann_publi;
   

4. Le nom des auteurs ayant écrit des livres de plus de 400 pages.

   SELECT DISTINCT A.nom, A.prenom
   FROM AUTEURS A
   INNER JOIN LIVRES L ON A.id = L.id_auteur
   WHERE L.nb_pages > 400;
   

   Résultat : Herbert Frank, Verne Jules

5. Les livres avec le nom de l'auteur, pour les auteurs américains nés après 1910.

   SELECT L.titre, A.nom
   FROM LIVRES L
   INNER JOIN AUTEURS A ON L.id_auteur = A.id
   WHERE A.nationalite = 'américain' AND A.ann_naissance > 1910;
   

6. Pour chaque nationalité, le nombre de livres écrits.

   SELECT A.nationalite, COUNT(*) AS nb_livres
   FROM AUTEURS A
   INNER JOIN LIVRES L ON A.id = L.id_auteur
   GROUP BY A.nationalite;
   

   Résultat : | nationalite | nb_livres | |-------------|-----------| | américain | 6 | | britannique | 2 | | français | 4 |

---

Exercice 7 : Requêtes complexes

1. Le titre du livre le plus long de chaque auteur, avec le nom de l'auteur.

   SELECT A.nom, A.prenom, L.titre, L.nb_pages
   FROM LIVRES L
   INNER JOIN AUTEURS A ON L.id_auteur = A.id
   WHERE L.nb_pages = (
       SELECT MAX(nb_pages)
       FROM LIVRES
       WHERE id_auteur = L.id_auteur
   );
   

2. Les auteurs n'ayant écrit qu'un seul livre.

   SELECT A.nom, A.prenom, L.titre
   FROM AUTEURS A
   INNER JOIN LIVRES L ON A.id = L.id_auteur
   WHERE A.id IN (
       SELECT id_auteur
       FROM LIVRES
       GROUP BY id_auteur
       HAVING COUNT(*) = 1
   );
   

3. Le nombre moyen de pages des livres par nationalité d'auteur.

   SELECT A.nationalite, AVG(L.nb_pages) AS moy_pages
   FROM AUTEURS A
   INNER JOIN LIVRES L ON A.id = L.id_auteur
   GROUP BY A.nationalite;
   

4. Les genres pour lesquels tous les livres ont été publiés après 1940.

   SELECT genre
   FROM LIVRES
   GROUP BY genre
   HAVING MIN(ann_publi) > 1940;
   

   Résultat : dystopie (min 1931... non!), SF (min 1950), space opera (min 1951)

Note : En vérifiant les données, "Le meilleur des mondes" (dystopie) est de 1931, donc dystopie ne correspond pas. Les genres valides sont : SF, space opera, post-apo.

1. Le nom complet des auteurs et le nombre total de pages qu'ils ont écrites.

   SELECT A.prenom || ' ' || A.nom AS nom_complet, SUM(L.nb_pages) AS total_pages
   FROM AUTEURS A
   INNER JOIN LIVRES L ON A.id = L.id_auteur
   GROUP BY A.id
   ORDER BY total_pages DESC;
   

---

Exercice 8 : INSERT, UPDATE, DELETE

1. Ajouter l'auteur Ursula K. Le Guin.

   INSERT INTO AUTEURS (id, nom, prenom, nationalite, ann_naissance)
   VALUES (9, 'Le Guin', 'Ursula K.', 'américain', 1929);
   

2. Ajouter le livre "La Main gauche de la nuit".

   INSERT INTO LIVRES (id, titre, id_auteur, ann_publi, genre, nb_pages)
   VALUES (13, 'La Main gauche de la nuit', 9, 1969, 'SF', 304);
   

3. Changer le genre de "Dune" en "SF".

   UPDATE LIVRES
   SET genre = 'SF'
   WHERE titre = 'Dune';
   

4. Augmenter de 10% le nombre de pages de tous les livres de Jules Verne.

   UPDATE LIVRES
   SET nb_pages = nb_pages * 1.1
   WHERE id_auteur = 8;
   

5. Supprimer tous les livres publiés avant 1900.

   DELETE FROM LIVRES
   WHERE ann_publi < 1900;
   

   Note : Cela supprimera "De la Terre à la Lune" (1865) et "Vingt mille lieues sous les mers" (1870).

---

Exercice 9 : Création de tables

1. Créer la table EMPRUNTS.

   CREATE TABLE EMPRUNTS (
       id INTEGER PRIMARY KEY,
       id_livre INTEGER,
       id_adherent INTEGER,
       date_emprunt DATE,
       date_retour DATE,
       FOREIGN KEY (id_livre) REFERENCES LIVRES(id)
   );
   

2. Créer la table EDITEURS.

   CREATE TABLE EDITEURS (
       id INTEGER PRIMARY KEY,
       nom TEXT,
       pays TEXT,
       ann_creation INTEGER
   );
   

3. Ajouter une colonne id_editeur à la table LIVRES.

   ALTER TABLE LIVRES ADD COLUMN id_editeur INTEGER REFERENCES EDITEURS(id);
   

---

Exercice 10 : Analyse de requêtes

1. Requête avec sous-requête MIN :

   SELECT titre FROM LIVRES WHERE ann_publi = (SELECT MIN(ann_publi) FROM LIVRES);
   

   Explication : Cette requête retourne le titre du livre le plus ancien de la base. La sous-requête trouve l'année minimale, puis la requête principale sélectionne le(s) livre(s) publié(s) cette année-là.

Résultat : "De la Terre à la Lune" (1865)

1. Requête complexe avec double sous-requête :

   SELECT A.nom, COUNT(*)
   FROM AUTEURS A
   INNER JOIN LIVRES L ON A.id = L.id_auteur
   GROUP BY A.id
   HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) as cnt FROM LIVRES GROUP BY id_auteur));
   

   Explication : Cette requête retourne le(s) auteur(s) ayant écrit le plus grand nombre de livres. La sous-requête interne compte les livres par auteur, la sous-requête externe trouve le maximum, et la requête principale affiche les auteurs atteignant ce maximum.

Résultat : Les auteurs avec 2 livres chacun (Asimov, Bradbury, Barjavel, Verne)

1. Requête avec NOT IN :

   SELECT DISTINCT genre FROM LIVRES WHERE genre NOT IN (SELECT genre FROM LIVRES WHERE ann_publi < 1950);
   

   Explication : Cette requête retourne les genres pour lesquels AUCUN livre n'a été publié avant 1950. La sous-requête trouve les genres ayant au moins un livre avant 1950, puis la requête principale exclut ces genres.

Résultat : Les genres dont tous les livres sont de 1950 ou après (SF, space opera, potentiellement post-apo selon les données)

---

Exercice 11 : QCM

1. Quelle clause permet de filtrer les résultats d'un GROUP BY ?

2. Réponse : c) HAVING

3. Quelle est la différence entre WHERE et HAVING ?

4. Réponse : b) WHERE filtre avant GROUP BY, HAVING filtre après

Explication : WHERE filtre les lignes individuelles avant le regroupement. HAVING filtre les groupes après le regroupement et permet d'utiliser des fonctions d'agrégation.

1. Que retourne SELECT COUNT(*) FROM LIVRES WHERE 1=0; ?
2. Réponse : c) 0

Explication : La condition 1=0 est toujours fausse, donc aucune ligne n'est sélectionnée. COUNT(*) sur un ensemble vide retourne 0 (pas NULL, pas d'erreur).

1. Quel type de jointure retourne tous les enregistrements de la table de gauche ?
2. Réponse : b) LEFT JOIN

Explication : LEFT JOIN retourne toutes les lignes de la table de gauche, même si elles n'ont pas de correspondance dans la table de droite (avec NULL pour les colonnes de droite).

1. Quelle fonction d'agrégation ignore les valeurs NULL ?
2. Réponse : b) COUNT(colonne)

Explication : COUNT(*) compte toutes les lignes, y compris celles avec des NULL. COUNT(colonne) ne compte que les valeurs non NULL de cette colonne.

---

Auteur : Florian Mathieu

Licence CC BY NC

Ce cours est mis à disposition selon les termes de la Licence Creative Commons Attribution - Pas d'Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International.