Plan du cours

• Partie I : Introduction aux bases de données relationnelles

Quatrième partie IV • Cours 1 : Concepts des bases de données relationnelles
• Cours 2 : L’algèbre relationnelle
• Partie II : Utilisation des bases de données relationnelles
• Cours 3 : Le langage SQL DML (1)
Le langage SQL DML (2) • Cours 4 : Le langage SQL DML (2)
• Cours 5 : Le langage SQL DDL
• Partie III : Developpement des bases de données relationnelles
• Cours 6 : Le modèle entité-association
• Cours 7 : Élaboration d’un schéma conceptuel
• Cours 8 : Production du schéma de la base de données

Qu’allons nous aborder dans ce cours ? Extraction de données de plusieurs tables

• La jointure permet de coupler les lignes de plusieurs tables afin d’en

extraire des données corrélées.
• Exemple :
1. L’extraction de données de plusieurs tables au moyen de l’opérateur de NCOM NCLI DATECOM NOM LOCALITE
jointure 30178 K111 21/12/2008 VANBIST Lille
30179 C400 22/12/2008 FERARD Poitiers
2. Les opérateurs ensemblistes (union, intersection et di↵érence) 30182 S127 23/12/2008 VANDERKA Namur
3. Le traitement des structures de données cycliques, dans lesquelles une ligne 30184 C400 23/12/2008 FERARD Poitiers
30185 F011 02/01/2009 PNCELET Toulouse
fait directement ou indirectement référence à une ligne de la même table 30186 C400 02/01/2009 FERARD Poitiers
30188 B512 03/01/2009 GILLET Toulouse
4. L’extraction de données groupées, qui permettent de s’intéresser non plus
à des lignes mais à des groupes de données Les colonnes NCOM, NCLI et DATECOM sont issues de la table
COMMANDE tandis que NCLI, NOM et LOCALITE sont tirées de la
table CLIENT. Cette table a été obtenue par une jointure entre les tables
COMMANDE et CLIENT par association de la colonne NCLI.
Extraction de données de plusieurs tables Extraction de données de plusieurs tables

• La requête qui permet d’obtenir le résultat précédent est :

SELECT NCOM, NCLI , DATECOM, NOM, LOCALITE

• Une jointure peut s’appliquer sur plus de 2 tables. Il faut alors n 1
FROM COMMANDE, CLIENT
conditions d’association.
WHERE COMMANDE. NCLI = CLIENT. NCLI
• Exemple :
• Conceptuellement, le résultat pourrait être construit comme suit :
1. On construit une table en couplant chaque ligne de la première table avec SELECT NCLI , NOM, DATECOM, NPRO
chaque ligne de la seconde : FROM CLIENT , COMMANDE, DETAIL
FROM COMMANDE, CLIENT WHERE CLIENT. NCLI = COMMANDE. NCLI
AND [Link] = [Link]
2. On sélectionne parmi les lignes ainsi obtenues, celles qui vérifient la
condition d’association et éventuellement les autres conditions : • Les conditions d’association sont aussi appelées condition de jointure
WHERE COMMANDE. NCLI = CLIENT. NCLI • En pratique, les conditions de jointure sont traitées comme des conditions
3. On retient que les colonnes demandées : de sélection ordinaires
SELECT NCOM, NCLI , DATECOM, NOM, LOCALITE

Extraction de données de plusieurs tables Extraction de données de plusieurs tables

• Exemple :
• Donner pour chaque commande antérieure au 23/12/2009 passée par des
clients de catégorie C1 le numéro de commande, sa date, le numéro du
• Les lignes célibataires sont les lignes qui n’ont aucune correspondance
client, son nom et sa ville dans l’une des tables de la jointure
• Exemple :
SELECT NCOM, NCLI , DATECOM, NOM, LOCALITE • les clients qui n’ont passé aucune commande
FROM COMMANDE, CLIENT • aucune commande n’est célibataire
WHERE COMMANDE. NCLI = CLIENT. NCLI • Il existe une méthode pour obtenir les lignes célibataires appelée la joiture
AND CAT = ’ C1 ’ externe (OUTER JOIN)
AND DATECOM < ’ 23 12 2009 ’
Les opérateurs ensemblistes Les opérateurs ensemblistes

• Un ensemble est une collection d’éléments distincts • L’opérateur UNION produit une collection de lignes distinctes à partir d’un
• Un ensemble de lignes ne peut donc contenir 2 lignes dont les attributs,
ensemble de 2 collections de lignes
considérés 2 à 2, ont la même valeur
• Une collection de lignes dont les éléments ne sont pas distincts constitue • Si une même ligne apparaı̂t dans chacune des 2 collections de lignes en
un multi-ensemble argument de l’opérateur UNION, cette ligne n’apparaı̂tra qu’une seule fois
• Une requête dont la liste d’éléments de la clause SELECT n’inclut pas tous dans l’ensemble résultat
les éléments d’un identifiant renvoie un multi-ensemble
• Remarque : Les arguments de l’opérateur UNION peuvent être des
• Un multi-ensemble peut se réduire si nécessaire à un ensemble par le
modifieur DISTINCT muli-ensembles, auquels cas les doublons sont égalements éliminés

