2007년 1월 28일

|
09:00 ~
**그룹함수(집계함수)
select 그룹함수                                                        5)
from                                                                      1)
where 그룹함수 사용불가 =>행을 제한하는 조걸절               2)
group by                                                                3)
having 그룹함수 사용 => 그룹을 제한하는 조건절                4)
order by 그룹함수 사용                                               6)

문제1)
select conut(*),avg(sal),max(sal),min(sal)
from emp;
문제2)
select avg(sal), max(sal), min(sal), sum(sal)
from emp
where job = 'SALESMAN';
문제3)
select count(*), count(comm), avg(comm), avg(nvl(comm,0)), sum(comm)/count(comm), sum(comm)/count(*) , count(distinct deptno)
from emp;

select sal, ename
from emp
where sal=(select max(sal) from emp);

문제3)
select deptno, count(*), avg(sal),min(sal),max(sal),sum(sal)
from emp
group by deptno;

문제4)
select deptno, count(*), avg(sal), min(sal), max(sal), sum(sal)
from emp
group by deptno
order by sum(sal) desc;

문제5)
select deptno, job, count(*), avg(sal), sum(sal)
from emp
group by deptno, job
order by deptno;

문제6)
select null "부서 및 전체", sum(sal)
from emp
union all
select deptno,sum(sal)
from emp
group by deptno;

//////////////////////////////////////////////
//동일한 의미
select deptno,job, sum(sal)
from emp
group by rollup(deptno,job);

select deptno,sum(sal)
from emp
group by grouping sets((deptno,job),(deptno),());
//////////////////////////////////////////////
//////////////////////////////////////////////
//동일한 의미
select deptno,job,sum(sal)
from emp
group by cube(deptno,job);

select deptno,job, sum(sal)
from emp
group by grouping sets((),(job),(deptno),(deptno,job));
//////////////////////////////////////////////

//그룹에 대해서 전체를 의미하는 ()
select deptno,sum(sal)
from emp
group by grouping sets((),deptno);

문제7)
select deptno, count(*), sum(sal)
from emp
group by deptno
having (count(*) >4);

문제8)
select job, sum(sal)
from emp
group by job
having sum(sal)>5000 and job != 'SALESMAN'
order by sum(sal) desc;

**조인 문법
1)ORACLE JOIN 문법
- cartesian product
- equi join
- non equi join
- self join
- outer join

2)ANSI JOIN 문법(9i부터 사용가능)
- cross join
- natural join
- using 절을 이용한 join
- join~ on 절을 이용한 join
- left/right/full outer join


문제1)
select e.empno, e.ename, e.job, e.deptno "사원부서번호", d.deptno "부서번호", d.dname,d.loc
from emp e, dept d

ANSI JOIN
select e.empno, e.ename, e.job, e.deptno "사원부서번호", d.deptno "부서번호", d.dname,d.loc
from emp e cross join dept d;


문제2) emp 테이블과 dept테이블을 Cartesian product하여 사원번호, 이름, 업무, 부서번호, 부서명, 근무지를 출력하여라
select e.empno, e.ename, e.job, e.deptno "사원부서번호", d.deptno "부서번호", d.dname,d.loc
from emp e, dept d
where e.deptno = d.deptno;

ANSI JOIN
select empno, ename, job, deptno "사원부서번호", deptno "부서번호", dname, loc
from emp natural join dept;

select empno,ename,job,deptno,dname,loc
from emp join dept using (deptno);

select e.empno, e.ename, e.job, e.deptno "사원부서번호", d.deptno "부서번호", d.dname,d.loc
from emp e JOIN dept d
ON (e.deptno=d.deptno);

문제3) salesman의 사원번호, 이름, 급여, 부서명, 근무지를 출력하여라
select e.empno, e.ename, e.sal, d.dname, d.loc
from emp e, dept d
where e.job = 'SALESMAN' and ( e.deptno = d.deptno);

ANSI JOIN
select e.empno, e.ename, e.sal, d.dname, d.loc
from emp e JOIN dept d
ON (e.deptno=d.deptno and e.job = 'SALESMAN');

문제4) 사원번호, 이름, 업무, 급여, 급여의 등급을 출력하여라. (emp와 salgrade테이블이용)
select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

