7. 연습(1)
1. 1999 년의 월별 신입 사원수를 출력하시오.
SELECT MONTH(HIRE_DATE), COUNT(*)
FROM EMPLOYEES
WHERE YEAR(HIRE_DATE)=1999
GROUP BY MONTH(HIRE_DATE);
2. 월별 신입 사원수를 다음 그림과 같이 출력하시오.(월-행을 열로)
SELECT SUM(CASE WHEN MM=1 THEN CNT ELSE 0 END) M1
, SUM(CASE WHEN MM=2 THEN CNT ELSE 0 END) M2
, SUM(CASE WHEN MM=3 THEN CNT ELSE 0 END) M3
, SUM(CASE WHEN MM=4 THEN CNT ELSE 0 END) M4
, SUM(CASE WHEN MM=5 THEN CNT ELSE 0 END) M5
, SUM(CASE WHEN MM=6 THEN CNT ELSE 0 END) M6
, SUM(CASE WHEN MM=7 THEN CNT ELSE 0 END) M7
, SUM(CASE WHEN MM=8 THEN CNT ELSE 0 END) M8
, SUM(CASE WHEN MM=9 THEN CNT ELSE 0 END) M9
, SUM(CASE WHEN MM=10 THEN CNT ELSE 0 END) M10
, SUM(CASE WHEN MM=11 THEN CNT ELSE 0 END) M11
, SUM(CASE WHEN MM=12 THEN CNT ELSE 0 END) M12
FROM (
SELECT MONTH(HIRE_DATE) MM, COUNT(*) CNT
FROM EMPLOYEES
WHERE YEAR(HIRE_DATE)=1999
GROUP BY MONTH(HIRE_DATE)
) DS;
3. 다음 그림과 같이 1999년 신입 사원수과 전년(1998) 대비 월별 신입 사원수를 출력하시오.
SELECT YR
, SUM(CASE WHEN MM=1 THEN CNT ELSE 0 END) M1
, SUM(CASE WHEN MM=2 THEN CNT ELSE 0 END) M2
, SUM(CASE WHEN MM=3 THEN CNT ELSE 0 END) M3
, SUM(CASE WHEN MM=4 THEN CNT ELSE 0 END) M4
, SUM(CASE WHEN MM=5 THEN CNT ELSE 0 END) M5
, SUM(CASE WHEN MM=6 THEN CNT ELSE 0 END) M6
, SUM(CASE WHEN MM=7 THEN CNT ELSE 0 END) M7
, SUM(CASE WHEN MM=8 THEN CNT ELSE 0 END) M8
, SUM(CASE WHEN MM=9 THEN CNT ELSE 0 END) M9
, SUM(CASE WHEN MM=10 THEN CNT ELSE 0 END) M10
, SUM(CASE WHEN MM=11 THEN CNT ELSE 0 END) M11
, SUM(CASE WHEN MM=12 THEN CNT ELSE 0 END) M12
FROM (
SELECT YEAR(HIRE_DATE) YR, MONTH(HIRE_DATE) MM, COUNT(*) CNT
FROM EMPLOYEES
WHERE YEAR(HIRE_DATE) IN (1998, 1999)
GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
) DS
GROUP BY YR;
4. 3번 문제를 다음 그림과 같이 출력하시오.(년도를 열로 표시)
SELECT MM
, SUM(CASE WHEN YR=1998 THEN CNT ELSE 0 END) BEFOREYEAR
, SUM(CASE WHEN YR=1999 THEN CNT ELSE 0 END) THISYEAR
FROM (
SELECT YEAR(HIRE_DATE) YR, MONTH(HIRE_DATE) MM, COUNT(*) CNT
FROM EMPLOYEES
WHERE YEAR(HIRE_DATE) IN (1998, 1999)
GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
) DS
GROUP BY MM;
5. 급여를 다음과 같이 구간으로 만들어 직원수를 구하시오.
100,000달러 이상이면 1
80,000 달러 이상이면 2
65,000 달러 이상이면 3
45,000 달러 이상이면 4
45,000 달러 미만이면 5
SELECT CASE WHEN SALARY >= 100000 THEN 1
WHEN SALARY >= 80000 THEN 2
WHEN SALARY >= 65000 THEN 3
WHEN SALARY >= 45000 THEN 4
ELSE 5 END SALARYGRADE
, COUNT(*) CNT
FROM SALARIES WHERE TO_DATE='9999-01-01'
GROUP BY CASE WHEN SALARY >= 100000 THEN 1
WHEN SALARY >= 80000 THEN 2
WHEN SALARY >= 65000 THEN 3
WHEN SALARY >= 45000 THEN 4
ELSE 5 END;
SELECT SALARYGRADE, COUNT(*)
FROM (
SELECT CASE WHEN SALARY >= 100000 THEN 1
WHEN SALARY >= 80000 THEN 2
WHEN SALARY >= 65000 THEN 3
WHEN SALARY >= 45000 THEN 4
ELSE 5 END AS SALARYGRADE, EMP_NO
FROM SALARIES
WHERE TO_DATE='9999-01-01'
) DS
GROUP BY SALARYGRADE;
6. 연령대별(10대, 20대, 30대~~) 직원수를 구하시오.
SELECT CASE WHEN BIRTH>=60 THEN 60
WHEN BIRTH>=50 THEN 50
WHEN BIRTH>=40 THEN 40
WHEN BIRTH>=30 THEN 30
ELSE 20 END AGE
, SUM(CNT)
FROM (
SELECT TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) BIRTH, COUNT(*) CNT
FROM EMPLOYEES E, SALARIES S
WHERE S.EMP_NO=E.EMP_NO
AND TO_DATE='9999-01-01'
GROUP BY TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW())
) DS
GROUP BY CASE WHEN BIRTH>=60 THEN 60
WHEN BIRTH>=50 THEN 50
WHEN BIRTH>=40 THEN 40
WHEN BIRTH>=30 THEN 30
ELSE 20 END;
SELECT BIRTH DIV 10 * 10, SUM(CNT)
FROM (
SELECT TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) BIRTH, COUNT(*) CNT
FROM EMPLOYEES E, SALARIES S
WHERE S.EMP_NO=E.EMP_NO
AND TO_DATE='9999-01-01'
GROUP BY TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW())
) DS
GROUP BY BIRTH DIV 10;
SELECT TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) DIV 10 * 10 BIRTH, COUNT(*) CNT
FROM EMPLOYEES E, SALARIES S
WHERE S.EMP_NO=E.EMP_NO AND TO_DATE='9999-01-01'
GROUP BY TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) DIV 10
출처 : SW 개발이 좋은 사람
'Programming > SQL' 카테고리의 다른 글
| 9. 연습(3) (0) | 2017.03.11 |
|---|---|
| 8. 연습(2) (0) | 2017.03.11 |
| 6. 실습(4) - SubQuery (0) | 2017.02.28 |
| 5. 실습(3) - Join (0) | 2017.02.28 |
| 4. 실습(2) - Group (0) | 2017.02.25 |
