SQL 문제 10-11일차

10일차

 

10일차는 Connect by 구문관련 내용입니다.

  MGR 컬럼은 해당 사원에 바로 한단계 위에 있는 관리자입니다. 즉 아래 예제를 설계로 표현 한 것 입니다. 가장 상위 김CEO는 상위 관리자가 없으므로 MGR에서 NULL로 표현합니다.

(실무에서는 NULL이 아닌 특정 값 넣음 인덱스 스캔 때문에)
  ex) 김 CEO
          I       I

        한 부장      이 부장
I          I        I
김사원   이대리    권과장

 

1. 가장 상위 관리자(MGR)부터 하위 사원까지 관계를 아래 예제처럼 출력하세요?

ex) 최 상위 관리자(MGR)은 NULL

 

   

select 
mgr, empno, ename, 
sys_connect_by_path(ename, '>') as relation
from emp
start with mgr is null
connect by prior empno = mgr;

 

 

2. JONES(사원번호 7566)의 밑에 있는 사원을 모두 아래 예제처럼 출력하세요?

ex)

 

 

 

select 
mgr, empno, ename, 
sys_connect_by_path(ename, '>') as relation
from emp
start with mgr = '7566' 
connect by prior empno = mgr;

 

 

3. ALLEN (사원번호 7499)의 위에 있는 사원을 모두 아래 예제처럼 출력하세요?

ex)

 

 

 

select
mgr, empno, ename, 
sys_connect_by_path(ename, '>') as relation
FROM EMP
START WITH EMPNO = '7698'
CONNECT BY PRIOR MGR = EMPNO;

 

 

4. BLAKE (사원번호 7698), CLARK(사원번호 7782) 의 밑에 있는 사원을 모두 아래 예제처럼 출력하세요?

ex) DEPTH 도 표현

 

 

SELECT LEVEL depth,mgr, empno, ename, 
sys_connect_by_path(ename, '>') as relation
FROM emp
START WITH EMPNO in (7698, 7782) 
CONNECT BY PRIOR empno = mgr;

 

 

5. 가장 말단에 있는 사원을 모두 아래 예제처럼 출력하세요?

ex) 자기 밑에 아무도 없는 사원

 

 

SELECT LEVEL depth, mgr, empno, ename, 
sys_connect_by_path(ename, '>') as relation
FROM emp
WHERE CONNECT_BY_ISLEAF = 1
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

 

 

 

 

 

11일차

 

1. 직업이 판매원(SALESMAN)이 아닌 직업들의 월 급여 합계가 5000을 초과하는 직업을 출력하시오.

 

 

select job, sum(sal)
from emp
where job not in ('SALESMAN') 
group by job
having sum(sal) > 5000;

 

 

2. 사원수가 3명 이상인 부서명과 사원수와 총 급여(SAL)를 출력하시오.

 

 

select d.dname, count(*)사원수, sum(sal)
from emp e, dept d
where e.deptno = d.deptno 
group by d.dname 
having count(*) >= 3;

 

 

3. 각 직업(JOB)별로 모든 사원의 급여(SAL)가 1000을 넘는 직업의 급여(SAL) 합계를 출력하시오.

 

 

select job, sum(sal)
from emp
group by job
having job not in (select job from emp where sal < 1000);