-- lab4.sql
-- by: Hunter Ciarabellini, Drew Hengehold 
-- last modified: 14/Feb/2025

set serveroutput on;
start /home/st10/set-up-ex-tbls.sql

spool lab4-out.txt;

prompt; 
prompt by: Hunter Ciarabellini, Drew Hengehold;
prompt;

/*
    procedure: empl_overview: void -> void
    purpose: expects nothing, returns nothing
    has the side-effect (if serveroutput is set to on) of printing the following:
    the current sysdate, # of empls, avg empl_salary for current employees.
*/

create or replace procedure empl_overview as
    curr_date date;
    number_empls number;
    avg_empl_salary number;
Begin
    select sysdate
    INTO curr_date
    from dual;

    select count(*)
    INTO number_empls
    from empl;

    select avg(salary)
    INTO avg_empl_salary
    from empl;

    dbms_output.put_line('The current date is ' || curr_date);
    dbms_output.put_line('The number of employees is ' || number_empls);
    dbms_output.put_line('The average salary is ' || avg_empl_salary);
end;
/
show errors

prompt;
prompt   TESTING EMPL OVERVIEW - if working properly, you should see a printout of the;
prompt ========================================;
prompt   The current date is CURR DATE;
prompt   The number of employees is 14;
prompt   The average salary is 2073.214285714285714285714285714285714286;
prompt;
prompt   PL/SQL procedure successfully completed.;
prompt ========================================;
prompt;

exec empl_overview;

delete from empl
where job_title = 'Clerk';

prompt;
prompt   TESTING EMPL OVERVIEW IF ALL CLERKS REMOVED - if working properly, you should see a printout of the ;
prompt ========================================;
prompt   The current date is CURR DATE;
prompt   The number of employees is 10;
prompt   The average salary is 2487.5;
prompt;
prompt   PL/SQL procedure successfully completed.;
prompt ========================================;
prompt;

exec empl_overview;

rollback;

spool off