ORACLE SQL) JOIN 연습문제

1. 70년대 생(1970~1979) 중 여자이면서 전씨인 사원의 이름과 주민번호, 부서 명, 직급 조회  

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) IN('2','4')
        AND (EMP_NAME LIKE '전%');

2. 이름에 ‘형’이 들어가는 사원의 사원 코드, 사원 명, 직급 조회  

SELECT EMP_ID, EMP_NAME, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE EMP_NAME LIKE '%형%';

3. 부서코드가 D5이거나 D6인 사원의 사원 명, 직급 명, 부서 코드, 부서 명 조회  

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_CODE IN ('D5', 'D6');

4. 보너스를 받는 사원의 사원 명, 부서 명, 지역 명 조회  

SELECT EMP_NAME, DEPT_TITLE, LOCAL_NAME, BONUS*100, '%'
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN LOCATION ON(LOCAL_CODE = LOCATION_ID)
WHERE BONUS IS NOT NULL;

5. 사원 명, 직급 명, 부서 명, 지역 명 조회  

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

6. 한국이나 일본에서 근무 중인 사원의 사원 명, 부서 명, 지역 명, 국가 명 조회  

SELECT EMP_NAME, DEPT_TITLE, LOCAL_NAME, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN LOCATION ON(LOCAL_CODE = LOCATION_ID)
JOIN NATIONAL USING(NATIONAL_CODE)
WHERE NATIONAL_NAME IN('한국', '일본');

 7. 한명의 사원과 같은 부서에서 일하는 사원의 이름 조회 (자체조인 활용)  
-  같은 부서에서 일하는 다른 사원의 이름을 조회하시오.  
-  단, 모든 사원이 자신의 부서에서 함께 일하는 다른 사원의 이름을 확인할 수 있어야 합니다.  
-  (예: 사원 A가 속한 부서에서 다른 사원 B의 이름을 조회) 

SELECT A.EMP_NAME, B.EMP_NAME 
FROM EMPLOYEE A
JOIN EMPLOYEE B ON(A.DEPT_CODE = B.DEPT_CODE)
WHERE A.EMP_ID != B.EMP_ID
ORDER BY A.EMP_ID;

8. 보너스가 없고 직급 코드가 J4이거나 J7인 사원의 이름, 직급 명, 급여 조회 (NVL 이용) 

SELECT EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
LEFT JOIN JOB USING(JOB_CODE)
WHERE NVL(BONUS, 0) = 0 
        AND JOB_CODE IN('J4','J7');

9. 부서 명과 부서 별 급여 합계 조회  

SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE;
-- GROUP BY DEPT_CODE, DEPT_TITLE;

10. 부서 별 급여 합계가 전체 급여 총 합의 20%보다 많은 부서의 부서 명, 부서 별 급여 합계 조회 
-전체 급여 총 합의 20%를 계산해서 구하는 방식

SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) >= 14019248; --SELECT SUM(SALARY)*0.2 FROM EMPLOYEE;  = 14019248

 >> 서브 쿼리 방식

SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) >= (SELECT SUM(SALARY)*0.2 FROM EMPLOYEE);

11. 나이 상 가장 막내의 사원 코드, 사원 명, 나이, 부서 명, 직급 명 조회

SELECT EMP_ID, EMP_NAME,
       FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12) AS "나이",
       DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE)
WHERE TO_NUMBER(SUBSTR(EMP_NO, 3, 2)) BETWEEN 1 AND 12   -- 월이 1~12
  AND TO_NUMBER(SUBSTR(EMP_NO, 5, 2)) BETWEEN 1 AND 31   -- 일이 1~31
  AND FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12) = (
      SELECT MIN(FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12))
      FROM EMPLOYEE
      WHERE TO_NUMBER(SUBSTR(EMP_NO, 3, 2)) BETWEEN 1 AND 12
        AND TO_NUMBER(SUBSTR(EMP_NO, 5, 2)) BETWEEN 1 AND 31
  );