Manipulation des données dans SQL

Langage de manipulation de données

LMD

1
 Langage de Manipulation de Données (LMD)

Les commandes du LMD sont :

– SELECT : sélection (Projection) de la liste des
colonnes que l’on veut obtenir.

– INSERT (à voir en TP) : ajoute des lignes à une table.

– UPDATE (à voir en TP) : mettre à jour (modification)

d’une ou plusieurs colonnes d’une ou plusieurs lignes.

– DELETE (à voir en TP): suppression d’une ou

plusieurs lignes. 2
 Consultation de données
SQL est basé sur l'utilisation de blocks dont la structure est composée
principalement de trois éléments :

•SELECT : permet de définir la liste des colonnes que l'on peut obtenir.

•FROM : n'indique que les noms des tables nécessaires pour obtenir le résultat
souhaité.

•WHERE : définit la condition que doit vérifier un tuple donné pour qu'il fasse
partie du résultat.

Syntaxe :
SELECT col1, col2, … ,coln
FROM nom_table
[WHERE condition];
3
 Consultation de données

Syntaxe :
SELECT col1, col2, … ,coln
FROM nom_table
[WHERE condition];

Remarque
•Si on souhaite avoir toutes les colonnes de la table: SELECT * FROM ...
•Si on souhaite avoir toutes les lignes de la table, on ne met pas la clause WHERE.

4
 Exemple
• On considère la table Produit suivante :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

80 Disquette 20 24 4 125

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

1-Afficher les numéros et désignations de tous les produits:

• Select numprod, desprod

From produit;
5
 Exemple
• On considère la table Produit suivante :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

80 Disquette 20 24 4 125

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

2-Afficher les numéros et désignations de produits existants en stock avec une quantité > 20

Select numprod, desprod

From produit
Where qte_stk >20; 6
 Exemple
• On considère la table Produit suivante :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

80 Disquette 20 24 4 125

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

3-Afficher les produits existants en stock avec une quantité > 20

Select *
From produit
Where qte_stk > 20; 7
 Exemple
• On considère la table Produit suivante :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

80 Disquette 20 24 4 125

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

4-Afficher les couleurs des différents produits :

Select distinct couleur

From produit;
8
 Notion d’alias
Chaque fois qu'une requête d'interrogation est exécutée:
•les noms des colonnes définies dans la structure de la table
apparaissent comme titres de colonnes dans le résultat.

•On peut modifier ces noms de colonnes, à l'affichage

uniquement, en ajoutant des ALIAS.

•Si l’alias est composé de plusieurs mots il faut qu’il apparaisse

entre double quote: " "

•La commande devient :

SELECT col1 [alias 1], … , coln [alias n]
FROM nom_table
[WHERE condition];
9
 Notion d’alias
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

80 Disquette 20 24 4 125

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

• Exemple:
Afficher les numéros et désignations de tous les produits.
Les titres des colonnes à afficher seront respectivement:
Numéro et "Nom produit"

• Solution:
Select numprod Numéro, desprod "Nom produit"
From Produit; 10
 Spécification d’un critère dans une requête
• La condition est composée généralement de trois termes :
– un nom de colonne,
– un opérateur de comparaison,
– et une constante, une colonne, une liste de valeurs, une expression
ou une requête.

En plus des opérateurs de comparaison ( = , <> , < , > , >= , <= )

SQL dispose d'un certain nombre d'opérateurs spécifiques :

IS NULL : permet de tester si le contenu d'une colonne est

une valeur nulle (indéfinie).
Exemple :
Afficher les numéros de produits dont la couleur n’a pas été
saisie:
SELECT * FROM produit WHERE couleur is NULL;
11
 Spécification d’un critère dans une requête
• La condition est composée généralement de trois termes :
– un nom de colonne,
– un opérateur de comparaison,
– et une constante, une colonne, une liste de valeurs, une expression
ou une requête.

SQL dispose d'un certain nombre d'opérateurs spécifiques :

IN (liste de valeurs): permet de tester si le contenu d'une

colonne coïncide avec l'une des valeurs de la liste.
Exemple :
Afficher les produits de couleur Rouge, Bleu ou Gris:

SELECT * FROM produit WHERE couleur IN ('R', 'B', 'G');

12
 Les expressions agrégats
(les fonctions d’agrégation)

13
ORACLE dispose de fonctions agrégats qui s'appliquent à
un ensemble de données :

SELECT Fonction_agrégat( colonne ) ...

FROM ...
[WHERE ...] ;

avec Fonction_agrégat = SUM, AVG, MIN, MAX, COUNT,…

SUM : SOMME
AVG : MOYENNE (AVERAGE)
MIN : MINIMUM
MAX : MAXIMUM

COUNT : COMPTER, NOMBRE

14
 La fonction COUNT

COUNT : permet de compter le nombre de lignes

que pourrait retourner une requête
SELECT).

Donner le nombre de produits de couleurs rouge:

SELECT Count(*)
FROM Produit
Where Couleur = 'R';

15
COUNT : permet de compter le nombre de lignes
que pourrait retourner une requête
SELECT).

Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

Donner le nombre de produits de couleurs rouge:

SELECT COUNT(*)
FROM Produit
Where Couleur = 'R';
16
 Les expressions agrégats
SUM : permet d'avoir la somme des éléments.

Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

Afficher la quantité totale en stock des produits de couleur Rouge

SELECT SUM(Qte_stk)
FROM Produit
Where Couleur = 'R';
17
 Les expressions agrégats
