데이터베이스 서브쿼리

문제: Abel이라는 사원이 받는 급여보다 더 많은 급여를 받는 사원의 이름과 급여를 구하세요
select last_name, salary
from employees
where last_name = 'Abel'                   where절에 조건을 걸어야 되는데 Abel의 급여를 모른다
그래서 먼저 이렇게 조회하고

select last_name, salary
from employees
where salary>11000;          이런 두번의 실행을 한번의 실행으로 -> 서브쿼리를 이용하면 가능


---서브쿼리: 특정값을 모르고 있을때 주로 사용 (select문장안에 또 다른 select문장)
select last_name, salary
from employees
where salary > (select salary from employees
                 where last_name='Abel') -> 서브쿼리
 
서브쿼리 먼저 실행하고 그 결과를 이용해 메인쿼리를 실행한다.

group by 빼고 전부 서브쿼리 사용가능. select from where having order by 서브쿼리 사용가능


WHERE  column 연산자 값 으로 구성된 조건식

having avg(col) 연산자 값 으로 구성된 조건식       where와 having절에서 사용되는 서브쿼리는 값을 모를때 그값을 찾기위해 사용한다.

where와 HAVING절의 서브쿼리
- 서브쿼리는 항상 괄호로 묶는다.(메인쿼리와 구분을 위해)
- 비교 조건의 오른쪽에 서브쿼리를 넣는다.
- 서브쿼리는 select절에서 사용한 문법 다 사용가능
- 서브쿼리는 값의 역할을 하고 출력은 되지않으니까, order by는 쓸필요 없다.

단일행 서브쿼리: 값을 하나를 출력한다. 이때 연산자는 단일연산자을 쓴다. > < = <= 등
다중행 서브쿼리: 복수의 값을 출력한다.  이때는 복수연산자 in을 쓴다.

서브쿼리 먼저 작성하는게 낫다.

1. 내가 뭘 모르는지 찾자
2. 모르는 값을 서브쿼리를 통해 찾자
3. 모르는 값을 찾기 위해 서브쿼리에 어떤 컬럼을 통해 찾을것인지 결정
   (Abel의 급여)                 (select salary)
4. 서브쿼리가 넘겨주는 컬럼의 값을 메인쿼리의 어떤 컬럼에서 받을 것인지 결정 (where salary)
5. 넘겨주는 값이 한개인지 복수인지에 따라 연산자를 결정
6. 서브쿼리의 컬럼과 받아주는 컬럼의 데이터타입과 길이가 반드시 같아야 한다. 단 컬럼이름은 달라도 상관없음

select last_name, job_id
from employees
where job_id = (select job_id from employees where employee_id = 141);


select last_name, job_id, salary
from employees
where job_id = (select job_id from employees where employee_id = 141)
    and salary > (select salary from employees where employee_id = 143);


select last_name, job_id, salary from employees
where salary = (select min(salary) from employees);

조인을 일부 서브쿼리가 대체 할 수있다.

IT라는 부서에 근무하는 사원의 이름과 급여를 출력하시오
select last_name, salary
from employees
where department_name = 'IT'   -> 'IT'라는 부서의 department_id를 모르는데 조인도 가능하고 서브쿼리도 가능하다.


select last_name, salary
from employees
where department_id = ( select department_id from departments where department_name='IT');


복습할때 코드 한줄씩 해석해보자


-HAVING절에서의 서브쿼리
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (select min(salary) from employees where department_id =50);


 
select employee_id, last_name
from employees
where salary = (select min(salary) from employees group by department_id);
 →  어디가 잘못되어있나?

              서브쿼리에서 부서별 최소급여로 여러개의 결과가 나오는데 단일연산자라서
 = -> in으로 바꾼다.

where salary in (select min(salary) from employees group by department_id);
 
select last_name, job_id
from employees
where job_id = (select job_id from employees where last_name = 'Haas');

Haas란 사람이 없어서 null값인데 null값은 is null로 받을수 있다-> job_id is null  


복수연산자
IN 
ANY 값을 서브쿼리에 의해 반환된 각 값과 비교한다.
all 



     서브쿼리 결과 (30,40)가 나왔을때
