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

1. Les Requêtes S.Q.L« Structured Query Language) Rénovation 1ière STG Formateurs : Richit Nathalie, Pouplier Thierry, Patrice Viaud, Patrick Laupies

2. Le cas support : Net_Provence CONTEXTE: 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 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 places limité. Il vous est demandé de d’interroger la base existante en créant des requêtes en SQL.

3. Présentation de l’écran Access pour la réalisation d’une requête S.Q.L Faire apparaître les relations pour avoir les noms des propriétés La fenêtre requête en SQL

4. Opérateurs de l’Algèbre Relationnelle et Arithmétiques LA PROJECTION LA SELECTION LES CALCULS LA JOINTURE

5. LA PROJECTION

6. PROJECTION On veut garder les champs Code_client, Nom_client,Prénom_client, Ville_Client uniquement SELECT [Code_client], [Nom_client], [Prénom_client], [Ville_client] FROM [Clients];

7. R1 : Liste des Clients SELECT [Code_client], [Nom_client], [Prénom_client], [Ville_client] FROM Clients;

8. R2 : Liste des Voyages SELECT * FROM[VOYAGES];

9. R3 : Liste des Agences SELECT * FROM [Agences de voyage];

10. R4 : Liste des Clients code, nom (tri croissant), prénom, ville SELECT [Code_client], [Nom_client], [Prénom_client], [Ville_client] FROM [Clients] ORDER BY [Nom_client];

11. R5 : Liste des Agences (tri sur le nom de l’agence) SELECT * FROM [Agences de voyage] ORDER BY [Nom_agence];

12. 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) SELECT [Nom_client] AS Noms, [Prénom_client] AS Prénoms, [Adresse_rue_client] AS Adresses, [Ville_client] AS Villes FROM [Clients] ORDER BY [Ville_client] DESC;

13. Opérateurs de l’Algèbre Relationnelle et Arithmétiques LA PROJECTION LA SELECTION LES CALCULS LA JOINTURE

14. LA SELECTION

15. R7 : Liste des clients de Paris (tri croissant sur le nom du client) SELECT * FROM [Clients] WHERE [Ville_client]="PARIS" ORDER BY [Nom_client];

16. R8 : Liste des agences des Bouches du Rhône (tri sur le CP) SELECT [Code_agence], [Nom_agence], [Adresse_agence], [CP_agence], [Ville_agence] FROM [Agences de voyage] WHERE [CP_agence] Like "13*" ORDER BY [CP_agence];

17. R9 : Liste des Clients de Manosque OU Aix en Provence (tri sur le nom du client) SELECT * FROM [Clients] WHERE [Ville_client]="AIX EN PROVENCE" Or [Ville_client]="MANOSQUE" ORDER BY [Nom_client];

18. R10: Afficher le nom du client N°18 avec son code postal, ainsi que le nom du client N° 50 avec son CP SELECT [Nom_client], [CP_client] FROM [CLIENTS] WHERE [Code_client] in (18,50);

19. 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) SELECT [Nom_voyage], [Date_départ], [Durée_voyage], [Prix_voyage] FROM [Voyages] WHERE [Nom_voyage]="PARIS" Or [Nom_voyage]="MADRID" ORDER BY [Date_départ];

20. R12: Afficher la liste des voyages (Nom voyage) sauf le voyage à destination de ZURICH (tri sur le nom du voyage) SELECT [Nom_voyage] FROM [Voyages] WHERE Not [Nom_voyage]="ZURICH" ORDER BY [Nom_voyage];

21. 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) SELECT [Nom_client] AS [NOMS lettre P] FROM Clients WHERE [Nom_client] LIKE "P*" ORDER BY [Nom_client];

22. R14: Voyages dont la durée >=15 jours (tri croissant sur la durée du voyage) SELECT [Num_voyage] AS [Référence du voyage], [Nom_voyage] AS [Destination du voyage], [Durée_voyage] AS [Durée des voyages jours] FROM Voyages WHERE [Durée_voyage]>=15 ORDER BY [Durée_voyage];

23. R15: Liste des voyages proposés en 2004 dont la durée est >= 9 jours (tri croissant sur la propriété date_départ) SELECT [Num_voyage], [Nom_voyage], [Date_départ], [Durée_voyage] FROM Voyages WHERE [Date_départ] Between #1/1/2004# And #12/31/2004# AND [Durée_voyage]>=9 ORDER BY [Date_départ];

