Les Requêtes S.Q.LLes Requêtes S.Q.L« Structured Query Language)« Structured Query Language)

Rénovation 1Rénovation 1ièreière STG STG

Formateurs : Richit Nathalie, Pouplier Thierry, Patrice Viaud, Patrick Laupies Formateurs : Richit Nathalie, Pouplier Thierry, Patrice Viaud, Patrick Laupies

Le cas support : Net_ProvenceLe cas support : Net_Provence

CONTEXTE:Net_Provence est un réseau d’agences de voyage né en Provence, il y a Net_Provence est un réseau d’agences de voyage né en Provence, il y a quelques années, et qui se développe actuellement dans toute la quelques années, et qui se développe actuellement dans toute la France. Le système de réservation est géré par un SGBDR. Chaque France. Le système de réservation est géré par un SGBDR. Chaque agence de voyage dispose, pour un voyage donné, d’un nombre de agence de voyage dispose, pour un voyage donné, d’un nombre de places limité. Il vous est demandé de d’interroger la base existante en places limité. Il vous est demandé de d’interroger la base existante en créant des requêtes en SQL.créant des requêtes en SQL.

Présentation de l’écran Access pour la Présentation de l’écran Access pour la réalisation d’une requête S.Q.Lréalisation d’une requête S.Q.L

Faire apparaître Faire apparaître les relations pour les relations pour

avoir les noms avoir les noms des propriétésdes propriétés

La fenêtre La fenêtre requête en requête en

SQLSQL

Opérateurs de l’Algèbre Relationnelle Opérateurs de l’Algèbre Relationnelle et Arithmétiqueset Arithmétiques

1)1) LA PROJECTIONLA PROJECTION2)2) LA SELECTIONLA SELECTION3)3) LES CALCULSLES CALCULS4)4) LA JOINTURELA JOINTURE

LA PROJECTIONLA PROJECTION

PROJECTIONPROJECTION

On veut garder les champs On veut garder les champs Code_clientCode_client, , Nom_clientNom_client,,Prénom_clientPrénom_client, , Ville_ClientVille_Client uniquementuniquement

SELECTSELECT [Code_client], [Nom_client], [Prénom_client], [Ville_client]FROMFROM [Clients];

R1 : Liste des Clients

SELECTSELECT [Code_client], [Nom_client], [Prénom_client], [Code_client], [Nom_client], [Prénom_client], [Ville_client][Ville_client]

FROMFROM Clients; Clients;

R2 : Liste des Voyages

SELECTSELECT *FROMFROM [VOYAGES];

R3 : Liste des Agences

SELECTSELECT *

FROMFROM [Agences de voyage];

R4 : Liste des Clients code, nom (tri croissant), prénom, ville

SELECTSELECT [Code_client], [Nom_client], [Prénom_client], [Ville_client]

FROMFROM [Clients]

ORDER BYORDER BY [Nom_client];

R5 : Liste des Agences (tri sur le nom de l’agence)

SELECTSELECT *

FROMFROM [Agences de voyage]

ORDER BYORDER BY [Nom_agence];

R601: Liste des clients en remplaçant les intitulés des colonnes nom client en NOMS, prénom_client en PRENOMS,Adresses et Villes (tri décroissant Villes)

SELECTSELECT [Nom_client] ASAS Noms, [Prénom_client] ASAS Prénoms, [Adresse_rue_client] ASAS Adresses, [Ville_client] ASAS Villes

FROMFROM [Clients]

ORDER BYORDER BY [Ville_client] DESCDESC;

Opérateurs de l’Algèbre Opérateurs de l’Algèbre Relationnelle et ArithmétiquesRelationnelle et Arithmétiques

1)1) LA PROJECTIONLA PROJECTION2)2) LA SELECTIONLA SELECTION3)3) LES CALCULSLES CALCULS4)4) LA JOINTURELA JOINTURE

LA SELECTIONLA SELECTION

R7 : Liste des clients de Paris (tri croissant sur le nom du client)

SELECTSELECT *

FROMFROM [Clients]

WHEREWHERE [Ville_client]="PARIS"

ORDER BYORDER BY [Nom_client];

R8 : Liste des agences des Bouches du Rhône (tri sur le CP)

