Requêtes SQL avec critères et fonctions d'agrégation

Introduction

Le chapitre précédent a posé les bases du SQL : créer des tables, insérer des données, faire des sélections simples et des jointures. Ce chapitre va plus loin : on apprend à interroger finement les données avec des conditions élaborées, des calculs statistiques, et des requêtes qui s'appellent entre elles.

Ces techniques sont celles que vous utiliserez le plus en pratique pour analyser et exploiter des bases de données réelles.

La grammaire d'une requête SQL

SELECT colonnes
FROM table
[WHERE condition]
[GROUP BY colonne]
[HAVING condition]
[ORDER BY colonne]
[LIMIT n];

Chaque clause joue un rôle précis. Ce qui rend SQL particulier : l'ordre d'écriture n'est pas l'ordre d'exécution.

Filtres avec WHERE

SELECT TitreFilm, AnneeSortie
FROM FILM
WHERE Pays = 'USA' AND AnneeSortie BETWEEN 2000 AND 2010;

Utilisation de plusieurs conditions avec AND, OR, BETWEEN, LIKE :

SELECT NomActeur, PrenomActeur
FROM ACTEURS
WHERE NomActeur LIKE 'Mac%' OR DateNaissanceActeur BETWEEN '1980-01-01' AND '1990-12-31';

Opérateurs disponibles dans WHERE

Utilisation de jokers avec LIKE

-- Tous les films américains ou britanniques sortis après 2010
WHERE (Pays = 'USA' OR Pays = 'UK') AND AnneeSortie > 2010

Le piège des valeurs NULL

-- ❌ Ne retourne RIEN, même si des lignes ont DateSortie = NULL
WHERE DateSortie = NULL

-- ✅ Correct
WHERE DateSortie IS NULL

-- ❌ N'inclut pas les lignes avec NULL dans DateSortie
WHERE DateSortie != '2020-01-01'

-- ✅ Inclut les NULL
WHERE DateSortie != '2020-01-01' OR DateSortie IS NULL

Fonctions d'agrégation

Les fonctions d'agrégation calculent une valeur à partir de plusieurs lignes. Elles sont incontournables pour produire des statistiques.

Exemples sur l'ensemble de la table

-- Nombre de films sortis en 2009
SELECT COUNT(*) AS nb_films
FROM FILM
WHERE AnneeSortie = 2009;

-- Plus petit et plus grand budget de la base
SELECT MIN(Budget) AS budget_min, MAX(Budget) AS budget_max
FROM FILM;

-- Budget moyen arrondi des films américains
SELECT ROUND(AVG(Budget), 2) AS budget_moyen
FROM FILM
WHERE Pays = 'USA';

Regrouper avec GROUP BY

GROUP BY divise les lignes en groupes selon une colonne, et les fonctions d'agrégation s'appliquent à chaque groupe séparément.

SELECT Pays, COUNT(*) AS nb_films, AVG(Budget) AS budget_moyen
FROM FILM
GROUP BY Pays;

-- Budget total cumulé par année
SELECT AnneeSortie, SUM(Budget) AS total_budget
FROM FILM
GROUP BY AnneeSortie;

Filtrer après regroupement : HAVING

HAVING s'applique après GROUP BY pour filtrer les groupes selon une condition sur les agrégats.

-- Pays ayant produit plus de 5 films
SELECT Pays, COUNT(*) AS nb
FROM FILM
GROUP BY Pays
HAVING nb > 5;

-- Années dont le budget cumulé dépasse 500 millions
SELECT AnneeSortie, SUM(Budget) AS total_budget
FROM FILM
GROUP BY AnneeSortie
HAVING total_budget > 500000000;

Combiner WHERE et HAVING

Les deux clauses peuvent coexister : elles n'agissent pas au même moment.

-- Années avec au moins 3 films américains et un budget moyen supérieur à 10M
SELECT AnneeSortie, COUNT(*) AS nb_films, AVG(Budget) AS budget_moyen
FROM FILM
WHERE Pays = 'USA'                       -- 1. filtre les lignes (films USA seulement)
GROUP BY AnneeSortie                     -- 2. groupe par année
HAVING nb_films >= 3                     -- 3. filtre les groupes
  AND AVG(Budget) > 10000000;

Différence entre WHERE et HAVING

-- ❌ Erreur : AVG ne peut pas être utilisé dans WHERE
SELECT Pays FROM FILM
WHERE AVG(Budget) > 10000000;

-- ✅ Correct
SELECT Pays FROM FILM
GROUP BY Pays
HAVING AVG(Budget) > 10000000;

Requêtes imbriquées (sous-requêtes)

Une sous-requête est un SELECT placé à l'intérieur d'une autre requête. Elle est exécutée en premier, et son résultat est utilisé par la requête externe.

