-- -----------------------------------------------------------------------------------------------
-- Creation des tables OAI
-- -----------------------------------------------------------------------------------------------

-- Pour supprimer les tables, on doit respecter l'ordre des contraintes
-- La tables enfant (employe)  doit etre supprimee avant la table parente (departement)

DROP TABLE employe;
DROP TABLE departement;
DROP TABLE grille_salaire;

-- Les conraintes sont mises en place lors de la creation des tables
-- ==> la table departement doit etre creee en premier

CREATE TABLE departement (
 id_departement     NUMBER(2),
 nom                VARCHAR2(15),
 lieu               VARCHAR2(15),
 CONSTRAINT departement_id_departement_PK PRIMARY KEY (id_departement)
);

CREATE TABLE employe (
 id_employe         NUMBER(4),
 nom                VARCHAR2(20)    NOT NULL,
 poste              VARCHAR2(20),
 id_Manager         NUMBER(4) 	    CONSTRAINT employe_id_Manager_FK REFERENCES employe (id_employe),
 date_embauche      DATE            DEFAULT SYSDATE,
 salaire            NUMBER(7,2),
 commission         NUMBER(7,2),
 id_departement     NUMBER(2),
 CONSTRAINT employe_id_employe_PK PRIMARY KEY (id_employe)
);

CREATE TABLE grille_salaire (
 grade              NUMBER(2),
 salaire_min        NUMBER(7,2),
 salaire_max        NUMBER(7,2),
 CONSTRAINT grille_salaire_grade_PK PRIMARY KEY (grade)
);

-- Contrainte de validite de la colonne poste
-- attention : la casse est respectee pour les valeurs des champs
-- ALTER TABLE employe
-- ADD CONSTRAINT employe_poste_CK
-- CHECK(poste ='Analyste' or poste='Employe' OR poste='Manager' OR poste='President' OR poste='Commercial');


ALTER TABLE employe
ADD CONSTRAINT employe_poste_CK
CHECK(poste IN ('Analyste','Employe','Manager','President','Commercial'));

 -- ---------------------------------------------
-- Alimentation des tables en donnees
-- ----------------------------------------------
-- Les contraintes etant activees, les ajouts doivent respecter l'ordre impose par les contraintes
-- La table departement doit etre remplie avant la table employe
INSERT INTO departement VALUES (10,'Comptabilite','NEW YORK');
INSERT INTO departement VALUES (20,'Recherche','DALLAS');
INSERT INTO departement VALUES (30,'Ventes','CHICAGO');
INSERT INTO departement VALUES (40,'Operations','BOSTON');
-- Pour respecter les contraintes, les chefs doivent etre inseres avant leurs subordonnes
INSERT INTO employe VALUES (7839,'KING','President',NULL,'17/11/1981',5000,NULL,10);
INSERT INTO employe VALUES (7698,'BLAKE','Manager',7839,'01/05/1981',2850,NULL,30);
INSERT INTO employe VALUES (7782,'CLARK','Manager',7839,'09/06/1981',2450,NULL,10);
INSERT INTO employe VALUES (7566,'JONES','Manager',7839,'02/04/1981',2975,NULL,20);
INSERT INTO employe VALUES (7654,'MARTIN','Commercial',7698,'28/09/1981',1250,1400,30);
INSERT INTO employe VALUES (7499,'ALLEN','Commercial',7698,'20/02/1981',1600,300,30);
INSERT INTO employe VALUES (7844,'TURNER','Commercial',7698,'08/09/1981',1500,0,30);
INSERT INTO employe VALUES (7900,'JAMES','Employe',7698,'03/12/1981',950,NULL,30);
INSERT INTO employe VALUES (7521,'WARD','Commercial',7698,'22/02/1981',1250,500,30);
INSERT INTO employe VALUES (7902,'FORD','Analyste',7566,'03/12/1981',3000,NULL,20);
INSERT INTO employe VALUES (7369,'SMITH','Employe',7902,'17/12/1980',800,NULL,20);
INSERT INTO employe VALUES (7788,'SCOTT','Analyste',7566,'09/12/1982',3000,NULL,20);
INSERT INTO employe VALUES (7876,'ADAMS','Employe',7788,'12/01/1983',1100,NULL,20);
INSERT INTO employe VALUES (7934,'MILLER','Employe',7782,'23/01/1982',1300,NULL,10);
INSERT INTO employe VALUES (7935,'ELECTRON LIBRE','Manager',7839,'23/01/1984',4300,NULL,NULL);

INSERT INTO grille_salaire VALUES (1,700,1200);
INSERT INTO grille_salaire VALUES (2,1201,1400);
INSERT INTO grille_salaire VALUES (3,1401,2000);
INSERT INTO grille_salaire VALUES (4,2001,3000);
INSERT INTO grille_salaire VALUES (5,3001,9999);

-- Validation des lignes ajoutees ci-dessus
COMMIT;