La question de la responsable
Un rapport en SQL résume beaucoup de lignes en quelques chiffres, comme un nombre de commandes ou un total en euros. Tu vas répondre à la question d'un responsable avec une seule requête.
La responsable d'une boutique en ligne demande : combien de commandes avons-nous livrées, et quelle catégorie rapporte le plus ? Compter à la main prendrait des heures. Une requête répond en une seconde.
À la fin de cette leçon
Remarque
Je peux répondre à la question d'un responsable avec une requête SQL.
Mots clés
| Mot | Ce que ça veut dire | Exemple |
|---|---|---|
| Agrégat | une fonction qui résume plusieurs lignes en une valeur | COUNT(*) |
| Groupe | les lignes qui ont la même valeur dans une colonne | |
| Jointure | le lien entre deux tables par une colonne commune | |
| Alias | un nom court donné à une colonne ou à une table | AS total |
| HAVING | la clause qui garde ou écarte des groupes |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
La base du lab
Remarque
Le lab garde une boutique appelée shop, avec huit tables : categories, suppliers, products, employees, customers, orders, order_items et reviews. Tu les lis et tu ne changes rien. Si le lab est arrêté, appuie sur Start the lab.
Accepte 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.
COUNT, SUM et AVG résument des lignes
Une fonction d'agrégat lit plusieurs lignes et rend une seule valeur. COUNT(*) compte les lignes. SUM(colonne) additionne. AVG(colonne) donne la moyenne.
WHERE choisit les lignes avant le calcul. AS donne un nom lisible à la colonne du résultat : c'est un alias.
Compte les commandes livrées
sql "SELECT COUNT(*) AS commandes_livrees FROM orders WHERE status = 'delivered'"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.
Compte les lignes et les articles
sql "SELECT COUNT(*) AS lignes, SUM(quantity) AS articles FROM order_items"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 : les fonctions d'agrégat
| Fonction | Ce qu'elle rend | Question du responsable |
|---|---|---|
| COUNT(*) | Le nombre de lignes | Combien de commandes ? |
| SUM(quantity) | La somme des valeurs | Combien d'articles vendus ? |
| AVG(price) | La moyenne des valeurs | Quel est le prix moyen ? |
| MAX(price) | La plus grande valeur | Quel est l'article le plus cher ? |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
GROUP BY fait un résultat par groupe
Souvent le responsable veut un chiffre pour chaque groupe : chaque statut de commande, chaque catégorie. GROUP BY colonne range les lignes en groupes, un par valeur. La fonction travaille ensuite sur chaque groupe.
HAVING garde ou écarte les groupes d'après leur résultat. WHERE agit avant le regroupement, HAVING après.
Exemple : trois groupes de commandes
| Ligne | status | Groupe | COUNT(*) du groupe |
|---|---|---|---|
| 1 | delivered | delivered | 3 |
| 2 | pending | pending | 1 |
| 3 | delivered | delivered | 3 |
| 4 | shipped | shipped | 1 |
| 5 | delivered | delivered | 3 |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Compte les commandes de chaque statut
sql "SELECT status, COUNT(*) AS nb FROM orders GROUP BY status ORDER BY nb DESC"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.
Garde les clients de cinq commandes
sql "SELECT customer_id, COUNT(*) AS nb FROM orders GROUP BY customer_id HAVING nb >= 5 ORDER BY nb 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.
Une erreur fréquente
Attention
Beaucoup écrivent WHERE COUNT(*) >= 5. MySQL refuse : WHERE agit avant le regroupement, quand aucun groupe n'existe encore. Pour filtrer sur le résultat d'une fonction d'agrégat, utilise HAVING, placé après GROUP BY.
JOIN relie deux tables par une colonne commune
Le nom d'une catégorie est dans categories. Le prix d'un article est dans products. Pour avoir les deux, relie les tables.
Dans products, la colonne category_id contient le numéro d'une catégorie. Dans categories, ce numéro est id. JOIN ... ON dit quelles colonnes se correspondent. Un alias court, comme p et c, évite de répéter le nom des tables.
Compte les articles de chaque catégorie
sql "SELECT c.name AS categorie, COUNT(*) AS nb FROM products p JOIN categories c ON c.id = p.category_id GROUP BY c.name ORDER BY nb DESC, categorie"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.
Calcule le chiffre d'affaires de chaque catégorie
sql "SELECT c.name AS categorie, SUM(oi.quantity * oi.unit_price) AS ca FROM order_items oi JOIN products p ON p.id = oi.product_id JOIN categories c ON c.id = p.category_id GROUP BY c.name ORDER BY ca DESC, categorie"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.
À toi : le total d'un magasin
-- Le nombre d'articles vendus et le chiffre d'affaires du magasin.
SELECT SUM(v.quantite) AS articles,
SUM(v.quantite * p.prix_euros) AS chiffre_affaires
FROM ventes v
JOIN produits p ON p.id = v.produit_id;
-- Ajoute WHERE p.rayon_id = 1 avant la fin pour ne garder que le rayon Sport.
À toi : les ventes de chaque rayon
-- Le nombre d'articles vendus dans chaque rayon.
SELECT r.nom AS rayon, SUM(v.quantite) AS articles
FROM ventes v
JOIN produits p ON p.id = v.produit_id
JOIN rayons r ON r.id = p.rayon_id
GROUP BY r.nom
ORDER BY articles DESC;
-- Ajoute HAVING SUM(v.quantite) >= 5 avant ORDER BY et relance.
Pour aller plus loin : le panier moyen
Le responsable veut maintenant le montant moyen d'une commande livrée. Il faut d'abord le total de chaque commande, puis la moyenne de ces totaux.
Écris sur papier les deux étapes. Quelle requête donne le total de chaque commande ? Pourquoi ne peut-on pas écrire AVG(SUM(...)) ?