Données :
Créer une base de données, le nom ce que vous voulez et interclassement utf8general_ci
Créer la table Livres
Première table :
INSERT INTO Livres (LivreID (A_I), Titre, Auteur, AnneePublication, Genre) VALUES
(1, 'Les Misérables', 'Victor Hugo', 1862, 'Roman historique'),
(2, 'Le Petit Prince', 'Antoine de Saint-Exupéry', 1943, 'Conte philosophique'),
(3, '1984', 'George Orwell', 1949, 'Science-fiction'),
(4, 'Le Seigneur des Anneaux', 'J.R.R. Tolkien', 1954, 'Fantasy'),
(5, 'Harry Potter à l’école des sorciers', 'J.K. Rowling', 1997, 'Fantasy'),
(6, 'Da Vinci Code', 'Dan Brown', 2003, 'Thriller'),
(7, 'L’Étranger', 'Albert Camus', 1942, 'Roman'),
(8, 'Germinal', 'Émile Zola', 1885, 'Roman social'),
(9, 'Le Comte de Monte-Cristo', 'Alexandre Dumas', 1844, 'Roman d’aventure'),
(10, 'Bel-Ami', 'Guy de Maupassant', 1885, 'Roman'),
(11, 'La Nuit des temps', 'René Barjavel', 1968, 'Science-fiction');
Créer la table Emprunts
Seconde table :
INSERT INTO Emprunts (EmpruntID (A_I), LivreID, DateEmprunt, DateRetour (pensez à
cocher null), AdherentID) VALUES
(1, 1, '2023-01-10', '2023-01-24', 101),
(2, 3, '2023-02-01', NULL, 102),
(3, 5, '2023-02-15', '2023-03-01', 103),
(4, 2, '2023-01-12', '2023-01-26', 104),
(5, 4, '2023-02-20', NULL, 105),
(6, 7, '2023-03-05', NULL, 106),
(7, 6, '2023-01-18', '2023-02-01', 107),
(8, 8, '2023-02-25', NULL, 108),
(9, 10, '2023-03-10', NULL, 109),
(10, 9, '2023-01-22', '2023-02-05', 110),
(11, 3, '2023-02-28', NULL, 105),
(12, 2, '2024-02-05', NULL, 106);
Questions
Sélectionnez tous les livres.
SELECT * FROM livres;
Trouvez tous les livres publiés après 2010.
SELECT * FROM livres WHERE AnneePublication > 2010;
Comptez le nombre de livres pour chaque genre.
SELECT Genre, COUNT(*) AS livres FROM livres GROUP BY Genre;
Sélectionnez tous les emprunts qui n'ont pas encore été retournés (où DateRetour est
NULL).
SELECT * FROM emprunts WHERE DateRetour IS NULL;
Sélectionnez les livres qui n'ont jamais été empruntés.
SELECT * FROM livres WHERE LivresID NOT IN (SELECT LivresID FROM emprunts);
Trouvez le nombre total d'emprunts pour chaque livre par titre.
SELECT [Link], COUNT(EmpruntID) AS nombre_emprunts FROM livres LEFT
JOIN emprunts ON LivresID = LivresID GROUP BY [Link];
Sélectionnez le livre le plus emprunté.
SELECT LivreID, [Link], COUNT([Link]) AS nombre_emprunts
FROM Livres LEFT JOIN Emprunts ON LivreID = [Link] GROUP BY LivreID,
[Link] ORDER BY nombre_emprunts DESC LIMIT 1;
Trouvez tous les livres empruntés dans les 30 derniers jours.
SELECT * FROM Livres WHERE LivresID IN ( SELECT LivreID FROM Emprunts
WHERE DateEmprunt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) );
Mettez à jour le genre du livre avec LivreID = 5 en 'Science-Fiction'.
UPDATE Livres SET Genre = 'Science-Fiction' WHERE LivresID = 5;
Sélectionnez le nombre de livres par genre et par année de publication.
SELECT Genre, AnneePublication, COUNT(*) AS nombre_de_livres FROM Livres
GROUP BY Genre, AnneePublication;
Trouvez l'adhérent qui a emprunté le plus de livres.
SELECT AdherentID, COUNT(*) AS nombre_emprunts FROM Emprunts GROUP BY
AdherentID ORDER BY nombre_emprunts DESC LIMIT 1;
Supprimez tous les emprunts où la DateRetour est antérieure à 2020.
DELETE FROM Emprunts WHERE DateRetour < '2020-01-01';
Créez une vue qui montre le nombre d'emprunts par mois pour l'année 2023.
CREATE VIEW Vue_Emprunts_Par_Mois_2023 AS
SELECT MONTH(DateEmprunt) AS Mois, COUNT(*) AS Nombre_Emprunts
FROM Emprunts
WHERE YEAR(DateEmprunt) = 2023
GROUP BY MONTH(DateEmprunt);
Trouvez le genre de livres le moins emprunté.
SELECT [Link], COUNT([Link]) AS nombre_emprunts FROM
Livres LEFT JOIN Emprunts ON LivreID = [Link] GROUP BY [Link]
ORDER BY nombre_emprunts ASC LIMIT 1;
Mettez à jour la table Emprunts pour prolonger de 7 jours la DateRetour pour tous les
emprunts où la DateRetour est aujourd'hui.
UPDATE Emprunts SET DateRetour = DATE_ADD(DateRetour, INTERVAL 7 DAY)
WHERE DateRetour = CURDATE();
Créez une procédure stockée pour insérer un nouvel emprunt (vous devrez adapter les
types et les valeurs par défaut selon votre système de gestion de base de données).
DELIMITER //
CREATE PROCEDURE InsertNewEmprunt (
IN p_LivreID INT,
IN p_DateEmprunt DATE,
IN p_DateRetour DATE,
IN p_AdherentID INT
)
BEGIN
INSERT INTO Emprunts (LivreID, DateEmprunt, DateRetour, AdherentID)
VALUES (p_LivreID, p_DateEmprunt, p_DateRetour, p_AdherentID);
END//
DELIMITER ;
Écrivez une requête pour trouver tous les livres dont le titre commence par 'Le '
Créez un index sur la colonne AnneePublication dans la table Livres pour améliorer les
performances des requêtes filtrant sur cette colonne.
Créez une vue qui montre le nombre d'emprunts par adhérent et par mois pour l'année
2023.
Trouvez tous les genres de livres qui n'ont été empruntés qu'une seule fois.
Listez le titre du livre et le nombre de jours empruntés pour l'emprunt le plus long.
Nouveaux jeux de données :
3 tables : clients, chambres et réservations
INSERT INTO Clients (ClientID, Nom, Prenom, Email) VALUES
(1, 'Dupont', 'Jean', '[Link]@[Link]'),
(2, 'Martin', 'Alice', '[Link]@[Link]'),
(3, 'Leroy', 'Marc', '[Link]@[Link]'),
(4, 'Bertrand', 'Sophie', '[Link]@[Link]'),
(5, 'Chevalier', 'Lucas', '[Link]@[Link]'),
(6, 'Blanchard', 'Émilie', '[Link]@[Link]'),
(7, 'Girard', 'Thomas', '[Link]@[Link]'),
(8, 'Lambert', 'Julie', '[Link]@[Link]');
INSERT INTO Chambres (ChambreID, Numero, Type, PrixParNuit) VALUES
(1, '101', 'Simple', 70.00),
(2, '102', 'Double', 85.00),
(3, '103', 'Suite', 150.00),
(4, '104', 'Simple', 75.00),
(5, '105', 'Double', 90.00),
(6, '201', 'Suite', 160.00),
(7, '202', 'Suite', 165.00),
(8, '203', 'Double', 95.00),
(9, '204', 'Simple', 80.00);
INSERT INTO Reservations (ReservationID, ClientID, ChambreID, DateDebut, DateFin,
MontantTotal) VALUES
(1, 1, 1, '2024-03-01', '2024-03-03', 140.00),
(2, 2, 2, '2024-03-05', '2024-03-08', 255.00),
(3, 3, 3, '2024-03-10', '2024-03-12', 300.00),
(4, 4, 4, '2024-03-15', '2024-03-20', 375.00),
(5, 5, 5, '2024-04-01', '2024-04-05', 360.00),
(6, 6, 6, '2024-04-10', '2024-04-15', 800.00),
(7, 7, 7, '2024-02-20', '2024-02-25', 825.00),
(8, 8, 8, '2024-03-25', '2024-03-30', 475.00),
(9, 1, 9, '2024-03-07', '2024-03-09', 160.00),
(10, 2, 1, '2024-03-20', '2024-03-23', 210.00),
(11, 3, 2, '2024-04-05', '2024-04-08', 255.00),
(12, 4, 3, '2024-02-25', '2024-03-01', 450.00),
(13, 5, 4, '2024-03-01', '2024-03-03', 150.00),
(14, 6, 5, '2024-03-10', '2024-03-12', 180.00),
(15, 7, 6, '2024-03-15', '2024-03-17', 320.00);
Listez toutes les chambres avec leur type et prix par nuit.
SELECT Numero, Type, PrixParNuit
FROM Chambres;
Trouvez le nombre total de réservations pour chaque type de chambre.
SELECT [Link], COUNT([Link]) AS NombreDeReservations
FROM Chambres c
JOIN Reservations r ON [Link] = [Link]
GROUP BY [Link];
Calculez le revenu total généré par les réservations pour chaque type de chambre.
SELECT [Link], SUM([Link]) AS RevenuTotal FROM Chambres c JOIN
Reservations r ON [Link] = [Link] GROUP BY [Link];
Créez une procédure stockée qui permet d'ajouter une nouvelle réservation. La procédure
doit automatiquement calculer le MontantTotal basé sur le nombre de nuits et le prix par nuit
de la chambre.
Créez une vue VueChambresDisponibles qui liste toutes les chambres actuellement non
réservées
CREATE VIEW VueChambresDisponibles AS
SELECT c
FROM Chambres c
LEFT JOIN Reservations r ON [Link] = [Link]
WHERE [Link] IS NULL;
Écrivez un trigger qui met à jour le MontantTotal d'une réservation si les dates de début ou
de fin sont modifiées.
Mettez à jour la vue VueChambresDisponibles pour inclure le type de chambre et le prix par
nuit.
CREATE VIEW VueChambresDisponibles AS SELECT Type, PrixParNuit FROM
Chambres c LEFT JOIN Reservations r ON [Link] = [Link] WHERE
[Link] IS NULL;
Créez une procédure stockée VerifierDisponibilite qui prend en entrée un type de chambre
et des dates de début et de fin, et retourne si une chambre de ce type est disponible pour
ces dates.
