<DataBase_231103금> 서브쿼리 문제&답안

<선생님이 정리해주신>
<서브쿼리>
 
*** 서브쿼리
-- SQL명령에서 데이터의 추출이 필요한 또 다른 select명령이 필요한 경우 사용하는 고급쿼리 기법(64개까지 가능)
-- 서브쿼리는 먼저 실행***되며, 서브쿼리만으로도 문법적인 오류가 없다! (상관쿼리 제외)
-- 서브쿼리는 반드시 ( )로 묶는다
-- 서브쿼리의 결과값***이 외부쿼리와 최종적으로 비교, 연산된 후 실행됨

--종류
1. 단일행 서브쿼리 : 서브쿼리의 결과 행이 단일행인 경우 (=,!=,>,<,>=,<=)
2. 다중행 서브쿼리 : 서브쿼리의 결과 행이 다중행인 경우 (in***, any, all, exists***)
--------------
3. 다중컬럼 서브쿼리 : 서브쿼리의 결과 컬럼이 다중컬럼인 경우
4. 상호관련 서브쿼리 : 외부쿼리의 단일* 후보행이 서브쿼리로 들어가서 비교/연산된 후, 
    다시 외부쿼리의 후보행과 최종적으로 비교/연산된 후 처리되는 서브쿼리

--이순신과 같은 부서인 사람을 추출
select deptno, saname from sawon
where deptno = (select deptno from sawon where saname='이순신'); 

--회사에서 가장 급여를 많이 받는 사람을 추출
select saname, sapay from sawon
where sapay = (select max(sapay) from sawon);

--회사의 평균 급여보다 많이 받는 사람을 추출
select saname, sapay from sawon
where sapay > (select avg(sapay) from sawon);

--10번 부서에서 전체 사원의 평균급여보다 많이 받는 사람을 추출
select saname,sapay,deptno from sawon
where deptno = 10 
   and sapay > (select avg(sapay) from sawon);

--10번 부서에서 최대급여, 최소급여를 받는 사람을 추출
select deptno, saname, sapay from sawon
where (sapay = ( select max(sapay) from sawon where deptno = 10)
      or sapay = ( select min(sapay) from sawon where deptno = 10))
    and deptno=10; --***

-- where sapay=5000 or sapay=1000;

-- 이순신과 부서와 직책이 같은 사람을 추출 // 다중컬럼
select saname, deptno, sajob from sawon
where (deptno, sajob) = (select deptno, sajob from sawon where saname='이순신');
--where (nvl(deptno, -1), sajob) = (select nvl(deptno, -1), sajob from sawon where saname='이순신');

* 다중컬럼 서브쿼리 주의
1) 컬럼의 순서, 개수, 타입이 같아야 함
2) 비교 연산자는 같은 연산만 사용해야 함
3) 널을 포함한 컬럼이 존재하는 경우 널대체(음수)하여 비교해야 함

-- 각 부서에서 최대급여를 받는 사람을 추출
select deptno, saname, sapay from sawon
where (deptno,sapay) in (select deptno, max(sapay) from sawon 
       group by deptno);

-- 각 부서에서 부서의 평균급여보다 많이 받는 사람을 추출 // 상관쿼리
select saname, deptno, sapay from sawon s
where sapay > (select avg(sapay) from sawon t 
        where t.deptno = s.deptno);

-- 각 부서에서 최대급여를 받는 사람을 추출(상관쿼리)
select saname, deptno, sapay from sawon s
where sapay = (select max(sapay) from sawon t 
        where t.deptno = s.deptno)

* 컬럼 서브쿼리 = 스칼라 서브쿼리

-- 사원명, 직책, 부서명, 직책평균급여(자신이 속한)를 추출
select s.saname, s.sajob, d.dname, (select avg(t.sapay) from sawon t where t.sajob=s.sajob) "직책평균급여" from sawon s, dept d
where s.deptno = d.deptno;

* Top & N 서브쿼리
--최대값 또는 최소값을 가진 일부의 행을 추출하는 서브쿼리 기법
--반드시 rownum을 이용해야 함
--from절 서브쿼리(inline-view) : 가상 테이블 개념, 남발 금지(성능저하)

-- 급여를 가장 많이 받는 3명을 추출
select name, pay from (select saname name, sapay pay from sawon order by 2 desc)
where rownum<=3;

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

