오라클 강의(8강~10강)

8강 그룹함수(하나 이상의 행을 그룹으로 묶어 연산하여, 하나의 결과를 나타내는 함수)

종류 sum, avg, max, min, count

특징 :컬럼값이 null인 경우는 제외하고 계산한다

단일행 함수는 

각행에 대해서 함수의 결과가 구해지기 때문에 결과가 여러개의 로우로 구해짐

sum(해당 컬럼 값들에 대해 총합을 구하는 함수)

avg(해당 컬럼의 평균값을 구하는 함수)

max(해당 컬럼의 최대값을 구하는 함수)

min(해당 컬럼의 최소값을 구하는 함수)

count(테이블에서 조건을 만족하는 행의 개수를 반환하는 함수)

->중복되어 있는 것까지 카운트 되니 -> distinct로 중복해 제거를 해서 구해야함

ex)select count(distinct job) 업무수 from emp;

****select문에 그룹함수를 사용하는 경우

그룹 함수를 적용하지 않은 단순컬럼은 올수 없음

ex) min함수를 쓰고 ename를 쓰면 오류가 발생 -> 서브쿼리를 이용해서 둘다 나오도록 한다

group by절

특정 컬럼을 기준으로 테이블에 존재하는 행들을 그룹별로 구분하기 위해 사용

형식 : select 컬럼명, 그룹함수 from 테이블명 where 조건 group by 컬럼명;

즉, 그룹 함수를 쓰되 어떤 컬럼값을 기준으로 그룹 함수를 적용할지 기술

***컬럼의 별칭을 사용할 수 없고 반드시 컬럼명을 기술

사원 테이블을 부서번호로 그룹 짓는 경우

ex)select deptno from emp group by delptno;

부서별 평균 급여를 구하는 경우

ex)select deptno, avg(sal) from emp group by deptno;

*****그룹함수를 쓸때 단순컬럼을 올수 없다고 했는데 group by와 동일한 컬럼은 매치가 되서 가능하다

부서별 급여 총액과 평균 급여를 구하는 경우

select delptno, sum(Sal), avg(sal) from emp group by deptno;

부서별 최대 급여와 최소 급여를 구하는 경우

select delptno, max(Sal), min(sal) from emp group by deptno;

부서별 사원의 수와 커미션을 받는 사원의 수를 계산하는 경우

select delptno, count(*), count(comm) from emp group by deptno;

having 절

group by 절에 의해 생성된 결과 값 중 원하는 조건에 부합하는 자료만 보고자 할때 사용

즉, 그룹의 결과를 제한

그룹함수을 적용해서 나온 결과값 중에서 원하는 조건에 부합하는 자료만 산출 할때 사용

그룹함수

where절은 조건을 사용하여 결과를 제한

테이블에서 특정 조건에 부합하는 자료만을 검색할때 사용

단순컬럼

ex) 그룹 지어진 부서별 평균 급여가 200이상인 부서의 번호와 부서별 평균 급여를 출력하는 경우

select deptno, avg(Sal) from emp group by deptno, having avg(sal) >= 2000;

ex) 부서의 최대값과 최소값을 구하되, 최대 급여가 2900이상인 부서만 출력하는 경우

select deptno, max(sal), min(sal) from emp group by deptno having max(sal) > 2900;

9장

자동으로 소계/합계를 구해주는 함수

-rollup(주어진 데이터들의 소계를 구해주는 함수)

-cube(주어진 테이터들의 천체 총계까지 구해주는 함수)

select deptno, job, count(*), sum(sal) from emp group by rollup(deptno, job);

select deptno, job, count(*), sum(sal) from emp group by job, rollup(deptno);

select deptno, job, count(*), sum(sal) from emp group by deptno, rollup(job);

select deptno, job, count(*), sum(sal) from emp group by cube(deptno, job);

계층형 쿼리 : 데이터를 조회할 때 상위와 하위 관계를 표시할때 사용

-> connect by 절 이용(계층 구조에서 각 행의 연결관계를 설정 -> emp

-> start with가 시작위치이며 보통 사장님

ex) select level, lpad(empno, level*4, ‘*’) as empno01, substr(sys_connect_by_path(ename,’-’),2) ename02, 

ename, mgr from emp

start with mgr is null

connect by prior empno = mgr;

***lpad(empno, level*4, ‘*’) : level*4 바이트 길이로 출력하되, 왼쪽 빈자리는 *로 채우라는 뜻

***level은 현재테이블에는 존재하지 않는 컬럼, 오라클의 모든 sql에서 사용할 수 있는 것으로 해당 데이터가 몇번째 단

계이냐를 의미

level별로 급여 합계, 사원의 명수 구하는 쿼리

ex)select level, sum(sal) total, count(empno) cnt from emp

start with mgr is null

connect by prior empno = mgr

groub by level

order by level;

오라클 11g에 새롭게 추가된 함수

-listagg(정규화된 테이블을 비정규화해서 출력하는 함수 -> 세로로 출력되는 행을 가로로 보기를 원하는 경우)

ex)set lines 200

col listagg for a40

select delptno, listagg(ename, ‘**’) within group (order by hiredate) “listagg” from emp group by deptno;

**는 구분자, order by는 규칙을 적어주면 됨(여기는 hiredate)

-pivot(row단위를 column단위로 변경해주어, 사용자가 한눈에 보기 편한 레포트를 만들어 주는 함수)

