TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

Lab 01 : Oracle SQL - les bases

Dans ce TP, nous allons introduire le langage SQL. Pour se faire, les instructions et les commandes SQL
sont présentées et testées en utilisant une simple base de données de gestion de stock. Le TP commence par
une brève introduction à SQL, suivie de trois parties concernant la définition des données avec SQL, leurs
manipulations, et enfin la consultation des données sous SQL.

Objectifs : à l’issue de ce TP, vous seriez en mesure de créer, manipuler et consulter des bases de
données relationnelles en utilisant le langage SQL. Plus précisément vous seriez en mesure de :

- Créer, modifier et supprimer des tables via le langage de définitions de données SQL.

- Insérer, mettre à jour et effacer des enregistrements de bases de données en utilisant le langage de
manipulation de données SQL.

- Consulter des bases de données relationnelles avec la commande SELECT.

Partie 01 : Introduction à SQL

Cette partie est consacrée à l’introduction d’SQL ainsi que le modèle entité/associations de l’application
simplifie de gestion de stock utilisée dans l’apprentissage d’SQL au long de ce TP.
1. Bref historique de SQL
En 1970, le Dr E.F. Codd a publié "A Relational Model of Data for Large Shared Data Banks", un article
qui décrivait un modèle pour stocker et manipuler des données à l'aide de table. Peu de temps après, IBM a
commencé à travailler sur la création d'une base de données relationnelle. Entre 1979 et 1982, Oracle (alors
Relational Software, Inc.), Relational Technology, Inc. (plus tard acquise par Computer Associates) et IBM
ont mis en place des bases de données relationnelles commerciales. En 1986, American National Standards
Institute (ANSI) a standardisé SQL (Structured Query Language) comme langage informatique pour stocker,
manipuler et récupérer des données stockées dans une base de données relationnelle. Tous les systèmes de
gestion de bases de données relationnelles (SGBD-R) tels que MySQL, MS Access, Oracle, Sybase, Informix,
PostgreSQL et MS SQL Server utilisent SQL comme langage standard. En outre, Les SGBD-R utilisent
différents dialectes, tels que: T-SQL (MS SQL Server), PL/SQL (Oracle), JET SQL (MS Access).
2. Les commandes SQL
Les commandes SQL standard pour interagir avec les bases de données relationnelles commencent par des
mots-clés comme CREATE, SELECT, INSERT, UPDATE, DELETE et DROP. Toutes les instructions
SQL se terminent par un point-virgule (;). Un point important à noter est que SQL est insensible à la
casse, ce qui signifie que SELECT et select ont la même signification dans les instructions SQL. Les

1
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

commandes SQL peuvent être classées en groupes en fonction de leurs natures comme on peut le voir dans
Tableau 1:
Table 1 les groupes des commandes SQL
 CREATE
 ALTER
Langage de définition de données (LDD)  DROP
 RENAME
 TRUNCATE
 INSERT
 UPDATE
Langage de manipulation de données (LMD)  DELETE
 MERGE

Extraction de données  SELECT

Langage de contrôle de données (LCD)  GRANT

 REVOKE
 COMMIT
Contrôle des transactions  ROLLBACK
 SAVEPOINT

3. Les commentaires en SQL

Les commentaires peuvent rendre votre application plus facile à lire et maintenir. Dans SQL, les
commentaires peuvent apparaître sur une seule ligne ou sur plusieurs comme suit :
 /* commentaires sur plusieurs lignes */
 -- commentaires sur une seule ligne
Vous pouvez associer un commentaire à une table, une vue ou une colonne à l'aide de la commande
COMMENT. Les commentaires associés aux objets de schéma sont stockés dans le dictionnaire de données.
Veuillez-vous référer à la commande COMMENT pour une description des commentaires.
Dans Oracle, vous pouvez utiliser des commentaires pour passer des instructions à l’Optimiseur Oracle.
TIPS
Voir https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements006.htm#SQLRF51101 pour plus
d’information sur les commentaires, la commande COMMENT et les commentaires pour L’optimiseur Oracle

4. Exemple d’apprentissage d’SQL

Dans le reste de ce TP, on va utiliser une simple application de gestion de stock pour apprendre les
commandes SQL. Le diagramme Entité/Association de telle application est donné dans la figure 1.
TODO :

2
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

Partie 02: Langage de Définition de Données LDD (basic)

