Exercice SQL corrigé requêtes sélection et mise à jour – Cas camping

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');

Exercice SQL

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. 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 :

Exercice SQL

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'
);