Les opérateurs ensemblistes Les opérateurs ensemblistes

• Si l’on désire empêcher l’élimination des lignes en double, on utilisera

l’opérateur UNION ALL
• Soit la requête suivante : • Une même ligne qui apparaı̂t n1 fois dans le premier membre et n2 fois dans
le second apparaı̂tra n1 + n2 dans le résultat, comme le montre la requête
SELECT LOCALITE FROM CLIENT WHERE CAT = ’ C1 ’ ;
suivante :
• avec la suivante qui lui ajoute des éléments : SELECT LOCALITE FROM CLIENT WHERE CAT = ’ C1 ’ ;
UNION ALL
SELECT LOCALITE FROM CLIENT WHERE CAT = ’ C1 ’ ; SELECT LOCALITE FROM CLIENT WHERE COMPTE < 0 ;
UNION
• le résultat de la requête est le suivant :
SELECT LOCALITE FROM CLIENT WHERE COMPTE < 0 ;
LOCALITE
• le résultat produit est le suivant : Poitiers
Namur
LOCALITE Poitiers
Namur Namur
Poitiers Namur
Toulouse Namur
.
.
.
Les opérateurs ensemblistes Les opérateurs ensemblistes

• Les opérateurs d’intersection (INTERSECT) et de di↵érence (EXCEPT)

fonctionne de manière similaire à l’opérateur UNION
• L’opérateur INTERSECT construit l’ensemble des éléments simultanément • Les opérateurs d’intersection et de di↵érence entre deux ensembles de
présents dans deux collections lignes ne sont pas strictement indispensables dans la mesure ou ils peuvent
• L’opérateur EXCEPT construit l’ensemble des éléments appartenant à la être exprimés par des requête standards :
première collection mais pas à la seconde • L’intersection ensembliste de 2 tables est obtenue par leur jointure, car
• Remarque : Munis de la clause ALL, ces opérateurs préservent les lignes en celle-ci reprend les éléments qui sont simultanément présents dans ces tables
doubles. Pour une même ligne respectivement en n1 et n2 exemplaires dans • La di↵érence ensembliste s’exprimera par le prédicat NOT IN dont la
chaque argument : sous-requête définit les éléments de la seconde collection
• INTERSECT ALL produira min(n1 , n2 ) exemplaires de cette ligne
• EXCEPT ALL produira max(n1 n2 , 0) exemplaires de cette ligne

Le produit relationnel Le produit relationnel

• Considérons tous les couples (LOCALITE, NPRO) possibles issus de la

base de données :
• Une jointure sans condition de jointure telle que la suivante n’est pas
S1 = (SELECT DISTINCT LOCALITE, NPRO
interdite
FROM CLIENT , PRODUIT)
SELECT NCOM, CLIENT. NCLI , DATECOM, NON, ADRESSE
FROM COMMANDE, CLIENT • Construisons maintenant tous les couples e↵ectifs, indiquant qu’on
commande réellement le produit dans la localité :
Elle risque d’être cependant coûteuse (le résultat contiendra ici
16 ⇥ 7 = 112 lignes). Sauf justification, il faudra la considérer comme une S2 = (SELECT DISTINCT LOCALITE, NPRO
erreur. FROM CLIENT C, COMMANDE M, DETAIL D
WHERE C. NCLI = M. NCLI AND [Link] = [Link])
• Cette jointure particulière porte le nom de produit relationnel ou produit
cartésien • Le service commercial sera certainement très intéressé par les produit
• Le produit relationnel permet de comprendre le mécanisme de la jointure qu’on ne commande pas dans chaque commune, i.e., pour lesquels un
mais n’o↵re pas de grand intérêt en soi. e↵orts d’information serait utile :

S1 EXCEPT S2 ;
Requête sur des structures de données cycliques Requête sur des structures de données cycliques

• On qualifie de cyclique une structure de données qui fait directement ou PERSONNE

non, référence à elle-même NPERS NOM RESPONSABLE p1 p2
p1 Mercier –
• Par exemple : p2 Durant –
p3 Noirons p1
PERSONNE p4 Dupont p1
NPERS NOM RESPONSABLE p5 Verger p4 p4 p3 p8
p6 Dupont p4
Il existe un cycle entre la colonne RESPONSABLE (clé étrangère de la p7 Dermiez p6
table PERSONNE) et la table PERSONNE elle-même. Le rôle de cette p8 Anciers p2
colonne est de désigner le responsable direct de chaque personne, s’il p6 p5
existe.

p7

Requête sur des structures de données cycliques Requête sur des structures de données cycliques

• La table PERSONNE permet de répondre à la question suivante : donner,