* exists 연산자
--not in의 문제를 해결하기 위해 많이 사용됨
--서브쿼리의 결과가 존재하면 외부쿼리가 실행되고, 서브쿼리의 결과가 존재하지 않으면 외부쿼리는 실행되지 않는 연산자

SQL> select * from dept
  2  where exists(select dname from dept where deptno=77);

선택된 레코드가 없습니다.

SQL> select * from dept
  2  where exists(select dname from dept where deptno=30);

    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 총무부     서울
        20 영업부     대전
        30 전산부     부산
        40 관리부     광주

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

* with 명령
--from절 서브쿼리를 보완하기 위한 명령으로 복잡한 쿼리를 단순한 테이블로 사용하기 위한 명령
--컬럼이 조작된 경우, 컬럼 별칭은 반드시 준다
--형식
with table1 as (select~~~),
      table2 as (select~~~),
      .......
select ~~~ from table1, table2,....;

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

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;

*** Level 쿼리
-- 데이터가 카테고리 형태로 구성된 경우(조직도) 상위 또는 하위 데이터를 추출하는 쿼리 기법(자신은 레벨 1)

--14번 사원의 상위 조직도를 추출
SQL> select level, sabun, saname, sajob from sawon
  2  connect by sabun=prior samgr   --***
  3  start with sabun=14;

     LEVEL      SABUN SANAME     SAJOB
---------- ---------- ---------- ----------
         1         14 채시라     사원
         2          3 이순신     과장
         3          2 한국남     부장
         4          1 홍길동     회장

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

     LEVEL      SABUN SANAME     SAJOB
---------- ---------- ---------- ----------
         2          3 이순신     과장
         3          5 이순라     사원
         3         14 채시라     사원
         2          7 놀기만     과장
         3         19 임꺽정     사원
         2         11 류별나     과장


-------------------------------------------
 
// 연습문제
 
1. 이순신과 같은 부서에서 근무하는 사람.
select deptno, saname 
from sawon
where deptno=(select deptno from sawon where saname='이순신');

2. 회사에서 가장 급여를 많이 받는 사람을 추출
select saname, sapay 
from sawon
where sapay=(select max(sapay) from sawon);

3. 회사의 평균 급여보다 많이 받는 사람을 추출
select saname, sapay
from sawon
where sapay>(select avg(sapay) from sawon);

4. 10번 부서에서 전체 사원의 평균급여보다 많이 받는 사람을 추출
select saname, deptno, sapay
from sawon
where sapay>(select avg(sapay) from sawon where deptno=10);

5. 10번 부서에서 최대급여, 최소급여를 받는 사람을 추출
select deptno, saname, sapay from sawon
where deptno=10 and 
(sapay=(select max(sapay) from sawon where deptno=10)) or
(sapay=(select min(sapay) from sawon where deptno=10));

6. 이순신과 부서와 직책이 같은 사람을 추출
select saname, deptno, sajob from sawon
where (deptno, sajob)=(select deptno, sajob from sawon where saname='이순신');

7. 각 부서에서 최대급여를 받는 사람을 추출 (상관쿼리를 이용)
select saname, deptno, sapay from sawon
where (deptno, sapay) in(select deptno, max(sapay) "최대급여" from sawon group by deptno);
 
또는 

select saname, deptno, sapay from sawon s
where sapay=(select max(sapay) from sawon t where t.deptno=s.deptno);

8. 사원명, 직책, 부서, 직책평균급여(자신이 속한)를 추출 (상관쿼리를 이용)
select saname, sajob, deptno, 
(select avg(sapay) from sawon a where a.sajob=b.sajob) "직책평균" 
from sawon b;
 
또는
 
select saname, deptno, sapay from sawon s
where sapay>(select avg(sapay) from sawon t where t.deptno=s.deptno);

-- 직책별 평균 
select sajob, avg(sapay) 
from sawon
group by sajob;

-- 직책별 평균급여보다 급여가 큰 사람을 추출
select saname, sajob, sapay,
(select avg(sapay) from sawon t where t.sajob = s.sajob) "직책평균"
from sawon s
where sapay > (select avg(sapay) from sawon t where t.sajob = s.sajob)

