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.
Puisqu'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 faire cette condition sur
toute autre colonne d'Animal ayant la contrainte NOT NULL, et qui donc ne sera NULL que lors d'une jointure externe, en
cas de non-correspondance avec l'autre table.
Il n'y a plus alors 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.
Secret (cliquez pour afficher)
Code : SQL
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 ?
Petit indice, pour avoir le prix d'un animal selon que sa race soit définie ou non, vous pouvez utiliser une fonction que
nous venons de voir au chapitre précédent.
Secret (cliquez pour afficher)
Code : SQL
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;
Partie 3 : Fonctions : nombres, chaînes et agrégats
190/414
www.openclassrooms.com
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.
Puisqu'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 faire cette condition sur
toute autre colonne d'Animal ayant la contrainte NOT NULL, et qui donc ne sera NULL que lors d'une jointure externe, en
cas de non-correspondance avec l'autre table.
Il n'y a plus alors 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.
Secret (cliquez pour afficher)
Code : SQL
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 ?
Petit indice, pour avoir le prix d'un animal selon que sa race soit définie ou non, vous pouvez utiliser une fonction que
nous venons de voir au chapitre précédent.
Secret (cliquez pour afficher)
Code : SQL
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;
Partie 3 : Fonctions : nombres, chaînes et agrégats
190/414
www.openclassrooms.com