[Oracle] 오라클 Join 과제하기

학원에서 오라클 내부의 hr계정의 샘플테이블들을 이용한 Join 과제를 내주었다.

예전 컴활 시험을 준비하면서 뭔지도 모르고 봤던 ERD가 이 과제를 할때 조금 도움되었던 것 같다.

 

 

과제 문제

---- [과제] -----
테이블들을 조인하여
아래의 컬럼들이 나오게 하세요.
    
/*
부서번호,부서명,부서주소,부서장성명,사원번호,사원명,성별,나이,연봉,연봉소득세액,부서내연봉평균차액,부서내연봉등수,전체연봉등수 
*/

처음엔 생각없이 바로 코드를 썼다가, 점점 머릿속이 복잡해지고 내가 무슨코드를 쓰고있는지도 알 수 없게 되었다.

결국 문제는 풀었지만, 다시 풀어보려고 하면 정리가 안되어있으니 시간도 오래걸리고, 또 복잡한 SQL을 쓸 것 같아서 한번 그림으로 어떻게 풀지에 대한 고민을 해보고 난 뒤에 손을 대기 시작하니 금방 풀리게 되었다.

 

먼저 부서에 관한 테이블을 만들고, 사원에 관한 테이블을 만들었다.

 

• 부서정보테이블(부서번호, 부서명,부서주소, 부서장성명)
• 사원정보테이블(사원번호,사원명,부서번호,성별,나이,연봉)

