Outils pour la conception de systèmes d'information

Chapitre 8 : Algèbre relationnelle et SQL

Les opérations de manipulations des données d'une base relationnelle

1. L'algèbre relationnelle, qu'est ce que c'est ?

C'est un outil de raisonnement qui permet de décomposer quelles sont les opérations
élémentaires à faire sur une base de données pour obtenir les résultats souhaités.

Exemple de résultat :

Afficher la liste de tous les clients ayant passé une commande avant le 31 Décembre 1998.

De même qu'une addition utilise des nombres comme opérandes et a toujours un nombre comme
résultat, une opération en algèbre relationnelle utilise une ou plusieurs relations comme
opérandes et a toujours pour résultat une relation.

De même qu'il existe plusieurs opérations sur les nombres (addition, soustraction, etc..), il existe
différentes opérations que l'on peut faire sur les relations d'un schéma relationnel.

Pour comprendre facilement l'algèbre relationnelle, il faut se représenter chaque relation d'un
schéma relationnel comme un tableau dont chaque colonne est en fait un attribut de la relation.

Exemple : Soit la relation enfant (EnfantID, EnfantNom, EnfantPrenom).

Pour manipuler facilement la relation "enfant" en algèbre relationnelle, il faut se la représenter

comme suit (comme une table de la base) :

enfant
EnfantId EnfantNom EnfantPrenom
1234 Radroite Sylvie
54321 Phoutus Honnet

2. Et le SQL, c'est quoi ?

S.Q.L. (Structured Query Language) est un langage de programmation particulier qui permet de
manipuler des bases de données relationnelles.

S.Q.L. se retrouve aujourd'hui dans la très grande majorité des S.G.B.D. (Système de Gestion de
Bases de Données), et fonctionne sur des plates-formes allant des gros systèmes aux micro-
ordinateurs.

Exemple de S.G.B.D. utilisant S.Q.L. pour la gestion des données : MS-ACCESS, ORACLE,
SYBASE, INFORMIX, DB2, INGRES, SQLSERVER...

S.Q.L. fait l'objet d'une normalisation (la norme actuelle est la norme ANS 1985), et le fait qu'il
soit devenu un standard dans l'interrogation des bases de données, n'empêche pas les éditeurs de
S.G.B.D. d'avoir des versions de SQL qui diffèrent légèrement les unes des autres, ce qui limite
considérablement la portabilité des bases de données d'un environnement à un autre.

S.Q.L. n'est pas un langage de programmation au sens classique du terme, c'est un langage qui
permet de faire des manipulations sur des bases de données (ou requêtes).

1
 Outils pour la conception de systèmes d'information

Ce langage contient plusieurs catégories de commandes :

 Le langage de description de données (L.D.D.) qui permet la création et la modification de la

