SQL/Microsoft Jet

Par M. Qbadou

LE LANGAGE SQL
I. Introduction
1. Qu'est-ce que SQL ?
SQL est un langage de manipulation de bases de donnes mis au point dans les annes 70 par IBM. Il permet
trois types de manipulations sur les bases de donnes :
La maintenance des tables : cration, suppression, modification de la structure des tables grce aux
instructions DDL (Data Definition Language).
Les manipulations des bases de donnes : Slection, modification, ajout et suppression d'enregistrements
grce aux instructions DML (Data Manipulation Language).
La gestion des droits d'accs aux tables : Contrle des donnes : droits d'accs, validation des
modifications grce aux instructions DCL (Data Control Language).

2. Intrts du langage SQL

L'intrt de SQL est que c'est un langage de manipulation de bases de donnes standard, On peut l'utiliser sur
n'importe quelle base de donnes. Ainsi, avec SQL, on peut grer une base de donnes Access, mais aussi
Paradox, dBase, SQL Server, Oracle ou Informix .

II. La maintenance de la structure dune base de donnes

La premire srie de commandes permettant la maintenance de la structure dune base de donnes, est compose
des instructions suivantes :

ALTER TABLE

CREATE INDEX

CREATE TABLE

DROP

1. Instruction CREATE TABLE

La commande CREATE TABLE permet de crer une table dans la base de donnes courante. Sa syntaxe est la
suivante :

CREATE TABLE

Nomtable(

champ1 type[(taille)] [NOT NULL] [CONSTRAINT IndexMonochamp ...],

champ2 type[(taille)] [NOT NULL] [CONSTRAINT IndexMonochamp ...],
[...]
[,CONSTRAINT IndexMultichamp... ]]);

Page 1

SQL/Microsoft Jet

Par M. Qbadou

Clauses CONSTRAINT
Une contrainte permet de dfinir un index ou dtablir une relation avec une autre table. Il existe deux types de
clauses CONSTRAINT :

Contrainte MonoChamp portant sur un seul champ :

CONSTRAINT

NomIndex {PRIMARY KEY | UNIQUE | NOT NULL |

REFERENCES tableExterne

[(ChampExterne)]}

Contrainte MultiiChamps portant sur plusieurs champs.

CONSTRAINT

NomIndex

{PRIMARY KEY (ChampPrimaire1[, ChampPrimaire2 [, ...]]) |

UNIQUE (ChampUnique1[, ChampUnique2 [, ...]]) |
NOT NULL (ChampNotnull1[, ChampNotnull2 [, ...]]) |
FOREIGN KEY (ChampRef1[, ChampRef2 [, ...]]) REFERENCES
tableExterne [(ChampExterne1 [,ChampExterne2 [, ...]])]}

Paramtre

Signification

Champ

Nom du champ

Type

Type de donnes, (voir tableau des types)

Proprits

NOT NULL : autorise ou non que le champ puisse tre vide (champ obligatoire).

UNIQUE : deux enregistrements ne pourront avoir la mme valeur dans ce champ.

PRIMARY KEY : indique que ce champ est la clef primaire

REFERENCES : indique le nom de la table trangre ainsi que le ou les champs externes

FOREIGN KEY : indique le ou les champs de rfrence de la table dfinir

Exemple : Crer la nouvelle table "table_Etudiants" contenant deux champs : le champ Matricule entier qui doit
toujours la cl primaire et le champ Nom contenant une chane obligatoire de 15 caractres :
CREATE TABLE Etudiants (Matricule long CONSTRAINT PKEtudiants Primary
Key, Nom text(15)Not Null);

2. La commande ALTER TABLE

La commande ALTER TABLE permet de modifier la structure d'une table, sa syntaxe est la suivante :

ALTER TABLE

Nomtable {ADD {COLUMN champ type[(taille)] [NOT NULL]

[CONSTRAINT IndexMonochamp ...] |CONSTRAINT IndexMultichamp ...} |

DROP {COLUMN champ | CONSTRAINT NomIndex}}

Page 2

SQL/Microsoft Jet

Par M. Qbadou

ALTER TABLE permet deux types dactions :

ADD pour ajouter un champ ou un index une table existante
DROP Supprime un champ ou un index d'une table existante
Aprs l'action, on indique, entre parenthses, les spcifications du champ ou de lindex de la mme faon que
pour la commande CREATE TABLE. On ne peut faire qu'une action la fois (add ou drop ).
Exemple :
L'exemple suivant ajoute la table Employs une donne Salaire avec un champ de type Montaire :
ALTER TABLE Employs ADD COLUMN Salaire currency ;

L'exemple suivant retire de la table Employs le champ Salaire :

ALTER TABLE Employs DROP COLUMN Salaire ;

L'exemple suivant retire la cl externe existante de la table Commandes :

ALTER TABLE Commandes DROP CONSTRAINT EmploysCommandes ;

L'exemple suivant remet la cl externe dans la table Commandes :

ALTER

TABLE

Commandes

ADD

CONSTRAINT

EmploysCommandes