Dans cette partie, nous nous intéressons aux commandes SQL qui constituent le LDD (CREATE, ALTER,
DROP, RENAME, TRUNCATE), en les appliquant sur l’objet de base de données: table.
1. Création de tables
Une table est une structure tabulaire dont les colonnes représentent des attributs, et les lignes représentent
des tuples. Les attributs sont contrôlés par des règles appelées contraintes d’intégrités. Une contrainte
d’intégrité peut être une contrainte de colonne lorsqu’elle fait partie de la définition d’une colonne ou une
contrainte d’intégrité de table quand elle concerne un ensemble de colonnes de la table.
SYNTAXE DE BASE
CREATE TABLE nom_table (
nom_colonne1 type_std|nom_domaine contrainte_col,
nom_colonne2 type_std|nom_domaine contrainte_col,
……
nom_colonneN type_standard | nom_domaine contrainte_colonne
[liste_contrainte_tab]
);
type_std = NUMBER[(précision, échelle)]|DECIMAL[(précision, échelle)]|
INTEGER|FLOAT|CHAR[(nbMaxChar)]|VARCHAR|VARCHAR2(Oracle8)|LONG|DATE|…
contrainte_col = [CONSTRAINT nom_contrainte] type_contrainte_col
type_contrainte_col = PRIMARY KEY|NOT NULL|CHECK(condition)|UNIQUE|
DEFAULT valeur|REFERENCES nom_table (liste_colonne)
liste_contrainte_tab = PRIMARY KEY(liste_col)|UNIQUE(liste_col)| FOREIGN
KEY (liste_col)| CHECK(condition)|REFERENCES nom_table(liste_col)

TIPS
Voir ‘Chapter 5 : SQL Data Types’ du « SQL Tutorial » pour les types de données standard et ‘Chapter 5 : SQL
Constraints’ pour se familiariser avec les contraintes SQL

Activite2.a : créer les tables client, commande, article et ligne-com comme suit :

3
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

CREATE TABLE client (

idclient NUMBER CONSTRAINT u_id PRIMARY KEY,
nom VARCHAR2(20) CONSTRAINT nn_nom NOT NULL,
adresse VARCHAR2(80),
codepost NUMBER(5) CHECK (codepost < 99999),
ville VARCHAR2 (20),
tel NUMBER(8)
);
CREATE TABLE commande (
numcom NUMBER CONSTRAINT p_ncom PRIMARY KEY,
idclient NUMBER CONSTRAINT fk_idcli REFERENCES client (idclient),
datecom DATE
);
CREATE TABLE article (
idarticle NUMBER CONSTRAINT pk_idart PRIMARY KEY,
designat VARCHAR2(30)
);
CREATE TABLE ligne_com (
numcom NUMBER CONSTRAINT fk_ncom REFERENCES commande (numcom),
idarticle NUMBER CONSTRAINT fk_idart REFERENCES article,
qtecom NUMBER CONSTRAINT nn_qte NOT NULL CHECK (qtecom > 0),
PRIMARY KEY (numcom, idarticle)
);

La structure des tables ainsi crée pourra être consulté en utilisant la clause DESC:

DESC table_name;

Une nouvelle table peut être créée à partir d’une table existante en combinant l'instruction CREATE TABLE
et la commande SELECT. Lorsque une table est créé de cette, la nouvelle table sera remplie en utilisant les
valeurs existantes dans l'ancienne.
Activité 2.b : créer la table client2 à partir de la table client on utilisant les instructions suivantes (la
commande SELECT sera étudier on détails dans la Partie 4 de ce TP)
CREATE TABLE client2 (nom, adr1, adr2)
AS SELECT nom, adresse, codepost||’,’||ville
FROM client;

4
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

2. Supprimer une table existante

SQL permet de supprimer des objets de base de données tels que les tables on utilisant la syntaxe suivante:
SYNTAXE DE BASE
DROP TABLE [schéma.]table_name[CASCADE CONSTRAINTS];

Activité 2.c : Supprimer la table client2 en utilisant la syntaxe de base ci-dessus.

3. Modification des structures de tables
SQL permet les modifications suivantes sur la structure d’une table existante : (a) ajout et suppression de
nouvelles colonnes, (b) modification de type d’une colonne, (c) modification des caractéristiques de stockage,
(d) ajout, activation, désactivation et suppression de contraintes d’intégrité.
SYNTAXE DE BASE
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_type};
ALTER TABLE table_name RENAME TO new_table_name;

Activité 2.d : Modifier la structure de table client en appliquant les instructions suivantes une à une. Utiliser
la commande DESC pour afficher la structure de la table modifiée après chaque opération.
ALTER TABLE client
ADD remarque VARCHAR2(80);
MODIFY ville VARCHAR2(30);
DISABLE / ENABLE CONSTRAINT nn_nom;
DROP CONSTRAINT nn_nom CASCADE;

