Introduction aux langage SQL

Alexandre Mesl
31 mars 2015

Table des matires

1 Introduction

2 Contraintes dclaratives

3 Introduction aux requtes

1.1 Qu'est-ce qu'un SGBDR? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1.2 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.3 Connexion une base de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.3.1 Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.3.2 mySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.4 Consultation des tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.5 Organisation relationnelle des donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.5.1 Crer des tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.5.2 Ajouter une ligne dans une table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.5.3 Suppression d'une table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1
2.2
2.3
2.4
2.5

Valeurs par dfaut . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Champs non renseigns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Cl primaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Cl trangre . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Syntaxe alternative . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

3.1 Complments sur SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

3.2 Instruction WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3 Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3.1 Comparaison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3.2 Ngation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3.3 Connecteurs logiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3.4 NULLit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3.5 Encadrement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3.6 Inclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3.7 LIKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.4 Suppression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.5 Mise jour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

4 Jointures

4.1
4.2
4.3
4.4

Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Produit cartsien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Jointure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Jointures rexives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1
1
1
1
2
2
3
3
3
3

4
4
4
4
5
6
6
6
6
7
7
7
8
8
8
8
8

10

10
11
11
12

5 Agrgation de donnes

14

6 Vues

18

7 Requtes imbriques

20

A Scripts de cration de tables

28

B Livraisons Sans contraintes

28

C Modules et prerequis

28

D Gomtrie

30

E Livraisons

30

F Arbre gnalogique

32

G Comptes bancaires

32

H Comptes bancaires avec exceptions

34

I Secrtariat pdagogique

36

J Mariages

37

5.1 Fonctions d'agrgation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5.1.1 Exemple introductif . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.1.2 Dnition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.1.3 Exemples d'utilisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.1.4 Complments sur COUNT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.2 Groupage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.2.1 L'instruction GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.2.2 L'instruction HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.1
6.2
6.3
6.4

Dnition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Suppression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

7.1 Sous requtes renvoyant une valeur scalaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

7.1.1 Colonne ctive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.1.2 Conditions complexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.1.3 INSERT et UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.2 Sous requtes renvoyant une colonne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.3 Sous requtes non correles renvoyant une table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.4 Sous requtes correles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.5 Types numriques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.6 Types chaine de caractres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.7 Types date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.8 La fonction inclassable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.9 Contraintes CHECK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

14
14
14
14
15
16
16
16
18
18
18
19

20
20
20
21
21
22
23
25
26
26
27
27

Introduction

1.1 Qu'est-ce qu'un SGBDR ?

Un SGBD (Systme de Gestion de Bases de Donnes) est un logiciel qui stocke des donnes de faon organises
et cohrentes. Un SGBDR (Systme de Gestion de Bases de Donnes Relationnelles) est le type particulier de SGBD
2

qui fera l'objet de ce cours. Il vous sera dcrit plus tard ce qui fait qu'une bases de donnes est relationnelle.
Les bases de donnes les plus rpandues sont :
 Oracle, qui est considr comme un des SGBDR les plus performants.
 Microsoft SQL Server, la mouture de microsoft, qui est intgr au framework .NET.
 mySQL, un logiciel libre fonctionnant souvent de pair avec Apache et Php, et de ce fait trs rpandu dans la
programmation web.
 Access, incorpor Microsoft Oce. Il prsente l'norme avantage de mettre disposition de l'utilisateur une
interface graphique. En contrepartie il est mono-utilisateur et de faible capacit.
Les SGBDRs sont gnralement des serveurs auxquels des clients se connectent, il doivent supporter plusieurs
connections simultanes. Les clients dialoguent alors avec le serveur pour lire ou crire des donns dans la base.
1.2 SQL

Le SQL, Structured Query Language, est un langage Standard permettant un client de communiquer des instructions la base de donnes. Il se dcline en quatre parties :
 le DDL (Data denition language) comporte les instructions qui permettent de dnir la faon dont les donnes
sont reprsentes.
 le DML (Data manipulation language) permet d'crire dans la base et donc de modier les donnes.
 le DQL (Data query language) est la partie la plus complexe du SQL, elle permet de lire les donnes dans la
base l'aide de requtes.
 le DCL (Data control language), qui ne sera pas vu dans ce cours permet de grer les droits d'accs aux donnes.
A cela s'ajoute des extensions procdurales du SQL (appel PL/SQL en Oracle). Celui-ci permet d'crire des scripts
excuts par le serveur de base de donnes.
1.3 Connexion une base de donnes

Dans une base de donnes relationnelle, les donnes sont stockes dans des tables. Une table est un tableau deux
entres. Nous allons nous connecter une base de donnes pour observer les tables.
1.3.1 Oracle

Sous oracle, le client s'appelle SQL+, le compte utilisateur par dfaut a pour login scott et password tiger.
La liste des tables s'ache en utilisant l'instruction

sql> SELECT TABLE_NAME FROM USER_TABLES

1.3.2 mySQL

La mthode la plus simple pour s'initier mysql est d'utiliser un kit de easyphp, wamp, etc. Vous disposez dans
ce cas d'une option vous permettant d'ouvrir une console mysql.
La liste des bases de donnes stockes dans le serveur s'obtient avec l'instruction
sql> show databases

Database
|
++
| information_schema |
| arbreGenealogique
|
| banque
|
| geometrie
|
| livraisons
|
| playlist
|
| repertoire
|
| secretariat
|
| test
|
++
|

On se connecte l'une des bases de donnes avec l'instruction

sql> use nomdelabase

1.4 Consultation des tables

Une fois dans la base, on obtient la liste des tables avec l'instruction

sql> show tables

Tables_in_livraisons |
++
| DETAILLIVRAISON
|
| FOURNISSEUR
|
| LIVRAISON
|
| PRODUIT
|
| PROPOSER
|
++
|

On ache la liste des colonnes d'une table avec l'instruction

sql> desc PRODUIT

++++++
Field
| Type
| Null | Key | Default | Extra |
+++++++
| numprod | int ( 1 1 )
| NO
| PRI | 0
|
|
| nomprod | varchar ( 3 0 ) | YES
|
| NULL
|
|
+++++++
+
|

Le contenu d'une table s'ache avec l'instruction

sql> SELECT
sql> FROM PRODUIT

++
numprod | nomprod
|
+++
|
1 | Roue de secours |
|
2 | Poupee Batman
|
|
3 | Cotons tiges
|
|
4 | Cornichons
|
+++
+
|

1.5 Organisation relationnelle des donnes

Nous utiliserons pour commencer les types suivants :

 numrique entier : int
 numrique point xe : number (Oracle seulement)
 numrique point ottant : real
 chane de caractres : varchar taille ou varchar2 taille (Oracle seulement).
