1.1- SQL - Enseignement supérieur

Un peu de contexte

Une base de données est une collection organisée de données, structurée pour être stockée, consultée et mise à jour efficacement. Dans une base de données relationnelle (celles qui sont au programme), les données y sont organisées sous forme de tables (un peu comme des feuilles de calcul), où chaque ligne (ou enregistrement) représente un objet, et chaque colonne (ou attribut) une de ses caractéristiques.

Dans une table, un (ou plusieurs) attribut(s) forme la clé primaire : une valeur qui identifie de façon unique chaque enregistrement (deux enregistrements ne peuvent jamais avoir la même clé primaire). Des tables différentes peuvent être reliées entre elles : par exemple, une table d'élèves peut faire référence à une table d'établissements, via une clé étrangère (typiquement, la clé primaire de l'établissement correspondant).

Le langage SQL (Structured Query Language) est le langage utilisé pour interagir avec ce type de base de données : créer des tables, y insérer des données, et surtout les interroger pour en extraire des informations. Contrairement à un langage comme Python, SQL est un langage déclaratif : on décrit le résultat que l'on souhaite obtenir (par exemple "les élèves normands inscrits en CPGE"), sans avoir à écrire l'algorithme qui permet de le calculer. C'est le système de gestion de base de données (SGBD) qui se charge de trouver la façon la plus efficace de répondre à la requête.

TP

Cet exercice vise à exploiter une base de donnée réelle grâce au langage SQL. 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';

Il s'agit d'une base de donnée nationale sur les élèves dans l'enseignement supérieur en France, pour l'année 2023. Les données sont bien sûr anonymisées. Les attributs des trois tables sont :

Table Communes

Contient les communes de France qui contiennent un établissement.

clé primaire code : le code de la commune (ce n'est pas exactement le code postal).
nom : le nom de la commune
departement : le département de la commune
region : la région de la commune

Table Etablissements

Contient (une partie) des établissements d'enseignement supérieur en France.

clé primaire id : un identifiant d'établissement
type : le type d'établissement
nom : le nom de l'établissement
clé étrangère code_commune : le code de la commune du siège de l'établissement.

Table Eleves

Contient des groupes d'élèves correspondant à une catégorie, ainsi que leur nombre (avec et sans CPGE).

clé étrangère id_etablissement : l'identifiant de l'établissement
sexe : "M" ou "F", l'état civil de(s) élève(s) (malheureusement entièrement binaire en France)
serie_bac : le bac obtenu
departement_bac : si le bac a été passé dans un autre département que celui de l'établissement ou non
diplome : le diplôme préparé
discipline : la discipline étudiée. À noter que seule une partie des disciplines est présente ici (de la Chimie aux Maths globalement)
nb_etudiants_sans_CPGE : nombre d'étudiants dans le groupe, sans compter ceux en double inscription CPGE
nb_etudiants_avec_CPGE : idem mais avec les doubles inscriptions. Cette colonne compte tout de même les étudiants en inscription "classique", elle est donc toujours plus grande que la précédente.

La clé primaire de cette table est (id_etablissement, sexe, serie_bac, departement_bac, diplome, discipline).

Les enregistrement de la table élèves sont des groupes d'un ou plusieurs élèves, partageant le même établissement, sexe, série du bac, département du bac, diplome, et discipline.

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

Dans ce TP, les questions sont en ordre croissant de difficulté. N'hésitez pas à vous aider des slides du cours ou du memento mis à disposition sur le site.

SELECT <attributs> FROM <table> projette (et affiche) les attributs indiqués (ou tous, avec *) dans une table.
LIMIT n ne garde que les n premiers enregistrements du résultat.
WHERE <condition> filtre les enregistrements du résultat selon une condition, par exemple WHERE nom = "Rouen".
Une requête SQL se termine généralement par un point-virgule ;.

Question 1

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

SQL

Loading editor...

SELECT * FROM Communes;

SQL

Loading editor...

SELECT * FROM Etablissements;

SQL

Loading editor...

SELECT * FROM Eleves;

ORDER BY <attribut> trie les résultats selon un (ou plusieurs) attributs, par ordre croissant. On peut ajouter DESC après l'attribut pour trier par ordre décroissant.

Question 2

Quel est le groupe (donc l'enregistrement de la table Eleves) qui contient le plus d'étudiants ? Affichez cet enregistrement en triant les enregistrements puis en ne gardant que le premier.

SQL

Loading editor...

Question 3

Est-ce que Caen est répertoriée dans la table Communes ?

SQL

Loading editor...

2. Jointures

Dans cette partie, les requêtes demanderont l'utilisation de jointures, ainsi que quelques autres mots-clés.

SELECT ... FROM A JOIN B ON <condition> combine les enregistrements de deux tables A et B qui vérifient la condition indiquée, typiquement l'égalité entre une clé étrangère de l'une et la clé primaire de l'autre.
AS permet de renommer un attribut ou une table dans le résultat, ce qui est pratique pour raccourcir ou clarifier une requête portant sur plusieurs tables : SELECT e.nom AS nom_etablissement FROM Etablissements AS e.

Question 4

Quel est l'établissement du plus grand groupe d'étudiants ?

SQL

Loading editor...

Question 5

Quel est le nom des établissements qui se situent à Caen ?

Affichez les groupes d'élèves de ces établissements.

SQL

Loading editor...

3. Agrégats

Dans cette partie, les requêtes demanderont l'utilisation d'agrégats.

GROUP BY <attribut> regroupe les enregistrements qui ont la même valeur pour cet attribut, pour ensuite calculer un agrégat sur chaque groupe plutôt que sur toute la table.
SUM(<attribut>) est une fonction d'agrégat qui calcule la somme des valeurs d'un attribut, pour chaque groupe (ou sur toute la table si GROUP BY n'est pas utilisé).

Question 6

Écrivez une requête pour donner le nombre total d'élèves, avec et sans CPGE, par code établissement.

SQL

Loading editor...

Question 7

Écrivez une requête pour donner le nombre d'élèves en double inscription CPGE par code établissement.

Indice

Il est possible de faire des opérations sur les attributs : par exemple, on peut écrire SELECT (2 * nb_etudiants_avec_CPGE + 3) FROM Eleves;

SQL

Loading editor...

COUNT() compte le nombre d'enregistrements (dans chaque groupe, si combiné à GROUP BY).

Question 8

Écrivez une requête pour donner le nombre de communes (celles présentes dans la table uniquement) par région.

SQL

Loading editor...

4. 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 9

Écrivez une requête pour donner le nombre d'établissements par région.

SQL

Loading editor...

Question 10

Écrivez une requête pour donner le nombre d'élèves par département.

SQL

Loading editor...

Question 11

Écrivez une requête pour donner le nombre d'élèves en double inscription CPGE par établissement (avec le nom de l'établissement).

SQL

Loading editor...

SELECT DISTINCT <attributs> supprime les doublons du résultat : si plusieurs enregistrements ont exactement les mêmes valeurs pour les attributs sélectionnés, un seul est conservé.

Question 12

Écrivez une requête pour afficher les établissements (leur nom) qui ont au moins un élève en double inscription CPGE.

SQL

Loading editor...

<requête 1> UNION <requête 2> combine les résultats de deux requêtes SELECT en une seule liste, en supprimant les doublons. Les deux requêtes doivent sélectionner le même nombre d'attributs, avec des types compatibles.

Question 13

Écrivez une requête pour afficher la liste de tous les noms de lieux présents dans la base (noms de communes, de départements, de régions, et d'établissements), réunis en un seul attribut.

SQL

Loading editor...

Question 14

Écrivez une requête pour afficher, dans chaque région, le nombre d'élèves féminins et masculins. Même question pour chaque catégorie de bac.

SQL

Loading editor...

Question 15

Dans quelle série de bac les étudiants se déplacent-ils le plus après le bac ? (c'est-à-dire, ne font pas leurs études au même endroit que leur bac.)

SQL

Loading editor...

Une sous-requête (ou requête imbriquée) est une requête SELECT utilisée à l'intérieur d'une autre requête, par exemple dans une clause FROM (<sous-requête>) ou WHERE ... = (<sous-requête>).
AVG(<attribut>) est une fonction d'agrégat qui calcule la moyenne des valeurs d'un attribut, pour chaque groupe (ou sur toute la table si GROUP BY n'est pas utilisé).

Question 16

Écrivez une requête pour afficher le nombre moyen d'élèves dans un établissement, par région.

Pour être plus précis, la requête devra calculer, pour chaque établissement, le total des élèves inscrits (avec CPGE). Puis, il faudra faire la moyenne parmi les différents établissements d'une région, et afficher ce total pour chaque région.

SQL

Loading editor...

Question 17

Quelle proportion des élèves de Master prépare un Doctorat ? (en supposant les chiffres stables d'une année sur l'autre)

SQL

Loading editor...

ABS(<valeur>) renvoie la valeur absolue d'un nombre. C'est utile pour mesurer un écart (par exemple entre une valeur et une moyenne) sans se soucier de son signe.

Question 18

Quel est l'établissement avec le nombre d'étudiants le plus proche de la moyenne ?

On parle ici de la moyenne du nombre d'élèves par établissement, sur tous les établissements.

SQL

Loading editor...

MAX(<attribut>) (comme MIN) est une fonction d'agrégat qui donne la valeur maximale (ou minimale) d'un attribut, pour chaque groupe.

Question 19

Écrivez une requête pour afficher la discipline la plus étudiée dans chaque département.

SQL

Loading editor...

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

Code d'accès