TP DW - Sujet TP 1

Entrepôts de données TP N°1 M1 IO
IUP IO – M1 1/7
Démarrage : Télécharger la base de données TEDECA à l’adresse suivante :
http://www.irit.fr/~Gilles.Hubert/supports/TEDECA.mdb.
La base de données est constituée des tables et 'relations' suivantes :
A) Révisions concernant le langage SQL
A l'aide du langage SQL, créer les requêtes de sélection répondant aux questions suivantes :
Q1. Obtenir les Clients qui n’ont acheté aucun matériel de la famille "Caisse PC".
Q2. Obtenir les matériels achetés par les clients ayant acheté pour plus de 6000 euros en mars 2008.
Q3. Obtenir la liste des agences de la zone en tête des ventes (en termes de montant total des ventes)
en février 2008.
B) Approfondissement concernant le langage SQL
Le langage SQL, permet de définir/modifier/supprimer les éléments d’une base de données notamment les
tables.
 Création de table par requête de définition
La création d’une table à l’aide du langage SQL s’effectue par l’instruction CREATE TABLE.
Exemple : CREATE TABLE MATABLE1
(MONATTRIBUT11 CHAR(20),
MONATTRIBUT12 LONG,
CONSTRAINT clé_primaire_matable1 PRIMARY KEY(MONATTRIBUT1));
Les noms des types de données usuels en Access à utiliser en SQL dans Access sont les
suivants :
- Booléen : BIT
- Nombre entier : SHORT ou SMALLINT (entier), LONG ou INTEGER (entier long), BYTE
(octet)
- Nombre réel : SINGLE (réel simple), DOUBLE ou NUMERIC (réel double)
- Monétaire : CURRENCY, MONEY
- Date/Heure : DATE, TIME, DATETIME ;
- Texte : VARCHAR (255 caractères), CHAR(n) ou TEXT(n) (n caractères), LONGTEXT
(mémo, 32K max.) ;
- Compteur : COUNTER (NuméroAuto).

Entrepôts de données TP N°1 M1 IO
IUP IO – M1 2/7
Rappel : La création d’une table implique obligatoirement la désignation d’une clé primaire
(PRIMARY KEY).
La création des relations entre tables s’effectue au travers de l’ajout et la désignation d’attribut
clé étrangère (FOREIGN KEY).
Exemple : CREATE TABLE MATABLE2
(MONATTRIBUT21 LONG,
MONATTRIBUT22 DATE,
MONATTRIBUT23 CHAR(20),
CONSTRAINT clé_primaire_matable2 PRIMARY KEY(MONATTRIBUT21),
CONSTRAINT clé_étrangère_matable2 FOREIGN KEY(MONATTRIBUT23)
REFERENCES MATABLE1 (MONATTRIBUT11));
Q4. Créer à l’aide du langage SQL la table Contrat avec les caractéristiques suivantes : une
référence de type texte de 10 caractères qui sera la clé primaire, et une date de début. La table
Contrat aura une clé étrangère vers la table Client et l’attribut NumClient.
Le langage SQL, permet également d’ajouter/modifier/supprimer des enregistrements dans une base de
données.
 Ajout de données dans une table par requête
L’ajout d’un enregistrement à l’aide du langage SQL s’effectue à l’aide de l’instruction INSERT.
Exemple : INSERT INTO MATABLE2 VALUES (1, #01/24/2009#, "toto");
Q5. Ajouter à l’aide du langage SQL dans la table Contrat les enregistrements suivants : le contrat
A000000001 qui a débuté le 04/06/2009 signé par le client 1 et le contrat A000000002 qui a
débuté le 21/07/2009 signé par le client 4.
 Mise à jour de données dans une table par requête
La mise à jour d’un enregistrement à l’aide du langage SQL s’effectue à l’aide de l’instruction
UPDATE.
Exemple : UPDATE MATABLE2 SET MONATTRIBUT22 = #01/20/2009# WHERE
MONATTRIBUT23 = "toto";
Q6. Mettre à jour à l’aide du langage SQL, le contrat A000000002 en modifiant la date de début au
21/08/2009.
 Suppression de données dans une table par requête
La mise à jour d’un enregistrement à l’aide du langage SQL s’effectue à l’aide de l’instruction
DELETE.
Exemple : DELETE FROM MATABLE2 WHERE MONATTRIBUT23 = "toto";
Remarque : Sans clause WHERE l’instruction DELETE supprime tous les enregistrements de
la table
Q7. Supprimer à l’aide du langage SQL les contrats du mois de juin 2009.
 Ajouter des données résultant d’une requête dans une table par requête
L’ajout d’enregistrement issus d’une requête SELECT s’effectue en combinant INSERT et
SELECT.
Exemple : INSERT INTO MATABLE2 (MONATTRIBUT21) SELECT MONATTRIBUT12
FROM MATABLE1;
Remarque : Sans clause WHERE, l’instruction INSERT ajoute autant d’enregistrements dans la
table MATABLE2 que d’enregistrements de la table MATABLE1

Entrepôts de données TP N°1 M1 IO
IUP IO – M1 3/7
Q8. Ajouter à l’aide du langage SQL dans la table Contrat un contrat pour chaque client à partir du
numéro 10 avec comme référence A0000000 suivi du numéro de client, et comme date de début
de contrat la date de début présente dans la table Client.
C) Approfondissement concernant le langage graphique ACCESS
Le langage graphique Access, permet également d’ajouter/modifier/supprimer des enregistrements dans
une base de données.
 Mise à jour de données dans une table par requête
