23.10.12. Class 복습 겸 문제

[문제1] 2008년도에 입사한 사원들의 job_id별 인원수를 구하고 인원수가 많은 순으로 출력하세요 

hint) count, group by 절, having절 vs where절, to_date()

(방법1)

SELECT job_id, count(*)
FROM hr.employees
GROUP BY job_id
HAVING hire_date >= to_date('2008/01/01', 'yyyy/mm/dd') 
AND hire_date <= to_date('2008/12/31', 'yyyy/mm/dd')
ORDER BY count(*) desc;

 

(방법2)

SELECT job_id, count(*)
FROM hr.employees
WHERE hire_date BETWEEN to_date('2008/01/01', 'yyyy/mm/dd') 
AND to_date('2008/12/31', 'yyyy/mm/dd')
GROUP BY job_id
ORDER BY 2 desc;

같은 조건절이기는 하나,  HAVING 절과 WHERE절은 그룹화 또는 집계함수가 되기전에 쓰이는지 아닌지에 따라 쓰임이 다르다.

 

[문제2] 년도별 입사한 인원수를 출력하세요

hint) count, group by 절

SELECT to_char(hire_date, 'yyyy"년"') "년도별", count(*) 
FROM hr.employees 
GROUP BY to_char(hire_date, 'yyyy"년"');

[문제3] 월별 입사한 인원수를 출력하세요

hint) to_char(), count, 'fm'쓰임에 따라 order by절 사용 여부

(방법1)

SELECT to_char(hire_date, 'mm"월"') "월별", count(*)
FROM hr.employees
GROUP BY to_char(hire_date, 'mm"월"');

(방법2)

1. ORDER BY 절을 이용해서 정렬

2. 'fm' 을 쓸 때에는 잘보고 써야 한다.

SELECT to_char(hire_date, 'fmmm') "월별", count(*)
FROM hr.employees
GROUP BY to_char(hire_date, 'fmmm')
ORDER BY 1;

(방법3)

SELECT to_char(hire_date, 'mm') "월별", count(*)
FROM hr.employees
GROUP BY to_char(hire_date, 'mm')
ORDER BY 1;

[문제4] 년도별 입사 인원수를 아래 화면과 같이 출력하세요

hint) count, decode, case when-then end, count

/* NULL 값을 포함한 count */
select count(*)
from hr.employees 
where to_char(hire_date, 'yyyy') = 2001;

/* NULL 값을 포함하지 않는 count */
select count(hire_date)
from hr.employees
where to_char(hire_date, 'yyyy') = 2001;

ⓐ
SELECT COUNT(*) TOTAL
FROM hr.employees;

ⓑ
SELECT to_char(hire_date, 'yyyy')
FROM hr.employees;

ⓒ
SELECT 
    COUNT( DECODE( to_char(hire_date, 'yyyy'), '2001' , 'X') )
FROM hr.employees;

ⓓ
SELECT 
    COUNT( DECODE( to_char(hire_date, 'yyyy'), '2001' , 'X') ) AS "2001년"
FROM hr.employees;

ⓔ
SELECT 
    SUM( DECODE( to_char(hire_date, 'yyyy'), '2002' , 1) ) AS "2002년"
FROM hr.employees;

 

(방법1)

DECODE 사용

SELECT 
    COUNT(*) TOTAL , 
    COUNT( DECODE( to_char(hire_date, 'yyyy'), '2001' , 'X') ) AS "2001년" ,
    COUNT( DECODE( to_char(hire_date, 'yyyy'), '2002' , 'X') ) AS "2002년" ,
    COUNT( DECODE( to_char(hire_date, 'yyyy'), '2003' , 'X') ) AS "2003년"
FROM hr.employees;

(방법2)

CASE WHEN-THEN END 사용

ⓕ-2
SELECT
    COUNT(*) TOTAL ,
    COUNT(( CASE WHEN(to_char(hire_date, 'yyyy')) = '2001' THEN 'x' END )) AS "2001년" ,
    COUNT(( CASE WHEN(to_char(hire_date, 'yyyy')) = '2002' THEN 'x' END )) AS "2002년" ,
    COUNT(( CASE WHEN(to_char(hire_date, 'yyyy')) = '2003' THEN 'x' END )) AS "2003년"
FROM hr.employees;