/*===
    create a table salary_history,
    and initially populate it with the initial salaries
    of the employees inserted by set-up-ex-tbls.sql

    ASSUMES: that set-up-ex-tbls.sql has been run

    by: Sharon Tuttle
    last modified: 2025-03-08
===*/

drop table salary_history;

create table salary_history
(empl_num    char(4),
 prev_salary number(6, 2),
 next_salary number(6, 2),
 change_date date,
 primary key (empl_num, change_date),
 foreign key (empl_num) references empl);

/*===
    to simulate these having been inserted when each
        of the 14 original employees were hired,
        insert them now but with a change_date of their
        original hiredate
===*/

insert into salary_history(empl_num, next_salary, change_date)
values
('7839', 5000.00, to_date('17-Nov-2011'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7566', 2975.00, to_date('02-Apr-2012'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7698', 2850.00, to_date('01-May-2013'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7782', 2450.00, to_date('09-Jun-2012'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7902', 3000.00, to_date('03-Dec-2012'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7369', 800.00, to_date('17-Dec-2012'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7499', 1600.00, to_date('20-Feb-2018'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7521', 1250.00, to_date('22-Feb-2019'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7654', 1250.00, to_date('28-Sep-2018'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7788', 3000.00, to_date('09-Nov-2018'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7844', 1500.00, to_date('08-Sep-2019'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7876', 1100.00, to_date('23-Sep-2018'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7900', 950.00, to_date('03-Dec-2017'));

insert into salary_history(empl_num, next_salary, change_date)
values
('7934', 1300.00, to_date('23-Jan-2016'));

commit;

/*===
    used to double-check the initial inserts into
    salary_history

select e.empl_num, salary, next_salary, hiredate, change_date
from   empl e, salary_history s
where  e.empl_num = s.empl_num;
===*/