Exercice création des requêtes SQL

Soit la base de donnée suivante :

10. Soulignez les clés primaires, rajoutez un # derrière les clés étrangères.
11. Donnez la li e (Nom) des restaurants de plus de 2 étoiles par ordre alphabétique.
12. Nom et adresse des restaurants qui ne proposent pas le menu 10.
13. Donnez la li e (Nom, Adresse) des restaurants qui ne vendent pas de boissons alcoolisées.
14. Nombre de menus par restaurant par ordre croissant du nombre de menu.
15. Nom du restaurant, nom du chef, nombre d‘étoiles, prix mini, moyen et maxi d‘un menu dans chaque restaurant.
16. Nom du restaurant qui propose le menu le plus cher.
17. Liste des boissons en 'demi-bouteille proposées par le restaurant 1.
18. Nom des restaurants qui proposent ”foie gras poêlé‘ en entrée.
19. Donnez la li e des restaurants (nom, adresse, téléphone) qui proposent un plat à base de ”canard‘.
20. Donnez la li e (idem) des restaurants ayant le même nombre d'étoiles que le restaurant dont le chef s'appelle 'Dugros'

Soit la base de donnée suivante :

10. Soulignez les clés primaires, rajoutez un # derrière les clés étrangères. (4 pts)

11. Donnez la li e des restaurants de plus de 2 étoiles par ordre alphabétique. (2 pts)

SELECT Nomresto
FROM RESTO
WHERE NbEtoile > 2
ORDER BY Nomresto ;

12. Nom et adresse des restaurants qui ne proposent pas le menu 10. (2 pts)

SELECT Nomresto, Adresse
FROM RESTO
WHERE Idresto NOT IN
(SELECT Idresto
FROM MENURESTO
WHERE Idmenu=10) ;

13. Donnez la li e des restaurants qui ne vendent pas de boissons alcoolisées. (2 pts)

SELECT Nomresto, Adresse
FROM RESTO
WHERE Idresto NOT IN
(SELECT Idresto
FROM BOISSON B, BOISSONSERVIE BS
WHERE B.Idboisson=BS.Idboisson
AND DegreeAlcool > 0) ;

14. Nombre de menus par restaurant par ordre croissant du nombre de menu. (2 pts)

SELECT Nomresto, COUNT(Idmenu) as Nombre
FROM RESTO R, MENURESTO M
WHERE R.Idresto = M .Idresto
GROUP BY Nomresto
ORDER BY Nombre ;

15. Nom du restaurant, nom du chef, nombre d'étoiles, prix mini, moyen et maxi d‘un menu dans chaque restaurant. (2 pts)

SELECT Nomresto, Nomchef, NbEtoil MIN(prix), AVG(PRIX), MAX(PRIX)
FROM RESTO R, MENURESTO M
WHERE R.Idresto=M .Idresto
GROUP BY Nomresto, Nomchef, NBEtoil

16. Nom du restaurant qui propose le menu le plus cher. (2 pts)

SELECT Nomresto
FROM RESTO R, M ENURESTO M
WHERE R.Idresto = M .Idresto
AND Prix =
(SELECT MAX(Prix) FROM MENURESTO);

17. Liste des boissons en 'demi-bouteille proposées par le restaurant 1. (2 pts)

SELECT Nomboisson
FROM CONTENANT C, BOISSON B, BOISSONSERVIE BS
WHERE C.Idcontenant=BS.Idcontenant
AND BS.Idboisson=B.Idboisson
AND Idresto=1
AND Nomcontenant="demi-bouteille" ;

18. Nom des restaurants qui proposent ”foie gras poêlé‘ en entrée. (2 pts)

SELECT DISTINCT Nomresto
FROM RESTO R, MENURESTO M R, MENU M
WHERE R.Idresto = M R.Idresto
AND M R.Idmenu=M .Idmenu
AND Entree="foie gras poëlé";

19. Donnez la li e des restaurants (nom, adresse, téléphone) qui proposent un plat à base de ”canard‘. (2 pts)

