SQ.

create view v_chef as
select *  from Employe where NumE in (select NumChef from Employe );

-- 2 ---
create view v_missions_en_cours as
select
    m.NumM,
    m.NumE,
    m.DateDebut,
    m.DateFin,
    e.Nom AS NomEmploye,
    e.Fonction,
    d.Nom AS NomDepartement
from Mission m
join Employe e ON m.NumE = e.NumE
join Departement d ON e.NumDep = d.NumDep
where DateFin is NULL;

 -- 3 --
insert into Mission (NumM, NumE, DateDebut, DateFin, Ville)
values (123, 101, '2024-12-01', NULL, 'Paris'); -- It will also appear in the v_missions_en_cours --


-- 4--

alter table  Mission drop column Ville;
-- La vue v_mission_en_cours depend de la colonne ville du tableau Mission, elle deviendra invalide--



-- Partie 2--

-- 1 --

delimiter //

create procedure  afficher_salaire_totale()
begin
 declare somme_salaire int;
 select sum(Salaire) into somme_salaire from Employe;
 if somme_salaire is NULL then
    select "Salaire null";
else
    select concat("Salaire totale des employés est : ", somme_salaire) as Salaire_Totale;
end if;
end;
//
delimiter ;
call afficher_salaire_totale();


-- 2 --
delimiter $$

create procedure afficher_employes_par_departement(ID_dep int)
begin
select  NumE,Nom,Fonction,NumChef,Salaire,Commission from Employe where NumDep=ID_dep;
end;
$$

delimiter ;

call afficher_employes_par_departement(10);

-- 3 --


delimiter ££
create procedure ajouter_mission(p_num_mission int, p_num_employe int , p_date_debut date, p_date_fin date,p_ville varchar(20))

begin

if not  (p_num_employe in (select numE from employe)) THEN
        select Concat("L'employé numero ", p_num_employee," n'existe pas ") as Message;
    else
        insert into Mission (NumM, NumE, DateDebut, DateFin, Ville)values
        (p_num_mission, p_num_employe, p_date_debut, p_date_fin, p_ville);
        SELECT CONCAT('La mission ', p_num_mission, ' a été ajoutée avec succès.') AS Message;
    END IF;
end

££
delimiter ;




-- FONCTION STOCKEE --

-- 1 --
delimiter $$

create function get_total_comission()
returns  int  not deterministic
begin
declare total_comission int;
select sum(ifnull(Comission,0)) into total_comission from employe;
return total_comission;
end;
$$

delimiter ;
call get_total_comission()


-- 2 --

delimiter //

create function get_salaire_moyen_par_departement(p_num_dep int)
returns int not DETERMINISTIC
begin
    declare x float;

    select sum(Salaire)/count(Salaire) into salaire_moyen from Employe where NumDep = p_num_dep;
    return salaire_moyen;
end
//
delimiter ;






-- TRIGGERS --

-- 1 --


delimiter //

create trigger modifier_date_modif before update on employe
for each row
begin
     set new.derniere_modification=now();
end;
//

delimiter ;



-- 2 --

delimiter //

create trigger le_manager_est_le_plus_riche before  update on Employe
for each row
begin
 if  (new.fonction <> "Manager" or new.fonction <> "PDG") and (new.salaire>5000) then
    select "Impossible" as message;
end if;
end;
//
delimiter ;