La mise à jour d’enregistrements à l’aide du langage graphique ACCESS s’effectue :
- en créant une requête de sélection de manière habituelle (Mode création) pour obtenir
les enregistrements concernés par la mise à jour
- puis en transformant la requête en requête de mise à jour (Menu Requêtes/Requête
Mise à jour …)
- et en indiquant au niveau de la requête les modifications à réaliser pour les champs
concernées.
Q9. Mettre à jour à l’aide du langage graphique Access la date du contrat du client MegaMarket à la
date du 05/05/2008.
 Suppression de données dans une table par requête
La mise à jour d’enregistrements à l’aide du langage graphique ACCESS s’effectue :
- en créant une requête de sélection de manière habituelle (Mode création) pour obtenir
les enregistrements concernés par la suppression
- puis en transformant la requête en requête de suppression (Menu Requêtes/Requête
Suppression …)
Q10. Supprimer à l’aide du langage graphique Access le contrat A000000015.
 Ajouter des données résultant d’une requête dans une table par requête
L’ajout d’un enregistrement à l’aide du langage graphique ACCESS s’effectue :
- en créant une requête de sélection de manière habituelle (Mode création) pour obtenir
les enregistrements à ajouter
- puis en transformant la requête en requête d’ajout (Menu Requêtes/Requête Ajout …)
- et en indiquant la table de destination dans la boîte de dialogue qui s’ouvre
- et en indiquant au niveau de la requête les correspondances entre les champs.
Q11. Ajouter à l’aide du langage graphique Access les contrats pour les clients 5 et 6 avec des
références du type A00000000X avec X numéro du client et la date de début de client comme
date de début de contrat.
Attention : Les modifications apportées aux enregistrements par l’intermédiaire de requêtes
sont irréversibles.
D) Premiers pas vers la combinaison VBA et le langage de requête
Il est possible d’utiliser les requêtes pour transformer/calculer/extraire des données. Cela concerne aussi
bien aussi bien les requêtes définies à l'aide du langage graphique Access et les requêtes définies en SQL.
Pour cela, on peut utiliser de nombreuses fonctions pré-définies dans Access ou définir en VBA ces
propres fonctions de transformation/calcul/extraction.

Entrepôts de données TP N°1 M1 IO
IUP IO – M1 4/7
Fonctions VBA prédéfinies utiles (la disponibilité dépend de la version d’Access)
 Fonctions de chaînes de caractères
InStr(Chaîne, chaînecherchée) : Renvoie la position de la première occurrence d'une chaîne de caractères
(chaînecherchée) dans une autre chaîne (Chaîne) à partir du début (InStrRev cherche à partir de la fin).
LCase(Chaîne) : Renvoie une chaîne convertie en minuscules.
Left(Chaîne, Longueur) : Renvoie une chaîne contenant le nombre indiqué de caractères (Longueur) d'une
chaîne en partant de la de la gauche.
Len(Chaîne) Renvoie une valeur de type Long contenant le nombre de caractères d'une chaîne.
Replace(Chaîne, SousChaîneCherchée, SousChaîneRemplacement) : Renvoie une chaîne dans laquelle
une sous-chaîne (SousChaîneCherchée) spécifiée a été remplacée par une autre sous-chaîne
(SousChaîneRemplacement).
Right(Chaîne, Longueur) : Renvoie une chaîne contenant le nombre indiqué de caractères (Longueur)
d'une chaîne en partant de la droite.
Str(valeur) : Renvoie une chaîne représentant un nombre.
StrComp(Chaîne1, Chaîne2) : Renvoie une valeur (Integer) indiquant le résultat d'une comparaison de
chaînes.
Trim(Chaîne) : Renvoie une chaîne moins les espaces de gauche et de droite.
UCase(valeur) Renvoie une la chaîne indiquée convertie en majuscules.
…
 Fonctions de date :
