DROP TABLE PARTICIPATION
/
DROP TABLE PROJET
/
DROP TABLE EMP
/
DROP TABLE DEPT
/
CREATE TABLE DEPT
( DEPT   SMALLINT CONSTRAINT DEPT_PK PRIMARY KEY,
  NOMD   VARCHAR2(12) NOT NULL CONSTRAINT NOMD_U UNIQUE,
  LIEU   VARCHAR2(10)
)
/
CREATE TABLE EMP
( MATR   INTEGER CONSTRAINT EMP_PK PRIMARY KEY,
  NOME   VARCHAR2(10) NOT NULL,
  POSTE  VARCHAR2(10),
  SUP    INTEGER 
    CONSTRAINT EMP_REF_SUP REFERENCES EMP DEFERRABLE INITIALLY DEFERRED,
  DATEMB DATE,
  SAL    DECIMAL(10,2),
  COMM   DECIMAL(10,2),
  DEPT   SMALLINT CONSTRAINT DEPT_REF_EMP REFERENCES DEPT
)
/
DROP TABLE DEPTF1
/
CREATE TABLE DEPTF1
( DEPT   SMALLINT CONSTRAINT DEPTF1_PK PRIMARY KEY,
  NOMD   VARCHAR2(12) CONSTRAINT F1_NOMD_U UNIQUE,
  LIEU   VARCHAR2(10)
)
/
DROP TABLE DEPTF2
/
CREATE TABLE DEPTF2
( DEPT   SMALLINT CONSTRAINT DEPTF2_PK PRIMARY KEY,
  NOMD   VARCHAR2(12) CONSTRAINT F2_NOMD_U UNIQUE,
  LIEU   VARCHAR2(10)
)
/
REM Sinon on ne peut commencer à enregistrer des employes
REM Ne pas mettre cette commande avant car elle n'est valable
REM que pour la transaction courante.
REM Ca ne marche pas ! Bug Oracle ? Je le mets en commentaire
REM et ajoute initially deferred a la definition de la contrainte
REM SET CONSTRAINT EMP_REF_SUP DEFERRED
/
INSERT INTO DEPT VALUES
(10,'FINANCES','PARIS')
/
INSERT INTO DEPT VALUES
(20,'RECHERCHES','GRENOBLE')
/
INSERT INTO DEPT VALUES
(30,'VENTES','LYON')
/
INSERT INTO DEPT VALUES
(40,'FABRICATION','ROUEN')
/
INSERT INTO EMP VALUES
(7369,'LECLERC','SECRETAIRE',7902,'02/12/80',6400.00,NULL,20)
/
INSERT INTO EMP VALUES
(7499,'BIRAUD','COMMERCIAL',7698,'20/2/81',12800.00,2400.00,30)
/
INSERT INTO EMP VALUES
(7521,'BERGER','COMMERCIAL',7698,'22/10/81',10000.00,4000.00,30)
/
INSERT INTO EMP VALUES
(7566,'MERCIER','DIRECTEUR',7839,'02/3/81',23000.00,NULL,20)
/
INSERT INTO EMP VALUES
(7654,'MARTIN','COMMERCIAL',7698,'28/5/81',10000.00,11200.00,30)
/
INSERT INTO EMP VALUES
(7698,'NOIRET','DIRECTEUR',7839,'01/5/81',22800.00,NULL,30)
/
INSERT INTO EMP VALUES
(7782,'LESAGE','DIRECTEUR',7839,'09/6/81',19600.00,NULL,10)
/
INSERT INTO EMP VALUES
(7788,'DUBOIS','INGENIEUR',7566,'14/4/85',24000.00,NULL,20)
/
INSERT INTO EMP VALUES
(7839,'LEROY','PRESIDENT',NULL,'17/11/81',40000.00,NULL,10)
/
INSERT INTO EMP VALUES
(7844,'BENAIN','COMMERCIAL',7698,'08/5/81',12000.00,0.00,30)
/
INSERT INTO EMP VALUES
(7876,'CLEMENT','SECRETAIRE',7788,'10/5/85',8800.00,NULL,20)
/
INSERT INTO EMP VALUES
(7900,'FREMONT','SECRETAIRE',7698,'03/12/81',7600.00,NULL,30)
/
INSERT INTO EMP VALUES
(7902,'CHATEL','INGENIEUR',7566,'17/12/81',24000.00,NULL,20)
/
INSERT INTO EMP VALUES
(7934,'VILLARD','SECRETAIRE',7782,'23/8/82',10400.00,NULL,10)
/
INSERT INTO DEPTF1 VALUES
(10,'FINANCES','PARIS')
/
INSERT INTO DEPTF1 VALUES
(15,'INFORMATIQUE','MARSEILLE')
/
INSERT INTO DEPTF1 VALUES
(30,'VENTES','LYON')
/
INSERT INTO DEPTF2 VALUES
(20,'RECHERCHE','GRENOBLE')
/
INSERT INTO DEPTF2 VALUES
(30,'VENTES','LYON')
/
INSERT INTO DEPTF2 VALUES
(35,'PERSONNEL','TOULOUSE')
/
COMMIT
/