TD SQL avec correction : Cas Camping - Exercice Corrigé langage SQL SGBD : Requêtes Sélection et Mise à jour

1- Présentation du modèle du TD

     1.1-  Rappel de l'énoncé

     1.2-  MCD correspondant

     1.3-  Modèle relationnel correspondant

2- Requêtes à réaliser

  

     2.1-  Rédiger les requêtes de sélection suivantes

     2.2-  Rédiger les requêtes de mise à jour suivantes

 

  

 

1- Présentation du modèle du TD 

 

Le modèle de donnée est celui du cas Camping sur lequel vous allez également travailler en développement.

1.1-  Rappel de l’énoncé : 

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 

CAS CAMPING

  

1.3-  Modèle relationnel correspondant :

CAS CAMPING

 

 

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’

-------------------------------------------------------------------------------------------------------

Correction
 

-------------------------------------------------------------------------------------------------------
            

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 <= '07/31/2010';

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 <'07/01/2010';

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 <='07/31/2010';

 

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 <= Zone et Typemp

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 <= '08/31/2010';

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'

);