AVG : permet d'avoir la moyenne arithmétique d'un
ensemble donné.

Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

Afficher la quantité moyenne en stock des produits de couleur Rouge

SELECT AVG(Qte_stk)
FROM Produit
Where Couleur = 'R' 18
 Les expressions agrégats

– MAX : permet d'avoir la valeur maximale dans une colonne.

– MIN : permet d'avoir la valeur minimale dans une colonne.

Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

200 Souris G 30 12 5 200.450

102 Tapis R 0.125 10 5 10

Afficher les quantités minimale et maximale des produits en stock

SELECT MAX (Qte_stk) , MIN (Qte_stk)

FROM Produit;
19
Fonctions agrégats : SUM, AVG, MIN, MAX, COUNT,…

•Chacune de ces fonctions a comme argument un nom de colonne :

– elles ignorent les valeurs nulles

•Pour prendre en compte uniquement les valeurs distinctes,

il faut ajouter l'opérateur DISTINCT

•La fonction COUNT peut prendre comme argument le caractère *

pour connaître le nombre total des lignes sélectionnées: COUNT(*)
.

20
Regroupements de lignes

(Agrégats appliquées
par groupe de lignes )

21
 Regroupements de lignes
• Il est possible de partitionner (classifier) les résultats d’une requête:
– en regroupant les lignes ayant une même valeur pour la colonne de
partitionnement.

• Le regroupements de lignes permet d'effectuer un calcul

d’agrégats des valeurs de chaque groupe:
– et de présenter le résultat sous la forme d'une ligne par groupe.

Sous totaux

Groupes

22
• Un groupe est un sous-ensemble des tuples d'une table ayant la même
valeur pour un attribut (ou plusieurs attributs).
• Un groupe est déterminé par la clause GROUP BY suivie du nom de
l'attribut(s) sur lequel s’éffectue le regroupement.
• La clause GROUP BY réarrange la table résultat d'un SELECT par groupe
• Lorsqu'une clause GROUP BY est précisée, on peut utiliser des fonctions
agrégats portant sur les groupes.

Exemple:
Afficher la quantité totale de produits par couleur:
SELECT couleur, SUM(Qte_stk)
FROM produit
GROUP BY couleur;
couleur Qte_stk
R 10 couleur SUM(Qte_stk)
B 20
Utilisation du GROUP BY -> R 35
G 20
B 55
B 35
G 20
R 25 23
 Regroupements de lignes

Le regroupement de lignes en fonction des valeurs d'un ou plusieurs

colonnes de partitionnement,
suivi de l'application de fonctions de calcul d’agrégats sur chaque groupe
s'exprime en SQL comme suit :

SELECT colonnes_de_partitionnement, Fonction_agrégat(colonne) ,…

FROM ...
[WHERE ...]
GROUP BY colonnes_de_partitionnement

(avec Fonction_agrégat = SUM, AVG, MIN, MAX, COUNT, …)

24
Le regroupements de lignes en fonction des valeurs d'un ou plusieurs
colonnes de partitionnement,
suivi de l'application de fonctions de calcul d’agrégats sur chaque groupe
s'exprime en SQL comme suit sur un exemple :

SELECT idSer, AVG(salaire), COUNT(*)

FROM EMPLOYE
GROUP BY idSer;

idEmp nomEmp idSer salaire

100 Michel 20 2000
200 Sylvie 10 3000
300 Bernard 20 1000
400 Claude 10 2000 ???
500 Thomas 10 1000

25
 Résultat du groupage (GROUP BY) et
du calcul d’agrégats sur chaque groupe

SELECT idSer, AVG(salaire), COUNT(*)

FROM EMPLOYE
GROUP BY idSer;

idEmp nomEmp idSer salaire

100 Michel 20 2000
200 Sylvie 10 3000
300 Bernard 20 1000 idSer AVG(salaire) COUNT(*)
----- ------------ --------
400 Claude 10 2000 10 2000 3
20 1500 2
500 Thomas 10 1000

26
SELECT Colonnes_de_partitionnement, Fonction_agréga(Attribut)
FROM ...
[WHERE ...]
GROUP BY Colonnes_de_partitionnement
…

• Tous les colonnes de la clause SELECT sans fonction

d'agrégation doivent figurer dans la clause GROUP BY.

• Seules des expressions composées de fonctions agréga

peuvent exister en plus dans le SELECT.

27
Les étapes d’évaluation du WHERE + Group By par le SGBD Oracle

TABLE contenant
TABLE uniquement les
WHERE lignes filtrés

Création des groupes

suivant les attributs
du GROUP BY

SELECT TABLE contenant

Résultat uniquement les lignes filtrés
et partitionnée en Groupes

28
 Exercice
• On considère la table Produit suivante :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur B 152 5 2 1000.275

80 Disquette B 20 24 4 125

200 Ordinateur G 300 42 5 2000.450

102 Ordinateur B 125 40 5 1000
200 Souris G 30 12 5 200.450
200 Souris B 30 12 5 200.450

Écrire une Requête SQL

permettant d’afficher la quantité totale des ordinateurs par couleur ?
…

29
 Solution de l’exercice utilisant WHERE + Group By

TABLE contenant
TABLE uniquement les
WHERE lignes filtrés

Création des groupes

suivant les attributs
du GROUP BY

SELECT TABLE contenant uniquement

Résultat les lignes filtrés et
partitionnée en Groupes

