Bases de données avancées

Rappels
Langage de manipulation
SQL

Raida ElMansouri
Faculté des NTIC
[Link]@[Link]

Université Constantine 2 Semestre 1

 SQL

• SQL (Structured Query Language) est le langage de manipulation

des données relationnelles le plus utilisé aujourd’hui.
• Il est devenu un standard de fait pour les relationnels. Il possède des
caractéristiques proches de l’algèbre relationnelle (jointure par
emboîtement) et d’autres proches du calcul des tuples (variables sur
les relations).
• Les exemples dans ce chapitre s’appuient sur la base de données
relative aux fournisseurs (F), produits (P), usines (U) et livraisons
(PUF), décrite par le schéma suivant:

• F (id-F, Fnom, statut, villeF)

• Prod (id_P, Pnom, poids, couleur)
• U (id_U, Pnom, villeu)
• PUF (id_P, id_U, id_F, quantite)

Université Constantine 2 2
 Format de base d'une requête
SELECT Liste des noms d'attributs du résultat
FROM Nom d'une relation (ou de plusieurs relations)
[ WHERE Condition logique qui définit les tuples du résultat ]

Exemple: donner le nom et le poids des produits rouges.

SELECT pnom, poids
FROM Prod
WHERE couleur = ‘rouge’;
Exemple : tous les renseignements sur tous les fournisseurs.
SELECT NF, fnom, statut, villeF
FROM F
ou
SELECT * /* l'étoile signifie: tous les attributs*/
FROM F

Université Constantine 2 3
 Format de base d'une requête
• Un résultat sans doubles
Les SGBD commercialisés ne suppriment pas automatiquement les
doubles. La clause DISTINCT permet à l'utilisateur d'avoir un résultat
sans double.
Exemple : liste des couleurs qui existent.
SELECT DISTINCT couleur
FROM Prod
• Un résultat trié
La clause ORDER BY permet de définir un ordre de tri pour les tuples
du résultat .
Exemple : liste des fournisseurs de Constantine par ordre
alphabétique.
SELECT Fnom, id_f, statut
FROM F
WHERE villeF = ’Constantine’
ORDER BY Fnom ASC , id_F ASC ;
Si plusieurs fournisseurs ont le même nom, ils seront classés selon
leurs numéros.
Université Constantine 2 4
 Recherche avec blocs emboîtés
Exemple : numéros des fournisseurs de produits rouges ?
ensemble des numéros des produits rouges :
SELECT id_P
FROM Prod
WHERE couleur = ‘rouge’
ensemble des numéros des fournisseurs de produits rouges :
SELECT id_F
FROM PUF
WHERE id_P IN
( SELECT id_P
FROM Prod
WHERE couleur = ‘rouge’ );

• Le mot clef IN signifie "appartient", l'opérateur mathématique de la

théorie des ensembles ( ).
• La phrase id_P IN (SELECT id_P FROM P WHERE couleur = ‘rouge’) est
une condition logique, signifiant "la valeur de NP est dans l'ensemble
des numéros de produits rouges", ce qui peut être vrai ou faux.
Université Constantine 2 5
 Recherche avec blocs emboîtés