N.B : l’option CASCADE permet de supprimer toutes les clés étrangères qui se réfèrent à la clé primaire.
4. suppression des lignes d’une table
La commande SQL TRUNCATE TABLE est utilisée pour supprimer toutes les données d'une table
existante. Sa syntaxe de base est comme suit :
SYNTAXE DE BASE
TRUNCATE TABLE [schéma.]table[{DROP|REUSE} STORAGE]

DROP STORAGE permet de libérer l’espace des lignes supprimées et REUSE STORAGE permet de
maintenir l’espace des lignes supprimées.
Activité 2.e : Essayer d’effacer toutes les lignes de la table article en utilisant la commande ci-dessous
(jusqu’ici, la table article est vide, on revient sur cette commande dans la Partie 03).
TRUNCATE TABLE article REUSE STORAGE;

5
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

Partie 03 : Langage de Manipulation de Données (LMD)

Dans cette partie, nous nous intéressons à l’ensemble des commandes constituant le LMD (INSERT,
UPDATE, DELETE) permettant la mise à jour des objets créés par le LDD. La mise à jour englobe
l’insertion, la modification et la suppression de données.
1. Insertion de données
La commande INSERT INTO est utilisée pour ajouter de nouvelles lignes de données à une table dans la
base de données. Il existe deux syntaxes de base de l'instruction INSERT INTO comme suit:
SYNTAXE DE BASE
INSERT INTO table_name(col1, col2....colN) VALUES (val1, val2....valN);
INSERT INTO TABLE_NAME VALUES (val1,val2, val3,...valN);

Activité 3.a : Utiliser la syntaxe d’insertion ci-dessus pour insérer quelques lignes dans les tables de notre
base de données comme dans cet exemple :
INSERT INTO client VALUES (1, ‘Omar’, 45, ‘Rue 5 juillet’, 16000, ’Alger’,
02863499);
INSERT INTO client (idclient, nom, adresse, codepost, ville, tel)
VALUES (2, ‘Nacer’ , ‘35’, ‘Rue 5 juillet’, 31000, ‘Oran’, NULL);
…

Les données peuvent être aussi insérer dans une table à partir d’une autre, en utilisant les commandes
INSERT INTO et SELECT comme suit :
SYNTAXE DE BASE
INSERT INTO first_table_name [(col1, col2, ... colN)]
SELECT col1, col2, ...colN
FROM second_table_name
[WHERE condition];);

2. Modification de données
La requête SQL UPDATE est utilisée pour modifier les enregistrements existants dans une table. Vous
pouvez utiliser la clause WHERE avec la requête UPDATE pour mettre à jour les lignes sélectionnées, sinon
toutes les lignes seraient affectées.

6
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

SYNTAXE DE BASE
UPDATE table_name
SET col1 = val1, col2 = val2...., colN = valN
[WHERE condition];

Activité 3.b : en utilisant la syntaxe de base ci-dessus, modifiez des enregistrements de notre base de données.
Vous pouvez s’inspirer de l’exemple suivant :
UPDATE client SET adresse = ‘52, Rue BDD’ ;
UPDATE client SET ville = ’New City’
WHERE idclient = 1 ;
UPDATE client SET (adresse, ville, codepost, tel) = (SELECT adr, ville,
code, tel FROM client_priv WHERE idc = 2)
WHERE idclient = 2 ;

N.B : C’est la 3eme fois qu’on tombe sur la commande SELECT, elle est si fréquente, elle semble très
importante, elle sera étudier on détails dans la partie suivante de ce TP.
3. Suppression de données
La requête SQL DELETE est utilisée pour supprimer les enregistrements existants d'une table. La clause
WHERE est utiliser avec la requête DELETE pour supprimer les lignes sélectionnées, sinon tous les
enregistrements seraient supprimés.
SYNTAXE DE BASE
DELETE FROM table_name
[WHERE condition];

Activité 3.c : Utiliser la commande DELETE pour supprimer quelques enregistrements dans les tables de
notre base de données comme dans l’exemple suivant. Quelle est la différence entre DELETE et la
commande TRUNCATE vue dans le LDD (Partie 02 de ce TP). Utiliser TRUNCATE pour effacer toutes
les lignes de la table article.
DELETE FROM commande; {effacer toutes les lignes de la table commande}
DELETE FROM commande
WHERE numcom = 4; {effacer toutes les lignes de la commande n°4}

7
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

Partie 04 : Consultation de données (la commande SELECT)

