19 exercices sur les fonctions agrégées

Résolvez ces 19 exercices sur les fonctions agrégées SQL et affinez vos compétences SQL ! Entraînez-vous à utiliser les fonctions d'agrégation avec GROUP BY, HAVING, les sous-requêtes, etc. Inclut une solution et une explication détaillée pour chaque exercice.

Les fonctions agrégées sont une partie importante de SQL. Elles vous permettent de calculer différentes statistiques et de générer des rapports que vous n'auriez pas pu faire avec des opérations sur une seule ligne comme le filtrage. Il est important de s'entraîner souvent à l'utilisation des fonctions agrégées, car elles constituent une partie essentielle de vos compétences en SQL. C'est parti !

Cet article comprend 19 nouveaux exercices sur les fonctions d'agrégation utilisant la base de données de notre bibliothèque. Nous aborderons le regroupement avec GROUP BY, le filtrage des données avec HAVING, l'utilisation des fonctions d'agrégation dans les sous-requêtes et l'agrégation à plusieurs niveaux. Nous rafraîchirons également vos connaissances sur l'utilisation des JOIN et des NULL, ainsi que sur le classement des données en fonction de plusieurs statistiques.

Une fois que vous êtes prêt à appliquer ces concepts dans des scénarios réels, consultez notre cours Basic SQL Reporting ! Il couvre tous les concepts de fonctions agrégées que vous pratiquerez dans cet article, ainsi que d'autres sujets importants tels que l'utilisation de CASE WHEN.

Nous vous recommandons également d'essayer nos défis mensuels dans notre piste Pratique SQL mensuelle . Il s'agit d'une collection d'exercices pratiques - avec de nouveaux exercices publiés chaque mois - spécialement conçus pour maintenir vos compétences SQL à jour.

Que sont les fonctions agrégées ?

Lesfonctions agrégées effectuent des calculs sur un ensemble de valeurs et renvoient une seule valeur comme résultat. Les fonctions agrégées les plus courantes sont les suivantes

• SUM() - Calcul de la somme de toutes les valeurs de chaque groupe.
• AVG() - Calcule la valeur moyenne de toutes les valeurs de chaque groupe.
• COUNT() - Renvoie le nombre de valeurs dans chaque groupe.
• MIN() et MAX() - renvoient les valeurs les plus petites et les plus grandes (respectivement) dans chaque groupe.

Ces fonctions sont particulièrement utiles lors de la création de rapports dans lesquels vous devez calculer différentes mesures.

Besoin d'une explication plus détaillée ? Consultez notre fiche d'aide sur les fonctions d'agrégation SQL, qui couvre toutes les fonctions d'agrégation, leurs cas d'utilisation et leurs interactions avec GROUP BY.

Pourquoi pratiquer les agrégations SQL ?

Une pratique régulière vous aide à maintenir vos compétences à jour, ce qui vous permet d'analyser et de manipuler des données plus rapidement et plus efficacement. Plus vous vous entraînerez, plus vous serez en mesure de résoudre des problèmes complexes et d'optimiser vos requêtes.

La pratique du langage SQL est essentielle si vous poursuivez une carrière d'analyste de données, de développeur de bases de données ou tout autre poste qui traite de nombreuses données. Consultez notre article sur les exercices GROUP BY pour affiner vos compétences en matière de création de rapports.

L'ensemble de données

Jetons un coup d'œil à l'ensemble de données avec lequel nous allons travailler pour ces questions d'entraînement.

L'ensemble de données se compose de cinq tables : book author , book_author, patron et book_loan. Voici le schéma :

Les informations sur les livres sont stockées dans la table book. Elle comporte les colonnes suivantes :

• book_id - Un identifiant unique pour chaque livre et la clé primaire de la table.
• title - Le titre du livre
• publication_year - L'année de publication du livre. Peut être NULL si elle est inconnue.
• genre - Le genre du livre, par exemple "Fantasy" ou "Mystery".
• pages - Le nombre de pages du livre.

Voici un aperçu des données contenues dans la table :