(

1.5.1 Crer des tables

Voici un exemple de cration de table :

4

sql> CREATE TABLE CLIENT (

sql>
numcli int ,
sql>
nomcli varchar ( 3 2 ) ) ;
sql> desc CLIENT ;

1.5.2 Ajouter une ligne dans une table

Voici un exemple d'insertion de donnes dans une table :

sql> INSERT INTO CLIENT ( numcli , nomcli )

sql>
VALUES ( 1 , ' Marcel ' ) , ( 2 , 'Gg ' ) ;
sql> SELECT FROM CLIENT ;

Attention, chaque commande SQL se termine par un point-virgule!

1.5.3 Suppression d'une table

Une table se supprime avec l'instruction DROP TABLE.

sql> DROP TABLE CLIENT ;

Contraintes dclaratives

2.1 Valeurs par dfaut

sql> create table client
sql>
(
sql>
numcli int ,
sql>
nom varchar ( 2 5 6 ) default 'Moi ' ,
sql>
prenom varchar ( 2 5 6 )
sql>
)

fait de 'Moi' le nom par dfaut.

2.2 Champs non renseigns

sql> create table client
sql>
(
sql>
numcli int ,
sql>
nom varchar ( 2 5 6 ) NOT NULL,
sql>
prenom varchar ( 2 5 6 ) NOT NULL
sql>
)

force la saisie des champs nom et prnom.

2.3 Cl primaire

Une cl primaire est :

 toujours renseigne
 unique
On peut prciser PRIMARY KEY dans la cration de table

sql> create table client

sql>
(
sql>
numcli int PRIMARY KEY,
sql>
nom varchar ( 2 5 6 ) ,
sql>
prenom varchar ( 2 5 6 )
sql>
)

La colonne numcli est cl primaire, toute insertion ne respectant pas la contrainte de cl primaire sera refuse par
le SGBD.
2.4 Cl trangre

Dans le cas o l'on souhaite garder en mmoire des factures mises par des clients, la faon de faire est de crer
une deuxime table contenant la liste des factures :
sql> create table facture
sql>
(
sql>
numfact int PRIMARY KEY,
sql>
montantFacture int
sql>
numcli int REFERENCES CLIENT ( numCli )
sql>
);

Le champ numCli dans cette table est cl trangre, ce qui signie qu'une ligne ne pourra tre insre dans la table
facture que si le numcli de cette ligne existe dans la colonne numcli de la table client.
La syntaxe est
REFERENCES

<nomtable > (< nomcolonne >)

2.5 Syntaxe alternative

Il est possible de dnir les contraintes aprs la cration d'une table.

sql> ALTER TABLE nomtable

sql> ADD [CONSTRAINT nomcontrainte ] descriptioncontrainte ;

descriptioncontrainte d'une cl primaire :

PRIMARY KEY( colonne1 ,

... ,

colonnen )

FOREIGN KEY( colonne1 ,

... ,

colonnen )
( colonne1 ,

descriptioncontrainte d'une cl trangre :

REFERENCES tablereferencee

... ,

colonnen )

Il est aussi possible de placer une descriptioncontrainte dans le CREATE TABLE. Par exemple,

sql> create table facture

sql>
(
sql>
numfact int ,
sql>
montantFacture int
sql>
numcli int ,
sql>
PRIMARY KEY ( numfact ) ,
sql>
FOREIGN KEY nucli REFERENCES CLIENT ( numcli )
sql>
);

On remarque qu'il est possible de nommer une contrainte. C'est utile si on souhaite la supprimer :

sql> ALTER TABLE nomtable DROP CONSTRAINT nomcontrainte ;

Pour lister les contraintes sous Oracle, on utilise la commande :

sql> SELECT FROM USER_CONSTRAINTS ;

Sous mySQL :

sql> SHOW TABLE STATUS ;

Introduction aux requtes

3.1 Complments sur SELECT

Il est possible d'utiliser SELECT pour n'acher que certaines colonnes d'une table. Syntaxe :

SELECT <colonne_1 >,

FROM <table >

<colonne_2 >,

. . . , <colonne_n >

Cette instruction s'appelle une requte, elle achera pour chaque ligne de la table les valeurs des colonnes colonne
colonne . Il est possible de supprimer les lignes en double l'aide du mot-cl DISTINCT. Par exemple :

SELECT DISTINCT
FROM <table >

<colonne_1 >, <colonne_2 >,

. . . , <colonne_n >

Pour trier les donnes, on utilise ORDER BY. Exemple :

SELECT <colonne_1 >, <colonne_2 >, . . . , <colonne_n >

FROM <table >
ORDER BY <colonne_1bis >, <colonne_2bis >, . . . , <colonne_nbis >

Cette instruction trie les donnes par colonne croissants. En cas d'galit, le tri est fait par colonne croissants,
etc. Pour trier par ordre dcroissant, on ajoute DESC aprs le nom de la colonne choisie comme critre dcroissant. Par
exemple :
1bis

2bis

SELECT <colonne_1 >, <colonne_2 >, . . . , <colonne_n >

FROM <table >
ORDER BY <colonne_1bis > DESC, <colonne_2bis >, . . . ,

<colonne_nbis >

3.2 Instruction WHERE

Cette instruction permet de ne slectionner que certaines lignes de la table. Par exemple la requte va acher le
nom du produit numro 1 :
sql> SELECT nomprod
sql> FROM produit
sql> WHERE numprod =

La syntaxe gnrale est

SELECT <colonne_1 >,

FROM <table >
WHERE <condition >

<colonne_2 >,

. . . , <colonne_n >

condition sera value pour chaque ligne de la table, et seules celles qui veront cette condition feront partie du
rsultat de la requte.

3.3 Conditions
3.3.1 Comparaison

Les conditions peuvent tre des relations d'galit (=), de dirence (<>), d'ingalit (<, >, >= ou <=) sur des
colonnes :
numero_client = 2
nom_client = ' Marcel '
prenom_client <> ' Ginette '
salary < 230
taxes >= 2 3 0 0 0

3.3.2 Ngation

La ngation d'une condition s'obtient l'aide de NOT. Par exemple, il est possible de r-ecrire les conditions ci-avant :

NOT
NOT
NOT
NOT
NOT

( numero_client <> 2 )
( nom_client <>

' Marcel ' )

' Ginette ' )