9. 사원명, 직책, 부서명, 직책평균급여(자신이 속한)를 추출 (상관쿼리를 이용)
select s.saname, s.sajob, d.dname, 
(select avg(t.sapay) from sawon t where s.sajob=t.sajob) "직책평균" 
from sawon s, dept d
where s.deptno=d.deptno;
 
----------------------------------------------
 
<내가 풀이한>
<서브쿼리 문제&답안>

1. 이름이 ALLEN인 사원과 같은 업무를 하는 사람의 사원번호, 이름, 업무, 급여
set linesize 120
/
select empno, ename, job, sal  
from emp
where job=(select job from emp where ename='ALLEN');

2. EMP 테이블의
사원번호가 7521인 사원과 업무가 같고
급여가 사원번호가 7934인 사원보다 많은 사원의
사원번호, 이름, 담당업무, 입사일, 급여
select empno, ename, job, hiredate, sal
from emp
where job=(select job from emp where empno=7521) 
and sal>(select sal from emp where empno=7934);

3. EMP 테이블에서
급여의 평균보다 적은 사원의
사원번호, 이름, 업무, 급여, 부서번호
select empno, ename, job, sal, deptno
from emp
where sal<(select avg(sal) from emp);

4. 부서별 최소급여가 20번 부서의 최소급여보다 작은 부서의 부서번호, 최소급여
select deptno, min(sal)
from emp
group by deptno
having min(sal)>(select min(sal) from emp where deptno=20);

5. 업무별 급여 평균 중 
가장 작은 급여평균의 업무와 급여평균
select job, avg(sal)
from emp
group by job
having avg(sal)=(select min(avg(sal)) from emp group by job);

6. 업무별 최대 급여를 받는
사원의 사원번호, 이름, 업무, 입사일, 급여, 부서번호
select empno, ename, job, hiredate, sal, deptno
from emp
where sal in(select max(sal) from emp group by job);

7. 30번 부서의 최소급여를 받는 사원보다 많은 급여를 받는 사원의
사원번호, 이름, 업무, 입사일, 급여, 부서번호,
단 30번 부서는 제외
select empno, ename, job, hiredate, sal, deptno
from emp
where sal>(select min(sal) from emp where deptno=30)
and deptno!=30;

8. 급여와 보너스가 30번 부서에 있는 사원의 급여와 보너스가 같은 사원을 출력하라.
단, 30번 부서의 사원은 제외하고 출력하라.
select ename, sal, comm, deptno
from emp
where (sal, comm) in(select sal, comm from emp where deptno=30)
and deptno!=30;

9. BLAKE와 같은 부서에 있는 모든 사원의
이름과 입사일자를 출력
select ename, hiredate, deptno from emp
where deptno=(select deptno from emp where ename='BLAKE');

10. 평균급여 이상을 받는 모든 사원에 대해
사원의 번호와 이름을 출력,
급여가 많은 순서로..
select empno, ename, sal from emp
where sal>=(select avg(sal) from emp) 
order by sal desc;

11. 이름에 T가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해
사원번호, 이름, 급여를 출력,
사원번호 순서로 출력
select empno, ename, sal from emp 
where deptno in(select deptno from emp where ename like '%T%')
order by empno; 

12. 부서위치가 CHICAGO인 모든 사원에 대해 
이름, 업무, 급여 출력
select deptno, ename, job, sal from emp e 
where deptno = (select deptno from dept d where e.deptno = d.deptno and loc = 'CHICAGO');

13. KING에게 보고하는 모든 사원의 
이름과 급여를 출력
select ename, sal from emp 
where mgr in(select empno from emp where ename='KING');

14. FORD와 업무와 월급이 같은
사원의 모든 정보 출력
select empno, ename, job, mgr, hiredate, sal, comm deptno from emp
where (job, sal) in (select job, sal from emp where ename='FORD');

15. 업무가 JONES와 같거나
월급이 FORD 이상인
사원의 이름, 업무, 부서번호, 급여 출력
select ename, job, deptno, sal from emp
where sal>=(select sal from emp where ename='FORD')
or job=(select job from emp where ename='JONES');

16. SCOTT 또는 WARD와 월급이 같은
사원의 이름, 업무, 급여를 출력
select ename, job, sal from emp
where sal in(select sal from emp where ename='SCOTT' or ename='WARD');