ANSI JOIN
select e.empno, e.ename, e.job, e.sal, s.grade
from emp e JOIN salgrade s
ON (e.sal between s.losal and s.hisal);

문제5)emp테이블과 dept 테이블에서 사원들의 이름, 업무, 그들이 근무하는 부서명, 위치를 출력하시요.(단, 사원이 존재하지 않는 부서의 정보도 함께 출력하시오)

select e.ename, e.job, d.dname, d.loc
from emp e, dept d
where e.deptno(+) = d.deptno
order by dname;

ANSI JOIN
select e.ename, e.job, d.dname, d.loc
from emp e RIGHT OUTER JOIN dept d
ON (e.deptno=d.deptno);

문제6)emp 테이블에서 모든 사원들의 이름, 업무 및 그 사원들을 관리하는 관리자이름을 출력하여라.

select e.ename , e.job, m.ename
from emp e, emp m
where e.mgr = m.empno;

ANSI JOIN
select e.ename , e.job, m.ename
from emp e join emp m
ON( e.mgr = m.empno);

문제0)emp 테이블에서 scott의 급여보다 많은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여를 출력하여랴
select empno,ename, job, sal
from emp
where sal > (select sal from emp where ename ='SCOTT');

문제1) emp 테이블에서 사원번호가 7521인 사원과 업무가 같고 급여가 7934인 사원보다 많은 사원의 사원번호, 이름 담당업무, 입사일자, 급여를 출력하여라
select empno, ename, job, sal
from emp
where job = (select job from emp where empno =7521) and sal > (select sal from emp where empno=7934);

문제2)emp 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하여라.

select empno, ename, job, sal, deptno
from emp
where sal < (select avg(sal) from emp);

문제3)emp 테이블에서 부서별 평균급여가 20번 부서의 평균급여보다 높은 부서코드 및 평균급여를 출력하여라

select deptno, avg(sal)
from emp
group by deptno
having avg(sal)
 > (select avg(sal) from emp where deptno =20 group by deptno);


문제4)emp 테이블에서 업무별 평균급여중 가장 낮은 평균급여를 갖는 업무명과 그 평균급여를 출력하여라
select job, avg(sal)
from emp
group by job
having avg(sal) = (select min(avg(sal)) from emp group by job);

**서브쿼리의 종류
1)단일행 서브쿼리 : 리턴값이 1개인 경우, 일반비교연산자 사용
2)다중행 서브쿼리 : 서브쿼리의 리턴값이 2개 이상인 경우
where절, having 절에서 사용되는 경우 연산자를 IN, ANY, ALL을 사용하여야 한다.
3)단일컬럼 서브쿼리
4)다중컬럼 서브쿼리 : 컬럼을 쌍을 이루어서 비교해야 하는 경우
5)correlated 서브쿼리(상호관련 서브쿼리): 서브쿼리내에 메인쿼리의 테이블이 사용되는 경우로서 메인쿼리 테이블의 행의 갯수만큼 서브쿼리가 실행됨, 흡사 조인처럼 성능이 좋지 않음. 성능향상을 위해서 컬럼에 인덱스 생성해야 함
6)INLINE VIEW : FROM 절에 선언한 서브쿼리, 성능향상, 쿼리문을 simple하게 처리할 수 있음.


**dept 테이블에 존재하지 않는 부서코드를 갖는 사원의 정보를 조회하시오
select *
from emp
where deptno NOT IN (select deptno from dept);

select *
from emp e
where not exists ( select 'X' from dept d where e.deptno = d.deptno);


문제5)emp 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라.

select empno, ename, job, hiredate, sal, deptno
from emp
where (job,sal) in (select job, min(sal) from emp group by job);

문제6)emp와 dept테이블에서 업무가 manager인 사원의 이름, 업무, 부서명, 근무지를 출력하여라
select e.ename, e.job, d.dname, d.loc
from emp e, dept d
where e.job = 'MANAGER' and e.deptno=d.deptno;

select e.ename, e.job, d.dname, d.loc
from (select * from emp where job='MANAGER') e, dept d
where e.deptno = d.deptno;

오늘 배운 것은 그룹함수, 조인, 서브쿼리!!!

And