derived class for current employees

drop table terminate;
drop table hire;
drop table person;
drop sequence s;

create sequence s start with 1000 increment by 1;

create table Person(
  Personid int primary key default next value for s,
  fname varchar(50)
);

insert into person (fname) values('Joseph'),('Brigham'),('John'),('Wilfred'),('Lorenzo'),('Heber'),('Lyman'), ('');

create table hire(
hireID int primary key default next value for s,
HireDate datetime default getdate(),
Startdate datetime,
personID int references person,
hirerID int references person
);

insert into hire(startdate, personid, hirerid) values('4/6/1830', 1001, 1000);
insert into hire(startdate, personid, hirerid) values('4/6/1830', 1002, 1000);
insert into hire(startdate, personid, hirerid) values('4/6/1830', 1003, 1000);
insert into hire(startdate, personid, hirerid) values('4/6/1830', 1004, 1000);
insert into hire(startdate, personid, hirerid) values('4/6/1830', 1005, 1000);
insert into hire(startdate, personid, hirerid) values('4/6/1830', 1006, 1000);
insert into hire(startdate, personid, hirerid) values('4/6/1831', 1006, 1000);


create table terminate(
terminateID int primary key default next value for s,
termDate datetime default getdate(),
effectiveDate datetime,
terminatorid int references person,
hireID int references hire,
type varchar(50)
);

insert into terminate(effectiveDate, terminatorid, hireid, type) values(getdate(),1000,
(select MIN(hireid) from hire where personid = (select personid from person where fname = 'Lyman')),
'Excommunication'
 );




select * from person;
select * from  hire;
select * from terminate;


create view employee as
select fname, hiredate, startdate, terminate.hireid
from person
  join hire on person.personid = hire.personid
  left outer join terminate on terminate.hireid = hire.hireid
where terminate.hireid  is null
;

select * from employee