7 avril 2024 Lucas Tachen

SQL en prépa : Maîtriser les bases de données

Le langage SQL (Structured Query Language) est un outil essentiel pour tout futur ingénieur. Il permet de manipuler et d’interroger des bases de données, ce qui est crucial dans de nombreux domaines, de la recherche scientifique au développement web. Cet article vise à te donner une introduction complète à SQL, en couvrant les notions fondamentales et en les illustrant avec des exemples concrets.

Notions fondamentales

Tables et attributs: Une base de données est constituée de tables, qui sont des ensembles de données organisés en colonnes (attributs) et en lignes (enregistrements).
Entités et relations: Les tables représentent des entités (objets du monde réel) et les liens entre elles sont définis par des relations.
Clés primaires et étrangères: Chaque table possède une clé primaire qui identifie de manière unique chaque enregistrement. Une clé étrangère est une colonne qui référence la clé primaire d’une autre table.

Exemple de base de données

Voici un exemple de base de données que nous utiliserons tout au long de l’article.

Considérons les tables suivantes :

“Etudiants” qui contient les informations des étudiants, tels que leur numéro d’étudiant, leur nom, leur prénom, leur classe (MP, PSI, PT…) et l’identifiant de leur établissement :

NumEtudiant (int, clé primaire)
Nom (varchar(255))
Prenom (varchar(255))
Classe (varchar(4))
IdEtablissement (int)

“Etablissements” qui contient les informations des établissements, telles que l’identifiant de l’établissement et le nom de l’établissement :

IdEtablissement (int, clé primaire)
NomEtablissement (varchar(255))

La table “Etudiants” est liée à la table “Etablissement” par la colonne “IdEtablissement“. Un étudiant est inscrit dans un seul établissement.

Sans rentrer dans les détails, le type des variables est détaillé ici :

int correspond à un nombre entier
varchar(x) correspond à une chaîne de x caractères maximum

La déclaration du type de variable se fait lors de la création de la table, ce qui n’est pas au programme du concours.

Les requêtes

Parcourons à présents les différentes requêtes à connaître. Pour chaque requête un exemple sera donné pour l’expliquer, toujours en utilisant la base de données présentées précédemment.

La commande SELECT et les clauses WHERE et DISTINCT

La commande SELECT permet de sélectionner des données d’une ou plusieurs tables.

La clause WHERE permet de filtrer les résultats selon des conditions spécifiques. Le mot-clé DISTINCT permet de supprimer les doublons.

Exemples

SELECT Nom, Prenom FROM Etudiants WHERE Classe = 'MP' AND IdEtablissement = 1;

Cette requête sélectionne les noms et prénoms des étudiants de la classe MP inscrits dans l’établissement 1.

La clause WHERE permet de filtrer les résultats en fonction de deux conditions : Classe et IdEtablissement.

Le résultat est un tableau avec deux colonnes : Nom et Prenom

Nom | Prenom
------- | --------
Dupont | Jean
Martin | Pierre
...

SELECT DISTINCT NomEtablissement FROM Etablissements;

Cette requête sélectionne les noms des différents établissements.

Le mot-clé DISTINCT permet de supprimer les doublons dans le résultat s’il y en a.

Le résultat est donc une liste des noms de filières, sans doublons :

NomEtablissement
---------------
Louis-le-Grand
Sainte-Geneviève
Henri-IV
Le Parc
...

Les mots-clés LIMIT, OFFSET et ORDER BY

Les mot-clés permettent de trier les données que l’on veut afficher ou modifier les paramètres d’affichage. Voici ceux au programme :

LIMIT limite le nombre de résultats,
OFFSET permet de sauter un certain nombre de résultats en partant du début,
ORDER BY permet de trier les résultats selon un ou plusieurs attributs.

Exemples

SELECT * FROM Etudiants ORDER BY Nom ASC LIMIT 10;

Cette requête sélectionne les 10 premiers étudiants triés par ordre alphabétique croissant du nom.

ORDER BY Nom ASC trie les résultats par la colonne Nom dans l’ordre croissant (ASC).

LIMIT 10 limite le nombre de résultats à 10.

Ce qui donnerait :