Sous-requêtes scalaires

-- Films dont le budget est supérieur à la moyenne
SELECT TitreFilm, Budget
FROM FILM
WHERE Budget > (
  SELECT AVG(Budget) FROM FILM
);

-- Film(s) ayant le budget maximum
SELECT TitreFilm, Budget
FROM FILM
WHERE Budget = (
  SELECT MAX(Budget) FROM FILM
);

-- Films sortis la même année qu'Inception
SELECT TitreFilm
FROM FILM
WHERE AnneeSortie = (
  SELECT AnneeSortie FROM FILM WHERE TitreFilm = 'Inception'
);

Sous-requêtes avec IN et NOT IN

-- Acteurs plus jeunes que le plus ancien enregistré
SELECT NomActeur, PrenomActeur
FROM ACTEURS
WHERE DateNaissanceActeur > (
  SELECT MIN(DateNaissanceActeur) FROM ACTEURS
);

-- Films dont le budget correspond à celui d'un film de 2020
SELECT TitreFilm
FROM FILM
WHERE Budget IN (
  SELECT Budget FROM FILM WHERE AnneeSortie = 2020
);

-- Films dont le titre n'existe pas en 2010
SELECT TitreFilm
FROM FILM
WHERE TitreFilm NOT IN (
  SELECT TitreFilm FROM FILM WHERE AnneeSortie = 2010
);

Requêtes avancées sans jointure

Nombre de films par année (seulement si >= 3)

SELECT AnneeSortie, COUNT(*) AS nb
FROM FILM
GROUP BY AnneeSortie
HAVING nb >= 3
ORDER BY AnneeSortie;

Top 3 des années avec le plus gros budget cumulé

SELECT AnneeSortie, SUM(Budget) AS total
FROM FILM
GROUP BY AnneeSortie
ORDER BY total DESC
LIMIT 3;

Film le plus cher de chaque année (sous-requête corrélée)

SELECT f1.TitreFilm, f1.AnneeSortie, f1.Budget
FROM FILM f1
WHERE f1.Budget = (
  SELECT MAX(f2.Budget)
  FROM FILM f2
  WHERE f2.AnneeSortie = f1.AnneeSortie
);

La sous-requête est corrélée : elle est réévaluée pour chaque ligne de f1, en cherchant le budget maximum de la même année.

Réalisateurs ayant sorti plus de films que la moyenne

SELECT NomRealisateur, PrenomRealisateur, COUNT(*) AS nb_films
FROM FILM
GROUP BY NomRealisateur, PrenomRealisateur
HAVING nb_films > (
  SELECT AVG(nb)
  FROM (
    SELECT COUNT(*) AS nb
    FROM FILM
    GROUP BY NomRealisateur, PrenomRealisateur
  ) AS sous_stats
);

Opérateurs ALL et ANY

-- Films strictement plus chers que TOUS les autres films de leur année
SELECT f1.TitreFilm, f1.AnneeSortie, f1.Budget
FROM FILM f1
WHERE f1.Budget > ALL (
  SELECT f2.Budget
  FROM FILM f2
  WHERE f2.AnneeSortie = f1.AnneeSortie AND f2.TitreFilm <> f1.TitreFilm
);

-- Films plus chers qu'AU MOINS UN film de 2010
SELECT TitreFilm, Budget
FROM FILM
WHERE Budget > ANY (
  SELECT Budget FROM FILM WHERE AnneeSortie = 2010
);

Tri des résultats : ORDER BY

-- Films américains triés par budget décroissant
SELECT TitreFilm, AnneeSortie, Budget
FROM FILM
WHERE Pays = 'USA'
ORDER BY Budget DESC;

Trier par plusieurs colonnes

-- D'abord par année croissante, puis par budget décroissant dans chaque année
SELECT TitreFilm, AnneeSortie, Budget
FROM FILM
ORDER BY AnneeSortie ASC, Budget DESC;

L'ordre de tri est appliqué colonne par colonne : si deux films ont la même année, le budget départage.

Trier après agrégation

-- Années triées par nombre de films décroissant
SELECT AnneeSortie, COUNT(*) AS nb_films
FROM FILM
GROUP BY AnneeSortie
ORDER BY nb_films DESC;

On peut utiliser l'alias défini dans SELECT directement dans ORDER BY (car ORDER BY s'exécute après SELECT).

Trier les valeurs NULL

Le comportement par défaut des NULL varie selon le SGBD. Pour forcer leur position en MySQL :

-- Dates connues en premier, NULL en dernier
ORDER BY DateSortie IS NULL ASC, DateSortie ASC;

DateSortie IS NULL retourne 0 (faux) pour les valeurs renseignées et 1 (vrai) pour les NULL. En triant ASC, les 0 viennent avant les 1.