pour chaque personne (S, pour subordonné) ayant un responsable (R), le • Donner pour chaque personne dont le nom est Dupont, son numéro ainsi
numéro et le nom de celui-ci que le numéro et le nom de son responsable s’il existe

SELECT [Link], [Link], [Link] SELECT [Link], [Link], [Link]

FROM PERSONNE S, PERSONNE R FROM PERSONNE S, PERSONNE R
WHERE [Link] = [Link] ; WHERE [Link] = [Link] AND [Link] = ’ Dupont ’
UNION
• Cette requête construit des couples de personnes, la première étant la SELECT NPERS, ’ ’, ’ ’
personnes subordonnée (S) et la seconde son responsable (R) FROM PERSONNE
• Elle réalise donc une jointure de la table PERSONNE avec elle-même, ce WHERE RESPONSABLE I S NULL AND Nom = ’ Dupont ’ ;
qu’on appelle une auto-jointure
Requête sur des structures de données cycliques Requête sur des structures de données cycliques

• Donner, pour chaque personne subordonnée à la personne de numéro p4,

• On modélise la nomenclature de produit. Chaque produit est composé de
son numéro et son nom. On ignorera les personnes qui n’ont pas de
sous-produits qui sont eux-même composés de sous-produits etc.
responsable.
PRODUIT
SELECT [Link], [Link] NPRO LIBELLE PRIX U POIDS U
FROM PERSONNE R, PERSONNE.S, PERSONNE SS COMPOSITION
WHERE [Link] = ’ p4 ’ COMPOSE COMPOSANT QTE
AND [Link] = [Link]
• La table COMPOSITION représente les relations de composition entre
AND [Link] = [Link] ;
produits
• Remarques : • Une ligne (h, b, q) indique que le produit b est un composant du produit
• Nous obtenons ici les subordonnées de niveau 2 en e↵ectuant une double h, et qu’il faut q unités de b pour fabriquer 1 unité de h
jointure
• Il est impossible d’e↵ectuer des jointures récursivement • Les matières premières ont un prix et un poids unitaires fixés
• Cette requête montre que SQL ne permet pas d’obtenir facilement les • Le prix et le poids des autres produits peuvent être déterminés à partir des
responsables directs et indirects d’une personne déterminée sans recourir à caractéristiques de leurs composants
la programmation procédurale

Requête sur des structures de données cycliques Requête sur des structures de données cycliques

PRODUIT COMPOSITION • Donner les informations relatives aux produits p4 ainsi que sa composition
NPRO LIBELLE PRIX U POIDS U COMPOSE COMPOSANT QTE
p1 A-200 – – p1 p2 2 SELECT [Link], H. LIBELLE , [Link], B. LIBELLE
p2 A-056 – – p1 p3 1
p3 B-661 – – p1 p4 2
FROM PRODUIT H, COMPOSITION C, PRODUIT B
p4 B-122 – – p2 p7 8 WHERE [Link] = [Link]
p5 B-326 – – p2 p8 2 AND [Link] = [Link]
p6 D-822 3.50 0.70 p3 p8 5
p7 D-507 8.00 0.25 p4 p8 4 AND [Link] = ’ p4 ’ ;
p8 G-993 5.00 1.15 p4 p9 5
p9 F-016 – – p4 p10 5 • H et B désigne respectivement le produit composé (haut) et le produit
p10 J-500 – – p5 p4 2 composant (bas)
p11 J-544 0.50 0.90 p5 p6 7
p12 L-009 1.70 2.30 p9 p11 2
p10 p11 4
p10 p12 3
Compléments sur les jointures Compléments sur les jointures

• La jointure est un opérateur fondamental en ce qu’il permet de naviguer

parmi les données. • Certaines conditions utilisant une sous-requête (SELECT emboité)
• Dans la suite nous allons nous intéresser en particulier : peuvent s’exprimer à l’aide d’une jointure
1. à la pertinence d’utiliser une sous-requête ou une jointure • Examinons 2 cas :
2. aux valeurs dérivées dans une jointure 1. Le cas des conditions d’association et de non association
3. aux jointures généralisées 2. Le cas de sous-requête et de clé étrangère multicomposants
4. à l’interprétation du résultat d’une jointure

Compléments sur les jointures Compléments sur les jointures

