LOSTINTHEHEATOFITALL

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





-- connect tpg2/tpg2

select p_num, num_dept from projet join departement using(num_dept)
where p_location = 'tunis';

select p_num, num_dept from projet join departement using(num_dept)
where nom_dept = 'gestion';

select nom, adresse from employe join departement using(num_dept)
where nom_dept = 'informatique';

select nom, prenom
from employe join departement using (num_dept) join travail_dans using(nss)
where heure = (
    select heure from travail_dans where (
        nss = (select nss from employe where nom = 'Houda' and prenom = 'Meladi'))
    );


select nom, prenom
from employe, travail_dans
where employe.nss= travail_dans.nss and heure = (
    select heure
    from travail_dans ,employe
    where employe.nss= travail_dans.nss and nom = 'ali' and prenom = 'mohamed' and p_num = 1
)
and nom != 'ali' and prenom != 'mohamed';