17. SALES에서 근무하는 사원과 같은 업무를 하는
사원의 이름, 업무, 급여, 부서번호 출력
select ename, job, sal, deptno
from emp e
where deptno=(select deptno from dept d where e.deptno=d.deptno and dname='SALES');

18. 자신의 업무별 평균 월급보다 낮은 사원의
부서번호, 이름, 급여, 자신의 부서 평균급여를 출력

select e.deptno, e.ename, e.sal, e.job, d.dsal 
from emp e, (select job, avg(sal) asal from emp group by job) s, (select deptno, avg(sal) dsal from emp group by deptno) d 
where e.job=s.job and e.deptno=d.deptno and e.sal<s.asal; 

 

19. 사원번호, 사원명, 부서명, 소속부서 인원수, 업무, 소속 업무 급여평균, 급여를 출력

with a as(select e.empno, e.ename, e.deptno, e.job, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno),   
      b as (select deptno, count(*) deptcnt from emp group by deptno), -- 소속부서 인원수 
      c as (select job, avg(sal) jobsal from emp group by job) -- 소속 업무 급여평균 
select a.empno, a.ename, a.deptno, a.job, a.sal, a.dname, b.deptcnt, c.jobsal 
from a, b, c 
where a.deptno = b.deptno and a.job = c.job;

20. 사원번호, 사원명, 부서번호, 업무, 급여, 자신의 소속 업무 평균급여를 출력
select e.empno, e.ename, e.deptno, e.job, e.sal, j.savg 
from emp e, (select job, avg(sal) savg from emp group by job) j 
where e.job = j.job(+);

21. 최소한 한 명의 부하직원이 있는
관리자의 사원번호, 이름, 입사일자, 급여 출력
select m.empno, m.ename, m.hiredate, m.sal from emp m 
where exists(select e.mgr from emp e where e.mgr = m.empno);

22. 부하직원이 없는 사원의 사원번호, 이름, 업무, 부서번호 출력 
select empno, ename, job, deptno 
from emp 
where empno not in(select mgr from emp where mgr is not null); 

관리자번호가 있으면 관리자. null이면 부하직원 없음

직원번호가 관리자번호에 없으면 매니저가아님. 즉 부하직원이 없음. 

 

select m.empno, m.ename, m.job, m.deptno from emp m 
where not exists(select e.mgr from emp e where e.mgr = m.empno);

23. BLAKE의 부하직원의
이름, 업무, 상사번호 출력
select ename, job, mgr from emp 
where mgr = (select empno from emp where ename = 'BLAKE');

24. BLAKE와 같은 상사를 가진 사원의
이름, 업무, 상사번호 출력
select ename, job, mgr from emp
where mgr=(select mgr from emp where ename='BLAKE');
 
----------------------------------------------
 
<선생님이 풀이한>
<서브쿼리 문제&답안>
 
--SUBQUERY (SCOTT 계정으로!)
1.이름이 ALLEN인 사원과 같은 업무를 하는 사람의 사원번호,이름,업무,급여
select e.ename, d.dname, e.sal, e.empno
from emp e, dept d
where d.dname =
(select d.dname 
from emp e , dept d 
where e.deptno = d.deptno and e.ename='ALLEN')
and e.ename is not null;
 
또는
 
Select empno, ename, job, sal
From emp
Where job in (select job from emp where ename='ALLEN');

2.EMP 테이블의 사원번호가 7521인 사원과 업무가 같고 급여가 7934인 사원보다 많은 사원의 사원번호,이름,담당업무,입사일, 급여
select empno, ename, job, hiredate, sal
from emp
where job =(select job from emp where empno = 7521) and 
sal < (select sal from emp where empno = 7934);
 
또는
 
select empno, ename, job, hiredate, sal
from emp
where job in (select job from emp where empno=7521) 
and sal > (select sal from emp where empno=7934);

3.EMP 테이블에서 급여의 평균보다 적은 사원의 사원번호,이름,업무,급여,부서번호
select ename, job, sal, deptno from emp 
where sal < (select avg(sal) from emp);
 
또는
 
select empno, ename, job, sal, deptno
from emp
where sal < (select avg(sal) from emp);

4.부서별 최소급여가 20번 부서의 최소급여보다 작은 부서의 부서번호,최소급여
select deptno, min(sal) from emp
group by deptno
having min(sal)<(select min(sal) from emp where deptno=20);
 
또는
 
