Database기초 6일차 <서브쿼리>

■ 서브쿼리

 

예제)

Abel 이라는 사원이 받는 급여보다 더 많이 받는 사원의 이름과 급여를 출력하시오

select salary
from employees
where last_name = 'Abel'

--> 11000

select last_name, salary
from employees
where salary >11000
/

두 문장을 합치면

select last_name, salary
from employees
where salary > (
select salary
from employees
where last_name = 'Abel')
/

으로 실행된다

 

쿼리안에 쿼리가 들어가는 것 ==> 서브쿼리

 

** 특정값을 모를때 사용한다

** 조인을 대체하기도 한다.

** 꽃중의 꽃으로 현업에서 자주 사용한다.

** group by절을 제외하고 전체 절에서 사용가능하다

** 조건절 where, having 은 값을 대체하기 위해 사용한다.

(조건문의 특정 값을 모를때 서브쿼리를 통해서 찾는다.)

 

* 서브쿼리 먼저 실행되고 메인쿼리가 실행된다!

 

● 서브쿼리 지침

-  서브쿼리는 괄호로 묶는다.

-  비교 조건의 오른쪽에 서브쿼리를 넣는다.

-  서브쿼리의 ORDER BY절에서 안쓰는게 좋다.

-  단일행 서브쿼리에는 단일행 연산자를 사용하고 다중행 서브쿼리에는 다중행 연산자(in)를 사용한다.

 

 

1. 내가 뭘 모르는지 찾자(Abel의 급여)

2. 모르는 걸 서브쿼리를 통해 찾자

3. 모르는 걸 찾기 위해 서브쿼리의 select list절에 어떤 컬럼을 사용할 것인지 결정(select salary -> Abel의 급여)

4. 서브쿼리가 넘겨주는 컬럼의 값을 메인쿼리의 조건문의 어떤 컬럼이 받을 것인지 결정하자.(where salary -> select salary) ->반드시 서브쿼리의 컬럼과 메인쿼리의 조건문의 컬럼은 데이터 타입이 같아야한다. 단, 컬럼의 이름은 달라도 상관없음(그러나 두개의 컬럼의 이름은 같은경우가 대부분임)

5. 서브쿼리의 결과가 하나이면 =, > 과 같은 단일 연산자를, 결과가 복수이면 in과 같은 복수 연산자를 사용해야 한다.

 

서브쿼리 결과(30,40)

in(30,40) => 30,40

 

