hr@XE:11g2> column dept format 9999;
hr@XE:11g2> column salary format 999999;
hr@XE:11g2> column ename format a20;
hr@XE:11g2> column last_name format a12;
hr@XE:11g2> column sprank format 999.9;
hr@XE:11g2> column tprank format 999.9;
hr@XE:11g2> column mgmt format A4 heading 'MGR?';
hr@XE:11g2> r
1 SELECT
2 department_id AS dept,
3 last_name,
4 job_id,
5 (CASE
6 WHEN (employee_id IN (SELECT UNIQUE manager_id FROM employees)) THEN'*'
7 ELSE ' '
8 END) mgmt,
9 salary,
10 100*PERCENT_RANK() OVER
11 (PARTITION BY department_id ORDER BY salary ASC) AS sprank,
12 NTILE(4) over (PARTITION BY department_id ORDER BY salary DESC) AS s4tile,
13 sysdate - hire_date AS tenure,
14 100*PERCENT_RANK() OVER
15 (PARTITION BY department_id ORDER BY hire_date DESC) AS tprank,
16 NTILE(4) over (PARTITION BY department_id ORDER BY hire_date ASC) AS t4tile
17 FROM employees
18 WHERE department_id IN (30, 60, 100)
19 ORDER BY 1 ASC, 5 DESC
20 /
Dept. LAST_NAME JOB_ID MGR? SALARY SPRANK S4TILE TENURE TPRANK T4TILE
~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~ ~~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~
30 Raphaely PU_MAN * 109120 100.0 1 2700 100.0 1
30 Khoo PU_CLERK 30752 80.0 1 2538 80.0 1
30 Baida PU_CLERK 28768 60.0 2 1587 40.0 2
30 Tobias PU_CLERK 27776 40.0 2 1740 60.0 2
30 Himuro PU_CLERK 25792 20.0 3 1261 20.0 3
30 Colmenares PU_CLERK 24800 .0 4 993 .0 4
60 Hunold IT_PROG * 142848 100.0 1 1577 75.0 1
60 Ernst IT_PROG 95232 75.0 1 1074 .0 4
60 Pataballa IT_PROG 76186 25.0 2 1544 50.0 2
60 Austin IT_PROG 76186 25.0 3 1769 100.0 1
60 Lorentz IT_PROG 66662 .0 4 1177 25.0 3
100 Greenberg FI_MGR * 142943 100.0 1 2812 80.0 1
100 Faviet FI_ACCOUNT 107136 80.0 1 2813 100.0 1
100 Chen FI_ACCOUNT 97613 60.0 2 1674 60.0 2
100 Urman FI_ACCOUNT 92851 40.0 2 1514 20.0 3
100 Sciarra FI_ACCOUNT 91661 20.0 3 1672 40.0 2
100 Popp FI_ACCOUNT 82138 .0 4 874 .0 4
17 rows selected.