Bases de données et SQL : questions d’entraînement avec explications

1Ce qu’est une base de données relationnelle

Une base de données relationnelle stocke les données dans des tables. Une table ressemble à un tableur : chaque ligne est un enregistrement, chaque colonne est une propriété.

Une boutique peut avoir une table customers avec les colonnes id, name et city, et une table orders avec les colonnes id, customer_id et total.

Une clé primaire est une colonne, ou un ensemble de colonnes, qui identifie chaque ligne de façon unique. Elle ne peut jamais se répéter ni être vide. Ici, c’est l’id de chaque table.

Une clé étrangère est une colonne qui fait référence à la clé primaire d’une autre table. Dans orders, customer_id indique quel client a passé la commande.

C’est ainsi que les tables sont liées : un client peut avoir plusieurs commandes, mais chaque commande appartient à un seul client. C’est une relation un-à-plusieurs.

Le SQL (Structured Query Language) est le langage qui sert à créer des tables, modifier des données et les interroger.

2Lire des données avec SELECT

La plupart des questions portent sur l’instruction SELECT. Elle est construite à partir de clauses, toujours écrites dans cet ordre :

• SELECT liste les colonnes voulues. DISTINCT supprime les lignes en double.
• FROM nomme la table, ou les tables jointes.
• WHERE ne garde que les lignes qui remplissent une condition, par exemple WHERE total > 20.
• GROUP BY regroupe les lignes ayant la même valeur, pour pouvoir compter ou additionner par groupe.
• HAVING ne garde que les groupes qui remplissent une condition. Elle peut utiliser des agrégats, ce que WHERE ne peut pas.
• ORDER BY trie le résultat, par ordre croissant par défaut ; ajoutez DESC pour avoir le plus grand d’abord.

Les fonctions d’agrégation résument plusieurs lignes en une seule valeur : COUNT, SUM, AVG, MIN et MAX.

La base de données n’exécute pas les clauses dans l’ordre où vous les écrivez. Elle exécute d’abord FROM, puis WHERE, GROUP BY, HAVING, SELECT et enfin ORDER BY.

D’autres instructions modifient les données : INSERT ajoute des lignes, UPDATE modifie des lignes et DELETE supprime des lignes. CREATE, ALTER et DROP modifient les tables elles-mêmes.

3Jointures, normalisation et transactions

Une jointure combine les lignes de deux tables à l’aide d’une condition, généralement clé étrangère = clé primaire :

• INNER JOIN (ou JOIN) ne garde que les lignes qui ont une correspondance dans les deux tables.
• LEFT JOIN garde toutes les lignes de la table de gauche. Quand il n’y a pas de correspondance, les colonnes de la table de droite valent NULL.
• NULL signifie qu’une valeur est manquante ou inconnue. Testez-la avec IS NULL, jamais avec = NULL.

La normalisation scinde les tables pour que chaque fait soit stocké une seule fois. Cela évite de mettre à jour le même fait à plusieurs endroits.

• 1FN : chaque cellule contient une valeur atomique, sans groupes répétitifs.
• 2FN : 1FN, et chaque colonne hors clé dépend de la clé entière, pas d’une partie d’une clé composée.
• 3FN : 2FN, et aucune colonne hors clé ne dépend d’une autre colonne hors clé.

Une transaction regroupe des instructions en une seule unité. COMMIT la rend permanente et ROLLBACK l’annule. Les transactions suivent les propriétés ACID : atomicité (tout ou rien), cohérence, isolation et durabilité.

4La méthode pas à pas

Pour écrire une requête, ou déterminer ce que renvoie une requête donnée :

1. Trouvez les tables qui contiennent les données, et comment elles sont reliées par des clés.
2. Écrivez FROM avec les jointures. Choisissez LEFT JOIN quand les lignes sans correspondance doivent rester.
3. Ajoutez WHERE pour les conditions sur des lignes individuelles.
4. Si la question dit « par » ou « pour chaque », ajoutez GROUP BY et l’agrégat.
5. Ajoutez HAVING pour les conditions sur l’agrégat, puis ORDER BY si l’ordre compte.
6. Vérifiez le résultat sur un petit exemple : listez les lignes après chaque clause, dans l’ordre où la base de données les exécute.

Pour une question de théorie, nommez d’abord la notion (une clé, un type de jointure, une forme normale, une propriété ACID), puis choisissez la proposition qui correspond à sa définition.

5Un exemple détaillé

Suivez chaque étape. Essayez de deviner la suivante avant de l’ouvrir.

6Erreurs fréquentes

• Utiliser un agrégat dans WHERE. Les conditions sur COUNT, SUM ou AVG vont dans HAVING.
• Écrire = NULL. Toute comparaison avec NULL est inconnue ; utilisez IS NULL ou IS NOT NULL.
• Utiliser un INNER JOIN quand les lignes sans correspondance doivent rester. Utilisez un LEFT JOIN.
• Oublier la condition de jointure. Sans ON, chaque ligne est associée à chaque ligne (un produit cartésien).
• Sélectionner une colonne qui n’est ni dans GROUP BY ni dans un agrégat.
• Confondre DELETE (supprime des lignes) et DROP TABLE (supprime toute la table).
• Exécuter UPDATE ou DELETE sans WHERE. Cela modifie chaque ligne de la table.

7À quoi ressemblent les questions d’examen

• Facile : termes clés et faits. Ce que font SELECT, WHERE, ORDER BY ou DELETE, les clés primaires et étrangères, ce que signifie NULL.
• Moyen : application des notions. La bonne requête pour une tâche, WHERE contre HAVING, jointures internes contre externes, les formes normales, ACID.
• Difficile : cas et analyse. Sous-requêtes, niveaux d’isolation et leurs anomalies, FNBC, fonctions de fenêtrage, index et performance des requêtes, injection SQL.

Quand une requête est donnée, simulez-la sur quelques lignes dans l’ordre FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. La plupart des pièges apparaissent en trois lignes.