BAZA

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)
);

CREATE TABLE projet (
    p_num NUMBER (3),
    p_nom varchar2(30),
    p_location varchar2(30),
    num_dept NUMBER(8)
);

CREATE TABLE travail_dans (
    NSS NUMBER (4),
    p_num NUMBER (3),
    heure NUMBER(2)
);

ALTER TABLE
    employe
ADD
    CONSTRAINT check_salire CHECK (
        salaire BETWEEN 1000
        AND 8000
    );

ALTER TABLE
    projet
ADD
    CONSTRAINT check_location CHECK (p_location IN ('tunis', 'sfax', 'sousse'));

ALTER TABLE
    projet
ADD
    CONSTRAINT check_nom CHECK (p_nom LIKE 'P_%');

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

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

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

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

ALTER TABLE
    travail_dans
ADD
    CONSTRAINT pk_travail_dans FOREIGN KEY (NSS) REFERENCES employe(NSS);

ALTER TABLE
    travail_dans
ADD
    CONSTRAINT pk_travail_dans2 FOREIGN KEY (p_num) REFERENCES projet(p_num);

ALTER TABLE
    travail_dans
ADD
    CONSTRAINT fk_travail_dans_fkInsteadOfPk PRIMARY KEY(NSS, p_num);

ALTER TABLE
    employe
ADD
    num_dept NUMBER(8);

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

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

ALTER TABLE
    employe
ADD
    CONSTRAINT check_sexe CHECK (sexe IN ('M', 'F', 'm', 'f'));

INSERT INTO
    departement
VALUES
    (1, 'comptabilite');

INSERT INTO
    departement
VALUES
    (2, 'infromatique');

INSERT INTO
    departement
VALUES
    (3, 'gestion');

INSERT INTO
    departement
VALUES
    (4, 'vente');

INSERT INTO employe VALUES (1236,'ali','mohamed', '09 Jan 56', 'Tunis', 'M', '4000', NULL, 3);
INSERT INTO employe VALUES (4569,'Ben rejeb','Sami', '02 Sep 69', 'Sousse', 'M',3000, 1236, 3);
INSERT INTO employe VALUES (8523,'Mabrouk','Mounir', '12 Jun 60', 'Sfax', 'M',4000, NULL , 1);
INSERT INTO employe VALUES (1478,'Selmi','Riadh', '25 Mar 67', 'Tunis', 'M',2500, 8523, 2);
INSERT INTO employe VALUES (8965,'Sandid','Imen', '25 Mar 67', 'Gabes', 'F',3500, 8523, 2);
INSERT INTO employe VALUES (9632,'Saidan','Leila', '26 Aug 70', 'Monastir', 'F',3200, NULL, 1);
INSERT INTO employe VALUES (5826,'Ben salah','Mehdi', '27 May 72', 'Tunis', 'M',5000, 9632, 1);
INSERT INTO employe VALUES (3695,'Meladi','Houda', '14 Dec 73','Sfax','F',3000,1236,3);


INSERT INTO projet VALUES (1, 'P_Production1', 'sfax', 1);
INSERT INTO projet VALUES (23, 'P_Reorganisation', 'tunis', 3);
INSERT INTO projet VALUES (3, 'ProductionX', 'tunis', 1);
INSERT INTO projet VALUES (7, 'P_Communication', 'sousse', 2);
INSERT INTO projet VALUES (18, 'P_Gestion1', 'sousse', 3);

ALTER TABLE travail_dans
MODIFY (heure NUMBER(3));


INSERT INTO travail_dans VALUES (1236,23,210);
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);

DELETE FROM departement WHERE num_dept = 4;

UPDATE employe SET salaire = salaire + 100 WHERE NSS_sup = 8523;

-- TP2

SELECT * FROM employe WHERE num_dept = 3;

SELECT nom, num_dept FROM employe;

SELECT nom_dept FROM departement WHERE num_dept > 2;

SELECT salaire, salaire*12 FROM employe WHERE salaire*12 > 40000;

-- 5)

SELECT nom, salaire FROM employe WHERE salaire*12 BETWEEN 35000 AND 40000;

SELECT nom, salaire, date_naiss FROM employe WHERE num_dept = 3 ORDER BY salaire DESC;

SELECT nom FROM employe WHERE nom LIKE '__n%';

SELECT nom FROM employe WHERE nom LIKE '%i%';

SELECT NSS, nom FROM employe WHERE adresse IN ('Tunis', 'Sfax');

SELECT NSS, nom FROM employe WHERE adresse NOT IN ('Tunis', 'Sfax');

SELECT * FROM employe WHERE date_naiss BETWEEN '1 Jan 60' AND '31 Dec 70';

-- TP3