Requête pour afficher la quantité totale des ordinateurs par

couleur:
SELECT couleur, SUM(Qte_stk)
WHERE Desprod = ‘Ordinateur’
FROM produit
GROUP BY couleur;
30
 Solution de l’exercice utilisant WHERE + Group By

Requête pour afficher la quantité totale des ordinateurs par

couleur:
SELECT couleur, SUM(Qte_stk)
WHERE Desprod = ‘Ordinateur’
FROM produit
GROUP BY couleur;

Résultat :
couleur SUM(Qte_stk)

B 45
G 42

31
Exercice avec plusieurs colonnes de partitionnement

Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Souris R 15.2 3 2 100.275

200 Souris B 30 12 5 200.450

102 Souris R 0.125 10 5 10
103 Ordinateur B 60 120 5 300.50

• Afficher le nombre de produits regroupés par couleur et désignation

???

32
Solution avec plusieurs colonnes de partitionnement

• Afficher le nombre de produits regroupés par couleur et désignation

SELECT Desprod, couleur, COUNT(*)
FROM produit
Group by Desprod, couleur;

Résultat :
Desprod Couleur COUNT(*)

Souris R 2

Souris B 1
Ordinateur B 1

33
SELECT Colonnes_de_partitionnement, Fonction_agréga(Attribut)
FROM ...
[WHERE ...]
GROUP BY Colonnes_de_partitionnement
…

La clause WHERE peut porter:

• sur les attributs de la clause SELECT (agrégés ou non)

• ainsi que sur des attributs non affichés.

• il est interdit d’utiliser des fonctions d'agrégat dans

une clause WHERE
(une condition sur un agrégat s’écrit à l’aide de la clause
HAVING, voir la suite)
34
 Rajouter des
conditions sur les groupes
(clause HAVING ) :

35
 Restriction sur les groupages : clause HAVING

Pour effectuer une sélection de groupes,

il faut rajouter des conditions sur les groupes dans
une clause HAVING (conditions) :

SELECT Attributs_de_partitionnement, Fonction_agréga(Attribut)

FROM ...
[WHERE ...]
GROUP BY Attributs_de_partitionnement

HAVING (Conditions_de_séléction_sur_le_groupement)

36
Les étapes d’évaluation du WHERE + Group By + HAVING
par le SGBD Oracle :

TABLE TABLE contenant les

lignes filtrées
WHERE
Création de groupes
suivant les attributs du
GROUP BY

TABLE TABLE contenant les

Groupes lignes filtrées et
filtrés partitionnées en
HAVING Groupes

SELECT

Résultat

37
Restriction sur les groupages :
HAVING
SELECT idSer, AVG(salaire), COUNT(*)
FROM EMPLOYE
GROUP BY idSer
HAVING (COUNT(*) > 2);
restriction
idEmp nomEmp
idEmp nomEmp idSer
idSer salaire
salaire du groupage 20

100 Michel 20 2000

200 Sylvie 10 3000
300 Bernard 20 1000
400 Claude 10 2000 ???
500 Thomas 10 1000
38
Restriction sur les groupages :
HAVING
SELECT idSer, AVG(salaire), COUNT(*)
FROM EMPLOYE
GROUP BY idSer
HAVING COUNT(*) > 2;
restriction
idEmp nomEmp
idEmp nomEmp idSer
idSer salaire
salaire du groupage 20

100 Michel 20 2000

200 Sylvie 10 3000
300 Bernard 20 1000 idSer AVG(salaire) COUNT(*)
----- ------------ --------
400 Claude 10 2000 10 2000 3
500 Thomas 10 1000
39
 Restriction sur les groupages : clause HAVING

SELECT Attributs_de_partitionnement, Fonction_agréga(Attribut)

FROM ...
[WHERE ...]
GROUP BY Attributs_de_partitionnement
HAVING (Conditions_de_séléction_sur_le_groupement)

Les conditions utilisant des agrégats s’écrivent dans la clause HAVING:

il faut utiliser au moins un agrégat dans une clause HAVING

40
 Exemple
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur G 15.2 3 2 100.275

200 Souris B 30 45 5 200.450

102 Tapis G 0.125 39 5 10
104 Souris R 0.125 10 5 10

• Afficher la quantité totale des produits par couleur, si cette

quantité est supérieure à 40 :
???

41
 Solution
• Afficher la quantité des produits par couleur, si cette quantité est
supérieure à 40 :
SELECT couleur, sum(Qte_stk) "Qte totale par couleur"
FROM produit
Group by couleur;
Having sum (Qte_stk) >= 40;

• Résultat:
Couleur Qte totale par
couleur
B 45
G 42

42
 Exemple
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur G 15.2 3 2 100.275

200 Souris B 30 45 5 200.450

102 Tapis G 0.125 39 5 10
104 Souris R 0.125 10 5 10

• Afficher les couleurs ayant une quantité totale supérieure à 40 :

SELECT couleur
FROM produit
Group by couleur;
Having sum (Qte_stk) >= 40;

43
 Solution
• Afficher la quantité des produits par couleur, si cette quantité est
supérieure à 40 :
SELECT couleur
FROM produit
Group by couleur;
Having sum (Qte_stk) >= 40;

• Résultat:
Couleur

B
G

44
 Exercice
• On considère la table Produit suivante :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur B 152 3 2 1000.275

80 Disquette R 20 24 4 125

200 Ordinateur G 300 22 5 2000.450

