[코멘토 SQL 직무부트캠프] 4차과제- 데이터추출 최종과제

 

<과제1> 현업이 요청한 레이아웃으로 아래와 같이 최종 결과를 추출하는 sql 작성

 

EMP_NO	EMP_NM	EMP_ GENDER	COUPLE_YN	SPOUSE_NM	CHILD _NM	CHILD_ GENDER	CHILD_BIRTH_YMD	CHILD_AGE	TWIN_YN
직원번호	직원성명	직원성별	사내부부여부	배우자 이름	자녀이름	자녀성별	자녀 생년월일	자녀나이	쌍생아 여부

 

상세요건

[1] 현재 재직 중인 임직원 중 자녀가 1명 이상인 사람

[2] 자녀의 생년월일이 2012년 1월 1일 ~ 2015년 12월 31일 경우만 대상에 포함

[3] 사내부부이면서 [1],[2] 조건에 만족하는 경우 자녀의 아버지 “직원번호” 기준으로 데이터 추출하고, 어머니는 리스트에서 제거할 것, 이 때 사내부부 여부는 Y로 표시, 배우자성명은 어머니 기준으로 표시

[4] 쌍생아의 경우 2명 모두 지원이 아님, 따라서 쌍생아 중 1명만 포함 시킬 것

[5] 쌍생아 1명을 가져오는 조건은 이름의 오름차순의 상위자로 추출할 것

 

---

풀이과정

 

: WITH문으로 자녀정보를 담은 테이블과, 사내부부 테이블을 따로 만들어 

 각 테이블을 EMP_C에 조인해서 최종 쿼리를 만드는 방법으로 생각. 

 

CHILD 테이블 

 

- FAM_C 테이블에서 2012년 1월 1일 ~ 2015년 12월 31일 사이에 태어난 자녀(A27) 정보만 담은 테이블

- 쌍생아의 경우 1명만 포함, 1명의 기준은 쌍생아 이름의 오름차순 상위자가 자녀이름에 들어감

 

 SELECT ch.emp_no emp_no
     , ch.fam_nm fam_nm
     , ch.rel_type_cd
     , ch.gender_cd gender_cd
     , ch.birth_ymd birth_ymd
     , tw.emp_no twin_emp_no
     , tw.twin twin_nm
FROM (SELECT *
      FROM FAM_C 
      WHERE rel_type_cd = 'A27'
        AND birth_ymd BETWEEN '20120101' AND '20151231')ch
      LEFT JOIN
     (SELECT FAM1.EMP_NO AS emp_no
           , FAM1.FAM_NM AS twin
      FROM FAM_C FAM1
           INNER JOIN FAM_C FAM2
           ON FAM1.EMP_NO = FAM2.EMP_NO
          AND FAM1.FAM_NM <> FAM2.FAM_NM
          AND FAM1.REL_TYPE_CD = FAM2.REL_TYPE_CD
          AND FAM1.REL_TYPE_CD = 'A27'
          AND FAM1.BIRTH_YMD = FAM2.BIRTH_YMD) tw
       ON ch.emp_no = tw.emp_no
WHERE ch.fam_nm <> tw.twin
  AND fam_nm < tw.twin
   OR tw.twin IS NULL;

 

 

쿼리결과

 

 

COUPLE 테이블 

 

- FAM_REL_C 테이블과 EMP_C테이블로 현재 재직중인 사내부부 중 남편의 정보만 담은 테이블

- 배우자의 성명 컬럼추가 

 

SELECT  hus.emp_no AS h_no
     , hus.emp_nm AS h_nm
     , e2.emp_nm AS w_nm
FROM 
(SELECT e1.emp_no AS emp_no
      , e1.emp_nm AS emp_nm
      , frel.emp_rel_no AS emp_rel_no
FROM fam_rel_c frel
    LEFT JOIN emp_c e1 ON e1.emp_no = frel.emp_no 
WHERE SYSDATE BETWEEN frel.sta_ymd AND frel.end_ymd
     AND SYSDATE BETWEEN e1.hire_ymd AND e1.retire_ymd 
     AND rel_type_cd = 'A18') hus
LEFT JOIN emp_c e2 ON e2.emp_no = hus.emp_rel_no;

 

쿼리 결과

 

 

최종 쿼리

- 사내부부여부, 쌍생아 여부 CASE 조건문 이용

- 자녀 나이는 현재날짜 기준 만나이로 계산 

- '현재 재직중' WHERE 조건문 이용

