----- 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;