서브쿼리(SubQuery)

서브쿼리(SubQuery)란?

• Main Query에 반대되는 개념으로 이름을 붙인 것
• 메인쿼리를 구성하는 소단위 쿼리
• select, insert, delete, update절에서 모두 사용 가능.
• 서브쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용
• 서브쿼리 자체는 일반 쿼리와 다를 바가 없다.
• 서브쿼리 사용 이유 : 그룹으로 묶을경우 그룹컬럼과
• 그룹함수밖에 못쓰기 때문에.. 서브쿼리를 그룹으로 묶어주고
• 메인쿼리에선 모든 컬럼을 불러줄 수 있다.

-- 서브쿼리
SELECT	ROUND(AVG(SALARY)) FROM 	EMPLOYEES e ;

SELECT 	FIRST_NAME , SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY < 6462
;

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

-- ()안에 있는 select문이 서브쿼리
SELECT 	FIRST_NAME , SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY < (SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES e) ;
;

-- 나라의 ID가 US인 지역에 존재하는 부서를 조회
-- 내 풀이
SELECT 	d.DEPARTMENT_NAME 
FROM 	DEPARTMENTS d 
	LEFT outer JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID 
WHERE 	l.COUNTRY_ID = 'US'
;

-- 강사풀이
SELECT 	LOCATION_ID  
FROM 	LOCATIONS l
WHERE 	COUNTRY_ID = 'US'
;

SELECT 	*
FROM 	DEPARTMENTS d 
WHERE 	LOCATION_ID in(1400, 1500, 1600, 1700)
;

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	e.SALARY = (SELECT MIN(SALARY) 
FROM 	EMPLOYEES e )
;

-- 월급이 가장 많은 사람
SELECT 	MAX(SALARY) 
FROM 	EMPLOYEES e 
;
SELECT 	*
FROM 	EMPLOYEES e 
WHERE 	e.SALARY = (SELECT max(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 e2 
					WHERE 	LAST_NAME  = 'Kochhar')
;

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

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

-- 문제3) 업무별(job_id)로 최소 급여를 받는 사원의 정보를 
-- 사원번호,이름,업무,부서번호를 출력하여라. 
-- 단 업무별로 정렬하여라.
SELECT 	JOB_ID , MIN(SALARY) 
FROM 	EMPLOYEES e2 
GROUP BY 	JOB_ID
;

SELECT 	EMPLOYEE_ID , FIRST_NAME , JOB_ID , DEPARTMENT_ID , SALARY 
FROM 	EMPLOYEES e 
WHERE 	(JOB_ID, SALARY) IN (	SELECT 	JOB_ID , MIN(SALARY) 
								FROM 	EMPLOYEES e2 
								GROUP BY 	JOB_ID )
ORDER BY JOB_ID 
;

-- 문제4) EMPLOYEES 테이블에서 (가장 많은 사원)을 갖는 
-- MANAGER의 사원번호를 출력하라.
SELECT 	MANAGER_ID , COUNT(*) 
FROM 	EMPLOYEES e 
GROUP BY MANAGER_ID 
ORDER BY COUNT(*) DESC  
;

SELECT 	max(count(*))
FROM 	EMPLOYEES e 
GROUP BY MANAGER_ID 
;

SELECT 	MANAGER_ID , COUNT(*) 
FROM 	EMPLOYEES e 
GROUP BY MANAGER_ID 
HAVING 	COUNT(*) = (	SELECT 	max(count(*))
					FROM 	EMPLOYEES e 
					GROUP BY MANAGER_ID )		 
;

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

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

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