Chapitre 6 : Le langage SQL

4
Origines et Evolutions(1/2)
SQL est dérivé de l'algèbre relationnelle et de SEQUEL
(1974/1976- Lab. de recherche IBM à San José)

Il existe trois versions normalisées, du simple au complexe :

 SQL1 86 version minimale
 SQL1 89 (l’intégrité)
 SQL2 (92) langage complet à 3 niveaux
 SQL3 98: les évolutions objets
……

La plupart des systèmes supportent SQL1 complet

FSS 5
Origines et Evolutions(1/2)
SQL a été intégré à :
 SQL/DS,
 DB2,
 ORACLE,
 INGRES, …

SQL est un langage de requêtes des SGBDR :

 ORACLE (Oracle Corp. - 77)
 INGRES (Ingres Technology - 80)
 DB2 (IBM - 1984)
 INFORMIX (Informix Inc - 81)
 SYBASE (Sybase Inc - 84)

FSS 6
OPERATIONS
Langage de Définition des Données (LDD)
 Create
 Alter
 Drop

Langage de Manipulation des données (LMD)

 Insert
 Update
 Select

Langage de Contrôle de Données (LCD)

 GRANT et REVOKE
 COMMIT et ROLLBACK

FSS 7
Langage de Définition des Données (LDD)

FSS 8
OPERATIONS
La création d’une table se fait avec la commande : CREATE
TABLE et consiste à définir :
 son nom,
 les colonnes qui la composent et leurs types,
 les différentes contraintes de colonnes/table.

On dispose de deux formes pour cette commande :

 Création simple
 Création + Insertion (voir LMD)

FSS 9
Création des tables
Création simple

CREATE TABLE nom-table

( nom-col1 type-col [(taille)] [DEFAULT valeur] [contrainte-col],
nom-col2 type-col [(taille)] [DEFAULT valeur] [contrainte-col],
nom-coln type-col [(taille)] [DEFAULT valeur] [contrainte-col],
[contrainte-table]
);

FSS 10
 Création des tables
La taille indique la valeur maximale de la longueur du champ.
Les types de données possibles sont :

NUMBER entiers positifs et négatifs

NUMBER(n) entiers ayant au maximum n chiffres (entre 1 et 38)
NUMBER(n,m) entiers ayant au maximum n chiffres dont m sont situés à
droite de la virgule si m >0 ou à gauche si m <0.
CHAR(n) Chaînes de caractères de longueur fixe n caractères (n<=
255)
CHAR Un seul caractère
VAECHAR2(n) Chaîne de caractères de longueur variable ( 1<= n <=4000)
LONG Chaîne de caractères de longueur variable allant jusqu’à 2GO
DATE date dont le format standard est (DD-MON-YY)

FSS 11
Création des tables
Définition des contraintes : Une contrainte d'intégrité est une
règle qui définit la cohérence d'une donnée ou d'un ensemble de
données de la base de données.

On distingue :
 La clé primaire (un attribut ou un groupe d’attributs)
 La clé étrangère (intégrité référentielle)
La nullité des valeurs d’un attribut
 L’unicité de la valeur d’un attribut ou d’un groupe d’attributs
La contrainte de valeur avec la clause check

FSS 12
Définition des contraintes
Définition de la clé primaire : peut être définie comme contrainte
de colonne ou comme contrainte de table
 Clé primaire comme contrainte de colonne en ajoutant Primary key
dans la définition de la colonne
 Clé primaire comme contrainte de table, elle est définie comme
contrainte de table selon la syntaxe
Constraint nom_contrainte Primary key (att1)

 Dans le cas de clé primaire multiple, elle doit être définie comme
contrainte de table :

Constraint nom_contrainte Primary key (att1, att2, …,attN)

FSS 13
 Définition des contraintes
Exemple 1 : Créer la table Produit en supposant que Numprod est sa
clé primaire

Solution 1 : Solution 2:

CREATE TABLE Produit CREATE TABLE Produit

(Numprod number(6) primary key, (Numprod number(6),
Desprod varchar(15), Desprod varchar(15),
Couleur char, Couleur char,
Poids number(8,3), Poids number(8,3),
Qte_stk number(7,3), Qte_stk number(7,3),
Qte_seuil number(7,3), Qte_seuil number(7,3),
Prix number(10,3)); Prix number(10,3),
Constraint Pk1 primary key (Numprod));

FSS 14
Définition des contraintes
Exemple 2 : Créer la table

LigneCommande (NumCde, NumProd, QteCde)

CREATE TABLE LigneCommande

(NumCde number(8),
NumProd number(6),
QteCde number(7,3),
constraint pk_LigneCde primary key ( NumCde, Numprod)) ;

FSS 15
 Définition des contraintes
Définition d’une clé étrangère : lorsque un attribut figure comme clé
primaire d’une autre table. Elle peut être définie comme contrainte de
colonne ou comme contrainte de table

 Clé étrangère comme contrainte de colonne en ajoutant dans la définition

de la colonne
References nom_table (attribut)

 Clé étrangère comme contrainte de table, elle est définie comme contrainte
de table selon la syntaxe

Constraint nom_contrainte Foreign key (att1) References nom_table (attribut)

FSS 16
Définition des contraintes
Exemple : Créer la table produit sachant que la table magasin est déjà
créée
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Qte_stk, Prix, #CodMag)

Solution 1 : Clé étrangère comme contrainte de colonne

CREATE TABLE Produit

(Numprod number(6) primary key ,
Desprod varchar(15),
Qte_stk number(7,3),
Prix number(10,3),
CodMag number(6) references Magasin(NumMag));

FSS 17
Définition des contraintes
Exemple : Créer la table produit sachant que la table magasin est déjà
créée
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod,, Qte_stk, Prix, #CodMag)

Solution 2 : Clé étrangère comme contrainte de table

CREATE TABLE Produit

(Numprod number(6) primary key ,
Desprod varchar(15),
Qte_stk number(7,3),
Prix number(10,3),
CodMag number(6),
Constraint FK_Produit Foreign key(CodMAg) references Magasin(NumMag));