-- 부서정보테이블(부서번호, 부서명,부서주소, 부서장성명)
    SELECT D.DEPARTMENT_ID,
           D.DEPARTMENT_NAME,
           LOCATION_ID,
           E.FIRST_NAME || ' ' || E.LAST_NAME DEPARTMENT_HEAD
    FROM DEPARTMENTS D JOIN EMPLOYEES E
    ON D.MANAGER_ID = E.EMPLOYEE_ID;
    
 
    -- 사원정보테이블(사원번호,사원명,부서번호,성별,나이,연봉)
    SELECT EMPLOYEE_ID,
           FIRST_NAME || ' ' || LAST_NAME,
           DEPARTMENT_ID,
           CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' ELSE '여' END GENDER,
           EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUBUN,1,2) + (CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN 1900 ELSE 2000 END)) + 1 AGE,
           (SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12 MONTH_SAL
    FROM EMPLOYEES;

그다음 그룹을 지을수 밖에 없는 테이블들을 만들었다.

• 부서번호별 연봉평균테이블(부서번호,부서별 연봉평균)
• 사원별 연봉소득세액 테이블(사원번호,연봉소득세액)

 -- 부서번호별 연봉평균 테이블(부서번호,부서별 연봉평균)
    SELECT DEPARTMENT_ID,
           AVG((SALARY+(SALARY * NVL(COMMISSION_PCT,0)))*12) AVG_DEPARTMENT_MONTH_SAL
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID;
    
    -- 사원별 연봉소득세액 테이블(사원번호,연봉소득세액)

    SELECT E.EMPLOYEE_ID,
           ((SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12) * TAXPERCENT MONTH_SAL_INCOMETAX
    FROM EMPLOYEES E JOIN TBL_TAXINDEX T
    ON (SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12 BETWEEN LOWERINCOME AND HIGHINCOME;

부서에 관련된 테이블은 부서정보테이블과 조인하고, 사원에 관련된 테이블은 사원정보테이블과 조인하였다.

 

 

 -- 사원정보테이블과 사원별 연봉소득세액 테이블 조인
   SELECT A.*,
         B.MONTH_SAL_INCOMETAX
   FROM
   (
   SELECT EMPLOYEE_ID,
           FIRST_NAME || ' ' || LAST_NAME,
           DEPARTMENT_ID,
           CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' ELSE '여' END GENDER,
           EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUBUN,1,2) + (CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN 1900 ELSE 2000 END)) + 1 AGE,
           (SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12 MONTH_SAL
    FROM EMPLOYEES
    ) A
    LEFT JOIN
    (
    SELECT E.EMPLOYEE_ID,
           ((SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12) * TAXPERCENT MONTH_SAL_INCOMETAX
    FROM EMPLOYEES E JOIN TBL_TAXINDEX T
    ON (SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12 BETWEEN LOWERINCOME AND HIGHINCOME
    ) B
    ON B.EMPLOYEE_ID = A.EMPLOYEE_ID;
    
    
    -- 부서정보테이블과 부서번호별 연봉평균테이블 조인
    
    SELECT A.*,
           B.AVG_DEPARTMENT_MONTH_SAL
    FROM
    (
    SELECT D.DEPARTMENT_ID,
           D.DEPARTMENT_NAME,
           LOCATION_ID,
           E.FIRST_NAME || ' ' || E.LAST_NAME DEPARTMENT_HEAD
    FROM DEPARTMENTS D JOIN EMPLOYEES E
    ON D.MANAGER_ID = E.EMPLOYEE_ID
    ) A
    RIGHT JOIN
    (
    SELECT DEPARTMENT_ID,
           AVG((SALARY+(SALARY * NVL(COMMISSION_PCT,0)))*12) AVG_DEPARTMENT_MONTH_SAL
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID
    ) B
    ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 

조인한 2개의 결과테이블을 조인하여서 내가 원하는 컬럼들만 뽑아왔다.

--================>> 최종결과 <<==============
    -- 각 조인한 결과를 조인하여 
    -- 부서번호,부서명,부서주소,부서장성명,사원번호,사원명,성별,나이,연봉,연봉소득세액
    -- ,부서내연봉평균차액,부서내연봉등수,전체연봉등수 나타내기
    
   SELECT A.DEPARTMENT_ID 부서번호,
          B.DEPARTMENT_NAME 부서명,
          B.LOCATION_ID 부서주소,
          B.DEPARTMENT_HEAD 부서장성명,
          A.EMPLOYEE_ID 사원번호,
          A.ENAME 사원명,
          A.GENDER 성별,
          A.AGE 나이,
          A.MONTH_SAL 연봉,
          A.MONTH_SAL_INCOMETAX 연봉소득세액,
          NVL(TRUNC(A.MONTH_SAL - B.AVG_DEPARTMENT_MONTH_SAL),0) 부서내연봉평균차액,
          RANK()OVER(PARTITION BY A.DEPARTMENT_ID ORDER BY A.MONTH_SAL DESC) 부서내연봉등수,
          RANK()OVER(ORDER BY A.MONTH_SAL DESC) 전체연봉등수
   FROM
   (
       SELECT A.*,
              B.MONTH_SAL_INCOMETAX
       FROM
       (
       SELECT EMPLOYEE_ID,
               FIRST_NAME || ' ' || LAST_NAME ENAME,
               DEPARTMENT_ID,
               CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' ELSE '여' END GENDER,
               EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(JUBUN,1,2) + (CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN 1900 ELSE 2000 END)) + 1 AGE,
               (SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12 MONTH_SAL
        FROM EMPLOYEES
        ) A
        LEFT JOIN
        (
        SELECT E.EMPLOYEE_ID,
               ((SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12) * TAXPERCENT MONTH_SAL_INCOMETAX
        FROM EMPLOYEES E JOIN TBL_TAXINDEX T
        ON (SALARY + (SALARY * NVL(COMMISSION_PCT,0)))*12 BETWEEN LOWERINCOME AND HIGHINCOME
        ) B
        ON B.EMPLOYEE_ID = A.EMPLOYEE_ID
    ) A
    LEFT JOIN
    (
    SELECT A.*,
           B.AVG_DEPARTMENT_MONTH_SAL
    FROM
        (
        SELECT D.DEPARTMENT_ID,
               D.DEPARTMENT_NAME,
               LOCATION_ID,
               E.FIRST_NAME || ' ' || E.LAST_NAME DEPARTMENT_HEAD
        FROM DEPARTMENTS D JOIN EMPLOYEES E
        ON D.MANAGER_ID = E.EMPLOYEE_ID
        ) A
        RIGHT JOIN
        (
        SELECT DEPARTMENT_ID,
               AVG((SALARY+(SALARY * NVL(COMMISSION_PCT,0)))*12) AVG_DEPARTMENT_MONTH_SAL
        FROM EMPLOYEES
        GROUP BY DEPARTMENT_ID
        ) B
        ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
    ) B
    ON B.DEPARTMENT_ID = A.DEPARTMENT_ID;

 

구상을 한 뒤에 그림을 그렸다. (나중에 알아보니 이게 ERD라는 것이더라)

 

 

처음에 구상을 하지 않고 문제에 접근하였을 때는 중간에 오타가 나거나, 오류가 뜨면 원인도 찾지못하고 쿼리문이 실행이 되어도 왜 실행이 되는지 잘 몰랐는데, 큰 그림을 먼저 구상한 뒤에 쿼리문을 작성하니 막상 쿼리문 쓰는데에는 시간이 엄청 조금 걸렸다. 이 과제를 통하여 큰 설계를 먼저 철저하게 한 뒤에 코드를 짜거나, 쿼리문을 작성하는 것이 퀄리티가 높게 나온다는 것을 깨닫게 되었다.