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

Manuel de TP
Conception des bases de données
Partie I

Réalisé par : Dr. Badis Djamaa

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

Lab 01 : Architecture du SGBDR Oracle 11g XE

Dans ce TP, vous vous familiariserez avec l'architecture du système de gestion de base de données Oracle
11g eXpress Edition (XE).

Objectifs: A l’issue de ce TP, vous serez en mesure d'identifier et d'expliquer l'architecture interne du
SGBD, y compris la possibilité de:
 Expliquer les processus qui constituent une instance de base de données
 Identifier comment les processus SGBD utilisent la mémoire
 Identifier et expliquer les composants composant le SGBD Oracle et leur interaction
 Présenter l'architecture des fichiers de base de données Oracle 11g
 Arrêter et démarrer la base de données.

Partie 01 : Introduction à Oracle et Installation d’Oracle 11g XE

1. Bref historique d’Oracle
L’histoire d’oracle a commencé avec la création d’Oracle corporation en 1977 dont la première version a été
conçue à partir des spécifications du système R d’IBM. La première version, commercialisée en 1979, a été
installée dans la société FORD (c’était le premier Système de Gestion des Bases de Données (SGBD)
Relationnel dans le marché). Le modèle relationnel a été introduit par Mr E.CODD en 1977. Le principe du
modèle consiste à présenter aussi bien les entités que les associations à l’aide des relations appelées table.
2. Structure en couche d’Oracle
La figure I.1 présente une architecture en couche d’oracle, où chaque couche est brièvement décrite dans les
points suivants :

Figure I.1 : Structure en couches du système oracle

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

 Noyau : il constitue la première couche de base, il permet la communication à la BD et la

communication avec d’autres noyaux dans le cas des BD réparties.
 Dictionnaire de données : Il décrit d’une façon dynamique la BD; les objets de la base (table, vue,
index,…). Il s’agit d’une méta-base, car il reflète l’image de la base à un instant donné. Il décrit les
utilisateurs accédant à Oracles avec leurs privilèges et les droits qu’ils ont sur les différents objets de
la base…etc.
 SQL : Cette troisième couche de base joue le rôle d’interface entre le noyau et les différents outils
d’ORACLE. Ainsi tout accès à la BD est exprimé en langage SQL.
 PL/SQL : Cette couche constitue une extension procédurale du langage SQL.

3. Installation d’Oracle 11g XE

Oracle 11g XE est une édition libre (open source) du SGBD Oracle qui prend en charge la plupart des
fonctionnalités de l'édition Standard. 11g XE est disponible (au moins) pour les systèmes d’exploitations
Windows et Linux. Comme avec les versions libres de bases de données, XE a également des limites:
 La taille maximale de la base de données est de 11 Go.
 La quantité maximale de RAM XE peut utiliser est de 1 Go.
 Seule une instance d'Oracle XE peut être installée sur un seul ordinateur.
 XE n'utilise qu'une seule CPU pour ne pas répartir les opérations entre plusieurs CPU.
Oracle Database XE est facile à installer. Oracle Database XE fournit une base de données Oracle et des
outils pour gérer la base de données.
TIPS
Avant d’installer Oracle XE, vérifiez que le pare-feu n'a pas été configuré pour bloquer la communication
des ports par défaut des utilisateurs Oracle Database XE :
 1521: Oracle Database Listener
 2030: Oracle Services for Microsoft Transaction Server
 8080: Oracle HTTP Transaction Server.

Activité 1.a : Installer le SGBD Oracle 11g Express Edition.

Lorsque l'installation commence, Oracle vérifie la configuration requise pour l'installation, à savoir:
 Système d'exploitation, au moins Windows XP
 L'utilisateur a des privilèges d'administrateur
 Aucune instance XE existante n'a été trouvée.
L'étape suivante consiste à définir le dossier d'installation. La valeur par défaut est oraclexe.
Après cela, l'installation vous invite à entrer les mots de passe de deux utilisateurs préconfigurés de la base
de données, à savoir SYS et SYSTEM. Ces utilisateurs sont les administrateurs types d'une instance Oracle.

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

TIPS
Vous pouvez changer, par exemple, le mot de passe SYS en un autre mot après l'installation pour
distinguer ces utilisateurs lorsqu'ils se connectent à la base.

Et à la fin, avant l’installation proprement dite, l’écran Résumé s’affiche.

TIPS
Noter les ports par défaut utilisés par votre installation, particulièrement, celui d’ HTTP listener :
- Listener: 1521
- Services for MTS: 2030
- HTTP listener: 8080