FSS 18
Définition des contraintes
La non nullité des valeurs d’un attribut : l’utilisateur est obligé de
saisir la valeur de l’attribut
 Elle est définie comme contrainte de colonne en ajoutant NOT NULL
dans la définition de la colonne

L’unicité de valeur d’un attribut (ou d’un groupe d’attributs) : la

valeur doit être unique
 pour le cas d’un seul attribut, elle est définie comme contrainte de
colonne en ajoutant UNIQUE dans la définition de la colonne
Pour le cas d’unicité d’un groupe d’attributs, elle est définie comme
contrainte de table selon la syntaxe
Constraint nom_contrainte Unique (att1, att2, …,attN)

FSS 19
 Définition des contraintes
Exemple 1 : Créer la table Produit en supposant que :
‒ La saisie de la couleur du produit est obligatoire
‒ Deux produits différents ne peuvent pas avoir la même
désignation
Avec:
 Numprod : de type numérique de taille 6 CREATE TABLE Produit
 Desprod : de type caractère variable de taille (Numprod number(6) primary key,
15.
Desprod varchar(15) unique,
 Couleur : de type caractère sur une position
Couleur char not null,
 Poids : de type numérique sur huit positions
dont trois chiffres après la virgule. Poids number(8,3),
 Qte_stk : de type numérique sur sept Qte_stk number(7,3),
positions dont trois chiffres après la virgule. Qte_seuil number(7,3),
 Qte_seuil : de type numérique sur sept Prix number(10,3));
positions dont trois chiffres après la virgule.
 Prix : de type numérique sur dix positions
dont sept chiffres avant la virgule.

FSS 20
Définition des contraintes
Exemple 2 : Créer la table Produit en supposant que :
‒ La saisie de la couleur du produit est obligatoire
‒ Deux produits différents ne peuvent pas avoir la même
combinaison (désignation,couleur)

CREATE TABLE Produit

(Numprod number(6) primary key,
Desprod varchar(15),
Couleur char not null,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
Constraint U_Produit Unique (desprod,couleur));

FSS 21
 Définition des contraintes
Contraintes de valeurs avec la clause check: permet de limiter les
valeurs possibles pour une colonne en vérifiant une certaine
condition. Le contrôle se fera lors de l’insertion des données.

Constraint nom_contrainte check (attribut condition)

 la condition peut utiliser :

 Un opérateur de comparaison
 La clause between val1 and val2
 La clause In (liste de valeurs)

FSS 22
Définition des contraintes
Exemple : Créer la table produit sachant que le poids doit être positif, la
quantité en stock est comprise entre 0 et 1000 et est supérieure à la
quantité seuil et que la couleur ne peut être que ‘N’, ‘G’ ou ‘B’.
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix)

CREATE TABLE Produit

(Numprod number(6) primary key ,
Desprod varchar(15),
Couleur char,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
Constraint CK1_Produit check (Poids > 0),
Constraint CK2_Produit check (Qte_stk >Qte_seuil ),
Constraint CK3_Produit check (Qte_stk between 0 and 1000),
Constraint CK4_Produit check (Couleur IN ('N','G','B') ));

FSS 23
Modification de la structure d’une table
Les cinq possibilités de modification de structure de table
permettent :
 d'ajouter des colonnes,
 de modifier la structure d'une colonne,
 de supprimer des colonnes existantes,
 d'ajouter des contraintes,
 de supprimer des contraintes.

FSS 24
Modification de la structure d’une table
Ajout des colonnes :
ALTER TABLE nom_table
ADD (nom-col1 type-col [(taille)] [DEFAULT valeur] [contrainte-col],
nom-col2 type-col [(taille)] [DEFAULT valeur] [contrainte-col],
nom-coln type-col [(taille)] [DEFAULT valeur] [contrainte-col] ) ;

Modification de la structure d’une colonne existante

ALTER TABLE nom_table
Modify (nom-col1 type-col [(taille)] [DEFAULT valeur] [contrainte-col],
nom-col2 type-col [(taille)] [DEFAULT valeur] [contrainte-col],
nom-coln type-col [(taille)] [DEFAULT valeur] [contrainte-col] ) ;

Suppression de colonnes existantes

ALTER TABLE nom_table

Drop (nom-col1, nom-col2, …, nom-coln) ;

FSS 25
Modification de la structure d’une table
Exemples :
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix, #CodMag)

Ajouter le champ Gérant de type caractère variable de taille 20 à la table magasin.

Alter table magasin

Add gerant varchar(20);

Ajouter le champ Ville à la table magasin de type caractère de taille 15 contenant par
défaut Sfax.

Alter table magasin

Add ville char(15) default 'Sfax’;

FSS 26
 Modification de la structure d’une table
Exemples :
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix, #CodMag)

Modifier le champ Gérant de la table magasin de manière qu'il devienne caractère

variable de taille 10.

Alter table magasin

Modify gerant varchar(10);

Modifier la table Produit de manière à ce que la valeur par défaut de QteSeuil soit égale
à 10.

Alter table produit

Modify qteseuil default 10;

Supprimer les colonnes gérant et ville de Magasin

Alter table magasin

Drop (gerant,ville);

FSS 27
 Modification de la structure d’une table
Ajout d’une contrainte :
ALTER TABLE nom_table
ADD Constraint Définition_contrainte ;

Exemples : On suppose que la table produit a été créée sans clé primaire ni
étrangère.
Ajouter la contrainte clé primaire pour le champ NumProd

Alter table produit

Add constraint PK_Produit primary key (NumProd);

Ajouter la contrainte clé étrangère pour le champ CodMag

Alter table produit

Add constraint FK_Produit foreign key (CodMag) references Magasin(NumMag);

Ajouter la contrainte de vérification pour le champ QteSeuil de manière qu'il soit > 0

Alter table produit

Add constraint CK5_Produit check (QteSeuil > 0);

FSS 28
Modification de la structure d’une table
Suppression de la contrainte clé primaire

