Chapitre 7 Le Langage SQL

7-1- Introduction

traduit Langage de requêtes structuré ou

SQL (Structured Query Language, langage d’interrogation structuré) est un langage de quatrième génération (L4G), non procédural, conçu par IBM dans les années 70. SQL est basée sur l’algèbre relationnelle (opérations ensemblistes et relationnelles). SQL a été normalisé dès 1986 mais les premières normes, trop incomplètes, ont été ignorées par les éditeurs de SGBD. La norme actuelle SQL-2 (appelée aussi SQL-92) date de 1992. Elle est acceptée par tous les SGBD relationnels. Ce langage permet l’accès aux données et se compose de quatre sous-ensembles : (cid:1) Le Langage d’Interrogation de Données : LID : Ce langage permet de rechercher des informations utiles en interrogeant la base de données. Certains considèrent ce langage comme étant une partie du LMD.

(cid:2) Le Langage de Manipulation de Données : LMD (Data Manipulation Language DML) : Ce langage permet de manipuler les données de la base et de les mettre à jour.

(cid:3) Le Langage de Définition de Données : LDD (Data Definition Language DDL) : Ce langage permet la définition et la mise à jour de la structure de la base de données (tables, attributs, vues, index, ...).

(cid:4) Le Langage de Contrôle de Données : LCD (Data Control Language DCL) : Ce langage permet de définir les droits d’accès pour les différents utilisateurs de la base de données, donc il permet de gérer la sécurité de la base et de confirmer et d’annuler les transactions.

LDD Create Alter Drop

LMD

Insert Update Delete

SQL1

LID Select

LCD

Grant Revoke

Exemple : Soit la base de données relationnelle (BD commerciale) suivante :

