SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH

문법

sys_connect_by_path::=

그림 설명

목적

SYS_CONNECT_BY_PATH함수는 계층적 쿼리에서 유의하다. 이 함수는 루트로 부터 node로 열의 값 Path를 반환환다. column 값은 CONNECT BY 조건에 의해 반환되는 각행을 char에 의해 분리된다.

column과 char의 데이터형은 CHAR,VARCHAR2,NCHAR,NVARCHAR2이다. 반환되는 문자열은 VARCHAR2형이고, column과 같은 문자 집합이다.

SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리(계층구조)에서만 유효하며, column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.

예제

다음 예제는 Kochhar이름의 종업원으로 부터 Kochhar 모든 종업원에게 종업원 이름의 Path를 반환한다.

SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
START WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id;

Path
---------------------------------------------------------------
 /Kochhar
   /Kochhar/Greenberg
     /Kochhar/Greenberg/Faviet
     /Kochhar/Greenberg/Chen
     /Kochhar/Greenberg/Sciarra
     /Kochhar/Greenberg/Urman
     /Kochhar/Greenberg/Popp
   /Kochhar/Whalen
   /Kochhar/Mavris
   /Kochhar/Baer
   /Kochhar/Higgins
     /Kochhar/Higgins/Gietz

테이블 전치 : http://blogbbs1.media.daum.net/griffin/do/blognews/cafe/read?bbsId=CF01&articleId=13122&pageIndex=17&searchKey=&searchValue=

select * from hrm_bas_mgt;
select * from hrm_dept;

--1번
select * from hrm_bas_mgt where main_thsis is not null;

--2번
select emp_nm from hrm_bas_mgt where emp_nm like '%김%' union 
select emp_nm from hrm_bas_mgt where emp_nm like '%주%';
--3번
select emp_nm as 성명, fn_get_crypt('D',RES_NO)as 주민번호,substr(fn_get_crypt('D',RES_NO),3,2),
case when to_number(substr(fn_get_crypt('D',RES_NO),3,2)) between 01 and 03 then '1분기'
        when to_number(substr(fn_get_crypt('D',RES_NO),3,2)) between 04 and 06 then '2분기'
        when to_number(substr(fn_get_crypt('D',RES_NO),3,2)) between 07 and 09 then '3분기'
        when to_number(substr(fn_get_crypt('D',RES_NO),3,2)) between 10 and 12 then '4분기'
else '알수 없음'
end  분기 from hrm_bas_mgt order by 3;

--4번
select emp_nm as 성명,  fn_get_crypt('D',RES_NO) as 주민등록번호 from hrm_bas_mgt where  to_number(substr(fn_get_crypt('D',RES_NO),3,2)) between 01 and 04;

-- 5번
select * from (select emp_nm as 성명,  fn_get_crypt('D',RES_NO) as 주민등록번호 ,
dense_rank() over (order by  (to_number(substr(fn_get_crypt('D',RES_NO),3,4))))as rownums
 from hrm_bas_mgt  ) where rownums between 20 and 40;

--6번

select B.dept_nm,count(A.dept_cd) from hrm_bas_mgt A 
                       LEFT OUTER JOIN hrm_dept B
                       ON A.DEPT_CD = B.DEPT_CD(+)
                       group by B.DEPT_nm
                       order by 2 desc;
                       
--7번

select A.emp_nm,
        A.emp_no,
        B.fmly_nm,
      row_number() OVER (PARTITION BY A.EMP_NO order by B.fmly_nm) RNUM
      from HRM_BAS_MGT A,HRM_FMLY B
      
      where A.EMP_NO = B.EMP_NO;
                    
          
          select emp_nm as 성명,
          substr(max(sys_connect_by_path(fmly_nm,',')),2)  as 가족성명
         from  (select A.emp_nm,
            A.emp_no,
            B.fmly_nm,
          row_number() OVER (PARTITION BY A.EMP_NO order by B.fmly_nm) RNUM
          from HRM_BAS_MGT A,HRM_FMLY B
          where A.EMP_NO = B.EMP_NO
          ) where emp_nm is not null
    start with RNUM =1
    connect by prior RNUM = RNUM -1
        and prior emp_no =emp_no
        group by emp_nm; 
        
    select * from hrm_dept;
    
    
    select dept_nm as 부서명,
    substr(max(sys_connect_by_path(dept_cd,'.')),2  )as 모음 from 
   (      
    select dept_nm,
       dept_cd,
      row_number() OVER (PARTITION BY dept_nm order by dept_cd ) RNUM
      from HRM_DEPT)