SELECT em.emp_no AS 직원번호
     , em.emp_nm AS 직원성명 
     , em.gender_cd AS 직원성별
     , (CASE WHEN co.h_no IS NULL THEN 'N' ELSE 'Y' END) AS 사내부부여부
     , co.w_nm AS 배우자성명
     , ch.fam_nm AS 자녀성명
     , ch.gender_cd AS 자녀성별
     , ch.birth_ymd AS 자녀생년월일
     , FLOOR((TO_CHAR(SYSDATE, 'YYYYMMDD') - ch.birth_ymd)/10000) AS 자녀나이
     ,(CASE WHEN ch.twin_nm IS NULL THEN 'N' ELSE 'Y' END) AS 쌍생아여부
FROM emp_c em
 INNER JOIN child ch ON em.emp_no = ch.emp_no
  LEFT JOIN couple co ON em.emp_no = co.h_no 
WHERE SYSDATE BETWEEN em.hire_ymd AND em.retire_ymd
ORDER BY em.emp_no;

 

---

 

WITH문 포함 

 

WITH couple AS(
SELECT  hus.emp_no AS h_no
     , hus.emp_nm AS h_nm
     , e2.emp_nm AS w_nm
FROM 
(SELECT e1.emp_no AS emp_no
      , e1.emp_nm AS emp_nm
      , frel.emp_rel_no AS emp_rel_no
FROM fam_rel_c frel
    LEFT JOIN emp_c e1 ON e1.emp_no = frel.emp_no 
WHERE SYSDATE BETWEEN frel.sta_ymd AND frel.end_ymd
     AND SYSDATE BETWEEN e1.hire_ymd AND e1.retire_ymd 
     AND rel_type_cd = 'A18') hus
LEFT JOIN emp_c e2 ON e2.emp_no = hus.emp_rel_no)
, child AS (
SELECT ch.emp_no emp_no
     , ch.fam_nm fam_nm
     , ch.rel_type_cd
     , ch.gender_cd gender_cd
     , ch.birth_ymd birth_ymd
     , tw.emp_no twin_emp_no
     , tw.twin twin_nm
FROM (SELECT *
      FROM FAM_C 
      WHERE rel_type_cd = 'A27'
        AND birth_ymd BETWEEN '20120101' AND '20151231')ch
      LEFT JOIN
     (SELECT FAM1.EMP_NO AS emp_no
           , FAM1.FAM_NM AS twin
      FROM FAM_C FAM1
           INNER JOIN FAM_C FAM2
           ON FAM1.EMP_NO = FAM2.EMP_NO
          AND FAM1.FAM_NM <> FAM2.FAM_NM
          AND FAM1.REL_TYPE_CD = FAM2.REL_TYPE_CD
          AND FAM1.REL_TYPE_CD = 'A27'
          AND FAM1.BIRTH_YMD = FAM2.BIRTH_YMD) tw
       ON ch.emp_no = tw.emp_no
WHERE ch.fam_nm <> tw.twin
  AND fam_nm < tw.twin
   OR tw.twin IS NULL
)

 
SELECT em.emp_no AS 직원번호
     , em.emp_nm AS 직원성명 
     , em.gender_cd AS 직원성별
     , (CASE WHEN co.h_no IS NULL THEN 'N' ELSE 'Y' END) AS 사내부부여부
     , co.w_nm AS 배우자성명
     , ch.fam_nm AS 자녀성명
     , ch.gender_cd AS 자녀성별
     , ch.birth_ymd AS 자녀생년월일
     , FLOOR((TO_CHAR(SYSDATE, 'YYYYMMDD') - ch.birth_ymd)/10000) AS 자녀나이
     ,(CASE WHEN ch.twin_nm IS NULL THEN 'N' ELSE 'Y' END) AS 쌍생아여부
FROM emp_c em
 INNER JOIN child ch ON em.emp_no = ch.emp_no
  LEFT JOIN couple co ON em.emp_no = co.h_no 
WHERE SYSDATE BETWEEN em.hire_ymd AND em.retire_ymd
ORDER BY em.emp_no;

 

 

쿼리결과

 

 

---

 

<과제2>  LISTAGG를 활용하여 직원번호가 10001483 인 자녀의 성명이 한줄로 추출 되도록 SQL작성

 

SELECT EMP_NO AS 직원번호,
       LISTAGG(FAM_NM, ',') WITHIN GROUP(ORDER BY FAM_NM) AS 자녀성명
FROM FAM_C
WHERE EMP_NO = '10001483' 
  AND REL_TYPE_CD = 'A27'
GROUP BY EMP_NO;

 

쿼리결과