rem find the person who got the biggest payraise (either as a percentage or as an absolute amount) and specify the timestamp of that payraise


rem MINVALUE and MAXVALUE can be used to retrieve all records


rem versions_startscn, versions_starttime,  versions_endscn, versions_endtime,  versions_xid, versions_operation,
rem http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10002.htm#SQLRF01702
rem http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_fl.htm#1011142
       

update emp
set    sal = sal + 200
/

commit
/

update emp
set    sal = sal * 1.121
where  job = 'ANALYST'
/

commit
/


update emp
set    sal = sal*1.05 + 368
where  job = 'CLERK'
/

commit
/


update emp
set    sal = sal * 1.081 + 35
where  sal < 4000
/

commit
/



with sal_values as 
( select empno
  ,      ename
  ,      sal
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   emp versions between scn minvalue and maxvalue
)
, salary_raises as
( select empno
  ,      sal
  ,      lag(sal) over (partition by empno order by versions_endscn asc) previous_sal
  ,      ename
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   sal_values
)
select empno
,      ename
,      sal
,      (sal - previous_sal) absolute_raise
,      (sal - previous_sal)/previous_sal*100  percentage_raise
,      versions_starttime
from   salary_raises
order
by     ename
,      versions_endscn
/


with sal_values as 
( select empno
  ,      ename
  ,      sal
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   emp versions between scn minvalue and maxvalue
)
, salary_raises as
( select empno
  ,      sal
  ,      lag(sal) over (partition by empno order by versions_endscn asc) previous_sal
  ,      ename
  ,      versions_starttime
  ,      versions_endtime
  ,      versions_endscn
  from   sal_values
)
, ordered_absolute_payraises as
( select empno
  ,      ename
  ,      sal
  ,      (sal - previous_sal) absolute_raise
  ,      (sal - previous_sal)/previous_sal*100  percentage_raise
  ,      versions_starttime
  from   salary_raises
  order
  by     absolute_raise desc nulls last
)
select empno
,      ename
,      absolute_raise
,      percentage_raise
,      versions_starttime 
from   ordered_absolute_payraises
where  rownum = 1
/