book_id	title	publication_year	genre	pages
1	1984	1949	Political Fiction	328
2	Animal Farm	1945	Political Fiction	112
3	The Hobbit	1937	Fantasy	310
4	The Fellowship of the Ring	1954	Fantasy	423

Les informations sur les auteurs sont stockées dans la table author. Elle comporte les colonnes suivantes :

• author_id - Un identifiant unique pour chaque auteur et la clé primaire de la table.
• author_name - Le nom complet ou le pseudonyme de l'auteur.
• country - Le pays de l'auteur.

Voici quelques données de la table :

author_id	author_name	country
1	George Orwell	United Kingdom
2	J.R.R. Tolkien	United Kingdom
3	Isaac Asimov	United States
4	Agatha Christie	United Kingdom

Les données relatives aux personnes qui empruntent des livres à la bibliothèque sont stockées dans la table patron. Elle comporte les colonnes suivantes :

• patron_id - Un identifiant unique pour chaque utilisateur et la clé primaire de la table.
• patron_name - Le nom complet de l'utilisateur.
• registration_date - La date à laquelle l'utilisateur s'est inscrit dans le système de la bibliothèque.

Voici quelques-unes des données contenues dans la table :

patron_id	patron_name	registration_date
1	Alice Johnson	2024-01-15
2	Bob Smith	2024-03-22
3	Charlie Brown	2024-05-10
4	David Wilson	2024-06-01

La relation de plusieurs à plusieurs entre les auteurs et les livres qu'ils ont écrits est stockée dans la table book_author est stockée dans la table Elle comporte les colonnes suivantes

• book_author_id - Un identifiant unique pour chaque paire livre-auteur et la clé primaire de la table.
• author_id - L'ID de l'auteur.
• book_id - L'identifiant du livre écrit par l'auteur.

Voici quelques-unes des données contenues dans la table :

book_author_id	author_id	book_id
1	1	1
2	1	2
3	2	3
4	2	4

La relation de plusieurs à plusieurs entre les clients et les livres qu'ils ont empruntés est stockée dans la table book_loan est stockée dans la table Elle comporte les colonnes suivantes :

• loan_id - Un identifiant unique pour chaque prêt et la clé primaire de la table.
• book_id - L'identifiant du livre emprunté.
• patron_id - L'identifiant du client qui a emprunté le livre.
• loan_date - La date à laquelle le livre a été prêté.
• due_date - La date à laquelle le livre doit être rendu.
• return_date - La date à laquelle le livre a été rendu.

Voici quelques données du tableau :

loan_id	book_id	patron_id	loan_date	due_date	return_date
1	1	1	2024-01-20	2024-02-20	2024-02-15
2	8	1	2024-02-01	2024-03-01	2024-02-28
3	3	2	2024-02-10	2024-03-10	2024-03-05
4	4	3	2024-03-15	2024-04-10	2024-04-15

Revenez à cette section si vous oubliez des noms de tableaux ou de colonnes lors de la résolution des exercices. Si vous avez besoin de conseils sur la syntaxe, ayez à portée de main notre Aide-mémoire SQL pour l'analyse de données. Elle couvre tous les outils qui peuvent vous aider à résoudre ces exercices. Vous pouvez même la télécharger au format PDF et l'imprimer, afin qu'elle vous aide dans vos prochains exercices !

Entraînez-vous à utiliser ces fonctions agrégées SQL

Résolvez les exercices par vous-même, puis consultez les solutions sous chaque exercice. Il y a aussi des explications supplémentaires pour chaque solution si vous êtes bloqué.

Exercice 1 : Livres dans le système

Exercice : Comptez le nombre de livres enregistrés dans la base de données.

Solution :

SELECT COUNT(book_id)
FROM book;

Explication : Nous utilisons la fonction COUNT() pour obtenir le nombre de lignes dans le tableau. book table.