structure de bases de données (tables et attributs, vues, états, index, contraintes
d'intégrité ...).
Cette partie n'est pas au programme de pemière année : on n'utilise pas directement les
commandes SQL pour créer une base et/ou modifier sa structure, on le fait avec des logiciels
qui nous simplifient le travail en créant les commandes SQL à notre place.

 Le langage de contrôle des données (L.C.D.) doit permettre la sécurité des données, et leur
confidentialité (qui a le droit de faire quoi, et sur quoi ?), réservé à l'administrateur de la base
(le DBA).

 Le langage de manipulation de données (L.M.D.) qui permet la gestion des données se

trouvant dans les tables (consultation, mise à jour, ...).
C'est cette partie (et encore, pas tout…) que nous étudions en première année.

Exemple d'opérations de mise à jour : changer dans la base l'adresse d'un client car il a
déménagé.

2
 Outils pour la conception de systèmes d'information

MCD utilisé pour les exercices :

Schéma relationnel correspondant :

MAGASINS (mag_num, mag_loc, mag_ger)

ARTICLES (art_num, art_nom, art_poids, art_coul, art_pa, art_pv, frs_num#)
àFOURNISSEURS (frs_num, frs_nom)
CLIENTS (clt_num, clt_nom, clt_pnom, clt_pays, clt_loc)
VENTES (vnt_clt#, vnt_mag#, vnt_art#, vnt_qte, vnt_prix, vnt_date)

Remarque : l'attribut vnt_mag de la relation VENTES correspond à l'attribut mag_num de la

relation MAGASINS. De même, vnt_art et vnt_clt de VENTES s'appellent respectivement
art_num et clt_num.
En effet, un attribut ne porte pas forcément le même nom dans la relation où il est clé primaire et
dans la table où il est clé étrangère.

Modèle physique correspondant :

3
 Outils pour la conception de systèmes d'information

1. L'égalité

 Syntaxe en algèbre relationnelle (noté AR dans le reste du cours) :

NouvelleRelation = RelationUtilisée

 Syntaxe en SQL :

SELECT *
FROM RelationUtilisée

Ceci provoque l'affichage de toutes les données de la table "RelationUtilisée".

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les
renseignements concernant tous les fournisseurs.

AR : Relation = fournisseurs

SQL : Select * from fournisseurs

Résultat : Affichage du contenu complet de la table fourniseurs

2. La projection

Cette opération de l'algèbre relationnelle a pour rôle de créer une nouvelle relation ne contenant
qu'une partie des attributs (colonnes) issue d'une ou plusieurs tables.

21. Projection simple

 Syntaxe en algèbre relationnelle :

NouvelleRelation = Projection(Relation1 (attr1,attr2,…attrn))

 Syntaxe en SQL :

SELECT attr1,attr2,…attrn
FROM Relation1

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher les noms de tous
les fournisseurs.

AR : Relation= Projection(fournisseurs(frs_nom))

SQL : SELECT frs_nom FROM fournisseurs

Résultat : Affichage de la liste des noms des fournisseurs

4
 Outils pour la conception de systèmes d'information

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher les noms de tous
les articles existant.

AR : Relation= Projection(articles(art_nom))

SQL : SELECT art_nom FROM articles

Résultat : Affichage de la liste des noms des articles

Le problème : si on la laisse en l'état, cette requête affiche les répétitions, qui dans ce cas précis,
ne sont pas souhaitables.

22. Projection avec suppression des éventuels doublons

Pour éliminer les "doublons" dans le résultat, il existe une "clause" SQL qui est la clause
"DISTINCT". Si on écrit la requête précédente comme suit :

AR : Relation= Projection(articles(art_nom), sans double)

SQL : SELECT DISTINCT art_nom FROM articles

Résultat : Affichage de la liste des noms des articles avec suppression des doublons

3. La sélection (ou Restriction)

La sélection est l'opération de l'algèbre relationnelle qui permet de sélectionner des lignes (n-
uplets) d'une ou plusieurs tables répondant à certains critères.

AR : Relation= selection(RelationUtilisée(condition))

SQL : SELECT *
FROM RelationUtilisée
WHERE condition

La condition peut avoir des formes multiples :

 Comparaison à une valeur : =, <>, <, >, <=, >=

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les
renseignements sur les articles de couleur rouge.

AR : Relation= Sélection(articles(art_coul = "ROUGE"))

SQL : SELECT *
FROM articles
WHERE art_coul = "ROUGE"

Résultat : Affichage de la liste des articles rouges

5
 Outils pour la conception de systèmes d'information

 Comparaison à une fourchette de valeurs : BETWEEN…AND

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les
renseignements sur les clients dont le numéro est compris entre 5 et 15.

AR : Relation= Sélection(clients(clt_num entre 5 et 15)

SQL : SELECT *
FROM clients
WHERE clt_num BETWEEN 5 AND 15

Résultat : Affichage de la liste des clients de 5 à 15

 Comparaison à un filtre : LIKE

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les
renseignements sur les clients dont le nom comporte la lettre "C".

AR : Relation= Sélection(clients(clt_nom comme "%C%")

SQL : SELECT *
FROM clients
WHERE clt_nom LIKE "%C%"

 Comparaison à la valeur d'une expression

On peut faire des requêtes du genre suivant :

Exemple : Afficher les articles dont le prix de vente est supérieur ou égal à 2 fois le prix d'achat

SQL : SELECT *
FROM articles
WHERE art_pv >= 2 * art_pa

 Comparaison à une liste de valeurs : IN

Exemple : Afficher la liste des articles de couleur rouge ou verte

AR : Relation= Sélection(articles(art_coul dans ("ROUGE", "VERT"))

SQL : SELECT *
FROM articles
WHERE art_coul IN ("ROUGE","VERT")

Remarque : Cette requête peut aussi s'écrire

SELECT *
FROM articles
WHERE art_coul = "ROUGE" or art_coul = "VERT"
Autre exemple : Afficher tous les articles qui ont été vendus

Principe :

6
 Outils pour la conception de systèmes d'information

- Récupérer dans la table "ventes" tous les numéros d'articles

- Afficher les renseignements sur les articles dont le numéro figure dans la table "ventes"

AR : Relation = projection (ventes (art_num), sans double)

Résultat = Sélection (articles (art_num dans relation))

SQL : SELECT *
FROM articles
WHERE art_num IN (SELECT DISTINCT art_num
FROM ventes)

C'est l'instruction entre parenthèses qui est effectuée en premier. Ceci s'appelle une requête
imbriquée.

 Test sur la valorisation d'un attribut : IS NULL

Exemple : Afficher les articles dont le poids n'est pas renseigné.

AR : Résultat = sélection (articles (art_poids = NULL))

SQL : SELECT *
FROM articles
WHERE art_poids IS NULL

 Négation d'une condition : NOT

Exemple : Afficher tous les articles qui ne sont pas rouges

AR : Relation= Sélection(articles(art_coul <> "ROUGE"))

Ou bien Relation= Sélection(articles(NON art_coul = "ROUGE"))

SQL : SELECT *
FROM articles
WHERE NOT art_coul = "ROUGE"
(ou bien art_coul <> "ROUGE")

7
 Outils pour la conception de systèmes d'information

 Condition renfermant plusieurs sous-conditions liées avec : AND ou OR

- Avec AND

Exemple : Afficher tous les articles qui sont rouges et dont le poids dépasse 100 grammes

AR : Relation= Sélection(articles(art_coul = "ROUGE" ET art_poids > 100))

SQL : SELECT *
FROM articles
WHERE art_coul = "ROUGE" AND art_poids > 100

- Avec OR : même genre

 Ordre des opérateurs logiques : NOT est prioritaire sur AND qui est prioritaire sur OR.

Exemple : Afficher les articles ni rouges ni verts.

AR : resultat = sélection (articles (NON(art_coul = "ROUGE" ou art_coul = "VERT"))

SQL : SELECT *
FROM articles
WHERE NOT ( art_coul = "ROUGE" OR art_coul = "VERT")
Ou bien : WHERE NOT art_coul = "ROUGE" AND NOT art_coul = "VERT"

Remarque : si on enlève la parenthèse, c'est à dire si la requête est

SELECT *
FROM articles
WHERE NOT art_coul = "ROUGE" art_coul = "VERT", alors, on affiche les articles qui
ne sont pas rouges ou bien qui sont verts, c'est à dire qu'on affiche les articles verts.

4. Sélection et projection en même temps :

Il s'agit de sélectionner certaines colonnes des lignes satisfaisant à la condition indiquée dans la
clause WHERE.

Exemple : Afficher les noms des articles qui sont rouges

AR : Relation= Sélection(articles(art_coul <> "ROUGE"))

Resultat = projection (Relation(art_nom)

SQL : SELECT art_nom

FROM articles
WHERE art_coul = "ROUGE"

8
 Outils pour la conception de systèmes d'information

5. La jointure :

On fait une opération de jointure quand on établit un lien entre une colonne d'une table et une
colonne d'une autre table (par exemple : entre le numéro de fournisseur se trouvant dans le table
article et le numéro de fournisseur se trouvant dans la table fournisseur).

Les opérations de jointure servent à récupérer, par exemple, le nom du fournisseur fournissant
l'article numéro 5 alors que dans la table ARTICLE, on n'a que le numéro du fournisseur :

AR : relation1 = sélection (articles (art_num = 5)

Relation2 = jointure (relation1.frs_num = fournisseurs.frs_num)
Resultat = projection (relation2(frs_nom)

SQL : SELECT frs_nom

FROM articles, fournisseurs
WHERE articles.frs_num = fournisseur.frs_num
AND art_num = 5

Autre formulation SQL :

SELECT frs_nom
FROM fournisseurs
WHERE frs_num IN ( SELECT frs_num
FROM articles
WHERE art_num = 5)

Remarque :

on est obligé de préciser le nom de la table uniquement quand les attributs portent le même nom.

Exercices : Ecrire en AR puis en SQL les requêtes suivantes

Req 1 : Afficher la liste des articles fournis par le fournisseur "SARL ROULAND"

Req 2 : Afficher le nom des fournisseurs qui ne fournissent pas d'articles rouges.

Req 3 : Afficher les coordonnées des clients ayant commandé des produits en une quantité
supérieure à 50.

Req 4 : Afficher la liste des articles de la même couleur que l'article 10.

9
 Outils pour la conception de systèmes d'information

6. Présentation des résultats

61. Présentation du résultat trié sur certains critères

• Exemple 1 :

Affichage du numéro, nom et poids de tous les articles

SELECT art_num, art_nom, art_poids

FROM articles

Affichage du numéro, nom et poids de tous les articles triés l'ordre croissant de leur poids

SELECT art_num, art_nom, art_poids

FROM articles
ORDER BY art_poids

ou bien

SELECT art_num, art_nom, art_poids

FROM articles
ORDER BY 3; (car art_poids est le 3ème attribut de la clause SELECT)

ou bien

SELECT art_num, art_nom, art_poids

FROM articles
ORDER BY 3 ASC; (par défaut, c'est à dire si on ne précise rien, le tri se fait en ordre
croissant)

• Exemple 2 : ordre décroissant "ORDER BY …..DESC"

Liste des nom, numéro, poids et prix d'achat des articles de poids inférieur à 100 selon l'ordre
décroissant de leur prix d'achat

SELECT art_num, art_nom, art_poids, art_pa

FROM articles
WHERE art_poids <= 100
ORDER BY art_pa DESC;

• Exemple 3 :

Tri des articles de poids inférieur à 100 selon l'ordre croissant de leur poids et à poids égal, par
prix d'achat décroissant

SELECT art_num, art_nom, art_poids, art_pa

FROM articles
WHERE art_poids <= 100
ORDER BY art_poids, art_pa DESC;
Quand on indique plusieurs critères de tri à la file l'un de l'autre, le tri relatif au deuxième critère
est effectué pour les nuplets qui ont tous le premier critère identique, le tri relatif au troisième
critère est effectué pour les nuplets qui ont tous le deuxième critère identique etc...

10
 Outils pour la conception de systèmes d'information

62. Utilisation de fonctions arithmétiques dans les requêtes

Exemple : Afficher le nom et la marge bénéficiaire sur tous les produits, par ordre croissant sur
la valeur de la marge

SELECT art_nom, art_pv - art-pa

FROM articles
ORDER BY art_pv - art_pa;

ou bien

SELECT art_nom, art_pv - art-pa as marge

FROM articles
ORDER BY marge

On indique par "art-pv - art_pa as marge" que la variable temporaire "marge" est égale à la
différence entre art-pv et art_pa.

7. Les fonctions ensemblistes ou fonctions de groupe

Ces fonctions du langage SQL effectuent un calcul sur des ensembles de valeurs.

71. La moyenne : AVG

Elle calcule la moyenne des valeurs dans l'attribut (qui doit être de type numérique)

Exemple : Poids moyen des articles

SELECT AVG(art_poids)
FROM articles;

Remarque : pour les amoureux de l'algèbre relationnelle, on peut éventuellement introduire la

fonction "moyenne" et la requête ci-dessus, en AR est alors :

Résultat = projection (articles, moyenne(art_poids))

72. La somme : SUM

Calcule la somme des valeurs dans l'attribut.

Exemple : Somme des poids de tous les articles

SELECT SUM(art_poids)
FROM articles

73. Comptage du nombre d'occurrences dans l'attribut : COUNT

• Exemple : Afficher le nombre de couleurs différentes existant dans le stock

SELECT COUNT(DISTINCT art_coul)

FROM articles;

11
 Outils pour la conception de systèmes d'information

Question : que se passe-t-il si on ne met pas la clause "DISTINCT" ?

• Autre exemple : Compter tous les nuplets (= toutes les lignes) de la table ARTICLES.

SELECT count *
FROM articles;

74. Recherche de la plus grande (ou de la plus petite) valeur dans un attribut : MAX (ou
MIN)

• Exemple : Nom et poids de l'article le plus lourd

SELECT art_nom, MAX(art_poids)

FROM articles;

• Exemple : Liste des articles dont le prix de vente est supérieur au prix de vente de l'article de
couleur blanche le moins cher (Recherche de la plus petite valeur dans l'attribut : MIN, même
utilisation que MAX).

SELECT art_nom
FROM articles
WHERE art_pv > (SELECT MIN(art_pv
FROM articles
WHERE art_coul = 'BLANC')

8. Les requêtes sur des groupes

81. La clause GROUP BY

Crée des sous-tables dans une table en fonction de la valeur d'un critère donné : c'est à dire que
GROUP BY partitionne la table en sous-tables ayant la même valeur pour 1 attribut ou un
groupe d'attributs.

Les fonctions de groupe présentées ci-dessus sont alors calculées pour chaque sous-table.

• Exemple 1 : Nombre d'articles pour chaque couleur

SELECT art_coul, COUNT(*)

FROM articles
GROUP BY art_coul;

12
 Outils pour la conception de systèmes d'information

• Exemple 2 : Calcul du prix moyen des ventes des articles rangés par couleur

SELECT art_coul, AVG(art_pv)

FROM articles
GROUP BY art_coul;

SQL crée les groupes d'après les différentes valeurs de art_coul, puis, pour chaque valeur de
art_coul, calcule le prix moyen des ventes.
Si, dans le cas de notre exemple, certaines couleurs n'étaient pas indiquées, un groupe est créé
pour chaque n-uplet de couleur indéfinies.

• Exemple 3 : Calcul du prix moyen des ventes des articles rangés par couleur en excluant les
articles dont le prix d'achat est inférieur à 5

SELECT art_coul, AVG(art_pv)

FROM articles
WHERE art_pa >= 5
GROUP BY art_coul;

• Exemple 4 : Calcul du prix moyen des ventes des articles rangés par couleur en excluant les
articles dont le prix d'achat est inférieur à 5, les couleurs devant être affichées dans l'ordre
lexicographique

SELECT art_coul, AVG(art_pv)

FROM articles
WHERE art_pa >=
GROUP BY art_coul
ORDER BY art_coul;

• Exemple 5 : Calcul par article de la remise totale accordée par rapport au prix de vente
catalogue pour la semaine allant du 23 au 30 Décembre 97, l'affichage doit être trié sur le
numéro d'article.

SELECT ventes.art_num, SUM(vnt_qte * (art_pv - vnt_prix)) remise

FROM ventes , articles
WHERE ventes.art_num = articles.art_num
AND vnt_date BETWEEN '231297' AND '201297')
GROUPBY vnt_art
ORDER BY v.art_num ;

13
 Outils pour la conception de systèmes d'information

82. La clause HAVING

C'est l'équivalent de WHERE mais appliqué aux groupes, on ne peut donc utiliser cette clause
que si on a utilisé une fonction de groupe ou la clause GROUP BY.

Un petit truc : il faut utiliser la clause "HAVING" quand on a envie d'écrire "WHERE
COUNT…", qui n'est pas accepté par SQL.

• Afficher les numéros des magasins ayant réalisé 3 ventes :

SELECT vnt_mag, COUNT(*)

FROM ventes
GROUP BY vnt_mag
HAVING COUNT(*) = 3;

• Exemple : Afficher les magasins ayant réalisé plus de 2 ventes entre le 231197 et le 301197

SELECT vnt_mag, COUNT(*)

FROM ventes
WHERE vn_date BETWEEN '231197' AND '301197'
GROUP BY vnt_mag
HAVING COUNT(*) > 2

• Exemple : Afficher les noms et numéros des fournisseurs fournissant plus de 4 articles :

SELECT frs_nom, count(articles.frs_num)

FROM fournisseurs, articles
WHERE fournisseurs.frs_num = articles.frs_num
GROUP BY articles.frs_num, frs_nom
HAVING COUNT(articles.frs_num) >= 4;

• Remarque : ordre des commandes dans une requête

SELECT…
FROM….
WHERE….
GROUP BY….
HAVING….

14
 Outils pour la conception de systèmes d'information

9. Quelques commandes de mise à jour d'une base de données

91. Insertion de nuplets : INSERT INTO

Exemple1 :

INSERT INTO matable

VALUES (1,'DUPONT', 'Jean',33);
Si le nom des attributs n'est pas spécifié, cela suppose qu'ils sont tous concernés

ou bien

INSERT INTO matable (numero, nom ,pnom, age)

VALUES (1,'DUPONT', 'Jean',33);
Equivalent à la commande ci-dessus

Exemple 2 :

INSERT INTO matable(numero, nom)

VALUES (1,'DUPONT');
Insertion d'un nuplet en laissant le prénom et l'âge indéterminés.

Ou bien

INSERT INTO matable

VALUES (1,'DUPONT', NULL, NULL);
Equivalent à la commande ci-dessus

Exemple 3 :

On suppose pour cet exemple qu'une table CLIENTSBIS a été créée, ayant le même schéma que
la table CLIENTS, et qu'on veut y mettre tous les nuplets concernant les clients parisiens.

INSERT INTO clientbis

SELECT *
FROM clients
WHERE clt_loc = 'PARIS'

15
 Outils pour la conception de systèmes d'information

92. Suppression de nuplets : DELETE

Exemple 1 : Supprimer tous les nuplets de la table articles

DELETE *
FROM articles;

Exemple 2 : Supprimer tous les articles fournis par le fournisseur de l'agrafeuse

DELETE FROM articles

WHERE frs_num = (SELECT frs_num
FROM articles
WHERE art_nom = 'AGRAFEUSE');

93. Modification de nuplets : UPDATE ... SET

Exemple 1 : Doubler le prix de vente de tous les articles dont le poids est inférieur à 100

UPDATE articles
SET art_pv = art_pv * 2
WHERE art_poids <100;

Exemple 2 : Donner à tous les articles le même prix de vente qui est en fait le prix de vente
moyen actuel

UPDATE articles
SET art_pv = (SELECT AVG(art_pv)
FROM articles)

16