SELECT Nomresto, Adresse, Telephone
FROM RESTO R, MENURESTO M R, MENU M
WHERE R.Idresto = M R.Idresto
AND M R.Idmenu = M .Idmenu
AND Plat LIKE "%canard%" ;

20. Donnez la liste des restaurants ayant le même nombre d'étoiles que le restaurant dont le chef s'appelle 'Dugros'(2 pts)

SELECT RR.Nomresto, RR.Adresse, RR.Telephone
FROM RESTO RM , RESTO RR
WHERE RM .IDresto = RR.Idresto
AND RM .NbEtoile = RR.NbEtoile
AND RM .Nomchef = "Dugros"
AND RR.Nomchef "Dugros";

Autre solution :

SELECT Nomresto, Adresse, Telephone
FROM RESTO
WHERE NbEtoile =
(SELECT NbEtoile FROM RESTO WHERE Nomchef = "Dugros")
AND Nomchef "Dugros";

Exercice Langage SQL : Les Fonction (Part 1)

Objet: Travailler avec les Fonction (Partie 1)

Travail à Faire:

Créer le Code pour la Fonction SQL permettant d'extraire le suffixe d'une chaine de charactères.
Créer la Fonction d'ajout d'une durée (Année, Mois, Jour, Heure, Minute, Seconde) à une date.
Créer une fonction qui permet de retrouver la position du premier chiffre d'une chaine.

--1) Créer le Code pour la Fonction SQL permettant d'extraire le suffixe d'une chaine de charactères.ALTER FUNCTION dbo.Fx_RenvoieSuffixe (@CHAINEDONNEE AS VARCHAR(500), @SEPARATEUR AS VARCHAR(50))RETURNS VARCHAR(500)BEGIN-- Déclare les variables locales DECLARE @Result VARCHAR(500)DECLARE @IndexSeparateur INT-- Assigne les chaines d'origine sans les blancs possiblesSET @Result = RTRIM(LTRIM(@CHAINEDONNEE))SET @SEPARATEUR = RTRIM(LTRIM(@SEPARATEUR))-- Recherche si le séparateur existe dans la chaine donnéeSET @IndexSeparateur = CHARINDEX (@SEPARATEUR, @Result)IF @IndexSeparateur > 0 -- Si le séparateur existe je fourni tout ce qui est après cette chaine SELECT @Result = SUBSTRING(@Result, CHARINDEX (@SEPARATEUR, @Result)+LEN(@SEPARATEUR), LEN(@Result)- CHARINDEX(\ , @Result))ELSE SELECT @Result = @Result-- Renvoi le résultatRETURN @ResultEND-- 2) Créer la Fonction d"'"ajout d"'"une durée (Année, Mois, Jour, Heure, Minute, Seconde) à une date.SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOCREATE FUNCTION [dbo].[AddDateTimeDuree] (@Dt Datetime, @an int=0, @mois int=0, @jour int=0,@heure int=0, @minute int=0, @seconde int=0) RETURNS DateTime AS BEGIN DECLARE @Ret Datetime IF @Dt IS NULL SET @Ret = NULL ELSE SET @Ret = dateadd(second,isnull(@seconde,0),dateadd(minute,isnull(@minute,0),dateadd(hour,isnull(@heure,0),dateadd(day, isnull(@jour,0), dateadd(month, isnull(@mois,0), dateadd(year, isnull(@an,0), @Dt)))))) RETURN @RetENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO-- 3) Créer une fonction qui permet de retrouver la position du premier chiffre d"une chaine. SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC] (@NOMDIV nvarchar(30))RETURNS smallintASBEGIN DECLARE @nom nvarchar(30) DECLARE @i smallint DECLARE @j smallint SET @i = 1 SET @j = 0 WHILE @i len(@nomdiv) BEGIN DECLARE @car nvarchar(1) SET @car = (SUBSTRING(@NOMDIV, @i, @i) ) IF @car IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') BEGIN SET @j = @i BREAK END ELSE SET @i = @i + 1 ENDRETURN @jEND-- Ou Bien :CREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC_V2] (@NOMDIV nvarchar(30))RETURNS smallintASBEGIN RETURN PATINDEX ('%[0123456789]%', @NOMDIV)END