>all : (결과전부(최댓값40)보다 큰값 =>50, 60

<all : (결과전부(최솟값30)보다 작은값=> 10, 20

>any : (최솟값(30)보다 큰값 -> 40, 50, 60

<any : (최댓값(40)보다 작은값-> 10, 20, 30

 

서브쿼리 예제)

1. Zlotkey와 동일한 부서에 근무하는 다른 모든 사원들의 사번 및 고용날짜를 출력하시오

실행 코드

select last_name, employee_id, hire_date
from employees
where department_id =
(select department_id
from employees
where last_name = 'Zlotkey')
/

오답내용 : 'Zlotkey'라는 이름이 중복이 있을수도 있기 때문에 단일연산자'='보다 복수연산자 in을 사용하여 식을 만들고

'Zlotkey'사원이 포함되면 안되기 때문에 'Zlotkey'의 이름은 빼야한다!

 

정답코드

select last_name, employee_id, hire_date
from employees
where department_id in
(select department_id
from employees
where last_name = 'Zlotkey')
and last_name != 'Zlotkey'

2. 회사 전체 평균 급여보다 더 급여를 많이 받는 사원들의 사번 및 이름을 출력하시오

실행(정답)코드

select last_name, employee_id
from employees
where salary >
(select avg(salary)
from employees)
/

3. 이름에 u가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 이름을 출력하시오

실행(정답) 코드

select last_name, employee_id
from employees
where department_id in
(select department_id
from employees
where last_name like '%u%')
order by employee_id
/

4. Ernst와 동일한 부서에 근무하는 사원중 급여가 5000보다 큰 사원의 이름과 급여를 출력하시오

정답코드

select last_name, salary
from employees
where department_id =
(select department_id
from employees
where last_name in 'Ernst')
and salary > 5000
/

오답내용 :  조건이 두개이기 때문에 and연산자를 사용했어야 했는데 서브쿼리를 두개사용하려고 하여 오답을 하게 되었다. 너무 어렵게 생각하려고 하다 오답이 생겼다.

급여가 500보다 큰것은 서브쿼리를 쓸 필요가 없다.

 

5. 이름에 t를 포함하고 있는 사원과 같은 부서에 근무하는 사원의 이름과 사원번호와 부서번호를 출력하시오

실습코드

select last_name, department_id, employee_id
from employees
where department_id in
(select department_id
from employees
where last_name like '%t%')
/

오답내용 : 이름에 t 가 들어있는 사람이 여러명이기 때문에 ' = '이 아닌 'in'을 사용해야 한다.

단일연산자와 복수연산자를 헷갈리지 말자.

 

6. 최저 급여를 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력하시오

실습(정답) 코드

select last_name, salary
from employees
where salary >
(select min(salary)
from employees)
/

7. 50번 부서의 평균급여보다 더 많은 급여를 받는 사원의 이름과 급여와 부서번호를 출력하시오

실습(정답)화면 출력

select last_name, salary, department_id
from employees
where salary >
(select avg(salary)
from employees
where department_id = 50)
/

8. 부서별 최대 급여를 받는 사원의 번호, 이름과 급여를 출력하시오

실행 코드

select department_id, employee_id, last_name, salary, 
from employees
where salary in 
(select max(salary)
from employees
group by department_id)
order by department_id
/

정답코드

select department_id, last_name, salary
from employees
where (department_id,salary) in 
(select department_id, max(salary)
from employees
group by department_id)
/

오답 내용 : 최대급여와 같은 급여를 찾는다는 말이 되어버려서 타부서 급여와 중복이 되었다.

수업때 배우진 않았지만 서브쿼리 조건절에 컬럼을 중복으로 하여 서브쿼리와 연결하여 하는방법이 있다.

 

9. 50번 부서의 최저 급여보다 더 많은 최저급여를 받는 부서별 최저 급여를 출력하시오

실행코드

select department_id, min(salary)
from employees
where salary > 
(select min(salary)
from employees
where department_id = 50)
group by department_id
/

정답 코드

select department_id, min(salary)
from employees
group by department_id
having min(salary) >
(select min(salary)
from employees
where department_id = 50)
/

오답 내용 : 나의 실행코드대로 하면 50번 부서까지 나와버린다.

부서별의 최저급여를 출력해야 하기 때문에 그룹별로 나누기전에 50번 부서의 최저급여를 도출한 뒤 그룹별 최소급여와 비교하여 group by의 조건절인 having절에 서브쿼리를 넣어야 한다.

 

10. 이름이 DAVIES인 사람보다 후에 고용된 사원들의 이름 및 고용일자를 출력하시오. 고용일자는 역순으로 출력하시오

실행(정답)코드

select last_name, hire_date
from employees
where hire_date>
(select hire_date
from employees
where last_name in 'Davies')
order by hire_date desc
/

11. King을 매니저로 두고있는 모든 사원들의 이름 및 급여를 출력하시오

실행 코드

select last_name, salary, manager_id
from employees
where manager_id in
(select employee_id
from employees
where last_name='King')
/

12. 회사 전체 평균 급여보다 더 많이 받는 사원들 중 이름이 u가 있는 사원들이 근무하는 부서에서 근무하는 사원들의 사번, 이름 및 급여를 출력하시오

실행(정답)코드

select employee_id, last_name, salary
from employees
where department_id in
	(select department_id
	from employees
	where last_name like '%u%'
	and salary >
		(select avg(salary)
		from employees))
/

PS. 마지막 문제는 가장어렵고 이해하기 어렵기 때문에 잘 읽어보고  조건 순서를 정해서 천천히 서브쿼리를 넣어보자!