집계함수
행집합 연산을 수행하여 그룹별로 하나의 결과를 산출
AVG()
- NULL을 제외한 모든 값들의 평균을 반환,
- NULL값은 평균 계산에서 무시됨
SELECT ROUND(AVG(sal)) FROM emp;
SELECT AVG(comm) FROM emp;- NULL값은 0으로 대체한 후 평균을 구함
SELECT AVG(NVL(comm,0)) FROM emp;COUNT()
NULL을 제한 값을 가진 모든 레코드의 수를 반환.
count(*) 형식을 사용하면 NULL도 계산에 포함
SELECT COUNT(empno) FROM emp;
SELECT COUNT(*) FROM emp;
SELECT COUNT(comm) FROM emp;
SELECT * FROM emp;MAX()
레코드 내에 있는 여려 값 중 가장 큰 값을 반환
SELECT MAX(sal) FROM emp;MIN()
레코드 내에 있는 여러 값 중 가장 작은 값을 반환
SELECT MIN(sal) FROM emp;
SELECT MIN(ename) FROM emp;SUM()
레코드들이 포함하고 있는 모든 값을 다하여 반환
SELECT SUM(comm) FROM emp;그룹함수
GROUP BY
집계함수 적용 시 개별 컬럼을 지정할 수 없기 떄문에
개별 컬럼을 지정할 경우에는 반드시 GROUP BY 절에 지정한 컬럼만 가능
부서별로 근무하는 사원수
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;부서별로 최고 급여
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;10번 부서를 제외한 다른 부서의 급여 합계
SELECT deptno, SUM(sal) FROM emp
WHERE deptno!=10 GROUP BY deptno ORDER BY deptno DESC;HAVING
- 그룹 처리 결과를 제한하고자할 때 HAVING절 이용
- WHERE절에는 집합함수를 사용할 수 없고 HAVING절을 이용해야 함
- HAVING절에 Alias를 사용할 수 없음
그룹함수 중첩
부서별 평균급여를 구하고 그 평균급여 중에서 최고 평균급여를 구하기
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;분기별로 입사한 사원의 수
SELECT TO_CHAR(hiredate,'Q') AS quarter,
COUNT(empno) AS count_member
FROM emp GROUP BY TO_CHAR(hiredate,'Q')
ORDER BY quarter ASC;분석함수
RANK()
순위를 표현할 때 사용하는 함수
RANK(조건값) WITHIN GROUP (ORDER BY 조건값 컬럼명 [ASC | DESC] )
특정 데이터의 순위를 확인할 수 있음
[주의] RANK 뒤에 나오는 데이터와 ORDER BY 뒤에 나오는 데이터는 같은 컬럼
SELECT RANK('SMITH') WITHIN GROUP (ORDER BY ename) "RANK"
FROM emp;
SELECT ename FROM emp ORDER BY ename;emp테이블에서 empno,ename,sal, 급여순위 출력
SELECT empno, ename, sal,
RANK() OVER (ORDER BY sal) AS RANK_ASC,
RANK() OVER (ORDER BY sal DESC) RANK_DESC
FROM emp;그룹별 순위
RANK() OVER (PARTITION BY ~ ORDER BY)
emp테이블에서 10번 부서에 속한 직원들의 사번과 이름,급여,해당 부서내의
급여 순위를 출력
SELECT empno, ename, sal, RANK() OVER (ORDER BY sal DESC)"RANK"
FROM emp WHERE deptno=10;emp테이블을 조회하여 사번, 이름, 급여, 부서번호, 부서별 급여 순위를 출력
SELECT empno,ename,sal,deptno,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "RANK"
FROM emp;JOIN
- 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법
- 보통 둘 이상의 행들의 공통된 값 Primary key 및 Foreign key 값을
사용하여 조인. - 두 개의 테이블을 select 문장 안에서 조인하려면 적어도 하나의 컬럼이
그 두 테이블 사이에서 공유되어야 함.
Cartesian Product(카티션 곱)
검색하고자 했던 데이터뿐만 아니라 조인에 사용된 테이블의 모든 데이터가 반환되는 현상
SELECT * FROM emp,dept;동등 조인(equi join)
Oracle 전용
조건절이 Equality Condition(=)에 의하여
조인이 이루어짐
SELECT emp.ename, emp.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;테이블에 알리아스 부여하기
SELECT e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;컬럼을 호출 할 때 테이블명 또는 테이블 알리아스 생략
(한 쪽 테이블에만 컬럼이 존재할 경우 테이블명 또는 테이블 알리아스 생략
가능)
SELECT ename, e.deptno, dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;비동등 조인(non equi join)
테이블의 어떤 column도 join할 테이블의 column에 일치하지 않을 때
사용하고 조건은 동등(=)이외의 연산자를 갖음
between and,not between and,is null,is not null, in, not in
사원이름,급여,급여등급 구하기(emp,salgrade 테이블 이용)
SELECT e.ename,e.sal,s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;self join
같은 테이블이 서로 조인됨
사원이름과 해당 사원의 관리자 이름 구하기(관리자가 없는 사원 제외)
SELECT e.ename 사원이름, m.ename 관리자이름
FROM emp e, emp m
WHERE e.mgr = m.empno;외부조인(outer join)
- equi join을 하면 두 개의 테이블의 두 개 컬럼에서 공통된 값이
없다면 테이블로부터 데이터를 반환하지 않음 - equi join시 누락하는 데이터를 보기 위해서는 outer join을 사용하면
누락된 데이터를 볼 수 있음
누락된 행의 반대 테이블의 조인 조건에 (+)기호 표시
SELECT DISTINCT e.deptno, d.deptno
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;사원이름과 해당 사원의 관리자 이름 구하기 (관리자가 없는 사원도 표시)
SELECT e.ename 사원이름, m.ename 관리자이름
FROM emp e, emp m
WHERE e.mgr = m.empno(+);