연습 2

1. 부서별 부서장 정보를 출력하시오.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME, SALARY

  FROM DEPT_MANAGER DM

  INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DM.DEPT_NO

  INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DM.EMP_NO

  INNER JOIN SALARIES SAL ON SAL.EMP_NO=DM.EMP_NO

WHERE DM.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

ORDER BY DEPT_NAME;

2. 부서별 정보(부서장, 부서별 급여 평균)을 출력하시오.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME, AVG_SALARY

  FROM DEPT_MANAGER DM

  INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DM.DEPT_NO

  INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DM.EMP_NO

  INNER JOIN (
            SELECT DEPT_NO, AVG(SALARY) AVG_SALARY

              FROM DEPT_EMP DE

              INNER JOIN SALARIES SAL ON SAL.EMP_NO=DE.EMP_NO

             WHERE DE.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

             GROUP BY DEPT_NO

) DS ON DS.DEPT_NO=DM.DEPT_NO

 WHERE DM.TO_DATE='9999-01-01' 

 ORDER BY DEPT_NAME

3. 부서별 직원 리스트를 출력하되 부서장이면 표시를 하고,

   각 부서에서 가장 먼저 나오게 출력하시오.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME, IF(DM.EMP_NO IS NULL, NULL, 'MANAGER') POSITION

  FROM DEPT_EMP DE

  INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DE.DEPT_NO

  INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DE.EMP_NO

    LEFT OUTER JOIN DEPT_MANAGER DM ON DM.EMP_NO=DE.EMP_NO AND DM.TO_DATE='9999-01-01'

 WHERE DE.TO_DATE='9999-01-01' 

 ORDER BY DEPT_NAME, DM.EMP_NO DESC, FIRST_NAME, LAST_NAME

4. 부서별 직원 리스트와 급여를 출력하되,

   부서장이면 표시를 하고 직원들의 급여 평균을 표시하시오.

  이 문제는 실적 계산에 많이 사용하는 SQL문으로

  급여가 아닌 실적으로 하는 것이 적절하지만 데이터가 없어서

  급여를 실적처럼 사용해서 문제를 풀어본다.

  즉, 개인은 개인의 실적, 팀장은 팀원 실적의 평균으로 계산.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME

        , IF(DM.EMP_NO IS NULL, SAL.SALARY, DS.AVG_SALARY) AVG_SALARY

        , IF(DM.EMP_NO IS NULL, NULL, 'MANAGER') POSITION

  FROM DEPT_EMP DE

INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DE.DEPT_NO

INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DE.EMP_NO

INNER JOIN SALARIES SAL ON SAL.EMP_NO=DE.EMP_NO

   LEFT OUTER JOIN DEPT_MANAGER DM ON DM.EMP_NO=DE.EMP_NO AND DM.TO_DATE='9999-01-01'

   LEFT OUTER JOIN (

            SELECT DEPT_NO, AVG(SALARY) AVG_SALARY

              FROM DEPT_EMP DE

             INNER JOIN SALARIES SAL ON SAL.EMP_NO=DE.EMP_NO

             WHERE DE.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

             GROUP BY DEPT_NO

) DS ON DS.DEPT_NO=DE.DEPT_NO

WHERE DE.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

ORDER BY DEPT_NAME, DM.EMP_NO DESC, FIRST_NAME, LAST_NAME