Partie 2: Connaître votre installation Oracle

Une fois l’installation terminée, regardons ce qui a été installé. Cette partie vous aide à connaitre votre
installation, en détaillons les programmes, les endroits et les processus en cours d’exécutions associés à
Oracle 11g XE.
1. Les programmes
Activité 2.a : Après avoir installé Oracle 11g, allez au menu de démarrage et remarquez les nouveaux
programmes ajoutés.
En faisons cette activité, vous verrez une figure similaire à celle-ci (Figure I.2)

- Backup Database : sauvegarde des données de la base

- Get Started : ouvre la page d’accueil de la base
- Restore Database : restauration des données de la base
- Rn SQL CL : Ouvre une console SQL * Plus
- Start/Stop DB : Contrôler le service OracleServiceXE
pour démarrer/arrêter la base.

Figure I.2 : les programmes installés

Activité 2.b : Ouvrir le gestionnaire des tâches Windows et afficher les programmes en cours d'exécution et
trier les par taille de mémoire. Trouver les processus oracle.exe, java.exe et TNSLSNR.exe
2. Les services
Activité 2.c : Ouvrez la gestion de l'ordinateur et examinez les services installés, vous découvrirez cinq
nouveaux services comme illustrer dans la figure I.3.

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

Figure I.3 : les services oracle 11g XE

 OracleServiceXE : C’est le moteur de base de données 11g XE.
 OracleXETNSListener : Ce service est chargé d'écouter les connexions entrantes et de transmettre
les connexions réussies au moteur de base de données. Notez que si ce service est en panne, vous ne
pourrez pas vous connecter à la base de données à distance.
 OracleJobSchedulerXE : Ce service est utilisé lors de l'exécution de travaux externes (tels que des
exécutables, des lots, etc.). Par défaut, c'est désactivé.
 OracleXEClrAgent : Sur Windows, Oracle propose une intégration CLR. ClrAgent fournit un
mécanisme multi-thread afin qu'un processus extproc unique puisse servir plusieurs appels CLR.
 OracleMTSRecoveryService : Ce service est responsable de la résolution des transactions douteuses
lorsque Oracle participe à des transactions distribuées avec Microsoft Transaction Server.
3. Les répertoires
Maintenant que l'installation est terminée, il faut noter quelques emplacements (et fichiers). Il convient de
noter que ces emplacements varient en fonction de la façon dont vous avez installé Oracle.
 \oraclexe\app\oracle\oradata\XE : C'est le dossier où se trouvent les fichiers de base de données
après l'installation. Quand de nouveaux fichiers de base de données sont ajoutés, ils peuvent être placés
ailleurs, mais les fichiers critiques tels que le fichier de contrôle, le fichier de base de données système,
le tablespace d'annulation, etc. se trouvent ici.
Activité 2.d : en utilisant l'Explorateur Windows, accéder au répertoire de données et notez les conventions
de dénomination des fichiers.
 \oraclexe\app\oracle\product\11.2.0\server\network\ADMIN : Ce répertoire contient les
fichiers de configuration pour les connexions à la base de données:
 listener.ora : configure le listener. Par défaut, Oracle utilise le port d'écoute 1521. Cela peut être
modifié en modifiant la configuration dans listener.ora et en redémarrant le service d'écoute.
 tnsnames.ora : définit les adresses de base de données pour établir des connexions des programmes
clients tels que SQL*Plus. Par exemple, lorsque vous établissez une connexion au service XE, XE
est en réalité un alias utilisé pour résoudre la configuration réseau réelle à l'aide de tnsnames.ora.

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

Activité 2.e : À l'aide du Bloc-notes, ouvrez votre fichier tnsnames.ora. Notez des noms dans ce fichier qui
vous semblent familiers? Quel est le SERVICE_NAME dans votre fichier tnsnames.ora?. Fermez le fichier
lorsque vous avez terminé. NE PAS CONSERVER DES CHANGEMENTS.
 \oraclexe\app\oracle\diag\rdbms\xe\xe : Ce dossier contient différents types de fichiers de
journal et de trace pour l'instance de base de données.
 \oraclexe\app\oracle\diag\tnslsnr\<machinename>\listener : Ce dossier est similaire au
dossier rdbms correspondant. Toutefois, ce dossier contient des informations de journal et de trace
pour le programme d'écoute.

Partie 3: Environnements de développement Oracle

