[Oracle/수업 과제 practice] JOIN

✅ 답안과 비교하여 스스로 SQL 구문 개선점 짚어보기 완료(2022.01.20)

 

JOIN 연습문제 1. ✅

-- 1. 이름에 '형'자가 들어가는 직원들의
-- 사번, 사원명, 직급명을 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_ID 사번
     , E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE E.EMP_NAME LIKE '%형%';
 
-- 오라클 전용
SELECT
       E.EMP_ID 사번
     , E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
  FROM EMPLOYEE E
     , JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   AND E.EMP_NAME LIKE '%형%';

 

JOIN 연습문제 2. ✅

사실 WHEN 조건절을 SUBSTR(E.EMP_NO, 1, 2) >= 70 처럼 짧게만 세워도 괜찮다.

-- 2. 주민번호가 70년대 생이면서 성별이 여자이고, 
--    성이 전씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하세요.
-- ANSI 표준
SELECT
       E.EMP_NAME 사원명
     , E.EMP_NO 주민번호
     , D.DEPT_TITLE 부서명
     , J.JOB_NAME 직급명
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE TO_CHAR(TO_DATE(SUBSTR(E.EMP_NO, 1, 2), 'RR')) >= '70' --작은따옴표 빠트리지 말자.
   AND TO_CHAR(TO_DATE(SUBSTR(E.EMP_NO, 1, 2), 'RR')) < '80'
   AND SUBSTR(E.EMP_NO, 8, 1) = 2
   AND E.EMP_NAME LIKE '전%';
-- 오라클 전용
SELECT
       E.EMP_NAME 사원명
     , E.EMP_NO 주민번호
     , D.DEPT_TITLE 부서명
     , J.JOB_NAME 직급명
  FROM EMPLOYEE E
     , DEPARTMENT D
     , JOB J
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND E.JOB_CODE = J.JOB_CODE
   AND TO_CHAR(TO_DATE(SUBSTR(E.EMP_NO, 1, 2), 'RR')) >= '70'
   AND TO_CHAR(TO_DATE(SUBSTR(E.EMP_NO, 1, 2), 'RR')) < '80'
   AND SUBSTR(E.EMP_NO, 8, 1) = 2
   AND E.EMP_NAME LIKE '전%';

 

JOIN 연습문제 3. ✅

-- 3. 가장 나이가 적은 직원의 사번, 사원명, 
--    나이, 부서명, 직급명을 조회하세요.
-- ANSI 표준
SELECT
       E.EMP_ID 사번
     , E.EMP_NAME 사원명
     , (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(E.EMP_NO, 1, 2), 'RRRR')))) + 1 나이
     , D.DEPT_TITLE 부서명
     , J.JOB_NAME 직급명
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(E.EMP_NO, 1, 2), 'RRRR')))) + 1 = (SELECT MIN((EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(EMP_NO, 1, 2), 'RRRR')))) + 1)
                                                                                                          FROM EMPLOYEE
                                                                                                          );
-- ORACLE 전용
SELECT
       E.EMP_ID 사번
     , E.EMP_NAME 사원명
     , (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM(TO_DATE(SUBSTR(EMP_NO, 1, 2), 'RRRR')))) + 1 나이
     , D.DEPT_TITLE 부서명
     , J.JOB_NAME 직급명
  FROM EMPLOYEE E
     , DEPARTMENT D
     , JOB J
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND E.JOB_CODE = J.JOB_CODE
   AND (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(E.EMP_NO, 1, 2), 'RRRR')))) + 1 = (SELECT MIN((EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(EMP_NO, 1, 2), 'RRRR')))) + 1)
                                                                                                          FROM EMPLOYEE
                                                                                                          );

 

JOIN 연습문제 4. ✅

-- 4. 부서코드가 D2인 직원들의 사원명, 
--    직급명, 부서명, 근무지역명을 조회하세요.
-- ANSI 표준
SELECT
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , D.DEPT_TITLE 부서명
     , L.LOCAL_NAME 근무지역명
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
 WHERE E.DEPT_CODE = 'D2';
-- 오라클 전용
SELECT
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , D.DEPT_TITLE 부서명
     , L.LOCAL_NAME 근무지역명
  FROM EMPLOYEE E
     , JOB J
     , DEPARTMENT D
     , LOCATION L
 WHERE E.JOB_CODE = J.JOB_CODE
   AND E.DEPT_CODE = D.DEPT_ID
   AND D.LOCATION_ID = L.LOCAL_CODE
   AND E.DEPT_CODE = 'D2';

 

JOIN 연습문제 5. ✅

코드를 간단히 하는 연습도 필요할 것 같다.

NVL(E.BONUS, 0) = 0 AND J.JOB_CODE IN('J4', 'J7')

-- 5. 보너스포인트가 없는 직원들 중에서 직급코드가 
--     J4와 J7인 직원들의 사원명, 직급명, 급여를 조회하세요.
-- ANSI 표준
SELECT
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , E.SALARY 급여
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE E.BONUS IS NULL
   AND (E.JOB_CODE = 'J4'
    OR E.JOB_CODE = 'J7');
-- 오라클 전용
SELECT
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , E.SALARY 급여
  FROM EMPLOYEE E
     , JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   AND E.BONUS IS NULL
   AND (E.JOB_CODE = 'J4'
    OR E.JOB_CODE = 'J7');

 

JOIN 연습문제 6. ✅

오라클 전용 구문에서는 하동운 출력되지 않는다. 답안에서 하동운 제외하고 8명만 대상으로 봤다. FULL JOIN → JOIN

