/* Exercice 1 */

On souhaite connaitre le salaire moyen des JOB. Ecrire une fonction qui prend en paramètre un JOB et retourne le salaire moyen du JOB. Traiter le cas où le JOB passé en paramètre n'existe pas.
Afficher la liste des JOBs et leur salaire moyen par ordre décroissant de salaire.


CREATE OR REPLACE FUNCTION avg_sal_job(p_job EMP.JOB%TYPE)
RETURN NUMBER
IS
    v_avg_sal NUMBER;
    v_job_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_job_count FROM EMP WHERE JOB = p_job;
    IF v_job_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'JOB ' || p_job || ' n''existe pas');
    END IF;
    SELECT AVG(SAL) INTO v_avg_sal FROM EMP WHERE JOB = p_job;
    RETURN v_avg_sal;
END;
/

-- Liste des JOBs et salaire moyen
SELECT JOB, ROUND(AVG(SAL), 2) AS "Salaire moyen"
FROM EMP
GROUP BY JOB
ORDER BY "Salaire moyen" DESC;




/* Exercice 2 */

Créer 1 trigger qui met à jour automatiquement la colonne Nombre_Emp de la table Suivi_Emp si:
- un nouvel employé est embauché dans un département
- un nouvel employé démissionne
- un employé est muté d'un département à un autre


CREATE OR REPLACE TRIGGER trg_update_suivi_emp
AFTER INSERT OR DELETE OR UPDATE OF DEPTNO ON EMP
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        MERGE INTO SUIVI_EMP s
        USING DUAL ON (s.DEPTNO = :NEW.DEPTNO)
        WHEN MATCHED THEN
            UPDATE SET s.Nombre_Emp = s.Nombre_Emp + 1
        WHEN NOT MATCHED THEN
            INSERT (DEPTNO, Nombre_Emp) VALUES (:NEW.DEPTNO, 1);
    ELSIF DELETING THEN
        UPDATE SUIVI_EMP SET Nombre_Emp = Nombre_Emp - 1 WHERE DEPTNO = :OLD.DEPTNO;
        DELETE FROM SUIVI_EMP WHERE DEPTNO = :OLD.DEPTNO AND Nombre_Emp = 0;
    ELSIF UPDATING('DEPTNO') THEN
        -- Ancien département
        UPDATE SUIVI_EMP SET Nombre_Emp = Nombre_Emp - 1 WHERE DEPTNO = :OLD.DEPTNO;
        DELETE FROM SUIVI_EMP WHERE DEPTNO = :OLD.DEPTNO AND Nombre_Emp = 0;
        -- Nouveau département
        MERGE INTO SUIVI_EMP s
        USING DUAL ON (s.DEPTNO = :NEW.DEPTNO)
        WHEN MATCHED THEN
            UPDATE SET s.Nombre_Emp = s.Nombre_Emp + 1
        WHEN NOT MATCHED THEN
            INSERT (DEPTNO, Nombre_Emp) VALUES (:NEW.DEPTNO, 1);
    END IF;
END;
/




/* Exercice 3 */

Ecrire un script qui met à jour le colonne COMM de la table EMP en fonction de la commission minimale COMM_MIN de la table EMP_SEUIL; Tout employé de la table EMP doit avoir une commission supérieure ou égale au montant de la commission minimale.
- Si COMM is null → COMM_MIN
- Si COMM < COMM_MIN COMM_MIN


UPDATE EMP e
SET COMM = (
    SELECT GREATEST(COALESCE(e.COMM, es.COMM_MIN), es.COMM_MIN)
    FROM EMP_SEUIL es
    WHERE es.JOB = e.JOB
)
WHERE EXISTS (
    SELECT 1 FROM EMP_SEUIL es WHERE es.JOB = e.JOB
);




/* Exercice 4 */

Ecrire une procédure sp_update_commission qui prend en paramètre un JOB p_job et une commission p_com. La procédure devra :
- Mettre à jour la table EMP_SEUIL; le job p_job aura pour nouvelle commission p_com.
- Mettre à jour la table EMP ; Si un employé ayant le job p_job a une commission inférieure à p_com, alors la commission doit être mise à jour (la commission doit toujours être supérieure à la commission minimale).


CREATE OR REPLACE PROCEDURE sp_update_commission (
    p_job EMP_SEUIL.JOB%TYPE,
    p_com EMP_SEUIL.COMM_MIN%TYPE
)
IS
BEGIN
    MERGE INTO EMP_SEUIL d
    USING (SELECT p_job AS JOB, p_com AS COMM_MIN FROM DUAL) s
    ON (d.JOB = s.JOB)
    WHEN MATCHED THEN
        UPDATE SET d.COMM_MIN = s.COMM_MIN
    WHEN NOT MATCHED THEN
        INSERT (JOB, COMM_MIN) VALUES (s.JOB, s.COMM_MIN);
    
    UPDATE EMP
    SET COMM = GREATEST(COALESCE(COMM, p_com), p_com)
    WHERE JOB = p_job;
END;
/