select sal, deptno
from emp
where  sal < (select min(sal) from emp where deptno = 20 group by deptno);
 
또는
 
select d.deptno, min(e.sal)
from dept d, emp e
where d.deptno=e.deptno
group by d.deptno
having min(e.sal) < (select min(sal) from emp where deptno=20);

5.업무별 급여 평균 중 가장 작은 급여평균의 업무와 급여평균
select job, avg(sal)
from emp
group by job
having avg(sal) in (select amin from (select avg(sal) amin from emp group by job order by 1) where rownum=1);
 
또는
 
select job, avg(sal)
from emp
group by job
having avg(sal) in (select amin from (select avg(sal) amin from emp group by job order by 1) where rownum=1);

6.업무별 최대 급여를 받는 사원의 사원번호,이름,업무,입사일,급여,부서번호
select empno "사원번호", ename "이름", job "업무", hiredate "입사일", sal "급여", deptno "부서번호" from emp
where (job, sal) in (select job, max(sal) from emp group by job);
 
또는
 
select empno "사원번호", ename "이름", job "업무", hiredate "입사일", sal "급여", deptno "부서번호" from emp
where (job, sal) in (select job, max(sal) from emp group by job);

7.30번 부서의 최소급여를 받는 사원보다 많은 급여를 받는 사원의 사원번호, 이름, 업무, 입사일, 급여, 부서번호, 단 30번 부서는 제외
select empno, ename, job, hiredate, sal, deptno from emp
where sal > (select min(sal) from emp group by deptno having deptno = 30)
and deptno <> 30;
 
또는 
 
select ename, job, hiredate, sal, deptno
from emp
where sal > (select min(sal) from emp where deptno=30) and deptno != 30;

8.급여와 보너스가 30번 부서에 있는 사원의 급여와 보너스가 같은 사원을 30번 부서의 사원은 제외하고 출력하라.
select ename, sal, nvl(comm,-1) from emp
where (sal, nvl(comm,-1)) in (select sal, nvl(comm,-1) from emp where deptno = 30)
and deptno <> 30;
 
또는
 
select ename, sal, comm, deptno
from emp
where (sal, comm) in (select sal, comm from emp where deptno=30) and deptno!=30;

9.BLAKE와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력
select ename, hiredate from emp
where deptno = (select deptno from emp where ename = 'BLAKE');
 
또는 
 
select ename, hiredate, deptno
from emp
where deptno in (select deptno from emp where ename='BLAKE');

10.평균급여 이상을 받는 모든 사원에 대해 사원의 번호와 이름을 출력, 급여가 많은 순서로..
select empno, ename, sal from emp
where sal > (select avg(sal) from emp)
order by 3 desc;
 
또는
 
select empno, ename, sal
from emp
where sal >= (select avg(sal) from emp)
order by 3 desc;

11.이름에 T가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해 사원번호,이름,급여를 출력, 사원번호 순서로 출력
select empno, ename, sal from emp
where deptno in (select deptno from emp where ename like '%T%')
order by empno;
 
또는
 
select empno, ename, sal, deptno
from emp
where deptno in (select deptno from emp where ename like '%T%')
order by 1;

12.부서위치가 CHICAGO인 모든 사원에 대해 이름,업무,급여 출력
select deptno, ename, job, sal from emp e
where deptno = (select deptno from dept d where e.deptno = d.deptno and loc = 'CHICAGO');
 
또는
 
select e.ename, e.job, e.sal, d.loc
from emp e, dept d
where d.loc in (select loc from dept where loc='CHICAGO') and e.deptno=d.deptno;

13.KING에게 보고하는 모든 사원의 이름과 급여를 출력
select ename, mgr from emp
where mgr in (select empno from emp where ename = 'KING');
 
또는
 
select ename, sal
from emp
where mgr in (select empno from emp where ename='KING');

14.FORD와 업무와 월급이 같은 사원의 모든 정보 출력
select * from emp
where (job, sal) in (select job, sal from emp where ename = 'FORD');
 
또는
 
select *
from emp
where (job, sal) in (select job, sal from emp where ename='FORD');

15.업무가 JONES와 같거나 월급이 FORD 이상인 사원의 이름,업무,부서번호,급여 출력
select ename, job, deptno, sal from emp
where job = (select job from emp where ename = 'JONES') or sal > (select sal from emp where ename = 'FORD');
 
