2014/05/19 Oracle - Aggregate Function, Group by, Having, Subquery

Aggregate Function

여러개의 정보를 통해서 하나의 결과값

sal이 평균이상인 값을 출력하라.

select * 

from emp

where sal>(select avg(sal)

from emp);

하나의 쿼리문에서 두개의 값을 구하는것은 불가능하기에 where절에 쿼리문을 하나더 추가해준다.

Aggregate Function은 row들의 집합에 대해 연산이 이루어지는데, 이러한 row들의 집합은 table전체이거나 table의 일부 row일 수 있다.

Group by

어떤특정한 컬럼기준으로 그룹을 지정

ex)부서별 평균연봉을 구하라

select deptno, avg(sal) 

from emp

group by deptno;

select 맨앞에는 그룹에 속하는 값을 써주거나 상수값, 함수만 쓸수 있다.

ex)직종별 연봉의 합계

select job,sum(sal)

from emp

group by job;

실행순서

1.from

2. where

3. group by

4. select

5. order by

ex)

사원의 직무별로 급여의 평균과 최대, 최소값을 구하시오.

select title,avg(salary),max(salary),min(salary) 

from s_emp

group by title;

고객을 지역(region_id)별로 나눈 다음 다시 국가(country)별로 나누어 명수를 구하시오.

select region_id, country, count(*)

from s_customer

group by region_id, country;

Stock Clerk 직종의 사원들의 부서별 인원수를 출력하시오.

select dept_id, count(*)"Number"

from s_emp

where title='Stock Clerk'

group by dept_id;

Having 절

group by에 조건을 주고 싶을때 사용( where절과 같은 역할)

select deptno, avg(sal)

from emp

where hiredate >'81/01/01'

group by deptno

having count(*)>3;

부서별로 2명이상 근무하고 있는 직종 만을 출력하시오.

select dept_id, title

from s_emp

group by dept_id, title

having count(*)>1;

Subquery

서브쿼리는 괄호로 묶어야한다.

서브쿼리는 연산자의 오른쪽에 나타나야 한다.

서브쿼리는 여러SQL 문장에서 사용 가능하다.

서브쿼리는 실행 결과 row의 개수에 따라 operator의 종류를 맞게 써준다.

*에러

select *

from emp

where sal > avg(sal);     그룹함수로 바로 쓰면 좋겠지만 

select *

from emp

where sal > (select avg(sal) from emp);    이렇게 써줘야한다. 

select *

from emp

where job in (select job from emp group by job having avg(sal)>2000);

서브쿼리결과가 한개 이상 될경우 '=' 이 아닌 in(),=any()를  써준다.

select * from (select * from emp);    =  select * from emp;   둘은 같다.

select * from (select ename from emp);   =  select ename from emp;   둘은 같다.

ex) 아우터조인, 셀프조인

select e1.ename , e2.ename

from emp e1, (select empno,ename from emp) e2

where e1.mgr = e2.empno(+);    

select *

from emp

where sal>(select avg(sal) from emp)

직종별로 평균이상을 받는 사람의 값

select job, count(*)

from emp

where sal>(select avg(sal) from emp)

group by job;

emp테이블에서 전체평균보다 큰 그룹평균이 몇명인지와 직종을 출력하라

select job, count(*)

from emp

where sal>(select avg(sal) from emp)

group by job

having avg(sal) >= (select avg(sal) from emp);

Data Manipulation Language

Insert - 삽입

SYSDATE : 현재 날짜

ROWID : 행번호

모양만 똑같이 만들기

create table emp_hhh

as

select empno,ename,sal from emp

where empno = 0;

3개 행을 동시에 넣기

insert into emp_hhh select empno,ename,sal from emp where deptno=10;

UPDATE - 변경

update emp_hhh

set sal = 1000;

where ename = 'KING';

업데이트를 할때는 항상 조건을 다는 것이 좋다.

update(select sal from emp_hhh)

set sal = 500;

서브쿼리를 이용해서도 가능하다.

update emp_hhh

set sal = (select MAX(sal) from emp)

where ename = 'KING';

KING인 사람의 sal을 emp테이블에서 가장큰값을 가진 sal로 변경하라.

Merge - update+insert

두개의 테이블을 합칠때 사용

merge into emp_history eh

using emp e

on(e.empno = eh.empno)

when matched then

update set eh.salary = e.sal

when not matched then

insert values(e.empno,sysdate,sal);

DELETE - 테이블안에있는 데이터를 지움

delete from dept

where deptno=10