SQL - 서브쿼리

@markdown

## 서브쿼리

____

- 하나의 `SELECT` 쿼리 안에 포함된 또 하나의 `SELECT` 쿼리

- 서브 쿼리를 포함하고 있는 쿼리문을 메인 쿼리, 포함된 또 하나의 쿼리를 서브 쿼리라고 함

- 반드시 괄호로 둘러쌓아야 함

### 서브쿼리 사용이 가능 한 곳

____

 - SELECT (스칼라 서브쿼리)

 - FROM (인라인 뷰)

 - WHERE

 - HAVING

 - ORDER BY

 - INSERT VALUES

 - UPDATE SET

### 단일 행 서브 쿼리

____

- 단일 행(Single Row) 서브 쿼리는 수행 결과가 오직 하나의 행만을 반환하는 서브 쿼리

- `WHERE` 절에서는 단일 행 비교 연산자인 `=`, `>`, `>=`, `<`, `<=`,`<>`를 사용해야함

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

<pre><code class="sql" style="font-size:14px">select ename, sal

from emp

where sal >= (select sal from emp where ename = 'SCOTT')

</code></pre>

### 다중 행 서브 쿼리

____

- 서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리

- 다중 행 서브쿼리 연산자

  - `IN`, `=ANY` : 메인 쿼리의 비교 조건(`=` 연산자로 비교할 경우)이 서브 쿼리의 결과 중에서 하나라도 일치하면 참

  - `=ALL` : 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참

  - `EXISTS` : 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

- 부서별로 가장 급여를 많이 받는 사원의 정보 출력

- 멀티 칼럼인 경우 `IN` 연산자를 사용한다.

<pre><code class="sql" style="font-size:14px">select *

from emp

where (deptno, sal) in (

  select deptno, max(sal)

  from emp

  group by deptno

)

</code></pre>

- 직급(JOB)이 MANAGER인 사람의 속한 부서의 부서 번호와 부서명과 지역을 출력

<pre><code class="sql" style="font-size:14px">select *

from dept

where deptno in(

select deptno

from emp

where job = 'MANAGER'

)

</code></pre>

<pre><code class="sql" style="font-size:14px">>ALL (서브쿼리) : 서브쿼리 결과에서 가장 큰 값보다 큰 값

select *

from emp

where sal >=ALL(

select sal

from emp

where job = 'MANAGER'

)

</code></pre>

<pre><code class="sql" style="font-size:14px">>ANY (서브쿼리) : 서브쿼리 결과에서 가장 작은 값보다 큰 값

select *

from emp

where sal >=ANY(

select sal

from emp

where job = 'MANAGER'

)

</code></pre>

## 서브쿼리 연습

 - 'IT'부서에서 근무하는 직원들의 이름, 급여, 입사일을 조회

<pre><code class="sql" style="font-size:14px">select e.first_name as 이름, e.salary as 급여, e.hire_date as 입사일

from employees e

where e.department_id = (

select d.department_id

from departments d

where d.department_name = 'IT'

)

</code></pre>

- 'Alexander'와 같은 부서에서 근무하는 직원의 이름과 부서id를 조회

<pre><code class="sql" style="font-size:14px">select e.first_name as 이름, e.department_id as 부서ID

from employees e

where e.department_id in (

select e.department_id

from employees e

where e.first_name = 'Alexander'

)

</code></pre>

- 80번부서의 평균급여보다 많은 급여를 받는 직원의 이름, 부서id, 급여를 조회

<pre><code class="sql" style="font-size:14px">select e.first_name as 직원이름, e.department_id as 부서ID, e.salary as 급여

from employees e

where e.salary > (

select avg(e.salary)

from employees e

where e.department_id = 80

)

</code></pre>

- 'South San Francisco'에 근무하는 직원의 최소급여보다 급여를 많이 받으면서 50 번부서의 평균급여보다 많은 급여를 받는 직원의 이름, 급여, 부서명,

부서id를 조회

<pre><code class="sql" style="font-size:14px">select e.first_name as 직원이름, e.salary as 급여, d.department_name as 부서명, e.department_id as 부서ID

from employees e, departments d

where salary > (

select min(e.salary)

from locations l, departments d, employees e

where l.location_id = d.location_id

and e.department_id = d.department_id

and l.city = 'South San Francisco')

and salary > (

select avg(salary)

from employees

where department_id = 50)

</code></pre>

- 직원들의 이름, 입사일, 부서명을 조회하시오. 단, 부서가 없는 직원이 있다면 그 직원정보도 출력결과에 포함

그리고 부서가 없는 직원에 대해서는 '<부서없음>'이 출력(outer-join , nvl() )

<pre><code class="sql" style="font-size:14px">select e.first_name, e.hire_date, nvl(d.department_name, '<부서없음>')

from employees e left outer join departments d

using(department_id)

on e.department_id = d.department_id

</code></pre>