ex) select deptno, count(0) from emp group by deptno order by deptno;

-> select deptno, count(decode(job, ‘clerk’,0)) “cleark”,count(decode(job, ‘manager’,0)) “manager”,count

(decode(job, ‘president’,0)) “president”,count(decode(job, ‘analyst’,0)) “analyst”,count(decode(job, 

‘salesman’,0)) “salesman”  from emp group by deptno order by deptno;

->select * from (select deptno, job, empno from emp)

pivot ( 

count(empno) 

for job in(‘clerk’ as “clerk”, ‘manager’ as “manager”, ‘president’ as “president”, ‘analyst’ as 

“analyst”, ‘salesman’ as “salesman”)

)

order by deptno;

부서별/담당업무별 인원수와 급여 합계 출력

select * from (select deptno, job, empno, sal from emp)

pivot ( 

count(empno) as cnt, sum(nvl(sal, 0)) as sal

for job in(‘clerk’ as “clerk”, ‘manager’ as “manager”, ‘president’ as “president”, ‘analyst’ as 

“analyst”, ‘salesman’ as “salesman”)

)

order by deptno;

-rank

주어진 컬럼값의 그룹에서 값의 순위를 계산한 후 순위를 출력해 주는 함수

->집계용 문법 rank(조건값) within group(order by 조건컬럼명 asc|desc )

->분석용 문법 rank() over(order by 조건컬럼명 asc|desc )

ex) 이름 오름차순 정렬시 순위

select rank(‘smith’) within group (order by ename) “RANK” from emp;

ex) 10번 부서에 속한 직원들의 사번과 이름, 급여 해당부서내의 급여 순위를 출력하는 경우

 select empno, ename, sal, rank() over (order by sal desc) “순위” from emp where deptno = 10;

10강

조인 : 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들어서 결과를 보

여줌

조인의 종류

-cartesian product(카티션 곱)

where절에 공통되는 컬럼에 의한 조인이 발생되지 않아 모든 데이터가 검색 결과로 나타나는 경우

아무런 의미를 가지지 못함-> 그냥 보여만 주기 때문에

ex)select * from emp, dept;

-equi join(이퀄조인)

가장 많이 사용

조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인 기법

ex)select * from emp,dept where  emp.deptno = dept.deptno;

->select ename, dname from emp,dept where emp.deptno = dept.deptno;(컬럼앞에 어느테이블인지 명시해야 함)

->select ename, dname from emp,dept where emp.deptno = dept.deptno and ename=’scott’;

->select ename, dept.dname, emp.deptno from emp,dept where  emp.deptno = dept.deptno and ename=’scott’;(컬럼앞

에 어느테이블인지 명시해야 함, 단 중복되지 않는 컬럼일경우는 테이블명 안써도 된다)

->select e.ename, d.dname, e.deptno. d.deptno from emp e, dept d where  e.deptno = d.deptno and e.ename=’scott’

;(테이블의 별칭을 부여한 후, 컬럼앞에 소속테이블을 지정할 경우는 반드시 별칭을 붙어야 함-원래 테이블명을 쓰면 안

됨)

-no-equi join(non이퀄조인)

동일 컬럼이 없이 다른 조건을 사용하여 조인(크거나 작거나 등의 경우)

->이퀄조인이 서로같은 조건을 가진 데이터를 조인해오는 방식이라면 non이퀄조인은 크거나 작거나 하는 경우 조인 방법

사원의 급여가 몇등급인지 살펴보는 경우( salgrade에는 등급별로 low와 hign가 입력되어 있음)

select ename, sal, grade from emp, salgrade where sal >= losal and sal<=hisal;

select ename, sal, grade from emp, salgrade where sal between losal and hisal;

한명의 사원 이름과 소속 부서명, 급여의 등급을 출력하는 경우

(사원 테이블에는 부서이름과 급여 등급은 없고, 부서번호와 급여만 있음)

-> 부서명 이퀄조인, 급여등급은 non이퀄조인

select e.ename, d.dname. s.grade from emp e, dept d, salgrade s where e.deptno = d.deptno and e.sal between 

s.losal and s.hisal

-self join

자기자신과 조인

ex) 특정사원의 매니저가 누구인지 알아낼때 매니저 테이블은 중복이기 때문에 만들지 않고 사원테이블에서 모두 저장되

어 있음(별칭으로 두개의 테이블인 것처럼 사용해야 함)

select employee.ename ||’의 매너지는 ‘ || manager.ename ||’입니다.’ from emp emplyee, emp manager where 

employee.mgr = manager.empno;

-outer join

조인조건에 만족하지 않는 행도 나타내는 조인 기법

2개 이상의 테이블에 조인될때, 어느 한쪽의 테이블에는 해당하는 데이터가 존재함

-> 다른 쪽 테이블에는 데이터가 존재하지 않는 경우 그 데이터가 출력되지 않는 문제를 해결하기 위해서 사용되는 조인 

기법

self 조인에서 사장은 mgr가 null이므로 제외되어서 self join 결과값이 나온다

하지만 사장도 나오게 할때 사용하는 것이 outer join 임 

ex)select employee.ename ||’의 매너지는 ‘ || manager.ename ||’입니다.’ from emp emplyee, emp manager where 

employee.mgr = manager.empno(+);

정보가 부족한 컬럼뒤에 (+)를 붙여 줌