Exercice Langage SQL : Gestion Bibliothèque (RULE/INDEX/VACCUM)

Exercice sur les RULE/INDEX/VACCUM

Objectif : Pouvoir réaliser des Requêtes en SQL.

Enoncé de l'Exercice:

On considère le schéma relationnel suivant qui modélise une application sur la gestion d’une Bibliothèque

Créer les requêtes suivantes:

1- Ajoutez un champ rendu_theorique à la table livres qui correspondra à la data maximum à laquelle le livre devra être rendu. Ensuite ajoutez une règle qui calculera automatiquement cette date en se servant de la date_emprunt t en y ajoutant 15 jours.

2- Effacez cette règle.

3- Créer un index sur la table emprunteurs

4- Effacez-le

5- Libérez de l'espace occupés par les enregistrements éffacés ou obsolètes.

123456789101112131415161718192021222324

-- 1- Ajoutez un champs rendu_theorique à la table livres qui correspondra à la data maximum à laquelle le livre devra être rendu.-- Ensuite ajoutez une règle qui calculera automatiquement cette date en se servant de la date_emprunt t en y ajoutant 15 jours.CREATE RULE calcul_rendu_theoriqueAS ON INSERTTO emprunts.datepretDO (UPDATE emprunts SET rendu_theorique = datepret + 15);CREATE-- 2- Effacez cette règle.DROP RULE calcul_rendu_theorique;DROP-- 3- Créer un index sur la table emprunteursCREATE INDEX index_emprunteursON emprunteurs(idemprunteur, nom);CREATE-- 4- Effacez-leDROP INDEX index_emprunteurs;DROP

Exercice récapitulatif en Langage SQL (Suite...)

Objectif :

Créer des requêtes en SQL

Travail à faire:

ETUDIANT (Numetu, Nometu, Dtnaiss, Cdsexe)

SEXE (Cdsexe, Lbsexe)

ENSEIGNANT (Numens, Nomens, Grade, Ancien)

MATIERE (Numat, Nomat, Coeff, Numens)

NOTES (Numetu, Numat, Note)

À partir de la Base de données ETUDIANTS ci dessus, écrire les requêtes SQL permettant de répondre aux questions suivantes.

10. Afficher le nom, le numéro et le sexe des étudiants qui ont une note en informatique supérieure à leur note de Mathématiques.
11. Afficher le nom des couples, nom du garçon nom de la fille, dont la différence d'âge ne dépasse pas 6 mois (180 jours).
12. Pour les étudiants n'ayant pas de note dans une matière, afficher le numéro le nom de l'étudiant et le nom de la matière concernée.
13. Afficher, les matières pour lesquelles la moyenne des notes est inférieure à 10. Afficher le nom de l'enseignant correspondant.
14. Afficher, pour chaque matière, qu'elle est la meilleure note et quel est l'étudiant qui l'a obtenue.
15. Afficher, pour chaque sexe (Homme, Femme) la moyenne des notes dans la matière enseignée par Julie Boyer.

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051

