La masse salariale, en une question
Une directrice demande : combien de personnes travaillent dans chaque équipe, et combien coûte chaque équipe en tout ?
Les personnes sont dans une table. Les noms d'équipe sont dans une autre. La réponse a besoin des deux.
Relier et regrouper, c'est ainsi qu'une base répond à ce genre de question.
À la fin de cette leçon
Remarque
Je peux relier des tables et les résumer.
Mots clés
| Mot | Ce que ça veut dire | Exemple |
|---|---|---|
| Jointure | met côte à côte les lignes de deux tables, quand une clé correspond | |
| Alias | un nom court donné à une table ou à une colonne | AS masse |
| Agrégat | une fonction qui résume plusieurs lignes en une valeur | SUM(salaire) |
| GROUP BY | fait une ligne de résultat pour chaque valeur différente | |
| HAVING | garde seulement les groupes qui réussissent un test | |
| Jointure à gauche | une jointure qui garde toutes les lignes de la première table |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Une jointure met deux tables côte à côte
Les données sont réparties en tables pour ne rien écrire deux fois. Une jointure les rassemble.
La clause ON dit quelles colonnes se correspondent. C'est en général une clé étrangère d'un côté et une clé primaire de l'autre.
Quand deux tables ont une colonne de même nom, écrivez d'abord la table : suppliers.city.
Lancez les Runs de départ. Ce sont les mêmes qu'avant.
Accepter le certificat du serveur
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfAffichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Un nom court pour la boutique
alias sql='mariadb -h db -u student -pstudent shop -t -e'Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Les articles des fournisseurs français
sql "SELECT products.name, suppliers.name AS fournisseur, suppliers.city FROM products JOIN suppliers ON suppliers.id = products.supplier_id WHERE suppliers.country = 'France' ORDER BY products.name"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Exemple : la correspondance des clés
| membres.nom | membres.equipe_id | equipes.id | equipes.nom |
|---|---|---|---|
| Alice | 1 | 1 | Ventes |
| Chloe | 2 | 2 | Reparations |
| Farid | 3 | 3 | Juridique |
| Une personne d'équipe 9 | 9 | aucune ligne | Perdue par un JOIN simple |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
GROUP BY donne une ligne par valeur
Un agrégat résume plusieurs lignes en une valeur. COUNT(*) compte les lignes. SUM, AVG, MIN et MAX travaillent sur une colonne.
Seul, un agrégat résume toute la table. Avec GROUP BY, vous obtenez un résumé pour chaque valeur différente d'une colonne.
Donnez un nom au résultat avec AS.
Les clients de chaque pays
sql "SELECT country, COUNT(*) AS clients FROM customers GROUP BY country ORDER BY clients DESC, country"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Relier d'abord, regrouper ensuite
sql "SELECT customers.country, COUNT(*) AS commandes FROM orders JOIN customers ON customers.id = orders.customer_id GROUP BY customers.country ORDER BY commandes DESC, customers.country"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Le prix moyen par pays du fournisseur
sql "SELECT suppliers.country, ROUND(AVG(products.price), 2) AS prix_moyen FROM products JOIN suppliers ON suppliers.id = products.supplier_id GROUP BY suppliers.country ORDER BY suppliers.country"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
À vous : résumer par équipe
-- Combien de membres, et quelle masse salariale, par équipe ?
SELECT equipe_id, COUNT(*) AS effectif, SUM(salaire) AS masse
FROM membres
GROUP BY equipe_id
ORDER BY equipe_id;
-- Ajoutez HAVING COUNT(*) > 1 avant ORDER BY.
HAVING teste un groupe
WHERE teste les lignes une à une, avant le regroupement. À ce moment, aucun total n'existe encore.
HAVING teste les groupes, une fois formés. On l'emploie avec un agrégat : HAVING COUNT(*) >= 5.
L'ordre est : WHERE, puis GROUP BY, puis HAVING, puis ORDER BY.
Les clients à 5 commandes ou plus
sql "SELECT customer_id, COUNT(*) AS commandes FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5 ORDER BY commandes DESC, customer_id"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Le même test écrit dans WHERE
sql "SELECT customer_id, COUNT(*) AS commandes FROM orders WHERE COUNT(*) >= 5 GROUP BY customer_id"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Une erreur fréquente
Attention
Beaucoup pensent que WHERE COUNT(*) >= 5 filtre les groupes. C'est une erreur, car WHERE agit avant que le moindre groupe existe. Utilisez WHERE pour les lignes et HAVING pour les groupes.
Une jointure à gauche garde les orphelines
Un JOIN simple perd une ligne sans correspondance. Parfois, c'est justement elle qu'on cherche.
LEFT JOIN garde toutes les lignes de la première table. Là où rien ne correspond, les autres colonnes valent NULL.
Ajoutez WHERE ... IS NULL pour ne lister que les lignes sans correspondance.
Les employés qui n'ont traité aucune commande
sql "SELECT employees.first_name, employees.job_title FROM employees LEFT JOIN orders ON orders.employee_id = employees.id WHERE orders.id IS NULL ORDER BY employees.first_name"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
À vous : chacun à côté de son équipe
-- Chaque membre à côté du nom de son équipe.
SELECT membres.nom, equipes.nom AS equipe
FROM membres
JOIN equipes ON equipes.id = membres.equipe_id
ORDER BY membres.nom;
-- Ajoutez WHERE equipes.nom = 'Reparations' avant ORDER BY.
À vous : les équipes vides
-- Les équipes sans aucun membre : la jointure à gauche les garde.
SELECT equipes.nom, membres.nom AS membre
FROM equipes
LEFT JOIN membres ON membres.equipe_id = equipes.id
WHERE membres.id IS NULL;
-- Retirez la ligne WHERE : que voyez-vous pour Finance ?
Pour aller plus loin
La table employees a une colonne manager_id : le numéro du responsable, qui est lui aussi un employé. On peut donc relier la table à elle-même.
Donnez-lui deux alias, e pour l'employé et m pour le responsable. Listez chaque employé à côté du prénom de son responsable, sans perdre la directrice, qui n'en a aucun.