Queries--

Using of REGEXP_SUBSTR

SELECT REGEXP_SUBSTR('SMITH,ALLEN,WARD,JONES','[^,]+', 1, LEVEL) REG_EXMP
FROM DUAL
CONNECT BY REGEXP_SUBSTR('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, LEVEL) IS NOT NULL;

REG_EXMP
SMITH
ALLEN
WARD

JONES

USING WITH

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)*1.5)/100)/360*30,0)

                       WHEN S.JOB IN ('MANAGER','PRESIDENT') THEN TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*8.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, 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)  

SELECT E.EMPNO,

       E.ENAME,

       E.JOB,

       E.HIREDATE,

       E.SAL,

       E.COMM, 

       NVL2(E.SAL+E.COMM,E.SAL+E.COMM,E.SAL) NETS,  

       GIT.INCOME_TAX,

       DS.DEPTNO,

       DS.DNAME,

       DS.DEPT_SAL,

       DS.DEPT_AVG

FROM SCOTT.EMP E, GET_INCOME_TAX GIT,DEPTS DS

WHERE E.Empno = GIT.EMPNO

    AND E.DEPTNO = DS.DEPTNO;

Result vary According to the condition, i just call for empno 7839.