[Oracle] DML(SELECT)_JOIN 연습문제

천재강쥐 2022. 8. 31. 00:06

----- JOIN 종합 실습문제 -----

1. 직급이 대리이면서 ASIA 지역에 근무하는 직원들의 사번, 사원명, 직급명, 부서명, 근무지역명, 급여를 조회하시오

-->> 오라클 전용 구문
SELECT E.EMP_ID "사번"
         , E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명"
         , D.DEPT_TITLE "부서명"
         , L.LOCAL_NAME "근무지역명"
         , E.SALARY "급여"
FROM EMPLOYEE E, DEPARTMENT D, JOB J, LOCATION L
WHERE E.JOB_CODE = J.JOB_CODE
    AND E.DEPT_CODE = D.DEPT_ID
    AND D.LOCATION_ID = L.LOCAL_CODE
    AND J.JOB_NAME = '대리'
    AND L.LOCAL_NAME LIKE 'ASIA%';
    
-->> ANSI 구문
SELECT E.EMP_ID "사번"
         , E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명"
         , D.DEPT_TITLE "부서명"
         , L.LOCAL_NAME "근무지역명"
         , E.SALARY "급여"
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
    WHERE J.JOB_NAME = '대리'
    AND L.LOCAL_NAME LIKE 'ASIA%';

2. 70년대생이면서 여자이고, 성이 전씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하시오

-->> 오라클 전용 구문
SELECT E.EMP_NAME "사원명"
         , E.EMP_NO "주민번호"
         , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
         , J.JOB_CODE "직급명" -- E.JOB_CODE = J.JOB_CODE
FROM EMPLOYEE E, DEPARTMENT D, JOB J
WHERE E.DEPT_CODE =D.DEPT_ID
    AND E.JOB_CODE = J.JOB_CODE
    AND TO_NUMBER(SUBSTR(E.EMP_NO, 1, 2), 99) BETWEEN 70 AND 79
    AND SUBSTR(E.EMP_NO, 8, 1) IN ('2', '4')
    AND E.EMP_NAME LIKE '전%';
    
-->> ANSI 구문
SELECT E.EMP_NAME "사원명"
         , E.EMP_NO "주민번호"
         , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
         , J.JOB_CODE "직급명" -- E.JOB_CODE = J.JOB_CODE
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (D.DEPT_ID = E.DEPT_CODE)
JOIN JOB J ON (J.JOB_CODE = E.JOB_CODE)
WHERE TO_NUMBER(SUBSTR(E.EMP_NO, 1, 2), 99) BETWEEN 70 AND 79
    AND SUBSTR(E.EMP_NO, 8, 1) IN ('2', '4')
    AND E.EMP_NAME LIKE '전%';

3. 이름에 '형'자가 들어있는 직원들의 사번, 사원명, 직급명을 조회하시오

-->> 오라클 전용 구문
SELECT E.EMP_ID "사번"
         , E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
FROM EMPLOYEE E, JOB J
WHERE J.JOB_CODE = E.JOB_CODE
    AND E.EMP_NAME LIKE '%형%';

-->> ANSI 구문
SELECT E.EMP_ID "사번"
         , E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
FROM EMPLOYEE E
JOIN JOB J ON (J.JOB_CODE = E.JOB_CODE)
    WHERE E.EMP_NAME LIKE '%형%';

4. 해외영업팀에 근무하는 직원들의 사원명, 직급명, 부서코드, 부서명을 조회하시오

-->> 오라클 전용 구문
SELECT E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명"
         , D.DEPT_ID "부서코드"
         , D.DEPT_TITLE "부서명" -- J.JOB_CODE = E.JOB_CODE / D.DEPT_ID = E.DEPT_CODE
FROM EMPLOYEE E, JOB J, DEPARTMENT D
WHERE J.JOB_CODE = E.JOB_CODE
    AND D.DEPT_ID = E.DEPT_CODE
    AND D.DEPT_TITLE LIKE '해외영업%'
    
