데이터베이스 SQL 오라클 서브 쿼리문

--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
-- 서브 쿼리

-- 하나의 쿼리문 안에 포함되어 있는 또 하나의 쿼리문을 말함.

-- 쿼리문 안에 또 다른 쿼리문이 존재하는 것을 말한다.

-- 서브쿼리는 메인쿼리가 서브쿼리를 포함하는 종속적인 관계

-- 여러번 쿼리를 실행해서 얻을 수 있는 결과를 하나의 중첩된 쿼리문장으로 결과를 얻을 수 있게 해줌.

-- 주의사항
-- 1) 서브쿼리는 괄호로 묶어서 사용해야함
-- 2) 서브쿼리 안에서는 order by절은 사용할 수 없음.

-- 사용방법 : 안쪽에 있는 쿼리문을 실행 후, 그 결과 값으로 바깥쪽 쿼리문을 실행

-- emp테이블에서 이름이 'SCOTT'인 사원의 급여보다  : 서브쿼리
-- 더 많은 급여를 받는 사원의 사번, 이름, 담당업무, 급여를 보여주세요.  : 외부 쿼리

select empno, ename, job, sal 
from emp
where sal > 
(select sal
from emp
where ename = 'SCOTT');  -- SCOTT 사원의 급여 3000


-- [문제1] emp 테이블에서 평균급여보다 더 적게 받는 사원의
-- 사번, 이름, 담당업무, 급여, 부서번호를 화면에 보여주세요.
select empno, ename, job, sal, deptno from emp where sal < 
(select avg(sal) from emp) order by sal asc; 

-- [문제2] emp 테이블에서 사번이 7521인 사원과 담당업무가 같고,
-- 급여가 7934인 사원보다 더 많이 받는 사원의 사번, 이름, 담당업무,
-- 급여를 화면에 보여주세요.
select empno, ename, job, sal from emp
where job = (select job from emp where empno = 7521)  -- 담당업무 SALESMAN
and
sal > (select sal from emp where empno = 7934);  -- 7934의 급여

and 이용가능

-- [문제3] emp 테이블에서 담당업무가 'MANAGER' 인 사원의 최소급여보다
-- 적으면서, 담당업무가 'CLERK'은 아닌 사원의 사번, 이름, 담당업무,
-- 급여를 화면에 보여주세요.
select empno, ename, job, sal from emp
where sal < (select min(sal) from emp where job = 'MANAGER') 
and job != 'CLERK' order by sal asc;

-- [문제4] 부서위치가 'DALLAS' 인 사원의 사번, 이름, 부서번호, 
-- 담당업무를 화면에 보여주세요.
select empno, ename, deptno from emp where
deptno = (select deptno from dept where loc = 'DALLAS');


-- [문제5] member10 테이블에 있는 고객의 정보 중 마일리지가 가장 높은
-- 고객의 모든 정보를 화면에 보여주세요.
select * from member10 where mileage = (select max(mileage) from member10); 


-- [문제6] emp 테이블에서 'SMITH' 인 사원보다 더 많은 급여를
-- 받는 사원의 이름과, 급여를 화면에 보여주세요.
select ename, sal from emp where sal > (select sal from emp where ename = 'SMITH');


-- [문제7] emp 테이블에서 10번 부서 급여의 평균 급여보다 적은 급여를 받는
-- 사원들의 이름, 급여, 부서번호를 화면에 보여주세요.
select ename, sal, deptno from emp where sal <
(select avg(sal) from emp where deptno =10) order by sal asc;

-- [문제8] emp 테이블에서 'BLAKE'와 같은 부서에 있는 사원들의
-- 이름과 입사일자, 부서번호를 화면에 보여주되, 'BLAKE' 는 제외하고
-- 화면에 보여주세요.
select ename, hiredate, deptno from emp where deptno = 
    (select deptno from emp where ename = 'BLAKE') and ename != 'BLAKE';

-- [문제9] emp 테이블에서 평균급여보다 더 많이 받는 사원들의 사번,
-- 이름, 급여를 화면에 보여주되, 급여가 높은데서 낮은 순으로 화면에 보여주세요.
select empno, ename, sal from emp where sal > 
    (select avg(sal) from emp) order by sal desc; 

-- [문제10] emp 테이블에서 이름에 'T'를 포함하고 있는 사원들과 같은 부서에
-- 근무하고 있는 사원의 사번과 이름, 부서번호를 화면에 보여주세요.
select empno, ename, deptno from emp where deptno in
    (select deptno from emp where ename like '%T%');


-- [문제11] 'SALES' 부서에서 근무하고 있는 사원들의 부서번호,
-- 이름, 담당업무를 화면에 보여주세요.
select ename, job from emp where deptno = 
(select deptno from dept where dname = 'SALES');

-- [문제12] emp 테이블에서 'KING'에게 보고하는 모든 사원의
-- 이름과 급여, 관리자를 화면에 보여주세요.
select ename, sal, mgr from emp where mgr =
    (select empno from emp where ename = 'KING');   -- 사번 7839

-- [문제13] emp 테이블에서 자신의 급여가 평균급여보다 많고, 이름에
-- 'S' 자가 들어가는 사원과 동일한 부서에서 근무하는 모든 사원의 
-- 사번, 이름, 급여, 부서번호를 화면에 보여주세요.

select empno, ename, sal, deptno from emp where sal >
(select avg(sal) from emp)         -- 평균급여 2073
and deptno in
(select deptno from emp where ename like '%S%');

-- [문제14] emp 테이블에서 보너스를 받는 사원과 부서번호, 급여가 같은
-- 사원의 이름, 급여, 부서번호를 화면에 보여주세요.
select ename, sal, deptno from emp where deptno in 
(select deptno from emp where comm > 0)
and sal in
(select sal from emp where comm > 0);

-- [문제15] products 테이블에서 상품의 판매가격이 판매가격의
-- 평균보다 큰 상품의 전체 내용을 화면에 보여주세요.
select * from products where output_price >
    (select avg(output_price) from products);
    
    
-- [문제16] products 테이블에 있는 판매 가격에서 평균 가격 이상의
-- 상품 목록을 구하되, 평균을 구할 때 가격이 가장 큰 금액인 상품을
-- 제외하고 평균을 구하여 화면에 보여주세요.

select * from products where output_price >=
(select avg(output_price) from products
where output_price != (select max(output_price) from products));
서브쿼리문 두번 가능

-- [문제17] products 테이블에서 카테고리의 이름에 '에어컨' 이라는
-- 단어가 포함된 카테고리에 속하는 상품목록을 화면에 보여주세요.
select*from products where category_fk in  
(select category_fk from products where product_name like '%에어컨%');


-- [문제18] member10 테이블에 있는 고객 정보 중 마일리지가 가장 높은 
-- 금액을 가지는 고객에게 보너스 마일리지 5000점을 더 주어 고객명,
-- 마일리지, 마일리지+5000 점을 화면에 보여주세요.
select memname, mileage , mileage+5000 "추가마일리지" from member10
where mileage = (select max(mileage) from member10);