24. R16: Liste des voyages dont le prix est compris entre 1000€ et 2400€ (tri croissant sur la date_départ) SELECT [Nom_voyage], [Date_départ], [Durée_voyage], [Prix_voyage] FROM Voyages WHERE [Prix_voyage] BETWEEN 1000 AND 2400 ORDER BY [Date_départ];

25. Opérateurs de l’Algèbre Relationnelle et Arithmétiques LA PROJECTION LA SELECTION LES CALCULS LA JOINTURE

26. Les Calculs

27. R17: Prix du voyage le plus cher (afficher uniquement cette donnée) SELECTMAX([Prix_voyage])AS [Prix du voyage le plus cher] FROM Voyages;

28. R18: Prix du voyage le plus faible (afficher uniquement cette donnée) SELECT Min([Prix_voyage]) AS [PRIX DU VOYAGE LE PLUS BAS] FROM Voyages;

29. R19: Nombre de clients (le résultat doit être stocké sous l’intitulé Nombre de clients Net_Provence) SELECT COUNT([Code_client])AS [Nombre de clients Net_provence] FROM Clients;

30. R20: Nombre de voyages SELECT COUNT([Num_voyage])AS [Nombre de Voyages Net_provence] FROM [Voyages];

31. R21 : Nombre total d’Agences SELECT COUNT([Code_agence])AS [Nombre total Agences] FROM [Agences de voyage];

32. R22: Prix moyen des voyages Net_Provence SELECT AVG([Prix_voyage])AS [PRIX MOYEN DES VOYAGES NET_PROVENCE] FROM [Voyages];

33. R23: Prix moyen des voyages en fonction de la durée des voyages SELECT [Durée_voyage], AVG([Prix_voyage])AS [Prix moyen des voyages] FROM [Voyages] GROUP BY [Durée_voyage];

34. Opérateurs de l’Algèbre Relationnelle et Arithmétiques LA PROJECTION LA SELECTION LES CALCULS LA JOINTURE

35. LA JOINTURE

36. R24: NB de places proposées par voyage et par agence SELECT [Nom_agence], [#num_voyage], [Nombr_de_place_proposées] FROM [Agences de voyage], [Nombre de places proposées par agence] WHERE [Agences de voyage].Code_agence=[Nombre de places proposées par agence].[#code_agence];

37. R25: Montant dû par client SELECT [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û] FROM [Clients], [Réservations], [lignes réservation], [Voyages] WHERE Clients.Code_client=Réservations.[#Code client] And Réservations.[N° réservation]=[Lignes réservation].[#Num_réservation] And Voyages.Num_voyage=[Lignes réservation].[#Num_voyage];

38. R26: NB total de places proposées par voyage (trier sur le total) SELECT [Num_voyage] AS [Référence du voyage Net_provence], [Nom_voyage] AS [Nom du voyage], Sum([Nombr_de_place_proposées]) AS [places_ proposées par voyage] FROM [Nombre de places proposées par agence], [Voyages] WHERE Voyages.Num_voyage=[Nombre de places proposées par agence].[#num_voyage] GROUP BY [Num_voyage], [Nom_voyage] ORDER BY Sum([Nombr_de_place_proposées]);

39. R27: Réservations du 01/01/2003 au 31/01/2003 SELECT [Nom_client] AS [Nom du Client], [Prénom_client] AS Prénom, [Date réservation] AS [Réservations de janvier] FROM [Clients], [Réservations] WHERE Clients.Code_client=Réservations.[#Code client] And [Date réservation]>=#1/1/2003# And [Date réservation]<=#1/31/2003#;

40. R28 :Total des places proposées par agence SELECT [Nom_agence], SUM([Nombr_de_place_proposées]) AS [Total places par agence] FROM [Agences de voyage], [Nombre de places proposées par agence] WHERE [Agences de voyage].Code_agence=[Nombre de places proposées par agence].[#code_agence] GROUP BY [Nom_agence];

41. Académie Aix-Marseille Rénovation STG Formation S.Q.L Formateurs : Nathalie Richit Patrick Laupies Patrice Viaud Thierry Pouplier

42. FIN