SELECT *
FROM COMMANDE
SELECT NCOM, DATECOM WHERE NCOM IN (SELECT NCOM
FROM COMMANDE FROM DETAIL
WHERE NCLI IN (SELECT NCLI WHERE NPRO = ’ PA60 ’
FROM CLIENT AND QCOM < (SELECT QCOM
WHERE LOCALITE = ’ P o i t i e r s ’ ) ; FROM DETAIL
WHERE NPRO = ’ PA60 ’
peut s’écrire également sous la forme d’une jointure : AND NCOM = ’ 30182 ’ ;
SELECT NCOM, DATECOM
peut s’écrire également sous la forme d’une jointure :
FROM COMMANDE, CLIENT
WHERE COMMANDE. NCLI = CLIENT. NCLI SELECT [Link], DATENCOM, NCLI
AND LOCALITE = ’ P o i t i e r s ’ ; FROM COMMANDE M, DETAIL D1, DETAIL D2
WHERE [Link] = [Link] AND [Link] = ’ 30182 ’
AND [Link] = ’ PA60 ’ AND [Link] < [Link] ;
Compléments sur les jointures Compléments sur les jointures

SELECT NCOM, DATECOM, NCLI

FROM COMMANDE
WHERE NCOM NOT IN (SELECT NCOM
FROM DETAIL
WHERE NPRO = ’ PA60 ’ ) ;
• Conclusion
n’est absolument pas équivalente à 1. La jointure et la sous-requête permettent d’exprimer des conditions
d’association entre lignes
SELECT DISTINCT [Link], DATECOM, NCLI
2. En revanche, des conditions de non-association ne sont généralement
FROM COMMANDE, DETAIL exprimables que pas des sous-requêtes, ainsi que par la forme NOT EXISTS
WHERE [Link] = [Link] AND NPRO <> ’ PA60 ’ ;

• Remarque : il faut se souvenir qu’une jointure fondée sur le couple

identifiant primaire/clé étrangère permet de matérialiser des associations
entre lignes et non l’inexistence d’association.

Compléments sur les jointures Compléments sur les jointures

• Une jointure permet également d’e↵ectuer des calculs sur des quantités
extraites de plusieurs tables
• Le raisonnement est simple : la jointure constitue des lignes fictives dont la
clause SELECT extrait des valeurs comme elle le ferait d’une ligne réelle
issue d’une table
• Par exemple, la requête suivant associe à chaque ligne de DETAIL le
• Les jointures étudiées jusqu’ici étaient fondées sur l’égalité des valeurs
montant à payer :
d’une clé étrangère avec celles d’un identifiant
SELECT NCOM, [Link], QCOM*PRIX • Toutefois, la forme même de la condition de jointure suggère que toute
FROM DETAIL D, PRODUIT P compararison peut servir à indiquer comment associer les lignes des tables
WHERE [Link] = [Link] ; concernées

• La requête suivante, elle établit le montant de la commande 30184 :

SELECT ’ Montant commande 30184 = ’ , SUM(QCOM*PRIX)

FROM DETAIL D, PRODUIT P
WHERE [Link] = ’ 30184 ’ AND [Link] = [Link] ;
Compléments sur les jointures Compléments sur les jointures

• Considérons le schéma suivant :

• VENTE (CHAINE, PRODUIT, PRIX) : chaque ligne (c, p, x) indique que le • L’exemple ci-dessous illustre une opération fréquente qui consiste à
produit p a été vendu dans le magasin c au prix x condenser de l’information de manière à la rendre plus lisible
• IMPLANTATION (CHAINE, VILLE) : chaque ligne (c, v ) indique que le • La table ci-dessous établit des intervalles successifs de valeurs de compte
magasin c est implanté dans la ville v
(MIN CPT et MAX CPT) leur attribue un code (CODE CPT)
• La colonne CHAINE n’est pas une clé étrangère, ni un identifiant de la
CLASSE CPT
relation IMPLANTATION MIN CPT MAX CPT CODE CPT
• On peut seulement espérer que les colonnes aient des valeurs communes 10000 32000 A
5000 10000 B
• Nous ne sommes pas dans le schéma classique représentant des 2000 5000 C
associations explicites 1000 2000 D
500 1000 F
0 500 G
• SELECT DISTINCT PRODUIT, VILLE , PRIX -500 0 U
FROM VENTE V, IMPLANTATION I -1000 -500 V
-2000 -1000 W
WHERE [Link] = I .CHAINE ;
-5000 -2000 X
-10000 -5000 Y
indique, pour chaque ligne (p, v , x), que le produit p est disponible dans la
-32000 -10000 Z
ville v au prix x quelles que soient les magasins qui proposent p

Compléments sur les jointures Compléments sur les jointures

• Comment associer à chaque client le code de son compte ?

SELECT NCLI , NOM, CODE CPT

FROM CLIENT , CLASSE CPT
WHERE CAT = ’ C1 ’ • La construction d’une requête qui utilise une ou plusieurs jointures peut
AND COMPTE >= MIN CPT AND COMPTE < MAX CPT ; s’avérer délicate
• Il importe donc de bien comprendre ce que représente le résultat d’une
NCLI NOM CODE CPT
B112 HANSENNE D jointure
C123 MERCIER X
F010 TOUSSAINT G
L422 FRANCK G
S127 VANDERKA X
Compléments sur les jointures Compléments sur les jointures

• La règle relative à une jointure élémentaire fondée sur l’égalité

• La question est la suivante : sachant que toute ligne d’une table représente indentifiant/clé étrangère est simple
une entité du domaine d’application (un client, un achat, un détail, etc.) • Soit une table TA, d’indentifiant IA, et une table TB de clé éntrangère RA
quelles entités les lignes d’une jointure représentent-elles ? obligatoire référençant TA :
• TA (IA, DA)
• Par exemple, chaque ligne produite par l’évaluation de la requête :
• TB (IB, RA, DB)
SELECT C. NCLI , NOM, LOCALITE • Le résultat de la requête :
FROM CLIENT C, COMMANDE M
WHERE M. NCLI = C. NCLI ; SELECT *
FROM TA, TB
• représente-t-elle ? WHERE TA. IA = [Link] ;
1. un client
2. un client qui a passé une ou plusieurs commandes contient autant de lignes qu’il y en a dans la table TB. Autrement dit,
3. une commande chaque ligne du résultat d’une jointure représente une ligne de TB
• Réponse : des commandes • En bref : le résultat d’une jointure représente des entités de la table
contenant la clé étrangère

Compléments sur les jointures Compléments sur les jointures

• Ce que nous venons de discuter conduit à une autre règle : l’identifiant du

résultat de la jointure

SELECT *
FROM TA, TB
• Qu’en est il pour cette requête ? WHERE TA. IA = [Link] ;

SELECT [Link], DATECOM, NCLI est constitué des colonnes de l’identifiant primaire de TB (soit IB)
FROM COMMANDE, DETAIL • Si l’identifiant primaire de TB n’est pas répris dans la clause SELECT, le
WHERE [Link] = [Link] ; résultat n’a pas d’identifiant :

• Réponse : les lignes sont celles de la table DETAIL SELECT LOCALITE, LIBELLE
FROM CLIENT CLI , COMMANDE COM, DETAIL D, PRODUIT P
WHERE CLI . NCLI = COM. NCLI
AND [Link] [Link]
AND [Link] = [Link] ;
Extraction de données groupées Extraction de données groupées

• Considérons la table CLIENT. Il est permis d’y percevoir, virtuellement du

moins des groupes de clients selon leur localité, ou selon leur catégorie, ou
encore selon leur nom.

CLIENT
• Les requêtes examinées jusqu’à maintenant produisent des lignes qui sont NCLI NOM LOCALITE COMPTE

généralement une correspondance une pour une avec les lignes d’une table F400 JACOB Bruxelle 0

de la clause FROM B332 MONTI Genève 0

K111 VANBIST Lille 720
• Nous allons maintenant examiner comment il est possible d’extraire d’une
S127 VANDERKA Namur -4580
table, ou d’une jointure, des informations sur les concepts latents dans ces L422 FRANCK Namur 0
tables C123 MERCIER Namur -2300
B062 GOFFIN Namur -3200
S712 GUILLAUME Paris 0
F010 TOUSSAINT Poitiers 0
B112 HANSENNE Poitiers 1250
C400 FERARD Poitiers 350

Extraction de données groupées Extraction de données groupées

• Par exemple, la requête suivante donne, pour chaque groupe de clients • Des conditions de sélection peuvent être imposées aux groupes à
classés ou regroupés par localité, le nom de celle-ci, le nombre de clients sélectionner
dans le groupe et la valeur moyenne des comptes des clients du groupe
• Elles sont exprimées dans une clause HAVING pour éviter toute confusion
SELECT LOCALITE, avec la clause WHERE
COUNT(*) AS NOMBRE CLIENT, • Par exemple, la requête suivante donne le montant moyen des comptes des
AVG(COMPTE) AS MOYENNE COMPTE clients des villes en comptant au moins 3 :
FROM CLIENT
GROUP BY LOCALITE ; SELECT LOCALITE, COUNT(*) , AVG(COMPTE)
FROM CLIENT
• Le résultat est le suivant : GROUP BY LOCALITE
LOCALITE NOMBRE CLIENTS MOYENNE COMPTE HAVING count (*) >= 3 ;
Bruxelles 1 0.00
Genève 1 0.00
• Le résultat est le suivant :
Lille 1 720.00
Namur 4 -2520.00 LOCALITE COUNT(*) AVG(COMTE)
Paris 1 0.00 Poitiers 3 533.33
Poitiers 3 533.33 Namur 4 -2520.00
Toulouse 5 -2530.00 Toulouse 5 -2530.00
Extraction de données groupées Extraction de données groupées

• Exemple : on souhaite obtenir la liste des clients ayant commandé au

moins 2 fois le produit PA45
1. On regroupe les lignes de la table COMMANDE en les regroupant par
client :

SELECT NCLI , COUNT(*)

• Remarque : la condition exprimée par la clause HAVING peut porter sur les FROM COMMANDE
éléments cités dans la clause SELECT, mais aussi sur toute autre fonction GROUP BY NCLI ;
d’agrégation calculable sur chaque groupe 2. On ne retient ensuite que les groupes d’un moins 2 commandes :

SELECT NCLI , COUNT(*)

FROM COMMANDE
GROUP BY NCLI
HAVING COUNT(*) >= 2 ;

Extraction de données groupées Extraction de données groupées

• Il est également possible d’obtenir la quantité totale du produit PA45

3. Finalement, on ne considère, avant groupement, que les commandes
• Les données appartenant à plusieurs tables (NCLI et QCOM), il faut
spécifiant le produit PA45
e↵ectuer un groupement sur le résulat de la jointure de COMMANDE et
SELECT NCLI , COUNT(*) DETAIL
FROM COMMAND
SELECT NCLI , COUNT(*) , SUM(QCOM)
WHERE NCOM IN (SELECT NCOM
FROM COMMANDE M, DETAIL D
FROM DETAIL
WHERE [Link] = [Link]
WHERE NPRO = ’ PA45 ’ )
AND NPRO = ’ PA45 ’
GROUP BY NCLI
GROUP BY NCLI
HAVING COUNT(*) >= 2 ;
HAVING COUNT(*) >= 2 ;
Extraction de données groupées Extraction de données groupées

• Considérons un dernier exemple : Donner la quantité qui reste en stock si

• Considérons un autre exemple : Pour chaque client de Poitiers donner le on déduit les quantités commandées
montant total de ses commandes
SELECT [Link], QSTOCK - SUM([Link]) AS SOLDE
SELECT ’ Montant du p a r ’ , FROM DETAIL D, PRODUIT P
C. NCLI , WHERE [Link] = [Link]
’ = ’ , SUM(QCOM*PRIX) GROUP BY [Link], QSTOCK ;
FROM CLIENT C, COMMANDE M, DETAIL D, PRODUIT P
WHERE LOCALITE = ’ P o i t i e r s ’ • Le résultat est le suivant :
AND M. NCLI = C. NCLI NRPO SOLDE
CS262 -15
AND [Link] = [Link] CS464 -135
AND [Link] = P. NPRO PA45 535
GROUP BY M. NCLI ; PA60 -1
PH222 690
PS222 620

Extraction de données groupées Extraction de données groupées

• Remarque : Le critère de groupement peut aussi inclure une expression de

• Remarques : calcul quelconque
1. Le critère de groupement peut inclure plusieurs noms de colonne
2. L’ordre des colonnes est indi↵érent • Exemple : Donner la liste des clients en fonction de la première lettre de
leur catégorie
• Exemple : Donner pour chaque localité et produit le montant total
commandé SELECT SUBSTRING(CAT FROM 1 f o r 1) AS CAT,
COUNT(*) AS N
SELECT LOCALITE, [Link], SUMM(QCOM*PRIX) AS Montant
FROM CLIENT
FROM CLIENT C, COMMANDE M, DETAIL D, PRODUIT P
GROUP BY SUBSTRINB(CAT FROM 1 FOR 1) ;
WHERE M. NCLI = C. NCLI
AND [Link] = [Link] • Le résultat est le suivant :
AND [Link] = P. NPRO CAT N
GROUP BY LOCALITE, [Link] ; <null> 2
B 8
C 6
Extraction de données groupées Extraction de données groupées

• Un regroupement très intéressant serait de regrouper les clients selon leurs • L’extraction de données groupées est à définir avec précaution avec des
valeurs de COMPTE par intervalle de 1.000 jointures

SELECT ’ de ’ , i n t (COMPTE/1000)*1000 AS MIN, • Exemple : Donner pour chaque localité, la somme des comptes des clients
’ a ’ , i n t (COMPTE/1000)*1000 + 999 AS MAX, et le nombre de commandes. On serait tenté d’écrire :
COUNT(*) AS N SELECT LOCALITE, SUM(COMPTE) , COUNT(*)
FROM CLIENT C FROM CLIENT C, COMMANDE M
GROUP BY i n t (COMPTE/1000) ; WHERE C. NCLI = M. NCLI
GROUP BY LOCALITE ;
• Le résultat est le suivant :
de Min a Max N LOCALITE SUM(COMPTE) COUNT(*)
de -9000 a -8001 1 Lille 720.00 1
de -5000 a -4001 1 • Ce qui donnerait : Namur -4580.00 1
de -4000 a -3001 1 Poitiers 1050.00 3
de -3000 a -2001 2 Toulouse -8700.00 2
de -2000 a -1001 1
de 0 a 999 9 • Ce résultat, en apparence correct, est pourtant erroné (indépendemment
de 1000 a 1999 1 du fait que les clients sans commande ne sont pas repris)

Extraction de données groupées Extraction de données groupées

• Il est possible d’éviter la clause GROUP BY lorsque le concept latent dans

une table est explicitement représenté par une autre table, et que le
LOCALITE SUM(COMPTE) COUNT(*)
regroupement ne sert qu’à la sélection
Lille 720.00 1
Namur -4580.00 1 • Exemple : Donner les produits dont plus de 500 unités ont été
Poitiers 1050.00 3
commandées en 2009
Toulouse -8700.00 2
SELECT [Link]
• Le résultat de la jointure n’est pas des clients mais des commandes FROM DETAIL D, COMMANDE M
• Rappel : le résultat d’une jointure représente des entités de la table WHERE [Link] = [Link] AND DATECOM LIKE ’ %2009 ’
contenant la clé étrangère GROUP BY [Link] HAVING SUM(QCOM) > 500 ;
• En particulier, le compte du client CS400 est comptabilisé 3 fois, pour un est équivalente à
total de 1050 au lieu de 350
SELECT NPRO FROM PRODUIT P
• Le calcul de la somme des comptes s’e↵ectue donc sur des ensembles de
WHERE (SELECT SUM(QCOM) FROM DETAIL
commandes et non des clients
WHERE NPRO = [Link] AND NCOM IN
• Pour répondre correctement à la question il faut procéder en deux étapes (SELECT NCOM FORM COMMANDE
WHERE DATECOM LIKE ’ %2009 ’ )) > 500 ;
Ordre des lignes d’un résultat Ordre des lignes d’un résultat

• Donner la liste ordonnée par localité des clients (numéro, nom et localité)
de catégorie C1 et C2

SELECT NCLI , NOM, LOCALITE

• Par construction, l’ordre des lignes d’une table est arbitraire FROM CLIENT
WHERE CAT IN ( ’ C1 ’ , ’ C2 ’ )
• On ne peut pas supposer que les lignes sont stockées dans un ordre
ORDER BY LOCALITE ;
déterminé
• Par principe, l’ordre des lignes du résultat d’une requête est aussi arbitraire • On peut également indiquer plusieurs critères de tri :
• Il est possible d’imposer un ordre de présentation en utilisant la clause SELECT *
ORDER BY FROM CLIENT
ORDER BY LOCALITE, CAT ;

Les clients vont apparaı̂tre classés par localité, puis dans chaque localité
classés par catégorie. Attention l’ordre à une importance.

Ordre des lignes d’un résultat Ordre des lignes d’un résultat

• Si une expression apparaı̂t dans le SELECT, elle sera spécifiée par son nom
• Il est possible de modifier l’ordre utilisé pour le tri
• La clause ASC pour le tri ascendant • S’il s’agit d’une colonne avec un alias, elle sera spécifié par celui-ci
• La clause DESC pour le trie descendant • Exemple : Donner les localités par valeurs décroissantes de leur population
• Par défaut, le tri est ascendant de clients
• Par exemple : SELECT LOCALITE,
SELECT * COUNT(*) AS POPULATION,
FROM PRODUIT SUM(COMPTE)
WHERE LIBELLE LIKE ’%SAPIN% ’ FROM CLIENT
ORDER BY QSTOCK DESC ; GROUP BY LOCALITE
ORDER BY POPULATION DESC ;
Ordre des lignes d’un résultat Interprétation d’une requête

• Il est possible d’utiliser des critères qui ne sont pas dans la clause SELECT • Pour une requête monotable :
1. On considère la table spécifiée dans la clause FROM
• Exemple : 2. On sélectionne les lignes sur la base de la clause WHERE
SELECT NCOM, NPRO, QCOM 3. On classe ces lignes en groupes comme spécifié dans la clause GROUP BY
4. On ne retient que les lignes qui vérifient la clause HAVING
FROM DETAIL D, PRODUIT P 5. Les lignes des groupes sont ordonnées selon la clause ORDER BY
WHERE [Link] = [Link] éventuellement
ORDER BY NCOM, QCOM*PRIX DESC ; 6. De chacune des lignes, on extrait les valeurs demandées dans la clause
SELECT

Interprétation d’une requête Interprétation d’une requête

• À titre d’exemple, on indique, pour la requête suivante, l’ordre

• Pour une requête multitable : d’interprétation qui conduisent à l’élaboration du résultat :
1. On considère les tables spécifiées dans la clause FROM
2. On e↵ectue la jointure de ces tables selon le critère de jointure de la clause 7 : SELECT NCLI , COUNT(*) , SUM(QCOM)
WHERE 1 : FROM COMMANDE M, DETAIL D
3. On sélectionne les lignes de la jointure sur la base des autres conditions de 2 : WHERE [Link] = [Link]
la clause WHERE 3 : AND NPRO = ’ PA60 ’
4. On classe ces lignes en groupes comme spécifié dans la clause GROUP BY
4 : GROUP BY NCLI
5. On ne retient que les groupes qui vérifient la clause HAVING
6. Les lignes des groupes sont ordonnées selon la clause ORDER BY 5 : HAVING COUNT(*) > = 2
évetuellement 6 : ORDER BY NCLI
7. De chacune des lignes, on extrait les valeurs demandées dans la clause
SELECT • Remarque : Il s’agit d’une évaluation fictive, le SBGD utilisant
généralement d’autres procédés plus efficaces pour construire le résultat
Exercice 1 Exercice 1

1. Calculer le montant de chaque détail de commande du client ’C400’

SELECT [Link], [Link], QCOM*PRIX AS MONTANT

FROM COMMANDE M, DETAIL D, PRODUIT P
WHERE [Link] = [Link] AND [Link] = [Link] AND NCLI = ’ CS400 ’ ;

• Exprimer les requêtes suivantes en SQL : 2. Calculer le montant commandé des produits en sapin

1. Calculer le montant de chaque détail de commande du client ’C400’ SELECT SUM(QCOM*PRIX) AS MONTANT
2. Calculer le montant commandé des produits en sapin FROM DETAIL D, PRODUIT P
3. Afficher le total et la moyenne des comptes clients, ainsi que le nombre de WHERE [Link] = [Link] AND P. LIBELLE LIKE ’%SAPIN% ’ ;
clients, selon chacune des classifications suivantes : (1) par catégorie, (2)
3. Afficher le total et la moyenne des comptes clients, ainsi que le nombre de clients, selon
par localité, (3) par catégorie dans chaque localité
chacune des classifications suivantes : (1) par catégorie, (2) par localité, (3) par catégorie
dans chaque localité

SELECT LOCALITE, CAT, SUM(COMPTE) , AVG(COMPTE) , COUNT(*)

FROM CLIENT
GROUP BY LOCALITE, CAT ;

Exercice 2 Exercice 2

1. Combien y a-t-il de commandes spécifiant un (ou plusieurs) produit(s) en acier ?

SELECT COUNT(DISCTINCT [Link])

FROM COMMANDE M, DETAIL D, PRODUIT P
WHERE [Link] = [Link] AND [Link] = [Link]
AND LIBELLE LIKE ’%ACIER% ’ ;

2. Calculer le montant dû par chaque client. Dans le calcul, on ne prendra en compte que le
• Exprimer les requêtes suivantes en SQL : montant des commandes.
1. Combien y a-t-il de commandes spécifiant un (ou plusieurs) produit(s) en
SELECT NCLI , SUM(QCOM*PRIX) FROM COMMANDE M, DETAIL D, PRODUIT P
acier ? WHERE [Link] = [Link] AND [Link] = [Link] GROUP BY NCLI
2. Calculer le montant dû par chaque client. Dans le calcul, on ne prendra en UNION
compte que le montant des commandes SELECT NCLI , 0 FROM CLIENT C
3. Afficher pour chaque localité, les libellés des produits qui y sont commandés WHERE NOT EXISTS (SELECT * FROM COMMANDE WHERE NCLI = C. NCLI ) ;

3. Afficher pour chaque localité, les libellés des produits qui y sont commandés

SELECT LOCALITE, LIBELLE

FROM CLIENT C, COMMANDE M, DETAIL D, PRODUIT P
WHERE C. NCLI = M. NCLI AND [Link] = [Link] AND [Link] = [Link]
GROUP BY LOCALITE, LIBELLE ORDER BY LOCALITE, LIBELLE
Exercice 3 Exercice 3

1. Les matières premières (produit qui n’ont pas de composants)

SELECT NPRO FROM PRODUIT

WHERE NPRO NOT IN (SELECT COMPOSE FROM COMPOSITION) ;
Soit le schéma relationnel suivant :
2. Les produit finis (qui n’entre dans la composition d’aucun autre)

• PRODUIT(NPRO, LIBELLE, PRIX U, POIDS U) SELECT NPRO FROM PRODUIT

WHERE NPRO NOT IN (SELECT COMPOSANT FROM COMPOSITION) ;
• COMPOSITION(COMPOSE, COMPOSANT, QTE)
3. Les produit semi-finis (tous les autres)
Exprimer en SQL les requêtes suivantes : SELECT NPRO FROM PRODUIT
WHERE NPRO IN (SELECT COMPOSE FROM COMPOSITION)
1. Les matières premières (produit qui n’ont pas de composants) AND NPRO IN (SELECT COMPOSANT FROM COMPOSITION) ;
2. Les produit finis (qui n’entre dans la composition d’aucun autre) 4. Le prix et le poids unitaires d’un produit fini ou semi-fini dont tous les composants ont un
3. Les produit semi-finis (tous les autres) poids et un prix unitaires

SELECT [Link] SUM(QTE*PN. PRIX U ) , SUM(QTE*[Link] U)

4. Le prix et le poids unitaires d’un produit fini ou semi-fini dont tous les
FROM PRODUIT PH, COMPOSITION C, PRODUIT PB
composants ont un poids et un prix unitaires WHERE [Link] = [Link] AND [Link] = [Link]
AND NOT EXISTS (SELECT * FROM COMPOSITION CC, PRODUIT BB
WHERE [Link] = [Link] AND [Link] = [Link]
AND (NN. PRIX U i s n u l l o r [Link] U i s n u l l ) ;