<DataBase_231106월> 레벨 쿼리

<레벨 쿼리>

 

데이터가 카테고리 형태로 구성된 경우(조직도)

상위 또는 하위 데이터를 추출하는 쿼리 기법(자기 자신은 레벨 1)

 

ex)

 

1. 사원의 상위 조직도를 추출
select level, sabun, saname, sajob from sawon
connect by sabun=prior samgr
start with sabun=14;

 

-----------------------------------------------

 

<테이블 복사>

 

1. 데이터 하나도 없는 껍데기만 만드는 코드(구조만 복사 되는 것. 컬럼과 데이터 없이)

(원리 : where 조건절을 부정형으로 만들면 됨) 

create table dept_ex as select * from dept where 1=2;

 

-----------------------------------------------

 

// 연습문제

 

1. 가장 오래 근무한 사람 5명을 추출
select name, hd
from (select saname name, sahire hd from sawon order by 2)
where rownum<=5;

2. 부하직원이 없는 사람(=samgr이 없는사람)을 추출
select sabun, saname
from sawon s
where not exists(select 1 from sawon t where t.samgr=s.sabun);

또는

select sabun, saname, samgr
from sawon
where sabun not in(select samgr from sawon
where samgr is not null);

3. 부서(명)별 급여합계를 추출하되, 
급여합계가 전체 합계의 30%를 초과하는 부서만 추출
select d.dname, sum(s.sapay)
from sawon s, dept d
where s.deptno=d.deptno
group by d.dname
having sum(s.sapay)>(select sum(sapay) from sawon)*0.3;

또는

with
dept_tot as (select dname, sum(sapay) d_tot from sawon natural join dept group by dname),
all_tot as (select sum(sapay) total from sawon)
select dname, d_tot from dept_tot dt, all_tot at
where d_tot>at.total*0.3;

 

4. 사원의 상위 조직도를 추출
select level, sabun, saname, sajob from sawon
connect by sabun=prior samgr
start with sabun=14;

5. 사원의 하위 조직도를 추출
select level, sabun, saname, sajob from sawon
where sabun!=2
connect by prior sabun=samgr
start with sabun=2;

 

----------------------------------------------

* 여기서부터 모든 문제는 
sawon_ex, dept_ex, gogek_ex 사본 테이블을 생성하여 작업할 것.
create table sawon_ex as select * from sawon;
create table dept_ex as select * from dept;
create table gogek_ex as select * from gogek;

6. 고객의 담당자가 없는 고객은 13번 사원으로 변경
update gogek_ex 
set godam=13
where godam is null;

7. 13번 담당자를 갖는 고객의 담당자를 null로 변경
update gogek_ex 
set godam=null
where godam=13;

8. sawon_ex 테이블에 직책이 사원인 사람들의 sawon 테이블 모든 컬럼 정보를 입력
insert into sawon_ex
select * from sawon 
where sajob='사원';

 

9. 17번 사원의 부서와 같은 사람들의 직책을 17번 사원의 직책으로 변경
update sawon_ex
set sajob=(select sajob from sawon_ex where sabun=17)
where deptno=(select deptno from sawon_ex where sabun=17);

 

10. 영업부 사원들의 급여를 10% 인상 변경
update sawon_ex
set sapay=sapay*1.1
where deptno=(select deptno from dept_ex where dname='영업부');

 

11. 관리부서 사원 중에서 회사의 평균급여보다 낮은 사원들을 삭제
delete from sawon_ex
where deptno=(select deptno from dept_ex where dname='관리부') and
sapay<(select avg(sapay) from sawon_ex);

 

12. 총무부 사원들의 급여를 자신이 속한 직책의 평균급여로 변경
update sawon_ex b
set sapay=(select avg(sapay) from sawon_ex a where a.sajob=b.sajob)
where deptno=(select deptno from dept_ex where dname='총무부');

 

13. create table sawon_10 as select * from sawon where deptno=10;
이 테이블을 생성한 후,
sawon 테이블과 비교하여 사원정보가
sawon_10 테이블에 존재하면 급여를 10% 인상하고
sawon_10 테이블에 존재하지 않는다면 입력하라.
문법 : merge into 테이블명 using 비교테이블명 on(두 테이블의 비교문 - 기본키대상)
merge into sawon_10 a 
using sawon b
on(a.sabun=b.sabun)
when matched then update set sapay=sapay*1.1
when not matched then insert values (b.sabun, b.saname, b.deptno, b.sajob, b.sapay, b.sahire, b.sasex, b.samgr);