Oracle Database XE prend en charge les environnements de développement suivants:
1. L’utilitaire SQL*Plus
SQL*Plus est l’interface par défaut des produits Oracle. Il est léger et fiable, même si ce n'est pas aussi
convivial. Vous pouvez émettre des instructions SQL et des commandes SQL*Plus. Les commandes
SQL*Plus sont des commandes spécifiques à Oracle.
Activité 3.a : Aller dans le menu de démarrage – Tous les programmes – Oracle Database 11g Express
Edition, cliquez sur Run SQL Command Line. Puis, se connecter à la base en tapant :

connect;
SYSTEM /* pour vous connecter en utilisant le user-name SYSTEM ; */
le mot de passe que vous avez donné lors de l’installation

TIPS
Pour que votre base de données soit accessible par des clients à distance, tapez la commande :
EXEC DBMS_XDB.SETLISTENETLOCALACCES (FALSE).

Activité 3.b : Maintenant que vous êtes connecté, tapez ?. Quel est le résultat de cette commande.
Tapez maintenant ? INDEX pour avoir la liste des commandes SQL*Plus.
Pour savoir la signification d’une commande SQL*Plus, il suffit de taper ? ou HELP suivie du nom de la
commande souhaitée.
Activité 3.c : Que fait les commandes DESCRIBE, START, @, et SPOOL.
2. L’utilitaire SQL Developer
Oracle SQL Developer est une version graphique de SQL*Plus qui offre aux développeurs un moyen
pratique d’effectuer des tâches de base. Vous pouvez vous connecter à n'importe quel schéma Oracle XE à
l'aide de l'authentification de base de données Oracle standard. Une fois connecté, vous pouvez effectuer
des opérations sur les objets de la base.
6
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

3. L’environnement Oracle Application Express

Oracle Database XE comprend Oracle Application Express (Apex), un outil de développement rapide
d'applications Web pour la base de données Oracle. Apex est activé par défaut dans Oracle Database XE.
Dans ces travaux pratiques, nous utilisant l’environnement Apex, présenté ci-dessous.
4. Environnements Non-Oracle
En plus des environnements de développement offertes par Oracle, la base de données Oracle 11g XE
accepte les environnements de développement suivants :

 Java: Vous pouvez utiliser Oracle JDeveloper, un environnement de développement intégré et gratuit.
Oracle JDeveloper est disponible ici: http://www.oracle.com/technetwork/developer-
tools/jdev/overview/index.html

 .NET et Visual Studio: Vous pouvez utiliser Visual Studio pour développer des applications
Oracle 11g XE en utilisant ODAC (Oracle Data Access Components) pour Windows à partir de:
http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

 PHP : Vous pouvez aussi utiliser PHP pour développer des applications utilisant la base Oracle 11g
XE en utilisant le produit PHP suivant: http://www.php.net

Partie 4: Introduction à l’environnement Oracle Application Express

Cette partie décrit Oracle Application Express et son architecture. Elle vous présente également les concepts
et les termes utilisés dans cet environnement.
1. L'architecture Oracle Application Express
Apex est un outil de développement Web pour les applications BDD. Il s’installe avec Oracle XE.
- Du point de vue du développeur, en utilisant seulement un navigateur Web et une petite expérience
de programmation, il peut développer rapidement des applications complètes et sécurisées.
- Du point de vue de l’utilisateur final, les applications déployées nécessitent uniquement un
navigateur et un accès à une base de données Oracle exécutant Application Express.

Figure I.4 : Oracle Application Express

Le processus d’exécution est le même que vous soyez un développeur ou utilisateur d’une application Apex.
Le navigateur envoie une demande d’URL qui sera traduite par un appel PL/SQL. Une fois que la base de
données a traité PL/SQL, les résultats sont renvoyés à votre navigateur en HTML (Figure I.4).

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

Apex permet de partager l’accès à une base de données entre un groupe de travail en créant des espaces de
travail (Workspace). Un espace de travail constitue la zone dans laquelle on développe des applications. Il
peut se voir comme une base de données virtuelle privée qui permet à plusieurs utilisateurs de travailler dans
la même installation Apex tout en préservant la confidentialité de leurs objets, données et applications. Dans
un tel environnement, vous pouvez créer un seul espace de travail que tous vos développeurs peuvent
partager. On peut également créer des espaces de travail dédiés pour limiter l'accès aux objets d'espace de
travail aux seuls utilisateurs associés à cet espace. L'illustration suivante (Figure I.5) montre la relation entre
les utilisateurs, les développeurs, les espaces de travail et les schémas de base de données.

Figure I.5 : espace de travail Apex.