start with RNUM =1
connect by prior RNUM = RNUM -1
    and prior dept_nm =dept_nm
 group by dept_nm ;
      

제1회 SQL능력평가
※ 제한시간 30분.
※ 동점시 먼저 제출한 사람이 WINNER.
※ 따로 표시가 없는 문제에 대한 정렬은 무조건 성명(EMP_NM) 오름차순 ASC.
※ 문제를 풀고 SQL문을 확장자(.SQL) 파일로 저장 후 제출.
※ 채점은 SQL문 실행 후 결과 확인.
  
<참조 테이블>
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
1. HRM_BAS_MGT 테이블에 주요논문(MAIN_THSIS) 컬럼을 이용하여 주요논문이 등록되어 있는(NULL이 아닌) 사원을
 조회하는 SQL문을 작성 하시오. 
<성명, 주요논문>
  
(실행화면)
  
SELECT *
FROM HRM_BAS_MGT
WHERE MAIN_THSIS IS NOT NULL;
  
  
2. HRM_BAS_MGT 테이블에 성명(EMP_NM) 컬럼을 이용하여 성명에 '김' 또는 '주'가 들어가는 사원을 같은 건이 
중복되지 않게 조회하는 SQL문을 작성 하시오.
(단, UNION 또는 UNION ALL을 이용하여 작성한다.) <성명>
  
(실행화면)
  
SELECT DISTINCT (EMP_NM)
FROM (SELECT EMP_NM
FROM HRM_BAS_MGT
WHERE EMP_NM LIKE '%김%'
UNION ALL
SELECT EMP_NM
FROM HRM_BAS_MGT
WHERE EMP_NM LIKE '%주%')
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
3. HRM_BAS_MGT 테이블에 암호화 된 주민번호(RES_NO) 컬럼을 이용하여 생일을 분기별로 구분하여 조회하는 
SQL문을 작성하시오. 
(1월~3월 1분기,4월~6월 2분기, 7월~9월 3분기, 10월~12월 4분기) 
* 다음 함수를 이용하여 FN_GET_CRYPT('D',RES_NO) 복호화 한다. 
<성명, 주민번호, 분기>
  
(실행화면)
SELECT EMP_NM AS 성 명 
,FN_GET_CRYPT ('D', RES_NO) AS 주 민 번 호 
,SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 2)
,CASE
WHEN SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 2) BETWEEN '01' AND '03' THEN '1분 기 '
WHEN SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 2) BETWEEN '04' AND '06' THEN '2분 기 '
WHEN SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 2) BETWEEN '07' AND '09' THEN '3분 기 '
WHEN SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 2) BETWEEN '10' AND '12' THEN '2분 기 '
END AS 분 기 
FROM HRM_BAS_MGT
ORDER BY SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 2);
  
4. HRM_BAS_MGT 테이블에 암호화 된 주민번호(RES_NO) 컬럼을 이용하여 생일이 1월,2월,3월,4월인 사원을 
조회하는 SQL문을 작성 하시오.
* 다음 함수를 이용하여 FN_GET_CRYPT('D',RES_NO) 복호화 한다. 
<성명, 주민번호>
(실행화면)
SELECT EMP_NM AS 성 명 
,FN_GET_CRYPT ('D', RES_NO) AS 주 민 번 호 
, (RANK () OVER (ORDER BY SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 4))) ROWNUMS
FROM HRM_BAS_MGT
WHERE SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 1
OR SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 2
OR SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 3
OR SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 4
  
  
  
5. 4번에서 조회한 사원들 중에서 생년월일이 빠른 직원 20번째부터 40번째 까지 조회하는 SQL문을 작성 하시오.
(나이가 많은 순으로 정렬) 
<성명, 주민번호, 순번>
  
(실행화면)
SELECT *
FROM (SELECT EMP_NM AS 성 명 
,FN_GET_CRYPT ('D', RES_NO) AS 주 민 번 호 
, (RANK () OVER (ORDER BY SUBSTR (FN_GET_CRYPT ('D', RES_NO), 3, 4))) ROWNUMS
FROM HRM_BAS_MGT
WHERE SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 1
OR SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 2
OR SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 3
OR SUBSTR (FN_GET_CRYPT ('D', RES_NO), 4, 1) = 4)
WHERE ROWNUMS BETWEEN 20 AND 40
ORDER BY ROWNUMS
,주 민 번 호 
  
6. HRM_BAS_MGT 테이블과 HRM_DEPT 테이블을 이용하여 부서(DEPT_NM)별로 그룹하여 부서별 인원수가 8명 이상인 
부서만 조회하는 SQL문을 작성 하시오. (인원수가 많은 부서순으로 정렬)
<부서명, 인원수>
  
(실행화면)
  
SELECT *
FROM (SELECT B.DEPT_NM
,COUNT (A.DEPT_CD) AS CNT
FROM HRM_BAS_MGT A
,HRM_DEPT B
WHERE A.DEPT_CD = B.DEPT_CD(+)
GROUP BY B.DEPT_NM)
WHERE CNT >= 8
ORDER BY CNT DESC;
  
  
  
  
  
  
  
7. HRM_BAS_MGT 테이블과 HRM_FMLY 테이블을 이용하여 사원별로 가족들을 이름순으로 가로로 나열 하는 SQL문을 
작성 하시오. 
(단, 가족이 없는 사원들은 '가족없음'으로 표시되게 하시오.)
<성명, 가족성명>
  
(실행화면)
SELECT EMP_NM AS 성 명 
,SUBSTR (MAX (SYS_CONNECT_BY_PATH (FMLY_NM, ',')), 2) 가 족 성 명 
FROM (SELECT A.EMP_NM
,A.EMP_NO
,B.FMLY_NM
,ROW_NUMBER () OVER (PARTITION BY A.EMP_NO ORDER BY B.FMLY_NM) RNUM
FROM HRM_BAS_MGT A
,HRM_FMLY B
WHERE A.EMP_NO = B.EMP_NO)
WHERE EMP_NM IS NOT NULL
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1
AND PRIOR EMP_NO = EMP_NO
GROUP BY EMP_NM
  
8. 7번에 작성된 결과를 VIEW로 생성하시오.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
9. <추가문제> 다음은 부서별(DEPT_NM) 사원(EMP_NM)들의 급여(PAY_AMT)를 조회하는 SQL문이다. 아래
 SELECT문을 수정하여 부서별 급여의 합계를 구하는 SQL문을 작성하시오.
(정렬은 상관없음.)
  
WITH TEMP AS(
SELECT '기획조정실' DEPT_NM ,'이주임1' EMP_NM ,20000 PAY_AMT 
FROM DUAL
UNION ALL
SELECT '기획조정실' DEPT_NM ,'이주임2' EMP_NM ,30000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '기획조정실' DEPT_NM ,'이주임3' EMP_NM ,20000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '기획조정실' DEPT_NM ,'이주임4' EMP_NM ,15000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '공공사업부' DEPT_NM ,'황주임1' EMP_NM ,15000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '공공사업부' DEPT_NM ,'황주임2' EMP_NM ,26000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '공공사업부' DEPT_NM ,'황주임3' EMP_NM ,38000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '공공사업부' DEPT_NM ,'황주임4' EMP_NM ,40000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '인사총무실' DEPT_NM ,'김주임1' EMP_NM ,56000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '인사총무실' DEPT_NM ,'김주임2' EMP_NM ,46000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '인사총무실' DEPT_NM ,'김주임3' EMP_NM ,38000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '인사총무실' DEPT_NM ,'김주임4' EMP_NM ,60000 PAY_AMT
FROM DUAL 
UNION ALL
SELECT '인사총무실2' DEPT_NM ,'우주임1' EMP_NM ,56000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '인사총무실2' DEPT_NM ,'우주임2' EMP_NM ,46000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '인사총무실2' DEPT_NM ,'우주임3' EMP_NM ,38000 PAY_AMT
FROM DUAL
UNION ALL
SELECT '인사총무실2' DEPT_NM ,'우주임4' EMP_NM ,60000 PAY_AMT
FROM DUAL 
)
SELECT DEPT_NM
,EMP_NM
,PAY_AMT
FROM TEMP
  
  
(실행화면)
  
  
  
  
10. <추가문제> 9번 문제를 부서별 합계 금액이 큰 순서로 정렬하는 SQL문을 작성하시오.
  
  
(실행화면)
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
수고하셨습니다.*^^*