Requête 10:SELECT Nometu, E.Numetu, CdsexeFROM ETUDIANT E, NOTES N, MATIERE MWHERE E.Numetu=N.NumetuAND N.Numat=M.NumatAND Nomat='Informatique'AND Note > ALL (SELECT Note FROM NOTES N, MATIERE M WHERE N.Numat=M.Numat AND Nomat='Mathématiques' AND N.Numetu=E.Numetu);Requête 11:SELECT E1.Nometu,E2.NometuFROM ETUDIANT E1, ETUDIANT E2WHERE E1.CdSexe='H'AND E2.CdSexe='F'AND DATEDIFF(E1.Dtnaiss,E2.Dtnaiss) BETWEEN -180 AND 180;Requête 12:SELECT E.Numetu, Nometu, NomatFROM ETUDIANT ELEFT OUTER JOIN NOTES NON E.Numetu=N.NumetuLEFT OUTER JOIN Matiere MON N.Numat=M.Numat OR N.Numat IS NULLWHERE Note IS NULL;Requête 13:SELECT Nomens, Nomat, Avg(note)FROM ENSEIGNANT E, MATIERE M, NOTES NWHERE E.Numens=M.NumensAND M.Numat=N.NumatGROUP BY Nomens, NomatHAVING Avg(note) 10;Requête 14:SELECT Nomat, Nometu, NoteFROM MATIERE M, NOTES N, ETUDIANT EWHERE N.Numat=M.NumatAND E.Numetu=N.NumetuAND Note=(SELECT Max(Note) FROM NOTES E1 WHERE E1.Numat=M.Numat);Requête 15:SELECT Nomat, Nometu, NoteFROM MATIERE M, NOTES N, ETUDIANT EWHERE N.Numat=M.NumatAND E.Numetu=N.NumetuAND Note=(SELECT Max(Note) FROM NOTES E1 WHERE E1.Numat=M.Numat);

Exercice Langage SQL : Gestion Bibliothèque (Utilisateur)

Exercice sur la création/supression d'utilisateurs

Objectif : Pouvoir réaliser des Requêtes en SQL.

Enoncé de l'Exercice:

On considère le schéma relationnel suivant qui modélise une application sur la gestion d’une Bibliothèque

Créer les requêtes suivantes:

1- Créer un utilisateur superbiblio qui aura tous les droits sur la base biblio et qui aura lui-même le droit de créer d'autres bases et utilisateurs.
2- Créer un utilisateur bibliouser qui n'aura que le droit d'effectuer de la saisie sur la base biblio.
3- Modifier les attributs de l'utilisateur bibliouser pour lui permettre de créer de nouveaux utilisateurs et lui attribuer "AAAA" comme mot de passe.
4- Effacer l'utilisateur bibliouser.

123456789101112131415161718192021222324252627282930313233343536373839404142

-- 1- Créer un utilisateur superbiblio qui aura tous les droits sur la base biblio et qui aura lui-même le droit de créer d'autres bases et utilisateurs.-- 1ère solution: utiliser psq CREATE USER superbiblioWITH CREATEDB CREATEUSER;CREATE USER-- 2ème solution: utiliser la commande shell createuser CREATE user superbiblioShall the new user be allowed TO CREATE DATABASES? (y/n) yShall the new user be allowed TO CREATE more new users? (y/n) yCREATE USER-- 2- Créer un utilisateur bibliouser qui n'aura que le droit d'effectuer de la saisie sur la base biblio.-- 1ère solution: utiliser psql Type: \copyright FOR distribution terms \h FOR help WITH SQL commands \? FOR help ON internal slash commands \g OR terminate WITH semicolon TO execute query \q TO quitCREATE USER bibliouser;CREATE USER-- 2ème solution: utiliser la commande shell createuserCREATE user bibliouserShall the new user be allowed TO CREATE DATABASES? (y/n) nShall the new user be allowed TO CREATE more new users? (y/n) nCREATE USER-- 3- Modifier les attributs de l'utilisateur bibliouser pour lui permettre de créer de nouveaux utilisateurs et lui attribuer "AAAA" comme mot de passe.ALTER USER bibliouser WITH CREATEUSER PASSWORD 'AAAA';ALTER DATABASE-- 4- Effacer l'utilisateur bibliouser. DROP USER bibliouser;DROP USER

Exercice langage SQL : BD Cinéma (Partie 3)

Enoncé de l'Exercice (BD Cinéma Suite):