Cette partie est consacrée à la consultation des données avec la commande SELECT. Cette commande SQL
est très commune car il est très fréquent de devoir lire les données issues d’une base de données relationnelle.
1. Consultation simple
Il existe deux syntaxes de base pour la consultation simple en utilisant SELECT comme suit:
SYNTAXE DE BASE
SELECT col1, col2, colN FROM table_name;
SELECT * FROM table_name ; {le symbole * indique toutes les colonnes}

Activité 1.a : Consulter les données insérées dans les tables de notre base de données en utilisant la
consultation simple avec SELECT. Ci-dessous sont deux exemples :
SELECT * FROM client;
SELECT nom, tel FROM client;
…

N.B : (1) un nom d’attribut peut être qualifié par le nom d’une relation (ex. client.nom). Un nom d’attribut
non qualifié (ex. tel) référence la relation la plus interne qui a un attribut de ce nom-là. (2) On peut renommer
localement une relation dans la clause FROM (ex. FROM client c). La relation client s'appelle alors c
pour le SELECT correspondant à ce FROM uniquement.
Activité 1.b : Reprendre les consultations simples de l’Activité 1.a, en utilisant les deux règles ci-dessus.
2. Consultation avec qualification
La qualification se fait à l’aide de la clause WHERE suivie d’une ou plusieurs conditions. Ces dernières sont
groupées en trois familles :
 les opérateurs de comparaison.
 les conditions de jointure
 les conditions de sous-requêtes
SYNTAXE DE BASE
SELECT *|col1, col2, colN FROM table_name;
WHERE Condition_de_requête

2.1. SQL Opérateurs:

Un opérateur est un mot réservé ou un caractère utilisé principalement dans la clause WHERE d'une
instruction SQL pour effectuer des opérations, telles que des comparaisons (=, >, <, <>, !=, >=, <=, !<,

8
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

!>), des opérations arithmétiques (+, -, *, /, %) et des opérations logiques (ALL, AND, ANY, BETWEEN,
EXISTS, IN, LIKE, NOT, OR, IS NULL, UNIQUE) ou toutes combinaisons.
TIPS
Voir ‘Chapter 6 : SQL Operators’ du « SQL Tutorial » pour plus de détails concernant les opérateurs SQL

Activité 2.a : Utiliser la consultation avec qualification pour faire des requêtes avec conditions. Quelques
exemples sont donnés ci-dessous à titre indicatif. Essayer de tester un maximum d’opérateurs mentionnés
ci-dessus.
SELECT * FROM article
WHERE prixunit > 100 AND qtestock <= 80;
SELECT * FROM commande
WHERE datecom NOT BETWEEN ‘01-JAN-93’ AND ‘31-MAR-93’;
SELECT * FROM client
WHERE ville IN (‘ALGER’, ‘ORAN’, ‘BATNA’);
SELECT * FROM client
WHERE nom LIKE ‘%nd_’; {les deux avant-dernier caractères sont ‘nd’}
SELECT * FROM client WHERE tel IS NOT NULL;

N.B : Dans l’opérateur LIKE, le signe de pourcentage (%) représente zéro, un ou plusieurs caractères. Le
trait de soulignement (_) représente un seul chiffre ou caractère. Ces symboles peuvent être utilisés en
combinaison.
2.2. Conditions de jointure
Comme vous l’avez observé, toutes les opérations ci-dessus récupèrent l’information à partir d’une seule
table. Les conditions de jointure introduisent la possibilité de consulter plusieurs tables.
SYNTAXE DE BASE
SELECT coli…
FROM tab1, tab2…, tabn
WHERE condition_de_jointure_entre_les_tabi
AND conditions_de_requête

Equijointure : la condition de jointure est une comparaison d’égalité de deux colonnes appartenant à deux
tables différentes.
Activité 2.b : donner les identifiant des clients, leurs noms, ainsi que les date de commandes faites par des
habitants d’Alger entre 01 Janvier 2010 et aujourd’hui. La requête ci-dessous peut répondre a la question,
mais d’autres sont aussi possibles (essayer la commande JOIN).

9
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

SELECT client.idclient, nom, datecom

FROM client, commande
WHERE client.idclient = commande.idclient
AND ville = ‘Alger’
AND datecom BETWEEN ‘01-JAN-10’ AND SYSDATE;

NB : SYSDATE renvoie l'heure à laquelle la fonction s'exécute, c’est l’une des fonctions date SQL.
TIPS
Consulter ‘Chapter 37 : SQL Date Functions’ du « SQL Tutorial » pour plus de détails concernant les fonctions
date SQL.

