Exercice SQL requêtes sélection et mise à jour - Cas camping
Vous allez devoir informatiser un camping afin de l’aider dans sa gestion et sa communication avec ses clients.
Un camping est un terrain découpé en emplacement. Ceux-ci peuvent être de plusieurs types (vide, avec mobile home, avec caravane), peuvent disposer d’une place de parking ou non. Lorsqu’un client veut réserver un emplacement, il spécifie les dates de début et de fin de séjour, e type d’emplacement souhaité. Il est défini par un identifiant, un nom, un prénom, une adresse, un numéro de téléphone, une adresse de courrier électronique. Les emplacements sont numérotés. Ils font partie d’une zone et leur situation (à l’ombre ou non) doit être spécifiée ainsi que leur raccordement à l’électricité Le camping propose des activités (payantes ou gratuites) comme un cinéma, un restaurant, une piscine, un court de tennis, des terrains de pétanque, de volley, … Ces activités sont situées dans une zone précise du camping.
Une fois la réservation effectuée, le client a 15 jours pour envoyer son acompte. Au-delà de ce délai, la réservation est annulé et l’emplacement libéré pour la période considérée.
Les réservations se font par Internet ou par téléphone. Elles sont confirmées par l’envoi d’un règlement par chèque correspondant à 20% du prix total du séjour. Le prix des emplacements dépend de la période de l’année, de plus, il est déterminé en nombre de points, c’est-à-dire, un emplacement peut avoir un tarif de 31 points, le point valant 15 euro de Mars à Juin, 17 euro en Juillet et 18 euro en Août.
A l’arrivée d’un client au camping, il est procédé à un relevé du compteur électrique.
A son départ, e client règle le solde du séjour, comprenant le montant de l’électricité consommée (ce qui implique un relevé du compteur au départ du client).
1.2- MCD correspondant
1.3- Modèle relationnel correspondant :
2- Requêtes à réaliser
1. Rédiger l’ensemble des requêtes de création de table suivant les 2 modes :
o En donnant un nom aux contraintes
o Sans donner de nom aux contraintes
o en ajoutant les contraintes en dehors de la déclaration des tables
2.1- Rédiger les requêtes de sélection suivantes :
1. Quels sont les clients (nom, prénom, ville, département) habitant les départements parisiens ?
2. Quels sont les clients (nom, prenom) n’ayant pas de mail
3. Quels sont les clients (nom, prénom) dont le séjour débute au mois de juillet ?
4. Quels sont les clients (nom, prénom) ayant réservé un emplacement à l’ombre ?
5. Quelles sont les réservations n’ayant pas de règlement ?
6. Quels sont les emplacements à l’ombre disposant de l’électricité
7. Quels sont les emplacements vides (pouvant accueillir une caravane ou une tente) ?
8. Quels sont les emplacements se situant à coté de la piscine ?
9. Quels sont les clients ayant quittés le camping au mois de juillet ?
10.Quels sont les emplacements réservés au mois de juillet ?
11.Quels sont les emplacements libres au mois de juillet (dates disponibles) ?
12.Donnez la li e des clients (identité et dates du séjour) ayant consommés de l’électricité (quantité) ?
13.Quels sont les emplacements ne se trouvant à coté d’aucune activité ?
14.Donnez le montant total des règlements, ainsi que leur nombre, regroupé par client et classé par montant.
15.Quel a été le montant total des règlements effectués avant le 1er juillet ?
16.Quel a été le montant total des règlements effectués par des clients partis au plus tard le 31 juillet ?
2.2- Rédiger les requêtes de mise à jour suivantes
1. La base est vierge. Que devez-vous faire pour insérer un enregistrement dans la table RESERVATION ?
2. Insérez un client dans la table client, sachant que vous ne connaissez pas son mail.
3. Les tarifs du mois d’août ont augmenté de 10%
4. Augmentez de 2 points l’ensemble des emplacements n’étant pas affectés à des mobile-home
5. Insérez une réservation pour le client Dupont sur l’emplacement 10 du 10 au 22 juillet.
6. Le client Dupont (réservation ci-dessus) a payé 150 ? d’ares le 5 mai. Mettre à jour les tables correspondantes.
7. Créez une activité no 10 affectée à un terrain de pétanque dans la zone ‘verte’
1.1 Rédiger les requêtes de sélection suivantes :
1. Quels sont les clients (nom, prénom, ville, département) habitant les départements parisiens ?SELECT CLI_Nom, CLI_Pnom, Cli_Vill CLI_CP
FROM CLIENT
WHERE CLI_CP LIKE '75%'
OR Cli_cp LIKE '91%'
OR Cli_CP LIKE '77%'
OR CLI_CP LIKE '78%';
2. Quels sont les clients (nom, prenom) n'ayant pas de mail
SELECT CLI_Nom, CLI_Pnom, Cli_Vill CLI_CP
FROM CLIENT
WHERE Cl_Mai IS NULL ;
3. Quels sont les clients (nom, prénom) dont le séjour débute au mois de juillet ?
SELECT C.CLI_ID, CLI_Nom, RES_datedeb
FROM CLIENT C, RESERVATION R
WHERE R.Cli_id = C.Cli_Id
AND Res_datedeb between '07/01/2006' AND '07/31/2006';
4. Quels sont les clients (nom, prénom) ayant réservé un emplacement à l'ombre ?
SELECT C.CLI_ID, CLI_Nom, E.Emp_id
FROM CLIENT C, RESERVATION R, EMPLACEM ENT E
WHERE C.Cli_id = R.Cli_id
AND E.Emp_id = R.emp_ID
AND Emp_ombre IS true;
5. Quelles sont les réservations n'ayant pas de règlement ?
SELECT Res_id FROM RESERVATION
WHERE RES_id NOT IN
(SELECT Res_id FROM LIER);
6. Quels sont les emplacements à l'ombre disposant de l'électricité
SELECT Emp_id FROM EM PLACEM ENT
WHERE Emp_elec IS TRUE
AND Emp_Ombre IS TRUE;
7. Quels sont les emplacements vides (pouvant accueillir une caravane ou une tente) ?
SELECT E.Emp_id FROM EMPLACEMENT E, TYPEMP T
WHERE E.Typ_id = T.Typ_id
AND Typ_lib LIKE 'Vide';
8. Quels sont les emplacements se situant à coté de la piscine ?
SELECT E.Emp_id FROM EMPLACEMENT E, ZONE Z,ACTIVITE A
WHERE E.Zon_id = Z.Zon_id
AND Z.Zon_id = A.Zon_id
AND Act_lib = 'Piscine';
9. Quels sont les clients ayant quittés le camping au mois de juillet ?
SELECT C.CLI_ID, CLI_Nom, RES_datefin
FROM CLIENT C, RESERVATION R
WHERE R.Cli_id = C.Cli_Id
AND Res_datefin
10.Quels sont les emplacements réservés au mois de juillet ?
SELECT E.Emp_id, Res_datedeb, Res_datefin
FROM EMPLACEMENT E, RESERVATION R
WHERE E.Emp_id = R.Emp_id
AND (Res_datedeb BETWEEN '07/01/2010' AND '07/31/2010'
OR Res_datefin BETWEEN '07/01/2010' AND '07/31/2010');
PB : ceux qui réservent du mois de juin au mois d'aout ne sont pas pris en compte. Pour ceux là, un traitement est obligatoire.
11.Quels sont les emplacements libres au mois de juillet (dates disponibles) ?
On ne peut le faire en SQL => traitement
12.Donnez la li e des clients (identité et dates du séjour) ayant consommés de l'électricité (quantité) ?
SELECT C.CLI_ID, CLI_Nom, RES_datedeb,res_datefin,
(res_comptfin-Res_comptdeb) AS consommati
FROM CLIENT C, RESERVATION R
WHERE C.Cli_id = R.Cli_id
AND Res_comptfin IS NOT NULL ;
13.Quels sont les emplacements ne se trouvant à coté d'aucune activité ?
SELECT EM P_id FROM EMPLACEMENT E, ZONE Z
WHERE Z.Zon_id = E.Zon_id
AND Z.Zon_id NOT IN
(SELECT Zon_id FROM ACTIVITE);
14.Donnez le montant total des règlements, ainsi que leur nombre, regroupé par client, classé par montant.
SELECT C.Cli_id, Cli_nom, SUM (Reg_Mnt), Count(*)
FROM REGLEMENT R, LIER L, RESERVATION RS, CLIENT C
WHERE R.Reg_id = L.Reg_id
AND L.Res_id = RS.Res_id
AND RS.Cli_id = C.Cli_id
GROUP BY C.Cli d, Cli_nom
Order By 3;
15.Quel a été le montant total des règlements effectués avant le 1er juillet ?
SELECT SUM (Reg_Mnt), Count(*)
FROM REGLEMENT
WHERE Reg_date
16.Quel a été le montant total des règlements effectués par des clients partis au plus tard le 31 juillet ?
SELECT SUM (Reg_Mnt), Count(*)
FROM REGLEMENT R, LIER L, RESERVATION RS
WHERE R.Reg_id = L.Reg_id
AND L.Res_id = RS.Res_id
AND res_datefin
1.2 Rédiger les requêtes de mise à jour suivantes
1. La base est vierge. Que devez-vous faire pour insérer un enregistrement dans la table RESERVATION ?
pb intégrité référentielle Il faut créer les données liées des autres tables avant :
Client
Emplacement
2. Insérez un client dans la table client, sachant que vous ne connaissez pas son mail.
INSERT INTO CLIENT
VALUES (1,'Dupont', Jean', '6 route de Paris','75000' 'Paris',
'0123456789'null
Ou alors :
INSERT INTO CLIENT (Cli d, Cli_nom, Cli_Pnom, Cli_Adresse, Cli_Cp,Cli_Vill Cli_Tel)
VALUES (1,'Dupont', Jean', '6 route de Paris','75000' 'Paris','0123456789');
3. Les tarifs du mois d'août ont augmenté de 10%
UPDATE TARIF
SET Tar_Valeur = Tar_Valeur*1.1
WHERE Tar_datedeb >='08/01/2010'
AND Tar_datefin
4. Augmentez de 2 points l'ensemble des emplacements n'étant pas affectés à des mobile-home
UPDATE EMPLACEMENT
SET Emp_point = Emp_point + 2.1
WHERE Typ_id =
(SELECT Typ_id FROM TYPEM P WHERE Typ_Lib = 'Mobie-Home');
5. Insérez une réservation pour le client Dupont sur l'emplacement 10 du 10 au 22 juillet.
INSERT INTO RESERVATION (
SELECT 20, 10, Cli d, '07/10/2010' '07/22/2010', null,null
FROM CLIENT
WHERE Cl_nom = 'Dupont'
);
6. Le client Dupont (réservation ci-dessus) a payé 150 ? d'ares le 5 mai. Mettre à jour les tables correspondantes.
insert into reglement VALUES (20,'05/20/2010','150');
insert into LIER VALUES (20,20);
7. Créez une activité no 10 affectée à un terrain de pétanque dans la zone 'verte'
insert into ACTIVITE (
SELECT 10, Zon_ID, 'terrain de petanque' FROM ZONE
WHERE Zon_lb = 'Verte'
);