Lors de la configuration d'utilisateurs Application Express, vous attribuez des rôles et des privilèges à des
utilisateurs spécifiques. Les rôles dans Oracle Application Express sont les suivants:
- Les administrateurs d'espace de travail sont des utilisateurs qui effectuent des tâches d'administrateur
spécifiques à un espace de travail, telles que la gestion des comptes d'utilisateurs, la surveillance de
l'activité de l'espace de travail et l'affichage des fichiers journaux.
- Les développeurs sont des utilisateurs qui créent et modifient des applications et modifient des objets
de base de données.
- Les utilisateurs ne disposent d'aucun privilège de développement. Vous définissez les utilisateurs finaux
pour qu'ils puissent accéder aux applications qui n'utilisent pas de schéma d'authentification externe.
- Les administrateurs d'instance sont des super-utilisateurs qui gèrent une instance hébergée complète à
l'aide de l'application Application Express Administration Services.
L'illustration suivante (Figure I.6) montre plusieurs utilisateurs avec différents rôles accédant à Apex, aux
services d'administration Application Express et aux applications publiées.

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

Figure I.6 : les services oracle 11g XE

Cette illustration montre les développeurs et les administrateurs d'espace de travail accédant à
l'environnement de développement dans Application Express. Toutefois, l'administrateur est le seul rôle
ayant accès à l'application Services d'administration.
Pour nos TPs, vous devez disposer des privilèges généralement associés à trois rôles: Administrateur
d'instance, Administrateur d'espace de travail et Développeur.
2. Créer votre Application Express Workspace
Activité 4.a : Accéder à Oracle Application Express à partir de la page d'accueil de la base de données.
Cliquez sur le bouton Application Express.
Sur la page de connexion:
- Nom d'utilisateur - Spécifiez un auquel le rôle DBA a été attribué (par exemple, SYSTEM).
- Mot de passe - Entrez le mot de passe que vous avez créé lors du processus d'installation.
Activité 4.b : Créer un nouveau workspace pour nos TPs.
Dans la page Create Application Express Workspace, choisir nouveau est fournir les informations spécifique à
votre nouveau espace de travail :
- Database User – Créer un nouveau utilisateur de la base
- Database Username – Donner un nom à votre espace de travail (exemple, TPBDD)
- Application Express Username – Entrer un nom unique d’utilisateur Apex (Exemple, votre nom).
- Password – Entrer le password spécifique à cet utilisateur

TIPS
Une fois un Apex workspace est créer, vous pouvez y accéder directement en tapant son URL dans le
browser. Vous pouvez toujours y accéder via la page d’accueil comme indiqué ci-dessus.
-
9
TP BDD Oracle : SQL et PL/SQL Ecole Militaire Polytechnique

3. Les services d’administration Apex

Vous pouvez accéder aux services d’administration Apex (Apex Administration Services), ou vous pouvez
configurer, créer, gérer et supprimer des espaces de travail, développeurs et utilisateurs, en utilisant
l’administrateur d’instance (user-name : admin) avec le mot de passe introduit lors de l’installation d’oracle.
Pour se faire, allez à la page web : http://hostname:port/apex/apex_admin
Hostname est le nom de votre machine ou loopback adresse, et le port est le numéro de port donné à votre
Oracle http serveur lors de l’installation.
Activité 4.b : accéder aux services d’administration Apex et voir le workspace que vous venez de créer
précédemment dans Manage Workspaces.
Comme vous pouvez le constatez, vous pouvez réaliser plusieurs taches d’administration avec cet outil
comme la gestion des espaces de travail et utilisateurs.
Pour plus de détails concernant Apex vous pouvez vous référer au tutorial suivant:
TIPS
Le tutorial Oracle « Database Express Edition 2 Day + Application Express Developer's Guide »
Disponible ici https://docs.oracle.com/cd/E17781_01/doc.112/e18644/toc.htm présente les étapes et
détails pour développer une application complète avec Apex.

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

Lab 02 : 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, suivi de trois parties concernant la définition des données avec SQL, leurs
manipulations, et enfin la consultation des données sous SQL en utilisant Apex.

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.
4. 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).
5. 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

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

casse, ce qui signifie que SELECT et select ont la même signification dans les instructions SQL. Les
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

6. 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

7. 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 :

12
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

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

13
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;

14
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;

15
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.

16
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}

17
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 (=, >, <, <>, !=, >=, <=, !<,

18
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).

19
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.

20
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.

21
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;

22
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;

23
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

24
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.

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

Lab 03 : 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é;
26
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.

27
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’.

28