CASE
SELECT EMPLOYEE_ID,
SALARY,
JOB_ID,
CASE JOB_ID
WHEN ‘AC_ACCOUNT’ THEN SALARY + SALARY * 0.5
WHEN ‘AC_MGR’ THEN SALARY + SALARY * 0.3
WHEN ‘AD_VP’ THEN SALARY + SALARY * 0.2
ELSE SALARY
END AS SALARY
FROM EMPLOYEES;
DECODE
SELECT EMPLOYEE_ID,
SALARY,
JOB_ID,
DECODE( JOB_ID ,
‘AC_ACCOUNT’ , SALARY + SALARY * 0.5 ,
‘AC_MGR’ ,SALARY + SALARY * 0.3 ,
‘AD_VP’ , SALARY + SALARY * 0.2 ,
SALARY) AS “이번달 월급”
FROM EMPLOYEES;
SELECT EMPLOYEE_ID,AVG(SALARY) , MAX(SALARY)
FROM EMPLOYEES;
– ERROR
– 집계함수 이외의 칼럼은 프로젝션 될 수 없다
GROUP BY
SELECT JOB_ID,AVG(SALARY), MAX(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
GROUP BY JOB_ID;
– GROUP BY 절에 참여하고 있는 컬럼은 프로젝션 할 수 있다.