-- 6. 보너스포인트를 받는 직원들의 사원명, 
--    보너스포인트, 부서명, 근무지역명을 조회하세요.
-- ANSI 표준
SELECT
       E.EMP_NAME 사원명
     , E.BONUS 보너스포인트
     , D.DEPT_TITLE 부서명
     , L.LOCAL_NAME 근무지역명
  FROM EMPLOYEE E
  FULL JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  FULL JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
 WHERE E.BONUS IS NOT NULL;
-- 오라클 전용
SELECT
       E.EMP_NAME 사원명
     , E.BONUS 보너스포인트
     , D.DEPT_TITLE 부서명 --하동운 출력 필요
     , L.LOCAL_NAME 근무지역명
  FROM EMPLOYEE E
     , DEPARTMENT D
     , LOCATION L
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND D.LOCATION_ID = L.LOCAL_CODE
   AND E.BONUS IS NOT NULL;

 

JOIN 연습문제 7. ✅

-- 7. 해외영업팀에 근무하는 사원명, 
--    직급명, 부서코드, 부서명을 조회하세요.
-- ANSI 표준
SELECT
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , E.DEPT_CODE 부서코드
     , D.DEPT_TITLE 부서명
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
 WHERE D.DEPT_TITLE LIKE '해외영업%';
-- 오라클 전용
SELECT
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , E.DEPT_CODE 부서코드
     , D.DEPT_TITLE 부서명
  FROM EMPLOYEE E
     , JOB J
     , DEPARTMENT D
 WHERE E.JOB_CODE = J.JOB_CODE
   AND E.DEPT_CODE = D.DEPT_ID
   AND D.DEPT_TITLE LIKE '해외영업%';

 

JOIN 연습문제 8. ❎✅

보너스 포함 연봉 계산식에 주의하자. (월급 + 보너스) * 12...

-- 8. 본인 급여 등급의 최소급여(MIN_SAL)를 초과하여 급여를 받는 직원들의
--    사원명, 직급명, 급여, 보너스포함 연봉을 조회하세요.
--    연봉에 보너스포인트를 적용한다.
-- ANSI 표준
SELECT 
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , E.SALARY 급여
     , (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 "보너스포함 연봉"
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
  JOIN SAL_GRADE S ON(SALARY BETWEEN MIN_SAL AND MAX_SAL)
 WHERE E.SALARY > S.MIN_SAL;
-- 오라클 전용
SELECT
       E.EMP_NAME 사원명
     , J.JOB_NAME 직급명
     , E.SALARY 급여
     , (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 "보너스포함 연봉"
  FROM EMPLOYEE E
     , JOB J
     , SAL_GRADE S
 WHERE E.JOB_CODE = J.JOB_CODE
   AND SALARY BETWEEN MIN_SAL AND MAX_SAL
   AND E.SALARY > S.MIN_SAL;

 

JOIN 연습문제 9. ✅

비교연산자 IN을 사용하면 조건식을 보다 간추릴 수 있다: N.NATIONAL_NAME IN('한국', '일본');

-- 9. 한국과 일본에 근무하는 직원들의 
--    사원명, 부서명, 지역명, 국가명을 조회하세요.
-- ANSI 표준
SELECT
       E.EMP_NAME 사원명
     , D.DEPT_TITLE 부서명
     , L.LOCAL_NAME 지역명
     , N.NATIONAL_NAME 국가명
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
  JOIN NATIONAL N ON(L.NATIONAL_CODE = N.NATIONAL_CODE)
 WHERE (N.NATIONAL_NAME = '한국'
    OR N.NATIONAL_NAME = '일본');
-- 오라클 전용
SELECT
       E.EMP_NAME 사원명
     , D.DEPT_TITLE 부서명
     , L.LOCAL_NAME 지역명
     , N.NATIONAL_NAME 국가명
  FROM EMPLOYEE E
     , DEPARTMENT D
     , LOCATION L
     , NATIONAL N
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND D.LOCATION_ID = L.LOCAL_CODE
   AND L.NATIONAL_CODE = N.NATIONAL_CODE
   AND (N.NATIONAL_NAME = '한국'
    OR N.NATIONAL_NAME = '일본');

 

JOIN 연습문제 10. ✅

답안에서는 아래처럼 썼다. 부서코드가 같은 대상으로 보고, 이름이 같지 않다는 조건을 걸어 본인은 목록에서 제외되도록 한다.

WHERE E1.DEPT_CODE = E2.DEPT_CODE
   AND E1.EMP_NAME != E2.EMP_NAME
 ORDER BY 1;

-- 10. 같은 부서에 근무하는 직원들의 사원명, 부서코드, 
--     동료이름을 조회하세요. self join 사용 (60행)
-- ANSI 표준
SELECT
       E1.EMP_NAME 사원명
     , E1.DEPT_CODE 부서코드
     , E2.EMP_NAME 동료이름
  FROM EMPLOYEE E1
  JOIN EMPLOYEE E2 ON(E1.EMP_ID <> E2.EMP_ID)
 WHERE E1.DEPT_CODE = E2.DEPT_CODE;
-- 오라클 전용
SELECT
       E1.EMP_NAME 사원명
     , E1.DEPT_CODE 부서코드
     , E2.EMP_NAME 동료이름
  FROM EMPLOYEE E1
     , EMPLOYEE E2
 WHERE E1.EMP_ID <> E2. EMP_ID
   AND E1.DEPT_CODE = E2.DEPT_CODE;