ROW_NUMBER()

Another Query😤

Here is another Query Which are using Row_Number in With Clause..
WITH GET_INCOME_TAX AS
( SELECT S.EMPNO, CASE WHEN S.JOB IN ('CLERK','SALESMAN')    THEN TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*10.5)/100)/360*30,0)
                       WHEN S.JOB IN ('MANAGER','PRESIDENT') THEN TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*18.5)/100)/360*30,0)
                     ELSE TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*10)/100)/360*30,0) END INCOME_TAX
  FROM SCOTT.EMP S
),
 DEPTS AS 
 (SELECT D.DEPTNO,D.DNAME,D.LOC, SUM(E.SAL) DEPT_SAL,TRUNC( AVG(E.SAL),2) DEPT_AVG
                  FROM SCOTT.DEPT D,
                            SCOTT.EMP E
                  WHERE E.DEPTNO(+)=D.DEPTNO
                  GROUP BY D.DEPTNO,D.DNAME, D.LOC
 )       
SELECT ROW_NUMBER() OVER(ORDER BY E.DEPTNO,E.SAL DESC ) SNO,
       E.EMPNO,
       E.ENAME,
       E.JOB,
       E.HIREDATE,
       E.SAL,
       GR.GRADE,
       E.COMM,
       NVL2(E.SAL+E.COMM,E.SAL+E.COMM,E.SAL) NETS,
       GIT.INCOME_TAX,
       DS.DEPTNO,
       DS.DNAME,
       DS.LOC,
       DS.DEPT_SAL,
       DS.DEPT_AVG
FROM SCOTT.EMP E, GET_INCOME_TAX GIT,DEPTS DS, SCOTT.SALGRADE GR
WHERE E.Empno = GIT.EMPNO
    AND E.DEPTNO = DS.DEPTNO
    AND E.SAL BETWEEN GR.LOSAL AND GR.HISAL