( prenom_client =
( salary >= 2 3 0 )

( taxes < 2 3 0 0 0 )

3.3.3 Connecteurs logiques

De mme, vous avez votre disposition tous les connecteurs logiques binaires : AND, OR. Ainsi, les deux conditions
suivantes sont les mmes :

NOT( ( nom
( nom <>

= ' Raymond ' ) AND ( prenom <> ' Huguette ' ) )

' Raymond ' ) OR ( prenom = ' Huguette ' )

3.3.4 NULLit

Un champ non renseign a la valeur NULL, dans une comparaison, NULL n'est jamais gal quelque valeur qu'il
soit! La condition suivante est toujours fausse :

NULL

NULL;

La requte suivante ne renvoie aucune ligne :

sql> INSERT INTO MP3 ( numMp3 ) VALUES

sql> SELECT
sql> FROM MP3
sql> WHERE nomMp3 = NULL;

(3) ;

Pour tester la nullit d'un champ, on utilise IS NULL, par exemple :

sql> SELECT
sql> FROM MP3
sql> WHERE nomMp3 IS NULL;

++
numMp3 | nomMp3 |
+++
+
|
|

NULL

++

La non-nullit se teste de deux faons :

WHERE NOT ( nomMp3 IS NULL) ;

ou encore

sql> SELECT
sql> FROM MP3
sql> WHERE nomMp3 IS NOT NULL;

++
numMp3 | nomMp3
|
+++
|
1 | Get Lucky
|
|
2 | Locked Down |
+++
+
|

3.3.5 Encadrement

Une valeur numrique peut tre encadre l'aide de l'oprateur BETWEEN, par exemple les deux conditions suivantes
sont quivalentes :
SALAIRE BETWEEN 1 0 0 0 AND 5 0 0 0
( SALAIRE >= 1 0 0 0 ) AND ( SALAIRE

<= 5 0 0 0 )

3.3.6 Inclusion

L'oprateur IN permet de tester l'appartenance une liste de valeurs. Les deux propositions suivantes sont quivalentes
NAME IN ( 'Gg ' , ' Ginette ' , ' Marcel ' )
( NAME = 'Gg ' ) OR ( NAME = ' Ginette ' ) OR

( NAME =

' Marcel ' )

3.3.7 LIKE
LIKE

sert comparer le contenu d'une variable un littral gnrique. Par exemple, la condition

NAME LIKE 'M% '

sera vrie si NAME commence par un 'M'. Ca fonctionne aussi sur les valeurs de type numrique, la condition
SALARY LIKE ' %000000000 '

sera vrie si SALARY se termine par 000000000. Le caractre % peut remplacer dans le littral n'importe que suite,
vide ou non, de caractres; il a le mme rle que * en DOS et en SHELL. Le caractre _ remplace un et un seul
caractre dans le littral. Par exemple, la condition
NAME LIKE 'K_r % '

ne sera vrie que si NAME commence par un 'K' et contient un 'r' en troisime position.
3.4 Suppression

L'expression

DELETE FROM <NOMTABLE >

WHERE <CONDITION >

eace de la table NOMTABLE toutes les lignes vriant condition. Attention! La commande

DELETE FROM <NOMTABLE >

eace toutes les lignes de la table NOMTABLE !

3.5 Mise jour

L'expression

UPDATE <NOMTABLE >

SET <colonne_1 > =

<valeur_1 >,

<colonne_2 > = <valeur_2 >,

... ,

<colonne_n > = <valeur_n >

WHERE <CONDITION >

modie les lignes de la table NOMTABLE vriant condition. Elle aecte au champ colonne la valeur valeur . Par
exemple,
i

10

sql> UPDATE CLIENT

sql> SET prenomcli = 'Dark '
sql> WHERE nomcli = ' Vador '

aecte la valeur 'Dark' aux champs prenomcli de toutes les lignes dont la valeur nomcli est gale 'Vador'. Il
est possible, dans une modication, d'utiliser les valeurs des autres champs de la ligne, voire mme l'ancienne valeur
de ce champ. Par exemple,
sql> UPDATE OPERATION
sql> SET montantoper = montantoper

+ 5000

augmente les montants de toutes les oprations bancaires de 5000 (choisissez l'unit!).

11

Jointures

4.1 Principe

Nous utiliserons pour ce cours les donnes de C.

Si on souhaite connatre les numros des modules prerequis pour s'inscrire dans le module 'PL/SQL Oracle', il nous
faut tout d'abord le numro de ce module :
sql> SELECT numMod
sql> FROM MODULE
sql> WHERE nomMod = 'PL / SQL Oracle '

numMod |
++
|
|

Ensuite, cherchons les numros des modules prrequis pour s'inscrire dans le module numro 6,

sql> SELECT numModPrereq

sql> FROM PREREQUIS
sql> WHERE numMod = 6 ;

numModPrereq |
++
|
|

|
|

Et pour nir, allons rcuprer les noms de ces modules,

sql> SELECT nomMod
sql> FROM MODULE
sql> WHERE numMod IN

(1 ,

5) ;

nomMod |
++
| Oracle |
| Merise |
++
|

Vous tes probablement tous en train de vous demander s'il n'existe pas une mthode plus simple et plus rapide,
et surtout une faon d'automatiser ce que nous venons de faire. Il existe un moyen de slectionner des donnes dans
plusieurs tables simultanment. Pour traiter la question ci-dessus il susait de saisir :
sql> SELECT m2 . nomMod
sql> FROM MODULE m1 , MODULE m2 , PREREQUIS p
sql> WHERE m1 . numMod = p . numMod
sql> AND m2 . numMod = p . numModprereq
sql> AND m1 . nomMod = 'PL / SQL Oracle ' ;

nomMod |
++
| Oracle |
| Merise |
++
|

Le but de ce chapitre est d'expliciter ce type de commande.

12

4.2 Produit cartsien

L'instruction SELECT ... FROM ... peut s'tendre de la faon suivante :

sql> SELECT <listecolonnes >

sql> FROM <listetables >

L'exemple ci-dessous vous montre le rsultat d'une telle commande.

sql> SELECT
sql> FROM PROPOSER , PRODUIT ;

+++++
numfou | numprod | prix | numprod | nomprod
|
++++++
|
1 |
1 |
200 |
1 | Roue de secours |
|
1 |
2 |
15 |
1 | Roue de secours |
|
2 |
2 |
1 |
1 | Roue de secours |
|
3 |
3 |
2 |
1 | Roue de secours |
|
1 |
1 |
200 |
2 | Poupee Batman
|
|
1 |
2 |
15 |
2 | Poupee Batman
|
|
2 |
2 |
1 |
2 | Poupee Batman
|
|
3 |
3 |
2 |
2 | Poupee Batman
|
|
1 |
1 |
200 |
3 | Cotons tiges
|
|
1 |
2 |
15 |
3 | Cotons tiges
|
|
2 |
2 |
1 |
3 | Cotons tiges
|
|
3 |
3 |
2 |
3 | Cotons tiges
|
|
1 |
1 |
200 |
4 | Cornichons
|
|
1 |
2 |
15 |
4 | Cornichons
|
|
2 |
2 |
1 |
4 | Cornichons
|
|
3 |
3 |
2 |
4 | Cornichons
|
++++++
+
|

Placer une liste de tables dans le FROM revient former toutes les combinaisons de lignes possibles. Cependant, cela
a relativement peu de sens.
4.3 Jointure

Il serait plus intressant, dans le cas prsent, de ne voir s'acher que des lignes dont les numros de produits
concordent. Pour ce faire, il sut d'utiliser WHERE. Par exemple,
sql> SELECT
sql> FROM PROPOSER , PRODUIT
sql> WHERE PROPOSER . numprod

PRODUIT . numprod ;

+++++
numfou | numprod | prix | numprod | nomprod
|
++++++
|
1 |
1 |
200 |
1 | Roue de secours |
|
1 |
2 |
15 |
2 | Poupee Batman
|
|
2 |
2 |
1 |
2 | Poupee Batman
|
|
3 |
3 |
2 |
3 | Cotons tiges
|
++++++
+
|

Nous avons mis en correspondance des lignes de la table proposer avec des lignes de la table produit en utilisant le
fait que numprod est une cl trangre dans proposer. Comme la colonne numprod apparait deux fois dans la requte,
il est ncessaire de la prxer par le nom de la table de sorte que chaque colonne puisse tre dsigne de faon non
ambigu. Si on veut mettre face face les noms des produits et les noms des fournisseurs, il sut de saisir la requte
sql> SELECT nomfou , nomprod
sql> FROM PRODUIT , FOURNISSEUR

PROPOSER

13

sql> WHERE PRODUIT . numProd = PROPOSER . numProd

sql> AND FOURNISSEUR . numFou = PROPOSER . numFou ;

++
nomfou | nomprod
|
+++
| f1
| Roue de secours |
| f1
| Poupee Batman
|
| f2
| Poupee Batman
|
| f3
| Cotons tiges
|
+++
+
|

4.4 Jointures rexives

En utilisant la syntaxe suivante, il est possible de rennomer les tables,

sql> FROM <table_1 >

<table_1_renommee >,

Reformulons la requte ci-dessus,

. . . , <table_n > <table_n_renommee >

sql> SELECT nomfou , nomprod

sql> FROM PRODUIT p , FOURNISSEUR f , PROPOSER pr
sql> WHERE p . numProd = pr . numProd
sql> AND f . numFou = pr . numFou ;

++
nomfou | nomprod
|
+++
| f1
| Roue de secours |
| f1
| Poupee Batman
|
| f2
| Poupee Batman
|
| f3
| Cotons tiges
|
+++
+
|

Le renommage permet entre autres de faire des jointures rexives, c'est dire entre une table et elle mme. Par
exemple, en reprenant la table intervalle,
sql> SELECT FROM INTERVALLE ;

++
borneInf | borneSup |
+++
+
|
|

30

56

10

32

27

12

12

30

21

34

26

++

La commande ci-dessous ache tous les couples d'intervalles ayant une borne en commun,

sql> SELECT FROM INTERVALLE i , INTERVALLE j

sql> WHERE ( i . borneInf = j . borneInf AND i . borneSup < j . borneSup )
sql> OR ( i . borneInf < j . borneInf AND i . borneSup = j . borneSup ) ;

14

++++
borneInf | borneSup | borneInf | borneSup |
+++++
+
|
|

56

12

30

12

30

12

12

30

++++

15

Agrgation de donnes

5.1 Fonctions d'agrgation

5.1.1 Exemple introductif

Nous voulons connatre le nombre de lignes de table produit. Deux faons de procder :
1. Solution moche
sql> SELECT FROM PRODUIT ;

++
numprod | nomprod
|
+++
|
1 | Roue de secours |
|
2 | Poupee Batman
|
|
3 | Cotons tiges
|
|
4 | Cornichons
|
+++
+
|

On a la rponse avec le nombre de lignes slectionnes.

2. Solution belle

sql> SELECT count ( ) FROM PRODUIT ;

+
|

count ( )

+
|

La rponse est le rsultat de la requte.

5.1.2 Dnition

Une fonction d'agrgation retourne une valeur calcule sur toutes les lignes de la requte (nombre, moyenne...).
Nous allons utiliser les suivantes :
 COUNT(col) : retourne le nombre de lignes dont le champ col est non NULL.
 AVG(col) : retourne la moyenne des valeurs col sur toutes les lignes dont le champ col est non NULL.
 MAX(col) : retourne la plus grande des valeurs col sur toutes les lignes dont le champ col est non NULL.
 MIN(col) : retourne la plus petite des valeurs col sur toutes les lignes dont le champ col est non NULL.
 SUM(col) : retourne la somme des valeurs col sur toutes les lignes dont le champ col est non NULL.
5.1.3 Exemples d'utilisation

L'exemple suivant retourne le prix du produit propos au prix maximal.

sql> SELECT MAX( prix )

sql> FROM PROPOSER ;

+
|

MAX( prix )

+
|

200

Il est possible de renommer la colonne MAX(prix), en utilisant le mot cl AS :

16

sql> SELECT MAX( prix ) AS PRIX_MAXIMAL

sql> FROM PROPOSER ;

PRIX_MAXIMAL |
++
|
|

200

Les requtes suivantes rcuprent le nom du fournisseur proposant l'article 'Poupe Batman' au prix le moins lev :

sql> SELECT MIN( prix ) AS PRIX_MINIMUM

sql> FROM PROPOSER PR , PRODUIT P
sql> WHERE PR . numprod = P . numprod
sql> AND nomprod = ' Poupee Batman ' ;

PRIX_MINIMUM |
++
|
|

sql> SELECT nomfou

sql> FROM FOURNISSEUR F , PROPOSER PR , PRODUIT P
sql> WHERE F . numfou = PR . numfou
sql> AND PR . numprod = P . numprod
sql> AND nomprod = ' Poupee Batman '
sql> AND prix = 1 ;

nomfou |
++
| f2
|
++
|

Il est possible de faire cela avec une seule requte en rcuprant le prix minimum dans une requte imbrique. Mais
cela sera pour un cours ultrieur.
5.1.4 Complments sur COUNT

On rcupre le nombre de ligne retournes par une requte en utilisant COUNT(*). Par exemple, si on souhaite
connatre le nombre de produits proposs par le fournisseur 'f1' :
sql> SELECT COUNT( ) AS NB_PROD
sql> FROM FOURNISSEUR F , PROPOSER P
sql> WHERE F . numfou = P . numfou
sql> AND nomfou = 'f1 ' ;

NB_PROD |
++
|
|

On aurait aussi pu saisir :

sql> SELECT COUNT( numprod ) AS NB_PROD

sql> FROM FOURNISSEUR F , PROPOSER P
sql> WHERE F . numfou = P . numfou
sql> AND nomfou = 'f1 ' ;

17

NB_PROD |
++
|
|

Pour connatre le nombre de produits proposs, c'est dire dont le numprod a une occurence dans la table PROPOSER,
on procde de la faon suivante :
sql> SELECT COUNT(DISTINCT numprod ) AS NB_PRODUITS_PROPOSES
sql> FROM PROPOSER ;

NB_PRODUITS_PROPOSES |
++
|
|

Le DISTINCT nous sert viter qu'un mme produit propos par des fournisseurs dirents soit comptabilis
plusieurs fois.
5.2 Groupage
5.2.1 L'instruction GROUP BY

Les oprations d'agrgation considres jusqu' maintenant portent sur la totalit des lignes retournes par les
requtes, l'instruction GROUP BY permet de former des paquets l'intrieur desquels les donnes seront agrges. Cette
instruction s'utilise de la manire suivante
sql> SELECT . . .
sql> FROM . . .
sql> WHERE. . .
sql> GROUP BY <liste_colonnes >
sql> ORDER BY . . .

La liste des colonnes sert de critre pour rpartir les lignes dans des paquets de lignes. Si par exemple nous
souhaitons acher la liste des nombres de produits proposs par chaque fournisseur :
sql> SELECT nomfou , COUNT( numprod ) AS NB_PRODUITS_PROPOSES
sql> FROM FOURNISSEUR F , PROPOSER P
sql> WHERE F . numfou = P . numfou
sql> GROUP BY nomfou ;

++
nomfou | NB_PRODUITS_PROPOSES |
+++
| f1
|
2 |
| f2
|
1 |
| f3
|
1 |
+++
+
|

5.2.2 L'instruction HAVING

Supposons que de la requte prcdente, nous ne souhaitions garder que les lignes pour lesquelles la valeur
est gale 1. Ajouter une condition dans WHERE serait inutile, le ltrage occasionn par
est eectu avant l'agrgation. Il nous faudrait une instruction pour n'inclure que des groupes de donnes
rpondant certains critres. L'instruction utilise pour ce faire est HAVING. Son utilisation est la suivante :

NB_PRODUITS_PROPOSES
WHERE

18

sql> SELECT . . .
sql> FROM . . .
sql> WHERE . . .
sql> GROUP BY . . .
sql> HAVING <condition >
sql> ORDER BY . . .

Par exemple,

sql> SELECT nomfou , COUNT( numprod ) AS NB_PRODUITS_PROPOSES

sql> FROM FOURNISSEUR F , PROPOSER P
sql> WHERE F . numfou = P . numfou
sql> GROUP BY nomfou
sql> HAVING COUNT( numprod ) = 1
sql> ORDER BY nomfou DESC;

++
nomfou | NB_PRODUITS_PROPOSES |
+++
| f3
|
1 |
| f2
|
1 |
+++
+
|

Achons les noms des fournisseurs qui ont livr strictement plus d'un produit dirent (toutes livraisons confondues),
sql> SELECT nomfou
sql> FROM FOURNISSEUR F , DETAILLIVRAISON D
sql> WHERE F . numfou = D . numfou
sql> GROUP BY nomfou
sql> HAVING count (DISTINCT D . numprod ) > 1 ;

nomfou |
++
| f1
|
++
|

19

Vues

6.1 Dnition

Une vue est une table contenant des donnes calcules sur celle d'une autre table. Les donnes d'une vue sont tout
le temps jour. Si vous modiez les donnes d'une des tables sur lesquelles est calcule la vue, alors les modications
sont automatiquement rpercutes sur la vue.
6.2 Syntaxe

Apprciez la simplicit de la syntaxe :

CREATE VIEW <nom_vue > AS

<requete >

6.3 Application

Par exemple, la requte suivante met en correpondance les noms des produits avec le nombre de fournisseurs qui
le proposent :
SQL> SELECT nomprod , COUNT( numfou ) AS NB_FOURNISSEURS
2
FROM PRODUIT P
3
LEFT OUTER JOIN PROPOSER PR
4
ON P . numprod = PR . numprod
5
GROUP BY nomprod
6
ORDER BY COUNT( numfou ) ;
NOMPROD
NB_FOURNISSEURS

Cornichons
0
Cotons tiges
1
Roue de secours
1
Poup e Batman
2
4

ligne ( s ) selectionn e ( s ) .

Ce type de requte sera explit dans un cours ultrieur. Pour le moment, notez juste que les outils dont vous
disposez pour le moment ne vous permettront pas de formuler une requte achant les noms des produits n'ayant
aucun fournisseur. Crons une vue pour ne pas avoir se farcir la requte chaque fois que nous aurons besoin de ces
informations :
SQL> CREATE VIEW NB_FOURNISSEURS_PAR_PRODUIT AS
2
SELECT nomprod , COUNT( numfou ) AS NB_FOURNISSEURS
3
FROM PRODUIT P
4
LEFT OUTER JOIN PROPOSER PR
5
ON P . numprod = PR . numprod
6
GROUP BY nomprod
7
ORDER BY COUNT( numfou ) ;
Vue cr e .

Une fois cre, on peut interroger une vue de la mme faon qu'on interroge une table :

SQL> SELECT
2 FROM NB_FOURNISSEURS_PAR_PRODUIT ;
NOMPROD
NB_FOURNISSEURS

Cornichons
0
Cotons tiges
1
Roue de secours
1

20

Poup e Batman
4

ligne ( s ) selectionn e ( s ) .

Notez que toute modication dans la table PROPOSER ou PRODUIT sera immdiatement rpercute sur la vue.

SQL> INSERT INTO PROPOSER VALUES

1

(3 ,

4,

9) ;

ligne creee .

SQL> SELECT
2 FROM NB_FOURNISSEURS_PAR_PRODUIT ;
NOMPROD
NB_FOURNISSEURS

Cornichons
1
Cotons tiges
1
Roue de secours
1
Poup e Batman
2
4

ligne ( s ) selectionn e ( s ) .

Maintenant, nous souhaitons voir s'acher, pour tout i, le nombre de produits proposs par exactement i fournisseurs.
SQL> SET head off
SQL> SELECT 'Il y a ' | | COUNT( NOMPROD ) | | ' produit (s) qui est / sont '
2
' propose (s) par ' | | NB_FOURNISSEURS | | ' fournisseur (s). '
3 FROM NB_FOURNISSEURS_PAR_PRODUIT
4 GROUP BY NB_FOURNISSEURS
5 ORDER BY NB_FOURNISSEURS ;
Il y a
Il y a
2

3
1

produit ( s ) qui est / sont propos ( s ) par

produit ( s ) qui est / sont propos ( s ) par

ligne ( s ) selectionn e ( s ) .

SQL> SET head on

6.4 Suppression

On supprime une vue avec l'instruction suivante :

DROP VIEW <nom_vue >;

21

1
2

fournisseur ( s ) .
fournisseur ( s ) .

||

Requtes imbriques

Oracle permet d'imbriquer les requtes, c'est--dire de placer des requtes dans les requtes. Une requte imbrique
peut renvoyer trois types de rsultats :
 une valeur scalaire
 une colonne
 une table
7.1 Sous requtes renvoyant une valeur scalaire

Le rsultat d'une requte est dit scalaire s'il comporte une seule ligne et une seule colonne. Par exemple :

SQL> SELECT COUNT( ) FROM PERSONNE ;

COUNT( )

21

On peut placer dans une requte une sous-requte calculant un rsultat scalaire. Un tel type de sous-requte se place
soit comme une colonne supplmentaire, soit comme une valeur servant valuer des conditions (WHERE ou HAVING).
7.1.1 Colonne ctive

On peut ajouter une colonne dans une requte, et choisir comme valeurs pour cette colonne le rsultat d'une
requte. Ce type de requte est souvent une alternative GROUP BY. Par exemple, la requte suivante nous renvoie,
pour tout produit, le nombre de fournisseurs proposant ce produit :
SQL> SELECT nomprod , (SELECT COUNT( )
2
FROM PROPOSER PR
3
WHERE PR . numprod = P . numprod )
4
AS NB_FOURNISSEURS
5 FROM PRODUIT P ;
NOMPROD
NB_FOURNISSEURS

Roue de secours
1
Poupee Batman
2
Cotons tiges
1
Cornichons
0

7.1.2 Conditions complexes

On peut construire une condition en utilisant le rsultat d'une requte. Pour notre exemple, dclarons d'abord une
vue contenant le nombre d'articles proposs par chaque fournisseur,
SQL> CREATE VIEW NB_PROD_PAR_FOU AS
2 SELECT numfou , (SELECT COUNT( )
3
FROM PROPOSER P
4
WHERE P . numfou = F . numfou )
5
AS NB_PROD
6 FROM FOURNISSEUR F ;
Vue creee .

Ensuite, recherchons les noms des fournisseurs proposant le plus de produits :

SQL> SELECT nomfou

2 FROM FOURNISSEUR F , NB_PROD_PAR_FOU N
3 WHERE F . numfou = N . numfou

22

4
5

AND NB_PROD = (SELECT MAX( NB_PROD )

FROM NB_PROD_PAR_FOU ) ;

NOMFOU

f1

La requte SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU est value avant, et son rsultat lui est substitu dans
l'expression de la requte. Comme on a
SQL> SELECT MAX( NB_PROD ) FROM NB_PROD_PAR_FOU ;

MAX( NB_PROD )

Alors la requte prcdente, dans ce contexte, est quivalente

SQL> SELECT nomfou
2 FROM FOURNISSEUR F , NB_PROD_PAR_FOU N
3 WHERE F . numfou = N . numfou
4 AND NB_PROD = 2 ;
NOMFOU

f1

7.1.3 INSERT et UPDATE

On peut placer dans des instructions de mises jour ou d'insertions des requtes imbriques. Par exemple,

SQL> INSERT INTO PERSONNE

2
3

VALUES

( numpers ,

nom , prenom )
FROM PERSONNE )

( (SELECT MAX( numpers ) + 1

' Darth ' , ' Vador ' ) ;

7.2 Sous requtes renvoyant une colonne

On considre une colonne comme une liste de valeurs, on peut tester l'appartance d'un lment cette liste l'aide
de l'oprateur IN. On peut s'en servir comme une alternative aux jointures, par exemple, rcrivons la requte de la
section prcdente. La requte suivante nous renvoie le nombre de produits proposs par les fournisseurs proposant le
plus de produits :
SQL> SELECT MAX( NB_PROD ) FROM NB_PROD_PAR_FOU ;

MAX( NB_PROD )

Maintenant, recherchons les numros des fournisseurs proposant un tel nombre de produits :
SQL> SELECT N . numfou
2 FROM NB_PROD_PAR_FOU N
3 WHERE NB_PROD = (SELECT MAX( NB_PROD )
4
FROM NB_PROD_PAR_FOU ) ;
NUMFOU

23

Notons que s'il existe plusieurs fournisseurs proposant 2 produits, cette requte renverra plusieurs lignes. C'est
donc par hasard qu'elle ne retourne qu'une ligne. Le numro du fournisseur proposant le plus de produits est donc le
1. Cherchons ce fournisseur :
SQL> SELECT nomfou
2 FROM FOURNISSEUR F
3 WHERE F . numfou IN ( 1 ) ;
NOMFOU

f1

Il sut donc dans la requte ci-dessous de remplacer le 1 par la requte qui a retourn 1. On a nalement :

SQL> SELECT nomfou

2 FROM FOURNISSEUR F
3 WHERE F . numfou IN (SELECT N . numfou
4
FROM NB_PROD_PAR_FOU N
5
WHERE NB_PROD = (SELECT MAX( NB_PROD )
6
FROM NB_PROD_PAR_FOU ) ) ;
NOMFOU

f1

7.3 Sous requtes non correles renvoyant une table

On peut remplacer le nom d'une table dans la clause FROM par une sous-requte. Par exemple, la requte suivante
renvoie une table.
SQL> SELECT
2
3
4
5
6

(SELECT

COUNT( )
FROM PROPOSER PR
WHERE PR . numfou = F . numfou
) AS NB_PROD
FROM FOURNISSEUR F ;

NB_PROD

2
1
1
0

Cette table contient, pour chaque fournisseur, le nombre de produits proposs. Si l'on souhaite connatre le plus
grand nombre de produits proposs, on se sert du rsultat de la requte ci-dessus comme d'une table :
SQL> SELECT MAX( NB_PROD ) AS MAX_NB_PROD
2
3
4
5
6
7
8
9

FROM
(SELECT
(SELECT COUNT( )
FROM PROPOSER PR
WHERE PR . numfou = F . numfou
) AS NB_PROD
FROM FOURNISSEUR F
);

MAX_NB_PROD

24

Ce type de requte est une alternative aux vues. Rcuprons maintenant les noms des fournisseurs proposant le
plus de produits (sans jointure et sans vue!) :
SQL> SELECT nomfou
2 FROM FOURNISSEUR
3 WHERE numfou IN
4
(SELECT numfou
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

FROM
(SELECT numfou ,
(SELECT COUNT( )
FROM PROPOSER PR
WHERE PR . numfou = F . numfou
) AS NB_PROD
FROM FOURNISSEUR F
N

WHERE NB_PROD =
(SELECT MAX( NB_PROD )
FROM
(SELECT numfou ,
(SELECT COUNT( )
FROM PROPOSER PR
WHERE PR . numfou = F . numfou
) AS NB_PROD
FROM FOURNISSEUR F

22

23
24

)
);

NOMFOU

f1

Vous constatez que la solution utilisant les vues est nettement plus simple.

7.4 Sous requtes correles

Une sous-requte peut tre de deux types :

 simple : Elle value avant la requte principale
 correle : Elle est value pour chaque ligne de la requte principale
Par exemple, la requte suivante renvoie le nombre de produits livrs pour chaque fournisseur. Elle contient une
sous-requte correle.
SQL> SELECT numfou ,
2
3
4
5
6

(SELECT

SUM( qte )
FROM DETAILLIVRAISON D
WHERE D . numfou = F . numfou
)

NB_PROD_L

FROM FOURNISSEUR F ;

NUMFOU NB_PROD_L

1

45

2
3

10

Cette mme requte, une fois value, peut server de requte non correle si on souhaite connatre les noms de ces
fournisseurs :
SQL> SELECT nomfou , NB_PROD_L

25

2
3
4
5
6

FROM FOURNISSEUR F ,
(SELECT numfou ,
(SELECT SUM( qte )
FROM DETAILLIVRAISON D
WHERE D . numfou = F . numfou

7
9
10

NB_PROD_L

FROM FOURNISSEUR F

WHERE F . numfou

L . numfou ;

NOMFOU
NB_PROD_L

f1
45
f2
f3
10
f4

Amusons-nous : quel sont, pour chaque fournisseur, les produits qui ont t les plus livrs?

SQL> SELECT nomfou , nomprod

2 FROM FOURNISSEUR F , PRODUIT P ,
3
(SELECT FF . numfou , PP . numprod
4
FROM FOURNISSEUR FF , PRODUIT PP

WHERE
(SELECT SUM( qte )
FROM DETAILLIVRAISON L
WHERE L . numfou = FF . numfou
AND L . numprod = PP . numprod

5
6
7
8
9
10

11

(SELECT MAX( NB_PROD_L )

12

FROM
(SELECT numfou , SUM( qte ) AS NB_PROD_L
FROM DETAILLIVRAISON L
GROUP BY numprod , numfou

13
14
15
16
17

19
20
21
22
23

WHERE Q . numfou

18

FF . numfou

GROUP BY numfou , numprod

)

WHERE M . numprod = P . numprod

AND M . numfou = F . numfou ;

NOMFOU

f1
f3

NOMPROD

Roue de secours
Cotons tiges

Dans la requte prcdente, quelles sous-requtes sont correles et lesquelles ne le sont pas?

26

7.5 Types numriques

NUMBER(p, s)

virgule.

dnit un type numrique de au plus (p s) chires avant la virgule et au plus s chires aprs la

SQL> CREATE TABLE TOTO

2
( tutu number ( 4 , 2 )
3

);

Table creee .
SQL> INSERT INTO TOTO VALUES( 1 0 . 2 )
1

ligne creee .

SQL> INSERT INTO TOTO VALUES( 1 0 ) ;

1

ligne creee .

SQL> INSERT INTO TOTO VALUES( . 0 1 )

1

ligne creee .

SQL> INSERT INTO TOTO VALUES( 2 1 . 0 1 ) ;

1

ligne creee .

SQL> INSERT INTO TOTO VALUES( 2 1 . 0 )

1

ligne creee .

SQL> INSERT INTO TOTO VALUES( 2 1 . 0 1 2 ) ;

1

ligne creee .

SQL> INSERT INTO TOTO VALUES( 3 2 1 . 0 ) ;

INSERT INTO TOTO VALUES( 3 2 1 . 0 )

ERREUR a la ligne 1 :
ORA 0 1 4 3 8 : valeur incoherente avec la precision indiquee pour cette colonne

SQL> INSERT INTO TOTO VALUES( 3 2 1 ) ;

INSERT INTO TOTO VALUES( 3 2 1 )

ERREUR a la ligne 1 :
ORA 0 1 4 3 8 : valeur incoherente avec la precision indiquee pour cette colonne

SQL> SELECT
2 FROM TOTO ;
TUTU

10 ,2
10
,01
21 ,01
21
21 ,01

27

ligne ( s ) selectionnee ( s ) .

7.6 Types chaine de caractres

Une petite liste de proprits et de fonctions qui peuvent servir :

 Pour concatner deux chanes de caractres, on utilise l'oprateur ||
 Il est aussi possible de comparer deux chaines de caractres avec >, l'ordre considr est l'ordre "dictionnaire"
(ou lexicographique).
 La longueur s'obtient avec la fonction LENGTH.
 On extrait une sous-chaine de caractres de longueur l partir de l'indice i (les indices commencent 1) de la
chane s avec la fonction SUBSTR(S, i, l). Par exemple, SUBSTR('oracle', 3, 2) = 'ac'
 UPPER convertit en majuscules, LOWER convertit en minuscules.

7.7 Types date

Une date en SQL est considr comme un point dans le temps. On le convertit en chaine de caratres avec la
fonction to_char(date, format), o format est une chane de caractres optionnelle. Par exemple,
SQL> SELECT to_char ( dateli ) AS DT
2 FROM LIVRAISON ;
DT

30/10/06
30/10/06

SQL> SELECT to_char ( dateli , 'yyyy ' ) AS ANNEE

2 FROM LIVRAISON ;
ANNE

2006
2006

SQL> SELECT to_char ( dateli , 'yyyy / mm / dd ' ) AS DT

2 FROM LIVRAISON ;
DT

2006/10/30
2006/10/30

SQL> SELECT to_char ( dateli , ' yyyymmdd ' ) AS DT

2 FROM LIVRAISON ;
DT

20061030
20061030

On convertit une chaine de caractres en date avec la fonction to_date(date, format). Par exemple :

SQL> UPDATE LIVRAISON

2 SET dateli = to_date ( '1934 ' | | to_char ( dateli , 'mmdd ' )
2

ligne ( s ) mise ( s ) a jour .

SQL> SELECT
2 FROM LIVRAISON ;

28

' yyyymmdd ' ) ;

NUMFOU
NUMLI DATELI

1

1 30/10/34

1 30/10/34

SQL> UPDATE LIVRAISON

2 SET dateli = to_date ( '2006 ' | | to_char ( dateli , 'mmdd ' )
2

' yyyymmdd ' ) ;

ligne ( s ) mise ( s ) a jour .

SQL> SELECT FROM LIVRAISON ;

NUMFOU
NUMLI DATELI

1

1 30/10/06

1 30/10/06

7.8 La fonction inclassable

nvl(valeur1, valeur2)

renvoie valeur1 si valeur1 est non NULL, valeur2 sinon. Par exemple,

SQL> DELETE FROM TOTO ;

6

ligne ( s ) supprimee ( s ) .

SQL> SELECT SUM( tutu )

2 FROM TOTO ;

SUM( TUTU )

SQL> SELECT nvl (SUM( tutu )

2 FROM TOTO ;

0)

NVL (SUM( TUTU ) , 0 )

7.9 Contraintes CHECK

La contrainte dclarative de type permet de tester une condition portant les lignes de la table prises une par une.
La syntaxe est :
ALTER TABLE nomtable ADD CONSTRAINT nomcontrainte CHECK( condition ) ;

Par exemple,

SQL> ALTER TABLE EMP ADD CONSTRAINT ck_salary CHECK( SAL

> 0) ;

Table modifiee .
SQL> INSERT INTO EMP ( EMPNO , SAL ) VALUES ( 1 5 , 1) ;
INSERT INTO EMP ( EMPNO , SAL ) VALUES ( 1 5 , 1)

ERREUR a la ligne 1 :
ORA 0 2 2 9 0 : violation de contraintes

( SCOTT .

CK_SALARY ) de verification

Une contrainte de type CHECK ne peut pas contenir de requtes ni de valeurs non constantes (sysdate par exemple).
29

Scripts de cration de tables

Livraisons Sans contraintes

Attention : Le numro de livraison est une cl secondaire, c'est--dire un numro unique tant donn un fournisseur.

drop
drop
drop
drop
drop

table
table
table
table
table

PRODUIT ;
FOURNISSEUR ;
PROPOSER ;
LIVRAISON ;
DETAILLIVRAISON ;

CREATE TABLE PRODUIT

( numprod integer ,
nomprod varchar ( 3 0 ) ) ;
CREATE TABLE FOURNISSEUR
( numfou integer ,
nomfou varchar ( 3 0 ) ) ;
CREATE TABLE PROPOSER
( numfou integer ,
numprod integer ,
prix real ) ;

CREATE TABLE LIVRAISON

( numfou integer ,
numli integer ,
dateli date
);

CREATE TABLE DETAILLIVRAISON

( numfou integer ,
numli integer ,
numprod integer ,
qte integer ) ;

Modules et prerequis

les modules sont rpertoris dans une table, et les modules pr-requis pour s'y inscrire (avec la note minimale) se
trouvent dans la table prerequis. Une ligne de la table PREREQUIS nous indique que pour s'inscrire dans le module
numro numMod, il faut avoir eu au moins noteMin au module numModPrereq.
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

IF
IF
IF
IF
IF
IF

EXISTS
EXISTS
EXISTS
EXISTS
EXISTS
EXISTS

RESULTAT ;
EXAMEN ;
PREREQUIS ;
INSCRIPTION ;
MODULE ;
ETUDIANT ;

CREATE TABLE ETUDIANT

( numEtud int PRIMARY KEY,
nom varchar ( 4 0 ) ,
prenom varchar ( 4 0 ) ,
datenaiss date ,
civilite varchar ( 4 ) ,
patronyme varchar ( 4 0 ) ,
numsecu varchar ( 1 5 ) NOT NULL

30

);

CREATE TABLE MODULE

( numMod int PRIMARY KEY,
nomMod varchar ( 1 5 ) ,
effecMax int DEFAULT 30
);

CREATE TABLE EXAMEN

( numMod int REFERENCES MODULE ( numMod ) ,
numExam int ,
dateExam date ,
PRIMARY KEY( numMod , numExam )
);

CREATE TABLE INSCRIPTION

( numEtud int REFERENCES ETUDIANT ( numEtud ) ,
numMod int REFERENCES MODULE ( numMod ) ,
dateInsc date ,
PRIMARY KEY( numEtud , numMod )
);

CREATE TABLE PREREQUIS

( numMod int REFERENCES MODULE ( numMod ) ,
numModPrereq int REFERENCES MODULE ( numMod ) ,
noteMin int NOT NULL DEFAULT 1 0 ,
PRIMARY KEY( numMod , numModPrereq )
);

CREATE TABLE RESULTAT

( numMod int ,
numExam int ,
numEtud int ,
note int ,
PRIMARY KEY( numMod , numExam , numEtud ) ,
FOREIGN KEY ( numMod , numExam ) REFERENCES EXAMEN ( numMod , numExam ) ,
FOREIGN KEY ( numEtud , numMod ) REFERENCES INSCRIPTION ( numEtud , numMod )
);

INSERT INTO MODULE

(1 ,
(2 ,
(3 ,
(4 ,
(5 ,
(6 ,
(7 ,
(8 ,

( numMod ,

' Oracle ' ) ,

'C ++ ' ) ,
'C ' ) ,
'Algo ' ) ,
' Merise ' ) ,
'PL / SQL Oracle ' )
' mySQL ' ) ,
'Algo avancee ' ) ;

INSERT INTO PREREQUIS

(1 ,

5) ,

(2 ,

3) ,

(6 ,

5) ,

(8 ,

5) ,

(7 ,

5) ;

nomMod ) VALUES

( numMod ,

INSERT INTO PREREQUIS VALUES

numModPrereq ) VALUES

(6 ,

1,

12) ;

31

Gomtrie

La table INTERVALLE contient des intervalles spcis par leurs bornes infrieure et suprieure. Supprimer de la
table intervalle tous les intervalles qui n'en sont pas avec une seule instruction.
drop table if exists RECTANGLE ;
drop table if exists INTERVALLE ;
CREATE TABLE INTERVALLE
( borneInf int ,
borneSup int ,
PRIMARY KEY ( borneInf , borneSup ) ) ;
CREATE TABLE RECTANGLE
( xHautGauche int ,
yHautGauche int ,
xBasDroit int ,
yBasDroit int ,
PRIMARY KEY ( xHautGauche , yHautGauche , xBasDroit , yBasDroit ) ) ;
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO

INTERVALLE
INTERVALLE
INTERVALLE
INTERVALLE
INTERVALLE
INTERVALLE
INTERVALLE
INTERVALLE
INTERVALLE
INTERVALLE

INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO

RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE
RECTANGLE

Livraisons

drop
drop
drop
drop
drop

table
table
table
table
table

if
if
if
if
if

exists
exists
exists
exists
exists

VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES

VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES

(2 ,

56) ;

(12 ,
(2 ,

30) ;
3) ;

(12 ,
(8 ,

3) ;
27) ;

(34 ,

26) ;

(5 ,

10) ;

(7 ,

32) ;

(0 ,

30) ;

(21 ,

8) ;

(2 ,

12 ,

5,

7) ;

(2 ,

12 ,

1,

13) ;

(10 ,

13 ,

1,

(10 ,

13 ,

10 ,

(2 ,
(21 ,

7,

5,

73 ,

11) ;
11) ;

13) ;
15 ,

(1 ,

2,

3,

4) ;

(1 ,

5,

3,

2) ;

(1 ,

6,

3,

6) ;

(4 ,

2,

1,

4) ;

(2 ,

3,

4,

0) ;

(5 ,

4,

2,

1) ;

22) ;

DETAILLIVRAISON ;
LIVRAISON ;
PROPOSER ;
FOURNISSEUR ;
PRODUIT ;

CREATE TABLE PRODUIT

( numprod int ,
nomprod varchar ( 3 0 ) ) ;
CREATE TABLE FOURNISSEUR
( numfou int ,

32

nomfou varchar ( 3 0 ) ) ;

CREATE TABLE PROPOSER

( numfou int ,
numprod int ,
prix int NOT NULL) ;
CREATE TABLE LIVRAISON
( numfou int ,
numli int ,
dateli date
);

CREATE TABLE DETAILLIVRAISON

( numfou int ,
numli int ,
numprod int ,
qte int NOT NULL) ;
alter table
PRIMARY KEY
alter table
PRIMARY KEY
alter table
PRIMARY KEY
alter table
PRIMARY KEY
alter table
PRIMARY KEY
alter table
FOREIGN KEY
alter table
FOREIGN KEY
alter table
FOREIGN KEY
alter table
FOREIGN KEY
alter table
FOREIGN KEY

PRODUIT add constraint pk_produit

( numprod ) ;
FOURNISSEUR add constraint pk_fournisseur
( numfou ) ;
PROPOSER add constraint pk_proposer
( numfou , numprod ) ;
LIVRAISON add constraint pk_livraison
( numfou , numli ) ;
DETAILLIVRAISON add constraint pk_detail_livraison
( numfou , numli , numprod ) ;
PROPOSER add constraint fk_proposer_fournisseur
( numfou ) REFERENCES FOURNISSEUR ( numfou ) ;
PROPOSER add constraint fk_proposer_produit
( numprod ) REFERENCES PRODUIT ( numprod ) ;
LIVRAISON add constraint fk_livraison
( numfou ) REFERENCES FOURNISSEUR ( numfou ) ;
DETAILLIVRAISON add constraint fk_detail_livraison
( numfou , numli ) REFERENCES LIVRAISON ( numfou , numli ) ;
DETAILLIVRAISON add constraint fk_detail_livraison_proposer
( numfou , numprod ) REFERENCES PROPOSER ( numfou , numprod ) ;

INSERT INTO PRODUIT values

(2 ,
(3 ,
(4 ,

' Poupee Batman ' )

' Cotons tiges ' ) ,
' Cornichons ' ) ;

(1 ,

INSERT INTO FOURNISSEUR values

(2 ,
(3 ,
(4 ,

'f2 ' ) ,
'f3 ' ) ,
'f4 ' ) ;

INSERT INTO PROPOSER values

(1 ,

2,

15) ,

(2 ,

2,

1) ,

(3 ,

3,

2) ;

INSERT INTO LIVRAISON values

(1 ,

2,

(3 ,

1,

now ( ) ) ,
now ( ) ) ;

'Roue de secours ' )

(1 ,

'f1 ' )

1,

200) ,

(1 ,

(1 ,

1,

now ( ) )

33

INSERT INTO DETAILLIVRAISON values

(1 ,

1,

1,

25) ,

(1 ,

1,

2,

20) ,

(1 ,

2,

1,

15) ,

(1 ,

2,

2,

17) ;

(3 ,

1,

3,

10) ,

Arbre gnalogique

La table PERSONNE, le champ pere contient le numro du pre de la personne, le champ mere contient le numro
de la mre de la personne.

DROP TABLE IF EXISTS personne ;

CREATE TABLE personne
( numpers int PRIMARY KEY,
nom varchar ( 3 0 ) ,
prenom varchar ( 3 0 ) ,
pere int ,
mere int ,
FOREIGN KEY ( pere ) REFERENCES personne ( numpers ) ,
FOREIGN KEY ( mere ) REFERENCES personne ( numpers )
);

insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert

into
into
into
into
into
into
into
into
into
into
into
into
into
into
into
into

personne
personne
personne
personne
personne
personne
personne
personne
personne
personne
personne
personne
personne
personne
personne
personne

values
values
values
values
values
values
values
values
values
values
values
values
values
values
values
values

' Estermont ' , ' Cassana ' , NULL, NULL) ;

' Baratheon ' , ' Steffon ' , NULL, NULL) ;
( 3 , ' Baratheon ' , ' Renly ' , 2 , 1 ) ;
( 4 , ' Baratheon ' , ' Stannis ' , 2 , 1 ) ;
( 5 , ' Baratheon ' , ' Robert ' , 2 , 1 ) ;
( 1 2 , ' Lannister ' , ' Joanna ' , NULL, NULL) ;
( 1 3 , ' Lannister ' , ' Tywin ' , NULL, NULL) ;
( 9 , ' Lannister ' , ' Cersei ' , 1 3 , 1 2 ) ;
( 6 , NULL, ' Gendry ' , 5 , NULL) ;
( 8 , ' Baratheon ' , ' Tommen ' , 5 , 9 ) ;
( 7 , ' Baratheon ' , ' Joffrey ' , 5 , 9 ) ;
( 1 0 , ' Baratheon ' , ' Myrcella ' , 5 , 9 ) ;
( 1 1 , ' Lannister ' , ' Jaime ' , 1 3 , 1 2 ) ;
( 1 4 , ' Lannister ' , ' Tyrion ' , 1 3 , 1 2 ) ;
( 1 5 , ' Florent ' , ' Selyse ' , NULL, NULL) ;
( 1 6 , ' Baratheon ' , ' Shireen ' , 4 , 1 5 ) ;
(1 ,
(2 ,

Comptes bancaires

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

IF
IF
IF
IF
IF
IF

EXISTS
EXISTS
EXISTS
EXISTS
EXISTS
EXISTS

OPERATION ;
TYPEOPERATION ;
COMPTECLIENT ;
TYPECCL ;
PERSONNEL ;
CLIENT ;

CREATE TABLE CLIENT

( numcli int primary key auto_increment ,
nomcli varchar ( 3 0 ) ,
prenomcli varchar ( 3 0 ) ,
adresse varchar ( 6 0 ) ,
tel varchar ( 1 0 ) ,
CONSTRAINT ck_telephone CHECK( LENGTH ( tel ) =10)

34

);

CREATE TABLE PERSONNEL

( numpers int primary key auto_increment ,
nompers varchar ( 3 0 ) ,
prenompers varchar ( 3 0 ) ,
manager int ,
salaire int ,
CONSTRAINT ck_salaire CHECK( SALAIRE >= 1 2 5 4 . 2 8 )
);

CREATE TABLE TYPECCL

( numtypeccl int primary key auto_increment ,
nomtypeccl varchar ( 3 0 )
);

CREATE TABLE COMPTECLIENT

( numcli int ,
numccl int ,
numtypeccl int ,
dateccl date not null ,
numpers int ,
CONSTRAINT pk_compteclient
PRIMARY KEY ( numcli , numccl ) ,
CONSTRAINT fk_ccl_typeccl
FOREIGN KEY ( numtypeccl )
REFERENCES TYPECCL

( numtypeccl ) ,

CONSTRAINT fk_ccl_client
FOREIGN KEY ( numcli )
REFERENCES CLIENT

( numcli ) ,

CONSTRAINT fk_ccl_personnel
FOREIGN KEY ( numpers )
REFERENCES PERSONNEL

( numpers )

);

CREATE TABLE TYPEOPERATION

( numtypeoper int primary key auto_increment ,
nomtypeoper varchar ( 3 0 )
);

CREATE TABLE OPERATION

( numcli int ,
numccl int ,
numoper int ,
numtypeoper int ,
dateoper date not null ,
montantoper int not null ,
libeloper varchar ( 3 0 ) ,
CONSTRAINT pk_operation
PRIMARY KEY ( numcli , numccl , numoper ) ,
CONSTRAINT fk_oper_ccl
FOREIGN KEY ( numcli , numoper )
REFERENCES COMPTECLIENT

( numcli ,

CONSTRAINT fk_oper_codeoper
FOREIGN KEY ( numtypeoper )
REFERENCES TYPEOPERATION

CONSTRAINT montant_operation
CHECK( montantoper

numccl )

( numtypeoper ) ,

<> 0 )

);

35

INSERT INTO TYPECCL VALUES

(2 ,
(3 ,

' livret ' )

'PEL ' ) ;

(1

' Compte courant ' )

INSERT INTO TYPEOPERATION VALUES

(2 ,
(3 ,
(4 ,

'pr l vement ' )

' virement ' ) ,
' retrait ' ) ;

(1 ,

'dpt esp ces ' )

Comptes bancaires avec exceptions

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

IF
IF
IF
IF
IF
IF

EXISTS
EXISTS
EXISTS
EXISTS
EXISTS
EXISTS

OPERATION ;
COMPTECLIENT ;
TYPECCL ;
TYPEOPERATION ;
PERSONNEL ;
CLIENT ;

CREATE TABLE CLIENT

( numcli int ,
nomcli varchar ( 3 0 ) ,
prenomcli varchar ( 3 0 ) ,
adresse varchar ( 6 0 ) ,
tel varchar ( 1 0 )
);

CREATE TABLE PERSONNEL

( numpers int ,
nompers varchar ( 3 0 ) ,
prenompers varchar ( 3 0 ) ,
manager int ,
salaire int
);

CREATE TABLE TYPECCL

( numtypeccl int ,
nomtypeccl varchar ( 3 0 )
);

CREATE TABLE COMPTECLIENT

( numcli int ,
numccl int ,
numtypeccl int ,
dateccl date default sysdate not null ,
numpers int
);

CREATE TABLE TYPEOPERATION

( numtypeoper int ,
nomtypeoper varchar ( 3 0 )
);

CREATE TABLE OPERATION

( numcli int ,
numccl int ,
numoper int ,

36

numtypeoper int ,
dateoper date ,
montantoper int not null
libeloper varchar ( 3 0 )

);

ALTER TABLE CLIENT ADD

(

CONSTRAINT pk_client PRIMARY KEY ( numcli ) ,

CONSTRAINT ck_telephone CHECK( LENGTH ( tel ) =10)
);

ALTER TABLE PERSONNEL ADD

(

CONSTRAINT pk_personnel PRIMARY KEY ( numpers ) ,

CONSTRAINT ck_salaire CHECK( SALAIRE >= 1 2 5 4 . 2 8 )
);

ALTER TABLE TYPECCL ADD

CONSTRAINT pk_typeccl PRIMARY KEY ( numtypeccl ) ;
ALTER TABLE TYPEOPERATION ADD
CONSTRAINT pk_typeoperation PRIMARY KEY ( numtypeoper ) ;
ALTER TABLE COMPTECLIENT ADD
(

CONSTRAINT pk_compteclient
PRIMARY KEY ( numcli , numccl ) ,
CONSTRAINT fk_ccl_typeccl
FOREIGN KEY ( numtypeccl )
REFERENCES TYPECCL

( numtypeccl ) ,

CONSTRAINT fk_ccl_client
FOREIGN KEY ( numcli )
REFERENCES CLIENT

( numcli ) ,

CONSTRAINT fk_ccl_personnel
FOREIGN KEY ( numpers )
REFERENCES PERSONNEL

( numpers )

);

ALTER TABLE OPERATION ADD

(

CONSTRAINT pk_operation
PRIMARY KEY ( numcli , numccl , numoper ) ,
CONSTRAINT fk_oper_ccl
FOREIGN KEY ( numcli , numoper )
REFERENCES COMPTECLIENT

( numcli ,

CONSTRAINT fk_oper_codeoper
FOREIGN KEY ( numtypeoper )
REFERENCES typeoperation

CONSTRAINT montant_operation
CHECK( montantoper <>

( numtypeoper ) ,

AND montantoper

);

INSERT INTO TYPECCL VALUES (

(SELECT nvl (MAX( numtypeccl ) ,
FROM TYPECCL

numccl )

0) + 1

) ,

' Compte courant ' ) ;

37

>=

1000 AND montantoper

<= 1 0 0 0 )

INSERT INTO TYPECCL VALUES (

(SELECT nvl (MAX( numtypeccl ) ,
FROM TYPECCL

0) + 1

) ,

' livret ' ) ;

INSERT INTO TYPECCL VALUES (

(SELECT nvl (MAX( numtypeccl ) ,
FROM TYPECCL
'PEL ' ) ;

0) + 1

) ,

INSERT INTO TYPEOPERATION VALUES (

(SELECT nvl (MAX( numtypeoper ) ,
FROM TYPEOPERATION

0) + 1

) ,

'dpt esp ces ' ) ;

INSERT INTO TYPEOPERATION VALUES (

(SELECT nvl (MAX( numtypeoper ) ,
FROM TYPEOPERATION

0) + 1

) ,

'pr l vement ' ) ;

INSERT INTO TYPEOPERATION VALUES (

(SELECT nvl (MAX( numtypeoper ) ,
FROM TYPEOPERATION

0) + 1

) ,

' virement ' ) ;

INSERT INTO TYPEOPERATION VALUES (

(SELECT nvl (MAX( numtypeoper ) ,
FROM TYPEOPERATION

0) + 1

) ,

' retrait ' ) ;

Secrtariat pdagogique

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

IF
IF
IF
IF
IF
IF

EXISTS
EXISTS
EXISTS
EXISTS
EXISTS
EXISTS

RESULTAT ;
EXAMEN ;
PREREQUIS ;
INSCRIPTION ;
MODULE ;
ETUDIANT ;

CREATE TABLE ETUDIANT

( numEtud int PRIMARY KEY,
nom varchar ( 4 0 ) ,
prenom varchar ( 4 0 ) ,
datenaiss date ,
civilite varchar ( 4 ) ,
patronyme varchar ( 4 0 ) ,
numsecu varchar ( 1 5 ) NOT NULL
);

CREATE TABLE MODULE

( numMod int PRIMARY KEY,

38

nomMod varchar ( 1 5 ) ,
effecMax int DEFAULT

30

);

CREATE TABLE EXAMEN

( numMod int REFERENCES MODULE ( numMod ) ,
numExam int ,
dateExam date ,
PRIMARY KEY( numMod , numExam )
);

CREATE TABLE INSCRIPTION

( numEtud int REFERENCES ETUDIANT ( numEtud ) ,
numMod int REFERENCES MODULE ( numMod ) ,
dateInsc date ,
PRIMARY KEY( numEtud , numMod )
);

CREATE TABLE PREREQUIS

( numMod int REFERENCES MODULE ( numMod ) ,
numModPrereq int REFERENCES MODULE ( numMod ) ,
noteMin int NOT NULL DEFAULT 1 0 ,
PRIMARY KEY( numMod , numModPrereq )
);

CREATE TABLE RESULTAT

( numMod int ,
numExam int ,
numEtud int ,
note int ,
PRIMARY KEY( numMod , numExam , numEtud ) ,
FOREIGN KEY ( numMod , numExam ) REFERENCES EXAMEN ( numMod , numExam ) ,
FOREIGN KEY ( numEtud , numMod ) REFERENCES INSCRIPTION ( numEtud , numMod )
);

INSERT INTO MODULE

(1 ,
(2 ,
(3 ,
(4 ,
(5 ,
(6 ,
(7 ,
(8 ,

( numMod ,

' Oracle ' ) ,

'C ++ ' ) ,
'C ' ) ,
'Algo ' ) ,
' Merise ' ) ,
'PL / SQL Oracle ' )
' mySQL ' ) ,
'Algo avancee ' ) ;

INSERT INTO PREREQUIS

(1 ,

5) ,

(2 ,

3) ,

(6 ,

5) ,

(8 ,

5) ,

(7 ,

5) ;

nomMod ) VALUES

( numMod ,

INSERT INTO PREREQUIS VALUES

numModPrereq ) VALUES

(6 ,

1,

12) ;

Mariages

CREATE TABLE PERSONNE

( numpers number PRIMARY KEY,

39

nom varchar ( 3 0 ) NOT NULL,

prenom varchar ( 3 0 ) ,
pere REFERENCES PERSONNE ( numpers )
mere REFERENCES PERSONNE ( numpers )

);

CREATE TABLE MARIAGE

(

nummari NUMBER REFERENCES PERSONNE ( numpers ) ,

numfemme NUMBER REFERENCES PERSONNE ( numpers )
datemariage DATE DEFAULT SYSDATE ,
datedivorce DATE DEFAULT NULL,
PRIMARY KEY( nummari , numfemme , dateMariage )

);

40