Oracle 서브쿼리(SubQuery)

1. 서브쿼리(SubQuery)

• MainQuery에 반대되는 개념으로 이름을 붙인 거
• 메인쿼리를 구성하는 소단위 쿼리
• Select, Insert, Delete, Update절에서 모두 사용 가능
• 서브쿼리의 결과 집합을ㄹ 메인쿼리가 중간 결과값으로 사용
• 서브쿼리 자체는 일반쿼리와 다를바가 없다.

-- SubQuery
-- 평균 급여보다 작게 받는 사람
-- 6462

SELECT *
FROM   EMPLOYEES e 
WHERE  SALARY < (SELECT ROUND(AVG(SALARY)) 
				 FROM EMPLOYEES e)
;

SELECT * 
FROM DEPARTMENTS d
WHERE LOCATION_ID IN (SELECT LOCATION_ID  
					  FROM   LOCATIONS l 
			  		  WHERE  COUNTRY_ID = 'US')
;

-- 월급이 가장 적은 사원의 정보를 조회

SELECT *
FROM   EMPLOYEES e
WHERE  e.SALARY = (SELECT MIN(SALARY) FROM  EMPLOYEES e2)
;

-- 월급이 가장 많은 사원의 정보를 조회

SELECT *
FROM   EMPLOYEES e
WHERE  e.SALARY = (SELECT MAX(SALARY) FROM  EMPLOYEES e2)
;

-- 평균 급여보다 많이 받는 사원들의
-- employee_id, first_name, last_name, job_title 조회

SELECT EMPLOYEE_ID , e.FIRST_NAME , e.LAST_NAME, j.JOB_ID , j.JOB_TITLE 
FROM  EMPLOYEES e 
	LEFT OUTER JOIN JOBS j 
	ON e.JOB_ID = j.JOB_ID 
WHERE e.SALARY > (SELECT AVG(SALARY) 
				FROM EMPLOYEES e)
;

-- 문제1) EMPLOYEES 테이블에서 Kochhar의 급여보다 많은
-- 사원의 정보를 사원번호,이름,담당업무,급여를 출력하라.
-- 17000

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

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

SELECT EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY, d.DEPARTMENT_ID 
FROM   EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE e.SALARY < (SELECT AVG(SALARY) 
				  FROM EMPLOYEES e)

-- 문제3) EMPLOYEES 테이블에서 (가장 많은 사원이 속해 있는 부서 번호)와
-- 사원수를 출력하라.

SELECT  DEPARTMENT_ID , COUNT(DEPARTMENT_ID)
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 EMPLOYEE_ID , FIRST_NAME , JOB_ID , SALARY 
FROM   EMPLOYEES e 
WHERE  JOB_ID = (   SELECT JOB_ID 
					FROM   EMPLOYEES e 
					WHERE  EMPLOYEE_ID  = 123)	
AND 	SALARY >  (	SELECT SALARY 
					FROM EMPLOYEES e 
					WHERE EMPLOYEE_ID = 192)
;	

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

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