SQL 서브쿼리

서브쿼리 : 
 - 하나의 SELECT 문장의 절 안에 포함된 또하나의 SELECT 문장
 - 비교연산자의 오른쪽에 기술하며, 반드시 괄호로 둘러쌓아야함
 - 서브 쿼리는 메인 쿼리가 실행되기 전 한번만 실행 된다

 - 단일행 :  서브쿼리의 수행결과가 단 한줄만 return하는것
--- 사용 가능한 비교연산자 : = > >= < <= <>
 -  다중행 :  return 되는 결과가 하나이상의 행일때 사용
--- 사용 가능한 다중행연산자 : in any, some all exist

--뉴욕에서 근무하는 사원의 사원번호, 이름을 출력하시오
select empno, ename
from emp
where deptno=(  select deptno from dept
                where loc = 'NEW YORK');
                --//괄호 안을 '서브쿼리'라고 부른다.
 
--평균급여보다 많이 받는 사원의 사원번호, 이름, 급여를 출력하시오
select empno, ename, sal
from emp
where sal> (select avg(sal) 
             from emp);
 
------------▼설명생략▼------------
select avg(sal) from emp;
 
--clerk과 같은 부서에서 근무하는 사원의 이름과 부서번호를 출력하는 sql문을 출력
select * from emp;
 
select ename, deptno
from emp
where deptno = ( select deptno
                from emp
                where lower(ename) = 'clark');
                
select * 
from emp
where job = ( select job
            from emp
            where ename = 'CLARK');
            
SELECT ename, sal
from emp
where sal >= (select sal
                from emp
                where ename = 'CLARK');
 
SELECT ename, deptno
FROM emp
WHERE deptno = (SELECT deptno
                FROM dept
                WHERE loc = 'DALLAS');
select * from emp;
 
SELECT ename, sal FROM emp
WHERE deptno = (SELECT deptno FROM dept
                WHERE dname = 'SALES');
                
SELECT ename, sal FROM emp
WHERE mgr = (SELECT empno FROM emp
             WHERE ename = 'KING');
 
------------▲설명생략▲------------
--급여 3000이상인 사원 추출 하는 네가지 방법
SELECT empno
FROM emp
WHERE sal >= 3000;
 
SELECT empno, sal, ename
FROM emp
WHERE empno = 7839 or empno = 7902;
 
SELECT empno, sal, ename
FROM emp
WHERE empno in(7839,7902);
 
SELECT empno, sal, ename
FROM emp
WHERE empno in( SELECT empno FROM emp
                WHERE sal >= 3000);
 
 
--직급이 매니저인 사람의 속한 부서의
--부서번호, 부서명, 지역 출력
SELECT deptno, dname, loc
FROM dept
WHERE deptno in(SELECT deptno FROM emp
                WHERE job = 'MANAGER');
 
--부하직원이 있는 사원의 사원번호,이름, 급여, 입사일을 출력하세요
select empno, ename, sal, hiredate from emp
where empno in( select mgr from emp
            where mgr is not null);
 
--부하직원이 없는 사원의 사원번호, 이름, 급여, 입사일, 직책을 출력하세요
select empno, ename, sal, hiredate, job
from emp
where empno not in(select mgr
                    from emp
                    where mgr is not null);
 
--급여3000이상 받는 사원이 소속된 부서와 동일한 부서에서
--근무하는 사원의 이름, 급여, 부서를 출력하세요
select ename, sal, dname
from emp, dept
where emp.deptno = dept.deptno
    and emp.deptno in (select deptno from emp
                        where sal >=3000);
 
--부서별로 가장 급여를 많이 받는 사원의 사원번호, 이름, 급여, 부서번호 출력
select empno, ename, sal, deptno
from emp
where  sal in( select max(sal) from emp 
            group by deptno);
    --//틀린답이다    
 
----부서별로 가장 급여를 많이 받는 사원의 
--사원번호, 이름, 급여, 부서번호 출력
--문제를 다시 풀어보면,,,
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);
                
-- 위와 같은 결과.
-- 사용 빈도는 적다
select empno, ename, sal, deptno
from emp
where (deptno, sal) in( select deptno,max(sal) 
                from emp
                group by deptno);
 
 
--각 부서별로 평균급여보다 많이 받는 사원의
--부서, 사원번호, 이름, 급여, 입사일 출력
select deptno, empno, ename, sal, hiredate
from emp a
where sal > (select avg(sal)
                from emp b
                where a.deptno = b.deptno
                group by b.deptno );
  
 
--평균급여보다 많이 받는 사원의 
--부서코드, 이름, 급여, 입사일 출력
select deptno, ename, sal, hiredate
from emp
where sal > (select avg(sal)
            from emp);
            
 
--from에서 select 쓰는 방법
select *
from ( select ename,sal
        from emp join dept
            on emp.deptno = dept.deptno
            where dept.deptno = 30);
            
--각 사원의 이름과 급여, 급여의 평균급여의 차이를 출력하시오 
select empno, sal, 
        round((select avg(sal) from emp)) as "avg_sal",
        sal-round((select avg(sal) from emp)) as "diff"
from emp;
 
select e2.empno, e2.sal, e2.deptno,
        round((select avg(sal) from emp e1 
        where e1.deptno = e2.deptno
        group by deptno)) as avgdept
        from emp e2 group by e2.empno, e2.sal, e2.deptno;
 
select deptno, round(avg(sal))
from emp
group by deptno;
 
--영업 사원들의 최소 급여를 많이 받는 사원들의 이름과 급여와 직급
--(담당업무)를 출력하되 영업 사원은 출력하지 않습니다. 
select ename, sal
from emp
where sal > (select min(sal)
            from emp
            where job = 'SALESMAN')
    and deptno<>20;