FOREIGN

KEY

(N employ) REFERENCES Employs (N employ) ;

3. La commande CREATE INDEX

La commande CREATE INDEX permet de crer un index sur une table, sa syntaxe est :

CREATE

[UNIQUE]

INDEX

NomIndex

ON NomTable (Champ1 [ASC|DESC][, Champ2 [ASC|DESC], ...])

[WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}]

Remarques

Pour viter les doublons dans le ou les champs indexs des enregistrements, on utilise UNIQUE.

Dans la clause WITH facultative, on peut forcer les rgles de validation des donnes. Ainsi on peut :
Interdire les entres Null dans le ou les champs indexs des nouveaux enregistrements en utilisant
l'option DISALLOW NULL.
Empcher que les enregistrements contenant des valeurs Null dans le ou les champs indexs soient
inclus dans l'index en utilisant l'option IGNORE NULL.
Dfinir le ou les champs indexs comme cl primaire en utilisant le mot rserv PRIMARY. Cela
implique alors que la cl doit tre unique, ainsi on peut omettre le mot rserv UNIQUE.

Exemple d'instruction CREATE INDEX

Cration d'un index comportant les champs Tl domicile et Extension dans la table Employs :

Page 3

SQL/Microsoft Jet

Par M. Qbadou

CREATE INDEX NouvIndex ON Employs ([Tl domicile], Extension) ;

Cration dans la table Clients d'un index comportant le champ N client. Deux enregistrements ne peuvent avoir
les mmes donnes, et la valeur Nulle est interdite.
CREATE UNIQUE INDEX Numcli ON Clients ([N client]) WITH DISALLOW NULL;

4. Instruction DROP
Permet de supprimer une table dune base de donnes ou de supprimer un index dune table :
DROP {TABLE NomTable | INDEX NomIndex ON NomTable}

Exemple d'instruction DROP (Microsoft Access)

L'exemple suivant supprime l'index MonIndex de la table Employs :
DROP INDEX MonIndex ON Employs ;

L'exemple suivant supprime la table Stagiaires de la base de donnes :

DROP TABLE Stagiaires ;

5. Application
Enonc :
On donne le MCd Suivant :

1. Implanter cet MCD dans Power AMC Designor

2. Vrifier le Modle
3. Donner le Modle Relationnel
4. Donner le Code SQL pour implanter la BD sous Access
5. Raliser La base de donnes Access
Page 4

SQL/Microsoft Jet

Par M. Qbadou

Rponse
3. Le Modle Relationnel

4. Le Code SQL pour implanter la BD sous Access

# Table : CARGOS
#==============================================================
create table CARGOS(
N_CARGO

LONG

NOMCARGO

not null,

CHAR(30)

PROPRIETAIRE

not null,

CHAR(20)

not null,

constraint PK_CARGOS primary key (N_CARGO));

# Table : CLIENTS
#==============================================================
create table CLIENTS(
N_CLIENT
NOMCLIENT
N_TELCLT

LONG

not null,

CHAR(15)

not null,

CHAR(12),

constraint PK_CLIENTS primary key (N_CLIENT));

# Table : FOURNISSEURS
#==============================================================
create table FOURNISSEURS(
N_FOUR

LONG

not null,
Page 5

SQL/Microsoft Jet

NOMFOUR

CHAR(15)

N_TELF

Par M. Qbadou

not null,

CHAR(12),

constraint PK_FOURNISSEURS primary key (N_FOUR));

# Table : MARCHANDISES
#==============================================================
create table MARCHANDISES(
N_MARCHANDISE

LONG

N_TRANSPORT
N_FOUR

LONG

not null,

LONG

N_CLIENT

not null,

LONG

DESCRIPTION

not null,

CHAR(40)

VOLUME
POIDS

not null,

not null,

SINGLE
SINGLE

not null,
not null,

constraint PK_MARCHANDISES primary key (N_MARCHANDISE));

# Table : SITUER
#==============================================================
create table SITUER(
CODEVILLE

CHAR(5)

VIL_CODEVILLE
DISTANCE

not null,

CHAR(5)

not null,

LONG,

constraint PK_SITUER primary key (CODEVILLE, VIL_CODEVILLE));

# Table : TRANSPORTS
#==============================================================
create table TRANSPORTS(
N_TRANSPORT
N_CARGO

LONG
LONG

CODEVILLE

not null,

CHAR(5)

VIL_CODEVILLE
HEUREDEPART

not null,

CHAR(5)

DATETRANSPORT
PRIXU

not null,

not null,

DATE,
TIME,

CURRENCY,

constraint PK_TRANSPORTS primary key (N_TRANSPORT));

# Table : VILLES
#==============================================================
create table VILLES(
CODEVILLE

CHAR(5)

not null,

NOMVILLE

CHAR(20)

not null,

PAYS

CHAR(20)

not null,
Page 6

SQL/Microsoft Jet

Par M. Qbadou

constraint PK_VILLES primary key (CODEVILLE));