- 직원의 직책에 따라 월급을 다르게 지급하려고 한다. 직책에 'Manager'가 포함된 직원은 급여에 0.5를 곱하고 나머지 직원들에 대해서는 원래의 급여를 지급하도록 한다.

<pre><code class="sql" style="font-size:14px">select e.first_name, e.salary,

decode(substr(j.job_title, -7, 7), 'Manager', salary*0.5, salary) as 급여1,

case when j.job_title like '%Manager' then salary*0.5

else salary end as 급여2

from employees e join jobs j using(job_id)

</code></pre>

- 각 부서별로 최저급여를 받는 직원의 이름과 부서id, 급여를 조회 (서브쿼리 중에 멀티 컬럼인 경우 연산자는 in 사용)

<pre><code class="sql" style="font-size:14px">select e.first_name, e.department_id, e.salary

from employees e

where (e.department_id, salary) in (

select e.department_id, min(e.salary)

from employees e

group by e.department_id)

</code></pre>

- 자신의 부서의 평균급여보다 적은 급여를 받는 직원을 조회

 - 상관서브쿼리 : 메인쿼리와 서브쿼리 간에 조인한다.

<pre><code class="sql" style="font-size:14px">select e.first_name, e.salary, e.department_id, avgSal

from employees e, (select department_id, avg(salary) avgSal

from employees

group by department_id) emp

where e.department_id = emp.department_id

and e.salary < avgSal

</code></pre>

- 자신이 속한 부서의 평균 급여보다 급여가 적은 직원들을 대상으로 부서 이름 별 직원 수를 출력 (출력결과는 부서이름을 오름차순 정렬해야함)

<pre><code class="sql" style="font-size:14px">select d.department_name, count(*) 직원수

from employees e1, departments d, (select department_id, avg(salary) avgSal

  from employees

  group by department_id) e2

where e1.department_id = e2.department_id

and e1.salary < avgSal

and e1.department_id = d.department_id

group by d.department_name

order by 1

</code></pre>

- 각 직급별(job_title) 인원수를 조회하되 사용되지 않은 직급이 있다면 해당 직급도

출력결과에 포함시키고 직급별 인원수가 3명 이상인 직급만 출력결과에 포함시켜라

<pre><code class="sql" style="font-size:14px">select j.job_title, count(*) 인원수

from employees e right outer join jobs j using(job_id)

group by j.job_title

having count(*) >= 3

</code></pre>

- 각 부서별 최대급여를 받는 직원의 이름, 부서명, 급여를 조회

<pre><code class="sql" style="font-size:14px">select first_name, department_name, salary

from employees join departments using(department_id)

where (department_id, salary) in (

select department_id, max(salary)

from employees

group by department_id)

</code></pre>

- 직원의 이름, 부서id, 급여를 조회하시오. 그리고 직원이 속한 해당 부서의 최소급여를 마지막에 포함시켜 출력

 - 스칼라 쿼리

<pre><code class="sql" style="font-size:14px">select first_name, department_id, salary, (

select min(salary) from employees

where department_id = emp.department_id)

from employees emp

</code></pre>

- 인라인 쿼리

<pre><code class="sql" style="font-size:14px">select emp.first_name, emp.department_id, salary, emp2.minSal

from employees emp, (select department_id, min(salary) minSal

from employees

group by department_id) emp2

where emp.department_id = emp2.department_id

</code></pre>

## 인라인 뷰 연습

____

- 급여를 가장 많이 받는 상위 5명의 직원 정보를 조회

 - 가짜 칼럼(슈도 칼럼)

 - rownum : 1이 포함되지 않으면 가져올 수 없다.

<pre><code class="sql" style="font-size:14px">select rownum, first_name, salary

from (

select rownum, first_name, salary --2

from employees e --1

order by salary desc) --3

where rownum <= 5

</code></pre>

<pre><code class="sql" style="font-size:14px">select first_name, salary, rank() rk, over (order by salary desc) as ranking

from employees

</code></pre>

- 커미션을 가장 많이 받는 상위 3명의 직원 정보를 조회

<pre><code class="sql" style="font-size:14px">select *

from (

select *

from employees

order by commission_pct desc nulls last) e

where rownum <= 3

</code></pre>

- 월별 입사자 수를 조회하되, 입사자 수가 5명 이상인 월만 출력 (입사자 수가 상위 3개만 출력)

<pre><code class="sql" style="font-size:14px">select *

from (

select to_char(hire_date, 'mm')||'월' 월별, count(*) 인원수

from employees

group by to_char(hire_date, 'mm')

having count(*) >= 5

order by count(*) desc)

where rownum <=3

</code></pre>

- 년도별 입사자 수를 조회 단, 입사자수가 많은 년도부터 출력 (상위 3개만 출력하기)

<pre><code class="sql" style="font-size:14px">select *

from (

select to_char(hire_date, 'yyyy')||'년' 월별, count(*) "입사 인원"

from employees

group by to_char(hire_date, 'yyyy')

having count(*) >= 5

order by count(*) desc)

where rownum <=3

</code></pre>