DB G2

CREATE TABLE departement (num_dept NUMBER(8) ,
nom_dept varchar2(20));

CREATE TABLE employe (NSS NUMBER(4), nom varchar2(30), prenom varchar2(30),date_naiss DATE, adresse varchar2(20), sexe varchar2 (1),
salaire NUMBER(7,3), NSS_sup NUMBER (3));
ALTER TABLE employe
ADD CONSTRAINT salaire CHECK (salaire BETWEEN 1000 AND 8000);

CREATE TABLE projet (p_num NUMBER (3) , p_nom varchar2(30), p_location varchar2(30), num_dept NUMBER(8));
ALTER TABLE projet
ADD CONSTRAINT p_location CHECK (p_location IN ('Tunis','Sfax','Sousse'));
ALTER TABLE projet
ADD CONSTRAINT p_nom CHECK (p_nom LIKE 'P_%');

CREATE TABLE travail_dans (NSS NUMBER (4), p_num NUMBER (3), heure NUMBER(2));
ALTER TABLE travail_dans
ADD CONSTRAINT pk_travail PRIMARY KEY (NSS,p_num);

SELECT TABLE_NAME FROM user_tables;

SELECT constraint_name, TABLE_NAME FROM user_constraints WHERE TABLE_NAME ='DEPARTEMENT';

ALTER TABLE departement
ADD CONSTRAINT pk_departement PRIMARY KEY (num_dept);

ALTER TABLE employe
ADD CONSTRAINT pk_employe PRIMARY KEY (NSS);

ALTER TABLE employe
ADD CONSTRAINT fk_employe FOREIGN KEY (NSS_sup) REFERENCES employe;

ALTER TABLE projet
ADD CONSTRAINT pk_projet PRIMARY KEY (p_num) ;

ALTER TABLE projet
ADD CONSTRAINT fk_prejt FOREIGN KEY (num_dept) REFERENCES departement;


SELECT constraint_name, TABLE_NAME FROM user_constraints WHERE TABLE_NAME ='EMPLOYE';

ALTER TABLE employe
ADD (num_dept NUMBER(8));

ALTER TABLE employe
ADD CONSTRAINT fk2_employe FOREIGN KEY (num_dept) REFERENCES departement;

ALTER TABLE employe
ADD CONSTRAINT ck_employe CHECK (sexe IN ('M','F'));

CREATE TABLE materiel (codmat NUMBER(20), libmat varchar2(20), caracteristique varchar2(20), prix NUMBER (7,3));

ALTER TABLE materiel
ADD CONSTRAINT pk_materiel PRIMARY KEY (codmat);

ALTER TABLE materiel
MODIFY (caracteristique varchar2(30) );

ALTER TABLE materiel
ADD ( observation varchar2(10));
ALTER TABLE materiel
ADD CONSTRAINT ck_materiel CHECK (prix <10000 AND prix>100) ;

ALTER TABLE materiel
ADD (couleur varchar2(4)) ;
ALTER TABLE materiel
ADD CONSTRAINT ck2_materiel CHECK (couleur IN ('blanc', 'noir','beige'));

RENAME materiel TO materiel_info;

ALTER TABLE materiel_info
DROP PRIMARY KEY cascade;

DROP TABLE materiel_info;


INSERT INTO departement VALUES (1, 'P_comptabilité');
INSERT INTO departement VALUES (2, 'P_informatique');
INSERT INTO departement VALUES (3, 'P_gestion');
INSERT INTO departement VALUES (4, 'P_vente');
INSERT INTO employe VALUES (1236,'ali','mohamed', '09/01/56', 'Tunis', 'M', '4000', NULL, 3);
ALTER TABLE employe
MODIFY (nss_sup NUMBER(4));
INSERT INTO employe VALUES (4569,'Ben rejeb','Sami', '2 Feb 2001', 'Sousse', 'M',3000, 1236, 3);
INSERT INTO employe VALUES (8523,'Mabrouk','Mounir', '2 Feb 2001', 'Sfax', 'M',4000, NULL , 1);
INSERT INTO employe VALUES (1478,'Selmi','Riadh', '2 Feb 2001', 'Tunis', 'M',2500, 8523, 2);
INSERT INTO employe VALUES (8965,'Sandid','Imen', '2 Feb 2001', 'Gabes', 'F',3500, 8523, 2);
INSERT INTO employe VALUES (9632,'Saidan','Leila', '2 Feb 2001', 'Monastir', 'F',3200, NULL, 1);
INSERT INTO employe VALUES (5826,'Ben salah','Mehdi', '2 Feb 2001', 'Tunis', 'M',500*, 9632, 1); X
INSERT INTO employe VALUES (3695,'Meladi','Houda', '2 Feb 2001','Sfax','F',3000,1236,3);

INSERT INTO projet VALUES (1,'P_production1', 'Sfax', 1);
INSERT INTO projet VALUES (23,'P_Réorganisation', 'Tunis', 3);
INSERT INTO projet VALUES (3,'Production', 'Tunis',1);
INSERT INTO projet VALUES (7,'P Communication', 'Sousse',2);
INSERT INTO projet VALUES (18,'P_Gestion1', 'Bizerte *',3);
SELECT constraint_name, TABLE_NAME FROM user_constraints WHERE TABLE_NAME ='PROJET';

INSERT INTO travail_dans VALUES (1236,23,210); X
ALTER TABLE travail_dans
MODIFY (heure NUMBER(3));
INSERT INTO travail_dans VALUES (1236,1,23);
INSERT INTO travail_dans VALUES (8965,7,00);
INSERT INTO travail_dans VALUES (5826,1,10);
INSERT INTO travail_dans VALUES (4569,23,30);
INSERT INTO travail_dans VALUES (8523,7,60);
INSERT INTO travail_dans VALUES (5826,3,20);
INSERT INTO travail_dans VALUES (3695,18,10);
INSERT INTO travail_dans VALUES (3695,23,45);
INSERT INTO travail_dans VALUES (9632,3,15);
INSERT INTO travail_dans VALUES (1478,7,45);
INSERT INTO travail_dans VALUES (8965,23,57);