DROP TABLE PARTICIPATION;
DROP TABLE PROJET;
DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE DEPT
( DEPT   SMALLINT CONSTRAINT DEPT_PK PRIMARY KEY,
  NOMD   VARCHAR(12) NOT NULL CONSTRAINT NOMD_U UNIQUE,
  LIEU   VARCHAR(10)
);
CREATE TABLE EMP
( MATR   INTEGER CONSTRAINT EMP_PK PRIMARY KEY,
  NOME   VARCHAR(10) NOT NULL,
  POSTE  VARCHAR(10),
  SUP    INTEGER 
    CONSTRAINT EMP_REF_SUP REFERENCES EMP,
  DATEMB DATE,
  SAL    DECIMAL(10,2),
  COMM   DECIMAL(10,2),
  DEPT   SMALLINT CONSTRAINT DEPT_REF_EMP REFERENCES DEPT
);
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
(7839,'LEROY','PRESIDENT',NULL,'1981-11-17',40000.00,NULL,10);
INSERT INTO EMP VALUES
(7698,'NOIRET','DIRECTEUR',7839,'1981-5-1',22800.00,NULL,30);
INSERT INTO EMP VALUES
(7566,'MERCIER','DIRECTEUR',7839,'1981-3-2',23000.00,NULL,20);
INSERT INTO EMP VALUES
(7782,'LESAGE','DIRECTEUR',7839,'1981-6-9',19600.00,NULL,10);
INSERT INTO EMP VALUES
(7902,'CHATEL','INGENIEUR',7566,'1981-12-17',24000.00,NULL,20);
INSERT INTO EMP VALUES
(7369,'LECLERC','SECRETAIRE',7902,'1980-12-2',6400.00,NULL,20);
INSERT INTO EMP VALUES
(7499,'BIRAUD','COMMERCIAL',7698,'1981-2-20',12800.00,2400.00,30);
INSERT INTO EMP VALUES
(7521,'BERGER','COMMERCIAL',7698,'1981-10-22',10000.00,4000.00,30);
INSERT INTO EMP VALUES
(7654,'MARTIN','COMMERCIAL',7698,'1981-5-28',10000.00,11200.00,30);
INSERT INTO EMP VALUES
(7788,'DUBOIS','INGENIEUR',7566,'1985-4-14',24000.00,NULL,20);
INSERT INTO EMP VALUES
(7844,'BENAIN','COMMERCIAL',7698,'1981-5-8',12000.00,0.00,30);
INSERT INTO EMP VALUES
(7876,'CLEMENT','SECRETAIRE',7788,'1985-5-10',8800.00,NULL,20);
INSERT INTO EMP VALUES
(7900,'FREMONT','SECRETAIRE',7698,'1981-12-3',7600.00,NULL,30);
INSERT INTO EMP VALUES
(7934,'VILLARD','SECRETAIRE',7782,'1982-8-23',10400.00,NULL,10);
create table projet(
  codeP varchar(4) constraint pk_projet primary key,
  nomP varchar(15) not null);
create table participation(
  matr integer constraint fk_emp_part references emp, 
  codeP varchar(4) constraint fk_projet_part references projet,
  fonctionP varchar(15),
  constraint pk_part primary key (matr, codeP));
insert into Projet(CodeP,NomP)
values ('P1', 'SPORTS');
insert into participation (matr, codeP) 
values(7566, 'P1');
insert into participation (matr, codeP) 
values(7902, 'P1');