[MySQL] 서브쿼리 연습문제

문제풀기 요령 및 유의사항

 

 

1. 작성할 서브쿼리 부분을 먼저 파악하고 작성을 한다, 

파란색 - 서브쿼리 작성

 

2. GROUP BY, 그룹함수 사용시, select 로 불러올 column 값이 그룹화가 되는지 확인한다

 

3. FROM 절에 서브쿼리 작성시 꼭 alias 를 설정해줘야한다.

 

4. 조인과 서브쿼리를 같이 써야할때 조인을 서브쿼리에 쓰지말고 메인쿼리에 작성한다

 

-  조인과 FROM절 서브쿼리 일때 (서브쿼리) A, (다른 테이블) B, (다른테이블) C 로 메인 WHERE 절에 조인을 작성한다

 

5. 조인은 GROUP BY 절에도 들어갈 수 있다.

---

 

문제 1)

 

EMPLOYEES 테이블에서 kochhar 의 급여보다 급여가 많은 사원의 사원번호, 이름, 담당업무, 급여를 출력하라.

 

select employee_id, concat(last_name,' ',first_name) as 'name', job_id, salary
from employees
where salary >=
(   select salary
    from employees
    where last_name = 'Kochhar'	);

 

먼저  ' kochhar 급여 ' 정보를 알아내야 모든 각 사원의 급여와 비교할 수 있습니다. 서브쿼리를 통해 kochhar의 급여 정보르 얻어냅니다.

 

조건 :: 각 사원급여 > kochhar 급여(salary) 

WHERE salary > (서브쿼리 : kochhar 급여)

 

salary(kochhar) 가 서브쿼리를 통해 나와야합니다

 

서브쿼리 : select salary from employees where last_name = 'Kochhar'

 

 

---

문제 2)

 

EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 사원번호,이름,담당업무,급여,부서번호를 출력하여라.

 

select employee_id, concat(last_name,' ',first_name) as 'name', job_id, salary, department_id
from employees
where salary < (	
select avg(salary)
from employees	);

 

먼저  ' 급여의 평균 ' 정보를 알아내야 모든 각 사원의 급여와 비교할 수 있습니다. 서브쿼리를 통해 급여의 평균 정보를 얻어냅니다.

 

조건 ::  사원 급여(salary) < 급여의 평균 

WHERE salary > (서브쿼리: 급여의 평균)

 

급여의 평균이 서브쿼리를 통해 나와야합니다

 

서브쿼리 : select avg(salary) from employees

 

---

 

문제 3)

 

EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라

 

select department_id, min(salary)
from employees
group by department_id
having min(salary) > 
(   select min(salary)
    from employees
    where department_id = 100);

 

"100번 부서의 최소 급여보다"

 

먼저  ' 100번 부서의 최소급여 ' 정보를 알아내야 합니다. 서브쿼리를 통해 100번 부서의 최소급여 정보를 얻어냅니다.

 

"보다 최소 급여가 많은 다른 모든 부서"

각 부서의 최소급여 정보를 얻어내서 100번 부서의 최소급여와 비교하면 됩니다.

각 부서의 최소급여를 알기 위해서는 Group by 함수가 필요합니다! 또한 각 부서의 최소급여를 구하기 위해서는 Group by 함수의 조건 격인 Having 절을 사용해야합니다.

 

이때 서브쿼리는 where 절이 아니라 having 절에 위치하게 됩니다. 그룹함수(min)을 사용해서 나온 결과로 각 부서의 최소급여를 구하고 서브쿼리로 나온 100번부서의 최소급여와 비교할 수 있게 되기 떄문입니다.

 

 

조건 ::  각 부서의 최소급여 > 100번 부서의 최소급여

WHERE min(salary) > (서브쿼리: 100번 부서의 최소급여)

 

100번 부서의 최소급여 정보가 서브쿼리를 통해 나와야합니다. 또한 나온 결과는 각 부서의 최소급여인 min(salary)와 비교해야합니다/

 

서브쿼리 : select min(salary) from employees where department_id = 100

 

---

 

문제 4)

 

업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서번호를 출력하시오(+업무별로 오름차순)

 

select employee_id, first_name, job_id, department_id, salary
from employees
where (job_id, salary) in 
(   select job_id, min(salary) 
    from employees
    group by job_id)
order by job_id;

 

"업무별로 최소 급여를 받는 사원"

 

먼저  ' 업무별, 최소급여 ' 정보를 알아내야 합니다. 서브쿼리를 사용하는데 업무별이기 때문에 GROUP BY 를 사용합니다. 또한 업무별로 그룹화 하면 MIN() 그룹함수를 사용합니다.

 

"(~ 정보를 가지고) 사원번호, 이름, 업무, 부서번호를 출력하시오"

서브쿼리를 통해 가져온 정보와 EMPLOYEES 테이블과 일치할때 (WHERE IN) 그 사원의 정보를 출력합니다.

 

서브쿼리에서 그룹함수를 통해 업무별(job_id)로 최소급여 정보를 출력합니다, 서브쿼리만 실행하고 조회하면

job_id, min(salary) 두 컬럼이 나옵니다. 이 두 컬럼과 전체 employees 테이블과 비교해서 일치하는 사원의 정보(행, row)들을 조회합니다. 

 

 

조건 :: (전체테이블 job id, 전체테이블 salary ) in 

각 사원 업무, 급여가 서브쿼리로 가져온 업무별, 업무별 최소급여와 모두 일치하면 그 사원의 정보를 출력하는 것입니다!

 

 

서브쿼리 : select job_id, min(salary) from employees group by job_id

 

---

 

문제 5)

 

EMPLOYEES 과 DEPARTMENTS 두 테이블에서, 업무가 SA_MAN 인 사원의 이름, 업무, 부서명, 근무지를 출력하라

 

select first_name, job_id, d.department_id, department_name, city
from (  select first_name, job_id, department_id 
        from employees 
        where job_id = 'SA_MAN') e, departments d, locations l //locations 에 근무지 city가 있음
where e.department_id = d.department_id
	and d.location_id = l.location_id;

 

"두 테이블에서 업무가 SA_MAN 인 사원의 (정보)"

 

먼저  ' 두 테이블에서 업무가 SA_MAN 인 사원의 정보를 서브쿼리에서 가져옵니다.

 

서브쿼리를 어디에 배치할지 생각을 합니다. 서브쿼리를 FROM 절에 배치하여 데이터의 크기를 줄여 처리를 빠르게 할 수 있습니다. 단, 출력해야할 필요한 정보는 가져와야 하므로 first_name, job_id, department_id 컬럼을 가져옵니다.

그 다음에 세 테이블을 조인해 줍니다.

 

(++ EMPLOYEES, DEPARTMENTS 는 job_id 로 조인할수 있으며, DEPARTMENTS 와 LOCATIONS 는 location_id 로 조인할 수 있음)

 

조건 ::\ 두 테이블에서 업무(job_id)가 SA_MAN 인 사원의 정보

두 테이블 모두 JOB_ID 를 가지고 있으므로 둘 중 하나의 테이블에서만 job_id = 'SA_MAN' 조건을 통해 테이블을 가져와도 됩니다!, 서브쿼리가 FROM 절에 있으므로 alias 를 설정해줍니다 ( 서브쿼리 e )

 

 

	first_name	job_id	department_id	department_name	city
	EMPLOYEES, JOBS	EMPLOYEES, JOBS	EMPLOYEES, JOBS	EMPLOYEES, JOBS	LOCATIONS

city 정보가 LOCATIONS 테이블에 있으므로 세개의 테이블을 조인해야합니다.