실습 문제/oracle
[Oracle 실습문제] JOIN, 다중JOIN, 함수, 오라클 구문, ANSI구문
좨랭이
2021. 10. 4. 23:19
**문제풀이 참고 (연결된 컬럼)
SELECT * FROM EMPLOYEE; -- DEPT_CODE JOB_CODE
SELECT * FROM DEPARTMENT; -- DEPT_ID LOCATION_ID
SELECT * FROM LOCATION; -- LOCAL_CODE NATIONAL_CODE
SELECT * FROM NATIONAL; -- NATIONAL_CODE
SELECT * FROM JOB; -- JOB_CODE
<문제 1>
-- 1. 직급이 대리이면서 ASIA 지역에서 근무하는 직원들의 사번, 사원명, 직급명, 부서명, 근무지역, 급여를 조회하세요.
-- ANSI 구문
SELECT E.EMP_ID,
E.EMP_NAME,
J.JOB_NAME,
D.DEPT_TITLE,
L.LOCAL_NAME,
E.SALARY
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 JOB_NAME = '대리' AND LOCAL_NAME LIKE 'ASIA%';
-- 오라클 구문
SELECT E.EMP_ID,
E.EMP_NAME,
J.JOB_NAME,
D.DEPT_TITLE,
L.LOCAL_NAME,
E.SALARY
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 JOB_NAME = '대리' AND LOCAL_NAME LIKE 'ASIA%';
<문제 2>
-- 2. 70년대생 이면서 여자이고, 성이 전 씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하세요.
-- ANSI 구문
SELECT E.EMP_NAME AS "사원명",
E.EMP_NO AS "주민번호",
D.DEPT_TITLE AS "부서명",
J.JOB_NAME AS "직급명"
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
--WHERE SUBSTR(EMP_NO,1,1) = '7'
WHERE E.EMP_NO LIKE '7%'
AND SUBSTR(EMP_NO,8,1) = '2'
AND EMP_NAME LIKE '전%';
-- 오라클 구문
SELECT E.EMP_NAME AS "사원명",
E.EMP_NO AS "주민번호",
D.DEPT_TITLE AS "부서명",
J.JOB_NAME AS "직급명"
FROM EMPLOYEE E, DEPARTMENT D, JOB J
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
AND SUBSTR(EMP_NO,1,1) = '7'
AND SUBSTR(EMP_NO,8,1) = '2'
AND EMP_NAME LIKE '전%';- 조건 중 70년대생 작성 시 아래와 같이 두 가지로 작성이 가능하다.
WHERE SUBSTR(EMP_NO,1,1) = '7' / E.EMP_NO LIKE '7%'
-> 결과는 똑같이 나오지만 성능차이가 있을 수 있음 => 성능확인 방법은 나중에 ...!
<문제 3 **>
-- 3. 보너스를 받는 직원들의 사원명, 보너스, 연봉, 부서명, 근무지역을 조회하세요.
-- 단, 부서 코드가 없는 사원도 출력될 수 있게 Outer JOIN 사용
-- ANSI 구문
SELECT E.EMP_NAME AS "사원명",
NVL(E.BONUS,0) AS "보너스",
TO_CHAR(E.SALARY * 12, '99,999,999') AS "연봉",
D.DEPT_TITLE AS "부서명",
L.LOCAL_NAME AS "근무지역"
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN LOCATION L ON (D.LOCATION_ID = LOCAL_CODE)
WHERE BONUS IS NOT NULL;
-- 오라클 구문
SELECT E.EMP_NAME AS "사원명",
NVL(E.BONUS,0) AS "보너스",
TO_CHAR(E.SALARY * 12, '99,999,999') AS "연봉",
D.DEPT_TITLE AS "부서명",
L.LOCAL_NAME AS "근무지역"
FROM EMPLOYEE E, DEPARTMENT D, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID(+)
AND D.LOCATION_ID = LOCAL_CODE(+)
AND BONUS IS NOT NULL;- 부서코드가 없는 사원도 출력을 하기 위해 EMPLOYEE테이블과 DEPARTMENT테이블 LEFT JOIN
-> DEPARTMENT테이블과 LOCATION테이블을 JOIN하면 부서코드가 없는 사원에 대한 값이 없어짐
-> DEPARTMENT테이블과 LOCATION테이블을 LEFT JOIN
~~ 여러 테이블을 조인하는 경우 조심할 것 ~~
<문제 4>
-- 4. 한국과 일본에서 근무하는 직원들의 사원명, 부서명, 근무지역, 근무 국가를 조회하세요.
-- 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 IN ('한국', '일본');
--WHERE N.NATIONAL_NAME = '한국' OR 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 IN ('한국', '일본');
--AND NATIONAL_NAME = '한국' OR NATIONAL_NAME = '일본';
<문제 5>
-- 5. 각 부서별 평균 급여를 조회하여 부서명, 평균 급여(정수 처리)를 조회하세요.
-- 단, 부서 배치가 안된 사원들의 평균도 같이 나오게끔 해주세요^^
-- ANSI 구문
SELECT NVL(D.DEPT_TITLE, '부서없음') AS "부서명",
TO_CHAR(FLOOR(AVG(E.SALARY)),'99,999,999') AS "급여 평균"
FROM DEPARTMENT D
RIGHT JOIN EMPLOYEE E ON (D.DEPT_ID = E.DEPT_CODE)
GROUP BY D.DEPT_TITLE
ORDER BY D.DEPT_TITLE;
-- 오라클 구문
SELECT NVL(D.DEPT_TITLE, '부서없음') AS "부서명",
TO_CHAR(FLOOR(AVG(E.SALARY)),'9,999,999') AS "평균 급여"
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DEPT_ID(+) = E.DEPT_CODE
GROUP BY D.DEPT_TITLE
ORDER BY D.DEPT_TITLE;
<문제 6>
-- 6. 각 부서별 총 급여의 합이 1000만원 이상인 부서명, 급여의 합을 조회하시오.
-- ANSI 구문
SELECT D.DEPT_TITLE AS "부서명",
TO_CHAR(SUM(E.SALARY),'999,999,999') AS "급여의 합"
FROM DEPARTMENT D
JOIN EMPLOYEE E ON (D.DEPT_ID = E.DEPT_CODE)
GROUP BY D.DEPT_TITLE
HAVING SUM(E.SALARY) >= 10000000
ORDER BY D.DEPT_TITLE;
-- 오라클 구문
SELECT D.DEPT_TITLE AS "부서명",
TO_CHAR(SUM(E.SALARY),'999,999,999') AS "급여의 합"
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DEPT_ID = E.DEPT_CODE
GROUP BY D.DEPT_TITLE
HAVING SUM(E.SALARY) >= 10000000
ORDER BY D.DEPT_TITLE;
<문제 7>
-- 7. 사번, 사원명, 직급명, 급여 등급, 구분을 조회 (NON EQUAL JOIN 후에 실습 진행)
-- 이때 구분에 해당하는 값은 아래와 같이 조회 되도록 하시오.
-- 급여 등급이 S1, S2인 경우 '고급'
-- 급여 등급이 S3, S4인 경우 '중급'
-- 급여 등급이 S5, S6인 경우 '초급'
-- ANSI 구문
SELECT E.EMP_ID AS "사번",
E.EMP_NAME AS "사원명",
J.JOB_NAME AS "직급명",
S.SAL_LEVEL AS "급여등급",
CASE
WHEN S.SAL_LEVEL IN ('S1', 'S2') THEN '고급'
WHEN S.SAL_LEVEL IN ('S3', 'S4') THEN '중급'
WHEN S.SAL_LEVEL IN ('S5', 'S6') THEN '초급'
END AS "구분"
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
JOIN SAL_GRADE S ON (E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL);
-- 오라클 구문
SELECT E.EMP_ID AS "사번",
E.EMP_NAME AS "사원명",
J.JOB_NAME AS "직급명",
S.SAL_LEVEL AS "급여등급",
CASE
WHEN S.SAL_LEVEL IN ('S1', 'S2') THEN '고급'
WHEN S.SAL_LEVEL IN ('S3', 'S4') THEN '중급'
WHEN S.SAL_LEVEL IN ('S5', 'S6') THEN '초급'
END AS "구분"
FROM EMPLOYEE E, JOB J, SAL_GRADE S
WHERE E.JOB_CODE = J.JOB_CODE AND E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL;- CASE
비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환(조건은 범위 값 가능)
CASE WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
…
ELSE 결과N
END AS "별칭"