• 40 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

Ce cours existe en livre papier.

course.header.alt.is_certifying

Vous pouvez être accompagné et mentoré par un professeur particulier par visioconférence sur ce cours.

J'ai tout compris !

Mis à jour le 22/05/2019

Exercices sur les agrégats

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Jusqu'à maintenant, tout a été très théorique. Or, la meilleure façon d'apprendre, c'est la pratique. Voici donc quelques exercices que je vous conseille de faire.
S'il vaut mieux que vous essayiez par vous-mêmes avant de regarder la solution, ne restez cependant pas bloqué trop longtemps sur un exercice, et prenez toujours le temps de bien comprendre la solution.

Du simple…

1. Combien de races avons-nous dans la table Race ?

SELECT COUNT(*) 
FROM Race;

Simple échauffement.

2. De combien de chiens connaissons-nous le père ?

SELECT COUNT(pere_id)
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
WHERE Espece.nom_courant = 'Chien';

L'astuce ici était de ne pas oublier de donner la colonne pere_id en paramètre à COUNT(), pour ne compter que les lignes où pere_id est non NULL. Si vous avez fait directement WHERE espece_id = 1  au lieu d'utiliser une jointure pour sélectionner les chiens, ce n'est pas bien grave.

3. Quelle est la date de naissance de notre plus jeune femelle ?

SELECT MAX(date_naissance) 
FROM Animal
WHERE sexe = 'F';

4. En moyenne, quel est le prix d'un chien ou d'un chat de race, par espèce, et en général ?

SELECT nom_courant AS Espece, AVG(Race.prix) AS prix_moyen
FROM Race
INNER JOIN Espece ON Race.espece_id = Espece.id
WHERE Espece.nom_courant IN ('Chat', 'Chien')
GROUP BY Espece.nom_courant WITH ROLLUP;

Ne pas oublier WITH ROLLUP  pour avoir le résultat général.

5. Combien avons-nous de perroquets mâles et femelles, et quels sont leurs noms (en une seule requête, bien sûr) ?

SELECT sexe, COUNT(*), GROUP_CONCAT(nom SEPARATOR ', ')
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE nom_courant = 'Perroquet amazone'
GROUP BY sexe;

Il suffisait de se souvenir de la méthode GROUP_CONCAT()  pour pouvoir réaliser simplement cette requête. Peut-être avez-vous groupé sur l'espèce aussi (avec nom_courant ou autre). Ce n'était pas nécessaire, puisque l'on avait restreint à une seule espèce avec la clause WHERE. Cependant, cela n'influe pas sur le résultat, mais sur la rapidité de la requête.

…Vers le complexe

1. Quelles sont les races dont nous ne possédons aucun individu ?

SELECT Race.nom, COUNT(Animal.race_id) AS nombre
FROM Race
LEFT JOIN Animal ON Animal.race_id = Race.id
GROUP BY Race.nom
HAVING nombre = 0;

Ici, il ne fallait pas oublier de faire une jointure externe (LEFT  ou RIGHT, selon votre requête), ainsi que de mettre la colonne Animal.race_id (ou Animal.id, ou Animal.espece_id, mais c'est moins intuitif) en paramètre de la fonction COUNT().

2. Quelles sont les espèces (triées par ordre alphabétique du nom latin) dont nous possédons moins de cinq mâles ?

SELECT Espece.nom_latin, COUNT(espece_id) AS nombre
FROM Espece
LEFT JOIN Animal ON Animal.espece_id = Espece.id
WHERE sexe = 'M' OR Animal.id IS NULL
GROUP BY Espece.nom_latin
HAVING nombre < 5;

À nouveau, une jointure externe et espece_id en argument de COUNT(), mais il y avait ici une petite subtilité en plus.
Puisque l'on demandait des informations sur les mâles uniquement, il fallait une condition WHERE sexe = 'M'. Mais cette condition fait que les lignes de la jointure provenant de la table Espece n'ayant aucune correspondance dans la table Animal sont éliminées également (puisque forcément, toutes les colonnes de la table Animal, dont sexe, seront à NULL  pour ces lignes). Par conséquent, il fallait ajouter une condition permettant de garder ces fameuses lignes (les espèces pour lesquelles on n'a aucun individu, donc aucun mâle). Il fallait donc ajouter OR Animal.id IS NULL, ou mettre cette condition sur toute autre colonne d'Animal ayant la contrainte NOT NULL, et qui ne sera NULL  que lors d'une jointure externe, en cas de non-correspondance avec l'autre table.
Il n'y a alors plus qu'à ajouter la clause HAVING  pour sélectionner les espèces ayant moins de cinq mâles.

3. Combien de mâles et de femelles de chaque race avons-nous, avec un compte total intermédiaire pour les races (mâles et femelles confondues) et pour les espèces ? Afficher le nom de la race et le nom courant de l'espèce.

SELECT Animal.sexe, Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
INNER JOIN Race ON Animal.race_id = Race.id
WHERE Animal.sexe IS NOT NULL
GROUP BY Espece.nom_courant, Race.nom, sexe WITH ROLLUP;

Deux jointures sont nécessaires pour pouvoir afficher les noms des races et des espèces. Il suffit alors de ne pas oublier l'option WITH ROLLUP  et de mettre les critères de regroupement dans le bon ordre pour avoir les super-agrégats voulus.

4. Quel serait le coût, par espèce et au total, de l'adoption de Parlotte, Spoutnik, Caribou, Cartouche, Cali, Canaille, Yoda, Zambo et Lulla ?

SELECT Espece.nom_courant, SUM(COALESCE(Race.prix, Espece.prix)) AS somme
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
LEFT JOIN Race ON Race.id = Animal.race_id
WHERE Animal.nom IN ('Parlotte', 'Spoutnik', 'Caribou', 'Cartouche', 'Cali', 'Canaille', 'Yoda', 'Zambo', 'Lulla')
GROUP BY Espece.nom_courant WITH ROLLUP;

C'est ici la fonction SUM()  qu'il fallait utiliser, puisque l'on veut le prix total par groupe. Sans oublier le WITH ROLLUP  pour avoir également le prix total tous groupes confondus.
Quant au prix de chaque animal, c'est typiquement une situation où l'on peut utiliser COALESCE()  !

Et voilà pour les nombres et les chaînes de caractères ! Notez que les fonctions d'agrégat sont parmi les plus utilisées donc soyez bien sûr d'avoir compris comment elles fonctionnent, couplées à GROUP BY  ou non.

Exemple de certificat de réussite
Exemple de certificat de réussite