서브 쿼리(sub Query)

1. 서브 쿼리란?

• main Quer에 반대되는 개념으로 이름을 붙인것
  -메인 쿼리를 구성하는소단위 쿼리
• select, insert, delete, update 절에서 모두 사용 가능
• 서브 쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용
• 서브 쿼리 자체는 일반 쿼리와 다를 바가 없음

예시 코드

-- 서브 쿼리
-- 평균 급여보다 적게 되는 사람
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEES e 
;
SELECT *
FROM EMPLOYEES e 
WHERE e.SALARY < (SELECT ROUND(AVG(SALARY))FROM EMPLOYEES e)
;
-- 1400, 1600, 1700
SELECT LOCATION_ID 
FROM LOCATIONS l 
WHERE COUNTRY_ID = 'US'
;
SELECT *
FROM DEPARTMENTS d 
WHERE LOCATION_ID IN (SELECT LOCATION_ID 
						FROM LOCATIONS l 
						WHERE COUNTRY_ID = 'US')
;

-- 월급이 가장 적은 사원
SELECT MIN(SALARY) FROM EMPLOYEES e;
SELECT *
FROM EMPLOYEES e 
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES e)
;

-- 월급이 가장 많은 사원
SELECT MAX(SALARY) FROM EMPLOYEES e;
SELECT *
FROM EMPLOYEES e 
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES e)
;

-- 평균 급여보다 많이 받는 사원들
SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e;
SELECT *
FROM EMPLOYEES e 
WHERE SALARY > (SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e)
;

문제 코드

-- 1) EMPLOYEES 테이블에서 Kochhar의 급여보다 많은 사원의 정보를 사원번호,이름,담당업무,급여를 출력하라.
SELECT SALARY FROM EMPLOYEES e WHERE LAST_NAME = 'Kochhar';

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES e 
WHERE SALARY > (SELECT SALARY FROM EMPLOYEES e WHERE LAST_NAME = 'Kochhar')
;

-- 2) EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 사원번호,이름,담당업무,급여,부서번호를 출력하여라.  
SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e;

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, DEPARTMENT_ID 
FROM EMPLOYEES e 
WHERE SALARY < (SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e)
;

-- 3) EMPLOYEES 테이블에서 (가장 많은 사원이 속해 있는 부서 번호)와 사원수를 출력하라.
SELECT MAX(COUNT(*)) FROM EMPLOYEES e GROUP BY DEPARTMENT_ID;

SELECT DEPARTMENT_ID, COUNT(*) 
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
HAVING COUNT(DEPARTMENT_ID) = (SELECT MAX(COUNT(*)) FROM EMPLOYEES e GROUP BY DEPARTMENT_ID)
;

-- 4) EMPLOYEES 테이블에서 (사원번호가 123인 사원의 직업)과 같고 
-- (사원번호가 192인 사원의 급여(SAL))보다 많은 사원의 사원번호,이름,직업,급여를 출력하라.
SELECT JOB_ID FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 123; --st_man
SELECT SALARY FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 192; -- 4000

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, DEPARTMENT_ID, SALARY 
FROM EMPLOYEES e 
WHERE JOB_ID = (SELECT JOB_ID FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 123)
AND SALARY > (SELECT SALARY FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 192)
;

-- 5) EMPLOYEES 테이블에서 (50번 부서의 최소 급여)를 받는 사원보다 많은 급여를 받는 
-- 사원의 사원번호,이름,업무,입사일자,급여,부서번호를 출력하라. (단 50번 부서는 제외)
SELECT MIN(SALARY) FROM EMPLOYEES e WHERE DEPARTMENT_ID = 50;

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY, DEPARTMENT_ID 
FROM EMPLOYEES e 
WHERE SALARY > (SELECT MIN(SALARY) FROM EMPLOYEES e WHERE DEPARTMENT_ID = 50)
AND DEPARTMENT_ID != 50
;