[퀴즈] 계층구조쿼리의 응용

emp 테이블을 이용하여 다음과 같은 결과를 도출하세요.
- ename_asc : empno와 mgr의 계층관계를 ename을 연결시켜 표현
- ename_desc : ename_asc 를 역으로 표현

WITH emp AS
(
SELECT 7369 empno, 'SMITH' ename, 7902 mgr FROM dual
UNION ALL SELECT 7499, 'ALLEN' , 7698 FROM dual
UNION ALL SELECT 7521, 'WARD'  , 7698 FROM dual
UNION ALL SELECT 7566, 'JONES' , 7839 FROM dual
UNION ALL SELECT 7654, 'MARTIN', 7698 FROM dual
UNION ALL SELECT 7698, 'BLAKE' , 7839 FROM dual
UNION ALL SELECT 7782, 'CLARK' , 7839 FROM dual
UNION ALL SELECT 7788, 'SCOTT' , 7566 FROM dual
UNION ALL SELECT 7839, 'KING'  , NULL FROM dual
UNION ALL SELECT 7844, 'TURNER', 7698 FROM dual
UNION ALL SELECT 7876, 'ADAMS' , 7788 FROM dual
UNION ALL SELECT 7900, 'JAMES' , 7698 FROM dual
UNION ALL SELECT 7902, 'FORD'  , 7566 FROM dual
UNION ALL SELECT 7934, 'MILLER', 7782 FROM dual
)
SELECT * FROM emp;

[결과]

정답은 몇일 뒤에 올리도록 하겠습니다.

[참조] http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1050&seq=2385&page=1&position=1

[정답보기] <=== 트리플클릭
SELECT empno
     , ename
     , mgr
     , LEVEL lv
     , SUBSTR(SYS_CONNECT_BY_PATH(ename,'-'),2) ename_asc
     , REVERSE(SUBSTR(SYS_CONNECT_BY_PATH(REVERSE(ename),'-'),2)) ename_desc
  FROM emp
 START WITH mgr IS NULL
 CONNECT BY PRIOR empno = mgr
;