Date() : Renvoie la date système actuelle.
DateAdd(niveau, valeur, valeurdate) : Renvoie une date/heure correspondant à la date à laquelle un
intervalle de temps (niveau) a été ajouté (ou retranché). Les valeurs possibles pour niveau sont "yyyy"
pour année, "q" pour trimestre, "m" pour mois, "y" pour jour de l'année, "d" pour jour, "w" pour numéro
de la semaine, "ww" pour semaine de l'année, "h" pour heure, "n" pour minute, ou "s" pour seconde.
DateDiff(niveau, valeurdate1, valeurdate2) : Renvoie une valeur (Long) indiquant l’écart suivant le
niveau temporelle entre deux dates données. Les valeurs possibles pour niveau sont les mêmes que pour
DateAdd.
DatePart(niveau, valeurdate) : Renvoie une valeur de contenant l'élément spécifié d'une date donnée. Les
valeurs possibles pour niveau sont les mêmes que pour DateAdd.
DateSerial(Année, Mois, Jour) : Renvoie une date correspondant à la combinaison des 3 valeur.
DateValue(Chaîne) : Renvoie une date correspondant à une chaîne de caractères.
Day(valeurdate) Renvoie une valeur de type Variant (Integer) indiquant un nombre entier compris entre 1
et 31, inclus, qui représente le jour du mois.
Hour(valeurheure) Renvoie une valeur de type Variant (Integer) indiquant un nombre entier compris entre
0 et 23 inclus, qui représente l'heure du jour.
Minute(valeurheure) Renvoie une valeur de type Variant (Integer) indiquant un nombre entier compris
entre 0 et 59, inclus, qui représente la minute de l'heure en cours.
Month(valeurdate) Renvoie le numéro de mois (1 à 12).

Entrepôts de données TP N°1 M1 IO
IUP IO – M1 5/7
Now() Renvoie une valeur de type Variant (Date) indiquant la date et l'heure en cours fournies par la date
et l'heure système de votre ordinateur.
Time() Renvoie une valeur de type Variant (Date) indiquant l'heure système en cours.
Year(valeurdate) Renvoie une valeur de type Variant (Integer) contenant un nombre entier qui représente
l'année.
…
 Fonctions mathématiques :
Abs(valeur) : Renvoie la valeur absolue d'un nombre.
Fix(valeur) : Renvoie la partie entière d'un nombre.
Int(valeur) : Renvoie la partie entière d'un nombre.
Round(valeur, nbdécimales) : Renvoie un nombre arrondi à un nombre spécifié de positions décimales.
Sgn(valeur) : Renvoie une valeur de type Variant (Integer) indiquant le signe d'un nombre.
Sqr(valeur) : Renvoie une valeur de type Double indiquant la racine carrée d'un nombre.
Val(Chaîne) : Renvoie le nombre contenu dans une chaîne de caractère sous la forme d'une valeur
numérique d'un type approprié.
…
 Fonctions de formatage :
Format(Chaîne, expressionformat) : Renvoie une chaîne formatée en fonction des instructions contenues
dans l'expression de mise en forme.
Mises en formes possibles :
 Pour les dates et heures
Formes prédéfinies parmi "General Date" : Date, générale, "Long Date" : Date, complet, "Medium Date" :
Date, réduit, "Short Date" : Date, abrégé, "Long Time" : Heure, complet, "Medium Time" : Heure, réduit,
ou "Short Time" : Heure, abrégé
Combinaisons de lettres "h (heure), m (minute), s (seconde), d (jour), m (mois), ou y (année), exemples :
Format(#17:04:23#, "h:m:s") renvoie "17:4:23".
Format(#17:04:23#, "hh:mm:ss AM/PM") renvoie "05:04:23 PM".
Format(#9/20/2008#, "dddd d mmm yyyy") renvoie "samedi 20 septembre 2008"
 Pour les nombres
Formes prédéfinies parmi "General Number" (nombre sans séparateur de milliers), "Currency" (séparateur
de milliers, 2 décimales et sigles monétaire), "Fixed" (2 décimales), "Standard" (séparateur de milliers, 2
décimales), "Percent" (pourcentage), "Scientific" (notation scientifique), "Yes/No" (Oui ou Non),
"True/False" (Vrai ou Faux), ou "On/Off".
Combinaisons de caractères '#' (chiffre affiché si présent), '0' (chiffre affiché si présent ou 0), '.'
(séparateur décimales), ',' (séparateur milliers), exemples :
Format (130.6, "#,##0.00") renvoie '130,60'
Format (130.6, "0,000.00") renvoie '0 130,60'