SQL - DQL(SELECT) 4

JOIN을 활용하여 실습 문제를 풀어보자.

---------------- 실습 문제 ----------------

• 1. DEPARTMENT 테이블과 LOCATION 테이블의 조인하여 부서 코드, 부서명, 지역 코드, 지역명을 조회
     SELECT D.DEPT_ID, D.DEPT_TITLE, D.LOCATION_ID, L.LOCAL_NAME
     FROM DEPARTMENT D JOIN LOCATION L
     ON D.LOCATION_ID = L.LOCAL_CODE;

• 2. EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인해서 보너스를 받는 사원들의 사번, 사원명, 보너스, 부서명을 조회
     SELECT E.EMP_ID, E.EMP_NAME, E.BONUS, D.DEPT_ID
     FROM EMPLOYEE E JOIN DEPARTMENT D
     ON E.DEPT_CODE = D.DEPT_ID
     WHERE E.BONUS IS NOT NULL;

• 3. EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인해서 인사관리부가 아닌 사원들의 사원명, 부서명, 급여를 조회
     SELECT E.EMP_NAME, D.DEPT_TITLE, E.SALARY
     FROM EMPLOYEE E JOIN DEPARTMENT D
     ON E.DEPT_CODE = D.DEPT_ID
     WHERE D.DEPT_TITLE != '인사관리부';

• 4. EMPLOYEE 테이블, DEPARTMENT 테이블, LOCATION 테이블의 조인해서 사번, 사원명, 부서명, 지역명 조회
     SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME
     FROM EMPLOYEE E JOIN DEPARTMENT D
     ON E.DEPT_CODE = D.DEPT_ID
     JOIN LOCATION L ON D.LOCATION_ID = L.LOCAL_CODE;

• 5. 사번, 사원명, 부서명, 지역명, 국가명 조회
     SELECT E.EMP_ID, 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;

• 6. 사번, 사원명, 부서명, 지역명, 국가명, 급여 등급 조회
     SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME, N.NATIONAL_NAME, S.SAL_LEVEL
     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
     JOIN SAL_GRADE S ON E.SALARY >= S.MIN_SAL and E.SALARY < S.MAX_SAL;

• 1. 직급이 대리이면서 ASIA 지역에서 근무하는 직원들의 사번, 사원명, 직급명, 부서명, 근무지역, 급여를 조회하세요.
     SELECT E.EMP_ID, E.EMP_NAME,J.JOB_NAME ,D.DEPT_TITLE, L.LOCAL_NAME, N.NATIONAL_NAME, E.SALARY
     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
     JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
     WHERE E.JOB_CODE = 'J6';

• 2. 70년대생 이면서 여자이고, 성이 전 씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하세요.
     SELECT E.EMP_NAME, J.JOB_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 E.EMP_NAME LIKE '전%' AND SUBSTR(E.EMP_NO, 8,1) = '2' AND SUBSTR(E.EMP_NO, 1,2) LIKE '7%';

• 3. 보너스를 받는 직원들의 사원명, 보너스, 연봉, 부서명, 근무지역을 조회하세요.
     SELECT E.EMP_NAME,E.BONUS,E.SALARY*12, D.DEPT_TITLE, L.LOCAL_NAME
     FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
     JOIN LOCATION L ON D.LOCATION_ID = L.LOCAL_CODE
     WHERE E.BONUS IS NOT NULL;

• 4. 한국과 일본에서 근무하는 직원들의 사원명, 부서명, 근무지역, 근무 국가를 조회하세요.
     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_CODE = 'KO' OR N.NATIONAL_CODE = 'JP';

• 5. 각 부서별 평균 급여를 조회하여 부서명, 평균 급여(정수 처리)를 조회하세요.
     SELECT D.DEPT_TITLE, ROUND(AVG(E.SALARY))
     FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
     GROUP BY DEPT_TITLE;

• 6. 각 부서별 총 급여의 합이 1000만원 이상인 부서명, 급여의 합을 조회하시오.
     SELECT D.DEPT_TITLE, ROUND(SUM(E.SALARY))
     FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
     GROUP BY DEPT_TITLE
     HAVING SUM(E.SALARY) > 10000000;

• 7. 사번, 사원명, 직급명, 급여 등급, 구분을 조회
     -- 이때 구분에 해당하는 값은 아래와 같이 조회 되도록 하시오.
     -- 급여 등급이 S1, S2인 경우 '고급'
     -- 급여 등급이 S3, S4인 경우 '중급'
     -- 급여 등급이 S5, S6인 경우 '초급'
     SELECT
     E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE ,J.JOB_NAME, S.SAL_LEVEL ,
     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 DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
     JOIN SAL_GRADE S ON E.SALARY >= S.MIN_SAL and E.SALARY < S.MAX_SAL
     JOIN JOB J ON E.JOB_CODE = J.JOB_CODE;

• 8. 보너스를 받지 않는 직원들 중 직급 코드가 J4 또는 J7인 직원들의 사원명, 직급명, 급여를 조회하시오.
     SELECT EMP_NAME, JOB_NAME, SALARY
     FROM EMPLOYEE E
     JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
     WHERE E.JOB_CODE = 'J4' OR E.JOB_CODE = 'J7' AND E.BONUS IS NULL;

• 9. 부서가 있는 직원들의 사원명, 직급명, 부서명, 근무 지역을 조회하시오.
     SELECT E.EMP_NAME, J.JOB_NAME, D.DEPT_TITLE, L.LOCAL_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 JOB J ON E.JOB_CODE = J.JOB_CODE
     WHERE E.DEPT_CODE IS NOT NULL;

• 10. 해외영업팀에 근무하는 직원들의 사원명, 직급명, 부서 코드, 부서명을 조회하시오
      SELECT E.EMP_NAME,J.JOB_NAME, E.DEPT_CODE, D.DEPT_TITLE
      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
      JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
      WHERE D.DEPT_ID IN ('D5','D6','D7');

• 11. 이름에 '형'자가 들어있는 직원들의 사번, 사원명, 직급명을 조회하시오.
      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 ('%형') OR E.EMP_NAME LIKE ('%형%') OR E.EMP_NAME LIKE ('형%');