[DBMS_13] 서브 쿼리 (SELECT 문장 안에 SELECT 중첩)

---

여러번의 DB접속이 필요한 상황을 한번으로 줄여 속도증가에 도움을 줄 수 있다.

서브쿼리를 사용하는 곳

1. WHERE , HAVING 절

예제) 

사원테이블에서 이름이 Michael이고 직종이 MK_MAN인

사원의 급여보다 초과해서 받는 사원들의 정보를

사번, 이름, 직종, 급여 순으로 출력

 

첫번째 select접근)

이름이 마이클이고 직종이 MK_MAN인 사원의 급여 구하기

SELECT salary FROM employees

WHERE first_name = 'Michael' AND job_id = 'MK_MAN';

두번째 select접근)

첫번째 select로 얻은 급여값 13000을 초과하는 직원을 조회

SELECT employee_id, first_name, job_id, salary

FROM employees

WHERE salary > 13000;

---

서브쿼리를 이용해 위의 두가지 SELECT문을 하나로 통합 )

SELECT employee_id, first_name, job_id, salary

FROM employees

WHERE salary >  (SELECT salary FROM employees

WHERE first_name = 'Michael' AND job_id = 'MK_MAN');

 

---

문제1)

사원테이블에서 사번이 111번인 사원의 직종과 같고,

/사번이 159번인 사원의 급여를 초과한

/ 사원들의 정보를 사번, 이름, 직종, 급여 순으로 출력

 

답)

SELECT employee_id, first_name, job_id, salary

FROM employees

WHERE

job_id = (SELECT job_id FROM employees WHERE employee_id = 111 )

AND

salary > (SELECT salary FROM employees WHERE employee_id = 159);

---

문제2)

사번이 150인 사원의 급여와 같은 급여를 받는

사원들의 사번, 이름, 급여를 출력

답)

SELECT employee_id, first_name, salary FROM employees

WHERE 

salary= (SELECT salary FROM employees WHERE employee_id = 150);

 

---

문제3)

급여가 평균 월급 이상인 사원들의 이름과 급여를 출력

답)

SELECT first_name, salary FROM employees
WHERE
salary >= (SELECT AVG(salary) FROM employees);

---

문제4) 

Bruce와 같은 부서에서 근무하고있는

사원들의 이름과 부서번호를 조회

답)

SELECT first_name, department_id FROM employees

WHERE

department_id = ( SELECT department_id FROM employees WHERE first_name = 'Bruce');

 

---

문제5)

137번 사원의 급여 이상이며 149 번 사원의 급여 이하인

사원들의 이름과 급여를 조회

답)

SELECT first_name, salary FROM employees

WHERE

salary

BETWEEN

(SELECT salary FROM employees WHERE employee_id = 137)

AND

(SELECT salary FROM employees WHERE employee_id = 149);

 

---

 문제6)

직종과 직종별 평균급여를 조회하되, 

평균급여가 Bruce사원의 급여를 초과하는 경우만 조회

 

* GROUP함수와 일반컬럼을 같이 조회하면 무조건 GROUP BY 사용!

* GROUP함수를 비교할때는 WHERE 대신 HAVING사용하기!

답)

SELECT job_id, AVG(salary) SAL_AVG FROM employees

GROUP BY job_id

HAVING 

AVG(salary) > ( SELECT AVG(salary) FROM employees WHERE fist_name = 'Bruce' )

ORDER BY SAL_AVG ;

---

문제7) 

사원 테이블에서 급여가 가장 적은 사원의 사번, 이름, 급여를 출력

답)

SELECT employee_id, first_name, salary FROM employees

WHERE 

salary = (SELECT min(salary) FROM employees);

 

---

문제8)

사번이 158번인 사원과 직종이 같고, 급여가 164번 사원 초과인 

사람들의 사번, 이름, 직종, 급여를 출력

 

답)

SELECT employee_id, first_name, job_id, salary FROM employees

WHERE 

job_id = (SELECT job_id FROM employees WHERE employee_id = 158)

AND 

salary > ( SELECT salary FROM employees WHERE employee_id = 164);

(164번 직원의 급여는 7200)

 

---

문제9)

사원테이블에서 직종이 SA_REP인 사원의 최소급여보다 적으면서(미만) 직종이 SH_CLERK이 아닌 

사원들의 이름, 직종, 급여를 출력

답)

SELECT first_name, job_id, salary FROM employees

WHERE

salary < ( SELECT MIN(salary) FROM employees WHERE job_id = 'SA_REP'  )

AND job_id != 'SH_CLERK' ;

 

32rows

---

문제10)

사원테이블에서 100번 부서의 최소 급여를 초과하는 ( 100번 부서의 최소 급여는 6900 )

다른 모든 부서의 번호와, 최소 급여를 출력

 

답)

SELECT department_id, MIN(salary) FROM employees
GROUP BY department_id, 
HAVING
MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 100);

---

문제11)

사원테이블에서 last_name 중에 'Bat'라는 단어를 포함하고 있는 

사원과 같은 부서에서 근무하는

사원의 사번, 부서번호, 이름을 출력

성에 'Bat'을 포함하는 사원의 부서번호는 80

답)

SELECT employee_id, department_id, first_name FROM employees
WHERE 
department_id = ( SELECT department_id FROM employees WHERE last_name LIKE('%Bat%') );

총 34명

 

---

 

---

2. SELECT나 DELETE 의 FROM 절

---

3. UPDATE의 SET절

---

4. INSERT INTO절