[Oracle] SQL Class 9 - 숙제

 

SUBQUERY (SCOTT 계정으로!)

 

set linesize 120

 

1. 이름이 ALLEN인 사원과 같은 업무를 하는 사람의 사원번호, 이름, 업무, 급여

(정답1)

SQL> select empno, ename, job, sal from emp

          where job=(select job from emp where ename='ALLEN');
(정답2)

SQL> 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;

 

2. EMP 테이블의 사원번호가 7521인 사원과 업무가 같고 급여가 사원번호가 7934인 사원보다 많은 사원의 사원번호, 이름, 담당업무, 입사일, 급여

(정답1)

SQL> 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');

(정답2) 

SQL> 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 테이블에서 급여의 평균보다 적은 사원의 사원번호, 이름, 업무, 급여, 부서번호

(정답1)

SQL> select empno, job, sal, deptno from emp

           where sal<(select avg(sal) from emp);

 

4. 부서별 최소급여가 20번 부서의 최소급여보다 작은 부서의 부서번호, 최소급여

(정답1)

SQL> select empno, ename, job, sal, deptno from emp

          where sal in (select min(sal) from emp group by deptno);

(정답2)

SQL> 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);

(정답3)

SQL> select sal, deptno from emp

          where sal < (select min(sal) from emp where deptno = 20 group by deptno);

(정답4)

 

5. 업무별 급여 평균 중 가장 작은 급여평균의 업무와 급여평균

(작성1)

SQL> select deptno, job, sal, from emp

          where sal=(select min(avg(sal)) from emp group by job);

(정답2)
SQL> 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. 업무별 최대 급여를 받는 사원의 사원번호, 이름, 업무, 입사일, 급여, 부서번호

(정답1)

SQL> select empno, ename, job, deptno from emp

          where (job,sal) in (select job, max(sal) from emp group by job);

(정답2)

SQL> 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번 부서는 제외

(정답1)

SQL> select deptno, min(sal) from emp
          group by deptno
          having min(sal)>(select min(sal) from emp where deptno=30); 

(정답2)

SQL> 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;

(정답3)

SQL> select ename, job, hiredate, sal, deptno from emp

          where sal > (select min(sal) from emp where deptno=30) and deptno !=30;

 

8. 급여와 보너스가 30번 부서에 있는 사원의 급여와 보너스가 같은 사원을 30번 부서의 사원은 제외하고 출력하라.

(정답1)
SQL> select ename, sal, comm from emp group by deptno

           having sal+comm >(select sal+comm from emp where deptno=30);

(정답2)
SQL> 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;(정답3)
SQL> select ename, sal, comm, deptno from emp           where (sal, comm) in (select sal, comm from emp where deptno=30) and deptno !=30;

 

 

9. BLAKE와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력

(정답1)

SQL> select ename, hiredate from emp

          where deptno=(select deptno from emp where ename like ‘BLAKE’);
(정답2)

SQL > select ename, hiredate, deptno from emp

           where deptno in (select deptno from emp where ename='BLAKE');

 

10. 평균급여 이상을 받는 모든 사원에 대해 사원의 번호와 이름을 출력, 급여가 많은 순서로..

(정답1)

SQL> select deptno, ename, sal from emp

          where sal>(select avg(sal) from emp)

           order by sal desc;

(정답2)

SQL> select deptno, ename, sal from emp

          where sal>(select avg(sal) from emp)

           order by 3 desc;

(정답3)
SQL> select deptno, ename, sal from emp

          where sal>=(select avg(sal) from emp)

           order by sal desc;

 

11. 이름에 T가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해 사원번호, 이름, 급여를 출력, 사원번호 순서로 출력

(정답1)

SQL> select empno, ename, sal from emp

          where deptno in (select deptno from emp where ename like '%T%');

(정답2)
SQL> select empno, ename, sal, deptno from emp

           where deptno in (select deptno from emp where ename like '%T%'); 

           order by 1;

(정답3)
SQL> select empno, ename, sal, deptno from emp

           where deptno in (select deptno from emp where ename like '%T%'); 

           order by empno;

 

12. 부서위치가 CHICAGO인 모든 사원에 대해 이름, 업무, 급여 출력

(정답1) 

