DEVOIRE ADM-BD:
1/Les Req SQL:
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/ajout, modification, suppression:
