EXEMPLE

Un club de voyage propose des voyages organisés à ses adhérents. En

septembre le club envoie à tous ses adhérents un catalogue contenant les
voyages proposés pour l'année suivante.
Un voyage se caractérise par une référence unique, par sa destination, par sa
durée (la durée est exprimée en nombre de semaines), par son type (le type
peut prendre les valeurs suivantes CT pour circuit touristique, RD pour raid
découverte, SD pour sport et détente), par son coût (le montant global du
voyage) et par la liste de toutes les dates de départ valides.
Les adhérents peuvent s'inscrire à un ou plusieurs voyages ou adhérer
uniquement pour profiter d'autres activités proposées par le club. Toute
personne qui participe à un voyage doit être adhérente au club. Un numéro
unique est attribué à chaque adhérent. Les informations enregistrées dans
chaque dossier sont le nom de l'adhérent, son prénom, son adresse et un
indicateur (l'indicateur prend la valeur vrai ou faux) précisant s'il a réglé la
cotisation de l'année en cours.
Une inscription à un voyage se fait pour une date de départ précise et
s'accompagne toujours du versement d'un acompte.
 LES DONNEES

NUMA : numéro d'adhérent,

NOMA : nom d'adhérent,
PRENA : prénom d'adhérent,
VILLEA: adresse d'adhérent, (dans la base de données exemple en extension l'adresse
se limite à la ville),
COTA : règlement de la cotisation (vrai ou faux), TRUE/FALSE
NDOSSIER : numéro de dossier de réservation,
ACOMPTE : montant de l'acompte versé à la réservation
REFV : référence du voyage,
DEST : destination du voyage,
DUREE : durée du voyage,
COUT : coût du voyage,
TYPE : type du voyage,
DATDEP : date de départ.
 TABLES EXEMPLES

Le schéma de la base de données (appelé aussi base de données en intention)

VOYAGE (REFV:ENTIER, DEST:TEXTE, DUREE:ENTIER, TYPE:TEXTE,

COUT:REEL)

ADHERENT (NUMA:ENTIER, NOM:TEXTE, PRENOM:TEXTE, VILLE:TEXTE)

RESERVATION (NDOSSIER:ENTIER, NUMA:ENTIER, REFV:ENTIER, DATEDEP:DATE,

ACOMPTE:REEL)

DEPART (REFV:ENTIER, DATEDEP:DATE)

Par convention, dans chaque table, le groupe de champs servant de clé primaire est souligné
 TABLES EXEMPLES

VOYAGE REFV DEST DUREE TYPE COUT

101 TUNISIE 2 RD 1500
201 GRECE 2 CT 1300
109 TUNISIE 1 SD 1150
401 NORVEGE 2 RD 2300
304 ESPAGNE 2 CT 1300

DEPART REFV DATDEP

101 15/05
201 07/05
109 21/06
401 01/09
304 27/04
304 07/05
101 21/08
201 03/07
 TABLES EXEMPLES

ADHERENT NUMA NOM VILLE COTA

1 MARUS ANGOULEME VRAI
2 ROUL VERSAILLES FAUX
3 STANIS VERSAILLES VRAI
4 JACOB NICE FAUX
5 MADEL VERSAILLES VRAI
6 LAURE PARIS VRAI
7 SIDON AMIENS VRAI

RESERVATIONN DOSSIER NUMA REFV DATE ACOMPTE

1 5 101 15-05 300
2 5 401 01-09 450
3 1 401 01-09 450
4 6 304 27-04 240
5 3 304 07-05 230
6 3 101 21-08 275
7 7 201 07-05 300
 Structured Query Language : SQL

Définition et manipulation de bases de données relationnelles.

Standard: SQL est disponible dans tous les SGBD relationnels du

marché par exemple : en micro Access ; professionnel Oracle, Informix
; domaine public Postgrès, MySQL

Plusieurs normes ANSI/OSI : la plus répandue SQL2 définie en 1992

SQL permet d’écrire des expressions complètes de séquences

d’opérations relationnelles
 Les 4 instructions de manipulation
SELECT liste de champs ou de calculs DELETE *
FROM liste de tables FROM une table
WHERE condition WHERE condition ;
GROUP BY liste de champs
HAVING condition
ORDER BY liste de champs ;

UPDATE une table INSERT INTO une table Cible

SET liste d’affectations de SELECT liste de champs ou de calculs
valeurs FROM liste de tables
WHERE condition ; WHERE condition
GROUP BY liste de champs
HAVING condition
ORDER BY liste de champs ;

SQL est un langage de clauses : exemple la clause SELECT, la clause FROM….

Seules les clauses notées en gras sont obligatoires dans l’instruction.
L’exécution d’une instruction DELETE, UPDATE ou INSERT INTO modifie le contenu de
la BD. L’exécution d’une instruction SELECT ne modifie pas le contenu de la BD
 SELECT liste de champs ou de calculs
FROM liste de tables
WHERE condition
GROUP BY liste de champs
HAVING condition
ORDER BY liste de champs;

SELECT ce qui sera affiché dans le résultat

FROM les tables utiles à la requête (et seulement les tables utiles)
WHERE condition (une ou plusieurs conditions simples combinées par AND ou OR)
GROUP BY ce qui est utilisé pour le partionnement dans le cas où on utilise une
fonction dans la clause SELECT
HAVING condition sur le résultat d’une fonction
ORDER BY ce qui est utilisé pour le tri des champs de la clause SELECT ;

SELECT DISTINCT NOM, PRENOM

FROM ADHERENT , RESERVATION
WHERE VILLE = ‘PARIS’
AND [Link] = [Link] ;
 Clause SELECT

POUR AFFINER LA PROJECTION

•LES MOTS CLE : ALL ET DISTINCT

ALL PRECISE QUE TOUS LES TUPLES (DOUBLONS COMPRIS)

DOIVENT FIGURER DANS LA TABLE RESULTAT.

DISTINCT PRECISE QUE LES TUPLES IDENTIQUES DOIVENT

FIGURER UNE SEULE FOIS DANS LA TABLE RESULTAT
(ELIMINATION DES DOUBLONS).

Des doublons sont des lignes de données complétement identiques

Remarque : Par défaut ALL.

L’utilisation du caractère * dans la clause Select permet de demander

l’affichage de tous les champs des tables citées dans la clause FROM; c’est
une facilité d’écriture
 Clause SELECT
POUR AFFINER LA PROJECTION

•EXPRESSION DE TRI

CLAUSE ORDER BY liste de champs

exemple : SELECT *

FROM VOYAGE

WHERE TYPEV=‘CT’

ORDER BY DUREE DESC;

Remarque : ASC est utilisé par défaut si DESC est omis

ASC ordre croissant DESC décroissant.

 REQUETES SIMPLES
Donner la liste des noms et des prénoms de tous les
adhérents
SELECT NOM, PRENOM
FROM ADHERENT;

Donner la liste des destinations de voyage proposées

SELECT DISTINCT DEST
FROM VOYAGE;

Donner la liste triée par ordre croissant des noms et

des prénoms de tous les adhérents
SELECT NOM, PRENOM
FROM ADHERENT
ORDER BY NOM;
 REQUETES SIMPLES

Donner la liste des destinations de voyage de type

« circuit touristique » d’une durée de 2 semaines
proposées par le club
SELECT DISTINCT DEST
FROM VOYAGE
WHERE TYPE = ‘CT’
AND DUREE = 2;
; Donner la liste triée par ordre croissant des noms et
des prénoms de tous les adhérents
SELECT NOM, PRENOM
FROM ADHERENT
ORDER BY NOM;
 Clause WHERE
Une condition simple est de la forme

Champ COMPARATEUR Constante

< , > , <= , >= , <> , =

<> Signifie diffèrent de

BETWEEN…AND…
y BETWEEN x AND z  x<=y and y<=z

IN
X IN (a, b, …,z)  x=a or x=b or … or x=z
Ex: TYPEV IN (‘CT’, ‘RD’, ‘SD’)
LIKE
y LIKE modèle % , _ , # , etc…

NOT
 Clause WHERE

Une condition simple est de la forme

Champ COMPARATEUR Constante

pour sélectionner une ou plusieurs lignes

• Une constante numérique

• Une constante textuelle entre guillemets

• Une constante de type date entre #MM/JJ/AAAA#

• Une constante booléenne true/false

(rmq: pas de guillemet pour le type booléen)
 Clause WHERE

Une condition simple est de la forme

Champ COMPARATEUR Constante

pour sélectionner une ou plusieurs lignes

Donner la liste des noms et des prénoms

de tous les adhérents résidant à Paris
SELECT DISTINCT NOM, PRENOM
FROM ADHERENT
WHERE VILLE =‘PARIS’;
 Clause WHERE

Une condition simple est de la forme

Champ COMPARATEUR Constante

pour sélectionner une ou plusieurs lignes

Donner la liste des noms et des prénoms de tous les

adhérents dont le nom commence par la lettre M
SELECT DISTINCT NOM , PRENOM SELECT DISTINCT NOM , PRENOM
FROM ADHERENT FROM ADHERENT
WHERE NOM LIKE ‘M% ’ ; WHERE NOM LIKE ‘M* ’ ;

Attention : un-peu différent dans SQL du logiciel Access le caractère générique *

de la suite Microsoft remplace % de SQL normalisé
% ou * signifient une suite quelconque de caractères (éventuellement vide)
Voir le document caractères génériques
 Clause WHERE

Une condition simple est de la forme

Champ COMPARATEUR Champ

pour demander une jointure entre 2 tables

Donner pour chaque adhérent la liste des réservations

qu’il a effectuées. Afficher le numéro d’adhérent, son nom
et son prénom, le numéro de dossier de la réservation.
SELECT [Link], NOM, PRENOM, NDOSSIER
FROM ADHERENT, RESERVATION
WHERE [Link] = [Link];

.
La notation pointée ou préfixée en cas d’ambiguïté : nomTable nomChamp
Ambiguïté c’est-à-dire même nom de champ dans plusieurs tables de la clause FROM
 Clause WHERE

Une condition simple est de la forme

Champ COMPARATEUR Champ

pour demander une jointure entre 2 tables

Utilisation d’alias (appelés aussi variables)

Donner pour chaque adhérent la liste des réservations

qu’il a effectuées. Afficher le numéro d’adhérent, son nom
et son prénom, le numéro de dossier de la réservation.
SELECT [Link], NOM, PRENOM, [Link]
FROM ADHERENT AS A, RESERVATION AS R
WHERE [Link] = [Link];

.
La notation pointée ou préfixée en cas d’ambiguïté : Alias nomChamp
 Clause WHERE

Une condition simple est de la forme

Champ COMPARATEUR Champ

pour demander une jointure entre 2 tables

Utilisation d’alias (appelés aussi variables)

Donner pour chaque adhérent la liste des réservations

qu’il a effectuées. Afficher le numéro d’adhérent, son nom
et son prénom, le numéro de dossier de la réservation.
SELECT [Link], NOM, PRENOM, [Link]
FROM ADHERENT A, RESERVATION R
WHERE [Link] = [Link];

.
La notation pointée ou préfixée en cas d’ambiguïté : Alias nomChamp
 Clause WHERE

Une condition simple de jointure est de la forme

Champ COMPARATEUR Champ

pour demander une jointure entre 2 tables

Une règle à vérifier avant d’exécuter une requête :

Si N tables dans la clause FROM
alors N-1 expressions de jointure dans la clause WHERE
 Clause WHERE
Dans la clause WHERE les conditions simples
sont combinées par AND ou bien OR
Attention à la précédence de AND/OR

Retrouver les voyages de type circuit touristique ou sport

détente dont le prix est compris entre 1400 et 1500 euros ;
afficher leur destination et leur type
SELECT DISTINCT DEST, TYPE
FROM VOYAGE
WHERE COUT BETWEEN 1400 AND 1500
AND TYPE IN ("CT", "SD") ;
 Clause WHERE
Attention à la précédence de AND/OR
Rappel: AND similaire à la multiplication, OR
similaire à l’addition

Retrouver les voyages de type circuit touristique ou sport

détente dont le prix est compris entre 1400 et 1500 euros ;
afficher leur destination et leur type
SELECT DISTINCT DEST, TYPE
FROM VOYAGE
WHERE COUT >= 1400 AND COUT < =1500
AND (TYPE = ‘CT ’ OR TYPE = "SD") ;

Rmq: on force l’ordre d’évaluation en utilisant des parenthèses

 Clause WHERE
La même requête en développant les termes

Retrouver les voyages de type circuit touristique ou sport

détente dont le prix est compris entre 1400 et 1500 euros ;
afficher leur destination et leur type
SELECT DISTINCT DEST, TYPE
FROM VOYAGE
WHERE COUT >= 1400
AND COUT < =1500
AND TYPE = ‘CT ’
OR COUT >= 1400
AND COUT <= 1500
AND TYPE = "SD" ;
 FONCTIONS D’AGREGATION

Les fonctions d'agrégation dans SELECT.

SUM(champ) pour calculer le cumul de toutes les valeurs du champ

AVG(champ) pour calculer la moyenne de toutes les valeurs du champ

MIN(champ) pour calculer la plus petite de toutes les valeurs du champ

MAX(champ) pour calculer la plus grande de toutes les valeurs du champ

COUNT(champ) ou COUNT(*) pour compter le nombre de valeurs du champ

STDDEV(champ) pour calculer l’écart-type de toutes les valeurs du champ

VARIANCE(champ) pour calculer la variance de toutes les valeurs du champ

Afficher la durée maximale et la durée moyenne des

voyages
SELECT MAX(DUREE) as MAXIMUM, AVG(DUREE) as MOYENNE

FROM VOYAGE ;
 EXPRESSION DE PARTIONNEMENT & AGREGAT

• CLAUSE GROUP BY liste de champs

Le partitionnement permet de partitionner une table

en plusieurs sous-ensembles dont les intersections
sont vides et dont l’union redonne la table dans son
intégralité.

Le partitionnement est utilisé en liaison avec les

fonctions d'agrégation dans SELECT.
SUM - AVG - MIN - MAX - COUNT - STDDEV - VARIANCE

Il y aura un tuple réponse par sous-ensemble.

Remarque : partitionnement ou classification
Calculer le coût moyen des voyages de 2 semaines en fonction
de la destination
SELECT DEST, AVG(COUT)

FROM VOYAGE

WHERE DUREE =2

GROUP BY DEST;

Rmq: tous les champs utilisés dans GROUP BY doivent être affichés donc doivent
aussi figurer dans la clause SELECT

Afficher la durée maximale et la durée moyenne des voyages

SELECT MAX(DUREE), AVG(DUREE)

FROM VOYAGE ;

Rmq: sans clause GROUP BY, il n’y a rien d’autre que les fonctions dans le SELECT
 GROUP BY … HAVING
LA CLAUSE HAVING PERMET D'EXPRIMER UNE SELECTION SUR
LE OU LES RESULTATS DE FONCTION D’AGREGAT

CLAUSE HAVING QUALIFICATION AVEC FONCTION d ’AGREGAT

Retrouver les destinations pour lesquelles les voyages

d’une durée de 2 semaines ont un coût moyen inférieur
à 1500.
SELECT DEST, AVG(COUT)

FROM VOYAGE

WHERE DUREE =2

GROUP BY DEST

HAVING AVG(COUT) < 1500;

 SOUS-REQUETE
UNE STRUCTURE SUPPLEMENTAIRE POUR EXPRIMER LES JOINTURES

Retrouver les réservations faites sur des voyages à destination

de la Tunisie ; afficher tous les renseignements.

SELECT *
FROM RESERVATION
WHERE REFV IN
(SELECT REFV
FROM VOYAGE
WHERE DEST = ‘TUNISIE’ ) ;

Remarque la clause SELECT de la sous-requête est mono-colonne dans

cette construction
 SOUS-REQUETE
UNE STRUCTURE SUPPLEMENTAIRE POUR EXPRIMER LES JOINTURES

Retrouver les réservations faites sur des voyages à destination

de la Tunisie ; afficher tous les renseignements.

SELECT *
FROM RESERVATION
WHERE REFV IN
(SELECT REFV
FROM VOYAGE
WHERE DEST = ‘TUNISIE’ ) ;

Remarque la clause SELECT de la sous-requête est mono-colonne dans

cette construction
 SOUS-REQUETE
UNE STRUCTURE SUPPLEMENTAIRE POUR EXPRIMER LES JOINTURES

Retrouver les réservations faites sur des voyages à destination

de la Tunisie ; afficher tous les renseignements.

SELECT *
FROM RESERVATION
WHERE REFV IN
(LES VALEURS DU CHAMP REFV POUR LES SEULS VOYAGES A
DESTINATION DE LA TUNISIE) ;

Remarque la clause SELECT de la sous-requête est mono-colonne dans

cette construction
 SOUS-REQUETE
UNE STRUCTURE SUPPLEMENTAIRE POUR EXPRIMER LES JOINTURES

Retrouver les réservations faites sur des voyages à destination

de la Tunisie ; afficher tous les renseignements.

SELECT *
FROM RESERVATION
WHERE REFV IN
(101, 109) ;
FAIT A LA MAIN SELON LES VALEURS DE L’EXEMPLE DU DEBUT

Remarque la clause SELECT de la sous-requête est mono-colonne dans

cette construction
 SOUS-REQUETE
UNE STRUCTURE SUPPLEMENTAIRE POUR EXPRIMER LES JOINTURES

Retrouver les réservations faites sur des voyages à destination

de la Tunisie ; afficher tous les renseignements.

SELECT RESERVATION.*
FROM RESERVATION , VOYAGE
WHERE DEST = ‘TUNISIE’
And [Link] = [Link];
 Des sous-requêtes imbriquées

Retrouver le nom et le prénom des adhérents ayant réservé un ou

plusieurs voyages à destination de la Tunisie ; afficher tous les
renseignements.
SELECT DISTINCT NOM, PRENOM
FROM ADHERENT
WHERE NUMA IN
(SELECT NUMA
FROM RESERVATION
WHERE REFV IN
(SELECT REFV
FROM VOYAGE
WHERE DEST = ‘TUNISIE’ )
);
Retrouver le nom et le prénom des adhérents ayant réservé un ou
plusieurs voyages à destination de la Tunisie ; afficher tous les
renseignements.

SELECT DISTINCT NOM, PRENOM

FROM ADHERENT
WHERE NUMA IN
(SELECT NUMA
FROM RESERVATION
WHERE REFV IN
(SELECT REFV
FROM VOYAGE
WHERE DEST = ‘TUNISIE’ ) ) ;
Retrouver le nom et le prénom des adhérents n’ayant réservé
aucun voyage.

SELECT DISTINCT NOM, PRENOM

FROM ADHERENT
WHERE NUMA NOT IN
(SELECT NUMA
FROM RESERVATION ) ;
 SOUS-REQUETE

Pour traduire AUCUN

Retrouver les voyages ne faisant l’objet d’aucune réservation ;

afficher tous les renseignements.
SELECT *
FROM VOYAGE
WHERE REFV NOT IN
(SELECT REFV
FROM RESERVATION
);

Remarque la clause SELECT de la sous-requête est mono-colonne

 SOUS-REQUETE
La sous-requête pour effectuer le calcul d’une fonction d’agrégat

Retrouver les voyages de 2 semaines dont le prix est inférieur au

prix moyen des voyages de 2 semaines de type sport-détente ;
afficher tous les renseignements.
SELECT *
FROM VOYAGE
WHERE DUREE = 2
AND COUT <
(SELECT AVG(COUT)
FROM VOYAGE
WHERE TYPE = ‘SD’
AND DUREE = 2 ) ;

Remarque la clause SELECT de la sous-requête est mono-colonne dans cette

construction ; elle contient une fonction d’agrégat
 CONCLUSION SOUS-REQUETE

LA SOUS-REQUETE PEUT ETRE UTILISEE :

•DANS DES PREDICATS DE COMPARAISON SIMPLES

•DANS DES PREDICATS IN

Rmq : en combinant IN et NOT IN on peut exprimer QUE , TOUS

 JOINTURE INTERNE

NE PAS UTILISER

LA CLAUSE FROM PEUT ETRE UTILISEE POUR EXPRIMER DES

JOINTURES

JOINTURE CLASSIQUE APPELEE AUSSI JOINTURE INTERNE

FROM T1 INNER JOIN T2 ON CONDITION

 JOINTURE EXTERNE

JOINTURE EXTERNE

FROM T1 OUTER JOIN T2 ON CONDITION

JOINTURE EXTERNE GAUCHE

FROM T1 LEFT JOIN T2 ON CONDITION

JOINTURE EXTERNE DROITE

FROM T1 RIGHT JOIN T2 ON CONDITION

Remarque: les 2 expressions suivantes sont équivalentes

FROM T1 LEFT JOIN T2 ON CONDITION

FROM T2 RIGHT JOIN T1 ON CONDITION

 LES REQUETES DE MISE A JOUR
l’insertion

INSERT INTO une seule table Cible(col1, col2,…)

SELECT champ1, champ2,…
FROM liste de tables
WHERE condition
GROUP BY liste de champs
HAVING condition
ORDER BY liste de champs ;

Insérer dans la nouvelle table COTISANT les adhérents ayant réglé leur cotisation
pour l’année courante

INSERT INTO COTISANT(NUMERO, NOM, PRENOM)

SELECT NUMA, NOM, PRENOM
FROM ADHERENT
WHERE COTA = TRUE;
 LES REQUETES DE MISE A JOUR
l’insertion

INSERT INTO une seule table Cible(col1, col2,…)

VALUES (valeur1, valeur2,…) ;

Insérer dans la nouvelle table COTISANT l’adhérent Patrick Audibert ayant réglé
sa cotisation pour l’année courante et portant le numéro 19934

INSERT INTO COTISANT

VALUES (19934,‘AUDIBERT’,’PATRICK’);
 LES REQUETES DE MISE A JOUR
la suppression

DELETE *
FROM une seule table
WHERE condition ;

Supprimer les réservations correspondant à des voyages à destination de la

Turquie
DELETE
FROM RESERVATION
WHERE REFV IN ( SELECT REFV
FROM VOYAGE
WHERE DEST = ‘TURQUIE’);
Supprimer les réservations correspondant à des voyages à destination de la
Turquie
DELETE
FROM RESERVATION
WHERE REFV IN ( SELECT REFV
FROM VOYAGE
WHERE DEST = ‘TURQUIE’);
 LES REQUETES DE MISE A JOUR
la modification
UPDATE une seule table
SET nomchamp= expression de valeur [,…]
WHERE condition ;

Convertir la durée des voyages en nombre de jours

UPDATE VOYAGE
SET DUREE = DUREE * 7;
 Quelques mots réservés de SQL

SELECT UPDATE INSERT DELETE SET VALUES

FROM WHERE GROUP ORDER HAVING BY
AS
LIKE BETWEEN IN EXISTS NOT
AVG SUM MAX MIN COUNT
AND OR
INTO
ALL DISTINCT
ASC DESC
*
NULL
TRUE FALSE
….
 Biblio SGBD SQL
Pierre Delmas, SQL2- la Pratique, 3ème édition 2002, édition De Boeck
Université- bibliothéque des universités.
ouvrage recommandé. Très complet sur le langage SQL, beaucoup d’exemples. Un
rappel du modèle relationnel et des fonctionnalités et mécanismes de contrôle des
SGBD.
Roman, Bases de données Microsoft-Access : conception et programmation, 1998,
édition O’Reilly (317 pages).
orienté Access, quelques rappel du modèle relationnel, un-peu de SQL.
Georges Gardarin, Bases de données-Les systèmes et leurs langages, 7ème édition
1991, edition de poche (280 pages).
Georges Gardarin, Maîtriser les bases de données-Modèles et langages, 1993,
édition Eyrolles (368 pages).
modèle relationnel, fonctionnalités et mécanismes de contrôle et d’optimisation. Un
–peu de SQL.
Georges et Olivier Gardarin, Le client-serveur, 1996, édition Eyrolles.
le chapitre 4 (pages 89 à 120) traite les techniques des SGBD relationnels