Pour répondre à cette requête, le SGBD :
1. exécute la requête interne (calcul de l'ensemble des numéros des produits rouges),
2. exécute la requête externe SELECT id_F FROM PUF WHERE id_P IN (...) en balayant
PUF et en testant pour chaque tuple si ce id_P appartient à l'ensemble des numéros de
produits rouges.

Dans le WHERE , la condition logique peut avoir plusieurs formes :

- elle peut être composée de conditions élémentaires connectées par AND, OR, NOT, et
par des parenthèses;
- les conditions élémentaires sont du type :
<valeur attribut> <opérateur de comparaison> <valeur attribut> |
<valeur attribut> <opérateur d'appartenance> <ensemble>
avec :
<valeur attribut> ::= nom d'attribut | constante
<opérateur de comparaison> ::= > | < | ≥ | ≤ | = | ≠
<opérateur d'appartenance> ::= IN | NOT IN
<ensemble> est soit un ensemble constant, par exemple (1,2,3), soit un ensemble
défini par une requête SELECT, soit une union, différence ou intersection
d'ensembles :

Université Constantine 2 6
 Recherche avec blocs emboîtés

<ensemble> ::= "(" <constantes> ")" | <requête SELECT> |

<ensemble> <opérateur ensembliste> <ensemble>
<constantes> ::= constante | constante","<constantes>
<opérateur ensembliste> ::= UNION | MINUS | INTERSECT

Exemple : noms des fournisseurs n°1, 2, 3.

SELECT Fnom FROM F

WHERE id_F = ‘F1’ OR id_F =‘F2’ OR id_F = ‘F3’;
ou
SELECT fnom
FROM F
WHERE id_F IN (‘F1’, ‘F2’, ‘F3’);

Université Constantine 2 7
 Qualification des noms d'attributs
Notations :
Id_P : attribut
P.id_P, PUF.id_P : attributs qualifiés par le nom d'une relation
Règle 1 : Un nom d'attribut non qualifié, référence la relation la plus interne qui a un
attribut de ce nom-là.
Exemple : noms des fournisseurs ne livrant pas le produit numéro 2.
SELECT fnom
FROM F
WHERE ‘P2’ NOT IN
(SELECT id_P /* ensemble des NP livrés par ce
FROM PUF fournisseur */
WHERE PUF.id_F = F.id_F);
Dans le WHERE du SELECT interne, on aurait pu tout simplement écrire :
WHERE id_F = F.id_F .

Université Constantine 2 8
 Qualification des noms d'attributs
Règle 2 : On peut renommer localement une relation dans la clause
FROM .
Exemple: ... FROM PUF PUF1 ...
la relation PUF s'appelle alors PUF1 pour le SELECT correspondant à
ce FROM uniquement.
Exemple : numéros des fournisseurs livrant le même le produit que
fournisseur 1 en une quantité plus petite.

SELECT id_F
FROM PUF PUFX /* PUF est renommée*/
WHERE id_P IN
(SELECT id_P /* ensemble des produits du fournisseur 1 */
FROM PUF
WHERE id_F = 1 AND quantite > [Link]);

Université Constantine 2 9
 Recherche sur plusieurs relations simultanément

Format général:
SELECT Ai…
FROM R1, R2…, Rn
WHERE < condition de jointure entre les Ri >
AND < condition(s) de la requête >
Exemple : pour chaque produit livré, le nom du produit et les
villes de ses fournisseurs.
SELECT pnom, villef
FROM Prod, F, PUF
WHERE PUF.id_P = Prod.id_P AND PUF.id_F = F.id_F

Université Constantine 2 10
 Recherche sur plusieurs relations simultanément

On peut également utiliser join … on

Exemple : pour chaque produit livré, le nom du produit et son
poids
SELECT pnom, poids
FROM puf join Prod on PUF.id_P = Prod.id_P;
Exemple : pour chaque produit livré, le nom du produit et les
villes de ses fournisseurs
SELECT distinct pnom, villeF
FROM (F join PUF on F.id_F=PUF.id_F) join prod on
prod.id_p=puf.id_p;

Université Constantine 2 11
 Recherche avec quantificateurs : ANY, ALL
• SQL permet d'écrire des conditions où apparaissent des
quantificateurs proches de ceux de la logique ("il existe" ( ),
"quelque soit" ( ) ), grâce aux mots clefs ANY et ALL.

• Signification:

• Pour ANY : " existe-t-il dans l'ensemble au moins un élément e qui

satisfait la condition:
e <opérateur de comparaison> <ensemble> ? "

• pour ALL : " tous les éléments de l'ensemble satisfont-ils la

condition ? "

Université Constantine 2 12
 Recherche avec quantificateurs : ANY, ALL
Exemples:
Ensemble des numéros des fournisseurs de produits rouges :
SELECT id_F
FROM PUF
WHERE id_P = ANY
( SELECT id_P
FROM Prod
WHERE couleur = ‘rouge’ );
Ensemble des numéros des fournisseurs qui ne fournissent que des
produits rouges :
SELECT id_F
FROM F
WHERE ‘rouge’ = ALL
( SELECT couleur
FROM Prod
WHERE id_P = ANY ( SELECT id_P FROM PUF
WHERE PUF.id_F = F.id_F) ) ;
Université Constantine 2 13
 EXISTS < ensemble>
Cette condition teste si l'ensemble n'est pas vide (ensemble ≠ Ø).
Exemple : noms des fournisseurs qui fournissent au moins un
produit rouge.
SELECT fnom
FROM F
WHERE EXISTS (SELECT *
FROM PUF, Prod
WHERE id_F = F.id_F AND color ='rouge'
AND PUF.id_P=Prod.id_P);

Il existe aussi la condition inverse :

NOT EXISTS <ensemble>
qui teste si l'ensemble est vide.
Université Constantine 2 14
 Operations
• SQL nous permet de faire des calculs sur des tables .
• Il est possible de faire une opération qui affecte l’ensemble
des contenus d’une colonne.
• Les opérateurs utilisés sont +,- , * , /
• Exemple : on souhaite afficher la table PUF avec 500
unités de plus pour chaque commande effectuée.
• Select Id_p, id_f , id_u, qte+500 from PUF;
• Il est également possible de compter le nombre de lignes
d’une table (pas le nombre de colonnes), la moyenne
d’une colonne, la somme d’une colonne, de fournir la
valeur minimale ou maximale parmi les contenus d’une
colonne.

Université Constantine 2 15
 Fonctions d’agrégation
• Pour ce faire, on utilise les fonctions suivantes :
• AVG(nom_de_colonne) retourne la moyenne de la
colonne
• AVG(distinct nom_de_colonne) retourne la moyenne
des lignes qui ont des contenus différents pour nom de
colonne.
• COUNT( * ou nom_de_colonne) retourne le nombre de
lignes
• COUNT(distinct nom_de_colonne) retourne le nombre
de lignes qui ont des contenus différents pour nom de
colonne.

Université Constantine 2 16
 fonctions d’agrégation
• MIN(nom_de_colonne) retourne la valeur minimale
d’une colonne
• MAX((nom_de_colonne) retourne la valeur maximale
d’une colonne
• SUM(nom_de_colonne) retourne la somme d’une
colonne.
• SUM(distinct nom_de_colonne) retourne la somme des
lignes de contenus différents pour nom de colonne.
• Ces fonctions s’appellent des fonctions d’agrégation, car
elles réduisent un ensemble de valeurs à une valeur
unique

Université Constantine 2 17
 Evaluation d’une fonction d’agrégation

• L’évaluation d’une fonction d’agrégation s’effectuent

après avoir appliqué la clause where si elle est présente.
La clause where permet donc d’éliminer des lignes de la
table AVANT d’effectuer une fonction d’agrégation
• Exemples :
1) Obtenir le nombre de lignes de la table PUF qui concerne
le produit P1
 select count (*) from PUF where id_p= ‘P1’
2) Obtenir la moyenne des quantités vendues par le
fournisseur F1
select avg(qte) from PUF where id_F= ‘F1’