alter table MARCHANDISES
add constraint FK_MARCHAND_APPARTENI_CLIENTS foreign key (N_CLIENT)
references CLIENTS (N_CLIENT);
alter table TRANSPORTS
add constraint FK_TRANSPOR_ARRIVEE_VILLES foreign key (CODEVILLE)
references VILLES (CODEVILLE);
alter table TRANSPORTS
add constraint FK_TRANSPOR_ASSSURER_CARGOS foreign key (N_CARGO)
references CARGOS (N_CARGO);
alter table TRANSPORTS
add constraint FK_TRANSPOR_DEPART_VILLES foreign key (VIL_CODEVILLE)
references VILLES (CODEVILLE);
alter table MARCHANDISES
add constraint FK_MARCHAND_FOURNIR_FOURNISS foreign key (N_FOUR)
references FOURNISSEURS (N_FOUR);
alter table SITUER
add constraint FK_SITUER_SITUER_VILLES foreign key (CODEVILLE)
references VILLES (CODEVILLE);
alter table SITUER
add constraint FK_SITUER_SITUER2_VILLES foreign key (VIL_CODEVILLE)
references VILLES (CODEVILLE);
alter table MARCHANDISES
add constraint FK_MARCHAND_TRANSPORT_TRANSPOR foreign key (N_TRANSPORT)
references TRANSPORTS (N_TRANSPORT);

III. Les manipulations des bases de donnes

Une fois les tables cres, on peut commencer y insrer des donnes, les mettre jour, les supprimer. Toutes
ces oprations sont des oprations de manipulation des bases de donnes. Pour effectuer ces manipulations, SQL
dispose de 4 instructions :

Page 7

SQL/Microsoft Jet

SELECT

UPDATE

INSERT

DELETE

Par M. Qbadou

1. La commande SELECT
1.1. Prsentation et Syntaxe
La commande SELECT est la commande la plus complexe de SQL. Cette commande va servir faire des
requtes pour rcuprer des donnes dans les tables. Sa syntaxe gnrale est :

SELECT

[all|top

n|disctinct|Distinct

row]

{*|Nomtable.*|[Nomtable.]champ1 [AS alias1]

[, [Nomtable.]champ2 [AS alias2] [,...]]}
FROM ExpressionTable [, ...] [IN Baseexterne]
[WHERE Critre]
[GROUP BY champ1 [, champ2,...]
[HAVING Critre]]
[ORDER BY champ1 [Asc|Desc][, champ2 [Asc|Desc], ...]];

Exemples
Pour slectionner les champs "prnom" et "nom" de tous les enregistrements de la table Clients :
SELECT prnm,nom FROM Clients;

Si on veut rcuprer tous les champs des enregistrements slectionns, la syntaxe est la suivante :
SELECT * FROM Clients;

On peut indiquer des critres de slection avec la clause WHERE. Par exemple, pour slectionner tous les
Clients de la table "Clients" dont le code postal est 75000 :
SELECT * FROM Clients WHERE [code postal]=75000;

1.2. Les oprateurs de condition

On peut utiliser les oprateurs suivants dans les conditions :
Oprateur

Signification

Oprateur

Signification

Page 8

SQL/Microsoft Jet

Egal

>

Suprieur

<>

Diffrent

<=

Infrieur ou gal

<

Infrieur

>=

Suprieur ou gal

Par M. Qbadou

Pour slectionner tous les articles dont le prix est suprieur 100 F :
SELECT * FROM Produits WHERE prix_unitaire > 100;

1.3. Oprateurs logiques

Il est possible de combiner plusieurs conditions avec des oprateurs logiques (AND, OR, NOT) :
L'oprateur AND runit deux ou plusieurs conditions et slectionne un enregistrement seulement si cet
enregistrement satisfait toutes les conditions listes. Par exemple, pour slectionner tous les clients nomms
'Dafir' et qui habitent Casa :
SELECT * FROM Clients WHERE nom = 'Dafir' AND ville = 'Casa';

L'oprateur OR runit deux conditions mais slectionne un enregistrement si UNE des conditions listes est
satisfaite. Par exemple, pour slectionner tous les clients nomms 'Dafir' ou 'Dafik' :
SELECT * FROM Clients WHERE nom = 'Dafir' OR nom = 'Dafik';

AND et OR peuvent tre combins : Par exemple pour slectionner les clients nomms "Dafir" qui habitent soit
Rabat, soit Casa :
SELECT * FROM Clients WHERE nom = 'Dafir' AND (ville = 'Rabat' OR
ville ='Casa');

L'oprateur AND a une plus grande priorit que l'oprateur OR. Pour cette raison les conditions lies par OR
sont places entre parenthses.

1.4. Clauses IN et BETWEEN

Pour slectionner des enregistrements dont la valeur d'un champ peut tre comprise dans une liste ou entre deux
valeurs, on utilise les clauses IN et BETWEEN.
Par exemple : Pour slectionner les clients vivant Rabat ou Casa :
SELECT * FROM Clients WHERE ville IN ('Rabat', 'Casa');