SQL> select ename, job from emp
           where job in (select job from emp e, dept d where e.deptno=d.deptno and loc='CHICAGO');

(정답2)
SQL> 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;

(정답3)

SQL> 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에게 보고하는 모든 사원의 이름과 급여를 출력

(정답1)

SQL> select ename, sal from emp
           where mgr in (select empno from emp where ename='KING');

 

 

14. FORD와 업무와 월급이 같은 사원의 모든 정보 출력

(정답1)

SQL> select * from emp
          where (job, sal) in (select job, sal from emp where ename='FORD') and ename != 'FORD';

(정답2)
SQL> select *from emp

          where (job, sal) in (select job, sal from emp where ename='FORD');

 

15. 업무가 JONES와 같거나 월급이 FORD 이상인 사원의 이름, 업무, 부서번호, 급여 출력

(정답1)

SQL> select ename, job, deptno, sal from emp
          where job in (select job from emp where ename='JAMES') or sal in (select sal from emp where ename = 'FORD');

(정답2)

SQL> 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');

 

16. SCOTT 또는 WARD와 월급이 같은 사원의 이름, 업무, 급여를 출력

(정답1)

SQL> select ename, job, sal from emp
           where sal in (select sal from emp where ename='SCOTT' or ename='WARD');

(정답2)
SQL> select ename, job, sa from emp

           where sal in (select sal from emp where ename='SCOTT') or sal in(select sal from emp where ename='WARD');

 

17. SALES에서 근무하는 사원과 같은 업무를 하는 사원의 이름, 업무, 급여, 부서번호 출력

(정답1)

SQL> select ename, job, sal, deptno from emp
          where deptno in (select deptno from dept where dname='SALES');

(정답2)

SQL> select ename, job, sal, deptno from emp
           where deptno = (select deptno from dept where dname = 'SALES');

 

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

(정답1)

SQL> select empno, ename, sal from emp e
          where sal < (select avg(sal) from emp where job = e.job);

(정답2)
SQL> 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;

(정답3)

SQL> 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;

 

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

(정답1)

SQL> select e.empno, e.ename, d.dname, (select count(*) from emp t
          where e.deptno=t.deptno) "소속부서 인원수", job, (select (avg(sal)) from emp t where e.job=t.job) "소속업무급여평균", e.sal from emp e, dept d where e.deptno=d.deptno;

(정답2)
SQL> 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;

(정답3)

SQL>  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. 사원번호, 사원명, 부서번호, 업무, 급여, 자신의 소속 업무 평균급여를 출력(hard)   // 상호연관 서브쿼리

(정답1)

SQL> select e.empno, e.ename, e.deptno, e.job, e.sal, (select (avg(sal)) from emp t where e.job=t.job) "소속업무 평균급여" from emp e, dept d where e.deptno=d.deptno;

(정답2)

SQL> 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;

(정답3)
SQL> 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. 최소한 한 명의 부하직원이 있는 관리자의 사원번호, 이름, 입사일자, 급여 출력

(정답1)

SQL> select distinct(e1.empno),e1.ename,e1.hiredate, e1.sal from emp e1, emp e2

           where e1.empno=e2.mgr;

(정답2)
SQL> 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. 부하직원이 없는 사원의 사원번호, 이름, 업무, 부서번호 출력

(정답1)

SQL> select empno, ename, job, deptno from emp
          where empno not in (select mgr from emp where mgr is not null);

(정답2)

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

(정답3)

SQL> 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); 

 

23. BLAKE의 부하직원의 이름, 업무, 상사번호 출력

(정답1)

SQL> select e.ename, e.job, e.mgr from emp e, emp m
           where e.mgr = m.empno and m.ename='BLAKE';

(정답2)
SQL> select ename, job, mgr from emp
           where mgr = (select empno from emp where ename = 'BLAKE');

 

 

24. BLAKE와 같은 상사를 가진 사원의 이름, 업무, 상사번호 출력

(정답1)

SQL> select ename, hiredate from emp

            where deptno not in (select mgr from emp where ename like ‘BLAKE’);

(정답2)
SQL> select ename, job, mgr from emp
           where mgr = (select mgr from emp where ename = 'BLAKE');

 

##남교수님 정답건

subquery답안1.txt

0.01MB

subquery답안2.txt

0.01MB