Produit (NP, LibP, Coul, Poids, PU, Qtes) Client (NCl, NomCl, AdrCl) Commande (NCmd, DateCmd, #NCl) Ligne_Cmd (#NCmd, #NP, Qte)

- Désigne l’ensemble des produits. - Désigne l’ensemble des clients. - Désigne l’ensemble des commandes. - Désigne l’ensemble des lignes de commandes.

Client

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Produit Coul Gris Blanc Blanc Blanc Gris Jaune Gris Noir

Poids 5 1.2 2 25 3 2 1.7 50

Qtes 18.000 1200 1.500 1000 25.000 1500 33.000 900 12.000 1300 47.000 1250 5.500 2000 90.000 800

1 SQL ne fait pas la différence entre majuscules et minuscules

Commande DateCmd

NCmd

C001 C002 C003 C004 C005

10/12/2003 13/02/2004 15/01/2004 03/09/2003 11/03/2004

NCl CL02 CL05 CL03 CL10 CL03

Ligne_Cmd

NCmd

C001 C001 C001 C002 C002 C003 C004 C004 C004 C004 C005 C005

NP P001 P004 P006 P002 P007 P001 P002 P004 P005 P008 P001 P002

Qte

250 300 100 200 550 50 100 150 70 90 650 100

Toutes les manipulations concernant les commandes SQL seront faites sur cette base de données.

7-2- Langage d’Interrogation de Données

7-2-1- Syntaxe générale Le SQL est à la fois un langage de manipulation de données et un langage de définition de données. Toutefois, la définition de données est l'oeuvre de l'administrateur de la base de données, c'est pourquoi la plupart des personnes qui utilisent le langage SQL ne se servent que du langage de manipulation de données et plus précisément du langage d’interrogation des données, permettant de sélectionner les données qui les intéressent. La principale commande du langage d’interrogation de données est la commande SELECT. La commande SELECT est basée sur l'algèbre relationnelle, en effectuant des opérations de sélection de données sur plusieurs tables relationnelles par projection. Sa syntaxe générale est la suivante :

SELECT2 [ALL] | [DISTINCT] <liste des attributs> | * FROM3 <liste des tables> [WHERE <condition>] [GROUP BY <liste des attributs> ] [HAVING <condition de groupement >] [ORDER BY <liste des attributs de tri>] ;

(cid:5) [ … ] : le contenu entre crochet est facultatif.

(cid:5) L'option ALL est, par opposition à l'option DISTINCT, l'option par défaut. Elle permet de

sélectionner l'ensemble des lignes satisfaisant à la condition logique.

(cid:5) L'option DISTINCT permet de ne conserver que des lignes distinctes, en éliminant les

doublons.

(cid:5) La liste des attributs indique la liste des attributs choisis, séparés par des virgules. Lorsque l'on désire sélectionner l'ensemble des colonnes d'une table il n'est pas nécessaire de saisir la liste de ses attributs, l'option * permet de réaliser cette tâche.

(cid:5) La liste des tables indique l'ensemble des tables (séparées par des virgules) sur lesquelles on

opère.

(cid:5) La condition permet d'exprimer des qualifications complexes à l'aide d'opérateurs logiques et

de comparateurs arithmétiques.

2 Permet d’indiquer quelles colonnes ou quelles expressions doivent être retournées par l’interrogation. 3 Spécifie les tables participant à l’interrogation.

7-2-2- Projection Une projection est une instruction permettant de sélectionner un ensemble d’attributs dans une table.

Syntaxe : SELECT [ALL] | [DISTINCT] <liste des attributs> | * FROM <la table> ;

Applications :

(cid:6) Donner la liste des clients.

SELECT * FROM Client ;

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

(cid:6) Donner l’ensemble des produits qui ont été commandés (NP seulement).

SELECT NP FROM Ligne_Cmd;

SELECT DISTINCT NP FROM Ligne_Cmd;

Ligne_Cmd

NCmd

C001 C001 C001 C002 C002 C003 C004 C004 C004 C004 C005 C005

NP P001 P004 P006 P002 P007 P001 P002 P004 P005 P008 P001 P002

Qte

250 300 100 200 550 50 100 150 70 90 650 100

NP P001 P004 P006 P002 P007 P001 P002 P004 P005 P008 P001 P002

NP P001 P004 P006 P002 P007 P005 P008

les DISTINCT est utilisé pour éliminer duplications, par défaut on obtient tous les tuples (ALL).

7-2-3- Restrictions Une restriction consiste à sélectionner les lignes satisfaisant à une condition logique effectuée sur leurs attributs. En SQL, les restrictions s'expriment à l'aide de la clause WHERE suivie d'une condition logique exprimée à l'aide d'opérateurs logiques (AND, OR, NOT), d'opérateurs

arithmétiques (+, -, *, /, %), de comparateurs arithmétiques (=, !=, >, <, >=, <=) et des prédicats (NULL, IN, BETWEEN, LIKE, ALL, SOME, ANY, EXISTS). Ces opérateurs s’appliquent aux valeurs numériques, aux chaînes de caractères et aux dates4.

Syntaxe :

SELECT [ALL] | [DISTINCT] <liste des attributs> | * FROM <la table> WHERE <condition> ;

Au niveau de la clause WHERE, les prédicats sont les suivants : WHERE exp1 = exp2

WHERE exp1 != exp2

WHERE exp1 < exp2 WHERE exp1 > exp2

WHERE exp1 <= exp2

WHERE exp1 >= exp2

Condition est vraie si les deux expressions exp1 et exp2 sont égales. Condition est vraie si les deux expressions exp1 et exp2 sont différentes. Condition est vraie si exp1 est inférieure à exp2. Condition est vraie si exp1 est supérieure à exp2. Condition est vraie si exp1 est inférieure ou égale à exp2. Condition est vraie si exp1 est supérieure ou égale à exp2.

WHERE exp1 BETWEEN exp2 AND exp3 Condition est vraie si exp1 est comprise entre

WHERE exp1 LIKE exp2

WHERE exp1 NOT LIKE exp2

WHERE exp1 IN (exp2, exp3,…)

WHERE exp1 NOT IN (exp2, exp3,…)

WHERE exp1 IS NULL WHERE exp1 IS NOT NULL WHERE exp1 Op ALL (exp2, exp3,…)

WHERE exp1 Op ANY (exp2, exp3,…) WHERE exp1 Op SOME (exp2, exp3,…)

WHERE EXISTS sous-requête

exp2 et exp3, bornes incluses. Condition est vraie si la sous–chaîne exp2 est présente dans exp1. Condition est vraie si la sous–chaîne exp2 n’est pas présente dans exp1. Condition est vraie si exp1 appartient à l’ensemble (exp2, exp3, …). Condition est vraie si exp1 n’appartient pas à l’ensemble (exp2, exp3, …). Condition est vraie si exp1 est nulle. Condition est vraie si exp1 n’est pas nulle. Op représente un opérateur de comparaison (<, >, =, !=, <=, >=) Condition est vraie si la comparaison de l’exp1 est vraie avec toutes les valeurs de la liste (exp2, exp3, …). Si la liste est vide, le résultat est vrai. Op représente un opérateur de comparaison (<, >, =, !=, <=, >=) Condition est vraie si la comparaison de l’exp1 est vraie avec au moins une valeur de la liste (exp2, exp3, …). Si la liste est vide, le résultat est faux. Condition est vraie si le résultat de la sous- requête n’est pas vide.

Remarques :

(cid:5) L’opérateur IN est équivalent à = ANY (cid:5) L’opérateur NOT IN est équivalent à != ANY

4 Les constantes de type chaîne de caractères et date doivent être encadrées par apostrophes ‘ … ‘ contrairement aux nombres.

Applications :

(cid:6) Donner le numéro et le nom des clients de la ville de Sousse.

SELECT NCl, NomCl FROM Client WHERE AdrCl = ‘Sousse’;

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

NCl CL03 CL04 CL06 CL07

NomCl

AMS GLOULOU ELECTRON SBATIM

(cid:6) Donner la liste des commandes dont la date est supérieure à ‘01/01/2004’. SELECT * FROM Commandes WHERE DateCmd > ‘01/01/2004’;

… Commande

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

NCmd DateCmd NCl 13/02/2004 CL05 C002 15/01/2004 CL03 C003 11/03/2004 CL03 C005

(cid:6) Donner la liste des produits dont le prix est compris entre 20 et 50. SELECT * FROM Produit WHERE PU BETWEEN 20 AND 50;

… Produit

Coul Poids 5

Ou bien

SELECT * FROM Produit WHERE (PU >= 20) AND (PU <= 50);

NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Gris Blanc 1.2 Blanc 2 Blanc 25 Gris 3 Jaune 2 Gris Noir

1.7 50

PU Qtes 18.000 1200 1.500 1000 25.000 1500 33.000 900 12.000 1300 47.000 1250 5.500 2000 90.000 800

(cid:6) Donner la liste des clients dont les noms commencent par ‘B’.

NP P003 P004 P006

LibP Câble Peinture Serrure

Coul Poids

Blanc 2 Blanc 25 Jaune 2

PU Qtes 25.000 1500 33.000 900 47.000 1250

SELECT * FROM Client WHERE NomCl LIKE ‘B%’;

NCl

NomCl AdrCl Tunis CL01 BATAM CL02 BATIMENT Tunis Tunis CL12 BATFER

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

Le prédicat LIKE permet de faire des comparaisons sur des chaînes grâce à des caractères, appelés caractères jokers :

• Le caractère % permet de remplacer une séquence de caractères (éventuellement nulle). • Le caractère _ permet de remplacer un caractère. • Les caractères [-] permettent de définir un intervalle de caractères (par exemple [J-M]).

La sélection des clients dont les noms ont un E en deuxième position se fait par l'instruction : WHERE NomCl LIKE "_E%"

(cid:6) Donner les numéros des clients dont les dates de leurs commandes se trouvent parmi les dates suivantes : (‘10-12-03’, ‘10-12-04’,’13-02-04’,’11-03-04’).

SELECT DateCmd, NCl FROM Commande WHERE DateCmd IN (‘10-12-2003’,’10-12-2004’,’13-02-2004’,’11-03-2004’);

Commande

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

DateCmd 10/12/2003 13/02/2004 11/03/2004

NCl CL02 CL05 CL03

(cid:6) Donner les noms des clients qui n’ont pas d’adresse.

SELECT NomCl FROM Client WHERE AdrCl IS NULL;

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

NomCl MBATIM

Lorsqu'un champ n'est pas renseigné, le SGBD lui attribue une valeur spéciale que l'on note NULL. La recherche de cette valeur ne peut pas se faire à l'aide des opérateurs standards, il faut utiliser les prédicats IS NULL ou bien IS NOT NULL.

7-2-4- Expressions, alias et fonctions

Expression

Les expressions acceptées par SQL portent sur des attributs, des constantes et des fonctions. Ces trois types d’éléments peuvent être reliés par des opérateurs arithmétiques (+, -, *, /). Les expressions peuvent figurer : (cid:6) En tant que colonne résultat d’un ordre SELECT. (cid:6) Dans une clause WHERE. (cid:6) Dans une clause ORDER BY (cf. Tri). (cid:6) Dans les ordres de manipulation des données (INSERT, UPDATE, DELETE cf. LMD).

Alias

Les alias permettent de renommer des attributs ou des tables.

SELECT attr1 AS aliasa1, attr2 AS aliasa2, … FROM table1 aliast1, table2 aliast2… ;

Pour les attributs, l’alias correspond aux titres des colonnes affichées dans le résultat de la requête. Il est souvent utilisé lorsqu’il s’agit d’attributs calculés (expression).

NB : Le mot clé AS est optionnel.

Fonction

Le tableau suivant donne le nom des principales fonctions prédéfinies :

Nom de la fonction AVG SUM MIN MAX COUNT (*) COUNT (Attr) COUNT([DISTINCT] Attr) Nombre de valeurs non nulles différentes de l’attribut

Rôle de la fonction Moyenne Somme Minimum Maximum Nombre de lignes Nombre de valeurs non nulles de l’attribut

Applications :

(cid:6) Donner la valeur des produits en stock.

SELECT NP, (Qtes * PU) AS “Valeur Totale” FROM Produit ;

Produit NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Coul

Poids

Gris Blanc Blanc Blanc Gris Jaune Gris Noir

5 1.2 2 25 3 2 1.7 50

Qtes 18.000 1200 1000 1.500 25.000 1500 33.000 900 12.000 1300 47.000 1250 5.500 2000 90.000 800

NP P001 P002 P003 P004 P005 P006 P007 P008

Valeur Totale 21600 1500 37500 29700 15600 58750 11000 72000

On peut exprimer cette requête sans spécifier le mot clé AS :

SELECT NP, (Qtes* PU) “Valeur Totale” FROM Produit ;

On ne met les noms des colonnes résultats entre guillemets que s’ils contiennent des espaces.

SELECT NP, (Qtes * PU) Valeur FROM Produit ;

(cid:6) Donner la moyenne des prix unitaires des produits.

SELECT AVG(PU) FROM Produit;

… Produit

NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Gris Blanc Blanc Blanc Gris Jaune Gris Noir

1.2

PU Qtes 5 18.000 1200 1.500 1000 2 25.000 1500 25 33.000 900 3 12.000 1300 2 47.000 1250 5.500 2000 800

1.7 50 90.000

Coul Poids

AVG(PU) 29

7-2-5- Sélection avec jointure Il s’agit ici de sélectionner les données provenant de plusieurs tables ayant un ou plusieurs attributs communs. Cette jointure sera assurée grâce aux conditions spécifiées dans la clause WHERE.

Syntaxe :

SELECT [ALL] | [DISTINCT] <liste des attributs> | * FROM <liste des tables> WHERE Nom_Table1.Attrj = Nom_Table2.Attrj AND … AND <condition> ;

Applications :

(cid:6) Donner les libellés des produits de la commande numéro ‘C002’.

SELECT DISTINCT LibP FROM Produit, Ligne_Cmd WHERE Produit.NP = Ligne_Cmd.NP AND NCmd=‘C002’;

Ou bien

SELECT DISTINCT LibP FROM Produit P, Ligne_Cmd L WHERE P.NP = L.NP AND NCmd=‘C002’;

Pour ne pas spécifier à chaque fois le nom complet des tables Produit et Ligne_Cmd dans la condition, on peut renommer ces tables.

Produit

Ligne_Cmd

NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Coul Gris Blanc Blanc Blanc Gris Jaune Gris Noir

Poids 5 1.2 2 25 3 2 1.7 50

Qtes 18.000 1200 1.500 1000 25.000 1500 33.000 900 12.000 1300 47.000 1250 5.500 2000 90.000 800

NCmd

C001 C001 C001 C002 C002 C003 C004 C004 C004 C004 C005 C005

NP P001 P004 P006 P002 P007 P001 P002 P004 P005 P008 P001 P002

Qte

250 300 100 200 550 50 100 150 70 90 650 100

LibP Prise Verrou

On peut exprimer cette jointure autrement (cf. Sous-requêtes) :

SELECT LibP FROM Produit WHERE NP IN

(SELECT NP FROM Ligne_Cmd WHERE NCmd = ‘C002’);

(cid:6) Donner les produits (toutes les informations) commandés au cours de l’année 2003 et qui sont vendus aux clients de Tunis.

SELECT DISTINCT P.NP, LibP, Coul, Poids, PU, Qtes FROM Produit P, Commande C, Client Cl, Ligne_Cmd L WHERE P.NP = L.NP

AND C.NCmd = L.NCmd AND Cl.NCl = C.NCl AND Cl.AdrCl =‘Tunis’ AND DateCmd BETWEEN ‘01/01/2003’ AND ‘31/12/2003’;

NP P001 P004 P006

LibP

Robinet Peinture Serrure

Coul

Poids

Qtes

Gris Blanc Jaune

5 25 2

18 33 47

1200 900 1250

(cid:6) Donner les libellés des produits qui ont un prix unitaire supérieur à celui du produit ‘Robinet’.

SELECT P.LibP FROM Produit P, Produit P1 WHERE P.PU > P1.PU

AND P1.LibP =‘Robinet’;

Dans certaines requêtes, on est obligé de renommer soit des tables, soit des attributs (ici les tables).

LibP

Câble Peinture Serrure Fer

7-2-6- Groupement Il est possible de grouper des lignes de données ayant une valeur commune à l’aide de la clause GROUP BY et des fonctions de groupe (cf. Fonction).

Syntaxe :

SELECT Attr1, Attr2,…, Fonction_Groupe FROM Nom_Table1, Nom_Table2,… WHERE Liste_Condition GROUP BY Liste_Groupe HAVING Condition;

• La clause GROUP BY, suivie du nom de chaque attribut sur laquelle on veut effectuer des

regroupements.

• La clause HAVING va de pair avec la clause GROUP BY, elle permet d'appliquer une

restriction sur les groupes créés grâce à la clause GROUP BY.

NB : Les fonctions d’agrégat, utilisées seules dans un SELECT (sans la clause GROUP BY) fonctionnent sur la totalité des tuples sélectionnés comme s’il n’y avait qu’un groupe.

Applications :

(cid:6) Donner le nombre de commandes par client. SELECT NCl, COUNT((NCmd) NbCmd FROM Commande GROUP BY NCl ;

… Commande

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

NCl NbCmd CL02 CL05 CL03 CL10

1 1 2 1

(cid:6) Donner la quantité totale commandée par produit. SELECT NP, SUM(Qte) Som FROM Ligne_Cmd GROUP BY NP;

… Ligne_Cmd

NCmd NP Qte P001 250 C001 P004 300 C001 P006 100 C001 P002 200 C002 P007 550 C002 P001 50 C003 P002 100 C004 P004 150 C004 P005 70 C004 P008 90 C004 P001 650 C005 P002 100 C005

NP P001 P002 P004 P005 P006 P007 P008

Som

950 400 450 70 100 550 90

(cid:6) Donner le nombre de produits par commande. SELECT NCmd, COUNT(NP) NbProd FROM Ligne_Cmd GROUP BY NCmd;

… Ligne_Cmd

NCmd NP Qte P001 250 C001 P004 300 C001 P006 100 C001 P002 200 C002 P007 550 C002 P001 50 C003 P002 100 C004 P004 150 C004 P005 70 C004 P008 90 C004 P001 650 C005 P002 100 C005

(cid:6) Donner les commandes dont le nombre de produits dépasse 2. SELECT NCmd, COUNT(NP) NbProd FROM Ligne_Cmd GROUP BY NCmd HAVING COUNT(NP) > 2;

… Ligne_Cmd

NCmd NP Qte P001 250 C001 P004 300 C001 P006 100 C001 P002 200 C002 P007 550 C002 P001 50 C003 P002 100 C004 P004 150 C004 P005 70 C004 P008 90 C004 P001 650 C005 P002 100 C005

NCmd NbProd C001 C002 C003 C004 C005

3 2 1 4 2

NCmd NbProd C001 C004

3 4

(cid:6) Donner le total des montants par commande. SELECT NCmd, SUM(PU*Qte) TotMontant FROM Produit P, Ligne_Cmd L WHERE L.NP = P.NP GROUP BY NCmd;

NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Produit Coul Gris Blanc Blanc Blanc Gris Jaune Gris Noir

Poids 5 1.2 2 25 3 2 1.7 50

Qtes 18.000 1200 1.500 1000 25.000 1500 33.000 900 12.000 1300 47.000 1250 5.500 2000 90.000 800

Ligne_Cmd

NCmd

C001 C001 C001 C002 C002 C003 C004 C004 C004 C004 C005 C005

NP P001 P004 P006 P002 P007 P001 P002 P004 P005 P008 P001 P002

Qte

250 300 100 200 550 50 100 150 70 90 650 100

NCmd TotMontant 19100 C001 3325 C002 900 C003 14040 C004 11850 C005

7-2-7- Tri Les lignes constituant le résultat d’un SELECT sont obtenues dans un ordre quelconque. La clause ORDER BY précise l’ordre dans lequel la liste des lignes sélectionnées sera donnée.

Syntaxe : SELECT Attr1, Attr2,…, Attrn FROM Nom_Table1, Nom_Table2, … WHERE Liste_Condition ORDER BY Attr1 [ASC| DESC], Attr2 [ASC| DESC], …;

NB : L’ordre de tri par défaut est croissant (ASC).

Application : (cid:6) Donner les noms des clients suivant l’ordre décroissant.

SELECT NomCl FROM Client ORDER BY NomCl DESC ;

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

NomCl SOUDURE SBATIM SANITAIRE PRODELEC MELEC MBATIM GLOULOU ELECTRON BATIMENT BATFER BATAM AMS

(cid:6) Donner le nombre de produits et la quantité totale par commande suivant l’ordre décroissant du nombre de produits et l’ordre croissant de la quantité totale.

SELECT NCmd, COUNT DISTINCT(NP) NbProd, SUM(Qte) SomQte FROM Ligne_Cmd GROUP BY NCmd ORDER BY NbProd DESC, SomQte ASC ;

… Ligne_Cmd

NCmd NP Qte P001 250 C001 P004 300 C001 P006 100 C001 P002 200 C002 P007 550 C002 P001 50 C003 P002 100 C004 P004 150 C004 P005 70 C004 P008 90 C004 P001 650 C005 P002 100 C005

NCmd NbProd SomQte 410 4 C004 650 3 C001 750 2 C005 750 2 C002 50 1 C003

7-2-8- Sous requêtes Effectuer une sous-requête consiste à effectuer une requête à l'intérieur d'une autre, ou en d'autres termes d'utiliser une requête afin d'en réaliser une autre (on entend parfois le terme de requêtes en cascade). Une sous-requête doit être placée à la suite d'une clause WHERE ou HAVING, et doit remplacer une constante ou un groupe de constantes qui permettraient en temps normal d'exprimer la qualification.

•

lorsque la sous-requête remplace une constante utilisée avec des opérateurs classiques, elle doit obligatoirement renvoyer une seule réponse (une table d'une ligne et une colonne). Par exemple : SELECT … FROM … WHERE … < (SELECT … FROM …) ;

•

lorsque la sous-requête remplace une constante utilisée dans une expression mettant en jeu les opérateurs IN, ALL ou ANY, elle doit obligatoirement renvoyer une seule colonne. SELECT … FROM … WHERE … IN (SELECT … FROM …) ;

lorsque la sous-requête remplace une constante utilisée dans une expression mettant en jeu l’opérateur EXISTS, elle peut renvoyer une table de n colonnes et m lignes. SELECT … FROM … WHERE … EXISTS (SELECT … FROM …) ;

Applications :

(cid:6) Donner les produits dont les prix unitaires dépassent la moyenne des prix.

SELECT * FROM Produit WHERE PU > (SELECT AVG(PU) FROM Produit);

Le résultat de la sous-requête : AVG(PU) 29

… Produit

NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

NP P004 P006 P008

LibP Peinture Serrure Fer

Gris Blanc Blanc Blanc Gris Jaune Gris Noir

Blanc Jaune Noir

Coul Poids

1.2

PU Qtes 5 18.000 1200 1.500 1000 2 25.000 1500 25 33.000 900 3 12.000 1300 2 47.000 1250 1.7 5.500 2000 50 90.000 800

Coul Poids

PU Qtes 25 33.000 900 2 47.000 1250 800 50 90.000

(cid:6) Donner les commandes qui ont une date inférieure à chacune des commandes du client ‘CL03’. … Commande SELECT * FROM Commande WHERE DateCmd < ALL (SELECT DateCmd FROM Commande WHERE NCl = ‘CL03’);

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

Le résultat de la sous-requête : DateCmd 15/01/2004 11/03/2004

NCmd DateCmd NCl 10/12/2003 CL02 C001 03/09/2003 CL10 C004

(cid:6) Donner les commandes qui ont une date inférieure à au moins une des commandes du client ‘CL03’. SELECT * FROM Commande WHERE DateCmd < ANY (SELECT DateCmd FROM Commande WHERE NCl = ‘CL03’);

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

… Commande

Le résultat de la sous-requête : DateCmd 15/01/2004 11/03/2004

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004

(cid:6) Donner les clients qui ont passé au moins une commande.

SELECT NomCl FROM Client Cl WHERE EXIST (SELECT * FROM Commande C WHERE Cl.NCl =C.NCl);

Commande

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

NomCl BATIMENT AMS PRODELEC MELEC

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

(cid:6) Donner des clients qui n’ont passé aucune commande.

SELECT NomCl FROM Client Cl WHERE NOT EXIST (SELECT * FROM Commande C WHERE Cl.NCl =C.NCl);

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

Commande

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

NomCl

BATAM GLOULOU ELECTRON SBATIM SANITAIRE SOUDURE MBATIM BATFER

7-2-9- Opérateurs ensemblistes (cid:1) Union : L’opérateur UNION permet de fusionner deux sélections de tables pour obtenir un ensemble de lignes égal à la réunion des lignes des deux sélections. Les lignes communes n’apparaîtront qu’une seule fois.

Syntaxe : Requête1 UNION Requête2 ;

NB :

• Requête1 et Requête2 doivent avoir la même structure. • Par défaut les doublons sont automatiquement éliminés. Pour conserver les doublons, il est

possible d'utiliser une clause UNION ALL.

Application :

(cid:6) Donner l’ensemble des clients de Tunis et de Sousse qui ont des commandes.

SELECT Cl.NCl, NomCl, AdrCl FROM Client Cl, Commande C WHERE Cl.NCl=C.NCl AND J.AdrCl=‘Tunis’ UNION SELECT Cl.NCl, NomCl, AdrCl FROM Client Cl, Commande C WHERE Cl.NCl=C.NCl AND J.AdrCl=‘Sousse’;

Client NCl CL01 CL02 CL03 CL04 CL05 CL06 CL07 CL08 CL09 CL10 CL11 CL12

NomCl

BATAM BATIMENT AMS GLOULOU PRODELEC ELECTRON SBATIM SANITAIRE SOUDURE MELEC MBATIM BATFER

AdrCl

Tunis Tunis Sousse Sousse Tunis Sousse Sousse Tunis Tunis Monastir

Tunis

Commande

NCmd DateCmd NCl 10/12/2003 CL02 C001 13/02/2004 CL05 C002 15/01/2004 CL03 C003 03/09/2003 CL10 C004 11/03/2004 CL03 C005

NCl CL02 CL05

NomCl BATIMENT PRODELEC

AdrCl

Tunis Tunis

NCl CL03

NomCl

AMS

AdrCl Sousse

NCl CL02 CL05 CL03

NomCl BATIMENT PRODELEC AMS

AdrCl

Tunis Tunis Sousse

(cid:2) Intersection : L’opérateur INTERSECT permet d’obtenir l’ensemble des lignes communes à deux requêtes.

Syntaxe : Requête1 INTERSECT Requête2 ;

NB:

(cid:5) Requête1 et Requête2 doivent avoir la même structure. (cid:5) Il est possible de remplacer l'opérateur INTERSECT par des commandes usuelles :

SELECT a, b FROM table1 WHERE EXISTS ( SELECT c, d FROM table2

WHERE a=c AND b=d) ;

Application : (cid:6) Donner l’ensemble des produits communs aux commandes C001 et C005.

SELECT P.NP, LibP FROM Produit P, Ligne_Cmd L WHERE P.NP=L.NP AND NCmd=‘C001’ INTERSECT SELECT P.NP, LibP FROM Produit P, Ligne_Cmd L WHERE P.NP=L.NP AND NCmd=‘C005’ ;

Produit NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Coul Poids 5

Gris Blanc 1.2 Blanc 2 Blanc 25 Gris 3 Jaune 2 Gris Noir

1.7 50

PU Qtes 18.000 1200 1.500 1000 25.000 1500 33.000 900 12.000 1300 47.000 1250 5.500 2000 90.000 800

Ligne_Cmd NCmd NP Qte P001 250 C001 P004 300 C001 P006 100 C001 P002 200 C002 P007 550 C002 P001 50 C003 P002 100 C004 P004 150 C004 P005 70 C004 P008 90 C004 P001 650 C005 P002 100 C005

NP P001 P004 P006

LibP Robinet Peinture Serrure

NP P001 P002

LibP Robinet Prise

NP P001

LibP Robinet

(cid:3) Différence : L’opérateur MINUS permet d’obtenir les lignes de la première requête et qui ne figurent pas dans la deuxième.

Syntaxe : Requête1 MINUS (ou EXCEPT) Requête2 ; Avec Requête1 et Requête2 de même structure.

Application : (cid:6) Donner l’ensemble des produits qui n’ont pas été commandés.

Coul Poids 5

Gris Blanc 1.2 Blanc 2 Blanc 25 Gris 3 Jaune 2 Gris Noir

1.7 50

PU Qtes 18.000 1200 1.500 1000 25.000 1500 33.000 900 12.000 1300 47.000 1250 5.500 2000 90.000 800

Ligne_Cmd NCmd NP Qte P001 250 C001 P004 300 C001 P006 100 C001 P002 200 C002 P007 550 C002 P001 50 C003 P002 100 C004 P004 150 C004 P005 70 C004 P008 90 C004 P001 650 C005 P002 100 C005

SELECT NP, LibP FROM Produit MINUS SELECT P.NP, LibP FROM Produit P, Ligne_Cmd L WHERE P.NP=L.NP;

Produit NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

Tous les produits

Les produits commandés

LibP P003 Câble

NP P001 P002 P003 P004 P005 P006 P007 P008

LibP Robinet Prise Câble Peinture Poignée Serrure Verrou Fer

NP P001 P002 P004 P005 P006 P007 P008

LibP Robinet Prise Peinture Poignée Serrure Verrou Fer

(cid:3) Produit cartésien : Le produit cartésien est appliqué sur deux tables n’ayant pas nécessairement la même structure pour obtenir une autre table. Syntaxe : SELECT * FROM table1, table2, … ;

Application : (cid:6) Faire le produit cartésien entre la table Client et la table Produit.

SELECT * FROM Client, Produit;

Client

NCl CL01 CL02 CL03

NomCl

BATAM BATIMENT AMS

AdrCl

Tunis Tunis Sousse

NP P001 P002

LibP Robinet Prise

Produit Coul Gris Blanc

Poids 5 1.2

Qtes 18.000 1200 1000 1.500

NCl CL01 CL01 CL02 CL02 CL03 CL03

NomCl BATAM BATAM BATIMENT BATIMENT AMS AMS

AdrCl Tunis Tunis Tunis Tunis Sousse Sousse

NP P001 P002 P001 P002 P001 P002

LibP Robinet Prise Robinet Prise Robinet Prise

Coul Gris Blanc Gris Blanc Gris Blanc

Poids 5 1.2 5 1.2 5 1.2

PU 18.000 1.500 18.000 1.500 18.000 1.500

Qtes 1200 1000 1200 1000 1200 1000

Sélection Projection Jointure

7-2-10- Langage algébrique versus Langage SQL Clause WHERE dans SELECT Liste des attributs de SELECT Présence de plusieurs tables dans la clause FROM (avec la condition de jointure) Ex : SELECT * FROM T1, T2 WHERE T1.Attri=T2.Attrj ; Présence de plusieurs tables dans la clause FROM (sans la condition de jointure) Ex : SELECT * FROM T1, T2 ; UNION MINUS ou EXCEPT INTERSECT Il n’existe pas en SQL d’équivalent direct à la division. Cependant, il est toujours possible de trouver une autre solution notamment par l’intermédiaire des opérations de calcul et de regroupement.

Produit cartésien Union Différence Intersection Division

7-2-11- Exemple récapitulatif Donner le nombre de commandes et la somme des quantités du produit P001 commandé par client en ne gardant que les clients ayant un nombre de commandes >= à 2. Le résultat doit être trié selon l’ordre croissant des sommes des quantités.

Étape Requête

4 1 2 3 4 5 6

SELECT NCl, COUNT(NCmd) NbCmd, SUM(Qte) SomQte FROM Commande C, Ligne_Cmd L WHERE C.No = L.NoCommande AND NP = ‘P001’ GROUP BY NCl HAVING NbCmd >= 2 ORDER BY SomQte ;

1ère étape : Sélection des deux tables Commande et Ligne_Cmd.

Commande DateCmd

NCmd

C001 C002 C003 C004 C005 C006 C007

10/12/2003 13/02/2004 15/01/2004 03/09/2003 11/03/2004 01/03/2004 01/01/2004

NCl CL02 CL05 CL03 CL10 CL03 CL11 CL02

Ligne_Cmd

NCmd

C001 C001 C001 C002 C002 C003 C004 C004 C004 C004 C005 C005 C006 C007 C007

NP P001 P004 P006 P002 P007 P001 P002 P004 P005 P008 P001 P002 P001 P001 P002

Qte

250 300 100 200 550 50 100 150 70 90 650 100 20 40 30

NCmd

2ème étape : Application de la condition de jointure. DateCmd NCl 10/12/2003 CL02 10/12/2003 CL02 10/12/2003 CL02 13/02/2004 CL05 13/02/2004 CL05 15/01/2004 CL03 03/09/2003 CL10 03/09/2003 CL10 03/09/2003 CL10 03/09/2003 CL10 11/03/2004 CL03 11/03/2004 CL03 01/03/2004 CL11 01/01/2004 CL02 01/01/2004 CL02

C001 C001 C001 C002 C002 C003 C004 C004 C004 C004 C005 C005 C006 C007 C007

NP P001 P004 P006 P002 P007 P001 P002 P004 P005 P008 P001 P002 P001 P001 P002

Qte

250 300 100 200 550 50 100 150 70 90 650 100 20 40 30

3ème étape : Sélection des commandes du produit P001 uniquement.

NCmd

C001 C003 C005 C006 C007

DateCmd NCl 10/12/2003 CL02 15/01/2004 CL03 11/03/2004 CL03 01/03/2004 CL11 01/01/2004 CL02

NP P001 P001 P001 P001 P001

Qte

250 50 650 20 40

4ème étape : Groupement par NCl en calculant le nombre de commandes et la somme des quantités.

NCl CL02 CL03 CL11

NbCmd SomQte 2 2 1

290 700 20

5ème étape : Sélection des groupes ayant un nombre de commandes >= 2.

NCl CL02 CL03

NbCmd SomQte 2 2

290 700

6ème étape : Tri selon la somme des quantités.

NCl CL03 CL02

NbCmd SomQte 2 2

700 290

7-3- Langage de Manipulation de Données Le LMD est un ensemble de commandes permettant la consultation et la mise à jour des objets créés. La mise à jour englobe l’insertion de nouvelles données, la modification et la suppression de données existantes.

7-3-1- Insertion de données Syntaxe : INSERT INTO Nom_Table [(Attr1, Attr2,…, Attrn)] VALUES (Val1, Val2,…, Valn);

INSERT INTO Nom_Table (Attr1, Attr2, …, Attrn) SELECT…;

Permet d’insérer un tuple à la fois.

Permet d’insérer plusieurs tuples à partir d’une ou plusieurs autres tables.

L'ordre INSERT attend la clause INTO, suivie du nom de la table, ainsi que du nom de chacun des attributs entre parenthèses (les attributs omis prendront la valeur NULL par défaut). Les

données sont affectées aux attributs (colonnes) dans l'ordre dans lequel les attributs ont été déclarés dans la clause INTO.

Les valeurs à insérer peuvent être précisées de deux façons :

• avec la clause VALUES : une seule ligne est insérée, elle contient comme valeurs, l'ensemble des valeurs passées en paramètre dans la parenthèse qui suit la clause VALUES. INSERT INTO Nom_table (Attr1, Attr2, ...) VALUES (Valeur1, Valeur2, ...) ; Lorsque chaque attribut de la table est modifié, l'énumération de l'ensemble des attributs est facultative. Lorsque les valeurs sont des chaînes de caractères, il ne faut pas omettre de les délimiter par des guillemets.

• avec la clause SELECT : plusieurs lignes peuvent être insérées, elle contiennent comme

valeurs, l'ensemble des valeurs découlant de la sélection. INSERT INTO Nom_table (Attr1, Attr2, ...) SELECT Attr1, Attr2, ... FROM Nom_table2 WHERE condition ; Lorsque l'on remplace un nom d’attribut suivant la clause SELECT par une constante, sa valeur est affectée par défaut aux tuples. Il n'est pas possible de sélectionner des tuples dans la table dans laquelle on insère des lignes (en d'autres termes Nom_table doit être différent de Nom_table2).

Applications :

(cid:6) Remplissage de la table Client. INSERT INTO Client VALUES(‘CL01’,’BATAM’,’Tunis’); INSERT INTO Client VALUES(‘CL02’,’BATIMENT’,’Tunis’); INSERT INTO Client VALUES(‘CL03’,’AMS’,’Sousse’); INSERT INTO Client VALUES(‘CL04’,’GLOULOU’,’Sousse’); INSERT INTO Client VALUES(‘CL05’,’PRODELEC’,’Tunis’); INSERT INTO Client VALUES(‘CL06’,’ELECTRON’,’Sousse’); INSERT INTO Client VALUES(‘CL07’,’SBATIM’,’Sousse’); INSERT INTO Client VALUES(‘CL08’,’SANITAIRE’,’Tunis’); INSERT INTO Client VALUES(‘CL09’,’SOUDURE’,’Tunis’); INSERT INTO Client VALUES(‘CL10’,’MELEC’,’Monastir’); INSERT INTO Client VALUES(‘CL11’,’MBATIM’,’’); INSERT INTO Client VALUES(‘CL12’,’BATFER’,’Tunis’);

(cid:6) Remplissage de la table Produit.

INSERT INTO Produit VALUES(‘P001’,’Robinet’,’Gris’,5,18,1200); INSERT INTO Produit VALUES(‘P002’,’Pr

Chapitre 7 - Le Langage SQL

Chapitre 7 - Le Langage SQL