Les attributs NUM, NUM, NUMA, NUMC, NUMS sont des identifiants uniques (clés primaires) pour respectivement : FILM, PERSONNE, ACTEUR, CINÉMA, SALLE.
Un de ces attributs utilisé comme attribut d’une autre relation est une clé étrangère qui renvoie à la clé primaire de la relation correspondante, par exemple dans GÉNÉRIQUE, NUMF renvoie au NUMF de FILM et est défini sur le même domaine.
De plus, les attributs RÉALISATEUR dans FILM et NUMA dans ACTEUR sont définis sur le domaine des NUMP, et renvoient au NUMP de la personne correspondante.

Réaliser les Requêtes suivantes:

Requête 20 : Trouver les couples acteur-réalisateur (noms et prénoms) tels que l’un a dirigé l’autre sur un film et vice-versa sur un autre.

Requête 21 : Trouver le nom, le prénom, le numéro des acteurs qui ont joué dans tous les films de Lelouch, s'il y en a.

Requête 22 : Pour chaque film de Bergman, trouver le nom et le prénom de l'acteur qui a eu le plus gros salaire.

Requête 23 : Donner le nom et le prénom des réalisateurs qui ont eu le plus gros salaire sur un de leurs films (par comparaison avec ceux des acteurs).

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147

