Aller au contenu

Exercices — SQL

Contexte

Pour tous les exercices, on considère la base de données d'une médiathèque contenant les tables suivantes :

Table AUTEURS :

id nom prenom nationalite ann_naissance
1 Orwell George britannique 1903
2 Herbert Frank américain 1920
3 Asimov Isaac américain 1920
4 Huxley Aldous britannique 1894
5 Bradbury Ray américain 1920
6 Dick Philip K. américain 1928
7 Barjavel René français 1911
8 Verne Jules français 1828

Table LIVRES :

id titre id_auteur ann_publi genre nb_pages
1 1984 1 1949 dystopie 328
2 Dune 2 1965 space opera 896
3 Fondation 3 1951 space opera 256
4 Le meilleur des mondes 4 1931 dystopie 288
5 Fahrenheit 451 5 1953 dystopie 192
6 Ubik 6 1969 SF 224
7 Chroniques martiennes 5 1950 SF 256
8 La nuit des temps 7 1968 SF 318
9 Les Robots 3 1950 SF 253
10 Ravage 7 1943 post-apo 316
11 De la Terre à la Lune 8 1865 aventure 192
12 Vingt mille lieues sous les mers 8 1870 aventure 480

Table EMPRUNTS :

id id_livre id_adherent date_emprunt date_retour
1 1 101 2024-01-15 2024-01-29
2 3 102 2024-01-20 NULL
3 5 101 2024-02-01 2024-02-10
4 2 103 2024-02-05 NULL
5 1 104 2024-02-10 2024-02-20

Exercice 1 : Requêtes SELECT simples

Écrire les requêtes SQL permettant d'obtenir :

  1. Tous les livres de la table LIVRES.

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

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

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

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

  6. Les auteurs de nationalité française.

Exercice 2 : Filtres et conditions

Écrire les requêtes SQL permettant d'obtenir :

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

  2. Les livres ayant plus de 300 pages.

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

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

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

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

Exercice 3 : Tri et limitation

Écrire les requêtes SQL permettant d'obtenir :

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

  2. Les 5 livres les plus récents.

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

  4. Les 3 livres ayant le plus de pages.

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

  6. La liste des genres sans doublons.

Exercice 4 : Fonctions d'agrégation

Écrire les requêtes SQL permettant d'obtenir :

  1. Le nombre total de livres dans la base.

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

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

  4. Le nombre moyen de pages des livres.

  5. Le nombre de livres par genre.

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

Exercice 5 : GROUP BY et HAVING

Écrire les requêtes SQL permettant d'obtenir :

  1. Le nombre de livres écrits par chaque auteur (afficher id_auteur et le compte).

  2. Le nombre moyen de pages par genre.

  3. Les genres ayant plus de 2 livres.

  4. Les auteurs ayant écrit au moins 2 livres (afficher id_auteur et le nombre).

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

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

Exercice 6 : Jointures

Écrire les requêtes SQL permettant d'obtenir :

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

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

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

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

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

  6. Pour chaque nationalité, le nombre de livres écrits par des auteurs de cette nationalité.

Exercice 7 : Requêtes complexes

Écrire les requêtes SQL permettant d'obtenir :

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

  2. Les auteurs n'ayant écrit qu'un seul livre (afficher nom, prénom, titre du livre).

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

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

  5. Le nom complet (prénom + nom) des auteurs et le nombre total de pages qu'ils ont écrites.

Exercice 8 : INSERT, UPDATE, DELETE

  1. Écrire une requête pour ajouter l'auteur suivant :
  2. id : 9
  3. nom : "Le Guin"
  4. prénom : "Ursula K."
  5. nationalité : "américain"

  6. année de naissance : 1929

  7. Écrire une requête pour ajouter le livre suivant :

  8. id : 13
  9. titre : "La Main gauche de la nuit"
  10. id_auteur : 9
  11. année de publication : 1969
  12. genre : "SF"

  13. nombre de pages : 304

  14. Écrire une requête pour changer le genre de "Dune" en "SF".

  15. Écrire une requête pour augmenter de 10% le nombre de pages de tous les livres de Jules Verne.

  16. Écrire une requête pour supprimer tous les livres publiés avant 1900.

Exercice 9 : Création de tables

  1. Écrire la requête SQL pour créer la table EMPRUNTS avec :
  2. id (entier, clé primaire)
  3. id_livre (entier, clé étrangère vers LIVRES)
  4. id_adherent (entier)
  5. date_emprunt (date)

  6. date_retour (date, peut être NULL)

  7. Écrire la requête SQL pour créer une table EDITEURS avec :

  8. id (entier, clé primaire)
  9. nom (texte)
  10. pays (texte)

  11. ann_creation (entier)

  12. Comment modifier la table LIVRES pour ajouter une colonne id_editeur qui référence la table EDITEURS ?

Exercice 10 : Analyse de requêtes

Pour chaque requête suivante, décrire en français ce qu'elle retourne :

  1. sql SELECT titre FROM LIVRES WHERE ann_publi = (SELECT MIN(ann_publi) FROM LIVRES);

  2. sql 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));

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

Exercice 11 : QCM

  1. Quelle clause permet de filtrer les résultats d'un GROUP BY ?
  2. [ ] a) WHERE
  3. [ ] b) FILTER
  4. [ ] c) HAVING

  5. [ ] d) GROUP FILTER

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

  7. [ ] a) Aucune, ils sont équivalents
  8. [ ] b) WHERE filtre avant GROUP BY, HAVING filtre après
  9. [ ] c) HAVING filtre avant GROUP BY, WHERE filtre après

  10. [ ] d) WHERE ne fonctionne qu'avec les nombres

  11. Que retourne SELECT COUNT(*) FROM LIVRES WHERE 1=0; ?

  12. [ ] a) Une erreur
  13. [ ] b) NULL
  14. [ ] c) 0

  15. [ ] d) Rien

  16. Quel type de jointure retourne tous les enregistrements de la table de gauche ?

  17. [ ] a) INNER JOIN
  18. [ ] b) LEFT JOIN
  19. [ ] c) RIGHT JOIN

  20. [ ] d) CROSS JOIN

  21. Quelle fonction d'agrégation ignore les valeurs NULL ?

  22. [ ] a) COUNT(*)
  23. [ ] b) COUNT(colonne)
  24. [ ] c) Les deux
  25. [ ] d) Aucune

Auteur : Florian Mathieu

Licence CC BY NC

Licence Creative Commons
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.