Ou pour slectionner les produits dont le prix est compris entre 100 et 1000 F :
SELECT * FROM Produits WHERE prix_unitaire BETWEEN 100 AND 1000;

Pour slectionner les produits dont le prix n'est pas dans cet intervalle :
SELECT * FROM Produits WHERE prix_unitaire NOT BETWEEN 100 AND 1000;

Page 9

SQL/Microsoft Jet

Par M. Qbadou

De la mme faon, NOT IN slectionne les enregistrements exclus de la liste spcifie aprs IN.

1.5. La clause LIKE

La clause LIKE permet de faire des recherches approximatives sur le contenu d'un champ. Par exemple, pour
slectionner les clients dont le nom commence par la lettre D :
SELECT * FROM Clients WHERE nom LIKE 'D*';

Le symbole * remplace un ensemble de caractres, pour reprsenter tous les noms commenant par S, on
utilisera 'S*', tous ceux se terminant par S, on utilisera '*S', et tous ceux comportant la lettre S : '*S*'. Le
symbole ? ne remplace qu'un seul caractre. Si on a deux clients nomms Dafir et Dafik, on utilisera 'Dafi?'.

1.6. Les jointures

La jointure permet de slectionner des informations dans deux tables ou plus grce aux relations existant entre
ces tables. On distingue plusieurs types de jointures :
Produit cartsien

Jointure externe droite

Equijointure

Thta jointure

Jointure externe gauche

Auto jointure

1.6.1 Produit cartsien

Il permet de crer une table rsultat partir deux tables ou plus comme suite : chaque enregistrement de la 1re
table est concatn tous les enregistrements de lautre table.
SELECT listechamps FROM Table1,Table2 ;

1.6.2 Equijointure
Appele aussi jointure naturelle, elle permet de raliser une liaison logique entre deux tables ou plus avec un
pivot de jointure (condition de jointure) qui exprime lgalit de valeur des cls des tables joindre (cl primaire
et cl trangre)
SELECT listechamps FROM Table1 INNER JOIN Table2 ON Table1.ClePri =
Table2.ClEtr ;

Ou bien :
SELECT listechamps FROM Table1 ,Table2
WHERE Table1.ClePri = Table2.ClEtr ;

Par exemple pour rcuprer le nom et le prnom du client ayant pass la commande n1 :

Page 10

SQL/Microsoft Jet
SELECT

nom,

prnom

FROM

Clients

Par M. Qbadou
INNER

JOIN

Commandes

ON

Commandes.num_client = Client.num_client
WHERE num_commande = 1;

Ou bien
SELECT nom, prnom FROM Clients , Commandes
WHERE Commande.num_client = Client.num_client AND num_commande = 1;

La clause WHERE indique que le numro de commande doit tre gal 1 et que la jointure doit se faire sur le
numro de client.

1.6.3. Jointure externe

Dans une quijointure, les enregistrements qui ne vrifient pas la condition de jointure ne sont pas affichs. Une
jointure externe, appele aussi demi-jointure, favorise une table appele table dominant, par rapport lautre,
appele table subordonne. Les enregistrements de la table dominante sont affichs mme si la condition de
jointure nest pas vrifie.
Lorsque la table dominante est celle dont la cl primaire entre dans la jointure, la jointure est dite externe gauche
si non elle est dite externe droite.
Jointure externe gauche :
SELECT listechamps FROM Table1 LEFT JOIN Table2 ON Table1.ClePri =
Table2.ClEtr ;

Jointure externe Droite :

SELECT listechamps FROM Table1 RIGHT JOIN Table2 ON Table1.ClePri =
Table2.ClEtr ;

1.6.4 Thta jointure

Une thta jointure est une jointure dont lexpression du pivot utilise des oprateurs autres que lgalit, tel que <,
<=, >, >=, <>
SELECT

listechamps

FROM

Table1

INNER

JOIN

Table2

ON

Table1.ClePri

{<|<=|>|>=|<>} Table2.ClEtr ;

1.6.5 Auto jointure

Une auto jointure est une jointure dune table elle-mme. SQL construit la requte selon les mmes rgles que
prcdemment en considrant plusieurs copies de la mme table. Il est alors obligatoire dutiliser des alias pour
distinguer les diffrentes copies :
Page 11

SQL/Microsoft Jet
SELECT

listechamps

FROM

Table

Par M. Qbadou

INNER

JOIN

Table

as

AliasTable

ON

Table.ClePri = AliasTable.ClEtr ;

Exemple :
Pour afficher pour chaque employ lemploy chef qui rend compte :
SELECT Employs.[N employ], Employs.Nom, Employs_1.[N employ],
Employs_1.Nom
FROM

Employs

INNER

JOIN

Employs

AS

Employs_1

ON

Employs.[Rend

compte ] = Employs_1.[N employ];

1.7. Supprimer les doubles avec DISTINCT