Requête 20 : Trouver les couples acteur-réalisateur (noms et prénoms) tels que l“’“un a dirigé l’autre sur un film et vice-versa sur un autre.Forme plate :SELECT DISTINCT P1.PRENOM, P1.NOM, P2.PRENOM, P2.NOMFROM PERSONNE P1, PERSONNE P2, FILM F1, FILM F2,DISTRIBUTION D1, DISTRIBUTION D2WHERE P1.NUMP > P2.NUMPAND P1.NUMP = F1.REALISATEURAND P2.NUMP = F2.REALISATEURAND F1.NUMF = D1.NUMFAND D1.NUMA = F2.RÉALISATEURAND F2.NUMF = D2.NUMFAND D2.NUMA = F1.REALISATEURForme imbriquée: SELECT DISTINCT P1.PRÉNOM, P1.NOM, P2.PRÉNOM, P2.NOM FROM PERSONNE P1, PERSONNE P2 WHERE (P1.NUMP, P2.NUMP) IN ( SELECT F1.RÉALISATEUR, F2.RÉALISATEUR FROM FILM F1, FILM F2, DISTRIBUTION D1, DISTRIBUTION D2 WHERE F1.RÉALISATEUR > F2.RÉALISATEURAND F1.NUMF = D1.NUMFAND D1.NUMA = F2.RÉALISATEURAND F2.NUMF = D2.NUMFAND D2.NUMA = F1.RÉALISATEUR)Requête 21 : Trouver le nom, le prénom, le numéro des acteurs qui ont joué dans tous les films de Lelouch, s“’“il y en a.Forme imbriquée – prédicat EXISTS : « dans un des films » SELECT NOM, PRÉNOM FROM PERSONNE P WHERE EXISTS (SELECT * FROM FILM F WHERE RÉALISATEUR IN (SELECT NUMP FROM PERSONNE WHERE NOM = ‘Lelouch’) ANDEXISTS (SELECT * FROM DISTRIBUTION DWHERE D.NUMF = F.NUMFAND D.NUMA = P.NUMP) )Requête 22 : Pour chaque film de Bergman, trouver le nom et le prénom de L“’ “acteur qui a eu le plus gros salaire.Forme imbriquée – prédicat NOT EXISTS : un seul rôle par acteur SELECT F.TITRE, PA.PRÉNOM, PA.NOM FROM FILM F, DISTRIBUTION D1, PERSONNE PA WHERE F.NUMF = D1.NUMF ANDD1.NUMA = PA.NUMP ANDRÉALISATEUR IN ( SELECT NUMP FROM PERSONNE WHERE NOM = “Bergman”) ANDNOT EXISTS ( SELECT * FROM DISTRIBUTION D2 WHERE D2.NUMF = D1.NUMF AND D2.SALAIRE > D1.SALAIRE )Forme imbriquée + – prédicat > ALL : possibilité de plusieurs rôles pour un même acteur SELECT F.TITRE, PA.PRÉNOM, PA.NOM FROM FILM F, DISTRIBUTION D1, PERSONNE PA WHERE F.NUMF = D1.NUMF ANDD1.NUMA = PA.NUMP ANDRÉALISATEUR IN ( SELECT NUMP FROM PERSONNE WHERE NOM = ‘Bergman’) GROUP BY D1.NUMF, D1.NUMA, F.TITRE, PA.PRÉNOM, PA.NOM HAVING SUM (SALAIRE) > ALL ( SELECT SUM (SALAIRE) FROM DISTRIBUTION D2 WHERE D2.NUMF = D1.NUMFAND D2.NUMA D1.NUMAGROUP BY D2.NUMA)Forme imbriquée: possibilité de plusieurs rôles pour un même acteur SELECT F.TITRE, PA.PRÉNOM, PA.NOM FROM FILM F, PERSONNE PAWHERE (F.NUMF, PA.NUMP) IN (SELECT D1.NUMF, D1.NUMA FROM DISTRIBUTION D1 WHERE D1.NUMF IN ( SELECT NUMF FROM FILM WHERE RÉALISATEUR IN (SELECT NUMP FROM PERSONNE WHERE NOM = ‘Bergman’ ) ) GROUP BY D1.NUMF, D1.NUMA HAVING SUM (D1.SALAIRE) = ( SELECT MAX ( SELECT SUM (D2.SALAIRE) FROM DISTRIBUTION D2 WHERE D2.NUMF = D1.NUMF GROUP BY D2.NUMA))Utilisation d’une vue GROUPée: CREATE VIEW SALAIRE_TOTAL_ACTEUR_FILM (NUMA, NUMF, SALAIRE_TOTAL) AS SELECT NUMA, NUMF, SUM (SALAIRE) FROM FILM GROUP BY NUMA, NUMFSELECT F.TITRE, PA.PRÉNOM, PA.NOMFROM FILM F, SALAIRE_TOTAL_ACTEUR_FILM D1, PERSONNE PAWHERE F.NUMF = D1.NUMFAND D1.NUMA = PA.NUMPAND RÉALISATEUR IN (SELECT NUMP FROM PERSONNE WHERE NOM = ‘Bergman’)ANDNOT EXISTS ( SELECT * FROM SALAIRE_TOTAL_ACTEUR_FILM D2 WHERE D2.NUMF = D1.NUMF AND D2.SALAIRE_TOTAL > D1.SALAIRE_TOTAL )Requête 23 : Donner le nom et le prénom des réalisateurs qui ont eu le plus gros salaire sur un de leurs films (par comparaison avec ceux des acteurs).Forme imbriquée : SELECT PRÉNOM, NOM FROM PERSONNE WHERE NUMP IN ( SELECT RÉALISATEUR FROM FILM F WHERE SALAIRE_RÉAL > ( SELECT MAX (SALAIRE) FROM DISTRIBUTION D WHERE D.NUMF = F.NUMF ) )Forme imbriquée 2: SELECT PRÉNOM, NOM FROM PERSONNE WHERE NUMP IN ( SELECT RÉALISATEURFROM FILM FWHERE SALAIRE_RÉAL > ALL (SELECT SUM (SALAIRE) FROM DISTRIBUTION D WHERE D.NUMF = F.NUMF GROUP BY NUMA ) )Forme imbriquée 3 : SELECT PRÉNOM, NOM FROM PERSONNE WHERE NUMP IN ( SELECT RÉALISATEUR FROM FILM F WHERE SALAIRE_RÉAL + (SELECT SUM (SALAIRE) FROM DISTRIBUTION D1 WHERE D1.NUMF = F.NUMF AND D1.NUMA = F.RÉALISATEUR ) >(SELECT MAX ( SELECT SUM (SALAIRE) FROM DISTRIBUTION D2 WHERE D2.NUMF = F.NUMF GROUP BY D2.NUMA ) ) )

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'

);

