Correction – Exercices Intégrés SQL &
Algèbre Relationnelle
Correction – Exercice 1 : Gestion des Étudiants et Cours
1. 1. Création des tables :
CREATE TABLE Etudiant (
id INT PRIMARY KEY,
nom VARCHAR(100),
age INT CHECK (age >= 18),
email VARCHAR(100)
);
CREATE TABLE Cours (
code VARCHAR(10) PRIMARY KEY,
titre VARCHAR(100),
credits INT
);
CREATE TABLE Inscription (
id_etudiant INT,
code_cours VARCHAR(10),
date_inscription DATE,
FOREIGN KEY (id_etudiant) REFERENCES Etudiant(id),
FOREIGN KEY (code_cours) REFERENCES Cours(code)
);
2. 2. L’âge est déjà contrôlé par : CHECK (age >= 18)
3. 3. Création de l’index :
CREATE INDEX idx_date_inscription ON Inscription(date_inscription);
4. 4. Algèbre relationnelle :
π_nom (Etudiant ⋈ Inscription ⋈ σ_credits=3(Cours))
SQL :
SELECT [Link]
FROM Etudiant e
JOIN Inscription i ON [Link] = i.id_etudiant
JOIN Cours c ON [Link] = i.code_cours
WHERE [Link] = 3;
5. 5. Function – âge max :
CREATE FUNCTION age_max_etudiant()
RETURNS INT
AS
BEGIN
DECLARE @max_age INT;
SELECT @max_age = MAX(age) FROM Etudiant;
RETURN @max_age;
END;
6. 6. Procedure – ajouter un étudiant :
CREATE PROCEDURE ajouter_etudiant (
@id INT, @nom VARCHAR(100), @age INT, @email VARCHAR(100)
)
AS
BEGIN
INSERT INTO Etudiant VALUES (@id, @nom, @age, @email);
END;
7. 7. Trigger – max 5 cours :
CREATE TRIGGER max_cinq_cours
ON Inscription
AFTER INSERT
AS
BEGIN
IF EXISTS (
SELECT id_etudiant
FROM Inscription
GROUP BY id_etudiant
HAVING COUNT(*) > 5
)
BEGIN
RAISERROR('Un étudiant ne peut pas être inscrit à plus de 5 cours.', 16, 1);
ROLLBACK TRANSACTION;
END
END;
Correction – Exercice 2 : Gestion d'une Bibliothèque
8. 1. Création des tables :
CREATE TABLE Livre (
isbn VARCHAR(13) PRIMARY KEY,
titre VARCHAR(200),
genre VARCHAR(50),
disponible INT CHECK (disponible >= 0)
);
CREATE TABLE Abonne (
aid INT PRIMARY KEY,
nom VARCHAR(100),
abonnement DATE
);
CREATE TABLE Emprunt (
isbn VARCHAR(13),
aid INT,
date_emprunt DATE,
date_retour DATE,
PRIMARY KEY (isbn, aid),
FOREIGN KEY (isbn) REFERENCES Livre(isbn),
FOREIGN KEY (aid) REFERENCES Abonne(aid)
);
9. 2. Contrainte déjà dans CHECK (disponible >= 0)
10. 3. Index sur genre :
CREATE INDEX idx_genre ON Livre(genre);
11. 4. Algèbre relationnelle :
π_nom (Abonne) ÷ π_isbn (σ_genre='Science'(Livre))
SQL :
-- Trouver les abonnés qui ont emprunté tous les livres de genre 'Science'
WITH LivresScience AS (
SELECT isbn FROM Livre WHERE genre = 'Science'
),
NbScience AS (
SELECT COUNT(*) AS total FROM LivresScience
),
AbonneCouverture AS (
SELECT [Link], COUNT(DISTINCT [Link]) AS nb
FROM Emprunt e
WHERE [Link] IN (SELECT isbn FROM LivresScience)
GROUP BY [Link]
)
SELECT [Link]
FROM AbonneCouverture a
JOIN NbScience n ON [Link] = [Link];
12. 5. Function – nombre d’emprunts :
CREATE FUNCTION nb_emprunts_abonne(@aid INT)
RETURNS INT
AS
BEGIN
DECLARE @nb INT;
SELECT @nb = COUNT(*) FROM Emprunt WHERE aid = @aid;
RETURN @nb;
END;
13. 6. Procedure – retour de livre :
CREATE PROCEDURE retourner_livre (@isbn VARCHAR(13), @aid INT)
AS
BEGIN
UPDATE Emprunt
SET date_retour = GETDATE()
WHERE isbn = @isbn AND aid = @aid;
UPDATE Livre
SET disponible = disponible + 1
WHERE isbn = @isbn;
END;
14. 7. Trigger – max 3 livres non rendus :
CREATE TRIGGER max_emprunts
ON Emprunt
AFTER INSERT
AS
BEGIN
IF EXISTS (
SELECT aid
FROM Emprunt
WHERE date_retour IS NULL
GROUP BY aid
HAVING COUNT(*) > 3
)
BEGIN
RAISERROR('Un abonné ne peut avoir plus de 3 livres non retournés.', 16, 1);
ROLLBACK TRANSACTION;
END
END;