// travaux pratiques · Fondamentaux en informatique
TP04 - Interroger les données : filtres, jointures et agrégats
- TP 4 / 7
- 8 étapes
- ≈ 50 min
- Accès libre
Objectif : écrire des requêtes SELECT de plus en plus riches sur la base parc : filtres et tris, recherche par sous-réseau, jointures internes et externes, agrégats avec GROUP BY et HAVING, puis enregistrer une requête utile sous forme de vue.
Contexte
Savoir interroger une base, c’est pouvoir répondre seul aux questions qui arrivent chaque semaine : quels serveurs ont été installés avant telle date, quelles machines n’ont reçu aucune intervention, combien de temps l’équipe a passé sur chaque site, quels équipements se trouvent dans tel sous-réseau. Dans une petite équipe, ces requêtes remplacent des heures de tri dans un tableur ; dans une grande DSI, elles servent à alimenter des tableaux de bord, à contrôler un rapprochement entre deux outils ou à préparer un audit. Le langage est le même, seule la volumétrie change.
Chaque requête de ce TP a un résultat connu d’avance, calculé à partir des données chargées au TP03 : comparez systématiquement ce que vous obtenez avec ce qui est attendu.
Prérequis : TP03 terminé (3 sites, 10 équipements, 12 interventions). Connexion : psql -h localhost -U parc_admin -d parc.
Durée estimée : 50 minutes.
Actions à réaliser
1. Sélectionner, filtrer et trier
Une requête SELECT liste les colonnes voulues (évitez SELECT * dans les scripts : la requête casserait si des colonnes étaient ajoutées ou réordonnées), WHERE filtre les lignes et ORDER BY fixe l’ordre. Sans ORDER BY, l’ordre des lignes n’est pas garanti.
SELECT nom_hote, categorie, mise_en_service
FROM equipements
WHERE categorie = 'serveur'
ORDER BY mise_en_service;
Résultat attendu : trois lignes, dans l’ordre srv-fic01 (2021-09-15), srv-ad01 (2022-03-01) et srv-sup01 (2024-01-10).
2. Filtrer sur un motif, une date et un sous-réseau
LIKE compare à un motif où % remplace n’importe quelle suite de caractères (ILIKE fait de même sans tenir compte de la casse). Les dates se comparent comme des nombres. Le type inet apporte l’opérateur <<, « est contenu dans », qui évite des comparaisons de chaînes fragiles.
SELECT nom_hote FROM equipements WHERE nom_hote LIKE 'sw-%';
SELECT nom_hote, mise_en_service FROM equipements WHERE mise_en_service < '2022-01-01';
SELECT nom_hote, adresse_ip FROM equipements WHERE adresse_ip << '192.168.10.0/24';
Résultat attendu : deux commutateurs (sw-siege-01, sw-sophia-01) ; trois équipements mis en service avant 2022 (srv-fic01, sw-siege-01, imp-entrepot) ; cinq équipements dans 192.168.10.0/24 (les trois serveurs, fw-siege et sw-siege-01). Le poste pc-compta-01, en 192.168.20.31, n’en fait pas partie bien qu’il soit au siège.
3. Joindre deux tables
Une jointure interne (JOIN … ON) associe chaque équipement à la ligne de son site grâce à la clé étrangère. Les alias (e, s) raccourcissent l’écriture et lèvent toute ambiguïté entre colonnes de même nom, comme id.
SELECT e.nom_hote, s.nom AS site, s.ville
FROM equipements e
JOIN sites s ON s.id = e.site_id
ORDER BY s.nom, e.nom_hote;
Résultat attendu : dix lignes, regroupées par site dans l’ordre alphabétique : Agence Sophia (3 équipements, Valbonne), Entrepôt (1, Carros), Siège (6, Nice).
4. Trouver les absents avec une jointure externe
Une jointure interne ne garde que les lignes qui ont une correspondance. Pour trouver les équipements sans aucune intervention, on utilise LEFT JOIN, qui conserve toutes les lignes de la table de gauche et remplit de valeurs NULL les colonnes de droite quand il n’y a pas de correspondance ; on filtre ensuite sur ces NULL.
SELECT e.nom_hote
FROM equipements e
LEFT JOIN interventions i ON i.equipement_id = e.id
WHERE i.id IS NULL
ORDER BY e.nom_hote;
Résultat attendu : pc-compta-01, srv-sup01 et sw-sophia-01. Notez qu’on teste IS NULL : la comparaison = NULL ne renvoie jamais vrai en SQL.
5. Agréger par technicien
GROUP BY regroupe les lignes qui partagent une valeur ; les fonctions d’agrégat (count, sum, avg, min, max) calculent un résultat par groupe. HAVING filtre les groupes après le calcul, là où WHERE filtre les lignes avant.
SELECT technicien, count(*) AS nb, sum(duree_min) AS minutes
FROM interventions
GROUP BY technicien
ORDER BY minutes DESC;
SELECT technicien, count(*) AS nb
FROM interventions
GROUP BY technicien
HAVING count(*) >= 3
ORDER BY technicien;
Résultat attendu : amina 4 interventions et 170 minutes, sophie 2 et 135, karim 3 et 130, lucas 3 et 75. La seconde requête ne garde que amina, karim et lucas.
6. Combiner trois tables et un agrégat
On veut le temps passé par site, y compris pour un site sans intervention. La jointure vers interventions est donc externe, count(i.id) ne compte que les interventions réelles (il ignore les NULL, contrairement à count(*)), et coalesce remplace un total vide par 0.
SELECT s.nom AS site, count(i.id) AS nb, coalesce(sum(i.duree_min), 0) AS minutes
FROM sites s
JOIN equipements e ON e.site_id = s.id
LEFT JOIN interventions i ON i.equipement_id = e.id
GROUP BY s.nom
ORDER BY minutes DESC;
Résultat attendu : Siège 8 interventions et 325 minutes, Agence Sophia 3 et 170, Entrepôt 1 et 15. Le total (12 interventions, 510 minutes) correspond au contrôle du TP03.
7. Regrouper par mois
date_trunc ramène un horodatage au début de la période choisie et to_char le met en forme. Le calcul tient compte du fuseau horaire de la session (paramètre TimeZone) ; avec nos données, aucune intervention n’est assez proche d’un changement de mois pour que le résultat en dépende.
SELECT to_char(date_trunc('month', date_intervention), 'YYYY-MM') AS mois,
count(*) AS nb, sum(duree_min) AS minutes
FROM interventions
GROUP BY 1
ORDER BY 1;
Résultat attendu : 2026-10 avec 8 interventions et 355 minutes, 2026-11 avec 4 interventions et 155 minutes.
8. Enregistrer une vue
Une vue est une requête enregistrée sous un nom et interrogée comme une table. Elle simplifie le travail des personnes qui consultent la base et, au TP06, permettra de donner accès à une présentation des données sans exposer les tables elles-mêmes.
CREATE VIEW v_interventions AS
SELECT i.date_intervention, e.nom_hote, s.nom AS site,
i.technicien, i.description, i.duree_min
FROM interventions i
JOIN equipements e ON e.id = i.equipement_id
JOIN sites s ON s.id = e.site_id;
\x auto
SELECT * FROM v_interventions WHERE site = 'Agence Sophia' ORDER BY date_intervention;
Résultat attendu : CREATE VIEW, puis trois interventions (fw-sophia le 21 octobre, pc-sophia-01 le 23 octobre et le 17 novembre). La méta-commande \x auto bascule l’affichage en mode vertical quand les lignes sont trop larges pour le terminal.
Vérification
Le TP est réussi si chaque requête donne exactement le résultat attendu et si \dv liste la vue v_interventions. Contrôle final : SELECT count(*), sum(duree_min) FROM v_interventions; doit renvoyer 12 et 510, ce qui prouve que les jointures de la vue ne perdent ni ne dupliquent aucune intervention.
Problèmes fréquents
- « column “e.nom_hote” must appear in the GROUP BY clause or be used in an aggregate function » : une colonne affichée n’est ni regroupée ni agrégée. Ajoutez-la au
GROUP BYou appliquez-lui une fonction d’agrégat. - « column reference “id” is ambiguous » : la colonne existe dans plusieurs tables de la jointure. Préfixez-la par l’alias de la table (
e.id,i.id). - La jointure externe ne renvoie rien : le filtre a été écrit
WHERE i.id = NULL. UtilisezIS NULL. - Totaux par site gonflés : une jointure sur une mauvaise colonne (par exemple
i.equipement_id = s.id) associe des lignes sans rapport. Vérifiez chaque conditionONet comparez le total général à celui du TP03.
Bonnes pratiques et sécurité
- Testez une requête d’analyse sur une copie ou un serveur secondaire avant de la lancer sur une base de production chargée : une jointure mal écrite peut produire des millions de lignes.
- Dans une application, ne construisez jamais une requête en concaténant une saisie utilisateur : utilisez des requêtes paramétrées, seule protection fiable contre l’injection SQL.
- Limitez les colonnes renvoyées au strict nécessaire, notamment quand elles contiennent des données personnelles.
- Nommez et documentez les vues partagées : elles deviennent rapidement des contrats sur lesquels reposent des tableaux de bord.
Questions de contrôle
1. Quelle différence entre WHERE et HAVING ?
Voir la réponse
WHERE filtre les lignes avant le regroupement ; HAVING filtre les groupes après le calcul des agrégats, il peut donc utiliser count(*) ou sum().
2. Pourquoi utiliser count(i.id) plutôt que count(*) à l’étape 6 ?
Voir la réponse
Avec une jointure externe, un site sans intervention produit une ligne dont les colonnes d’interventions sont NULL : count(*) la compterait (1), count(i.id) l’ignore (0).
3. Que renvoie la condition adresse_ip << '192.168.10.0/24' ?
Voir la réponse
Vrai pour toute adresse strictement contenue dans le réseau 192.168.10.0/24 ; c’est un opérateur du type inet, plus sûr qu’une comparaison de texte avec LIKE '192.168.10.%'.
Pour aller plus loin
Explorez les fonctions de fenêtrage (OVER), qui calculent par exemple un cumul ou un rang sans regrouper les lignes, et les requêtes avec WITH (expressions de table communes). Notre article PostgreSQL, système de gestion de base de données relationnelle présente les autres usages du moteur. Conservez la base : le TP05 y ajoute un gros volume de données.
// formation sur mesure
Former votre équipe sur ce sujet ?
Azur Secure anime des sessions intra-entreprise, à distance ou dans vos locaux sur la Côte d'Azur, adaptées au niveau de vos équipes.