NumEtudiant | Nom | Prenom | Classe | IdEtablissement
------- | -------- | -------- | -------- | --------
1 | Abadie | Alexandre | MP | 1
2 | Bernard | Thomas | PSI | 2
3 | ...
...
10 | Petit | Jade | PC | 5

SELECT * FROM Etudiants ORDER BY Prenom DESC OFFSET 5;

Cette requête sélectionne tous les champs de la table Etudiants triés par ordre décroissant du prénom (DESC).

La clause OFFSET 5 permet de sauter les 5 premiers résultats.

NumEtudiant | Nom | Prenom | Classe | IdEtablissement
------- | -------- | -------- | -------- | --------
32 | Martin | Thomas | PSI | 2
31 | Durand | Sophie | MP | 1
30 | ...

Les opérateurs ensemblistes

Intéressons-nous désormais aux opérateurs ensemblistes :

UNION combine les résultats de deux requêtes,
INTERSECT ne conserve que les résultats présents dans les deux requêtes,
EXCEPT soustrait les résultats de la deuxième requête de ceux de la première.

Exemples

SELECT * FROM Etudiants WHERE Classe = 'MP' UNION SELECT * FROM Etudiants WHERE Classe = 'PC';

Cette requête combine les résultats de deux requêtes : les étudiants de la classe MP et ceux de la classe PC.

L’opérateur UNION permet de fusionner les deux ensembles de résultats en supprimant les doublons.

Le résultat est une liste de tous les étudiants en MP et PC.

SELECT * FROM Etudiants WHERE Classe = 'MP' INTERSECT SELECT * FROM Etudiants WHERE IdEtablissement = 1;

Cette requête sélectionne tous les champs de la table Etudiants qui satisfont deux conditions :

La classe est égale à MP,
L’identifiant de l’établissement est égal à 1.

L’opérateur INTERSECT est utilisé pour ne conserver que les enregistrements qui sont présents dans les deux ensembles de résultats. Cette requête utilise donc une jointure implicite.

SELECT * FROM Etudiants WHERE Classe = 'MP' EXCEPT SELECT * FROM Etudiants WHERE IdFiliere = 2;

Cette requête sélectionne tous les champs de la table Etudiants qui répondent à la condition suivante :

La classe est égale à ‘MP’ ET
L’identifiant de l’établissement est différent de 2.

L’opérateur EXCEPT permet d’exclure les enregistrements du second ensemble de résultats qui sont présents dans le premier ensemble.

Les jointures

Les jointures permettent de combiner des données de plusieurs tables. La jointure interne JOIN ne conserve que les enregistrements qui ont des correspondances dans les deux tables.

Exemple

SELECT Etudiants.Nom, Etablissements.NomEtablissement
FROM Etudiants JOIN Etablissements ON Etudiants.IdEtablissement = Etablissements.IdEtablissement;

Cette requête combine les informations des tables Etudiants et Etablissements.

La jointure JOIN permet de lier les deux tables sur la colonne commune IdEtablissement.

Le résultat est un tableau avec deux colonnes : Nom de l’étudiant et nom de l’établissement dans lequel il est inscrit.

Les fonctions d'agrégation

Les fonctions d’agrégation permettent de calculer des statistiques sur les données :

MIN calcule le minimum d’une série
MAX calcule le maximum
AVG calcule la moyenne
COUNT compte le nombre d’éléments de la série

Exemple

SELECT COUNT(*) FROM Etudiants WHERE Classe = 'MP';

Cette requête compte le nombre d’étudiants inscrits en MP.

Filtrage des agrégats

La clause HAVING permet de filtrer les résultats d’une requête d’agrégation.

Exemple

Imaginons que chaque élève possède désormais une note (sa moyenne générale par exemple), qui est renseignée dans la table Etudiants. La requête suivante va alors donner la liste des classes et la moyenne de la classe là où la moyenne de classe est supérieure à 12.

SELECT Classe, AVG(Note)
FROM Etudiants
GROUP BY Classe
HAVING AVG(Note) > 12;

Conclusion

Cet article a présenté les bases du langage SQL et ses applications comme on pourrait les retrouver au concours. Toutefois ces exemples restent simples pour assimiler les bases et ne sont pas suffisant pour établir toutes les requêtes qui seront demandées.