SELECTSELECT [Code_agence], [Nom_agence], [Adresse_agence], [CP_agence], [Ville_agence]

FROMFROM [Agences de voyage]

WHEREWHERE [CP_agence] LikeLike "13*"

ORDER BYORDER BY [CP_agence];

R9 : Liste des Clients de Manosque OU Aix en Provence (tri sur le nom du client)

SELECTSELECT *

FROMFROM [Clients]

WHEREWHERE [Ville_client]="AIX EN PROVENCE" Or Or [Ville_client]="MANOSQUE"

ORDER BYORDER BY [Nom_client];

R10: Afficher le nom du client N°18 avec son code postal, ainsi que le nom du client N° 50 avec son CP

SELECTSELECT [Nom_client], [CP_client]

FROMFROM [CLIENTS]

WHEREWHERE [Code_client] inin (18,50);

R11: Liste des voyages à Paris ou Madrid en affichant le nom du voyage avec la durée et la date du voyage (tri sur la date du départ)

SELECTSELECT [Nom_voyage], [Date_départ], [Durée_voyage], [Prix_voyage]

FROMFROM [Voyages]

WHEREWHERE [Nom_voyage]="PARIS" OrOr [Nom_voyage]="MADRID"

ORDER BYORDER BY [Date_départ];

R12: Afficher la liste des voyages (Nom voyage) sauf le voyage à destination de ZURICH (tri sur le nom du voyage)

SELECTSELECT [Nom_voyage]

FROMFROM [Voyages]

WHERE NotWHERE Not [Nom_voyage]="ZURICH"

ORDER BYORDER BY [Nom_voyage];

R13:Liste des clients dont le nom commence par la lettre P en changeant l’intitulé de la colonne par Noms Lettre P (trier sur Nom_client)

SELECTSELECT [Nom_client] ASAS [NOMS lettre P]FROMFROM ClientsWHEREWHERE [Nom_client] LIKELIKE "P*"ORDER BYORDER BY [Nom_client];

R14: Voyages dont la durée >=15 jours (tri croissant sur la durée du voyage)

SELECTSELECT [Num_voyage] ASAS [Référence du voyage], [Nom_voyage] ASAS [Destination du voyage], [Durée_voyage] ASAS [Durée des voyages jours]

FROMFROM Voyages

WHEREWHERE [Durée_voyage]>=15

ORDER BYORDER BY [Durée_voyage];

R15: Liste des voyages proposés en 2004 dont la durée est >= 9 jours (tri croissant sur la propriété date_départ)

SELECTSELECT [Num_voyage], [Nom_voyage], [Date_départ], [Durée_voyage]

FROMFROM Voyages

WHEREWHERE [Date_départ] BetweenBetween #1/1/2004# AndAnd #12/31/2004#

ANDAND [Durée_voyage]>=9

ORDER BYORDER BY [Date_départ];

R16: Liste des voyages dont le prix est compris entre 1000€ et 2400€ (tri croissant sur la date_départ)

SELECTSELECT [Nom_voyage], [Date_départ], [Durée_voyage], [Prix_voyage]

FROMFROM Voyages

WHEREWHERE [Prix_voyage] BETWEENBETWEEN 1000 ANDAND 2400

ORDER BYORDER BY [Date_départ];

Opérateurs de l’Algèbre Opérateurs de l’Algèbre Relationnelle et ArithmétiquesRelationnelle et Arithmétiques

1)1) LA PROJECTIONLA PROJECTION2)2) LA SELECTIONLA SELECTION3)3) LES CALCULSLES CALCULS4)4) LA JOINTURELA JOINTURE

Les Calculs

R17: Prix du voyage le plus cher (afficher uniquement cette donnée)

SELECTSELECT MAX(MAX([Prix_voyage])) ASAS [Prix du voyage le plus cher]

FROMFROM Voyages;

R18: Prix du voyage le plus faible (afficher uniquement cette donnée)

SELECT Min(SELECT Min([Prix_voyage]) AS) AS [PRIX DU VOYAGE LE PLUS BAS]

FROMFROM Voyages;

R19: Nombre de clients (le résultat doit être stocké sous l’intitulé Nombre de clients Net_Provence)

SELECT COUNT(SELECT COUNT([Code_client])) ASAS [Nombre de clients Net_provence]