Exercices requêtes avec langage SQL

Exercice n°1 :

Soit le modèle relationnel suivant relatif à une base de données sur des représentations musicales :

REPRESENTATION (n°représentation, titre_représentation, lieu)
MUSICIEN (nom, n°représentation*)
PROGRAMMER (date, n°représentation*, tarif)

Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *

Questions :

1 - Donner la liste des titres des représentations.
2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.
3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.
4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.

Exercice n°2 :

Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du Tour de France 97, dont une des étapes de type "contre la montre individuel" se déroula à Saint-Etienne :

EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif)
COUREUR(NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)
PAYS(CodePays, NomPays)
TYPE_ETAPE(CodeType, LibelléType)
ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, CodeType*)
PARTICIPER(NuméroCoureur*, NuméroEtape*, TempsRéalisé)
ATTRIBUER_BONIFICATION(NuméroEtape*, km, Rang, NbSecondes, NuméroCoureur*)

Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *

Questions :

1 - Quelle est la composition de l'équipe Festina (Numéro, nom et pays des coureurs) ?
2 - Quel est le nombre de kilomètres total du Tour de France 97 ?
3 - Quel est le nombre de kilomètres total des étapes de type "Haute Montagne"?
4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13 premières étapes sachant que les
bonifications ont été intégrées dans les temps réalisés à chaque étape ?
7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?

Correction de l'exercice n°1

1 - Donner la liste des titres des représentations.

SELECT titre_représentation FROM REPRESENTATION ;

2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.

SELECT titre_représentation FROM REPRESENTATION
WHERE lieu="Opéra Bastille" ;

3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.

SELECT nom, titre_représentation
FROM MUSICIEN, REPRESENTATION
WHERE MUSICIEN.n°représentation = REPRESENTATION.n°représentation ;

4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.

SELECT titre_représentation, lieu, tarif
FROM REPRESENTATION, PROGRAMMER
WHERE PROGRAMMER.n°représentation =
REPRESENTATION.n°représentation
AND date='14/06/96' ;

Correction de l'exercice n°2

1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des coureurs) ?

SELECT NuméroCoureur, NomCoureur, NomPays
FROM EQUIPE A, COUREUR B, PAYS C
WHERE A.CodeEquipe=B.CodeEquipe And B.CodePays=C.CodePays
And NomEquipe="FESTINA" ;

2 - Quel est le nombre de kilomètres total du Tour de France 97 ?

SELECT SUM(Nbkm) FROM ETAPE ;

3 - Quel est le nombre de kilomètres total des étapes de type HAUTE MONTAGNE ?

SELECT SUM(Nbkm) FROM ETAPE A, TYPE_ETAPE B
WHERE A.CodeType=B.CodeType And LibelléType="HAUTE MONTAGNE" ;

4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?

SELECT NomCoureur FROM COUREUR
WHERE NuméroCoureur NOT IN (SELECT NuméroCoureur FROM
ATTRIBUER_BONIFICATION) ;

5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?

SELECT NomCoureur FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur
GROUP BY NuméroCoureur, NomCoureur
HAVING COUNT(*)=(SELECT COUNT(*) FROM ETAPE) ;

6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13 premières étapes sachant que les bonifications ont été intégrées dans les temps réalisés à chaque étape ?

SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur and NuméroEtapeGROUP BY A.NuméroCoureur, NomCoureur, CodeEquipe, CodePays
ORDER BY Total;

7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?

SELECT NomEquipe, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B, EQUIPE C
WHERE A.NuméroCoureur=B.NuméroCoureur And
B.CodeEquipe=C.CodeEquipe
And NuméroEtapeGROUP BY B.CodeEquipe, NomEquipe
ORDER BY Total;

Article publié le 27 Mars 2011 Mise à jour le Samedi, 17 Décembre 2022 19:18 par Salim KHALIL