Exercice création des requêtes SQL

Participez au vote ☆☆☆☆☆★★★★★

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 possibles
SET @Result = RTRIM(LTRIM(@CHAINEDONNEE))
SET @SEPARATEUR = RTRIM(LTRIM(@SEPARATEUR))

-- Recherche si le séparateur existe dans la chaine donnée
SET @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ésultat
RETURN @Result

END

-- 2) Créer la Fonction d"'"ajout d"'"une durée (Année, Mois, Jour, Heure, Minute, Seconde) à une date.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  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 @Ret
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- 3) Créer une fonction qui permet de retrouver la position du premier chiffre d"une chaine. 

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC] (@NOMDIV nvarchar(30))
RETURNS smallint

AS

BEGIN

	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
	END

RETURN @j
END

-- Ou Bien :

CREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC_V2] (@NOMDIV nvarchar(30))
RETURNS smallint

AS

BEGIN

  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.

-- 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_theorique
AS ON INSERT
TO emprunts.datepret
DO (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 emprunteurs

CREATE INDEX index_emprunteurs
ON emprunteurs(idemprunteur, nom);
CREATE

-- 4- Effacez-le

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

Requête 10:
SELECT Nometu, E.Numetu, Cdsexe
FROM ETUDIANT E, NOTES N, MATIERE M
WHERE E.Numetu=N.Numetu
AND N.Numat=M.Numat
AND 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.Nometu
FROM ETUDIANT E1, ETUDIANT E2
WHERE E1.CdSexe='H'
AND E2.CdSexe='F'
AND DATEDIFF(E1.Dtnaiss,E2.Dtnaiss) BETWEEN -180 AND 180;

Requête 12:
SELECT E.Numetu, Nometu, Nomat
FROM ETUDIANT E
LEFT OUTER JOIN NOTES N
ON E.Numetu=N.Numetu
LEFT OUTER JOIN Matiere M
ON N.Numat=M.Numat OR N.Numat IS NULL
WHERE Note IS NULL;

Requête 13:
SELECT Nomens, Nomat, Avg(note)
FROM ENSEIGNANT E, MATIERE M, NOTES N
WHERE E.Numens=M.Numens
AND M.Numat=N.Numat
GROUP BY Nomens, Nomat
HAVING Avg(note)< 10;

Requête 14:
SELECT Nomat, Nometu, Note
FROM MATIERE M, NOTES N, ETUDIANT E
WHERE N.Numat=M.Numat
AND E.Numetu=N.Numetu
AND Note=(SELECT Max(Note) FROM NOTES E1
          WHERE E1.Numat=M.Numat);

Requête 15:
SELECT Nomat, Nometu, Note
FROM MATIERE M, NOTES N, ETUDIANT E
WHERE N.Numat=M.Numat
AND E.Numetu=N.Numetu
AND 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.

-- 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 superbiblio
WITH CREATEDB CREATEUSER;
CREATE USER

-- 2ème solution: utiliser la commande shell createuser 
CREATE user superbiblio
Shall the new user be allowed TO CREATE DATABASES? (y/n) y
Shall the new user be allowed TO CREATE more new users? (y/n) y
CREATE 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 quit

CREATE USER bibliouser;
CREATE USER

-- 2ème solution: utiliser la commande shell createuser
CREATE user bibliouser
Shall the new user be allowed TO CREATE DATABASES? (y/n) n
Shall the new user be allowed TO CREATE more new users? (y/n) n
CREATE 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).

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.NOM
FROM PERSONNE P1, PERSONNE P2, FILM F1, FILM F2,
DISTRIBUTION D1, DISTRIBUTION D2
WHERE P1.NUMP > P2.NUMP
AND P1.NUMP = F1.REALISATEUR
AND P2.NUMP = F2.REALISATEUR
AND F1.NUMF = D1.NUMF
AND D1.NUMA = F2.RÉALISATEUR
AND F2.NUMF = D2.NUMF
AND D2.NUMA = F1.REALISATEUR

Forme 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ÉALISATEUR
AND F1.NUMF = D1.NUMF
AND D1.NUMA = F2.RÉALISATEUR
AND F2.NUMF = D2.NUMF
AND 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 D
WHERE D.NUMF = F.NUMF
AND 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.NUMF
AND D2.NUMA  D1.NUMA
GROUP 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 PA
WHERE (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, NUMF
SELECT F.TITRE, PA.PRÉNOM, PA.NOM
FROM FILM F, SALAIRE_TOTAL_ACTEUR_FILM D1, PERSONNE PA
WHERE F.NUMF = D1.NUMF
AND D1.NUMA = PA.NUMP
AND 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ÉALISATEUR
FROM FILM F
WHERE 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 <= '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'

);

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éroEtape<=13
GROUP 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éroEtape<=13
GROUP BY B.CodeEquipe, NomEquipe
ORDER BY Total;

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