Notez deux choses. Premièrement, la requête n'a pas de clause WHERE, donc aucune ligne n'est filtrée ; toutes les lignes du tableau sont comptées. Deuxièmement, la requête n'a pas de clause GROUP BY. Lorsque vous utilisez une fonction d'agrégation sans GROUP BY, toutes les lignes sont placées dans un groupe et la fonction est appliquée à toutes les lignes de ce groupe. Notre requête compte donc toutes les lignes du tableau. book table.

Exercice 2 : Livres non retournés

Exercice : Comptez le nombre de livres qui n'ont pas encore été retournés (c'est-à-dire les livres qui n'ont pas de date de retour).

Solution:

SELECT COUNT(loan_id)
FROM book_loan
WHERE return_date IS NULL;

Explication : Les livres qui n'ont pas été retournés n'ont pas de return_date dans la table. book_loan leur return_date est NULL. Nous utilisons cette condition dans la clause WHERE pour ne sélectionner que les livres qui n'ont pas encore été retournés.

Nous utilisons la fonction COUNT() pour compter le nombre de lignes dans l'ensemble de données résultant. N'oubliez pas que l'argument utilisé avec la fonction COUNT() est important. Ici, nous avons indiqué loan_id comme argument, ce qui indique à la base de données de compter toutes les valeurs des colonnes loan_id.

Nous aurions également pu utiliser COUNT(*) et compter simplement toutes les lignes de l'ensemble de résultats, ce qui aurait donné le même résultat. Cependant, COUNT(return_date) ne serait pas approprié : le résultat serait 0. Tous les return_dates dans le résultat sont NULL en raison de la condition return_date IS NULL.

Une fois encore, il n'y a pas de GROUP BY dans cette requête, de sorte que la fonction COUNT() comptera tous les prêts pour lesquels la date de retour est vide.

Exercice 3 : Livres par genre

Exercice : Pour chaque genre, indiquez le nom du genre et le nombre de livres dans ce genre.

Solution :

SELECT
  genre,
  COUNT(book_id)
FROM book
GROUP BY genre;

Explication : Il s'agit de l'exercice le plus basique du site GROUP BY. Dans le tableau booknous sélectionnons le genre.

Pour s'assurer que la fonction COUNT() renvoie un résultat distinct pour chaque genre, nous divisons l'ensemble de données en groupes à l'aide de GROUP BY genre. Cela créera des groupes basés sur les valeurs de la colonne genre; les livres ayant la même valeur dans le genre seront placés dans le même groupe.

La fonction COUNT() travaille sur chaque groupe séparément, en comptant le nombre de livres dans chaque groupe.

Si vous avez besoin d'un rappel sur la façon de travailler avec GROUP BY et les fonctions d'agrégation, consultez notre Aperçu complet des fonctions GROUP BY et Aggregate.

Exercice 4 : Auteurs par pays

Exercice : Pour chaque pays, indiquez son nom et le nombre d'auteurs qui lui sont associés.

Solution :

SELECT
  country,
  COUNT(author_id)
FROM author
GROUP BY country;

Explication : Il s'agit d'un autre exercice de base de GROUP BY. Nous sélectionnons des données dans la table auteur et les regroupons par les valeurs de la colonne country. Ensuite, nous appliquons COUNT(author_id) à chaque groupe pour compter les auteurs provenant de ce pays.

Exercice 5 : plages de pages par genre

Exercice : Pour chaque genre, présentez quatre colonnes : le nom du genre, le nombre minimum et maximum de pages pour les livres de ce genre, et le difference entre le plus grand et le plus petit nombre de pages de chaque livre.

Solution :

SELECT
  genre,
  MIN(pages),
  MAX(pages),
  MAX(pages) - MIN(pages) AS difference
FROM book
GROUP BY genre;

Explication : Il s'agit d'un autre exercice de base de GROUP BY. Nous sélectionnons des données dans la table auteur et les regroupons en fonction des valeurs de la colonne pays. Nous appliquons ensuite COUNT(author_id) à chaque groupe pour compter les auteurs originaires de ce pays.

Exercice 5 : plages de pages par genre