-->> ANSI 구문
SELECT E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명"
         , D.DEPT_ID "부서코드"
         , D.DEPT_TITLE "부서명" -- J.JOB_CODE = E.JOB_CODE / D.DEPT_ID = E.DEPT_CODE
FROM EMPLOYEE E
JOIN JOB J ON (J.JOB_CODE = E.JOB_CODE)
JOIN DEPARTMENT D ON (D.DEPT_ID = E.DEPT_CODE)
    WHERE D.DEPT_TITLE LIKE '해외영업%'

5. 보너스를 받는 직원들의 사원명, 보너스, 연봉, 부서명, 근무지역명을 조회하시오

-->> 오라클 전용 구문
 SELECT E.EMP_NAME "사원명"
          , E.BONUS "보너스"
          , E.SALARY * 12 "연봉"
          , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
          , L.LOCAL_NAME "근무지역명" -- L.LOCAL_CODE = D.LOCATION_ID
FROM EMPLOYEE E, DEPARTMENT D, LOCATION L
WHERE D.DEPT_ID(+) = E.DEPT_CODE
-- (+): 붙이는 곳은 기준 반대! = 기준 테이블의 null 값도 포함해서 계산해 주라
    AND L.LOCAL_CODE(+) = D.LOCATION_ID
    AND E.BONUS IS NOT NULL;
    
-->> ANSI 구문
 SELECT E.EMP_NAME "사원명"
          , E.BONUS "보너스"
          , E.SALARY * 12 "연봉"
          , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
          , L.LOCAL_NAME "근무지역명" -- L.LOCAL_CODE = D.LOCATION_ID
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE =D.DEPT_ID)
LEFT JOIN LOCATION L ON (L.LOCAL_CODE = D.LOCATION_ID)
WHERE E.BONUS IS NOT NULL;

6. 부서가 있는 직원들의 사원명, 직급명, 부서명, 근무지역명을 조회하시오

-->> 오라클 전용 구문
SELECT E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
         , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
         , L.LOCAL_NAME "근무지역명" --  L.LOCAL_CODE = D.LOCATION_ID
FROM EMPLOYEE E, JOB J, DEPARTMENT D, LOCATION L
WHERE J.JOB_CODE = E.JOB_CODE
    AND D.DEPT_ID = E.DEPT_CODE
    AND L.LOCAL_CODE = D.LOCATION_ID
    AND E.DEPT_CODE IS NOT NULL;
    
-->> ANSI 구문
SELECT E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
         , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
         , L.LOCAL_NAME "근무지역명" --  L.LOCAL_CODE = D.LOCATION_ID
FROM EMPLOYEE E
JOIN JOB J ON (J.JOB_CODE = E.JOB_CODE)
JOIN DEPARTMENT D ON (D.DEPT_ID = E.DEPT_CODE)
JOIN LOCATION L ON (L.LOCAL_CODE = D.LOCATION_ID)
    WHERE E.DEPT_CODE IS NOT NULL;

7. '한국' 과 '일본' 에 근무하는 직원들의 사원명, 부서명, 근무지역명, 근무국가명을 조회하시오

-->> 오라클 전용 구문
SELECT E.EMP_NAME "사원명"
         , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
         , L.LOCAL_NAME "근무지역명" -- L.LOCAL_CODE = D.LOCATION_ID
         , N.NATIONAL_NAME "근무국가명" -- N.NATIONAL_CODE = L.NATIONAL_CODE
FROM EMPLOYEE E, DEPARTMENT D, LOCATION L, NATIONAL N
WHERE D.DEPT_ID = E.DEPT_CODE
    AND L.LOCAL_CODE = D.LOCATION_ID
    AND N.NATIONAL_CODE = L.NATIONAL_CODE
    AND N.NATIONAL_NAME IN ('한국', '일본');
    
