Initialisation du schéma relatoinnel

Pour illustrer les opérations complexes de croisement de données, nous définissons un modèle constitué de deux entités principales : apprenant et evaluation.

CREATE TABLE apprenant (
    apprenant_id INT AUTO_INCREMENT PRIMARY KEY,
    nom_complet VARCHAR(50) NOT NULL,
    genre CHAR(1),
    annee_naissance YEAR,
    filiere VARCHAR(30),
    ville_residence VARCHAR(50)
);

CREATE TABLE evaluation (
    evaluation_id INT AUTO_INCREMENT PRIMARY KEY,
    apprenant_id INT NOT NULL,
    matiere VARCHAR(30),
    note INT
);

L'alimentation de ces tables s'effectue via des instructions d'insertion standardisées :

-- Insertion des apprenants
INSERT INTO apprenant (nom_complet, genre, annee_naissance, filiere, ville_residence) VALUES 
('Martin Luc', 'M', 1995, 'Informatique', 'Paris'),
('Bernard Sophie', 'F', 1996, 'Lettres', 'Lyon'),
('Dubois Emma', 'F', 1998, 'Lettres', 'Marseille'),
('Thomas Pierre', 'M', 1994, 'Langues', 'Bordeaux'),
('Robert Julie', 'F', 1997, 'Langues', 'Lille'),
('Richard Paul', 'M', 1993, 'Informatique', 'Lyon');

-- Insertion des résultats d'évaluations
INSERT INTO evaluation (apprenant_id, matiere, note) VALUES 
(1, 'Algorithmique', 95), (1, 'Anglais', 82),
(2, 'Algorithmique', 68), (2, 'Litterature', 90),
(3, 'Litterature', 88),
(4, 'Algorithmique', 74), (4, 'Anglais', 91),
(5, 'Anglais', 85),
(6, 'Algorithmique', 92), (6, 'Anglais', 78);

Sélections simples et filtrages conditionnels

La récupération exhaustive des enregistrements s'obtient par une projection totale :

SELECT * FROM apprenant;

Pour isoler un intervalle d'identifiants spécifique, l'utilisation d'opérateurs de comparaison bornés est recommandée :

SELECT * FROM apprenant 
WHERE apprenant_id >= 2 AND apprenant_id <= 4;

La projection de colonnes ciblées permet d'optimiser le volume de données transitant sur le réseau :

SELECT apprenant_id, nom_complet, filiere FROM apprenant;

Le filtrage par valeurs discrètes gagne en lisibilité grâce à l'opérateur IN, évitant ainsi la répétition de clauses OR ou l'usage coûteux d'unions inutables :

SELECT * FROM apprenant 
WHERE filiere IN ('Informatique', 'Langues');

Le calcul dynamique de l'âge permet d'appliquer des filtres temporels précis basés sur l'année courante :

SELECT * FROM apprenant 
WHERE (YEAR(CURDATE()) - annee_naissance) BETWEEN 25 AND 30;

Fonctions d'agrégation et regroupement

L'analyse statistique des données nécessite l'usage de la clause GROUP BY. Voici le calcul de la répartition des effectifs par filière :

SELECT filiere, COUNT(apprenant_id) AS effectif 
FROM apprenant 
GROUP BY filiere;

L'extraction des performances maximales par discipline s'effectue de manière similaire :

SELECT matiere, MAX(note) AS note_maximale 
FROM evaluation 
GROUP BY matiere;

De même, l'évaluation du niveau moyen par matière utilise la fonction AVG :

SELECT matiere, AVG(note) AS moyenne_generale 
FROM evaluation 
GROUP BY matiere;

Opérations de jointure et intégrité référentielle

Avant d'exécuter des jointures, il est impératif de garantir l'intégrité des données via une contrainte de clé étrangère :

ALTER TABLE evaluation 
ADD CONSTRAINT fk_apprenant_eval 
FOREIGN KEY (apprenant_id) REFERENCES apprenant(apprenant_id);

Une fois la relation établie, l'extraction des résultats d'un individu précis devient triviale grâce à une jointure interne :

SELECT e.matiere, e.note 
FROM evaluation e 
JOIN apprenant a ON e.apprenant_id = a.apprenant_id 
WHERE a.nom_complet = 'Thomas Pierre';

La consolidation complète des profils et de leurs notes respectives s'opère par une projection croisée :

SELECT a.*, e.* 
FROM apprenant a 
INNER JOIN evaluation e ON a.apprenant_id = e.apprenant_id;

Le calcul du score cumulé pour chaque apprenant combine jointure et agrégation :

SELECT a.nom_complet, SUM(e.note) AS total_notes 
FROM apprenant a 
JOIN evaluation e ON a.apprenant_id = e.apprenant_id 
GROUP BY a.apprenant_id, a.nom_complet;

Le filtrage conditionnel au sein d'une jointure permet d'identifier des cas spécifiques, tels que les apprenants ayant obtenu une note inférieure à 90 en Algorithmique :

SELECT a.nom_complet, e.matiere, e.note 
FROM apprenant a 
JOIN evaluation e ON a.apprenant_id = e.apprenant_id 
WHERE e.matiere = 'Algorithmique' AND e.note < 90;

Les opérateurs de correspondance de chaînes (LIKE) s'intègrent parfaitement aux jointures pour extraire les résultats des apprenants dont le nom commence par des préfixes donnés :

SELECT a.nom_complet, a.filiere, e.matiere, e.note 
FROM apprenant a 
JOIN evaluation e ON a.apprenant_id = e.apprenant_id 
WHERE a.nom_complet LIKE 'Martin%' OR a.nom_complet LIKE 'Bernard%';

La recherche géographique appliquée aux données jointes permet d'isoler les résultats des apprenants résidant dans une zone déterminée, tout en calculant leur âge à la volée :

SELECT a.nom_complet, (YEAR(CURDATE()) - a.annee_naissance) AS age, a.filiere, e.matiere, e.note 
FROM apprenant a 
JOIN evaluation e ON a.apprenant_id = e.apprenant_id 
WHERE a.ville_residence LIKE '%Lyon%';

Requêtes avancées : Sous-ensembles, Unions et Tri

Pour identifeir les aprenants ayant passé simultanément deux matières spécifiques, l'approche par agrégation avec filtrage HAVING s'avère beaucoup plus performante et lisible que les sous-requêtes imbriquées corrélées :

SELECT a.apprenant_id, a.nom_complet 
FROM apprenant a
JOIN evaluation e ON a.apprenant_id = e.apprenant_id
WHERE e.matiere IN ('Algorithmique', 'Anglais')
GROUP BY a.apprenant_id, a.nom_complet
HAVING COUNT(DISTINCT e.matiere) = 2;

Le classement des performances sur une épreuve donnée nécessite l'application d'un tri descendant :

SELECT apprenant_id, note 
FROM evaluation 
WHERE matiere = 'Algorithmique' 
ORDER BY note DESC;

Enfin, l'opérateur ensembliste UNION permet de fusionner des listes d'identifiants provenant de tables distinctes, en éliminant automatiquement les doublons :

SELECT apprenant_id FROM apprenant 
UNION 
SELECT apprenant_id FROM evaluation;