Jointure multiple : la condition de jointure est une comparaison d’égalité de plusieurs colonnes appartenant
à plusieurs tables différentes.
Activité 2.c : Exécuter la requête ci-dessous, en précisant à quelle question elle répond. Essayer de répondre
à la même question en utilisant la commande JOIN.
SELECT article.idarticle, designation
FROM article, ligne_com, commande
WHERE article.idarticle = ligne_com.idarticle
AND commande.numcom = ligne_com.numcom
AND commande.datecom BETWEEN ‘01-JAN-93’ AND SYSDATE ;

Auto-jointure : c’est une jointure d’une table avec elle-même. Elle est nécessaire pour répondre à des
requêtes comparant les éléments d’une même table entre eux.
Activité 2.d : Liste des couples d’article dont la quantité en stock du premier est égale à deux fois celle du
second. La requête ci-dessous peut répondre à la question. Remarquer l’utilisation des attributs qualifiés ainsi
que le renommage temporaire locale des tables.
SELECT a.idarticle, a.qtestock, b.idarticle, b.qtestock
FROM article a, article b
WHERE a.qtestock = 2* b.qtestock ;

2.3. Conditions de sous-requêtes

Une sous-requête ou une requête interne ou une requête imbriquée est une requête dans une autre requête
SQL et incorporée dans la clause WHERE. Une sous-requête est utilisée pour renvoyer les données qui
seront utilisées dans la requête principale comme condition pour restreindre davantage les données à
récupérer. Les sous-requêtes peuvent être utilisées avec les instructions SELECT, INSERT, UPDATE et
DELETE avec les opérateurs comme =, <,>,> =, <=, IN, BETWEEN…etc.

10
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

SYNTAXE DE BASE
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE condition])

Activité 2.e : Liste les clients habitant Alger qui ont passé une commande entre la période du ’01 Janvier
2010’ et aujourd’hui. Comparer cette requête avec celle de l’Activité 2.b.
SELECT DISTINCT idclient FROM commande
WHERE idclient IN (SELECT idclient FROM client
WHERE ville = ‘ALGER’)
AND datecom BETWEEN ‘01-JAN-10’ AND SYSDATE;

Activité 2.f : Liste de clients qui ont passé au moins une commande entre la période du ’01 Janvier 2010’ et
aujourd’hui et dont la quantité commandé est égale à la quantité en stock. Expliquer la requête ci-dessous et
dire est ce qu’elle répond à cette question.
SELECT * from client x
WHERE idclient = (SELECT UNIQUE idclient from commande y
WHERE x.idclient = y.idclient
AND datecom BETWEEN ‘01-JAN-93’ AND SYSDATE
AND numcom IN (SELECT numcom from ligne_com z
WHERE y.numcom= z.numcom
AND qtecom=(SELECT qtestock FROM article t
WHERE t.idarticle = z.idarticle)
)
);

Activité 2.i : Revoir les CREATE et INSERT instructions où le SELECT a été utilisé.
TIPS
Consulter ‘Chapter 40 : SQL Sub Queries’ du « SQL Tutorial » pour connaitre quelques règles que les sous-
requêtes doivent suivre.

3. Consultation avancée avec SELECT

Après avoir se familiariser avec deux cas de consultation avec SELECT, cette section introduit le format
générique de SELECT avec quelques fonctionnalité avancées.

11
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

3.1. Fonctions d’agrégation

SQL offre les fonctions d'agrégation usuelles telles que: cardinal COUNT(), moyenne AVG(), minimum
MIN, maximum MAX, total SUM()…etc., qui opèrent sur un ensemble de valeurs prises par un attribut, ou
pour COUNT uniquement, sur un ensemble de tuples.
TIPS
Voir ‘Chapter 44 : SQL Useful Functions’ du « SQL Tutorial » pour plus de détails concernant les fonctions
d’agrégation SQL

Activité 3.a : Tester les exemples ci-dessous et expliquer le résultat obtenu.

SELECT COUNT(*) AS "record_count" FROM Client;
SELECT COUNT (DISTINCT idclient) from line_comm ;

N.B : (1) Le mot clé SQL DISTINCT est utilisé conjointement avec l'instruction SELECT pour éliminer
tous les enregistrements en double et extraire uniquement des enregistrements uniques. (2) Le mot clef AS
représente un alias.
TIPS
Voir le ‘Chapter 29 : SQL Alias Syntax’ du « SQL Tutorial » pour plus de détails sur les alias et le ‘Chapter 23 :
SQL Distinct Keyword’ pour se familiariser avec le mot clef DISTINCT.

3.2. La clause ORDER BY

