Question 1Lequel de ces résultats est correct pour SELECT ROUND(3.84); ?
Exécutez une requête pour voir les résultats
Fonctions ③ — Fonctions mathématiques (ROUND / FLOOR / CEILING) et COALESCE
Le troisième des trois articles sur les fonctions SQL. Couvre les fonctions mathématiques ROUND / FLOOR / CEILING / POWER et COALESCE — qui renvoie la première valeur non-NULL — en utilisant des jeux de données staff et test-score chargés depuis des CSV.
Données utilisées dans cet article — staff et test_score
Le dernier article sur les fonctions couvre les fonctions mathématiques et COALESCE. Avec les fonctions math, nous gérerons arrondi, plancher, plafond et exponentiation sur des valeurs numériques comme la colonne salary. Avec COALESCE, nous couvrirons le remplacement de NULL par une autre valeur.
Cet article utilise deux CSV. La première moitié (fonctions math) utilise la même table staff qu'avant (10 employés). La seconde moitié (COALESCE) introduit une nouvelle table test_score (8 étudiants × 3 tentatives de test, avec des NULL). Dans test_score, les colonnes score_1 / score_2 / score_3 ont des NULL là où un étudiant était absent et n'a pas passé ce test — parfait pour un scénario réaliste comme « utiliser le score du premier test que l'étudiant a pu passer » afin d'apprendre comment fonctionne COALESCE.
Avant de te lancer dans les exercices, vérifions les définitions de colonnes et les données d'exemple des deux tables — staff et test_score.
① Exécute PRAGMA table_info(staff); et PRAGMA table_info(test_score); pour voir les colonnes des deux.
② Exécute SELECT * FROM staff LIMIT 5; et SELECT * FROM test_score LIMIT 5; pour prévisualiser les 5 premières lignes.
Fonctions mathématiques — ROUND / FLOOR / CEILING / POWER
Voici les 4 fonctions principales pour façonner les valeurs numériques :
- ROUND(x, n) : arrondit à n décimales. Omets n et elle arrondit à un entier
- FLOOR(x) : plancher (arrondit vers l'infini négatif)
- CEILING(x) ou CEIL(x) : plafond (arrondit vers l'infini positif)
- POWER(x, y) : exponentiation (x à la puissance y)
-- 1) ROUND : arrondi
SELECT ROUND(3.14); -- 3 (entier)
SELECT ROUND(3.14, 1); -- 3.1 (1 décimale)
-- 2) FLOOR : plancher
SELECT FLOOR(3.84); -- 3
-- Note : CEILING fonctionne sur MySQL / PostgreSQL / Oracle / SQL Server — SELECT CEILING(3.14); → 4
-- 3) POWER : exponentiation
SELECT POWER(3, 4); -- 81 (3 à la 4e)
SELECT POWER(2, 10); -- 1024
-- 4) Sur une colonne — arrondir le salaire mensuel
SELECT name, salary, ROUND(salary / 12.0) AS monthly
FROM staff;
| Entrée | ROUND (arrondi) | FLOOR (plancher) | CEILING (plafond / non disponible dans la console de ce cours) |
|---|---|---|---|
| 3.14 | 3 | 3 | 4 |
| 3.84 | 4 | 3 | 4 |
| 3.50 | 4 | 3 | 4 |
| -3.14 | -3 | -4 (vers négatif) | -3 |
Imagine une requête : « divise le salaire annuel par 12 pour obtenir le salaire mensuel, affiché comme un entier arrondi ». (Si tu l'exécutes correctement, une explication apparaîtra.)
① Depuis la table staff, extrais name, salary et `ROUND(salary / 12.0)` avec l'alias `monthly` — 3 colonnes au total.
② Trie par monthly décroissant.
③ Vérifie que le résultat a 10 lignes et commence par Frank Tanaka 7,200,000 / 600,000.
Exécutez une requête pour voir les résultats
Imagine une requête : « estimer le salaire annuel dans 3 ans, en supposant une augmentation de 10% chaque année ». Multiplier par le même facteur plusieurs fois peut s'écrire en une seule expression avec `POWER(x, y)` (x à la puissance y). Trois augmentations consécutives de × 1.1 deviennent salary * POWER(1.1, 3), ce qui est la même chose que salary * 1.331.
① Depuis la table staff, extrais name, salary et *`ROUND(salary POWER(1.1, 3)) avec l'alias salary_after_3y`** — 3 colonnes au total (ROUND garde le résultat en entier).
② Trie par salary_after_3y décroissant et garde seulement les 5 premières lignes.
③ Vérifie que le résultat est 5 lignes, commençant par Frank Tanaka 7,200,000 / 9,583,200 et David Sato 6,800,000 / 9,050,800 en ligne 2.
Exécutez une requête pour voir les résultats
Imagine une requête : « dans l'affichage du salaire mensuel, vérifie comment l'arrondi vs le plancher changent le résultat ». Place les 2 fonctions d'arrondi côte à côte sur la même expression pour voir la différence de comportement en un coup d'œil.
① Depuis la table staff, extrais name, salary, `ROUND(salary / 12.0)` comme `round_monthly`, et `FLOOR(salary / 12.0)` comme `floor_monthly` — 4 colonnes au total.
② Trie par salary croissant et garde seulement les 5 premières lignes.
③ Vérifie que le résultat est 5 lignes dans l'ordre Emi / Carol / Iris / Alice / Bob. Pour Alice (dont le salaire se divise exactement par 12), round_monthly et floor_monthly devraient être égaux ; pour les autres ils devraient différer (la partie décimale change le comportement).
Exécutez une requête pour voir les résultats
COALESCE — renvoyer la première valeur non-NULL
`COALESCE(valeur1, valeur2, valeur3, ...)` évalue ses arguments de gauche à droite et renvoie le premier qui n'est pas NULL. S'ils sont tous NULL, elle renvoie NULL. Utilise-la chaque fois que tu veux un secours pour NULL : « utilise la colonne principale si elle a une valeur, sinon la colonne de secours, sinon une valeur par défaut ».
Cet article utilise la table test_score. Pour chaque student_id, score_1 / score_2 / score_3 contiennent les scores des 1er, 2e et 3e tests, avec NULL là où l'étudiant était absent. Passer (score_1, score_2, score_3, 0) à COALESCE te donne le score du premier test que l'étudiant a pu passer, traité comme « score final ». Ajouter 0 à la fin signifie « s'il a raté les trois, traiter comme 0 ».
-- 1) Vérifier avec des valeurs littérales
SELECT COALESCE(NULL, NULL, 'C'); -- 'C'
SELECT COALESCE(NULL, NULL, NULL); -- NULL
-- 2) Extraire le score final depuis la table test_score
SELECT student_id, name, score_1, score_2, score_3,
COALESCE(score_1, score_2, score_3) AS first_score
FROM test_score;
-- 3) Utiliser une valeur par défaut — « absent partout = 0 point »
SELECT student_id, name,
COALESCE(score_1, score_2, score_3, 0) AS final_score
FROM test_score;
La version à 2 arguments peut s'écrire avec IFNULL
Pour le cas plus simple à 2 arguments — « si NULL, utilise une valeur par défaut ; sinon, la valeur originale » — tu peux aussi écrire IFNULL(colonne, default) (supporté à la fois dans la console de ce cours et en MySQL). COALESCE(email, 'non enregistré') et IFNULL(email, 'non enregistré') sont exactement identiques.
Quand tu as besoin de 3 arguments ou plus, utilise COALESCE. Comme COALESCE est le standard SQL, la même forme fonctionne sur PostgreSQL, Oracle, SQL Server, etc. — c'est le choix le plus portable.
Imagine une requête : « nous avons fait 3 tests, mais il y a eu des absences — utilise le score du premier test que chaque étudiant a pu passer comme score final ».
① Depuis la table test_score, extrais student_id, name, score_1, score_2, score_3, et `COALESCE(score_1, score_2, score_3)` avec l'alias `first_score` — 6 colonnes au total.
② Vérifie que le résultat est 8 lignes : Alice 85 (score_1 est non-NULL), Bob 78 (score_1 est NULL donc score_2 est choisi), Carol 95 (score_1 et score_2 sont NULL donc score_3), Dave NULL (tous NULL).
Exécutez une requête pour voir les résultats
Imagine une requête : « donne à chaque étudiant un score final. Traite les étudiants systématiquement absents comme 0, et récompense le top 3 par score final ».
① Depuis la table test_score, extrais student_id, name, et `COALESCE(score_1, score_2, score_3, 0)` avec l'alias `final_score` — 3 colonnes au total.
② Trie par final_score décroissant et garde seulement les 3 premières lignes.
③ Vérifie que le résultat est 3 lignes : Carol 95 / Frank 90 / Grace 88.
Exécutez une requête pour voir les résultats
QUIZ
Vérification des connaissances
Répondez à chaque question une par une.
Question 2Que renvoie SELECT FLOOR(3.84), CEILING(3.14); ?
Question 3Que renvoie SELECT COALESCE(NULL, NULL, 'C', 'D'); ?