102 Ordinateur B 125 38 5 1000
200 Souris G 30 12 5 200.450
200 Souris B 30 12 5 200.450

Écrire une Requête SQL

permettant d’afficher la quantité totale des ordinateurs par couleur,
si cette quantité est supérieure à 40 ou strictement inférieur à 30 ?
…
45
 TABLE TABLE contenant les
lignes filtrées
WHERE
Création de groupes
suivant les attributs du
GROUP BY

TABLE
Groupes TABLE contenant les
filtrés lignes filtrées
partitionnées en
HAVING Groupes

SELECT
Résultat
• Afficher la quantité des ordinateurs par couleur, si cette quantité est
supérieure à 40 ou strictement inférieur à 30 :
SELECT couleur, sum(Qte_stk) "Qte Ordinateurs par
couleur"
FROM produit
WHERE Desprod = ‘Ordinateur’
Group by couleur;
46
 Solution utilisant WHERE + Group By + HAVING
• Afficher la quantité des ordinateurs par couleur, si cette quantité est
supérieure à 40 ou strictement inférieur à 30 :
SELECT couleur, sum(Qte_stk) "Qte Ordinateurs par couleur"
FROM produit
WHERE Desprod = ‘Ordinateur’
Group by couleur;
Having ( (sum (Qte_stk) >= 40) OR (sum (Qte_stk) < 30) );

• Résultat : Couleur Qte Ordinateurs

par couleur

B 41
G 22

47
Requêtes multi-tabulaires

48
 Requêtes multi-tabulaires
• Dans la majorité des cas, on doit combiner des
informations venant de plusieurs tables.

• On peut utiliser plusieurs tables en même temps en le

précisant dans la clause From.

• On utilise la notation [Link] pour différencier les

colonnes qui porte le même nom

49
 Syntaxe

SELECT [DISTINCT | ALL] { * | liste de colonnes}

[ FROM liste de tables ]
[ WHERE prédicat ]
[ GROUP BY liste des colonnes du groupage ]
[ HAVING prédicat ]
…

50
 Produit cartésien
de plusieurs tables

51
 Rappel : Produit cartésien de deux Tables
Le produit cartésien de deux tables R1 et R2 :
• est une table R définie sur l'union (la concaténation)
des colonnes de R1 et de R2.

• Les lignes de R sont toutes les concaténations

d'une ligne de R1 à une ligne de R2.

R = Produit ( R1, R2 )

R1  R2

En SQL : SELECT * FROM R1, R2 ;

52
Exemple de Produit cartésien de deux Tables

SELECT * FROM R1, R2 ;

A1 A2 B1 B2 B3
R1 R2 a 17 16 10 96
A1 A2
B1 B2 B3


a 17 22 11 69
a 17
16 10 96 b 10 16 10 96
b 10
22 11 69 b 10 22 11 69
c 58
c 58 16 10 96
c 58 22 11 69

53
 Produit cartésien
de plusieurs tables
SELECT liste_de_colonnes
FROM table1 , … , tablen

Le produit cartésien plusieurs tables table1 , … , tablen:

• est une table définie sur l'union (la concaténation) des
colonnes de table1 , table2 , table3 , … … … tablen.

• Les lignes du Résultat sont toutes les concaténations

possibles d'une ligne de table1 , d’une ligne de table2 ,
d’une ligne de table3 … … ... et d’une ligne de tablen
54
 Rappel : Jointure de deux Tables

La jointure de deux relations R1 et R2 selon une condition de

jointure est l'ensemble des lignes du produit cartésien R1  R2
satisfaisant à cette condition de jointure.

Exemple: SELECT * FROM R1, R2 WHERE R1.A2 > R2.B2;

R1 R2 A1 A2 B1 B2 B3
A1 A2
B1 B2 B3 a 17 16 10 96
a
b
17
10
16 10 96  a 17 22 11 69

22 11 69 c 58 16 10 96
c 58
c 58 22 11 69

55
Jointure de plusieurs tables

56
 Jointure de plusieurs tables
• La jointure permet de combiner des lignes issues à partir de
plusieurs tables

• Les colonnes utilisées pour faire la jointure doivent être de même

type et de même taille et sont appelés colonnes de jointure.

• La forme générale d'une requête de jointure est :

SELECT col1 , col2 , …. , colm
FROM table1 , … , tablen
WHERE condition de jointure ;
57
 Les étapes du calcul d’une
jointure par un SGBD
1. Commencer avec le produit cartésien de toutes les
tables citées dans la clause FROM.

1. Appliquer une sélection sur le résultat avec la condition

de la clause WHERE.

1. Projeter le résultat de l’étape 2 sur les attributs de la

clause SELECT.

58
 La jointure simple
•Pour effectuer une jointure, il faut spécifier :
– Les noms des tables, dans la clause FROM, séparés par des
virgules.
– La condition de jointure dans la clause WHERE.
– Les noms de colonnes doivent être préfixés par les noms de tables
pour éviter toute ambiguïté.

•Rapprochement de 2 tables via des attributs communs:

SELECT col1 , col2 , …. , colm
FROM Table1, Table2
WHERE [Link] = [Link]
… ;
59
 Exemple
Magasin (Nummag, adresse, Surface)
Produit (Numprod, Desprod, …, Nummag*)

• Afficher les numéros des produits ainsi que leur adresse

de stockage:
Select Numprod, adresse
From Produit, Magasin
Where [Link] = [Link];

60
 Exercice