La clause SQL ORDER BY est utilisée pour trier les données par ordre croissant ou décroissant, sur la base
d'une ou plusieurs colonnes. Certaines SGBD-R, comme Oracle, trient les résultats de la requête par ordre
croissant par défaut.
SYNTAXE DE BASE
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

N.B : Assurez-vous que la colonne que vous utilisez pour trier, doit être dans la liste des colonnes.
Activité 3.b: Essayer la commande ORDER BY en utilisant des requêtes de votre choix. Voici un exemple
SELECT * FROM client
ORDER BY nom;
SELECT * FROM client
ORDER BY idclient DESC;

12
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

3.3. Clauses GROUP BY et HAVING

GROUP BY : La clause SQL GROUP BY est utilisée en collaboration avec l'instruction SELECT pour
organiser des données identiques en groupes. La clause GROUP BY suit la clause WHERE dans une
instruction SELECT et elle doit précéder la clause ORDER BY si elle est utilisée.
SYNTAXE DE BASE
SELECT column-list
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, .. columnN]
[HAVING condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
;
N.B : (1) Assurez-vous que la colonne que vous utilisez pour trier ou grouper, doit être dans la liste des
colonnes. (2) Pour exécuter l’instruction GROUP BY, le SGBD groupe les tuples selon les colonnes de
groupage puis évalue le résultat du SELECT sur les groupes.
Activité 3.c: Essayer la commande GROUP BY en utilisant des requêtes de votre choix. Voici un exemple
SELECT * FROM client
GROUP BY ville;
SELECT ville, COUNT(*) as habitants FROM client
GROUP BY ville;

HAVING : La clause HAVING vous permet de spécifier des conditions qui filtrent quels résultats de
groupe apparaissent dans les résultats finaux. La clause WHERE place des conditions sur les colonnes
sélectionnées, tandis que la clause HAVING place des conditions sur les groupes créés par la clause GROUP
BY.
N.B : La clause HAVING doit suivre la clause GROUP BY dans une requête et doit précéder la clause
ORDER BY si elle est utilisée.
Activité 3.c: Essayer la commande GROUP BY avec HAVING en utilisant des requêtes de votre choix.
Voici des exemples :
SELECT * FROM commande
GROUP BY datecom;
HAVING datcom > 1 JANVIER 2010 ;
SELECT * FROM line_comm
GROUP BY idclient;
HAVING SUM(qu) > 100;

13
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

3.4. Clauses ANY, ALL, SOME,

SQL permet d'écrire des conditions où apparaissent des quantificateurs proches de ceux de la logique ("il
existe" (∃), "quelque soit" (∀)), grâce aux mots clefs SOME, ANY et ALL. Les mots clefs SOME et ANY
ont exactement la même signification; ce sont des synonymes. Le format général d'une condition élémentaire
avec quantificateur est le suivant:
SYNTAXE DE BASE
WHERE <attribut> <opérateur de comparaison> <quantificateur> <ensemble>
<quantificateur>::= SOME | ANY | ALL

Le format ci-dessus signifie :

 pour SOME et ANY : " existe-t-il dans l'ensemble au moins un élément e qui satisfait la condition:
e <opérateur de comparaison> <ensemble> ? "
 pour ALL : " tous les éléments de l'ensemble satisfont-ils la condition ? "
Le mot clef IN est équivalent à un quantificateur existentiel (SOME ou ANY) avec l'opérateur de
comparaison d'égalité, donc SOME et ANY sont donc plus puissants. Cependant le mot clef ALL ne permet
pas d'exprimer toutes les requêtes contenant un quantificateur du type "quelque soit". On peut alors écrire
la requête inverse avec le mot clef "NOT EXISTS". Par exemple la requête "chercher les X qui pour tout Y
satisfont telle condition" peut aussi s'exprimer: "chercher les X tels qu'il n'existe aucun Y qui ne satisfait pas
telle condition".
Activité 3.d: Utiliser les qualificateurs SOME, ANY et ALL dans des reauetes SQL. Un exemple est donee
ci-dessous :
SELECT * FROM client
WHERE ville = ANY (‘ALGER’, ‘ORAN’, ‘BATNA’);

Activité 3.e: Se familiariser avec les clauses ensemblistes <ensmeble1>CONATINS <ensemble2>(l’

ensemble1 contient (ou égal a) l’ensemble2) et EXISTS <ensmeble> (cette condition teste si l'ensemble
n'est pas vide (ensemble ≠ Ø).), et essayer de formuler des requêtes SQL sur notre base de données en les
utilisant.
TIPS
Voir ‘Chapter 44 : SQL Useful Functions’ du « SQL Tutorial » pour plus de détails concernant les fonctions

3.5. Les clauses UNION, INTERSECT, EXCEPT

UNION : La clause/opérateur SQL UNION est utilisée pour combiner les résultats de deux ou plusieurs
instructions SELECT sans retourner les lignes en double. Pour utiliser UNION, chaque SELECT doit avoir

14
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

le même nombre de colonnes sélectionnées, le même nombre de colonnes expressions, le même type de
données, et les avoir dans le même ordre.
SYNTAXE DE BASE
SELECT column1 [,column2]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

N.B : La clause UNION ALL permet de retourner tous les lignes même les doubles
INTERSECT : La clause/opérateur SQL INTERSECT est utilisée pour combiner deux instructions
SELECT, mais renvoie des lignes de la première instruction SELECT identique à une ligne de la deuxième
instruction SELECT. Cela signifie qu’INTERSECT renvoie uniquement les lignes communes renvoyées par
les deux instructions SELECT. De même que pour l'opérateur UNION, les mêmes règles s'appliquent
lorsque vous utilisez l'opérateur INTERSECT. MySQL ne supporte pas l'opérateur INTERSECT.
EXCEPT : La clause/opérateur SQL EXCEPT est utilisée pour combiner deux instructions SELECT et
renvoie des lignes de la première instruction SELECT qui ne sont pas renvoyées par la deuxième instruction
SELECT. Cela signifie EXCEPT retourne uniquement les lignes, qui ne sont pas disponibles dans la
deuxième instruction SELECT. De même que pour l'opérateur UNION, les mêmes règles s'appliquent
lorsque vous utilisez l'opérateur EXCEPT. MySQL ne supporte pas l'opérateur EXCEPT.
Activité 3.f: Se familiariser avec les clauses UNION, INTERSECT et EXCEPT et essayer de formuler des
requêtes SQL sur notre base de données en les utilisant.

15
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

Lab 02 : Oracle SQL – un exercice

Après avoir se familiariser avec les commandes SQL, ce TP va mettre ces connaissances au test avec
l’exemple d’une base de données de gestion de fabrication. On vous propose le MCD de l’application, on
vous demande de créer le MLD, puis générer le script de la base de données avant de formuler des requêtes
répondant aux besoins de l’utilisateur.

Objectifs : à l’issue de ce TP, vous seriez en mesure de créer des bases de données à partir d’un MCD
en utilisant des logiciels dédiés, la charger dans un SGBD-R et finalement formuler des requêtes avancées
pour l’interrogation de la base afin de répondre aux besoins exprimées par l’utilisateur. Plus précisément
vous seriez en mesure de :

- Dessiner des MCD et les transformer en modèles logiques automatiquement avec le logiciel
PowerAMC.

- Générer des scripts SQL de bases de données à partir des modèles crées par PowerAMC et les
charger dans un SGBD-R.

- Interroger la base de données pour répondre aux besoins exprimés par l’utilisateur.

Partie 01 : Génération du script SQL de la base de données

La base de données de fabrication des produits est donnée par le MCD suivant :
1. Modèle Conceptuel de Données
USINE PUF PRODUIT
NU <pi> Numérique (20) <O> Quantité Numérique (8) NP <pi> Numérique (20) <O>
NomU Caractère variable (20) 0,n NomP Caractère variable (20)
Ville Caractère variable (20) Couleur Caractère variable (20)
0,n
NU <pi> Poids Caractère variable (20)
NP <pi>
...

0,n

FOURNISSEUR
NF <pi> Numérique (20) <O>
NomF Caractère variable (20)
Statut Caractère variable (20)
Ville Caractère variable (20)
NF <pi>
...

 Une usine est d´écrite par son numéro NU, son nom NomU, la Ville dans laquelle elle est située;

 Un produit est décrit par son numéro NP, son nom NomP, sa Couleur, son Poids;

 Un fournisseur est décrit par son numéro NF, son nom NomF, son Statut (fournisseur sous-traitant,
fournisseur-client, .....), la Ville où il est domicilié;
16
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

 PUF: le produit de numéro NP a été livré à l’usine de numéro NU par le fournisseur de numéro NF
dans une Quantité donnée.
Activité 1.a : Installer le logiciel PowerAMC et dessiner le MCD ci-dessus.
Activité 1.b : Transformer le modèle obtenu en un modèle logique de données, puis vers le modèle physique
en utilisant PowerAMC.
Activité 1.c : Générer le script SQL de la base de données, ouvrir le fichier résultant pour voir les
commandes SQL générées.
2. Remplissage de la base

Voici les données avec lesquels on aimerait peupler notre base de données

Usine (NU, NomU, Ville) Produit (NP, NomP, Couleur, Poids)

(1, 'A', 'Paris') (1, 'iPod', 'rouge', 100)
(2, 'B', 'Paris') (2, 'iPod', 'gris', 100)
(3, 'C', 'Paris') (3, 'iMac', 'blanc', 300)
(4, 'D', 'Londres') (4, 'MacBook', 'blanc', 200)
(5, 'E', 'Londres') (5, 'MacBook', 'noir', 200)

Fournisseur (NF, NomF, Statut, Ville) PUF (NP, NU, NF, qu)
(1, 'A', 'Indépendent', 'Paris') (1, 1, 1, 10)
(2, 'B', 'Grossiste', 'Paris') (1, 1, 2, 15)
(3, 'C', 'Indépendant', 'Londres') (5, 4, 3, 15)
(4, 'D', 'Grossiste', 'Paris') (5, 5, 3, 15)
(5, 'E', 'Indépendant', 'Londres') (5, 1, 3, 15)
(6, 'F', 'Grossiste', 'Bruxelles') (5, 2, 3, 1)
(7, 'G', 'Indépendant', 'Liège') (5, 3, 3, 1)
(8, 'H', 'Grossiste', 'Namur') (2, 1, 4, 1)
(9, 'I', 'Indépendant', 'Charleroi')
(10, 'J', 'Grossiste', 'Namur')

Activité 2.a : Créer un script SQL pour l’insertion de ces données dans notre base de données.
Activité 2.b : Charger le script obtenu dans la base de données à travers SQL workshop de l’utilitaire
Application Express.

Partie 02 : Interrogation de la base de données

Activité 2.a : Exprimer en SQL les requêtes suivantes:
1. Donner le numéro, le nom et la ville de toutes les usines.
2. Donner le numéro, le nom et la ville de toutes les usines de Londres.
3. Donner les numéros des fournisseurs qui approvisionnent l’usine n◦1 en produit n◦1.
4. Donner le nom et la couleur des produits livrés par le fournisseur n◦1.

17
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

5. Donner les numéros des fournisseurs qui approvisionnent l’usine n◦1 en un produit rouge.
6. Donner les noms des fournisseurs qui approvisionnent une usine de Londres ou de Paris en un
produit rouge.
7. Donner les numéros des produits livrés à une usine par un fournisseur de la même ville.
8. Donner les numéros des produits livrés à une usine de Londres par un fournisseur de Londres.
9. Donner les numéros des usines qui ont au moins un fournisseur qui n’est pas de la même ville.
10. Donner les numéros des fournisseurs qui approvisionnent à la fois les usines n◦1 et n◦2.
11. Donner les numéros des usines qui utilisent au moins un produit disponible chez le fournisseur n◦3
(c’est-à-dire un produit qu’il livre mais pas nécessairement a cette usine).
12. Donner le numéro du produit le plus léger (les numéros si plusieurs produits ont ce même poids).
13. Donner les numéros des usines qui ne reçoivent aucun produit rouge d’un fournisseur londonien.
14. Donner les numéros des fournisseurs qui fournissent au moins un produit fourni par au moins un
fournisseur qui fournit au moins un produit rouge.
15. Donner tous les triplets (VilleF, NP, VilleU) tels qu’un fournisseur de la première ville
approvisionne une usine de la deuxième ville avec un produit NP.
16. Même question qu’en 15, mais sans les triplets où les deux villes sont identiques.
17. Donner les numéros des produits qui sont livrés à toutes les usines de Londres.
18. Donner les numéros des fournisseurs qui approvisionnent toutes les usines avec un même produit.
19. Donner les numéros des usines qui achètent au fournisseur n◦4 tous les produits qu’il fournit.
20. Donner les numéros des usines qui s’approvisionnent uniquement chez le fournisseur n◦3.
Activité 2.b : De plus, exprimer en SQL les requêtes et mises à jour suivantes:
21. Ajouter un nouveau fournisseur : h45, Alfred, sous-traitant, Chaloni.
22. Supprimer tous les produits de couleur noire et de numéro compris entre 100 et 199.
23. Changer la ville du fournisseur n◦1 : il a déménagé pour Nice.
24. Changer le statut de tous les fournisseurs de Paris et de Lyon pour “sous-traitant”.
25. Donner le nombre d’usines approvisionnées par le fournisseur n◦1.
26. Pour chaque produit livré à une usine, donner le numéro du produit, celui de l’usine et la quantité
totale qui a été livrée.
27. Donner les numéros des fournisseurs qui fournissent au moins le produit ’P5’ et le produit ’P9’.

18