NOM: BOUROUBA MOHAMED ANIS SSI
DEVOIRE ADM-BD:
a. Lister les noms d’hôtels et leur ville.
SELECT nom, ville FROM HOTEL;
Résultat attendu: Une liste de tous les hôtels dans la base de données, avec leur nom et ville.
b. Lister les hôtels sur lesquels porte au moins une réservation.
SELECT DISTINCT h.nom, h.ville
FROM HOTEL h
JOIN RESERVATION r ON h.numhotel = r.numhotel;
Résultat attendu: Une liste des hôtels qui ont été réservés au moins une fois.
c. Quels sont les clients qui ont toujours séjourné au premier étage ?
SELECT c.nomc, c.prenomc
FROM CLIENT c
JOIN RESERVATION r ON c.numc = r.numc
JOIN CHAMBRE ch ON r.numchambre = ch.numchambre AND r.numhotel = ch.numhotel
WHERE ch.etage = 1
GROUP BY c.numc
HAVING COUNT(DISTINCT ch.etage) = 1;
Résultat attendu: Une liste de clients qui ont toujours séjourné au premier étage.
d. Quels sont les hôtels (nom, ville) qui offrent des suites, et pour chaque suite,
à quel prix ?
SELECT h.nom, h.ville, ch.typechambre, ch.prixnuit
FROM HOTEL h
JOIN CHAMBRE ch ON h.numhotel = ch.numhotel
WHERE ch.typechambre = 'suite';
Résultat attendu: Une liste des hôtels avec le prix de leurs suites.
e. Quel est le type de chambre habituellement le plus réservé pour chaque
hôtel d’Alger ?
SELECT h.nom, ch.typechambre, COUNT(r.numchambre) AS nb_reservations
FROM HOTEL h
JOIN RESERVATION r ON h.numhotel = r.numhotel
JOIN CHAMBRE ch ON r.numchambre = ch.numchambre AND r.numhotel = ch.numhotel
WHERE h.ville = 'Alger'
GROUP BY h.nom, ch.typechambre
ORDER BY h.nom, nb_reservations DESC;
Résultat attendu: Une liste de chaque hôtel d'Alger et le type de chambre le plus réservé, avec le
nombre de réservations associé.
f. Quel est le manque à gagner provoqué par les chambres inoccupées à
chaque hôtel ?
SELECT h.nom, SUM(ch.prixnuit) AS manque_a_gagner
FROM HOTEL h
JOIN CHAMBRE ch ON h.numhotel = ch.numhotel
LEFT JOIN RESERVATION r ON ch.numchambre = r.numchambre AND ch.numhotel =
r.numhotel
WHERE r.numchambre IS NULL
GROUP BY h.nom;
Résultat attendu: Le manque à gagner pour chaque hôtel, basé sur les chambres inoccupées.
g. Ajouter 1000 DA à tous les prix des chambres de classement 4 étoiles.
UPDATE CHAMBRE
SET prixnuit = prixnuit + 1000
WHERE numhotel IN (SELECT numhotel FROM HOTEL WHERE etoiles = 4);
Résultat attendu: Le prix de toutes les chambres des hôtels 4 étoiles est augmenté de 1000 DA.
h. Supprimer toutes les chambres de l’hôtel 'Renaissance'. Quels sont les
problèmes rencontrés.
DELETE FROM CHAMBRE
WHERE numhotel = (SELECT numhotel FROM HOTEL WHERE nom = 'Renaissance');
Problèmes rencontrés:
Si des réservations sont effectuées pour ces chambres, la suppression échouera en raison
de la contrainte de clé étrangère dans la table RESERVATION.
i. Calcul des différents chiffres d’affaires enregistrés par les hôtels par année
et par types de chambres.
SELECT h.nom, YEAR(r.date_a) AS annee, ch.typechambre, SUM(ch.prixnuit) AS
chiffre_affaires
FROM RESERVATION r
JOIN CHAMBRE ch ON r.numchambre = ch.numchambre AND r.numhotel = ch.numhotel
JOIN HOTEL h ON r.numhotel = h.numhotel
GROUP BY h.nom, YEAR(r.date_a), ch.typechambre;
Résultat attendu: Le chiffre d'affaires pour chaque hôtel, par année et par type de chambre.
j. Ecrire les requêtes paramétrées permettant de :
- Lister tous les hôtels d’une ville donnée en paramètre.
SELECT nom, ville
FROM HOTEL
WHERE ville = :ville_param;
j. Ecrire les requêtes paramétrées permettant de :
- Tous les clients pour un type de chambre donnée pour un hôtel donné.
SELECT c.nomc, c.prenomc
FROM CLIENT c
JOIN RESERVATION r ON c.numc = r.numc
JOIN CHAMBRE ch ON r.numchambre = ch.numchambre AND r.numhotel = ch.numhotel
WHERE ch.typechambre = :typechambre_param
AND r.numhotel = :numhotel_param;
2/Procédures SQL - Gestion de l'Hôtel
/Ajouter un Hôtel
CREATE PROCEDURE AjouterHotel
@numhotel INT,
@nom VARCHAR(50),
@ville VARCHAR(50),
@etoiles INT
AS
BEGIN
IF EXISTS (SELECT 1 FROM HOTEL WHERE numhotel = @numhotel)
BEGIN
PRINT 'L''hôtel existe déjà.';
RETURN;
END
INSERT INTO HOTEL (numhotel, nom, ville, etoiles)
VALUES (@numhotel, @nom, @ville, @etoiles);
PRINT 'Hôtel ajouté avec succès.';
END;
/Modifier un Hôtel
CREATE PROCEDURE ModifierHotel
@numhotel INT,
@nom VARCHAR(50),
@ville VARCHAR(50),
@etoiles INT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM HOTEL WHERE numhotel = @numhotel)
BEGIN
PRINT 'L''hôtel n''existe pas.';
RETURN;
END
UPDATE HOTEL
SET nom = @nom, ville = @ville, etoiles = @etoiles
WHERE numhotel = @numhotel;
PRINT 'Hôtel modifié avec succès.';
END;
/Supprimer un Hôtel
CREATE PROCEDURE SupprimerHotel
@numhotel INT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM HOTEL WHERE numhotel = @numhotel)
BEGIN
PRINT 'L''hôtel n''existe pas.';
RETURN;
END
DELETE FROM HOTEL WHERE numhotel = @numhotel;
PRINT 'Hôtel supprimé avec succès.';
END;
/Ajouter une Chambre
CREATE PROCEDURE AjouterChambre
@numchambre INT,
@numhotel INT,
@etage INT,
@typechambre VARCHAR(50),
@prixnuit DECIMAL(10, 2)
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM HOTEL WHERE numhotel = @numhotel)
BEGIN
PRINT 'L''hôtel spécifié n''existe pas.';
RETURN;
END
INSERT INTO CHAMBRE (numchambre, numhotel, etage, typechambre, prixnuit)
VALUES (@numchambre, @numhotel, @etage, @typechambre, @prixnuit);
PRINT 'Chambre ajoutée avec succès.';
END;
/Modifier une Chambre
CREATE PROCEDURE ModifierChambre
@numchambre INT,
@numhotel INT,
@etage INT,
@typechambre VARCHAR(50),
@prixnuit DECIMAL(10, 2)
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM CHAMBRE WHERE numchambre = @numchambre AND
numhotel = @numhotel)
BEGIN
PRINT 'La chambre n''existe pas.';
RETURN;
END
UPDATE CHAMBRE
SET etage = @etage, typechambre = @typechambre, prixnuit = @prixnuit
WHERE numchambre = @numchambre AND numhotel = @numhotel;
PRINT 'Chambre modifiée avec succès.';
END;
/Supprimer une Chambre
CREATE PROCEDURE SupprimerChambre
@numchambre INT,
@numhotel INT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM CHAMBRE WHERE numchambre = @numchambre AND
numhotel = @numhotel)
BEGIN
PRINT 'La chambre n''existe pas.';
RETURN;
END
DELETE FROM CHAMBRE WHERE numchambre = @numchambre AND numhotel =
@numhotel;
PRINT 'Chambre supprimée avec succès.';
END;
/Ajouter un Client
CREATE PROCEDURE AjouterClient
@numc INT,
@nomc VARCHAR(50),
@prenomc VARCHAR(50)
AS
BEGIN
IF EXISTS (SELECT 1 FROM CLIENT WHERE numc = @numc)
BEGIN
PRINT 'Le client existe déjà.';
RETURN;
END
INSERT INTO CLIENT (numc, nomc, prenomc)
VALUES (@numc, @nomc, @prenomc);
PRINT 'Client ajouté avec succès.';
END;
/Modifier un Client
CREATE PROCEDURE ModifierClient
@numc INT,
@nomc VARCHAR(50),
@prenomc VARCHAR(50)
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM CLIENT WHERE numc = @numc)
BEGIN
PRINT 'Le client n''existe pas.';
RETURN;
END
UPDATE CLIENT
SET nomc = @nomc, prenomc = @prenomc
WHERE numc = @numc;
PRINT 'Client modifié avec succès.';
END;
/Supprimer un Client
CREATE PROCEDURE SupprimerClient
@numc INT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM CLIENT WHERE numc = @numc)
BEGIN
PRINT 'Le client n''existe pas.';
RETURN;
END
DELETE FROM CLIENT WHERE numc = @numc;
PRINT 'Client supprimé avec succès.';
END;
/Ajouter une Réservation
CREATE PROCEDURE AjouterReservation
@numc INT,
@numhotel INT,
@date_a DATETIME,
@date_d DATETIME,
@numchambre INT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM CLIENT WHERE numc = @numc)
BEGIN
PRINT 'Le client n''existe pas.'; RETURN;
END
IF NOT EXISTS (SELECT 1 FROM CHAMBRE WHERE numchambre = @numchambre AND
numhotel = @numhotel)
BEGIN
PRINT 'La chambre n''existe pas dans cet hôtel.'; RETURN;
END
IF EXISTS (SELECT 1 FROM RESERVATION WHERE numc = @numc AND numhotel =
@numhotel AND date_a = @date_a)
BEGIN
PRINT 'Le client a déjà une réservation à cette date dans cet hôtel.'; RETURN;
END
INSERT INTO RESERVATION (numc, numhotel, date_a, date_d, numchambre)
VALUES (@numc, @numhotel, @date_a, @date_d, @numchambre);
PRINT 'Réservation ajoutée avec succès.'; END;
Réponses aux Questions 4, 5 et 6
/Question 4 : Création des états de sortie nécessaires à une édition de
statistiques
Pour répondre à cette question, nous proposons les états de sortie suivants pour l’édition de
statistiques :
1. Statistiques sur les hôtels :
- Nombre total de chambres par hôtel.
- Moyenne des prix des chambres par hôtel.
- Nombre de réservations par hôtel.
2. Statistiques sur les chambres :
- Répartition des types de chambres (simple, double, suite, etc.).
- Taux d’occupation des chambres (chambres réservées vs chambres disponibles).
3. Statistiques sur les clients :
- Nombre total de clients ayant effectué une réservation.
- Classement des clients par nombre de réservations.
Les états peuvent être générés à l’aide de requêtes SQL ou via un outil de reporting (par
exemple, Microsoft Reporting Services ou Tableau).
/Question 5 : Total des revenus pour chaque hôtel
Pour calculer les revenus pour chaque hôtel, il est nécessaire d’ajouter un champ
`total_revenu_hotel` dans la table `HOTEL`.
Ensuite, nous créons un trigger qui met à jour automatiquement ce champ à chaque
insertion d’une réservation.
Exemple de code :
```sql
ALTER TABLE HOTEL ADD total_revenu_hotel DECIMAL(10, 2) DEFAULT 0;
CREATE TRIGGER TotalRevenu_Hotel_trigger
ON RESERVATION
AFTER INSERT
AS
BEGIN
UPDATE HOTEL
SET total_revenu_hotel = total_revenu_hotel + (
SELECT SUM(ch.prixnuit)
FROM CHAMBRE ch
JOIN INSERTED i ON ch.numchambre = i.numchambre AND ch.numhotel = i.numhotel
)
WHERE HOTEL.numhotel IN (SELECT numhotel FROM INSERTED);
END;
```
/Question 6 : Sauvegarder tous les séjours des clients
Pour sauvegarder les informations sur les séjours des clients dans une nouvelle table
`Hist_Resv`, nous procédons comme suit :
1. Création de la table :
```sql
CREATE TABLE Hist_Resv (
numc INT,
numhotel INT,
date_a DATETIME,
date_d DATETIME,
PRIMARY KEY (numc, numhotel, date_a)
);
```
2. Mise à jour initiale de la table :
```sql
INSERT INTO Hist_Resv (numc, numhotel, date_a, date_d)
SELECT numc, numhotel, date_a, date_d
FROM RESERVATION;
```
3. Création d’un trigger pour insérer automatiquement les nouvelles réservations :
```sql
CREATE TRIGGER Hist_Resv_Trigger
ON RESERVATION
AFTER INSERT
AS
BEGIN
INSERT INTO Hist_Resv (numc, numhotel, date_a, date_d)
SELECT numc, numhotel, date_a, date_d
FROM INSERTED;
END;
```
Ces étapes garantissent que toutes les réservations effectuées par les clients sont
conservées dans un historique complet.