10            in(30,40) -> 30,40
20          >all(서브쿼리 결과전체 최대값보다 큰값을 찾는다.    따라서 50,60을 반환
30          <all(서브쿼리 결과전체 최소값보다 작은값을 찾는다.   따라서 10,20을 반환
40
50          >ANY(서브쿼리 결과전체 최소값 보다 큰값을 찾는다.)   따라서 40,50,60을 반환
60          <ANY(서브쿼리 결과전체 최대값 보다 작은값을 찾는다.)   따라서 10,20,30을 반환


select employee_id, last_name, job_id, salary
from employees
where salary <any(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';

->여기서 서브쿼리 값은 9000 6000 4200이 나오는데

>all 9000보다 큰값을
<all 4200보다 작은값을
>any 4200보다 큰값을
<any 9000보다 작은값을


부서별 최고 급여를 받는 사원의 이름과 급여를 출력하시오
select last_name, salary
from employees
where salary in(select max(salary)
                                 from employees
                                 group by department_id);  -> 이건 한 부서의 최대급여가 다른부서에선 최대급여가 아닐수도있어서 
                                                   부서별 최대급여에 부서 최대급여가 아니지만 다른부서 최대급여와 같은인원도 껴있다.

  
select last_name, salary
from employees
where (department_id, salary) in(select max(salary)
                  from employees
  group by department_id)         ->오라클 책 16장 정도에 있다.
  

문제: Zlotkey와 동일한 부서에 근무하는 다른 모든 사원들의 사번 및 고용날짜를 출력하시오.
select employee_id, hire_date
from employees
where department_id in (select department_id from employees where last_name = 'Zlotkey')
and last_name !='Zlotkey'; 
       and로 Zlotkey는 빼줘야한다.
   department_id =서브쿼리 는 중복이 있을때 오류가 나니까 in으로 복수일 경우도 생각해줘야한다
      column의 성격에 따라 중복이 있을수도 없을수도 있는데 이름은 중복이 있을 수도 있다는걸 생각.   


문제: 회사 전체 평균 급여보다 급여를 더 많이 받는 사원들의 사번 및 이름을 출력하시오
select employee_id, last_name
from employees
where salary > (select avg(salary) from employees);


문제: 이름에 u가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 이름을 출력하시오
select employee_id, last_name
from employees
where department_id in(select department_id from employees where last_name like '%u%');

          -> 서브쿼리로 이름에 u를 포함하는 사원을 찾는게 아니라 이름에 u를 포함하는 사원들의 부서를 찾아야한다.
  
  

문제: 141번 사원과 동일한 업무를 수행하는 사원의 이름과 업무를 출력하시오
select last_name, job_id
from employees
where job_id=(select job_id from employees where employee_id=141);


문제: Ernst와 동일한 부서에 근무하는 사원중 급여가 5000보다 큰 사원의 이름과 급여를 출력하시오
select last_name, salary
from employees
where department_id in (select department_id from employees where last_name = 'Ernst')
and salary >5000;
               이때 사원의 이름은 중복 가능성이 있어서 =가 아니라 in을 사용했다.


문제: 이름에 t를 포함하고 있는 사원과 같은 부서에 근무하는 사원의 이름과 사원번호와 부서번호를 출력하시오
select last_name, employee_id, department_id
from employees
where department_id in(select department_id from employees where last_name like '%t%');    



문제: 시애틀에 근무하는 사람 중 커미션을 받지않는 모든 사원들의 이름, 부서명, 지역ID를 출력하시오 
(조인과 서브쿼리를 병행 테이블은 emp, dept만 써라)
select e.last_name, d.department_name, d.location_id
from employees e, departments d
where e.department_id = d.department_id and d.location_id = (select location_id from locations where city='Seattle')
and e.commission_pct is null; 


-> emp 와 dept는 조인을 통해서 테이블을 이어줬기때문에 where절에서 조인에 대한 조건식이 나와야한다.
 그리고 시애틀에 근무하는 사람을 찾기위해서 (조인말고 서브쿼리로) locations에서 Seattle의 location_id를 찾는다.
 추가 조건인 커미션을 받지 않는사람 -> commission_pct is null
 
 
 
 
 문제: 이름이 Davies인 사원보다 후에 고용된 사원들의 이름 및 고용일자를 출력하시오. 고용일자를 역순으로 출력하시오.
 select last_name, hire_date
 from employees
 where hire_date > (select hire_date from employees where last_name='Davies')
 order by hire_date desc;
 
 
 문제: King을 매니저로 두고 있는 모든 사원들의 이름 및 급여를 출력하시오
 select last_name, salary
 from employees
 where mangager_id in(select employee_id from employees where last_name = 'King');
 
                       여기서 서브쿼리 select문에 manager_id를 쓰면 King의 매니저 아이디를 찾는것 
  -> 여기서 찾는건 King을 매니저로 두고 있는 사원들을 찾으려고 하는거니까
     King의 employee_id를 찾아서 mangager_id와 같은 경우를 찾으면
   King을 매니저로 두고 있는 사원들을 찾을수 있다.



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

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