또는
 
select ename, job, deptno, sal
from emp
where job in (select job from emp where ename='JONES') or sal > (select sal from emp where ename='FORD'); 

16.SCOTT 또는 WARD와 월급이 같은 사원의 이름,업무,급여를 출력
select ename, job, sal from emp
where sal in (select sal from emp where ename in('SCOTT','WARD'));
 
또는
 
select ename, job, sal
from emp
where sal in (select sal from emp where ename='SCOTT') or sal in (select sal from emp where ename='WARD');

17.SALES에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무,급여,부서번호 출력
select ename, job, sal, deptno from emp
where deptno = (select deptno from dept where dname = 'SALES');
 
또는
 
select ename, job, sal, deptno
from emp
where deptno in (select deptno from dept where dname='SALES'); 

18.업무별로 월급이 평균 월급보다 낮은 사원의 부서번호,이름,급여,자신의 부서 평균급여를 출력(From절 서브쿼리)
select job, e.deptno, e.ename, e.sal, 부서평균급여 
from emp e, (select deptno, avg(sal) 부서평균급여 from emp group by deptno) d
where e.deptno(+) = d.deptno
and  e.sal  < (select avg(sal) from emp)
order by job;
 
또는
 
select e.job, e.deptno, e.ename, e.sal, d.dsal
from emp e, (select job, avg(sal) asal from emp group by job) s, (select deptno, avg(sal) dsal from emp group by deptno) d
where e.job=s.job and e.deptno=d.deptno and e.sal<s.asal;

19.사원번호,사원명,부서명,소속부서 인원수,업무,소속 업무 급여평균,급여를 출력하라.(WITH문)
with c as (select deptno, count(*) cnt from emp group by deptno), a as (select job, avg(sal) avg from emp group by job)
select e.empno, e.ename, d.dname, c.deptno, c.cnt, a.job, a.avg from emp e,dept d,c, a
where e.deptno = d.deptno and c.deptno = e.deptno and e.job = a.job;
 
또는
 
with a as(select e.empno, e.ename, e.deptno, e.job, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno),  
      b as (select deptno, count(*) deptcnt from emp group by deptno), -- 소속부서 인원수
      c as (select job, avg(sal) jobsal from emp group by job) -- 소속 업무 급여평균
select a.empno, a.ename, a.deptno, a.job, a.sal, a.dname, b.deptcnt, c.jobsal
from a, b, c
where a.deptno = b.deptno and a.job = c.job;

20.사원번호,사원명,부서번호,업무,급여, 자신의 소속 업무 평균급여를 출력하라.(상호연관 서브쿼리)
select e.empno, e.ename, e.deptno, e.job, e.sal, j.avg
from emp e, (select job, avg(sal) avg from emp group by job) j
where e.job = j.job;
 
또는
 
select e.empno, e.ename, e.deptno, e.job, e.sal, j.savg
from emp e, (select job, avg(sal) savg from emp group by job) j
where e.job = j.job(+);

21.최소한 한 명의 부하직원이 있는 관리자의 사원번호,이름,입사일자,급여 출력(EXISTS 이용)
select m.empno, m.ename, m.hiredate, m.sal from emp m
where exists(select e.mgr from emp e where e.mgr = m.empno);
 
또는
 
select empno, ename, hiredate, sal
from emp m
where exists (select empno from emp e where e.mgr=m.empno);

22.부하직원이 없는 사원의 사원번호,이름,업무,부서번호 출력
select m.empno, m.ename, m.hiredate, m.sal from emp m
where not exists(select e.mgr from emp e where e.mgr = m.empno);
 
또는
 
select empno, ename, job, deptno
from emp e
where not exists (select empno from emp m where m.mgr=e.empno);

23.BLAKE의 부하직원의 이름,업무,상사번호 출력
select ename, job, mgr from emp
where mgr = (select empno from emp where ename = 'BLAKE');
 
또는
 
select ename, job, mgr
from emp
where mgr in (select empno from emp where ename='BLAKE');

24.BLAKE와 같은 상사를 가진 사원의 이름,업무,상사번호 출력
select ename, job, mgr from emp
where mgr = (select mgr from emp where ename = 'BLAKE');

 
또는
 
select ename, job, mgr
from emp
where mgr=(select mgr from emp where ename='BLAKE');