ALTER TABLE nom_table DROP PRIMARY KEY [CASCADE] ;

L’option cascade est ajoutée pour pouvoir supprimer une clé primaire
référencée

Suppression d’une contrainte autre que la clé primaire

ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte ;

FSS 29
Modification de la structure d’une table
Remarque !!

Pour retrouver les différentes contraintes avec toutes leurs propriétés, on utilise
la commande suivante :
Select *
from user_constraints
[ where table_name = 'NOMTABLE' ];

Il est à signaler que pour cette commande, le nom de la table doit être écrit en
majuscule.

Parmi les propriétés les plus utilisées, on distingue constraint_name,

constraint_type, table_name, R_constraint_name.

FSS 30
 Modification de la structure d’une table
Exemples :
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix, #CodMag)

Supprimer les clés primaires de Magasin et de produit

Alter table magasin

drop primary key cascade ;

Alter table produit

drop primary key;

Supprimer la contrainte Clé étrangère de la table Produit.

Alter table Produit

Drop constraint FK_Produit;

FSS 31
Modification de la structure d’une table
Exemples :
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix, #CodMag)

Modifier la table Produit de manière que la couleur soit quelconque.

Alter table Produit

Drop constraint CK4_Produit;

Afficher les noms et types des contraintes de la table Produit.

Select constraint_name,constraint_type
From user_constraints
Where table_name=’PRODUIT’ ;

FSS 32
Suppression d’une table

DROP TABLE nom_table [ CASCADE CONSTRAINTS ] ;

L'option CASCADE CONSTRAINTS permet de supprimer toutes

les contraintes d'intégrité référentielles qui se reflètent aux clés
primaires de la table à supprimer.

Exemple : Supprimer dans l’ordre les tables Magasin et Produit

Drop table Magasin CASCADE CONSTRAINTS ;

Drop table Produit;

FSS 33
Changement du nom d’une table

Rename ancien_nom TO nouveau_nom ;

Exemple : modifier le nom de la table produit en Article

Rename Produit to article;

FSS 34
Langage de Manipulation des Données (LMD)

FSS 35
Insertion des données
L'instruction INSERT ajoute une/des ligne(s) à une table. Les
valeurs à ajouter lors d'une insertion doivent vérifier les
contraintes définies au moment de la définition des données.
Tout enregistrement ne vérifiant pas les contraintes sera rejeté.

On distingue deux formes:

 Insertion des valeurs données par l’utilisateur (1 ligne)

INSERT INTO nom_table [(les champs de la table)]

VALUES (liste des valeurs) ;

 Insertion à travers la copie des valeurs des colonnes d’une autre

table (1 ou plusieurs lignes)
INSERT INTO nom_table [(les champs de la table)]
Requête ;

FSS 36
Insertion des données
Remarques

 Les valeurs des colonnes de type caractère ou chaînes de caractère ou date

doivent apparaître entre ‘ ‘.

 Les champs ayant été créés avec la contrainte Not Null devront,
obligatoirement, avoir des valeurs.

 Il est possible d'insérer un enregistrement connaissant seulement les valeurs

de quelques champs. Pour cela il faut :

 Soit indiquer la liste des champs à insérer et leurs valeurs respectives.

 Soit mettre NULL dans la liste de valeur pour les champs vides.

FSS 37
Insertion des données
Exemple : Soit la table :
Produit (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix)

Ligne 1
Insert Into Produit (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix)
VALUES (100, 'ordinateur', 'R',15.2 ,3 ,2 , 100.275) ;
Ou
Insert Into Produit
VALUES (100, 'ordinateur', 'R', 15.2, 3, 2, 100.275) ;
Ligne 2
Insert Into Produit (Numprod, Desprod)
VALUES ( 80, 'disquette') ;

FSS 38
Insertion des données
Exemple : Soit la table :
Produit (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix)

Ligne 3
Insert Into Produit (Numprod , Desprod, couleur)
VALUES (200, ‘souris’,'G') ;

Ligne 4

Insert Into Produit

