[4일차]서브쿼리

## SQL - 서브쿼리

#서브쿼리
-쿼리 안의 내부 쿼리
-서브쿼리는 괄호로 감싼다.
-수행순서:서브쿼리 수행 => 메인쿼리에 전달
-서브쿼리의 결과값이 null 이면 메인쿼리의 결과값도 null 이다.
-사용위치
=>select:스칼라 서브쿼리:함수로 구현하여 사용하는 추세
=>where : 중첩 서브쿼리 : 조건식의 우항에 위치
=>from:IN-LINE 뷰 : 서브쿼리의 결과를 테이블 처럼 사용

Chen 보다 많은 급여를 받는 사원의 이름과 급여

SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY>( SELECT SALARY
FROM EMP 
WHERE LAST_NAME='Chen')
ORDER BY SALARY DESC;

-- 부서가 101번 사원과 같으며, 월급이 141번 사원보다 많은 직원의 이름과 급여를 검색

SELECT LAST_NAME,SALARY
FROM EMP
WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID
FROM EMP
WHERE EMPLOYEE_ID=101)
and SALARY>(SELECT SALARY
FROM EMP
WHERE EMPLOYEE_ID=141);

--우리 회사에서 월급을 가장 많이 받는 사원의 이름과 월급을 검색

SELECT LAST_NAME,SALARY
FROM EMP
WHERE =(SELECT MAX(SALARY)
FROM EMP);

--부서별로 최저금여 검색. 단, 60번 부서의 최저급여보다 큰값만 검색

SELECT DEPARTMENT_ID,MIN(SALARY) as 최저급여
FROM EMP
GROUP BY DEPARMENT_ID
HAVING MIN(SALARY)>(SELECT MIN(SALARY) 
FROM EMP 
WHERE DEPARTMENT_ID=60);

===============================================

오라클 서브 쿼리 다중행 연산자

--단일행 비교연산자:>,<,>=,<=,=,<>

--다중행 비교연산자:in,any(아무거나),all(모두)
  => 단일행비교연산자 any, 단일행비교연산자 all
  => >any,<all,in

-- 부서별 최고급여를 받는 사원의 이름과 급여
SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY=(SELECT MAX(SALARY)
FROM EMP
GROUP BY DEPARTMENT_ID);
===>에러
===>"single-row subquery more than one row"

select last_name, salary
from emp
where salary in (select max(salary)
from emp
group by department_id);

--'IT-PROG' 직군의 어떤 사원보다 작은 급여를 받는 사원의 정보

select last_name, salary, job_id
from emp
where salary < all (select salary
from emp
where job_id='IT_PROG');

==========================================

## TOP-N 알고리즘

-- FROM 절에 서브쿼리
-- IN-LINE VIEW : SQL 실행시 잠깐 생성되었다가 사라지는 테이블 형태
-- ROWNUM:의사컬럼 Pseudo-column
   테이블 사용시 일반컬럼처럼 사용하나 실제로 테이블에 존재하는 컬럼은 아니다

-- 급여를 10000 이상 받는 사원의 정보

select last_name, salary
from emp
where salary >= 10000
order by salary desc;

-- 급여를 가장 많이 받는 사원 10명
select last_name, salary
from (select last_name,salary
from emp
order by salary desc)
where rownum <= 10;

-- 급여를 가장 적게 받는 사원 10명
select last_name, salary
from (select last_name, salary
from emp
order by salary asc)
where rownum <= 10;

-------------------------------------------------
구매(구매번호,구매자,상품명,구매개수,구매금액,구매날짜)

1.2021년에 1000개 이상 팔린 상품명, 총 판매개수
select 상품명, sum(구매개수) as 총판매개수
from 구매
where extract(year from 구매날짜) = 2021
group by 상품명
having num(구매갯수)>=1000
order by 2 desc;

2.2021년 베스트 상품 10(상품명,총판매갯수)
select 상품명, sum(구매개수) as 총판매개수
from (select 상품명, sum(구매개수) as 총판매개수
from 구매
where extract (year from 구매날짜) = 2021
group by 상품명 
order by 구매개수,desc)
where rownum <=10;

3.작년에 베스트 상품 10
select 상품명,구매개수
from (select 상품명, sum(구매개수) as 총판매개수
from 구매
where extract(year from sysdate)-extract(year from 구매날짜)=1
group by 상품명
where 구매개수,desc)
where rownum <=10;

4.작년에 구매합계가 500000 원 이상인 구매자와 구매총합계
select 구매자, sum(구매금액) as 구매총금액
from 구매
where extract(year from sysdate)-extract(year from 구매날짜)=1
group by 구매자
having sum(구매금액) >= 500000
order by 2 desc;

-----------------------------------------------------------------
## with 절

--from 절에 사용하는 서브쿼리 많은 경우

with
e1 as (select 상품명,구매개수
from (select 상품명, sum(구매개수) as 총판매개수
from 구매
where extract(year from sysdate)-extract(year from 구매날짜)=1
group by 상품명
where 구매개수,desc)

select 상품명,총판매개수
from e1
where rownum <=10;

with
e1 as (select * from emp where department_id=60),
e2 as (select * from dept)

select e1.last_name, e2.department_name
from e1,e2
where e1.department_id=e2.department_id;

===========================

--서브쿼리 결과 값이 존재하는 경우
select *
from emp
where exists (select dname from dept where deptno=10);

--서브쿼리 결과 값이 존재하지 않는 경우
select