복습 문제
[문제24]
사원들의 급여를 기준으로 출력해주세요.
<풀이>
SELECT
salary,
CASE
WHEN salary < 5000 THEN
'low'
WHEN salary < 10000 THEN
'medium'
WHEN salary < 20000 THEN
'good'
ELSE
'excellent'
END AS grade
FROM
hr.employees
ORDER BY
1;[문제25]
사원들의 급여를 1000당 특수문자 하나를 출력해주세요.
단, 급여가 10000 이상이면 '*'을 출력하고 10000 미만이면 '^'로 출력해주세요.
<풀이>
SELECT
salary,
CASE
WHEN salary >= 10000 THEN
lpad('*', salary / 1000, '*')
ELSE
lpad('^', salary / 1000, '^')
END AS ans
FROM
hr.employees;<다른풀이>
SELECT
salary,
lpad(' ', trunc(salary / 1000) + 1,
CASE
WHEN salary >= 10000 THEN
'*'
ELSE
'^'
END
)
FROM
hr.employees;decode함수, case표현식에서 null 체크하는 방법
- decode 함수는 null keyword를 이용해서 null check
- case 표현식은 is null 연산자를 이용해서 null check
SELECT
salary,
commission_pct,
nvl2(commission_pct,(salary * 12) +(salary * 12 * commission_pct), salary * 12) AS annual_salary_1,
decode(commission_pct, NULL, salary * 12,(salary * 12) +(salary * 12 * commission_pct)) AS annual_salary_2,
CASE
WHEN commission_pct IS NULL THEN
salary * 12
ELSE
( salary * 12 ) + ( salary * 12 * commission_pct )
END AS annual_salary_3
FROM
hr.employees;그룹함수
- 여러행당 하나의 결과를 반환하는 함수
- sum(합), avg(평균), median(중앙값), variance(분산), stddev(표준편차), max(최대값), min(최소값), count(건수)
- 그룹함수에 입력하는 값이 숫자형만 입력해야 하는 그룹함수
- sum(합), avg(평균), median(중앙값), variance(분산), stddev(표준편차)- 그룹함수에 입력하는 값이 모든 타입이 가능한 그룹함수
- max(최대값), min(최소값), count(건수)- 그룹함수는 null을 포함하지 않습니다.
단, count(*)만 null을 포함한 행수를 구한다.
Count
- 행의 수를 구하는 함수
SELECT count(*) FROM hr.employees;
-- null 포함한 행의 수
SELECT count(commission_pct) FROM hr.employees;
-- null를 제외한 행의 수
SELECT count(distinct department_id) FROM hr.employees;
SELECT count(unique department_id) FROM hr.employees;
-- 중복을 제거한 행의 수Sum
- 합을 구하는 함수
SELECT sum(salary)
FROM hr.employees;
SELECT sum(salary)
FROM hr.employees
WHERE department_id = 50;Avg
- 평균을 구하는 함수
SELECT avg(commission_pct) -- null 제외한 평균
FROM hr.employees;
SELECT avg(nvl(commission_pct,0)) -- null 포함한 평균
FROM hr.employees;Median
- 중앙값을 구하는 함수
SELECT round(avg(salary)),median(salary)
FROM hr.employees;Variance
- 분산을 구하는 함수
SELECT avg(salary), variance(salary)
FROM hr.employees;stddev
- 표준편차를 구하는 함수
SELECT avg(salary), variance(salary),stddev(salary)
FROM hr.employees;Max, Min
- 최대값, 최소값을 구하는 함수
SELECT max(salary), min(salary), max(salary)-min(salary)
FROM hr.employees;GROUP BY 절
- 테이블의 행을 작은 그룹으로 나눌수 있는 절
- 9i R1 버전에서는 GROUP BY가 SORT GROUP BY로 수행했다. 그래서 정렬된 결과로 출력
- 9i R2 버전에서는 GROUP BY가 HASH GROUP BY로 수행했다. 그래서 정렬이 안된 결과로 출력
SELECT
department_id,
sum(salary),
COUNT(department_id)
FROM
hr.employees
GROUP BY
department_id;그룹함수 사용시 주의사항
- NULL을 포함하지 않습니다.
- SELECT절에 그룹함수에 포함되지 않은 개별 컬럼은 무조건 GROUP BY절에 명시해야한다.(오류발생)
- GROUP BY절에는 열별칭, 위치표기법 사용할 수 없음.
- WHERE 절에는 그룹함수의 결과를 제한할 수 없다.(오류발생)
- 그룹함수를 두 번 중첩했을 경우 개별 컬럼이 있으면 안된다.(오류발생)
HAVING 절
- 그룹함수의 결과를 제한하는 절
SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id
HAVING sum(salary) >= 10000;[문제26]
2008년도에 입사한 사원들의 job_id별 인원수를 구하고 인원수가 많은 순으로 출력하세요.
<풀이>
SELECT
job_id,
COUNT(job_id) AS cnt
FROM
hr.employees
WHERE
to_char(hire_date, 'yyyy') = '2008'
GROUP BY
job_id
ORDER BY
cnt DESC;[문제27]
년도별 입사한 인원수를 출력해주세요.
<풀이>
SELECT
to_char(hire_date, 'yyyy') AS year,
COUNT(*) AS hire_cnt
FROM
hr.employees
GROUP BY
to_char(hire_date, 'yyyy')
ORDER BY
1;[문제28]
월별 입사한 인원수 출력해주세요.
<풀이>
SELECT
to_number(to_char(hire_date, 'mm')) AS month,
COUNT(*) AS hire_cnt
FROM
hr.employees
GROUP BY
to_char(hire_date, 'mm')
ORDER BY
1;[문제29]
년도별 입사 인원수를 아래 화면과 같이 출력해주세요.
| TOTAL | 2001년 | 2002년 | 2003년 |
|---|---|---|---|
| 107 | 1 | 7 | 6 |
<풀이>
SELECT
count(*) as TOTAL,
sum(case when to_char(hire_date,'yyyy') = '2001' then 1 end) as "2001년",
sum(case when to_char(hire_date,'yyyy') = '2002' then 1 end) as "2002년",
sum(case when to_char(hire_date,'yyyy') = '2003' then 1 end) as "2003년"
FROM
hr.employees;<다른풀이>
SELECT
count(*) as TOTAL,
count(decode(to_char(hire_date,'yyyy') ,'2001' ,1)) as "2001년",
count(decode(to_char(hire_date,'yyyy') ,'2002' ,1)) as "2002년",
count(decode(to_char(hire_date,'yyyy') ,'2003' ,1)) as "2003년"
FROM
hr.employees;[5일차 후기]
"벌써 금요일 이라니!" 라는 생각이 들도록 만든 한주였고, 교육 시작한지 벌써 일주일이 지났다는게 안믿길 정도로 시간이 너무 빠르게 지나갔다. 오늘 수업에서 또 한번 디테일에서 놀랐던 부분은 그동안 GROUP BY절은 그저 군집화를 시켜준다는 개념으로 넘겨짚고 있었는데, 그 안에서는 군집화를 시키기 위한 알고리즘이 돌아가고 있고 버전에 따라서도 SORT알고리즘, HASH알고리즘 다르게 적용된다는 것을 알게되었다. 솔직히 GROUP BY절은 처음 듣는 사람 입장에서는 한번에 이해하기 어려운 부분인 것 같다. 그래도 나름 독학을 한 부분이 있기 때문에 아직 까지는 잘 따라 가고 있다고 느껴지는데 다음주에는 JOIN절과 나도 정확히는 모를 수 있는 서브쿼리를 배운다고 하니 벌써 기대된다. 역시 사람은 자기가 좋아하는걸 해야한다고 요즘 나름 인생에서 의미있는 시간을 보내고 있는 것 같다. 돈만 많으면 일 안하고 좋아하는것만 하고 다닐텐데..