Exercice : Pour chaque genre, présentez quatre colonnes : le nom du genre, le nombre minimum et maximum de pages pour les livres de ce genre, et la différence entre le plus grand et le plus petit nombre de pages dans chaque livre.

Solution :

Explication : Pour obtenir des statistiques pour chaque genre, il faut regrouper les données du tableau book en fonction de la colonne genre.

Utilisez les fonctions d'agrégation MIN(pages) et MAX(pages) pour calculer le nombre minimum et maximum de pages. Dans la troisième colonne, utilisez MIN(pages) - MAX(pages) pour calculer la différence pour chaque groupe. Enfin, renommez la dernière colonne en difference en utilisant AS.

Exercice 6 : Genres de grands livres

Exercice : Pour chaque genre, indiquez le nombre moyen de pages de tous les livres de ce genre. N'indiquez que les genres pour lesquels le livre moyen compte plus de 250 pages. Nommez la colonne des pages moyennes avg_pages.

Solution :

SELECT
  genre,
  AVG(pages) AS avg_pages
FROM book
GROUP BY genre
HAVING AVG(pages) >= 250;

Explication : Cet exercice est similaire au précédent : nous regroupons les livres par genre et calculons le nombre moyen de livres dans chaque genre à l'aide de AVG(). Cependant, il y a un nouvel élément ici : HAVING. HAVING est utilisé pour filtrer les groupes et trouver des groupes pour lesquels une fonction d'agrégation satisfait une certaine condition. Dans notre cas, nous recherchons les groupes (genres) dont le nombre moyen de pages est supérieur ou égal à 250.

N'oubliez pas que HAVING fonctionne différemment de WHERE. WHERE est utilisé pour filtrer les lignes individuelles avant le regroupement, tandis que HAVING est utilisé pour filtrer les lignes après le regroupement. Vous pouvez lire la différence entre HAVING et WHERE dans notre article HAVING vs. WHERE in SQL : Ce qu'il faut savoir.

Exercice 7 : Genres modernes

Exercice : Indiquez l'année de publication moyenne pour chaque genre de livres. Arrondissez l'année à un nombre entier. N'indiquez que les genres dont l'année de publication moyenne est postérieure à 1940.

Solution :

SELECT
  genre,
  ROUND(AVG(publication_year))
FROM book
GROUP BY genre
HAVING ROUND(AVG(publication_year)) > 1940;

Explication : Cet exercice est similaire au précédent : nous regroupons les livres par genre et calculons l'année de publication moyenne à l'aide de AVG(publication_year). Ensuite, nous filtrons les genres dont l'année moyenne d'agrégation est supérieure à 1940 à l'aide de HAVING.

Exercice 8 : Livres avec plusieurs auteurs

Exercice : Pour les livres qui ont été écrits par plus d'un auteur, indiquez le titre de chaque livre et le nombre d'auteurs.

Solution :

SELECT 
  title,
  COUNT(author_id)
FROM book b
JOIN book_author ba
  ON b.book_id = ba.book_id
GROUP BY b.book_id
HAVING COUNT(author_id) > 1;

Explication : Il faut d'abord trouver les auteurs de chaque livre. Pour ce faire, nous joignons les tables book et book_author sur leur book_id commun. Cela combinera les données relatives à chaque livre avec les données relatives à ses auteurs : une ligne pour chaque combinaison livre-auteur.

Nous regroupons ensuite les lignes par book_id: toutes les lignes relatives au même livre sont dans le même groupe. Ainsi, tous les auteurs de chaque livre seront dans le même groupe. Nous appliquons ensuite la fonction COUNT(author_id) pour compter les auteurs dans chaque groupe.

Enfin, nous filtrons les livres ne comportant qu'un seul auteur à l'aide de la fonction HAVING COUNT(author_id) > 1.

Exercice 9 : Dernier prêt de chaque livre

Exercice : Pour chaque livre, indiquez son adresse title et la date la plus récente à laquelle il a été prêté. Nommez la deuxième colonne last_loaned. Indiquez NULL dans la deuxième colonne pour tous les livres qui n'ont jamais été prêtés.

Solution :

