8. 연습(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
출처 : SW 개발이 좋은 사람
'Programming > SQL' 카테고리의 다른 글
| 대용량 테이블 삭제 방법 (0) | 2017.05.02 |
|---|---|
| 9. 연습(3) (0) | 2017.03.11 |
| 7. 연습(1) (0) | 2017.03.11 |
| 6. 실습(4) - SubQuery (0) | 2017.02.28 |
| 5. 실습(3) - Join (0) | 2017.02.28 |
