Exercice Globale sur SQL - Gestion d'une Bibliotheque
1. Creer la base de donnees :
-- Creation de la base de donnees
CREATE DATABASE Bibliotheque;
USE Bibliotheque;
2. Creer les tables necessaires :
-- Table Auteurs
CREATE TABLE Auteurs (
id_auteur INT AUTO_INCREMENT PRIMARY KEY,
nom TEXT NOT NULL,
prenom TEXT,
nationalite TEXT
);
-- Table Livres
CREATE TABLE Livres (
id_livre INT AUTO_INCREMENT PRIMARY KEY,
titre TEXT NOT NULL,
annee_publication INT,
genre TEXT,
id_auteur INT,
FOREIGN KEY (id_auteur) REFERENCES Auteurs(id_auteur)
);
-- Table Membres
CREATE TABLE Membres (
id_membre INT AUTO_INCREMENT PRIMARY KEY,
nom TEXT NOT NULL,
prenom TEXT,
date_inscription DATE
);
-- Table Emprunts
CREATE TABLE Emprunts (
id_emprunt INT AUTO_INCREMENT PRIMARY KEY,
id_livre INT,
id_membre INT,
date_emprunt DATE,
date_retour DATE,
FOREIGN KEY (id_livre) REFERENCES Livres(id_livre),
FOREIGN KEY (id_membre) REFERENCES Membres(id_membre)
);
3. Modifier une table :
-- Ajouter une colonne email a la table Membres
ALTER TABLE Membres ADD email TEXT;
4. Inserer des donnees dans les tables :
-- Insertion dans Auteurs
INSERT INTO Auteurs (nom, prenom, nationalite) VALUES
('Hugo', 'Victor', 'Francaise'),
('Austen', 'Jane', 'Anglaise');
-- Insertion dans Livres
INSERT INTO Livres (titre, annee_publication, genre, id_auteur) VALUES
('Les Miserables', 1862, 'Roman', 1),
('Pride and Prejudice', 1813, 'Roman', 2);
-- Insertion dans Membres
INSERT INTO Membres (nom, prenom, date_inscription, email) VALUES
('Dupont', 'Jean', '2024-01-15', '[Link]@[Link]'),
('Martin', 'Sophie', '2024-02-20', '[Link]@[Link]');
-- Insertion dans Emprunts
INSERT INTO Emprunts (id_livre, id_membre, date_emprunt, date_retour) VALUES
(1, 1, '2024-03-01', '2024-03-15'),
(2, 2, '2024-03-05', '2024-03-20');
5. Utiliser les requetes SELECT avec des JOIN :
-- Liste des livres empruntes avec les informations des livres, auteurs et membres
SELECT [Link], L.annee_publication, [Link],
[Link] AS auteur_nom, [Link] AS auteur_prenom,
[Link] AS membre_nom, [Link] AS membre_prenom, [Link],
E.date_emprunt, E.date_retour
FROM Emprunts E
JOIN Livres L ON E.id_livre = L.id_livre
JOIN Auteurs A ON L.id_auteur = A.id_auteur
JOIN Membres M ON E.id_membre = M.id_membre;
6. Mettre a jour les informations d'un membre :
-- Mettre a jour l'email de Jean Dupont
UPDATE Membres
SET email = '[Link]@[Link]'
WHERE nom = 'Dupont' AND prenom = 'Jean';
7. Supprimer un emprunt specifique :
-- Supprimer l'emprunt de Sophie Martin
DELETE FROM Emprunts
WHERE id_membre = (SELECT id_membre FROM Membres WHERE nom = 'Martin' AND prenom
= 'Sophie')
AND id_livre = (SELECT id_livre FROM Livres WHERE titre = 'Pride and Prejudice');