SELECT
  book.title,
  MAX(book_loan.loan_date) AS last_loaned
FROM book
LEFT JOIN book_loan 
  ON book.book_id = book_loan.book_id
GROUP BY 
  book.book_id,
  book.title;

Explication : Utilisez un LEFT JOIN pour relier les colonnes book et book_loan pour vous assurer que les livres qui n'ont jamais été prêtés sont également inclus dans l'ensemble des résultats. Regroupez les résultats par book_id et book_title.

Notez que vous ne devez pas simplement regrouper les résultats en fonction du titre ; si deux livres ont le même titre, ils seront placés par erreur dans le même groupe. Regroupez les résultats par book_id (car il identifie chaque livre de manière unique) et title (car SQL génère une erreur si une colonne non agrégée dans SELECT n'est pas placée dans GROUP BY). Vous pouvez lire à ce sujet notre article 7 Common GROUP BY Errors.

Pour obtenir la dernière version de loan_date, utilisez MAX(loan_date). Les dates ultérieures sont considérées comme "plus grandes". S'il n'y a pas de prêt pour ce livre, toutes ses dates de prêt seront NULL et la fonction MAX() renverra NULL pour ce livre.

Exercice 10 : Prêts de livres par mois

Exercice : Indiquez le nombre de prêts de livres accordés chaque mois de chaque année. Affichez trois colonnes :

• Les parties année et mois du site loan_date sous forme de nombres dans les deux premières colonnes. Nommez-les loan_year et loan_month
• Une colonne comptant le nombre de livres prêtés ce mois-là.

Classez les résultats par année, puis par mois, en commençant par les dates les plus anciennes.

Solution :

SELECT
  EXTRACT(YEAR FROM loan_date) AS loan_year,
  EXTRACT(MONTH FROM loan_date) AS loan_month,
  COUNT(loan_id)
FROM book_loan
GROUP BY 
  EXTRACT(MONTH FROM loan_date),
  EXTRACT(YEAR FROM loan_date)
ORDER BY 
  loan_year,
  loan_month;

Explication : Nous utilisons EXTRACT(YEAR FROM loan_date) et EXTRACT(MONTH FROM loan_date) pour obtenir les parties année et mois de loan_date. Nous utilisons à nouveau EXTRACT() dans GROUP BY pour regrouper les prêts des mêmes mois.

Nous utilisons la fonction COUNT() pour calculer le nombre de prêts effectués au cours de chaque mois.

Enfin, nous classons les résultats par loan_year et loan_month. Notez que vous pouvez utiliser les alias de colonne dans l'instruction ORDER BY. Dans l'instruction GROUP BY, cependant, vous devez toujours utiliser les fonctions ; lorsque cette clause est traitée, la fonction EXTRACT() (et donc les nouvelles colonnes) n'a pas encore été définie. Vous pouvez en savoir plus sur ce sujet dans notre article SQL Order of Operations.

Exercice 11 : Livres populaires

Exercice : Pour chaque livre, indiquez son title, le nombre de fois qu'il a été prêté et le nombre de personnes différentes qui l'ont emprunté. Nommez les deux dernières colonnes times_loaned et different_patrons.

Solution :

SELECT
  title,
  COUNT(loan_id) AS times_loaned,
  COUNT(DISTINCT patron_id) AS different_patrons
FROM book b
LEFT JOIN book_loan bl
  ON b.book_id = bl.book_id
GROUP BY
  b.title,
  b.book_id;

Explication : Pour trouver les prêts de livres pour chaque livre, vous devez joindre les tables book et book_loan. Utilisez LEFT JOIN pour vous assurer que les livres qui n'ont jamais été prêtés apparaissent également dans le résultat.

Nous voulons regrouper les prêts pour chaque livre, nous devons donc regrouper à la fois par book_id et par le livre title (pour la même raison que celle évoquée dans l'exercice 9).

Nous voulons compter le nombre de fois que le livre a été prêté et le nombre de clients différents qui ont emprunté le livre. Pour ce faire, nous devons utiliser la fonction COUNT() deux fois.

Tout d'abord, nous utilisons COUNT(loan_id) pour compter le nombre de prêts du livre.

La seconde utilisation de COUNT() est plus intéressante : nous voulons compter les différents utilisateurs qui ont emprunté le livre. Si quelqu'un a emprunté le même livre plusieurs fois, nous ne voulons le compter qu'une seule fois. C'est pourquoi nous utilisons COUNT(DISTINCT patron_id). L'utilisation de DISTINCT permettra de s'assurer que même si un utilisateur a emprunté les mêmes livres plusieurs fois, son ID ne sera compté qu'une seule fois.

Vous pouvez en savoir plus à ce sujet en lisant Quelle est la différence entre COUNT(*), COUNT(1), COUNT(colonne) et COUNT(DISTINCT) ?

Exercice 12 : Livres en retard

Exercice : Pour chaque client, indiquez son nom et le nombre de livres qu'il a (avait) en retard (c'est-à-dire dont la date de retour est postérieure à la date d'échéance).

Solution :

SELECT
  patron_name,
  COUNT(book_id) AS overdue_books
FROM patron p
LEFT JOIN book_loan bl
  ON p.patron_id = bl.patron_id AND return_date > due_date
GROUP BY
  p.patron_id,
  patron_name;

Explication : Rejoignez patron et book_loan en utilisant LEFT JOIN pour s'assurer que les clients qui n'ont pas de prêt de livres en retard sont également inclus dans le résultat.

Pour ne sélectionner que les prêts dont la date de retour est postérieure à la date d'échéance, utilisez une condition de jointure combinée : ON p.patron_id = bl.patron_id AND return_date > due_date. La première partie ne joint que les lignes qui sont réellement liées. La seconde partie est utilisée comme filtre supplémentaire pour ne joindre que les endroits où return_date > due_date.

Notez que cela diffère de l'utilisation d'une clause WHERE plus loin dans la requête. La clause WHERE écarte toutes les lignes où loan_id IS NULL. Cependant, nous voulons conserver ces lignes pour inclure les clients qui n'ont pas de livres en retard.

Nous regroupons les lignes par patron_id et patron_name (pour les mêmes raisons que dans l'exercice 9). Enfin, nous utilisons COUNT(book_id) pour compter les livres en retard pour chaque utilisateur. Le site COUNT() renverra 0 pour les clients qui n'ont jamais fait de prêt et pour les clients qui ont toujours rendu leurs livres à temps.

Exercice 13 : moyenne des auteurs par genre

Exercice : Pour chaque genre, indiquez son nom et le nombre moyen d'auteurs des livres de ce genre. Nommez la deuxième colonne average_authors_per_book

Solution :

WITH number_of_authors AS (
  SELECT
    book_id,
    COUNT(author_id) AS author_count
  FROM book_author
  GROUP BY book_id
)
SELECT
  genre,
  AVG(author_count) AS average_authors_per_book
FROM number_of_authors na
JOIN book b
  ON na.book_id = b.book_id
GROUP BY genre;

Explication : Nous utilisons ici une construction appelée expression de table commune (CTE). Pour en savoir plus sur les CTE, consultez notre Guide des expressions de tableau commun.

En bref, une CTE vous permet de créer un ensemble de résultats temporaire nommé que vous pouvez utiliser dans la requête. Vous créez un CTE à l'aide de la syntaxe suivante :

WITH <cte_name> AS (query)

Toute requête comprise dans la parenthèse agira comme une table virtuelle nommée cte_name et sera accessible à la requête principale (l'instruction SELECT après la parenthèse fermante de l'ETC).

Dans l'ETC, nous calculons le nombre d'auteurs pour chaque livre. Nous sélectionnons le site book_id et le nombre d'auteurs. Cette requête est similaire à celle de l'exercice 8.

Dans la requête externe, nous joignons notre ETC number_of_authors à la table book pour afficher le genre de chaque livre. Nous utilisons ensuite les genres AVG(author_count) et GROUP BY pour obtenir le résultat final.

Si vous ne souhaitez pas utiliser d'ETC, vous pouvez obtenir le même résultat à l'aide d'une sous-requête :

SELECT
  genre,
  AVG(author_count) AS average_authors_per_book
FROM (
  SELECT
    book_id,
    COUNT(author_id) AS author_count
  FROM book_author
  GROUP BY book_id) AS na
  JOIN book b
    ON na.book_id = b.book_id
  GROUP BY genre;

Exercice 14 : Nombre de pages lues par les utilisateurs

Exercice : Pour chaque lecteur, indiquez son nom et le nombre total de pages qu'il a lues (c'est-à-dire le nombre de pages de tous les livres qu'il a empruntés). Nous supposons qu'ils ont lu chaque livre en entier. Incluez tous les livres, même ceux qu'ils n'ont pas encore rendus.

N'affichez que les résultats des clients qui ont lu plus de 1 000 pages.

Solution :

SELECT
  patron_name,
  SUM(pages) AS total_pages_read
FROM book b
JOIN book_loan bl
  ON b.book_id = bl.book_id
JOIN patron p
  ON p.patron_id = bl.patron_id
GROUP BY 
  p.patron_id,
  p.patron_name
HAVING SUM(pages) > 1000;

Explication : Joignez trois tables à l'aide d'un lien régulier JOIN: patron, book_loan, et book. Utilisez SUM(pages) pour additionner le nombre de pages de tous les livres empruntés par l'utilisateur. Filtrez avec HAVING SUM(pages) > 1000 pour n'afficher que les utilisateurs qui ont lu plus de 1000 pages.

Remarque: comme nous voulons afficher les utilisateurs qui ont lu plus de 1 000 pages, il n'est pas nécessaire d'utiliser LEFT JOIN ou FULL JOIN ici. Les utilisateurs qui ont lu 0 page seront de toute façon filtrés avec notre condition HAVING.

Exercice 15 : Clients n'ayant pas emprunté de livres

Exercice : Indiquez le nombre total de clients qui n'ont jamais emprunté de livres.

Solution :

SELECT COUNT(p.patron_id)
FROM patron p
WHERE NOT EXISTS (
  SELECT * FROM book_loan
  WHERE patron_id = p.patron_id
);

Explication : Pour n'afficher que les clients qui n'ont jamais emprunté de livres, filtrez le résultat avec une clause WHERE NOT EXISTS. Pour tout utilisateur qui n'a jamais emprunté de livres, il n'y aura pas d'entrée book_loan avec l'adresse id de cet utilisateur. Utilisez une sous-requête pour trouver un ensemble de prêts de livres pour chaque utilisateur, puis utilisez le résultat de cette sous-requête dans la clause WHERE NOT EXISTS. Cela permettra de s'assurer que tous les clients de l'ensemble résultant n'ont pas de book_loanscorrespondants.

Enfin, utilisez la fonction COUNT() pour compter les patron_ids sélectionnés.

Exercice 16 : Auteurs et publics

Exercice : Pour chaque auteur, montrez combien de clients différents ont emprunté leur livre.

Solution :

SELECT
  a.author_name,
  COUNT(DISTINCT patron_id) AS distinct_patrons
FROM author a
JOIN book_author ba
  ON a.author_id = ba.author_id
JOIN book b
  ON b.book_id = ba.book_id
LEFT JOIN book_loan bl
  ON bl.book_id = b.book_id
GROUP BY
  a.author_id,
  a.author_name;

Explication : Joignez quatre tables : author, book_author, book, , et book_loan. Utilisez un JOIN régulier avec les trois premières tables et un LEFT JOIN entre book et book_loan. Le LEFT JOIN garantit que même si le livre n'a jamais été prêté, il apparaîtra dans le résultat.

SELECT Le nom de l'auteur et l'utilisation de COUNT(DISTINCT patron_id) pour compter tous les clients qui ont emprunté des livres. Si les livres de l'auteur n'ont jamais été empruntés, COUNT() renverra 0.

Regroupez les résultats en fonction de l'ID et du nom de l'auteur afin d'éviter les erreurs dont nous avons parlé précédemment.

Exercice 17 : Les livres les plus anciens

Exercice : Trouvez le(s) livre(s) le(s) plus ancien(s) de la base de données (c'est-à-dire le(s) livre(s) dont l'adresse publication_year est la plus ancienne). Ne montrez que deux colonnes : title et publication_year.

N'oubliez pas qu'il peut y avoir plus d'un livre dont l'année de publication est la plus ancienne.

Solution :

SELECT 
  title,
  publication_year
FROM book
WHERE publication_year = (
  SELECT MIN(publication_year)
  FROM book
);

Explication : Utilisez une sous-requête pour trouver les livres les plus anciens. Sélectionnez uniquement le site title et l'année de publication des livres dont le site publication_year est égal à l'année de publication la plus basse du système. Vous pouvez trouver les premiers publication_year avec MIN(publication_year). Utilisez cette expression dans une sous-requête, puis comparez le publication_year de chaque livre au résultat de la sous-requête.

Exercice 18 : Clients les plus actifs

Exercice : Trouvez les noms de tous les clients qui ont emprunté un nombre de livres supérieur à la moyenne. Indiquez le nombre de livres qu'ils ont empruntés ainsi que leur nom.

Solution :

SELECT 
  patron_name,
  COUNT(*) AS loan_count
FROM patron
JOIN book_loan 
  ON patron.patron_id = book_loan.patron_id
GROUP BY patron_name
HAVING COUNT(*) > (
  SELECT COUNT(*)
  FROM book_loan
) / (
  SELECT COUNT(*)
  FROM patron
);

Explication : Joignez les patron et book_loan et regroupez les résultats en fonction du nom et de l'identifiant de l'utilisateur. Pour n'afficher que les utilisateurs qui ont emprunté un nombre de livres supérieur à la moyenne, utilisez la clause HAVING qui compare le nombre de prêts de l'utilisateur actuel avec le nombre moyen de livres empruntés par utilisateur. Cette moyenne est obtenue en divisant le nombre total de prêts par le nombre total d'utilisateurs.

Parce que / en SQL est une division entière (ce qui signifie que le reste est rejeté), utilisez > (supérieur à) et non >= (supérieur ou égal) pour comparer les valeurs dans la clause HAVING.

Exercice 19 : l'auteur le plus productif

Exercice : Trouvez l'auteur qui a écrit le plus de livres.

Solution :

WITH authors_books_count AS (
  SELECT
    author_id,
    COUNT(*) AS book_count
  FROM book_author
  GROUP BY author_id
)
SELECT 
  author_name,
  book_count
FROM author
JOIN authors_books_count abc
  ON author.author_id = abc.author_id
WHERE book_count = (
  SELECT MAX(book_count)
  FROM authors_books_count
);

Explication : Dans l'ETC authors_books_count, nous trouvons le nombre de livres que chaque auteur a écrit en utilisant son adresse id.

Dans la requête externe, nous sélectionnons les auteurs dont le nombre de livres est égal au nombre maximum de livres. Nous utilisons une sous-requête et la fonction MAX() pour sélectionner le nombre maximum de livres dans l'ETC et le comparer à book_count pour chaque auteur.

Vous voulez en savoir plus sur les fonctions agrégées SQL ?

C'est la fin de cette série d'exercices sur les fonctions agrégées SQL. Vos compétences en matière de fonctions agrégées sont maintenant à jour ! Vous pouvez vérifier vos connaissances théoriques avec ces questions d'entretien sur le GROUP BY.

Nous avons couvert différentes fonctions agrégées, GROUP BY, HAVING et bien plus encore ! Nous avons pratiqué différents types de JOINs, les sous-requêtes et le travail avec les NULL. Vous en voulez encore plus ? Jetez un coup d'œil aux exercices de notre énorme piste La pratique du SQL ; elle contient plus de 100 heures de pratique SQL ! Ou essayez notre Pratique SQL mensuellequi propose un nouveau cours de pratique SQL chaque mois !