En utilisant les tables:
CLIENT(nclt, nom, age, adresse) et
COMMANDE(nclt, numprod, qte , date),
trouver la liste des numprods commandés par le client ‘Isimm’ ?

61
 Solution
En utilisant les tables:
CLIENT(nclt, nom, age, adresse) et
COMMANDE(nclt, numprod, qte , date),
trouver la liste des numprods commandés par le client ‘Isimm’ ?

SELECT numprod
FROM Client, Commande
WHERE nom = ‘Isimm’ AND
[Link] = [Link]

62
 Exemple avec des variables ALIAS

Magasin (Nummag, adresse, Surface)

Produit (Numprod, Desprod, Couleur, … Nummag#)

• Afficher les adresses des magasins contenant le

produit numéro 100:
Select adresse
From produit p, magasin m
Where [Link] = [Link]
And [Link] = 100;

63
 Auto-jointure

(table jointe avec elle-même)

64
 Auto-jointure
(table jointe avec elle-même)

• Une Auto-jointure consiste à faire une jointure

d'une table avec elle-même

• Pour cela il faut utiliser des variables ALIAS:

SELECT listeAttributs
FROM Table alias1, Table alias2
WHERE ([Link] = [Link]) … ... ...;

65
 Exercice
Magasin (Nummag, adresse, Surface)
Produit (Numprod, Desprod, …., prix, Nummag#)

Afficher la désignation et le prix des produits qui

coûtent plus cher que le produit ‘ordinateur’ ?

66
 Solution (Auto-jointure)
Magasin (Nummag, adresse, Surface)
Produit (Numprod, Desprod, Couleur, Poids, Qte_stk,
Qte_seuil; prix, Nummag#)

Afficher la désignation et le prix des produits qui

coûtent plus cher que le produit ‘ordinateur’.

Solution avec ALIAS :

Select [Link], [Link]

From produit p1, produit p2
Where [Link] = 'ordinateur' and [Link] > [Link];
67
 Exercice : Auto-jointure
En utilisant les tables:
CLIENT(nclt, nom, age, adresse) et
COMMANDE(nclt*, numprod, qte , date),

Trouver les paires de nclts qui ont commandé un même produit

?

68
 Exercice : Auto-jointure (suite)
En utilisant les tables:
CLIENT(nclt, nom, age, adresse) et
COMMANDE(nclt, numprod, qte , date),

Trouver les paires de nclts qui ont commandé un même produit

?

SELECT [Link], [Link]

FROM Commande c1, Commande c2
WHERE [Link] = [Link]

Problème :
Exemple de Réponse (42, 42) (42, 57) (57,42) … 69
 Exercice Auto-jointure : Solution
Trouver les paires de nclts qui ont commandé un
même produit ?

Problème :
Exemple de Réponse (42, 42) (42, 57) (57,42) …

Solution :
SELECT [Link], [Link]
FROM Commande c1, Commande c2
WHERE [Link] = [Link] AND
[Link] < [Link]
70
JOINTURE + WHERE +
GROUP BY…

71
 Exercice (JOINTURE + WHERE + GROUP BY…)
Produit (numprod, Desprod, Couleur, …)
CLIENT(nclt, nom, age, adresse) et
COMMANDE(nclt*, numprod*, qte , date),

Calculer la liste des clients (nclt)

ayant commandés 2 produits rouges différents,
en affichant le total des quantités commandées par
chaque client ?
………

72
 Produit (numprod, Desprod, couleur, …)
CLIENT(nclt, nom, age, adresse) et
COMMANDE(nclt, numprod, qte , date),

Calculer la liste des clients (nclt) ayant

commandés 2 produits rouges différents,
en affichant le total des quantités commandées par
chaque client ?

SELECT nclt, SUM(quantite)

FROM Commande c , Produit p
WHERE ([Link] = [Link]) AND (couleur = ‘R’)
GROUP BY nclt
HAVING COUNT(DISTINCT numprod) = 2

73
Les requêtes imbriquées

74
 Les requêtes imbriquées
(sous-requêtes)

Syntaxe :
SELECT …
FROM …
WHERE (col1, …, coln ) opérateur ( SELECT col1,…, coln
FROM . . .
WHERE . . . ) ;

• Les requêtes imbriquées (ou sous-requêtes) sont des requêtes à

l'intérieur d'autres requêtes

75
 Les requêtes imbriquées
(sous-requêtes)

Syntaxe :
SELECT …
FROM …
WHERE (col1, …, coln ) opérateur ( SELECT col1,…, coln
FROM . . .
WHERE . . . ) ;

• Les requêtes imbriquées (ou sous-requêtes) sont des requêtes à

l'intérieur d'autres requêtes

• Ceci se produit lorsque la clause WHERE contient une sous-requête.

76
 Les requêtes imbriquées
(sous-requêtes)

Syntaxe :
SELECT …
FROM …
WHERE (col1, …, coln ) opérateur ( SELECT col1,…, coln
FROM . . .
WHERE . . . ) ;

• Les requêtes imbriquées (ou sous-requêtes) sont des requêtes à

l'intérieur d'autres requêtes

• Le résultat d’une sous-requête est utilisé pour répondre à la requête

de niveau supérieur

77
 Les requêtes imbriquées simples

Syntaxe :
SELECT …
FROM …
WHERE (col1, …, coln ) opérateur ( SELECT col1,…, coln
FROM . . .
WHERE . . . ) ;

• Requêtes imbriquées simples :

– la requête interne est indépendante de la requête externe
– la requête interne est complètement évaluée avant la requête
externe qui utilisera ensuite le résultat.
78
 Les requêtes imbriquées simples
(Exemple)
Syntaxe :
SELECT …
FROM …
WHERE (col1, …, coln ) opérateur ( SELECT col1,…, coln
FROM . . .
WHERE . . . ) ;

Exemple
Afficher les numéros des produits de même couleur que le produit numéro 100 :

SELECT numprod
FROM produit
Where couleur = (SELECT couleur
FROM produit
WHERE numprod = 100);
79
 Rappel : Requêtes imbriquées simples

Syntaxe :
SELECT …
FROM …
WHERE (col1, …, coln ) opérateur ( SELECT col1,…, coln
FROM . . .
WHERE . . . ) ;

• Requêtes imbriquées simples :

– la requête interne est indépendante de la requête externe
– la requête interne est complètement évaluée avant la
requête externe qui utilisera ensuite le résultat.
80
Requêtes imbriquées
synchronisées

81
 Requêtes imbriquées synchronisées :
Une sous-requête est synchronisée si elle manipule des
colonnes d'une table du niveau supérieur

La forme générale d'une sous-requête synchronisée est la suivante :

Les alias des tables sont utiles pour pouvoir manipuler des colonnes
de tables de différents niveaux.
82
 Requêtes imbriquées synchronisées
La forme générale d'une sous-requête synchronisée est la suivante :

Très important:
Une sous-requête synchronisée est exécutée (par le SGBD) une fois
pour Les alias ligne
chaque des tables sontpar
extrait utiles pour pouvoir
la requête manipuler
de niveau des colonnes
supérieur
de tables de différents niveaux.
83
 Exemple de Requêtes imbriquées synchronisées :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100

200 Souris R 30 12 5 20
300 Souris G 30 12 5 25
102 Tapis R 0.125 40 5 100

Requête: Pour chaque couleur, afficher le(s) produit(s) dont le

prix est supérieur aux autres produits de même couleur

Résultat attendu:
…?

84
 Exemple de Requêtes imbriquées synchronisées :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100

200 Souris R 30 12 5 20
300 Souris G 30 12 5 25
102 Tapis R 0.125 40 5 100

Requête: Pour chaque couleur, afficher le(s) produit(s) dont le

prix est supérieur aux autres produits de même couleur

Résultat attendu:
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100

300 Souris G 30 12 5 25
102 Tapis R 0.125 40 5 100
85
 Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100

200 Souris R 30 12 5 20
300 Souris G 30 12 5 25
102 Tapis R 0.125 40 5 100

SELECT *
FROM PRODUIT Alias1
WHERE [Link] >= (SELECT MAX([Link])
FROM PRODUIT Alias2
WHERE [Link] =
[Link] );
Résultat :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100

300 Souris G 30 12 5 25
102 Tapis R 0.125 40 5 100
86
SELECT *
FROM PRODUIT Alias1
WHERE [Link] >= (SELECT MAX([Link])
FROM PRODUIT Alias2
WHERE [Link] =
[Link] );

Très important:
Une sous-requête synchronisée est exécutée (par le SGBD) une fois
pour chaque ligne extrait par la requête de niveau supérieur

Résultat :
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100

300 Souris G 30 12 5 25
102 Tapis R 0.125 40 5 100
87
 Exercice (Requêtes imbriquées synchronisées) :

Exercice:
Afficher les avions dont le nombre d'heures de vol (nbHVol) est supérieur au
nombre d'heures de vol moyen des avions de leur compagnie ?

Rappel :
Une sous-requête synchronisée est exécutée (par le SGBD) une fois
pour chaque ligne extrait par la requête de niveau supérieur

88
 Solution : Requêtes imbriquées synchronisées

compagnie

Afficher les avions dont le nombre d'heures de vol (nbHVol) est supérieur au
nombre d'heures de vol moyen des avions de leur compagnie:

SELECT *
FROM Avions A1
WHERE [Link] > ( SELECT AVG([Link])
FROM Avions A2
WHERE [Link] = [Link]
);
89
Exercice 2 : Requêtes imbriquées synchronisées

Magasin (Nummag, ville, …)

Produit (Numprod, Desprod ,…, prix, Nummag#)

Afficher la désignation et le prix des produits qui

coûtent plus cher que tous les autres produits
vendus dans la même ville ?

90
Les opérateurs multilignes
(Sous-requête avec plusieurs lignes)

91
 Les requêtes imbriquées
(sous-requêtes)
Syntaxe :
SELECT …
FROM …
WHERE (col1, …, coln ) opérateur ( SELECT col1,…, coln
FROM . . .
WHERE . . . ) ;

• La sous-requête peut retourner des données depuis une seule ou

plusieurs lignes

• La sous-requête est typiquement codée comme partie d'un WHERE :

– utilisant " l’opérateur = " lorsqu'elle retourne une seule valeur
– ou utilisant " l’opérateur IN " lorsqu'elle retourne plusieurs
valeurs. 92
 Exemple
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

200 Ordinateur G 30 12 5 200.450

102 Tapis R 0.125 40 5 10

Afficher les numéros des produits de même couleur qu’un ordinateur :

SELECT numprod
FROM produit
WHERE couleur IN (SELECT couleur
FROM produit
WHERE UPPER(desprod) = 'ORDINATEUR');

93
 Test d'appartenance
SELECT listeColonnes
FROM listeTables
WHERE colonne [ NOT ] IN (SELECT ...
FROM ...
[ WHERE ...] ) ;

Exemple :
Client (nclt, nom, age, adresse) et
Commande (nclt, numprod, qte , date),
Les clients qui n'ont pas commandé de produits ?

SELECT nclt
FROM Client
WHERE nclt NOT IN ( SELECT nclt FROM Commande ) ;

94
 L’opérateur ALL
x >= ALL ( sous-requête ) :
x est plus grande (ou égale) à chaque valeur
du résultat de la sous-requête

Exemple:
Commande (nclt, numprod, qte , date),

Trouver le(s) numprod(s) de la plus importante commande de

la table Commande :

SELECT numprod
FROM Commande
WHERE qte >= ALL ( SELECT qte FROM Commande)
95
 Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

200 Souris G 30 42 5 200.450

102 Tapis R 0.125 40 5 10

Afficher les produits dont la quantité est supérieure à la quantité de tout

produit de couleur rouge:

SELECT *
FROM produit
Where Qte_stk >= ALL (SELECT Qte_stk
FROM produit
WHERE couleur = 'R') ;
Résultat:
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

200 Souris G 30 42 5 200.450

102 Tapis R 0.125 40 5 10
96
 Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

100 Ordinateur R 15.2 3 2 100.275

200 Souris G 30 42 5 200.450

102 Tapis R 0.125 40 5 10

Une autre solution :

SELECT *
FROM produit
Where Qte_stk >= ( SELECT MAX(Qte_stk)
FROM produit
WHERE couleur = 'R') ;

Résultat:
Numprod Desprod Couleur Poids Qte_stk Qte_seuil Prix

200 Souris G 30 42 5 200.450

102 Tapis R 0.125 40 5 10
97
 L’opérateur ALL
• x <> ALL ( sous-requête ) : est vrai si et seulement si pour
chaque tuple t de la sous-requête, le tuple x est différent
de t.

– L’opérateur <> peut être remplacé par n’importe quel

autre opérateur de comparaison ( > , < , <= , >= , …)

98
La Division

99
 Rappel sur La Division

Définition:

Le quotient de la relation R1 par la relation R2 est la relation R:

Tous les tuples de R concaténés à chacun des tuples de R2
doivent appartenir à R1.

Notation:
R = Division ( R1, R2 )

R = (R1  R2)

100
 Exemple de division
R = Division ( R1, R2 )
Tous les tuples de R concaténés à chacun des tuples de R2 doivent
appartenir à R1

R1
A B
a x
b x R2 R


b y B A
c y x b
d z y e

e x
e y
e z

101
 R = Division ( R1, R2 )
Tous les tuples de R concaténés à chacun des tuples de R2 doivent
appartenir à R1

R1
A B
a x
b x R2 R


b y B A
c y x b
d z y e

e x
e y
Division : réponse aux questions contenant les
termes: «… tous … / …chacun … / … chaque …
e z
« Lignes de R1 associés à tous ceux de R2 »

Requête : quelles sont les valeurs de A liées à toutes les valeurs de R2.B

102
 R = Division ( R1, R2 )
Tous les tuples de R concaténés à chacun des tuples de R2 doivent
appartenir à R1

R1
A B
a x
b x R2 R


b y B A
c y x b
d z y e

e x
e y
e z
il n'existe pas d'opérateur de division
dans SQL

103
 Division SQL utilisant Group by
R = Division ( R1, R2 ) : Tous les tuples de R concaténés à chacun des
tuples de R2 doivent appartenir à R1
R1
A B
a x
b x R2 R


b y B A
c y x b
d z y e

e x
SELECT A
e y FROM R1, R2 WHERE (R1.B = R2.B)
e z GROUP BY A
HAVING COUNT (B) = ( SELECT COUNT (B) FROM R2 ) ;
1. Groupement des lignes de R1 par valeur de A
2. Calcul du nombre de valeurs distinctes de B dans chaque groupe
3. Égalité avec le nombre de valeurs distinctes de B dans R2

104
 Exemple de requête SQL de Division utilisant Group by
Produit (numprod, …, Couleur, …)
CLIENT(nclt, …, …) et
COMMANDE(nclt*, numprod*, date, …)

Afficher les clients qui ont commandé tous les produits de couleur rouges ?

105
 Exemple de requête SQL de Division utilisant GROUP BY
Produit (numprod, …, Couleur, …)
CLIENT(nclt, …, …) et
COMMANDE(nclt*, numprod*, date, …)

Afficher les clients qui ont commandé tous les produits de couleur rouges ?

SELECT c.*
FROM CLIENT c, COMMANDE cmd, Produit p
WHERE [Link] = [Link] AND [Link] = [Link]
AND Couleur = ‘Rouge’
GROUP BY nclt % On regroupe par nclt les commandes de produits rouges %
% puis, on compte le nombre de produits de couleur rouges par groupe et on compare %

HAVING COUNT(DISTINCT numprod) = ( SELECT COUNT (numprod)

FROM Produit
WHERE Couleur = ‘Rouge’

% sous-requête qui compte le nombre de

produits de couleur rouges %
);
106
Les différents types de
Jointures

107
 Jointure (Rappel)
Pays nom Capitale Populatio surface
JO Année Lieu pays n
1896 Athène Grèce Irlande Dublin 3 70

1900 Paris France Autriche Vienne 8 83

1904 St Louis USA Royaum Londre 36 244
e Uni
1908 Londre Royaum
e Uni Suisse Berne 7 41

USA Washingto 189 441

n

SELECT année, lieu, nom, capitale

FROM JO, PAYS
WHERE [Link] = [Link]
Résultat Année Lieu pays Capitale
1904 St Louis USA Washington

1908 Londre Royaume Londre

108 Uni
 Les Jointures : une autre Syntaxe
(ORACLE)

SELECT liste_de_colonnes
FROM table1 JOIN table2 ON <Condition de jointure >
 Les Jointures : une autre Syntaxe
(ORACLE)
Pays nom Capitale Population surface
JO Année Lieu pays
1896 Athène Grèce Irlande Dublin 3 70

1900 Paris France Autriche Vienne 8 83

1904 St Louis USA Royaume Londre 36 244
Uni
1908 Londre Royaum
e Uni Suisse Berne 7 41

USA Washington 189 441

SELECT année, lieu, nom, capitale

FROM JO JOIN PAYS ON [Link] = [Link]

Résultat Année Lieu pays Capitale

1904 St Louis USA Washington

1908 Londre Royaume Londre

Uni
110
 Les différents types de
Jointures
Syntaxe des différents types de Jointures:

SELECT liste_de_colonnes
FROM table1 [ {LEFT | RIGHT | FULL}
OUTER ] JOIN table2
ON <Condition de
jointure >

111
 La jointure externe
(récupération des lignes qui ne se joignent pas)

• La jointure externe de deux tables T1 et T2:

est une jointure particulière retournant le résultat normal
d'une jointure:
– > augmenté par tous les enregistrements issues de T1
pour lesquels il ne correspond aucune ligne dans T2

(respectivement les enregistrements issues de T2

pour lesquels il ne correspond aucune ligne dans T1)

112
 Jointure externe (à gauche)
Pays nom Capitale Population surface
JO Année Lieu pays
1896 Athène Grèce Irlande Dublin 3 70

1900 Paris France Autriche Vienne 8 83

1904 St Louis USA Royaume Londre 36 244
Uni
1908 Londre Royaum
e Uni Suisse Berne 7 41

USA Washington 189 441

Les lignes de la table à gauche sont affichées même si la

condition de jointure n'est pas réalisée:

SELECT année, lieu, nom, capitale

FROM JO LEFT OUTER JOIN PAYS ON [Link] = [Link]
113
 Jointure externe (à gauche)
Pays Nom Capitale Population surface
JO Année Lieu pays
1896 Athène Grèce Irlande Dublin 3 70

1900 Paris France Autriche Vienne 8 83

1904 St Louis USA Royaume Londre 36 244
Uni
1908 Londre Royaum
e Uni Suisse Berne 7 41

USA Washington 189 441

Résultat Année Lieu nom Capitale

1896 Athène NULL NULL

1900 Paris NULL NULL

SELECT année, lieu, pays, capitale
1904 St Louis USA Washington
FROM JO LEFT OUTER JOIN PAYS ON [Link] =
PAYS.nom1908 Londre Royaume
Uni
Londre
114
 Jointure externe (à droite)
Pays Nom Capitale Population surface
JO Année Lieu pays
Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaum Londre 36 244
1904 St Louis USA e Uni
1908 Londre Royaum Suisse Berne 7 41
e Uni
USA Washingto 189 441
n

Les lignes de la table à droite sont affichées même si la

condition de jointure n'est pas réalisée:

SELECT année, lieu, nom, capitale

FROM JO RIGHT OUTER JOIN PAYS ON [Link] = [Link]
115
 Jointure externe (à droite)
Pays Nom Capitale Population surface

JO Année Lieu pays Irlande Dublin 3 70

1896 Athène Grèce Autriche Vienne 8 83
1900 Paris France Royaume Londre 36 244
Uni
1904 St Louis USA
Suisse Berne 7 41
1908 Londre Royaume
Uni USA Washington 189 441

Résultat Année Lieu nom Capitale

1904 St Louis USA Washington

1908 Londre Royaume Londre

Uni
SELECT année,
NULLlieu, pays,
NULLcapitale
Irlande Dublin

FROM JO RIGHT
NULL OUTER JOIN PAYS
NULL ON [Link]
Autriche Vienne =
[Link] NULL Suisse Berne

116
 Jointure externe (FULL)
Pays Nom Capitale Population surface
JO Année Lieu pays
1896 Athène Grèce Irlande Dublin 3 70

1900 Paris France Autriche Vienne 8 83

1904 St Louis USA Royaume Londre 36 244
Uni
1908 Londre Royaum
e Uni Suisse Berne 7 41

USA Washington 189 441

SELECT année, lieu, nom, capitale

FROM JO FULL OUTER JOIN PAYS ON [Link] = [Link]

117
 Jointure externe (FULL)
Pays Nom Capitale Population surface
JO Année Lieu pays Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaume Londre 36 244
1904 St Louis USA Uni
1908 Londre Royaum Suisse Berne 7 41
e Uni
USA Washington 189 441

Résultat Année Lieu nom Capitale

1904 St Louis USA Washington

1908 Londre Royaume Londre

Uni
1896 Athène NULL NULL

1900
SELECT année, Pariscapitale
lieu, pays, NULL NULL

NULL
FROM JO FULL OUTER NULL
JOIN PAYS Irlande Dublin =
ON [Link]
[Link] NULL Autriche Vienne
118
NULL NULL Suisse Berne