VALUES (300, ‘Ordinateur', NULL,15.2 ,3 ,2 , 100) ;

FSS 39
Modification des données
L'instruction Update consiste à mettre à jour les colonnes d’une
table par de nouvelles valeurs
UPDATE nom_table
SET col1 = val1 , … , coln = valn
[WHERE condition] ;

Remarques
 Il n'est pas possible de mettre à jour plus qu'une table à la fois.

 La modification des données n'est autorisée que si les contraintes sont

toujours valides.
 Les valeurs peuvent être des constantes, des expressions, des résultats de
sous requêtes ou NULL (pour supprimer la valeur initiale du champ).
 Si la clause Where n'apparaît pas dans la commande, il s'agit de mettre à jour
tous les enregistrements de la table avec la même valeur ou expression.

FSS 40
Modification des données
Exemples :

Modifier la désignation du produit numéro 80 en Imprimante.

Update Produit
Set DesProd = 'Imprimante'
Where numProd = 80 ;

Majorer de 5% les prix des produits dont le prix est supérieur à 10.

Update Produit
Set Prix = Prix * 1.05
Where Prix > 10 ;

Modifier les quantités de tous les produits avec la valeur 10.

Update Produit
Set Qte_stk = 10;

FSS 41
Suppression des données
L'instruction Delete permet de supprimer une ou plusieurs lignes
(tuples) d’une table en gardant la base cohérente. Les
enregistrements à supprimer sont spécifiés par la condition de la
clause where.

Delete From nom_table

[WHERE condition] ;

Exemples
Supprimer tous les Produits de couleur Blanche (‘B’)

Delete From Produit

Where couleur='B';

Supprimer toutes les lignes de la table Produit.

Delete From Produit ;

FSS 42
Consultation des données
La consultation de données (ou requête d’interrogation) est
effectuée par la commande Select selon la syntaxe :
SELECT col1, col2, … , coln
FROM nom_table1, nom_table2, …, nom_tableP
[WHERE condition] ;

 SELECT : permet de définir la liste des colonnes à sélectionner

 Si on souhaite avoir en résultat toutes les colonnes de la table, on mettra après
SELECT *.
 Pour éviter la redondance d'une même ligne dans le résultat affiché, on ajoute le mot
clé DISTINCT avant l'énumération des colonnes.

 FROM : est suivie par les noms des tables nécessaires pour obtenir le
résultat souhaité.

 WHERE : définit la condition que doit vérifier un enregistrement donné pour

qu'il fasse partie du résultat. Si on souhaite avoir en résultat toutes les lignes
de la table, on ne met pas la clause WHERE.
FSS 43
Consultation des données
Exemples :

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

Select numprod, desprod

From Produit ;

Afficher les numéros et désignations des produits existants en stock avec une
quantité > 20.

Select numprod,desprod
From Produit
Where qte_stk > 20 ;

FSS 44
Consultation des données
Exemples :

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

Select *
From Produit
Where qte_stk > 20 ;

Afficher les couleurs des différents produits.

Select Distinct couleur

From produit;

FSS 45
Consultation des données
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. 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 côtes : " ".

SELECT col1 [alias 1], col2 [alias 2], … , coln [alias N],
FROM nom_table1
[WHERE condition] ;

Exemple : Afficher les numéros et désignations de tous les produits. Les

titres des colonnes à afficher seront respectivement Numéro et Nom
Produit

Select numprod Numéro , desprod "Nom Produit"

From Produit ;

FSS 46
Consultation des données
Spécification d’un critère dans une requête: La condition est composée
de 3 termes:
 Un nom de colonne, un opérateur de comparaison et une constante, colonne,
liste de valeurs, une expression ou une requête

Pour les opérateurs, on distingue :

 opérateurs classiques de comparaison ( = , <> , < , > , >= , <= )

 IS NULL : permet de tester si la colonne n'a pas encore de valeur (vide).

 IN (liste de valeurs): permet de tester si le contenu d'une colonne coïncide avec l'une des
valeurs de la liste.

 BETWEEN V1 and V2 : permet de tester si le contenu d'une colonne est compris entre les
valeurs V1 et V2.

 LIKE chaîne générique : permet de tester si le contenu d'une colonne ressemble à un

chaîne de caractères obtenue à partir de la chaîne générique.

FSS 47
Consultation des données
La chaîne générique est une chaîne de caractères qui contient l'un des
caractères suivants :
 % : remplace une autre chaîne de caractères qui peut être même une
chaîne vide.
 - : remplace un seul caractère.

Si la condition est composée de sous conditions, on fait recours aux

opérateurs logiques
 AND,
 OR
 et la négation NOT (NOT In, NOT Like, NOT between, Is NOT like).

FSS 48
Consultation des données
Exemples
Afficher les numéros de produits dont la couleur n'a pas été saisie.
Select numprod
From produit
Where couleur is null;

Afficher les produits de couleur Rouge, Bleu ou Gris.

Select *
From Produit
Where couleur In ('R','B','G');

Afficher les numéros des produits dont le prix est compris entre 100 et 200.
Select numprod
From produit
Where prix between 100 and 200.

FSS 49
Consultation des données
Exemples
Afficher les produits dont la désignation commence par 'o'.
Select *
From produit
Where desprod like 'o%';

Afficher les numéros et désignations des produits dont les noms commencent par o
ou par s
Select numprod,desprod
From produit
Where desprod like ‘o%' or desprod like 's%';

Afficher les désignations des produits contenant r en deuxième position de la

designation et existant en stock avec une quantité > 20.
Select desprod
From produit
Where desprod like '-r%' and qte_stk > 20;

FSS 50
Consultation des données
Remarque : Il existe une forme de création de tables accompagnée
d'une insertion. Cette forme permet de créer une table et d'y
insérer des données à partir d'une ou plusieurs tables ou vues.

CREATE TABLE nom_table

[ (col1 type [(taille)] [null / not null] ,
col2 type [(taille)] [null / not null] ,
...
coln type [(taille)] [null / not null] ) ]
AS requête ;

 Avec cette forme, la spécification des colonnes n'est pas obligatoire. La

requête est une opération de sélection qui indique les colonnes sources, les
tables auxquelles appartiennent ces colonnes et éventuellement des critères
de sélection.
 Il est à noter qu'aucune contrainte n'est créée pour la nouvelle table. Il faudrait
les ajouter ultérieurement.

FSS 51
Tri des résultats
Pour obtenir un résultat trié, il suffit d’ajouter à la requête la clause

ORDER BY expression [asc/desc]

Par défaut, le tri est croissant (asc)

Exemples
Donner la liste des produits ordonnés par ordre croissant de leurs prix.
SELECT *
FROM produit
ORDER BY prix ;

Donner la liste des produits ordonnés par ordre croissant de leurs prix et décroissant de
leurs désignations.
SELECT *
FROM produit
ORDER BY prix, des_prod desc ;

FSS 52
Les fonctions de groupes
Les fonctions de groupe (ou expressions agrégats): s’appliquent à un
ensemble de données
 AVG : permet d'avoir la moyenne arithmétique d'un ensemble donné
 COUNT : permet d'avoir le nombre d'occurrences des enregistrements

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

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

 SUM : permet d'avoir la somme des éléments

 STDDEV : permet d'avoir l'écart type

 VARIANCE : permet d'avoir la variance

Chacune de ces fonctions a comme argument un nom de colonne ou une

expression arithmétique. Elles ignorent les valeurs nulles et par défaut prennent les
valeurs multiples pour des valeurs différentes. La fonction COUNT peut prendre
comme argument le caractère * pour connaître le nombre de lignes sélectionnées.
On ne peut pas mettre une fonction de groupe dans la condition de la clause
WHERE.

FSS 53
 Les fonctions de groupes
Exemples :
Donner le nombrede produits de couleur Rouge.
Select count(*) "Nombre"
From produit
Where upper(couleur)='R';

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

Select sum(qte_stk) quantité
From produit
Where upper(couleur)='R';

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

Select avg(qte_stk) quantité
From produit
Where upper(couleur)='R';

Afficher les quantités minimale et maximales des produits en stock.

Select max(qte_stk) maximum, min(qte_stk) minimum
From produit;
FSS 54
Classification des résultats
Il peut être intéressant de regrouper des résultats afin de faire des
opérations par groupe. Cette opération se réalise à l'aide de la clause
GROUP BY, suivie du nom de chaque colonne sur laquelle on veut
effectuer des regroupements.

La clause qui permet la classification est :

GROUP BY expression
[HAVING condition]

FSS 55
Classification des résultats

SELECT continent, MIN(population), MAX(population), AVG(population),

SUM (surface), COUNT(*)

FROM PAYS
GROUP BY continent ;

FSS 56
Classification des résultats

Table Commande
noCommande dateCommande noClient
1 01/06/2000 10
3 02/06/2000 10 noClient nombreCommandes
4 05/07/2000 10 10 3
2 02/06/2000 20 20 2
6 09/07/2000 20
30 1
5 09/07/2000 30
40 2
7 15/07/2000 40
8 15/07/2000 40

SELECT noClient, count(*) nbreCommandes

FROM commande
GROUP BY noClient ;

FSS 57
 Classification des résultats
Exemples
Afficher par couleur, la quantité totale de produits.
Select couleur,sum(qte_stk) "Qte totale"
From produit
Group by couleur ;
Afficher par couleur, la quantité totale d'ordinateurs.
Select couleur,sum(qte_stk) "Qte totale"
From produit
Where upper(desprod)='ORDINATEUR'
Group by couleur ;
Afficher par couleur et designation, le nombre de produits.
Select desprod, couleur, count(*) "Nombre"
From produit
Group by desprod,couleur;

FSS 58
 Classification des résultats
Exemples
Afficher par couleur, la quantité totale des produits supérieure à 100,.
Select couleur,sum(qte_stk) "Qte totale"
From produit
Group by couleur
Having sum(qte_stk)> 100;
Afficher par couleur, la quantité totale des ordinateurs, supérieure à 100.
Select couleur,sum(qte_stk) "Qte totale"
From produit
Where upper(desprod)='ORDINATEUR'
Group by couleur
Having sum(qte_stk)> 100;
Afficher les couleurs des produits ayant une quantité totale > 100
Select couleur
From produit
Group by couleur
Having sum(qte_stk)> 100;

FSS 59
Les requêtes imbriquées
On parle de requête imbriquée lorsque la colonne de la condition de la
clause Where est comparée à une valeur/valeurs résultante(s) d'une autre
requête.
Le résultat de chaque requête imbriquée sert de valeur de référence dans
la condition de sélection de la requête de niveau supérieur, appelée
requête principale
La sous-requête peut renvoyer soit une ligne, soit plusieurs
Si la sous-requête renvoie plusieurs lignes, nous avons deux cas, selon
que la sous-requête est indépendante de la requête principale ou
synchronisée avec elle. La condition de sélection emploie alors :
 L’opérateur IN (équivalent à = ANY)
 L’opérateur NOT IN (équivalent à != ALL)
 Un opérateur simple ( =, !=, <>, <, >, <=, >=) suivi de ALL ou ANY
 L’opérateur EXISTS

FSS 60
 Les requêtes imbriquées
Exemples
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 produits
WHERE numprod=100);

