Views and misc

Create schema employeeSchema;

Create view employeeschema.Employeeview
As
Select 
employeekey,
personfirstname firstname, 
personlastname lastname,
personemail email,
positionname,
employeepositionstartdate startdate
From employee
inner join Person
using(personkey)
inner join employeeposition
using(employeekey)
inner join jobposition
using (positionkey);

Select * From employeeschema.employeeview;
Select personfirstname, personlastname from employeeschema.employeeview;
Select firstname, lastname from employeeschema.employeeview;

--updateable
--if there are no joins, no calculated fields, 

Create view employeeschema.emailview
As
Select personlastname,
personfirstname,
personemail
From person;

Create or replace view employeeschema.emailview
As
Select personlastname,
personfirstname,
personemail,
persondateadded
From person
Where personlastname like 'J%'
with check option;

Select * from employeeschema.emailview;
Select * from person where personkey=(select max(personkey)from person);



Update employeeschema.emailview
Set personfirstname='Jason'
where personemail='jAnderson@gmail.com';

Insert into employeeschema.emailview(personlastname, personfirstname, personemail, 
          persondateadded)
values('Brown','Nelson', 'nb@hotmail.com', current_timestamp);

Create materialized view employeeschema.grantview
As
Select Grantapplicationkey, grantapplicationdate, 
granttypename, personlastname, grantapplicationamount
From person
join grantapplication
using (personkey)
join granttype
using (granttypekey);

Select * from employeeschema.grantview where grantapplicationkey =
(Select max(grantapplicationkey) from grantapplication);

Insert into grantapplication(grantapplicationdate, granttypekey, personkey, 
 grantapplicationamount, grantapplicationexplain )
values (current_timestamp, 1, 50, 400.00, 'lunch time');

refresh materialized view employeeschema.grantview;