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 tenure format 9999;
hr@XE:11g2> column salary format 999999;
hr@XE:11g2> r
1 select department_id, last_name, salary,
2 ntile(4) over (PARTITION BY department_id ORDER BY salary DESC) AS s4tile,
3 ntile(3) over (PARTITION BY department_id ORDER BY salary DESC) AS s3tile,
4 sysdate-hire_date as tenure,
5 ntile(4) over (PARTITION BY department_id ORDER BY hire_date ASC) AS t4tile,
6 ntile(3) over (PARTITION BY department_id ORDER BY hire_date ASC) AS t3tile
7 FROM employees
8 WHERE
9 department_id IN
10 (SELECT department_id FROM
11 (SELECT COUNT(*), department_id FROM employees
12 GROUP BY department_id HAVING COUNT(*) BETWEEN 4 AND 8
13 )
14 )
15 ORDER BY 1 ASC, 3 DESC, 6 DESC;
DEPARTMENT_ID LAST_NAME SALARY S4TILE S3TILE TENURE T4TILE T3TILE
~~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~ ~~~~~~ ~~~~~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~~~~~
30 Raphaely 109120 1 1 2700 1 1
30 Khoo 30752 1 1 2538 1 1
30 Baida 28768 2 2 1587 2 2
30 Tobias 27776 2 2 1740 2 2
30 Himuro 25792 3 3 1261 3 3
30 Colmenares 24800 4 3 993 4 3
60 Hunold 142848 1 1 1577 1 1
60 Ernst 95232 1 1 1074 4 3
60 Austin 76186 2 2 1769 1 1
60 Pataballa 76186 3 2 1544 2 2
60 Lorentz 66662 4 3 1177 3 2
100 Greenberg 142943 1 1 2812 1 1
100 Faviet 107136 1 1 2813 1 1
100 Chen 97613 2 2 1674 2 2
100 Urman 92851 2 2 1514 3 3
100 Sciarra 91661 3 3 1672 2 2
100 Popp 82138 4 3 874 4 3
17 rows selected.