Séance 5 : Algèbre relationnelle et SQL

Eleve(id_eleve, nom, email, classe)
Auteur(id_auteur, nom)
Livre(id_livre, titre, id_auteur*, genre, editeur, annee_publication)
Emprunt(id_emprunt, id_eleve*, id_livre*, date_emprunt, date_retour)
-- FKs :
--   Livre.id_auteur   → Auteur.id_auteur
--   Emprunt.id_eleve  → Eleve.id_eleve
--   Emprunt.id_livre  → Livre.id_livre

1. Comprendre les requêtes avant le SQL

Avant d’écrire une requête SQL, il est essentiel de comprendre ce qu’elle fait. L’algèbre relationnelle est le langage théorique sur lequel SQL a été bâti. Elle permet d’exprimer les opérations possibles sur une base de données avec des mots simples.

1.1 Exemples de traduction en SQL

Projection : « Je veux afficher seulement les noms et emails des élèves. »

SELECT nom, email
FROM Eleve;

Sélection : « Je veux seulement les élèves de la classe T07. »

SELECT *
FROM Eleve
WHERE classe = 'T07';

Jointure : « Je veux relier les livres à leurs auteurs. »

SELECT titre, nom AS auteur
FROM Livre
JOIN Auteur ON Livre.id_auteur = Auteur.id_auteur;

Regroupement : « Je veux connaître le nombre d’emprunts par classe. »

SELECT classe, COUNT(*) AS nb_emprunts
FROM Eleve
JOIN Emprunt ON Eleve.id_eleve = Emprunt.id_eleve
GROUP BY classe;

Anti-jointure : « Je veux voir les livres qui n’ont jamais été empruntés. »

SELECT l.id_livre, l.titre
FROM Livre l
LEFT JOIN Emprunt e ON e.id_livre = l.id_livre
WHERE e.id_livre IS NULL;

2. Projeter des colonnes (SELECT … FROM)

SELECT nom, email
FROM Eleve;

3. Filtrer des lignes (WHERE)

SELECT *
FROM Eleve
WHERE classe = 'T07';

SELECT titre, editeur, annee_publication
FROM Livre
WHERE genre = 'roman' AND annee_publication >= 2021
ORDER BY annee_publication DESC;

4. Trier, limiter, dédoublonner

SELECT titre, editeur
FROM Livre
ORDER BY titre ASC;

SELECT DISTINCT editeur
FROM Livre
ORDER BY editeur;

SELECT titre, annee_publication
FROM Livre
ORDER BY annee_publication DESC
LIMIT 5;   -- 5 parutions les plus récentes

5. Joindre des tables (JOIN)

But : croiser Élève–Emprunt–Livre (et Auteur) via leurs clés étrangères.

SELECT e.nom AS eleve, e.classe, l.titre, a.nom AS auteur, em.date_emprunt
FROM Emprunt em
JOIN Eleve  e ON e.id_eleve  = em.id_eleve
JOIN Livre  l ON l.id_livre  = em.id_livre
JOIN Auteur a ON a.id_auteur = l.id_auteur
ORDER BY e.nom, em.date_emprunt DESC;

Les emprunts de l’éditeur « Flammarion » :

SELECT e.nom, l.titre, em.date_emprunt
FROM Emprunt em
JOIN Eleve e ON e.id_eleve = em.id_eleve
JOIN Livre l ON l.id_livre = em.id_livre
WHERE l.editeur = 'Flammarion'
ORDER BY em.date_emprunt DESC;

6. Compter, regrouper, filtrer les groupes

-- Nombre total d'élèves
SELECT COUNT(*) AS total_eleves
FROM Eleve;

-- Nombre d'emprunts par classe
SELECT e.classe, COUNT(*) AS nb_emprunts
FROM Emprunt em
JOIN Eleve e ON e.id_eleve = em.id_eleve
GROUP BY e.classe
ORDER BY nb_emprunts DESC;

-- Auteurs les plus empruntés
SELECT a.nom AS auteur, COUNT(*) AS nb_emprunts
FROM Emprunt em
JOIN Livre  l ON l.id_livre  = em.id_livre
JOIN Auteur a ON a.id_auteur = l.id_auteur
GROUP BY a.nom
ORDER BY nb_emprunts DESC;

-- Classes ayant au moins 2 emprunts
SELECT e.classe, COUNT(*) AS nb
FROM Emprunt em
JOIN Eleve e ON e.id_eleve = em.id_eleve
GROUP BY e.classe
HAVING COUNT(*) >= 2;

6 bis. Cas utiles : livres sans emprunt, DISTINCT, dates

SELECT l.id_livre, l.titre
FROM Livre l
LEFT JOIN Emprunt em ON em.id_livre = l.id_livre
WHERE em.id_livre IS NULL
ORDER BY l.titre;

SELECT e.nom, COUNT(DISTINCT em.id_livre) AS nb_livres
FROM Eleve e
LEFT JOIN Emprunt em ON em.id_eleve = e.id_eleve
GROUP BY e.nom
ORDER BY nb_livres DESC, e.nom;

SELECT e.nom, l.titre, em.date_emprunt
FROM Emprunt em
JOIN Eleve e ON e.id_eleve = em.id_eleve
JOIN Livre l ON l.id_livre = em.id_livre
WHERE em.date_emprunt BETWEEN '2024-09-01' AND '2024-09-30'
ORDER BY em.date_emprunt;

7. Modifier les données

INSERT INTO Eleve(id_eleve, nom, email, classe)
VALUES (9, 'Yaël Duclair', 'yael.duclair@mail.com', 'T01');

UPDATE Eleve
SET classe = 'T02'
WHERE id_eleve = 5;

DELETE FROM Emprunt
WHERE id_emprunt = 50;

8. Exercices