Université Constantine 2 18
 Evaluation d’une fonction d’agrégation

3) Obtenir la plus petite et la plus grande quantité livrée du

produit P1
select min(qte),max(qte) from PUF where id_p=‘p1’

4) Obtenir la somme de toutes les quantités vendues par le

fournisseur F1
 select sum(qte) from PUF where id_F=‘F1’

Université Constantine 2 19
 Regroupements

• Les fonctions d’agrégation peuvent s’appliquer sur des

groupes de lignes de la table et non sur la table entière.
Il faut alors spécifier les conditions de regroupement dans
une clause GROUP BY.
• Exemples :
1) Obtenir les sommes totales des articles tous confondus
vendus par fournisseur
 select Id_F, sum(qte) from PUF group by id_F
2) Obtenir les sommes des articles vendus par fournisseur et
par produit
 select id_F, id_P, sum(qte) from PUF group by id_F,id_p;

Université Constantine 2 20
 Regroupements
• Le ou les facteurs de regroupement doivent former des
groupes dans lesquels il n’y a qu’une seule valeur possible
pour les champs du select .
• Exemple : Soit un regroupement se faisant sur la quantité
 select count(id_F),qte from PUF group by qte?
est correct car chaque regroupement ne donnera qu’une seule
ligne de résultat.
• Et pour select id_F,qte from PUF group by qte ?
provoque une erreur car il y a plusieurs id_F pour un
regroupement.

Id_F n’est ni un facteur de regroupement, ni une fonction

d’agrégation
Université Constantine 2 21
 Regroupements

• Afin de garantir cette unicité de valeur, tous les champs se

trouvant dans la clause select sont soit des fonctions
d'agrégation, soit des facteurs de regroupement se trouvant
dans la clause group by.

Remarque
• On peut avoir un facteur de regroupement non présent
dans la clause select.
exemple: select count(id_F) from F group by status

Université Constantine 2 22
 La clause having

• Il est possible d’éliminer des groupes de la solution obtenus

par une requête avec regroupement.
La clause HAVING détermine une condition qui s’applique
au groupe.
La condition est évaluée pour chaque groupe . Les groupes
qui ne satisfont pas à la condition sont éliminés.

Exemples.
• On veut connaître les identifiants de produits dont le total
des quantités livrées -tout fournisseur confondu -est
strictement supérieure à 500.
 select id_p from puf group by id_P having sum(qte) > 500;

Université Constantine 2 23
 La clause having

• On veut connaître la quantité totale des produits fournis

(tous produits confondus) par les fournisseurs codés plus
grand que F2.
 select id_F, sum(qte) from puf group by id_F having id_F >’F2’

• La norme SQL indique que les conditions de la clause

HAVING doivent porter soit sur des champs de la clause
SELECT, soit sur une fonction d’agrégation présente ou
absente.

Université Constantine 2 24
 La clause having

 select id_f, count(*) from PUF group by id_F having

count(*) >2
Instruction correcte : La clause having porte sur une fonction
d’agrégation présente dans le select

 select id_F, id_p,count(*) from puf group by id_f,id_P

having id_P >’P3’
Instruction correcte : La clause having porte sur un champ
présent dans le select

Université Constantine 2 25
 La clause having

 select id_F, count(*) from PUF group by id_F having

sum(qte) > 800
Instruction correcte :

• La clause having porte sur une fonction d’agrégation

absente du select.

Université Constantine 2 26
 La clause having

 select id_F, count(*) from PUF group by Id_F having qte >
200

Instruction incorrecte :La clause having ne porte pas sur une

fonction d’agrégation ou un champ de la clause sélect.
(Plusieurs valeurs de qte existent dans un groupe)
• ATTENTION
L’instruction donne un message d’erreur

Université Constantine 2 27