Oracle 기본정리 - sub query2(where절에 select 넣어보기)

Oracle 기본정리 - sub query2(where절에 select 넣어보기)

-scott 계정의 테이블 사용

--평균급여보다 많이 받는 사원의 사원번호, 이름, 급여를 출력

select empno, ename, sal
from emp
where sal > (select avg(sal) from emp);

--clark 같은 부서에서 근무하는 사원의 이름과 부서번호를 출력

방법1

select e2.ename, e2.deptno
from emp e1, emp e2
where e1.ename = 'CLARK'
AND e1.deptno = e2.deptno;

방법2
select ename, deptno
from emp
where deptno = (select deptno from emp where ename='CLARK');

--clark과 동일한 직급을 가진 사원을 출력

select ename
from emp
where job = (select job from emp where ename='CLARK');

--clark의 급여와 동일하거나 더 많이 받는 사원명과 급여 출력

select ename, sal
from emp
where sal < (select sal from emp where ename='CLARK');

--dallas에서 근무하는 사원의 이름, 부서 번호 출력

select ename, deptno
from emp e
where e.deptno = (select deptno from dept where loc = 'DALLAS');

--salse 부서에서 근무하는 모든 사원의 이름과 급여 출력

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

--직속상관이 king인 사원의 이름과 급여 출력

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

--sal 3천보다 같거나 큰 사람의 고용번호, 급여, 이름 출력

select empno,sal,ename
from emp
where empno in( select empno
                from emp
                where sal>=3000);

             
               
--부서별로 가장 급여를 많이 받는 사원의정보(사원번호,사원이름,급여,부서번호) 출력

select empno, ename, sal,deptno
from emp
where sal in(select max(sal) from emp group by deptno)
order by sal desc;

--직급이 manager인 사람의 속한 부서의 부서번호와 부서명과 지역 출력

select e.empno, d.dname, d.loc
from emp e inner join dept d
on e.deptno = d.deptno
where job in(select job
             from emp
             where job='MANAGER');

--부하직원이 있는 사원의 이름과 급여 입사일을 출력

select ename, sal, hiredate
from emp
where empno in(select mgr
               from emp);

       

--부하직원이 없는 사원의 사원번호 이름 급여 입사일 직책을 출력

select * from emp;
select empno, ename, hiredate, job
from emp
where empno not in(select mgr
                   from emp
                   where mgr is not null);

--연봉이 3000이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원의 이름, 급여, 부서를 출력

select e.ename, e.sal, d.dname
from emp e inner join dept d
on e.deptno = d.deptno
where e.deptno  in(select deptno
                from emp
                where sal >= 3000);

--부서별로 가장 급여를 많이 받는 사원의 사원번호, 이름, 급여, 부서번호를 출력

select empno, ename, sal,deptno
from emp e1
where sal in(select max(sal)
             from emp e2
             where e1.deptno = e2.deptno
             group by deptno)
order by sal desc;

--테이블만들어서 확인
 
create table tt
(
    empno number(3),
    ename varchar2(10),
    sal number(3),
    deptno number(3)
);
 
insert into tt values(1,'aaa',100,10);
insert into tt values(2,'aaa',120,10);
insert into tt values(3,'ddd',130,10);
insert into tt values(4,'ddd',120,10);
insert into tt values(5,'ddd',120,20);
insert into tt values(6,'ddd',130,20);
insert into tt values(7,'rrr',140,10);
 
--잘못된경우
select empno, ename, sal,deptno
from tt a
where sal in(select max(sal)
             from tt b
             group by deptno);
 
 
select sal,deptno
from tt a
where sal=130 or sal=140;
 
select max(sal)
from tt
group by deptno;
 
--방법1
select empno, ename, sal,deptno
from tt a
where sal in(select max(sal)
             from tt b
             where a.deptno = b.deptno
             group by deptno)
order by sal desc;
 
--방법2
select empno, ename, sal,deptno
from tt a
where (deptno,sal) in(select deptno,max(sal)
                      from tt b
                      where a.deptno = b.deptno
                      group by deptno)
order by sal desc;
 
 
 
--각 부서별로 평균급여보다 많이 받는 사원의 부서코드, 사원번호 이름 급여 입사일을 출력
select e1.empno, e1.ename, e1.sal ,e1.deptno, e1.hiredate
from emp e1
where sal > (select avg(sal)
             from emp e2
             where e1.deptno = e2.deptno
             group by e2.deptno)
order by sal desc;
 
 
 
--평균 급여보다 많이 받는 사원의 부서코드 사원번호 이름 급여 입사일을 출력
select empno, ename, sal, hiredate
from emp
where sal > (select avg(sal) from emp);
 
--부서별로 가장 급여를 많이 받는 사원의 사원번호 이름 급여 부서번호 출력
select empno, ename, sal ,deptno, hiredate
from emp e1
where sal in(select max(sal)
             from emp e2
             where e1.deptno = e2.deptno
             group by deptno)
order by sal desc;
 
--영업 사원들 보다 급여를 많이 받는 사원들의 이름과 급여와 직급(담당업무)를 출력하되 영업사원은 출력하지 않는다.
--방법1
select ename,sal,job
from emp
where sal > (select max(sal)
             from emp
             where job='SALESMAN')
order by sal desc;
 
--방법2
select ename,sal,job
from emp
where sal > ALL(select sal
                from emp
                where job='SALESMAN')
order by sal desc;
 
 
 
 
-- 영업사원들 보다 급여를 많이 받는 사원들의 이름과 급여를 출력하되 영업사원들은 제외하고 출력하시오
select ename,sal,job
from emp
where sal > (select max(sal)
             from emp
             where job='SALESMAN');
            
 
-- 직급이salesman인 사원이 받는 급여들의 최대급여보다 많이 받는 사원들의 이름과 급여를 출력하되 부서가 20번인 사원은 제외한다.
select ename, sal
from emp
where sal >(select max(sal)
            from emp
            where upper(job)='SALESMAN')
and deptno <> 20;
 
 
--11. 직급이 salesman인 사원이 받는 급여들의 최소급여보다 많이받는 사원들의 이름과급여를 출력하되 부서가 20인 사원은 제외한다.
--방법1
select ename, sal
from emp
where sal >(select min(sal)
            from emp
            where upper(job)='SALESMAN')
and deptno <> 20
order by sal asc;
 
--방법2
select ename, sal
from emp
where sal > any(select sal
            from emp
            where upper(job)='SALESMAN')
and deptno <> 20
order by sal asc;