Supposons quon veut la liste des clients ayant pass au moins une commande sans avoir rpter les
informations sur les clients ayant pass plusieurs commande. Pour cela, il suffit de prciser SQL de supprimer
les doubles du rsultat de la slection en utilisant la clause DISTINCT et en faisant une jointure entre les tables
Clients et Commande :
SELECT DISTINCT Clients.num_client,nom, prnom FROM Clients, Commande
WHERE Commande.num_client = Client.num_client AND num_commande = 1;

On peut mme rendre le rsultat de la slection plus agrable la lecture en utilisant la clause ORDER BY :
La slection renvoye va tre classe alphabtiquement d'abord sur le nom, puis sur le prnom.
SELECT DISTINCTROW nom, prnom FROM Clients, Commande
WHERE Commande.num_client = Client.num_client AND num_commande = 1
ORDER BY nom, prnom;

1.8. Les fonctions d'ensemble

SQL a cinq fonctions importantes : SUM, AVG, MAX, MIN et COUNT. On les appelle fonctions d'ensemble
parce qu'elles rsument le rsultat d'une requte plutt que de renvoyer une liste d'enregistrements.
Fonction

Signification

SUM ()

Donne le total d'un champ de tous les enregistrements satisfaisant la condition de la requte.

AVG ()

donne la moyenne d'un champ de tous les enregistrements satisfaisant la condition de la requte

MAX ()

donne la valeur maximale d'un champ de tous les enregistrements satisfaisant la condition de la
requte

MIN ()

Donne la valeur minimale d'un champ de tous les enregistrements satisfaisant la condition de la
requte.

Page 12

SQL/Microsoft Jet
COUNT(*)

Par M. Qbadou

Renvoie le nombre d'enregistrements satisfaisant la requte.

Exemples :
Pour retourner le prix le plus petit, le prix le plus lev et le prix moyen de la table Produits.
SELECT MIN(prix_unitaire),MAX(prix_unitaire),AVG(prix_unitaire)
FROM Produits ;

Pour retourner le nombre de produits dont le libell commence par la lettre 'P'.
SELECT COUNT (*) FROM Produits WHERE libelle LIKE 'P*';

1.9. La clause GROUP BY

Une des utilisations les plus courantes de la clause GROUP BY est son association avec une fonction d'ensemble
(COUNT, SUM, AVG, MIN, MAX, )
Par exemple, pour afficher le nombre de commandes effectues par les vendeurs :
SELECT num_vendeur,COUNT (*) FROM Commandes GROUP BY num_vendeur;

On peut ajouter une condition la requte, par exemple, la liste des vendeurs avec leur nombre de commandes
pour le mois de janvier et dont le nombre des commandes est suprieur ou gal 10. On utilisera pour cela les
clauses WHERE et HAVING.
SELECT num_vendeur, COUNT (*) FROM Commandes
WHERE month([date commande])=1
GROUP BY num_vendeur HAVING COUNT(*)>=10;

1.10. Les sous-requtes

Ventes

Num_acheteur
On peut imbriquer autant de requtes que l'on veut. La condition Num_produit
aprs la clause WHERE peut porter sur le rsultat d'une autre requte Date_Vente
Prix
(ou sous-requte). Supposons les tables suivantes :

Acheteurs
Num_acheteur
Nom
Prnom

La table Ventes contient, pour chaque acheteur, le produit qu'il a achet et le prix d'achat.
On cherche la liste des acheteurs ayant achet des articles chers. On considre qu'un article cher est un article
dont le prix est suprieur la moyenne du prix des produits achets + 100.
SELECT Num_acheteur FROM Vente
WHERE prix >( SELECT AVG (prix) + 100 FROM Vente);

Page 13

SQL/Microsoft Jet

Par M. Qbadou

On peut constater que la condition de la requte est base sur le rsultat d'une autre requte. Dans cet exemple,
chaque fois qu'un acheteur aura achet un article cher, son numro apparatra, pour viter cela, on utilise la
clause DISTINCT num_acheteur pour liminer les doubles :
SELECT DISTINCT Num_acheteur FROM Vente
WHERE prix >( SELECT AVG (prix) + 100 FROM Vente);

1.11. Les UNIONS

Pour obtenir lunion des rsultats de deux requtes dont les listes des champs se correspondent champs pmps, on
utilise la clause UNION.
SELECT from
UNION
SELECT from ;

Par exemple, supposons quon a une table clients_Rabat pour les clients habitant Rabat et une table clients_Casa
pour les clients habitant Casa. Pour obtenir les numros des clients des deux tables, on crit :
SELECT num_client FROM clients_Rabat
UNION
SELECT num_client FROM clients_Casa;

Remarque : SQL supprime automatiquement les doubles lorsque UNION est utilis..

TP Requtes SELECT
1. Ouvrir la BD Comptoir (c:\...\Msoffice\Office\Exemples\)
2. Ecrire (sous forme de code SQL) et excuter les requtes suivantes
a. Requtes monotable
i. Liste des clients qui habitent les villes de Paris, Lille Ou Nantes
Select * from clients where ville in (paris,lille,nantes) ;

Ou bien
Where ville =paris or ville =lille or ville =nantes;
Select Socit , adresse from clients where

