hr@XE:11g2> column deptjob format a15;

hr@XE:11g2> column ename format a22;

hr@XE:11g2> column sprank format 999.9;

hr@XE:11g2> column tprank format 999.9;

hr@XE:11g2> column skew format 999.9;

hr@XE:11g2> column adjrate format 9.999;

hr@XE:11g2> column adjustment format $99999.99;

hr@XE:11g2> r

  1  WITH skewed_pay AS

  2  (

  3    SELECT

  4      department_id||job_id AS deptjob,

  5      first_name||' '||last_name AS ename,

  6      salary,

  7      100*PERCENT_RANK() OVER

  8        (PARTITION BY department_id||job_id ORDER BY salary ASC) AS sprank,

  9      sysdate - hire_date AS tenure,

 10      100*PERCENT_RANK() OVER

 11        (PARTITION BY department_id||job_id ORDER BY hire_date DESC) AS tprank

 12    FROM employees

 13    WHERE department_id||job_id IN

 14      (SELECT deptjob FROM

 15        (SELECT COUNT(*), department_id||job_id AS deptjob FROM employees

 16         GROUP BY department_id||job_id  HAVING COUNT(*) > 3 )

 17      )

 18  )

 19  SELECT

 20    deptjob,

 21    ename,

 22    salary,

 23    tenure,

 24    tprank - sprank AS skew,

 25    (CASE

 26       WHEN tprank-sprank >= 25.0 AND tprank-sprank < 50.0 THEN .01

 27       WHEN tprank-sprank >= 50.0 AND tprank-sprank < 75.0 THEN .02

 28       WHEN tprank-sprank >= 75.0 THEN .025

 29       ELSE 0

 30    END) adjrate,

 31    (CASE

 32       WHEN tprank-sprank >= 25.0 AND tprank-sprank < 50.0 THEN .01*salary

 33       WHEN tprank-sprank >= 50.0 AND tprank-sprank < 75.0 THEN .02*salary

 34       WHEN tprank-sprank >= 75.0 THEN .025*salary

 35       ELSE 0

 36     END) adjustment

 37  FROM

 38    skewed_pay

 39  WHERE

 40    tprank - sprank >= 25.0

 41  ORDER BY 5 DESC;

DEPTJOB         ENAME                   SALARY TENURE   SKEW ADJRATE ADJUSTMENT

~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~~ ~~~~~~~~~~

60IT_PROG       David Austin             76186   1770   75.0    .025   $1904.64

50ST_CLERK      James Marlow             24800   1899   52.6    .020    $496.00

50ST_MAN        Payam Kaufling           78368   2556   50.0    .020   $1567.36

80SA_REP        Lindsey Smith            79360   1877   39.3    .010    $793.60

80SA_REP        Allan McEwen             89280   2098   28.6    .010    $892.80

80SA_REP        Patrick Sully            94240   2248   28.6    .010    $942.40

80SA_REP        Louise Doran             74400   1597   25.0    .010    $744.00

60IT_PROG       Diana Lorentz            66662   1178   25.0    .010    $666.62

30PU_CLERK      Sigal Tobias             27776   1741   25.0    .010    $277.76

100FI_ACCOUNT   Ismael Sciarra           91661   1673   25.0    .010    $916.61

60IT_PROG       Valli Pataballa          76186   1545   25.0    .010    $761.86

11 rows selected.