Afficher les numéros des produits de même couleur qu'un ordinateur.

SELECT numprod
FROM produit
WHERE couleur IN ( SELECT couleur
FROM produits
WHERE upper(ltrim(rtrim(desprod)))='ORDINATEUR’);

FSS 61
 Les requêtes imbriquées
Exemples
Afficher les numéros des produits dont la quantité est supérieure à la quantité de tout
produit de couleur rouge.

SELECT numprod
From produit
Where qte_stk > ALL ( SELECT Qte_stk
From produit
Where upper(couleur)='R');
Ou encore :

SELECT numprod
From produit
Where qte_stk > ( select MAX(Qte_stk)
From produit
Where upper(couleur)='R');

FSS 62
Les opérateurs ensemblistes
Requête 1
Opérateur
Requête 2

Les opérateurs possibles sont :

 UNION : Elle permet d'avoir les résultats de la première requête suivis de ceux
de la deuxième requête.
 INTERSECT : Elle permet d'avoir les lignes communes aux résultats des deux
requêtes.
 MINUS : Elle permet d'avoir les lignes qui apparaissent dans la première requête
et qui n'apparaissent pas dans la seconde
Avec l'utilisation de ces opérateurs :
 Il faut utiliser le même nombre de variables avec les mêmes types dans les deux
requêtes.
 Les doublons sont automatiquement éliminés.
 Les titres des colonnes résultats sont ceux de la première requête.
 La clause ORDER BY ne fait plus référence aux noms des colonnes, mais à leurs
numéros.
FSS 63
 Les opérateurs ensemblistes
Exemple 1
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix,
#CodMag)

Afficher les numéros des magasins qui sont à sfax ou qui contiennent le produit
numéro 100.

SELECT codmag
FROM produit
WHERE numprod=100
UNION
SELECT nummag
FROM magasin
WHERE lower(ltrim(rtrim(adresse))) = 'sfax' ;

FSS 64
 Les opérateurs ensemblistes
Exemple 2
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix,
#CodMag)
Afficher les numéros des magasins qui sont à sfax et qui contiennent le produit
numéro 100.
SELECT codmag
FROM produit
WHERE numprod=100
INTERSECT
SELECT nummag
FROM magasin
WHERE lower(ltrim(rtrim(adresse))) = 'sfax' ;

FSS 65
 Les opérateurs ensemblistes
Exemple 3
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix,
#CodMag)
Afficher les numéros des magasins qui sont à sfax et qui ne contiennent pas le
produit numéro 100.
SELECT nummag
FROM magasin
WHERE lower(ltrim(rtrim(adresse))) = 'sfax' ;
MINUS
SELECT codmag
FROM produit
WHERE numprod=100;

FSS 66
La jointure
La jointure simple : permet de faire un rapprochement de deux ou
plusieurs tables par comparaison des valeurs d'une ou de plusieurs
colonnes communes à ces tables. Les colonnes utilisées pour la
comparaison 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 TA. idTableA, colonne2, colonne3

FROM TableA TA, TableB TBTB
WHERE [Link] = [Link]

Avec :
TableA (idTable A, colonne1, colonne2)

TableB (idTable B, colonne3, colonne4, #idTableA)

FSS 67
La jointure simple
Exemples :
MAGASIN (NumMag, Adresse, Surface)
PRODUIT (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix, #Nummag)

Afficher les numéros des produits ainsi que leur adresse de stockage.
Select numprod,adresse
From produit, magasin
WHERE [Link]=produit. Nummag;

Ou encore
Select numprod,adresse
From produit P, magasin M
WHERE [Link]=P. Nummag;

Afficher tous les produits ainsi que leur adresse de stockage.

Select P.*,adresse
From produit P, magasin M
WHERE [Link]=P. Nummag;

Afficher les adresses des magasins contenant le produit numéro 100.

SELECT adresse
FROM magasin , produit
WHERE [Link]=produit. Nummag and [Link]=100 ;

FSS 68
 La jointure
L’autojointure : Elle consiste à faire le rapprochement d'une table avec elle-même;
c'est-à-dire ramener sur une même ligne des informations qui proviennent de
plusieurs lignes de la même table. Pour cela il faut créer un SYNONYME de la table
ou utiliser des ALIAS.
Exemple :
Trouver la désignation et le prix unitaire des produits dont le prix est supérieur à celui
du produit ordinateur.

Solution 1 : Avec utilisation des synonymes.

CREATE SYNONYM prod FOR produit ;
SELECT [Link] , [Link]
FROM prod , produit
WHERE [Link] = 'ordinateur' AND [Link] > [Link];

Solution 2 : Avec utilisation des alias.

SELECT [Link], [Link]
FROM produit p1 , produit p2
WHERE [Link] = 'ordinateur' AND [Link] > [Link];

FSS 69
La jointure
La jointure externe: En plus des opérateurs de jointure classiques, la
jointure externe permet de ramener toutes les lignes satisfaisant aux
critères de jointure même si le reste des valeurs d'une ligne donnée est
nul.

De point de vue syntaxique, cette opération se formule de la même façon

qu'une jointure classique sauf qu'il faut ajouter à la condition de la jointure
le signe : (+).

Dans la condition de la jointure, il faut placer le champ "clé primaire" avant

le champ "clé secondaire".

FSS 70
 La jointure externe
Exemple : On considère les tables Produit et Ligne_Commande suivantes :

Donner les numéros des produits avec

éventuellement les commandes
correspondantes.

Select [Link] , [Link]

From produit P , Ligne_commande LC
Where [Link] = [Link] (+) ;

FSS 71
Manipulation des vues (View)
Une vue est une table virtuelle, Il s'agit d'une représentation des données
dans le but d'une exploitation visuelle.
Une vue est stockée sous forme de requête de sélection dans le
dictionnaire de données et permet de constituer les schémas externes.
L'utilisation des vues permet de :
 Restreindre l'accès à certaines colonnes et certaines lignes d'une table en
autorisant un ou plusieurs utilisateurs à manipuler cette table qu'à travers une
vue.
 Simplifier la tâche de l'utilisateur en le déchargeant de la formulation de
requêtes complexes

CREATE VIEW nom_de_la_vue [(col1 , …..,coln )]

As requête
[WITH CHECK OPTION] ;
Si on met WITH CHECK OPTION on ne peut pas :
 Insérer des enregistrements qui ne vérifient pas la condition de la création de la
vue.
 Modifier des enregistrements si la modification n'est pas conforme à la
condition de la création de la vue.
FSS 72
Manipulation des vues (View)
Exemples :
Créer une vue ProdR1 contenant tous les produits de couleur rouge.
Create View ProdR1
As Select *
From Produit
Where Couleur = 'R';

Créer une vue ProdR2 contenant tous les produits de couleur rouge. Ajouter l'option
de vérification de condition de création.
Create View ProdR2
As Select *
From Produit
Where Couleur = 'R'
With check option;

Créer une vue Vprod contenant les numéros et les désignations des produits, leurs
prix moyen et leur quantité totale en stock.
Create View Vprod (num, des, prixmoyen, qte)
As Select numprod, desprod , AVG(prix), SUM(qte_stock)
From produit
Group By desprod ;

FSS 73
Manipulation des vues (View)
Suppression d’une vue : Elle consiste à supprimer du dictionnaire de
données la définition correspondante à cette vue. Les données rattachées
à la vue restent dans leurs tables.

DROP VIEW nom_de_la_vue ;

 Exemple :
Supprimer la vue ProdR1.
Drop view ProdR1;

Consultation d’une vue : Toutes les requêtes de consultation possibles sur

les tables le sont aussi sur les vues.

 Exemple
Afficher les désignations des produits, leurs prix moyen, leur quantité totale
en stock et les numéros de commandes correspondants, en utilisant la vue
Vprod.
Select [Link], prixmoyen, qte, numcde
From Vprod V, Ligne_Commande LC
Where [Link]=[Link] ;
FSS 74
Manipulation des vues (View)
Mise à jour d’une vue :
 Si la vue dérive de plusieurs tables ou si sa définition contient la clause GROUP
BY, DISTINCT ou n'importe quelle fonction de groupe, alors les opérations
DELETE, INSERT et UPDATE sont interdites.

 Si un attribut de la vue est défini comme une expression, cet attribut ne peut
subir d'UPDATE et l'opération INSERT est interdite sur toute la vue.

 Si un attribut déclaré NOT NULL dans la table de base n'est pas repris dans la
vue alors aucune insertion n'est autorisée sur cette vue.

FSS 75
Langage de Contrôle des Données (LCD)

FSS 76
Gestion des utilisateurs
Création d’un utilisateur:
GRANT [ CONNECT / , RESOURCE / , DBA ]
TO utilisateur
IDENTIFIED BY mot de passe ;

Avec l'option CONNECT l'utilisateur peut :

 Se connecter à ORACLE et Créer des vues et des synonymes sur des tables existantes.

 Sélectionner, modifier et supprimer des données dans des tables sur lesquelles une
autorisation a été donnée au préalable.
 MAIS ne peut pas créer de tables ni de clusters ni d'index.

L'option RESOURCE ne peut être attribuée que si l'utilisateur a le droit CONNECT. Elle lui
permet:
 De créer des tables, des index et des regroupements (des clusters).

 Attribuer ou enlever des privilèges sur les tables, index et clusters à d'autres utilisateurs.

L'option DBA englobe les droits des deux options précédentes et permet en plus :
 D'accéder aux données de tous les utilisateurs de la même base.

 De créer et de supprimer des utilisateurs et des droits.

 D'administrer la base (sauvegarder et restaurer des données de la base, contrôler
l'intégrité,...).
FSS 77
Gestion des utilisateurs

Changement du mot de passe d’un utilisateur: on utilise la commande

suivante

ALTER USER nom_utilisateur IDENTIFIED BY Nouveau_motpasse;

Suppression d’un utilisateur : Pour supprimer un utilisateur, on utilise la

commande suivante:

DROP USER nom_utilisateur [CASCADE] ;

 L'option "cascade" est utilisée pour pouvoir supprimer l'utilisateur et tous les objets de son
schéma.

FSS 78
Création des droits
Toute table ou synonyme n'est initialement accessible que par l'utilisateur
qui l'a créé.
Pour partager certains droits avec d'autres utilisateurs, le propriétaire doit
utiliser l'instruction :

GRANT droit1 , droit2 , .... , droit n

ON objet
TO utilisateur1 , utilisateur2 , ... , utilisateur p
[ WITH GRANT OPTION ];

 On peut utiliser ALL pour désigner tous les droits et PUBLIC pour désigner tous
les utilisateurs.
 Les droits possibles sont : ALTER, DELETE, INDEX, INSERT, UPDATE, SELECT,
ou aussi ALL pour toutes les opérations.
 Les objets sont : les noms des tables et des vues.

 L'option WITH GRANT OPTION : autorise le nouvel utilisateur à accorder les

droits reçus à un autre utilisateur.
FSS 79
 Création des droits
Exemple
L'utilisateur User1 décide d'attribuer à l'utilisateur User2 le droit de sélection et
de mise à jour à sa table commandes
GRANT SELECT, UPDATE
ON commandes
TO User2;

L'utilisateur User2 peut créer une table cde à partir de la table commandes de User1

CREATE TABLE cde

AS SELECT * FROM [Link] ;

FSS 80
Suppression des droits
La commande qui permet de supprimer un ou plusieurs droits sur un objet
est :

REVOKE [ GRANT OPTION FOR ] droit1 , droit2 , … , droit n

ON objet
FROM utilisateur1 , utilisateur2 , ... , utilisateur p ;

 Avec REVOKE aussi on peut utiliser ALL pour désigner tous les droits et
PUBLIC pour désigner tous les utilisateurs.

 L'option "grant option for", permet de supprimer le droit d'un utilisateur et à

accorder des permissions à un autre utilisateur.

Exemple
REVOKE ALL
ON commande
FROM PUBLIC ;

FSS 81
 Suppression des droits
Exemple
L'utilisateur User1 décide d'attribuer à l'utilisateur User2 le droit de sélection et
de mise à jour à sa table commandes
GRANT SELECT, UPDATE
ON commandes
TO User2;

L'utilisateur User2 peut créer une table cde à partir de la table commandes de User1

CREATE TABLE cde

AS SELECT * FROM [Link] ;

FSS 82
Annexe : Les fonctions intégrées de SQL

FSS 83
Les fonctions intégrées de SQL
Les expressions arithmétiques : dans les clauses select et where.
 ABS(n) : permet de calculer la valeur absolue de n.
 CEIL(n) : permet d'avoir le plus petit entier supérieur ou égal à n.
Ceil(128.3) retourne 129
Ceil(128.8) retourne 129
 FLOOR(n) : permet d'avoir la partie entière de n.
Floor(128.3) retourne 128
Floor(128.8) retourne 128
 MOD(m,n) : permet d'avoir le reste de la division entière de m par n.
 ROUND(n,m) : arrondit la valeur n à m décimale.
Round(128.3) retourne 128
Round(128.8) retourne 129
Round(128.123,2) retourne 128.12
 POWER(m,n) : permet d'avoir m puissance n
 SIGN(n) : donne –1 si n <0, donne 0 si n=0 et donne 1 si n>1.
 SQRT(n) : permet d'avoir √x.
 TRUNC(n,m) : permet de tronquer la valeur n après m décimales. Si m est négatif, la
valeur de n est tronquée avant le point décimal.
TRUNC (121.371,2) donne 121.37
TRUNC (121.371,-2) donne 100

FSS 84
 Les fonctions intégrées de SQL
Exemples
Afficher les désignations et les prix arrondis en Dinars de tous les produits.
SELECT desprod, ROUND(prix) "Prix en D"
FROM produit ;

En supposant que les numéros des produits sont exactement sur trois positions,
afficher les désignations des produits dont le numéro commence par 1.
Select desprod
From produit
Where trunc(numprod,-2)=100;

Modifier la table produit de manière à majorer les prix en dinars.

Update Produit
Set prix=ceil(prix);

Supprimer tous les produits dont le prix Appartient à [100,101[.

Delete from Produit
Where floor(prix) = 100;
FSS 85
Les fonctions intégrées de SQL
Les fonctions s’appliquant aux chaînes de caractères :
 RTRIM (ch) : supprime les espaces à la fin de la chaîne  LTRIM.
 RPAD (ch,n) : ajoute n espaces à la fin de la chaîne  LPAD.
 INITCAP(ch) : met en majuscule la première lettre de chaque mot de la chaîne.
 INSTR(ch1,ch2,n,m) : donne la position de la mième occurrence de ch2 dans ch1
à partir du caractère à la position n.
 LENGTH(ch) : renvoie la longueur d'une chaîne.
 LOWER(ch) : transforme la chaîne ch en minuscule  UPPER.
 SUBSTR(ch,m,n) : permet d'extraire la sous-chaîne de ch qui commence à partir
du caractère à la position m et de longueur n.
 TRANSLATE(ch,ch1,ch2) : permet de transformer dans la chaîne ch toutes les
occurrences de ch1 par ch2.
 Replace(ch, ch1[,ch2]) : remplace une chaîne par une autre dans une colonne.
Si ch2 est non spécifiée, ch1 sera remplacée par un vide.
 ch1ch2 : concatène les deux chaînes.

FSS 86
 Les fonctions intégrées de SQL
Exemples

Afficher les numéros des produits correspondants à des souris.

Select numprod
From produit
Where upper(ltrim(rtrim(desprod)))= 'SOURIS';

Supprimer les produits dont la désignation est composée au maximum de 6

caractères.
Delete from produit
Where length(desprod)<= 6;

Afficher les désignations des produits avec seule la première lettre en majuscule.
Select distinct initcap(lower(desprod)) désignation
From produit;

FSS 87
 Les fonctions intégrées de SQL
Exemples

Afficher les numéros et couleurs des produits dont la désignation contient "er" à
partir de la 6ème position.
Select numprod,couleur
From produit
Where lower(substr(desprod,6,2)='er';

Afficher toutes les désignations des produits en remplaçant toute "a" par "A".
Select distinct translate(desprod,'a','A') désignation
From produit;

Modifier la table produit en remplaçant les Scanners en Imprimantes.

Update produit
Set desprod = replace(desprod,'scanner','imprimante');

FSS 88
 Les fonctions intégrées de SQL
Exemples

Ajouter le champ Propriete de type caractère variable sur 4 positions formé des trois
premiers caractères de la désignation concaténés à la couleur.

Alter table produit

Add propriete varchar(4);

Update produit
Set propriete= substr(ltrim(desprod),1,3)|| couleur;

FSS 89
Les fonctions intégrées de SQL
Les fonctions s’appliquant à des dates:

 ADD_MONTHS(d,n) : permet d'ajouter n mois à la date d sachant que n est un

entier.
 GREATEST(d1,d2) : permet d'avoir la date la plus grande parmi d1 et d2 
LEAST.
 MONTHS_BETWEEN(d1,d2) : permet d'avoir le nombre de mois qui se trouvent
entre la date d1 et la date d2. (avec d1 > d2)
 LAST_DAY(d) : permet d'avoir la date du dernier jour du mois de la date d.

 SYSDATE : donne la date système.

FSS 90
 Les fonctions intégrées de SQL
Exemples : Soit la table
Employe( Matricule,nom,prenom,dateNais,DateEmb)

En supposant qu'un employé est à la retraite à l'âge de 60 ans, afficher les noms et
prénoms des employés ainsi que les dates prévus pour leurs retraites.
Select nom,prenom,add_months(dateNais,720) "Retraite"
From employe;

En supposant qu'un employé est à la retraite après 30 ans de service, afficher les
noms et prénoms des employés ainsi que les dates prévus pour leurs retraites.
Select nom,prenom,add_months(dateEmb,360) "Retraite"
From employe;

En supposant qu'un employé est à la retraite après 30 ans de service ou à l'âge de 60

ans, afficher les noms et prénoms des employés ainsi que les dates prévus pour
leurs retraites.
Select nom, prenom, least(add_months(dateNais,720),
add_months(dateEmb,360)) "Retraite"
From employe;
FSS 91
 Les fonctions intégrées de SQL
Exemples : Soit la table
Employe( Matricule,nom,prenom,dateNais,DateEmb)

Afficher le matricule et l'âge en mois de l'employé lors de son embauche.

Select matricule, months_between(dateemb,datenais) "Age en mois"
From employe;

Afficher le matricule et l'âge en années de l'employé lors de son embauche.

Select matricule, floor(months_between(dateemb,datenais)/12) "Age "
From employe;

Afficher le matricule et le nombre de jours de travail pendant le premier

mois d'embauche de chaque employé.
Select matricule, last_day(dateEmb)-dateEmb + 1 "Nbre Jour"
From employe;

FSS 92
 Les fonctions intégrées de SQL
Les fonctions de conversion:
 TO_CHAR(valeur-date,format-date) / TO_CHAR(nombre[,format]) : convertit
une date ou une valeur numérique à une chaîne de caractères.
 TO_DATE(valeur-chaîne,format-date) : convertit une chaîne de caractères
représentant une date à une date.
 TO_NUMBER(ch[,format]) : convertit une chaîne de caractères représentant un
nombre en nombre.
Quelques formats de dates :
YYYY Année sans virgule
YY 2 derniers chiffres de l’année
Q Numéro de trimestre de l’année (1 à 4)
WW Numéro de semaine de l’année (1 à 52)
W Numéro de semaine dans le mois
MM Numéro du mois (1 à 12)
DDD Numéro de jour dans l’année (1 à 366)
DD Numéro du jour dans le mois (1 à 31)
D Numéro de jour dans la semaine (1 à 7)
YEAR Année en toute lettre
MON Nom du mois abrégé en toute lettre
DAY Nom du jour sur 9 caractères
DY Nom du jour abrégé en 3 lettres

FSS 93
 Les fonctions intégrées de SQL
Exemples :

Afficher les employés embauchés pendant l'année en cours.

Select *
From employe
Where to_char(dateEmb,'YYYY') = to_char(sysdaye,'YYYY');

Afficher les employés ayant au moins 3 ans d'ancienneté.

Select *
From employe
Where to_number(to_char(sysdate,'YYYY')) –
to_number(to_char(date_emb,'YYYY')) >= 3;

FSS 94