ii. Liste des clients dont les nom de socit commencent par A , F ou O
Select * from clients where socit like A* or socit like F* or socit like O*;

Ou bien
Where socit like [AFO]*;

Page 14

SQL/Microsoft Jet

Par M. Qbadou

iii. Liste des produits dont le Prix unitaire nest pas dans lintervalle [500 700]
Select * from produits where [prix unitaire] not between 500 and 700;

Ou bien
Where [prix unitaire]<500 OR [prix unitaire]>700;

iv. Liste des commandes faites dans le mois 7 de lanne 1996

Select

from

commandes

where

month([date

cmmande])=7

and

year([date

commande])=1996 ;
Select * from commandes where [date commande] like */07/1996;

v. Liste des commandes ralises au cours du 2me semestre de lanne 1997

Select * from commandes where [date commande] between #07/01/1997# and #12/31/1997#

Ou bien
Where month([date commande]) in (7,8,9,10,11,12) and year([date commande]) =1997;

b. Requtes multi tables

i. Liste des produits fournis par le fournisseur "Exotic Liquids"
Select produits.*
From fournisseurs inner join produits on fournisseurs.[n fournisseur] = produits.[n
fournisseur]
Where Socit= Exotic Liquids ;

Ou bien
From fournisseurs, produits
Where fournisseurs.[n fournisseur] = produits.[n fournisseur]

and Socit= Exotic

Liquids ;

ii. Liste des employs qui rendent compte lemploy "Fuller"

Select Employs.*
from Employes inner join employs as employsChef on employs.[rend compte ] =
employsChef.[n employ]
Where employshef.nom=Fuller ;

iii. Liste des commandes assures par les employs qui rendent compte "Fuller"
iv. Liste des produits commands pour les commandes faites par le client "ALFKI" et calculer pour
chaque produit command le prix total en tenant compte de la remise
c. Requtes multi tables avec groupement
i. Nombre de commande par clients
Select clients.[code client], socit , count(*) as NbreCommandes
From Clients inner join Commandes on clients.[code client]=commandes.[code client]

Page 15

SQL/Microsoft Jet

Par M. Qbadou

Group by clients.[code client], socit

ii. Liste des clients dont le nombre de commande est suprieur ou gal 5. Le rsultat doit tre tri par
ordre dcroissant du nombre de commande
Select clients.[code client], socit , count(*) as NbreCommandes
From Clients inner join Commandes on clients.[code client]=commandes.[code client]
Group by clients.[code client], socit
Having count(*)>=5

iii. Liste des commandes avec le calcul du montant global pour chaque commande en tenant compte de
la remise
Select commandes.[n commande], sum([quantit]*[prix unitaire]*(1-[remise (%)])) as
MontantTotal
From commandes inner join [dtails commandes] on commandes.[n commande]= [dtails
commandes].[n commande]
Group by commandes.[n commande]

iv. Liste des produits commands 5 fois

Select produits.[rf produit],count(*) as NbreCommandes
From Produits inner join [dtails commandes] on

produits.[rf produit]= [dtails

commandes].[rf produit]
Group by produits.[rf produit]
Having count(*)=5

2. La commande INSERT INTO

2.1. Prsentation et syntaxe
La commande INSERT est utilise pour ajouter des enregistrements ou des parties d'enregistrements dans des
tables. Elle est utilise gnralement sous deux formes :
1re forme
INSERT

Remarque
Pour paramtrer la requte on peut utiliser des
paramtres placs entre crochets au des valeurs

INTO Nomtable (champ1,champ2,...)

VALUES ('valeur1','valeur2',...);

Exemple
insert into
fournisseurs(numfournisseur,socit,adresse,cp,ville)

constantes :
Insert
Into nomtable(champ1,champ2,)
Values([param1],[param2],) ;
Exemple
insert into
fournisseurs(numfournisseur,socit,adresse,c
p,ville,tlphone)
values([quel est num du fournisseur
?],[quel
Page
16
est le nom ?],[quelle est l'adresse
?],[CP?],[Ville ?],[Tl ?])

SQL/Microsoft Jet

Par M. Qbadou

values(F06,Thomson,'n 100 Hassan II,

20000,Casa)

Cette forme est utilise lorsqu'on veut insrer un seul enregistrement. On crera un nouvel enregistrement dont le
contenu du champ1 sera valeur1, le contenu du champ2 sera valeur2,etc...
Exemples

2me forme
INSERT
INTO Nomtable (champ1,champ2,...)
(requte_select);