FROMFROM Clients;

R20: Nombre de voyages

SELECT COUNT(SELECT COUNT([Num_voyage])) ASAS [Nombre de Voyages Net_provence]

FROMFROM [Voyages];

R21 : Nombre total d’Agences

SELECT COUNT(SELECT COUNT([Code_agence])) ASAS [Nombre total Agences]

FROMFROM [Agences de voyage];

R22: Prix moyen des voyages Net_Provence

SELECT AVG(SELECT AVG([Prix_voyage])) ASAS [PRIX MOYEN DES VOYAGES NET_PROVENCE]

FROMFROM [Voyages];

R23: Prix moyen des voyages en fonction de la durée des voyages

SELECTSELECT [Durée_voyage], AVG(AVG([Prix_voyage])) ASAS [Prix moyen des voyages]

FROMFROM [Voyages]

GROUP BYGROUP BY [Durée_voyage];

Opérateurs de l’Algèbre Opérateurs de l’Algèbre Relationnelle et ArithmétiquesRelationnelle et Arithmétiques

1)1) LA PROJECTIONLA PROJECTION2)2) LA SELECTIONLA SELECTION3)3) LES CALCULSLES CALCULS4)4) LA JOINTURELA JOINTURE

LA JOINTURE

R24: NB de places proposées par voyage et par agence

SELECTSELECT [Nom_agence], [#num_voyage], [Nombr_de_place_proposées]

FROMFROM [Agences de voyage], [Nombre de places proposées par agence]

WHEREWHERE [Agences de voyage].Code_agence=[Nombre de places proposées par agence].[#code_agence];

R25: Montant dû par client

SELECTSELECT [Nom_client], [Prénom_client], [Adresse_rue_client], [CP_client], [Ville_client], [N° réservation], [Date réservation], [Nom_voyage], [Date_départ], [Prix_voyage], [Nombre_places_réservées], [Tx_de_remise], ([Prix_voyage]*(1-[Tx_de_remise]))*[Nombre_places_réservées] AS [Montant dû]

FROMFROM [Clients], [Réservations], [lignes réservation], [Voyages]

WHEREWHERE Clients.Code_client=Réservations.[#Code client] AndAnd Réservations.[N° réservation]=[Lignes réservation].[#Num_réservation] And And Voyages.Num_voyage=[Lignes réservation].[#Num_voyage];

R26: NB total de places proposées par voyage (trier sur le total)

SELECTSELECT [Num_voyage] ASAS [Référence du voyage Net_provence], [Nom_voyage] ASAS [Nom du voyage], Sum([Nombr_de_place_proposées]) ASAS [places_ proposées par voyage]

FROMFROM [Nombre de places proposées par agence], [Voyages]

WHEREWHERE Voyages.Num_voyage=[Nombre de places proposées par agence].[#num_voyage]

GROUP BYGROUP BY [Num_voyage], [Nom_voyage]

ORDER BYORDER BY Sum([Nombr_de_place_proposées]);

R27: Réservations du 01/01/2003 au 31/01/2003

SELECTSELECT [Nom_client] ASAS [Nom du Client], [Prénom_client] ASAS Prénom, [Date réservation] ASAS [Réservations de janvier]

FROMFROM [Clients], [Réservations]

WHEREWHERE Clients.Code_client=Réservations.[#Code client] AndAnd [Date réservation]>=#1/1/2003# And And [Date réservation]<=#1/31/2003#;

R28 :Total des places proposées par agence

SELECTSELECT [Nom_agence], SUMSUM([Nombr_de_place_proposées])

ASAS [Total places par agence]

FROMFROM [Agences de voyage], [Nombre de places proposées par agence]

WHEREWHERE [Agences de voyage].Code_agence=[Nombre de places proposées par agence].[#code_agence]

GROUP BYGROUP BY [Nom_agence];

Académie Aix-MarseilleAcadémie Aix-Marseille

Rénovation STG Rénovation STG

Formation S.Q.LFormation S.Q.L

Formateurs :Formateurs :

Nathalie RichitNathalie Richit

Patrick LaupiesPatrick Laupies

Patrice ViaudPatrice Viaud

Thierry PouplierThierry Pouplier

FINFIN