exercice 4

CREATE TABLE employe(
    matricule INT NOT NULL,
    nom VARCHAR(50),
    prenom VARCHAR(50),
    datedenaissance DATE,
    dateenbauche DATE ,
    salaire FLOAT ,
    diplome VARCHAR(50) ,
    fonction VARCHAR(50),
    tel VARCHAR(50),
    ville VARCHAR(50)
);

CREATE TABLE departement(
    numdept VARCHAR(50) NOT NULL,
    nomdept VARCHAR(50),
    villeDept VARCHAR(50),
    tele VARCHAR (50),
);

ALTER TABLE employe ADD CONSTRAINT pm_mat PRIMARY KEY(matricule);


ALTER TABLE departement ADD CONSTRAINT pm_numdept PRIMARY KEY(numdept);

ALTER TABLE employe ADD CONSTRAINT sup_dateemb CHECK(dateenbauche < GetDate());

ALTER TABLE employe ADD CONSTRAINT sup_sal CHECK(salaire > 3000);

ALTER TABLE employe ADD CONSTRAINT deploam_check CHECK(diplome IN ('Bac +2','Bac +3','Bac +5') ), DEFAULT  'Bac +2' FOR diplome;

ALTER TABLE employe ADD CONSTRAINT fonction_check CHECK (fonction IN('Diveloppeur','chef project','secretaire','commercial','directeur'));

ALTER TABLE employe ADD CONSTRAINT ville_check CHECK(ville NOT IN ('tata','ourwawate')), DEFAULT 'casa' FOR ville;

ALTER TABLE departement ADD CONSTRAINT deplt_ville CHECK(villeDept IN ('Rabat','Tanger')),DEFAULT 'casa' FOR villeDept;

ALTER TABLE departement ADD CONSTRAINT nomdept_check CHECK(nomdept IN('Informatique','Commercial','logistique','rechere'));


ALTER TABLE employe ADD numdept VARCHAR(50) ;
ALTER TABLE employe ADD CONSTRAINT fk_numdept FOREIGN KEY (numdept) REFERENCES departement(numdept);


INSERT INTO departement VALUES ('1','Informatique','Rabat','000000');
INSERT INTO departement VALUES ('2','Commercial','Tanger','000000');
INSERT INTO departement VALUES ('3','logistique','Tanger','000000');
INSERT INTO departement VALUES ('4','rechere','Rabat','000000');

INSERT INTO employe (nom,prenom,salaire,numdept) VALUES ('mouhi','ismail',3001,'1');



--12
UPDATE employe SET salaire = (salaire+(salaire/100)) WHERE ville!='casa';

--13
UPDATE employe SET salaire = (salaire+(salaire/100)*1.5) WHERE  diplome='Bac +5' AND fonction='directeur';


--18
SELECT nom,prenom FROM employe WHERE diplome='Bac +5' AND fonction!='Directeur' AND ville!='casa'
--19
SELECT COUNT(matricule) AS 'nombre d'' employe' FROM employe
--20


/*
create table employe(
    matricule int NOT NULL,
    nom varchar(50),
    prenom varchar(50),
    datedenaissance date,
    dateenbauche date ,
    salaire float ,
    diplome varchar(50) ,
    fonction varchar(50),
    tel varchar(50),
    ville varchar(50)
);


create table departement(
    numdept varchar(50) NOT NULL,
    nomdept varchar(50),
    villeDept varchar(50),
    tele varchar (50),
);
'Informatique','Commercial','logistique','rechere'
*/