Dans cette seconde forme, le rsultat de la requte va tre insr dans les champs indiqus de la table. Cette
mthode est utilise lorsque plusieurs enregistrements sont ajouts simultanment.
Dans les deux cas, les valeurs insres doivent correspondre au type de donnes du champ dans lequel l'insertion
va tre faite. Les chanes de caractres constantes doivent tre places entre apostrophes ( ' ).

2.2. Insrer tout ou une partie d'un enregistrement

Si des valeurs doivent tre insres dans tous les champs de l'enregistrement de la table, la liste des noms des
champs n'a pas besoin d'tre explicitement indique dans la commande. Les valeurs des champs insrer doivent
cependant apparatre dans le mme ordre que les noms des champs lors de la cration de la table.
La syntaxe est alors :
INSERT
INTO Nomtable
VALUES ('valeur1','valeur2','valeur3',...);

Par exemple, dans la table Client, pour insrer un nouveau client :

INSERT
INTO Clients
VALUES (100,'Alami','Farid','rue de la rsistance,75000,'Casa',
NULL);

Remarque : dans les champs pour lesquels les valeurs sont inconnues on insre la valeur NULL, condition que
la proprit du champ "NULL interdit" soit NON.
Page 17

SQL/Microsoft Jet

Par M. Qbadou

2.3. Insrer plusieurs enregistrements ou plusieurs parties d'enregistrements

On peut insrer simultanment plusieurs enregistrements ou parties d'enregistrements dans une table. Dans ce
cas, les donnes insres vont tre rcupres dans une ou plusieurs autres tables.
Par exemple, pour placer dans une table nomme "Clients_Rabat" les clients habitant Rabat :
Crer une table "Client_Rabat" compose des champs: le nom, le prnom et l'adresse.
excuter la commande :
INSERT
INTO Clients_Rabat(nom,prenom,adresse)
SELECT nom,prenom,adresse
FROM Clients
WHERE ville='Rabat ;

Application
On considre les trois dossiers suivants Tp1, Tp2 et Tp3. Chacun de ces dossiers contient trois fichiers bases de
donnes suivants : GestStockD,GestStockS et DB1
1. Depuis le dossier Tp1, ouvrez la base GstStockS et copier toutes ses donnes dans la base GestStockD.
On prcise que les champs des tables sources et destinations ont les mmes noms.
2. Depuis le dossier Tp2, Ouvrez la base GestStokD et copier y toutes les donnes de la base GestStockS.
On prcise que les champs des tables sources et destinations ont les mmes noms sauf pour les tables
Catgories
3. Dans le dossier Tp3, ouvrez la base DB1, et copier toutes les donnes de la base GestStockS dans la
base GestStockD. On prcise que les champs des tables sources et destinations ont les mmes noms.

Solution
1.
Insert Into Catgories IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Catgories;
Insert Into Socits IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Socits;
Insert Into Produits IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Produits;
Insert Into Entres IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Entres;
Insert Into Sorties IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Sorties;
Insert Into DtailsEntres IN 'C:\tp\tp1\geststockD.mdb' Select * FROM DtailsEntres;
Insert Into DtailsSorties IN 'C:\tp\tp1\geststockD.mdb' Select * FROM DtailsSorties;
Insert Into Rglements IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Rglements;

Page 18

SQL/Microsoft Jet

Par M. Qbadou

Insert Into Encaissements IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Encaissements;

2.
Insert Into Catgories Select * FROM Catgories 'C:\tp\tp2\geststockS.mdb';
Insert Into Socits Select * FROM Socits 'C:\tp\tp2\geststockS.mdb';
Insert Into Produits Select * FROM Produits 'C:\tp\tp2\geststockS.mdb';
Insert Into Entres Select * FROM Entres 'C:\tp\tp2\geststockS.mdb';
Insert Into Sorties Select * FROM Sorties 'C:\tp\tp2\geststockS.mdb';
Insert Into DtailsEntres Select * FROM DtailsEntres 'C:\tp\tp2\geststockS.mdb';
Insert Into DtailsSorties Select * FROM DtailsSorties 'C:\tp\tp2\geststockS.mdb';
Insert Into Rglements Select * FROM Rglements 'C:\tp\tp2\geststockS.mdb';
Insert Into Encaissements Select * FROM Encaissements 'C:\tp\tp2\geststockS.mdb';

3.
Insert Into Catgories IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Catgories in 'C:\tp\tp2\geststockS.mdb';
Insert Into Socits IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Socits in 'C:\tp\tp2\geststockS.mdb';
Insert Into Produits IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Produits in 'C:\tp\tp2\geststockS.mdb';
Insert Into Entres IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Entres in 'C:\tp\tp2\geststockS.mdb';
Insert Into Sorties IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Sorties in 'C:\tp\tp2\geststockS.mdb';
Insert

Into

DtailsEntres

IN

'C:\tp\tp1\geststockD.mdb'

Select

FROM

DtailsEntres

in

'C:\tp\tp2\geststockS.mdb';
Insert Into DtailsSorties IN 'C:\tp\tp1\geststockD.mdb' Select * FROM DtailsSorties in 'C:\tp\tp2\geststockS.mdb';
Insert Into Rglements IN 'C:\tp\tp1\geststockD.mdb' Select * FROM Rglements in 'C:\tp\tp2\geststockS.mdb';
Insert

Into

Encaissements

IN

'C:\tp\tp1\geststockD.mdb'

Select

FROM

Encaissements

in

'C:\tp\tp2\geststockS.mdb';

3. La commande UPDATE

La commande UPDATE est utilise pour changer des valeurs dans des champs d'une table. Sa syntaxe est :
UPDATE Nomtable
SET champ1 = nouvelle_valeur1, /* il sagit dune expression */
champ2 = nouvelle_valeur2,
champ3 = nouvelle_valeur3

Page 19

SQL/Microsoft Jet

Par M. Qbadou

WHERE condition;

La clause SET indique quels champs de la table vont tre mis jour et avec quelles valeurs ils vont l'tre. Les
champs non spcifis aprs la clause SET ne seront pas modifis.
Par exemple, pour modifier, dans la table Produits, le prix d'un produit dont le nom est "prod1" :
UPDATE produits
SET prix_unitaire = 1000
WHERE libelle = 'prod1';

La commande UPDATE affecte tous les enregistrements qui rpondent la condition donne dans la clause
WHERE. Si la clause WHERE est absente, tous les enregistrements de la table seront affects.
Par exemple pour modifier Le prix unitaire de TOUS les produits de la table produits :
UPDATE produits
SET prix_unitaire = 1000;

Exemple 2
Remplacer par 0 toutes les qtStock dfinies NULL :
Update Produits
Set QtStock=0
Where QtStock is NULL
Comme la commande INSERT, la commande UPDATE peut contenir une sous requte select. Dans ce cas la
syntaxe est la suivante :
UPDATE table
SET champ1 = nouvelle_valeur1,
champ2 = nouvelle_valeur2,
champ3 = nouvelle_valeur3,
WHERE condition = (requte);

Exemple : Pour changer Dafir le nom des acheteurs qui ont achet le produit de code 1
UPDATE Acheteurs SET nom = 'Dafir'
WHERE num_acheteur =(SELECT num_acheteur
FROM Ventes WHERE num_Produit = 1);

Page 20

SQL/Microsoft Jet

Par M. Qbadou

4. La commande DELETE
Pour supprimer des enregistrements d'une table, utilisez la commande DELETE. La syntaxe est la suivante :
DELETE *
FROM table
WHERE condition;

On ne peut pas supprimer seulement le contenu de quelques champs des enregistrements. La commande
DELETE supprime des enregistrements entiers, c'est pour cela qu'il n'est pas ncessaire d'indiquer ici des noms
de champs. La condition spcifie aprs WHERE va dterminer quels sont les enregistrements supprimer.
Par exemple, pour supprimer tous les clients dont la ville est Rabat :
DELETE
FROM Clients
WHERE ville='Rabat';

Pour supprimer tous les enregistrements d'une table, n'indiquez pas de clause WHERE :
DELETE FROM table;

Cette variante de la commande DELETE ne supprime pas la table, elle supprime seulement les enregistrements
contenus dans cette table et laisse une table vide. On peut aussi, utiliser une sous requte qui servira dterminer
la condition de la suppression. La syntaxe est la suivante :
DELETE
FROM table
WHERE condition = (requte);

5. Application
Une agence de voyage veut informatiser la gestion des voyages quelle propose ses clients( itinraires,
monuments visits, rservations )
Une base de donnes doit tre construite partir du cahier des charges suivant :

Les circuits :
Un circuit est identifi par un numro, un nombre dtapes et une squence dtapes. Une tape se droule
pendant un nombre donn de jours dans une ville donne. Au cours de chaque tape, tous les monuments de la
ville, lorsquil y en a, sont visits.
Un mme circuit ne repasse jamais plusieurs fois dans la m^me ville, mais il peut arriver que la ville de dpart ou
darrive figure parmi les villes tapes.
Page 21

SQL/Microsoft Jet

Par M. Qbadou

Les villes sont identifies par leurs noms. Les monuments sont identifis par leurs noms dans la ville o ils sont
situs.
Un circuit peut tre programm plusieurs fois des dates diffrentes. A chacune de ces programmations on
associe un nombre de places. Deux programmations dun m^me circuit peuvent avoir des nombres de places
diffrents. Par contre le prix dun circuit est toujours le mme quelle que soit sa date de programmation. La
dure dun circuit est gale la somme des dures de ses tapes.

Les rservations :
Une rservation, identifie par un numro, est effectue pour le compte dun client (identifi par un nom) et
concerne une programmation dun circuit. Plusieurs places pour la mme programmation du mme circuit
peuvent tre rserves en une seule fois.

5.1. Schma relationnel

Le schma relationnel retenu pour la base de donnes est compos des tables suivantes :
Villes (NomVille :Char(15), Pays : Char(15))
Monuments(NomMonument : Char(30), NomVille#,Prix :Currency)
Circuits(NCircuit : Integer,NbreEtapes : Integer,PrixCircuit : Currency)
Etapes(NEtape : Integer,NCircuit#,NomVille#,Dure : Integer)
Programmations(NCircuit#,DateProg : Date,NbrePlaces : Integer)
Clients(codeClt : Long,NomClt : Char(15))
Rservations(NReservation : Long,CodeClt#,(NCircuit,DateProg)#,Places : Integer)
Sous forme graphique le schma se prsente comme suit :

5.2. Implantation

Page 22