[Oracle]22.07.17 연습문제

--@실습문제_kh
--1. 2022년 12월 25일이 무슨 요일인지 조회하시오.

SELECT 
    TO_CHAR(TO_DATE('22/12/25'), 'DAY')
FROM DUAL;

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

SELECT
    EMP_NAME
    , EMP_NO
    , DEPT_TITLE
    , JOB_NAME  
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
JOIN JOB USING (JOB_CODE)
WHERE (SUBSTR(EMP_NO, 1, 2) BETWEEN 70 AND 79) 
    AND SUBSTR(EMP_NO, 8, 1)='2'
    AND SUBSTR(EMP_NAME,1,1)='전';

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

    EMP_ID
    , EMP_NAME
    , DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
WHERE EMP_NAME LIKE '%형%';

--5. 해외영업부에 근무하는 사원명, 직급명, 부서코드, 부서명을 조회하시오.
SELECT

    EMP_NAME
    , JOB_NAME
    , DEPT_CODE
    , DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN JOB USING(JOB_CODE)
WHERE DEPT_TITLE LIKE '%해외영업%';

--6. 보너스포인트를 받는 직원들의 사원명, 보너스포인트, 부서명, 근무지역명을 조회하시오.
SELECT

    EMP_NAME
    , BONUS
    , DEPT_TITLE
    , LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
WHERE BONUS IS NOT NULL;

--7. 부서코드가 D2인 직원들의 사원명, 직급명, 부서명, 근무지역명을 조회하시오.

SELECT
    EMP_NAME
    ,JOB_NAME
    ,DEPT_TITLE
    ,LOCAL_NAME
    ,DEPT_ID
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
WHERE DEPT_ID = 'D2';

--8. 급여등급테이블의 최대급여(MAX_SAL)보다 많이 받는 직원들의 사원명, 직급명, 급여, 연봉을 조회하시오.
-- (사원테이블과 급여등급테이블을 SAL_LEVEL컬럼기준으로 조인할 것)

SELECT
    EMP_NAME
    ,JOB_NAME
    ,SALARY
    ,SALARY*12+(SALARY*BONUS)
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
JOIN SAL_GRADE USING(SAL_LEVEL)
WHERE SALARY > MAX_SAL;

--9. 한국(KO)과 일본(JP)에 근무하는 직원들의 사원명, 부서명, 지역명, 국가명을 조회하시오.

SELECT 
    EMP_NAME
    , DEPT_TITLE
    , LOCAL_NAME
    , NATIONAL_CODE
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
JOIN LOCATION ON LOCATION_ID=LOCAL_CODE
WHERE NATIONAL_CODE IN('KO','JP');

--10. 보너스포인트가 없는 직원들 중에서 직급이 차장과 사원인 직원들의 사원명, 직급명, 급여를 조회하시오. 단, join과 IN 사용할 것

SELECT 
    EMP_NAME
    , JOB_NAME
    , SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE BONUS IS NULL AND JOB_NAME IN('차창','사원');

--11. 재직중인 직원과 퇴사한 직원의 수를 조회하시오.

--GROUP BY
SELECT
    DECODE(ENT_YN, 'N','재직','퇴사')"재직여부"
    , COUNT(*)
FROM EMPLOYEE
GROUP BY DECODE(ENT_YN, 'N','재직','퇴사');