Untitled

DELIMITER //

CREATE PROCEDURE last_name_proc1()
BEGIN
    DECLARE henderson_job VARCHAR(50);
    DECLARE henderson_edu_level INT;

    -- Fetch job and education level of HENDERSON
    SELECT JOB, EDLEVEL
    INTO henderson_job, henderson_edu_level
    FROM employee
    WHERE LASTNAME = 'HENDERSON';


    DROP TABLE IF EXISTS persistent_henderson;


    CREATE TABLE persistent_henderson (
        job VARCHAR(50),
        edlevel INT
    );

    INSERT INTO persistent_henderson (job, edlevel)
    VALUES (henderson_job, henderson_edu_level);


    CREATE OR REPLACE VIEW salary_vw AS
    SELECT 
        e.EMPNO AS 'Employee Number',
        e.WORKDEPT AS 'Department Name',
        CONCAT(e.FIRSTNAME, ' ', e.MIDINT, ' ', e.LASTNAME) AS 'Full Name',
        e.JOB AS 'Job',
        CASE 
            WHEN e.EDLEVEL = 12 THEN 'High School'
            WHEN e.EDLEVEL = 14 THEN 'Associate Degree'
            WHEN e.EDLEVEL = 16 THEN 'Bachelor’s Degree'
            WHEN e.EDLEVEL = 18 THEN 'Master’s Degree'
            WHEN e.EDLEVEL = 20 THEN 'Doctorate Degree'
            ELSE 'Other'
        END AS 'Education Level',
        FLOOR(DATEDIFF(CURDATE(), e.HIREDATE) / 365) AS 'Years Worked',
        e.SALARY AS 'Salary',
        CONCAT(FLOOR(DATEDIFF(CURDATE(), e.HIREDATE) / 365), ' Years') AS 'WORKED'
    FROM 
        employee e
    JOIN 
        persistent_henderson ph
    ON 
        e.JOB = ph.job AND e.EDLEVEL = ph.edlevel;
END$$

DELIMITER //;


DROP PROCEDURE IF EXISTS last_name_proc1;


CALL last_name_proc1();


SELECT * FROM salary_vw;