--사원테이블에서 사원들의 평균 월급보다 더 많은 급여를 받는 사원의 사번과 이름과 급여를 출력
select avg(sal) from emp; -- 2073 sub 
select *
from emp
where sal >2073; -- main 

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

--쿼리 작성 기준
--함수를 쓰다가 해결이 안되면 join을 하고 안되면 subquery를 사용
--1.single row subquery : subquery 의 결과가 1개의 row(단일값) : 한개의 값(단일 컬럼)
--2.multi row subquery : subquery 의 결과가 1개 이상의 row : 여러개의 값(단일 컬럼) >> = ,and, or이 안먹혀서 다른 연산자를 사용해야 한다.
--구분하는 이유 ( 사용되는 연산자가 차이)
--연산자(in,not in)(any,all) >> 다중 데이터
--in : sal=1000 or sal=2000 or or...
--all : sal > 1000 and sal > 2000 and ...
--any : sal > 1000 or sal > 2000 or ...
--문법(정의)
--1.subquery는 괄호 안에 있어야 한다.
--2.subquery는 단일 컬럼으로 구성
--3.subquery는 단독으로 실행 가능 해야 한다.

--subquery를 가지고 있는 sql문장
--1. subquery먼저 실행
--2. subqurey 의 결과 값을 가지고 main을 실행한다.

--사원 테이블에서 jones의 급여보다 더 많은 급여를 받는 사원의 사번, 이릅, 급여를 출력하세요
select sal from emp where ename ='JONES';

select empno,ename,sal
from emp
where sal>(select sal from emp where ename ='JONES');

select *
from emp
where sal in (select sal from emp where sal > 2000);
--where sal =2975 or sal = 2850 or sal =2450 or sal = 3000..... 

select *
from emp
where sal not in (select sal from emp where sal > 2000);
--where sal != 2975 and sal != 2850 and sal != .....

--부하직원이 있는 사원의 사번과 이름을 출력하세요
select * from emp e join emp m on e.empno = m.mgr;

select empno,ename
from emp
where empno in(select mgr from emp);
--where empno = 7839 or empno = 7566 or empno is null or empno = 7902

--부하직원이 없는 사원의 사번과 이름을 출력하세요
select empno,ename
from emp
where empno not in(select nvl(mgr,0) from emp); 
--where empno != 7839 and empno != 7566 and empno is not null....

--직속이 king인 사원의 사번, 이름, 직종, 관리자 사번을 출력하세요
select empno from emp where ename ='KING';
select empno,ename,job,mgr
from emp
where mgr  = (select empno from emp where ename ='KING');

--20번 부서의 사원 중에서 가장 많은 급여를 받는 사원보다 더 많은 급여를 받는 사원의 
--사번,이름,급여,부서번호를 출력하세요
select max(sal) from emp where deptno = 20;

select empno , ename , sal , deptno
from emp
where sal > (select max(sal) from emp where deptno = 20);

--집계함수가 subquery활용 ....
select *
from emp
where deptno in(select deptno from emp where job = 'SALESMAN')
and sal in(select sal from emp where job = 'SALESMAN');

/*
자기 부서의 평균 월급보다 더 많은 월급을 받는 사원의 
사번 이름 부서번호 부서별 평균 월급을 출력하세요
*/
select avg(sal) from emp;
select avg(sal) from emp where ename;
select deptno,avg(sal) as avgsal from emp group by deptno;

select e.deptno , e.ENAME, e.DEPTNO, trunc(s.avgsal,0)
from emp e join(select deptno,avg(sal) as avgsal from emp group by deptno) s
on e.deptno = s.deptno
where e.sal > s.avgsal;

--1. 'SMITH'보다 월급을 많이 받는 사원들의 이름과 월급을 출력하라.
select sal from emp where ename ='SMITH';
 
 select EMPNO,ENAME,sal
 from emp
 where sal >(select sal from emp where ename ='SMITH');
--2. 10번 부서의 사원들과 같은 월급을 받는 사원들의 이름, 월급,
-- 부서번호를 출력하라.
select sal from emp where DEPTNO = 10;
select ename,sal,deptno
from emp
where sal in(select sal from emp where DEPTNO = 10);

 
--3. 'BLAKE'와 같은 부서에 있는 사원들의 이름과 고용일을 뽑는데
-- 'BLAKE'는 빼고 출력하라.
select deptno from emp where ename = 'BLAKE';
select ename , HIREDATE
from emp
where deptno = (select deptno from emp where ename = 'BLAKE')
and ename != 'BLAKE';


--4. 평균급여보다 많은 급여를 받는 사원들의 사원번호, 이름, 월급을
-- 출력하되, 월급이 높은 사람 순으로 출력하라.
select avg(sal) from emp;
select EMPNO,ENAME,sal
from emp
where sal > (select avg(sal) from emp)
order by sal desc;
 
--5. 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고
-- 있는 사원의 사원번호와 이름을 출력하라.
select deptno from emp where ENAME LIKE '%T%';  

select empno,ename
from emp
where deptno in(select deptno from emp where ENAME LIKE '%T%');



--6. 30번 부서에 있는 사원들 중에서 가장 많은 월급을 받는 사원보다
-- 많은 월급을 받는 사원들의 이름, 부서번호, 월급을 출력하라.
--(단, ALL(and) 또는 ANY(or) 연산자를 사용할 것)
select max(sal) from emp where deptno = 30;
 
select ename,empno,sal
from emp
where sal >(select max(sal) from emp where deptno = 30);
 
--7. 'DALLAS'에서 근무하고 있는 사원과 같은 부서에서 일하는 사원의
-- 이름, 부서번호, 직업을 출력하라.
select e.deptno
from emp e join dept d
on e.DEPTNO = d.DEPTNO
where d.loc = 'DALLAS';

select ename,deptno,job
from emp
where deptno in (select deptno
from dept
where loc = 'DALLAS');

--8. SALES 부서에서 일하는 사원들의 부서번호, 이름, 직업을 출력하라.
select deptno,ENAME,JOB
from emp
where deptno in(select deptno from dept where dname='SALES');

 
--9. 'KING'에게 보고하는 모든 사원의 이름과 급여를 출력하라
--king 이 사수인 사람 (mgr 데이터가 king 사번)
select empno from emp where ename = 'KING';
select ename,sal
from emp
where mgr = (select empno from emp where ename = 'KING');
--10. 자신의 급여가 평균 급여보다 많고, 이름에 'S'가 들어가는
-- 사원과 
--동일한 부서에서 근무하는 모든 사원의 사원번호, 이름,
-- 급여를 출력하라.
select avg(sal) from emp;
select empno,ename,sal
from emp
where sal > (select avg(sal) from emp)
and deptno in (select deptno from emp where ename like '%S%');
 
--11. 커미션을 받는 사원과 부서번호, 월급이 같은 사원의
-- 이름, 월급, 부서번호를 출력하라.
select deptno from emp where comm is not null;
select ename,sal,deptno
from emp
where deptno in(select deptno from emp where comm is not null)
and sal in(select sal from emp where comm is not null);



--12. 30번 부서 사원들과 월급과 커미션이 같지 않은
-- 사원들의 이름, 월급, 커미션을 출력하라.
select deptno from emp where deptno = 30;
select ename , sal , comm
from emp
where sal not in (select deptno from emp where deptno = 30) 
and comm not in (select deptno from emp where deptno = 30 
and comm is not null);