1.2- SQL - Qualité de l'air

Un peu de contexte

En France, la qualité de l'air est surveillée en continu par des associations agréées (Atmo Normandie, Airparif, etc.), qui gèrent environ 500 stations de mesure réparties sur tout le territoire, y compris en outre-mer. Chaque station mesure, heure par heure, la concentration dans l'air d'un ou plusieurs polluants. Ces mesures sont rassemblées par le Laboratoire Central de Surveillance de la Qualité de l'Air, et publiées en données ouvertes sur data.gouv.fr.

Les polluants présents dans la base sont :

Polluants

NO2 (dioxyde d'azote) : émis par les combustions, en particulier par le trafic routier.
O3 (ozone) : il n'est pas émis directement, mais se forme dans l'air à partir d'autres polluants, sous l'effet du rayonnement solaire.
PM10 et PM2.5 : les particules fines, de diamètre inférieur à 10 µm et 2,5 µm respectivement (les PM2.5 font donc partie des PM10). Elles proviennent du chauffage au bois, du trafic, de l'industrie ou de l'agriculture, mais aussi de sources naturelles (sel marin, sable du Sahara...).
SO2 (dioxyde de soufre) : émis principalement par l'industrie (raffineries...) et les volcans.
CO (monoxyde de carbone) : émis par les combustions incomplètes.

Toutes les concentrations sont exprimées en microgrammes par mètre cube d'air (µg/m³).

TP

Ce TP fait suite au précédent : les mots-clés vus dans le premier TP (SELECT, FROM, WHERE, ORDER BY, LIMIT, JOIN, AS, GROUP BY, SUM, COUNT, AVG, MAX, MIN, DISTINCT, UNION, sous-requêtes) sont supposés connus. N'hésitez pas à revenir au premier TP, aux slides du cours ou au memento en cas de doute.

Vous pouvez afficher la liste des tables disponibles dans la base de donnée en exécutant la requête suivante (qui n'est pas à savoir ni à comprendre) :

SQL

Loading editor...

SELECT name FROM sqlite_master WHERE type = 'table';

La base contient les moyennes journalières des concentrations mesurées, du 1er janvier au 27 septembre 2026. Les attributs des cinq tables sont :

Table Regions

clé primaire code : le code de la région.
nom : le nom de la région.
clé étrangère chef_lieu : le code de la commune chef-lieu de la région.

Table Departements

clé primaire code : le code du département (par exemple "76" pour la Seine-Maritime, ou "2A" pour la Corse-du-Sud).
nom : le nom du département.
clé étrangère code_region : le code de la région du département.
clé étrangère chef_lieu : le code de la commune chef-lieu (la préfecture) du département.

Table Communes

Contient toutes les communes de France (environ 35 000), même celles qui n'ont pas de station.

clé primaire code : le code de la commune (ce n'est pas exactement le code postal).
nom : le nom de la commune.
clé étrangère code_departement : le code du département de la commune.

Table Stations

clé primaire code : un identifiant de station.
nom : le nom de la station.
clé étrangère code_commune : le code de la commune où se trouve la station.
implantation : le type d'environnement autour de la station ("Urbaine", "Périurbaine", "Rurale régionale"...).
influence : la principale source de pollution à laquelle la station est exposée : "Trafic" (au bord d'une route), "Industrielle" (près d'un site industriel), ou "Fond" (loin de toute source particulière : la station mesure alors la pollution "moyenne" à laquelle la population est exposée).
altitude : l'altitude de la station, en mètres.

Table Mesures

Contient la concentration moyenne d'un polluant sur une journée, dans une station.

clé étrangère code_station : le code de la station.
date : le jour de la mesure.
polluant : le polluant mesuré ("NO2", "O3", "PM10", "PM2.5", "SO2" ou "CO").
valeur : la concentration moyenne du polluant sur la journée, en µg/m³.

La clé primaire de cette table est (code_station, date, polluant).

Une station ne mesure pas forcément tous les polluants, et il manque des jours (panne, maintenance, station ouverte ou fermée en cours d'année...).
Les dates sont des chaînes de caractères au format "AAAA-MM-JJ", par exemple "2026-03-14" pour le 14 mars 2026.
Il s'agit de données « temps réel », publiées avant leur validation définitive : quelques valeurs peuvent être aberrantes.

1. Découverte de la base de donnée

Question 1

Affichez simplement tous les attributs des cinq tables et familiarisez-vous avec le type des données.

SQL

Loading editor...

SELECT * FROM Regions;

SQL

Loading editor...

SELECT * FROM Departements;

SQL

Loading editor...

SELECT * FROM Communes;

SQL

Loading editor...

SELECT * FROM Stations;

SQL

Loading editor...

SELECT * FROM Mesures;

Question 2

Quel est le code de la commune de Rouen ? En utilisant ce code dans une deuxième requête, affichez les stations situées à Rouen.

SQL

Loading editor...

SQL

Loading editor...

LIMIT n OFFSET k ignore les k premiers enregistrements du résultat, puis garde les n suivants.

Question 3

Affichez le nom et l'altitude des 5 stations les plus hautes, puis des 5 stations suivantes (de la 6e à la 10e plus haute).

La réglementation européenne impose que la moyenne journalière de PM10 ne dépasse pas 50 µg/m³ plus de 35 jours par an.

Quelles stations ont déjà dépassé ce seuil plus de 35 jours en 2026 ? Et plus de 10 jours ?

SQL

Loading editor...

4. Dates

Les chaînes de caractères peuvent être comparées avec <, <=, >, >=, selon l'ordre alphabétique (plus précisément, l'ordre du dictionnaire). Comme les dates sont écrites au format "AAAA-MM-JJ", cet ordre est aussi l'ordre chronologique : on peut donc écrire WHERE date >= '2026-06-01' pour ne garder que les mesures à partir du 1er juin.

Question 14

Écrivez une requête pour afficher, pour chaque polluant, sa concentration moyenne en hiver (janvier et février) et en été (juin, juillet et août), dans un même tableau. Que remarquez-vous ?

SQL

Loading editor...

Question 15

Quels sont les 5 jours où la concentration moyenne de PM2.5, sur toutes les stations, a été la plus élevée ?

SQL

Loading editor...

Question 16

Certaines stations ont cessé d'envoyer des mesures au cours de l'année. Affichez les stations dont la dernière mesure date d'avant le 1er septembre, avec la date de cette dernière mesure.

SQL

Loading editor...

5. Opérations ensemblistes

<requête 1> INTERSECT <requête 2> ne garde que les enregistrements présents dans les résultats des deux requêtes.
<requête 1> EXCEPT <requête 2> garde les enregistrements du résultat de la première requête qui ne sont pas dans celui de la seconde.
Comme pour UNION, les deux requêtes doivent sélectionner le même nombre d'attributs, avec des types compatibles.
<attribut> IN (<sous-requête>) est vrai si la valeur de l'attribut fait partie du résultat de la sous-requête (qui doit sélectionner un seul attribut). On peut aussi écrire NOT IN.

Question 17

Combien de stations mesurent à la fois le NO2 et l'O3 ? Affichez ensuite leurs noms.

SQL

Loading editor...

Question 18

Quels sont les départements qui n'ont aucune station ? Affichez leur code, puis leur nom.

SQL

Loading editor...

6. Tout en même temps

Désormais, toutes les opérations seront nécessaires (pas forcément à chaque question bien sûr).

Vous devez répondre à chacune de ces questions directement, en une seule requête. (Bien sûr, cette requête peut être constituée de requêtes imbriquées.)

Les questions qui suivent sont, autant que possible, classées par ordre croissant de difficulté (l'ordre est indicatif, il ne s'agit que d'une estimation).

Question 19

Pour chaque polluant, quelle est la station qui a la concentration moyenne la plus élevée sur l'année ?

SQL

Loading editor...

Question 20

Dans les communes qui ont à la fois une station de trafic et une station de fond, comparez la concentration moyenne de NO2 mesurée par les stations de trafic et par les stations de fond. Classez les communes par écart décroissant entre les deux.

SQL

Loading editor...

Une table peut être jointe avec elle-même (en lui donnant deux noms différents), pour mettre en relation deux enregistrements de la même table. Par exemple, pour afficher les paires de stations situées dans la même commune : SELECT s1.nom, s2.nom FROM Stations AS s1 JOIN Stations AS s2 ON s1.code_commune = s2.code_commune AND s1.code < s2.code

Question 21

Les PM2.5 font partie des PM10 : le rapport entre la concentration de PM2.5 et celle de PM10, mesurées le même jour dans la même station, indique donc la part des particules les plus fines.

Calculez la moyenne de ce rapport dans chaque région, et classez les régions par ordre décroissant. Que remarquez-vous ?

SQL

Loading editor...

Question 22

L'Organisation Mondiale de la Santé recommande de ne pas dépasser une concentration journalière de 15 µg/m³ de PM2.5.

Quels sont les jours où plus de la moitié des stations mesurant les PM2.5 ont dépassé cette valeur ?

SQL

Loading editor...

Entrez le code donné en cours pour accéder à la solution :

Code d'accès