Conditional Expression

1. CASE

  • ANSI 표준 (모든 DB에서 사용 가능)
SELECT ename, job, sal, CASE job WHEN 'CLERK' THEN 1.10*sal
WHEN 'MANAGER' THEN 1.15*sal
WHEN 'PRESIDENT' THEN 1.20*sal
ELSE sal END REVISED_SALARY
FROM emp;

2. DECODE

  • ORACLE 전용 함수
SELECT ename, job, sal, DECODE(job, 'CLERK', 1.10*sal,
'MANAGER', 1.15*sal,
'PRESIDENT', 1.20*sal,
sal) REVISED_SALARY
FROM emp;

SELECT DEPTNO,	sum(DECODE(DEPTNO,10, sal, 0)) DEPTNO10,
				sum(DECODE(DEPTNO, 20, sal, 0)) DEPTNO20,
				sum(DECODE(DEPTNO, 30, sal, 0)) DEPTNO30,
				sum(DECODE(DEPTNO, 40, sal, 0)) DEPTNO40
FROM emp
GROUP BY DEPTNO
ORDER BY DEPTNO;

Aggregation Function

  • 집계 함수

1. SUM

  • sum(c) : c 컬럼의 모든 값을 더함
SELECT sum(sal)
FROM emp;

2. AVG

  • avg(c): c 컬럼의 평균
SELECT avg(sal)
FROM emp;

3. COUNT

  • count(c): c 컬럼의 개수
SELECT count(empno)
FROM emp;

SELECT count(*)
FROM emp;

4. MAX

  • max(c): c 컬럼 값 중 최대 값을 반환
SELECT max(sal)
FROM emp;

5. MIN

  • min(c): c 컬럼 값 중 최소 값을 반환
SELECT min(sal)
FROM emp;

6. STDDEV

  • stddey(c): c 컬럼의 표준편차를 반환
SELECT stddey(sal)
FROM emp;