--------------------------------------------------------------------------------------------------------------------------
-- 서브쿼리
--------------------------------------------------------------------------------------------------------------------------
-- 서브쿼리를 사용하지 않고, 전체 직원의 평균급여보다 급여를 많이 받는 직원의 아이디, 이름, 급여를 조회하기
-- 1. 전체 직원의 평균급여를 조회한다.
SELECT AVG(SALARY)
FROM EMPLOYEES;
-- 2. 조회된 평균급여보다 급여를 많이 받은 직원을 조회한다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 6485.1962; -- 1번에서 조회한 평균급여(6485.1962)
-- 서브쿼리를 사용해서 전체 직원의 평균급여보다 급여를 많이 받는 직원의 아이디, 이름, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEES); -- 실행결과가 한 행나오는것을 단일행 서브쿼리라고한다. (평균급여 AVG(SALARY) 한행만 조회된다.)
--------------------------------------------------------------------------------------------------------------------------
-- 단일행 서브쿼리
--------------------------------------------------------------------------------------------------------------------------
-- 'Neena'와 같은 부서에서 근무하고 있는 직원의 아이디, 이름을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME = 'Neena');
-- 전체 직원들 중에서 최저 급여를 받는 직원의 아이디, 이름, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY)
FROM EMPLOYEES);
-- 'Neena'가 입사한 해에 입사한 직원 중에서, 전체 직원의 평균급여보다 급여를 적게 받는 직원의 아이디, 이름, 입사일, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
FROM EMPLOYEES
WHERE FIRST_NAME = 'Neena')
AND SALARY < (SELECT AVG(SALARY)
FROM EMPLOYEES);
--------------------------------------------------------------------------------------------------------------------------
-- 다중행 서브쿼리
--------------------------------------------------------------------------------------------------------------------------
-- 직종 변경 이력이 있는 직원의 아이디, 이름, 현재 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (SELECT DISTINCT EMPLOYEE_ID -- DISTINCT - 중복된 행을 제거
FROM JOB_HISTORY);
-- 직종 변경 이력이 없는 직원의 아이디, 이름, 현재 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT DISTINCT EMPLOYEE_ID
FROM JOB_HISTORY);
-- 'Seattle'에서 근무중인 직원의 아이디, 이름을 조회하기 (서브쿼리)
-- 1. 'Seattle'의 소재지 아이디를 조회한다.
-- 2. 해당 소재지 아이디에 위치한 부서의 아이디를 조회한다.
-- 3. 해당 부서에 근무중인 직원을 조회한다.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID -- 다중행 서브쿼리
FROM DEPARTMENTS
WHERE LOCATION_ID IN (SELECT LOCATION_ID -- 단일행 서브쿼리 (같다 서브쿼리 한 에서는 IN을 적어도 된다.)
FROM LOCATIONS -- (크다 작다 에서는 단일,다 중을 구별해야된다.)
WHERE CITY = 'Seattle'));
-- 'Seattle'에서 근무중인 직원의 아이디, 이름을 조회하기(조인)
-- 조인으로 값을 구할 수 있으면 조인으로 구하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE L.CITY = 'Seattle'
AND L.LOCATION_ID = D.LOCATION_ID
AND D.DEPARTMENT_ID = E.DEPARTMENT_ID;
-- 80번 부서에 소속된 직원의 급여보다 급여를 많이 받는 사원의 아이디, 이름, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY -- 다중행 서브쿼리 > ALL은 서브쿼리의 조회결과값 전부에 대해서 큰 값일 때 TRUE로 판정한다.
FROM EMPLOYEES -- WHERE SALARY > ALL (SELECT SALARY ...)는 WHERE SALARY > (SELECT MAX(SALARY) ...)와 동일하다.
WHERE DEPARTMENT_ID = 80)
AND DEPARTMENT_ID != 80;
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY -- 다중행 서브쿼리 > ANY는 서브쿼리의 조회결과값 중에서 어느 하나의 값보다 큰 값일 때 TRUE로 판정한다.
FROM EMPLOYEES -- WHERE SALARY > ANY (SELECT SALARY ...)는 WHERE SALARY > (SELECT MIN(SALARY) ...)와 동일하다.
WHERE DEPARTMENT_ID = 80)
AND DEPARTMENT_ID <> 80; -- <> : 같지않다
--------------------------------------------------------------------------------------------------------------------------
-- HAVING 절에서 서브쿼리
--------------------------------------------------------------------------------------------------------------------------
-- 부서별 사원수를 조회했을때 사원수가 가장 많은 부서의 아이디와 사원수를 조회하기
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID);
-- 부서별 사원수를 조회했을때 사원수가 가장 많은 부서의 아이디와 사원수를 조회하기
-- WITH ~ AS는 쿼리에서 여러번 사용되는 조회결과를 미리 조회해서 메모리에 저장시키고, 테이블처럼 사용할 수 있도록 한다.
WITH EMPLOYEE_COUNT
AS (
SELECT DEPARTMENT_ID, COUNT(*) CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
)
SELECT DEPARTMENT_ID, CNT
FROM EMPLOYEE_COUNT
WHERE CNT = (SELECT MAX(CNT)
FROM EMPLOYEE_COUNT);
--------------------------------------------------------------------------------------------------------------------------
-- 다중 열 서브쿼리
--------------------------------------------------------------------------------------------------------------------------
-- 다중 열 서브 쿼리를 사용하지 않고, 145번 부서의 관리자와 같은 부서에 일하고, 같은 해에 입사한 직원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 145)
AND TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 145);
-- 다중 열 서브 쿼리를 사용해서, 145번 부서의 관리자와 같은 부서에 일하고, 같은 해에 입사한 직원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY')) IN (SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY')
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 145);
-- 각 부서에서 최저급여를 받는 직원의 아이디, 이름, 급여, 부서아이디를 조회하기
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, SALARY) IN (SELECT DEPARTMENT_ID, MIN(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID ASC;
--------------------------------------------------------------------------------------------------------------------------
-- 상호연관 서브쿼리
--------------------------------------------------------------------------------------------------------------------------
-- 각 부서에서 최저급여를 받는 직원의 아이디, 이름, 급여, 부서아이디를 조회하기
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.DEPARTMENT_ID, A.SALARY
FROM EMPLOYEES A
WHERE A.SALARY = (SELECT MIN(B.SALARY)
FROM EMPLOYEES B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID)
ORDER BY A.DEPARTMENT_ID;
-- 모든 부서의 부서아이디, 이름, 소속사원수를 조회하기(GRUOP BY, 인라인뷰, 포괄조인 사용)
SELECT Y.DEPARTMENT_ID, Y.DEPARTMENT_NAME, NVL(X.CNT, 0) CNT
FROM (SELECT DEPARTMENT_ID, COUNT(*) CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID) X, DEPARTMENTS Y
WHERE X.DEPARTMENT_ID(+) = Y.DEPARTMENT_ID
ORDER BY Y.DEPARTMENT_ID;
-- 모든 부서의 부서아이디, 이름, 소속사원수를 조회하기(상호연관 서브쿼리 사용)
-- * SELECT절에서 사용되는 서브쿼리를 특별히 스칼라 서브쿼리라고 한다.
-- * 스칼라 서브쿼리의 실행결과는 반드시 단일행, 단일열이어야 한다.
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, (SELECT COUNT(*)
FROM EMPLOYEES B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID) CNT
FROM DEPARTMENTS A
ORDER BY A.DEPARTMENT_ID;
학원 정리/데이터베이스
댓글