Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

PrepaVogt-ESSCA Management & Finances 2 Année Académique : 2018-2019 Par Etienn

PrepaVogt-ESSCA Management & Finances 2 Année Académique : 2018-2019 Par Etienne KOUOKAM 1/6 Version du 9 octobre 2018 Requête SQL avec Microsoft Access ! Travail à faire : Assurez-vous d’avoir pris connaissance du contenu du cours sur SQL. Durant ce TP, nous allons définir un schéma de base de données, y ajouter des contraintes, des vues, des déclencheurs (triggers), ainsi qu’un certain nombre d’informations. La base de données s’intitule Agence de voyages, et propose de d’écrire les activités de différents clients lors d’un séjour dans une station balnéaire. a. Création des tables Voici le schéma Agence de voyages : • STATION (nomStation,capacité, lieu, région, tarif) • ACTIVITE (nomStation, libellé, prix) • CLIENT (id, nom, prénom, ville, région, solde) • SEJOUR (id, station, début, nbPlaces) Voici les autres contraintes portant sur ces tables. 1. Les données capacité, lieu, nom, ville, solde et nbPlaces doivent toujours être connues. 2. Les montants (prix, tarif et solde) ont une valeur par défaut à 0. 3. Il ne peut pas y avoir deux stations dans le même lieu et la même région. 4. Les régions autorisées sont : ’Océan Indien’, ’Antilles’, ’Europe’, ’Amériques’ et ’Extrême Orient’. 5. La destruction d’une station doit entraîner la destruction de ses activités et de ses séjours. Créer les tables du schéma Agence de voyages. On veillera à bien définir les clés primaires et étrangères, et à donner des noms explicites au contraintes check, primary key et foreign key b. Formulaire + Insertion de données 1. Créer pour chaque table un formulaire de saisie 2. Tester ensuite les contraintes avec quelques ordres SQL. Par exemple : détruire la station et vérifier que les activités ont disparu ; insérer une autre station en (Guadeloupe, Antilles) ; insérer une station dans une région 'Nullepart', etc. 3. Insérer les données suivantes dans vos différentes tables : PrepaVogt-ESSCA Management & Finances 2 Année Académique : 2018-2019 Par Etienne KOUOKAM 2/6 Version du 9 octobre 2018 STATION NomStation Capacité Lieu Région Tarif Venusa 350 Guadeloupe Antilles 1200 Farniente 200 Seychelles Océan Indien 1500 Santalba 150 Martinique Antilles 2000 Passac 400 Alpes Europe 1000 ACTIVITES NomStation Libellé Prix Venusa Plongée 120 Venusa voile 150 Farniente Plongée 130 Passac Ski 200 Passac Piscine 20 Santalba Kayac 50 CLIENT id nom prenom ville région solde 10 Fogg Phileas Londres Europe 12465 20 Pascal Blaise Paris Europe 6763 30 Kerouac Jack New York Amérique 9812 SEJOUR idClient station début nbPlaces 20 Venusa 01/07/1998 4 10 Passac 01/07/1998 2 30 Santalba 14/08/1996 5 20 Santalba 03/08/1998 4 30 Passac 15/08/1998 3 30 Venusa 03/08/1998 3 30 Farniente 24/06/1999 5 10 Farniente 05/09/1998 3 c. Requêtes La base de données que vous avez créée contient déjà un petit jeu de données plus ou moins réaliste. A présent, il faut concevoir, saisir et exécuter les ordres SQL correspondant aux requêtes suivantes. Créons, la requête qui extrait de la table "STATION" (contenant une liste de stations) tous les enregistrements. Pour cela, activons Menu « Créer », puis « Création de Requêtes ». Cliquons sur la petite flèche située à droite de l'outil "Affichage", et dans la liste déroulante, choisissons "Mode SQL". Sélection Introduisons alors la requête suivante : 1. SELECT * 2. FROM STATION ; On remarque alors que sont affichés tous les champs de la table Station, il n’y a pas eu de sélection particulière. Projection Si d’aventure on s’intéresse à la requête qui extrait de la table "STATION" (contenant une liste de stations) uniquement les trois champs « NomStation », «Capacité » et « Lieu », on aura alors PrepaVogt-ESSCA Management & Finances 2 Année Académique : 2018-2019 Par Etienne KOUOKAM 3/6 Version du 9 octobre 2018 1. SELECT STATION.NomStation, STATION.Capacité, STATION.Lieu 2. FROM STATION ; La syntaxe relative aux noms des champs consiste à écrire le nom de la table, suivi d'un point et du nom du champ. Cette façon de procéder s'appelle la qualification. Dans le cas présent, cette qualification est redondante, et nous pouvons très bien écrire : SELECT NomStation, Capacité, Lieu La politique la plus raisonnable consiste à qualifier les champs chaque fois qu'une ambiguïté existe (même nom de champ dans deux tables différentes, lors d'une requête multi-table), et de ne pas les qualifier dans le cas contraire. Récupérons la requête précédente dans l'interface graphique et faisons en sorte qu'elle crée une table appelée "Essai", puis basculons en mode SQL. Nous obtenons : 1. SELECT NomStation, Capacité, Lieu INTO ESSAI 2. FROM STATION ; Dans Access, cette syntaxe fonctionne à condition que la table "Essai" préexiste, et contienne au moins les champs « NomStation », « Capacité » et « Lieu » avec les mêmes propriétés que dans la table STATION. Access effectue alors une requête ajout des trois premières colonnes de la table STATION à la table ESSAI. Il s’agit là de l’opération de Projection vu dans le cadre de l’algèbre relationnelle. Tri Nous pouvons demander que le résultat de la requête soit trié sur un ou plusieurs champs. Récupérons la requête précédente dans l'interface graphique, faisons en sorte que le résultat soit trié sur le nom de la station d'abord, sur le lieu ensuite, et basculons en mode SQL. Nous obtenons : 1. SELECT NomStation, Capacité, Lieu INTO ESSAI 2. FROM STATION 3. ORDER BY NomStation, Lieu ; Nous voyons que le tri (dans l'ordre croissant) s'obtient grâce à la clause ORDER BY, suivi des noms des champs. Le tri multiple est effectué dans l'ordre d'énumération des champs. Le tri d'un champ dans l'ordre décroissant s'obtient en faisant suivre le nom de ce champ par l'opérateur DESC. L'exemple suivant effectue un tri croissant sur les noms, suivi d'un tri décroissant sur les prénoms : 1. SELECT NomStation, Capacité, Lieu INTO ESSAI 2. FROM STATION 3. ORDER BY NomStation, Lieu DESC; La requête simple peut créer des doublons, et il est possible de remédier de façon simple à cette situation en jouant sur les propriétés de la requête. Créons dans l'interface graphique une requête de sélection simple qui concerne le seul champ « NomStation » de la table « Activités ». Que constatez-vous ? L'élimination des doublons s'obtient à l'aide de l'opérateur DISTINCT placé juste après la clause SELECT en suivant la syntaxe SELECT DISTINCT (NomStation) Une syntaxe plus ancienne est également comprise par Access, mais elle ne semble plus guère utilisée Pour éviter de créer des doublons sur deux champs, la commande SQL s'écrit : SELECT DISTINCT Champ1, Champ2 Requête emboîtée Il est possible de créer dans Access une requête à partir du résultat d'une autre requête, à condition que cette dernière ne crée pas de table. En mode SQL, la commande s'écrit : 1. SELECT Requête1.Nom 2. FROM Requête1 ; On ne peut pas rêver plus simple pour emboîter deux requêtes ! Cette belle simplicité ne se retrouve pas en SQL pur et dur, où l'emboîtement de deux requêtes est d'une écriture plutôt complexe. Que l'on en juge : • Si la première requête (encore appelée sous-requête, ou sous-interrogation) ramène une valeur numérique unique (résultat d'une opération du type comptage, sommation, calcul de moyenne, etc.), on utilise les opérateurs arithmétiques usuels : =, <, >, >=, <= et <> ; • Si la première requête ramène une seule ligne, on utilise les opérateurs IN, ALL, ou ANY suivant les cas ; • Si la première requête est susceptible de ramener plusieurs lignes, on utilise EXISTS ou NON EXISTS Restriction Outre l’opération de projection, on utilise également Access pour effectuer la restriction (selon une qualification) ou encore la jointure. La restriction est faite en se servant de l’opération WHERE pour fixer les conditions recherchées. Par exemple, on veut déterminer la liste (nom et prénom des clients dont on ignore le prénom). 1. SELECT nom, prenom 2. FROM Client 3. WHERE prenom IS NULL; Dans l’exemple précédent, identifiez les différentes opérations de l’algèbre opérationnelle vues en cours. Jointure Et maintenant, la jointure qui est obtenu en faisant un peu le produit cartésien de deux tables ou plus, simplement en indiquant les différentes table dans la clause « FROM » PrepaVogt-ESSCA Management & Finances 2 Année Académique : 2018-2019 Par Etienne KOUOKAM 4/6 Version du 9 octobre 2018 Exemple, on veut avoir la liste (nom et prénom) des clients ayant séjourné à Santalba. Alors, on a dans SQL 1 : 1. SELECT nom, prenom 2. FROM CLIENT, SEJOUR 3. WHERE station = 'Santalba' 4. AND id = idClient; Essayez d’expliquer très clairement la requête précédente en identifiant les opérations de l’algèbre relationnelle que vous y reconnaissez. Le SGBD Access accepte cette syntaxe mais, si nous revenons dans l'interface graphique, nous constatons que cette dernière a changé : la relation entre les deux tables a disparu ! Elle est remplacée par la condition d'égalité de contenu entre les champs id et idClient des deux tables (condition exprimée en utilisant la syntaxe du SQL). C’est une façon élémentaire, mais parfaitement exacte, de créer une relation entre deux tables. On notera que la relation normale entre les deux tables n'est pas supprimée, mais simplement éliminée de la fenêtre de création uploads/Voyage/atelier3-requetes-sql.pdf