오라클 함수(funtion)

1. 단일 행 함수:

행(row)이 하나씩 함수의 argument로 전달되고, 행 마다 하나씩 결과가 리턴되는 함수.

단일 행 함수의 예 - 문자열을 소문자로 바꾸기.

SELECT ename, LOWER(ename) FROM emp;

to_char(): 날짜 타입 데이터를 원하는 문자열 포맷으로 출력.

SELECT hiredate, TO_CHAR(hiredate, 'YYYY-MM-DD') FROM emp;

2. 다중 행 함수(그룹 함수):

여러 개의 행이 함수의 argument로 전달되고, 하나의 결과가 리턴되는 함수.
(예) 통계 관련 예: count, sum, avg, max, min, variance(분산), stddev(표준 편차), ...
(주의) 단일 행 함수와 그룹 함수는 동시에 select에서 사용할 수 없음!

null value

nvl(컬럼, val): 컬럼이 null이면 val을 리턴하고, null이 아니면 원래 값을 리턴.
nvl2(컬럼, val1, val2): 컬럼이 null이 아니면 val1을 리턴, null이면 val2를 리턴.

SELECT comm, nvl(comm, -1), nvl2(comm, comm, -1) FROM emp;

다중 행 함수

count(컬럼): null이 아닌 행의 개수를 리턴.

SELECT COUNT(empno), COUNT(mgr), COUNT(comm) FROM emp;

테이블의 행(row)의 개수를 리턴.

SELECT COUNT(*) FROM emp;

통계 함수: 합계, 평균, 최댓값, 최솟값, 분산, 표준편차

SELECT SUM(sal), AVG(sal), MAX(sal), MIN(sal), VARIANCE(sal), STDDEV(sal) FROM emp;

단일 행 함수와 다중 행 함수는 함께 사용할 수 없음

그룹별 쿼리

SELECT ... FROM ... [WHERE ...] GROUP BY ... [HAVING ...] ORDER BY ...;

부서별 급여 평균

SELECT deptno, ROUND(AVG(sal), 2) FROM emp GROUP BY deptno ORDER BY deptno;

모든 문제에서 소수점은 반올림해서 소수점 이하 2자리까지만 표시하세요.
ex1. 부서별 급여 평균, 표준편차를 부서번호 오름차순으로 출력

SELECT ROUND(AVG(sal),2), ROUND(STDDEV(sal),2) 
FROM emp 
GROUP BY deptno 
ORDER BY deptno;

ex2. 직무별 직무, 직원수, 급여 최댓값, 최솟값, 평균을 직무 오름차순으로 출력.

SELECT job, COUNT(job), MAX(sal) AS "MAX_SAL", MIN(sal) AS "MIN_SAL", ROUND(AVG(sal),2) AS "AVG_SAL" 
FROM emp 
GROUP BY job 
ORDER BY job;

ex3. 부서별, 직무별 부서번호, 직무, 직원수, 급여 평균을 검색
정렬 순서: (1) 부서번호, (2) 직무

SELECT deptno, job, COUNT(*), ROUND(AVG(sal),2) AS "AVG_SAL" 
FROM emp 
GROUP BY deptno, job 
ORDER BY deptno, job;

ex4. 입사연도별 사원수를 검색. (힌트) to_char(날짜, 포맷) 이용.

SELECT TO_CHAR(hiredate, 'YYYY') AS "YEAR", COUNT(*) 
FROM emp 
GROUP BY TO_CHAR(hiredate, 'YYYY') 
ORDER BY YEAR;

별명을 ORDER BY에서 바로 사용 가능

SELECT 문장 순서:

SELECT 컬럼, ... AS "별명"                     [5]
FROM 테이블                                    [1]
WHERE 조건식                                   [2]
GROUP BY 그룹으로 묶어줄 컬럼, ...              [3]
HAVING 조건식                                  [4]
ORDER BY 출력 순서를 결정하기 위한 컬럼, ...;   [6]

부서별 급여 평균 검색. 부서별 급여 평균이 2000이상인 부서만 검색.

SELECT deptno, ROUND(AVG(sal), 2) -- 출력할 수 있는 내용은 deptno으로 묶였을 때 나올 수 있는 값들
FROM emp 
GROUP BY deptno -- deptno으로 그룹화 해줬으니까
HAVING AVG(sal) >= 2000 
ORDER BY deptno;

mgr 컬럼이 null이 아닌 직원들 부서멸 급여 평균 검색. 부서번호 오름차순.

SELECT deptno, ROUND(AVG(sal),2) 
FROM emp 
WHERE mgr IS NOT NULL 
GROUP BY deptno 
ORDER BY deptno;

직무별 사원수를 검색. PRESIDENT는 검색 제외. 직무별 사원수가 3명 이상인 직무만 검색.
직무의 오름차순으로 정렬

SELECT job, COUNT(job) 
FROM emp
GROUP BY job 
HAVING job != 'PRESIDENT' AND COUNT(job) >= 3 
ORDER BY job;

입사연도, 부서번호, 입사연도별 부서별 사원수 검색
1980년은 검색에서 제외
연도별 부서별 사원수가 2명 이상인 경우만 선택.
연도별 오름차순 출력.

SELECT TO_CHAR(hiredate, 'YYYY') AS "YEAR", deptno, COUNT(*) 
FROM emp 
WHERE TO_CHAR(hiredate, 'YYYY') != '1980'
GROUP BY TO_CHAR(hiredate, 'YYYY'), deptno 
HAVING COUNT(*) >= 2
ORDER BY YEAR;