-->> ANSI 구문
SELECT E.EMP_NAME "사원명"
         , D.DEPT_TITLE "부서명" -- D.DEPT_ID = E.DEPT_CODE
         , L.LOCAL_NAME "근무지역명" -- L.LOCAL_CODE = D.LOCATION_ID
         , N.NATIONAL_NAME "근무국가명" -- N.NATIONAL_CODE = L.NATIONAL_CODE
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (D.DEPT_ID = E.DEPT_CODE)
JOIN LOCATION L ON (L.LOCAL_CODE = D.LOCATION_ID)
JOIN NATIONAL N ON (N.NATIONAL_CODE = L.NATIONAL_CODE)
    WHERE N.NATIONAL_NAME IN ('한국', '일본');

8. 보너스를 받지 않는 직원들 중 직급코드가 J4 또는 J7 인 직원들의 사원명, 직급명, 급여를 조회하시오

-->> 오라클 전용 구문
SELECT E.EMP_NAME "사원명"
         , J.JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
         , E.SALARY "급여"
FROM EMPLOYEE E, JOB J
WHERE J.JOB_CODE = E.JOB_CODE
    AND BONUS IS NULL
    AND E.JOB_CODE IN ('J4', 'J7');
    
-->> ANSI 구문
SELECT EMP_NAME "사원명"
         , JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
         , SALARY "급여"
FROM EMPLOYEE E
JOIN JOB USING (JOB_CODE)
    WHERE BONUS IS NULL
        AND JOB_CODE IN ('J4', 'J7');

9. 사번, 사원명, 직급명, 급여등급 (SAL_GRADE), 구분을 조회하는데
이 때, 구분에 해당하는 값은
급여등급이 S1, S2 인 경우 '고급'
급여등급이 S3, S4 인 경우 '중급'
급여등급이 S5, S6 인 경우 '초급' 으로 조회되게 하시오

-->> 오라클 전용 구문
SELECT E.EMP_ID "사번"
         , J.JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
         , DECODE(S.SAL_LEVEL, 'S1', '고급', 'S2', '고급', 'S3', '중급', 'S4', '중급', 'S5', '초급',  '초급') -- S.SAL_LEVEL = E.SAL_LEVEL
FROM EMPLOYEE E, JOB J, SAL_GRADE S
WHERE J.JOB_CODE = E.JOB_CODE
    AND S.SAL_LEVEL = E.SAL_LEVEL;
    
-->> ANSI 구문
SELECT EMP_ID "사번"
         , JOB_NAME "직급명" -- J.JOB_CODE = E.JOB_CODE
         , DECODE(SAL_LEVEL, 'S1', '고급', 'S2', '고급', 'S3', '중급', 'S4', '중급', 'S5', '초급',  '초급') -- S.SAL_LEVEL = E.SAL_LEVEL
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
JOIN SAL_GRADE USING (SAL_LEVEL);

10. 각 부서별 총 급여합을 조회하되 이 때, 총 급여합이 1000만원 이상인 부서명, 급여합을 조회하시오

-->> 오라클 전용 구문
SELECT D.DEPT_TITLE "부서명"
        , SUM(E.SALARY) "급여합"
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.DEPT_ID = E.DEPT_CODE
GROUP BY D.DEPT_TITLE
    HAVING SUM(E.SALARY) >= 10000000;
    
-->> ANSI 구문
SELECT D.DEPT_TITLE "부서명"
        , SUM(E.SALARY) "급여합"
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (D.DEPT_ID = E.DEPT_CODE)
GROUP BY D.DEPT_TITLE
    HAVING SUM(E.SALARY) >= 10000000;

11. 각 부서별 평균급여를 조회하여 부서명, 평균급여 (정수처리) 로 조회하시오 단, 부서배치가 안된 사원들의 평균도 같이 나오게끔 하시오

-->> 오라클 전용 구문
SELECT DEPT_TITLE "부서명"
         , ROUND(AVG(SALARY),0) "평균급여"
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+)
GROUP BY DEPT_CODE, DEPT_TITLE(+);

-->> ANSI 구문
SELECT DEPT_TITLE "부서명"
         , ROUND(AVG(SALARY),0) "평균급여"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE =DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE;