2020년 11월 03일

업데이트: November 03, 2020

SUBQUERY

SELECT 문장 안에 포함된 또 다른 SELECT 문장으로 메인 쿼리가 실행되기 전 한 번만 실행된다. 비교 연산자의 오른쪽에 기술해야 하며 반드시 괄호()로 묶어야한다. 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 한다.

-- 1) 사원명이 '노옹철'인 사람의 부서코드 조회
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'; ---> 'D9'

-- 2) 부서코드가 'D9'인 직원들의 사번, 이름, 부서코드 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

-- 3) 1, 2번 SQL 한 번에 작성하기
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
                   FROM EMPLOYEE
                   WHERE EMP_NAME = '노옹철'); ---> 서브쿼리!


-- 전 직원의 평균 급여보다 많은 급여를 받고 있는 직원의 
-- 사번, 이름, 직급코드, 급여 조회
SELECT FLOOR(AVG(SALARY))
FROM EMPLOYEE;

-- 2) 직원들 중 급여가 평균 이상인 사원의 사번, 이름, 직급코드, 급여 조회
SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 3047662 ;

-- 3) 
SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY >= (SELECT FLOOR(AVG(SALARY))
FROM EMPLOYEE) ;

단일 행 서브쿼리(SINGLE ROW SUBQUERY)

서브쿼리의 조회 결과 행의 개수 1개 + 단일 열로 이루어진 서브쿼리이다. WHERE절에 사용 시 서브쿼리 앞에는 비교 연산자( >, <, >=, <=, =, !=, <>, ^= )를 사용한다.

-- 예제 1-1
-- 전 직원의 급여 평균보다 많은 급여를 받는 직원의 이름, 직급명, 부서명, 급여를
-- 직급 코드 내림차순으로 정렬하여 조회한다.
SELECT EMP_NAME, JOB_NAME, DEPT_TITLE, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE) 
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)
ORDER BY JOB_CODE  DESC;

 -- 예제 1-2
 -- 가장 적은 급여를 받는 직원의 사번, 이름, 직급명, 부서명, 급여, 입사일 조회
 SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, SALARY, HIRE_DATE
 FROM EMPLOYEE
 NATURAL JOIN JOB
 JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
 WHERE SALARY =  (SELECT MIN(SALARY) FROM EMPLOYEE);


-- 예제 1-3
-- 노옹철 사원보다 급여를 많이 받는 사원의 사번, 이름, 부서명, 급여 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID AND
SALARY > (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME = '노옹철');

-- ** 서브쿼리는 WHERE절 뿐만 아니라 SELECT, FROM, HAVING절에 작성 가능


-- 예제 1-4
-- 부서별(부서 없는 사람도 포함) 급여의 합계가 가장 큰 부서의 부서명, 급여 합 조회

-- 1) 부서별 급여 합 중 가장 큰 값을 조회 --> 17700000
SELECT MAX(SUM(SALARY))
FROM EMPLOYEE 
GROUP BY DEPT_CODE;

-- 2) 부서별 급여 합이 17700000원인 부서의 부서명과 급여 합 조회
SELECT DEPT_TITLE, SUM(SALARY) "급여 합"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = 17700000;


-- 3) 1, 2 합치기
SELECT DEPT_TITLE, SUM(SALARY) "급여 합"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
                      FROM EMPLOYEE 
                      GROUP BY DEPT_CODE);

다중 행 서브쿼리(MULTI ROW SUBQUERY)

서브쿼리의 조회 결과 행의 개수가 여러 개 + 단일 열로 이루어진 서브쿼리이다. 다중행 서브쿼리에는 일반 비교 연산자를 사용할 수 없다.

사용 가능한 연산자
- IN / NOT IN : 여러 개의 결과 값 중 하나라도 일치하는 / 일치하지 않는 값이 있다면 / 없다면
- ’> ANY, < ANY : 여러 개의 결과값 중 하나라도 큰 / 작은 경우
  –> 가장 작은 값 보다 큰가? / 가장 큰값보다 작은가?
- ’> ALL, < ALL : 여러 개의 결과값 중 모든 값보다 큰 / 작은 경우 –> 가장 큰 값보다 큰가? / 가장 작은 값보다 작은가?
- EXISTS / NOT EXISTS : 값이 존재하는가? / 존재하지 않는가?

-- 예제 2-1
-- 부서별 최고 급여를 받는 직원의 이름, 부서명, 직급명, 급여 조회

--1) 각 부서별 최고 급여 조회
SELECT MAX(SALARY) FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 2) 전체 사원 중 각 부서별 최고 급여와 일치하는 사원 조회
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME,  SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE SALARY IN(2890000, 3660000, 8000000, 3760000, 3900000, 2490000, 2550000);

SELECT EMP_NAME, DEPT_TITLE, JOB_NAME,  SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE SALARY IN(SELECT MAX(SALARY) FROM EMPLOYEE
GROUP BY DEPT_CODE);


-- 예제 2-2
-- 모든 사원에 대해 사번에 대해 사수 / 직원을 구분하여 사번, 이름, 부서명, 직급명, 구분(사수/직원)을 조회

-- 1) 관리자에 해당하는 사번을 모두 조회
SELECT DISTINCT MANAGER_ID FROM EMPLOYEE
WHERE MANAGER_ID IS NOT NULL;

-- 2) 관리자에 해당하는 직원의  사번, 이름, 부서명, 직급명, 구분(사수/직원)
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, '관리자' 구분
FROM EMPLOYEE
NATURAL JOIN JOB
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EMP_ID IN (SELECT DISTINCT MANAGER_ID 
                 FROM EMPLOYEE
                 WHERE MANAGER_ID IS NOT NULL);


-- 3) 직원에 해당하는 사원의 사번, 이름, 부서명, 직급명, 구분(사수/직원) 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, '직원' 구분
FROM EMPLOYEE
NATURAL JOIN JOB
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EMP_ID NOT IN (SELECT DISTINCT MANAGER_ID 
                     FROM EMPLOYEE
                     WHERE MANAGER_ID IS NOT NULL);
                                        
                                        
-- 4) 2, 3 조회 결과를 하나로 합치기
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, '관리자' 구분
FROM EMPLOYEE
NATURAL JOIN JOB
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EMP_ID IN (SELECT DISTINCT MANAGER_ID 
                 FROM EMPLOYEE
                 WHERE MANAGER_ID IS NOT NULL)
UNION
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, '직원' 구분
FROM EMPLOYEE
NATURAL JOIN JOB
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EMP_ID NOT IN (SELECT DISTINCT MANAGER_ID 
                     FROM EMPLOYEE
                     WHERE MANAGER_ID IS NOT NULL);
                                        

-- 5) SELECT 절에 서브쿼리 사용한 형태
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, 
    CASE WHEN EMP_ID IN (SELECT DISTINCT MANAGER_ID 
                         FROM EMPLOYEE
                         WHERE MANAGER_ID 
                         IS NOT NULL) THEN '관리자'
    ELSE '직원'
    END 구분                    
FROM EMPLOYEE
NATURAL JOIN JOB
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);


-- 예제 2-3 
-- 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는 직원의
-- 사번, 이름, 직급, 급여를 조회

--1) 직급이 '대리'인 직원의 사번, 이름, 직급명, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = '대리';

-- 2) 직급이 '과장'인 직원들의 급여 조회
SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장';


-- 3) MIN을 이용하여 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = '대리'
AND SALARY > (SELECT MIN(SALARY)
              FROM EMPLOYEE
              JOIN JOB USING(JOB_CODE)
              WHERE JOB_NAME = '과장');

--4) > ANY를 이용하여 과장 중 가장 적은 급여보다 초과해서 받는 대리 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = '대리'
AND SALARY > ANY (SELECT SALARY
                  FROM EMPLOYEE
                  JOIN JOB USING(JOB_CODE)
                  WHERE JOB_NAME = '과장');


-- 예제 2-4
-- 차장 직급을 가진 직원들 중 가장 많이 받는 차장보다 
-- 더 많이 받는 과장의 사번, 이름, 직급, 급여 조회
-- ( > ALL, < ALL )
SELECT EMP_ID, EMP_NAME,  JOB_NAME, SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = ' 과장' 
AND SALARY > ALL (SELECT SALARY FROM EMPLOYEE 
                  NATURAL JOIN JOB
                  WHERE JOB_NAME = '차장');

다중 열 서브쿼리

서브쿼리 SELECT절에 나열된 컬럼 수가 여러 개로 이루어진 서브쿼리이다.

-- 1) 퇴사한 여직원의 이름, 부서 코드, 직급 코드 조회
SELECT EMP_NAME, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE ENT_YN = 'Y' AND
SUBSTR(EMP_NO, 8, 1) = '2';

-- 2) 퇴사한 직원과 같은 부서, 같은 직급
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE 
  -- 같은 부서 
  DEPT_CODE = (SELECT DEPT_CODE
               FROM EMPLOYEE
               WHERE ENT_YN = 'Y' AND
               SUBSTR(EMP_NO, 8, 1) = '2')
    AND
    -- 같은 직급
    JOB_CODE =  (SELECT JOB_CODE
                 FROM EMPLOYEE
                 WHERE ENT_YN = 'Y' AND
                 SUBSTR(EMP_NO, 8, 1) = '2')
    AND 
    ENT_YN != 'Y';

-- 3) 다중열 서브쿼리로 작성
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE 
  -- 같은 부서 
   (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE
                            FROM EMPLOYEE
                            WHERE ENT_YN = 'Y' AND
                            SUBSTR(EMP_NO, 8, 1) = '2')
    AND
    ENT_YN != 'Y';

다중 행 다중 열 서브쿼리

서브쿼리의 조회 결과가 N개의 행, N개의 열로 이루어진 서브쿼리이다.

-- 본인 직급의 평균 급여를 받고 있는 직원의 
-- 사번, 이름, 직급코드, 급여를 조회
-- 단, 급여와 급여 평균은 만원 단위까지만 계산( TRUNC(컬럼명, -4) )

-- 1) 급여를 200, 600만 받는 직원을 조회
SELECT EMP_ID, EMP_NAME,  JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (2000000, 6000000);

-- 2) 직급별 평균 급여 (만원 단위까지만 계산)
SELECT JOB_CODE, TRUNC(AVG(SALARY),  -4)
FROM EMPLOYEE
GROUP BY JOB_CODE;  ---> 서브쿼리

-- 3) 1, 2 쿼리를 하나로 합침
SELECT EMP_ID, EMP_NAME,  JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, TRUNC(AVG(SALARY),  -4)
FROM EMPLOYEE
GROUP BY JOB_CODE);


-- 각 부서별 최고 급여를 받는 사원을 조회(단, 부서별 오름차순 정렬)
-- 사번, 사원명, 부서코드 , 급여

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE (NVL(DEPT_CODE, 0), SALARY) IN (SELECT NVL(DEPT_CODE,0), MAX(SALARY) FROM EMPLOYEE
GROUP BY DEPT_CODE)
ORDER BY DEPT_CODE;

-- 다중 열 서브쿼리 연산 시 NULL 데이터가 하나라도 있으면 조회되지 않음

상관 서브쿼리(상호연관 서브쿼리)

메인 쿼리가 사용하는 테이블 값을 서브쿼리가 이용해서 결과를 만든다. 메인 쿼리의 테이블 값이 변경되면 서브쿼리 결과 값도 변경되는 구조이다.

-- ** 상관쿼리는 먼저 메인쿼리의 한 행을 조회하고 
-- 해당 행이 서브쿼리의 조건을 충족하는지 확인하여 SELECT 진행

-- 예제 5-1
-- 관리자가 EMPLOYEE 테이블에 있는 직원의 사번, 이름, 부서명, 관리자 번호 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, MANAGER_ID
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
-- EXISTS : 서브쿼리에 해당하는 행이 적어도 한 개 이상 존재할 경우 충족되는 SELECT 가 실행됨
WHERE EXISTS (SELECT EMP_ID
              FROM EMPLOYEE M
              WHERE E.MANAGER_ID = M.EMP_ID);


-- 예제 5-2
-- 직급별 급여 평균보다 급여를 많이 받는 직원의
-- 이름, 직급 코드, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE E
WHERE SALARY > (SELECT AVG(SALARY)
                FROM EMPLOYEE M
                WHERE E.JOB_CODE = M. JOB_CODE);

스칼라 서브쿼리

SELECT절에 사용되는 단일행 서브쿼리(1행), SQL에서는 단일 값을 가르켜 ‘스칼라’라고 부른다.

-- 예제 6-1
-- 모든 사원의 사번, 이름, 관리자번호, 관리자명 조회
-- 단, 관리자가 없는 경우 '없음'ㅡ으로 표시
-- (스칼라 + 상관)

SELECT EMP_ID, EMP_NAME, MANAGER_ID,
        NVL((SELECT EMP_NAME
        FROM EMPLOYEE M
        WHERE E.MANAGER_ID = M.EMP_ID), '없음') "관리자명"
FROM EMPLOYEE E
ORDER BY 1;


-- 예제 6-2
-- 각 직원들이 속한 직급의 급여 평균을 조회 (소수점 아래 내림)
SELECT EMP_NAME, JOB_CODE,
    (SELECT FLOOR(AVG(SALARY))
    FROM EMPLOYEE M
    WHERE E.JOB_CODE = M.JOB_CODE)  "급여 평균"
FROM EMPLOYEE E;

인라인 뷰(INLINE VIEW)

FROM절에 서브쿼리를 사용한다. 서브쿼리가 만들 결과의 집합(RESULT SET)을 테이블 대신 사용한다.

SELECT 사번, 급여
FROM (SELECT EMP_ID 사번, EMP_NAME 이름, SALARY 급여
FROM EMPLOYEE
WHERE SALARY >= 3000000)
WHERE 급여 < 4000000;

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;

-- 예제 7-1 : 인라인 뷰를 이용한 TOP-N 분석

-- 전 직원 중 급여가 높은 상위 5명의
-- 순위, 이름, 급여 조회
-- * ROWNUM : 조회된 순서대로 1부터 1씩 증가하는 번호를 매기는 가상 컬럼\
SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE;

-- ROWNUM 주의사항1 : ORDER BY절 보다 먼저 해석되어 번호를 부여한다.
SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC;


SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE ROWNUM <= 5
ORDER BY SALARY DESC;


--> 인라인뷰 사용하기
SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT * FROM EMPLOYEE
ORDER BY SALARY DESC) ---> 인라인뷰를 이용하여 미리 SALARY 내림차순 정렬
WHERE ROWNUM <= 5;


-- 예제 7-2
-- 급여 평균이 3위 안에 드는 부서의 부서코드, 부서명, 급여 평균 조회
SELECT *  FROM
(SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY)) "급여 평균"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY "급여 평균" DESC)
WHERE ROWNUM <= 3;

WITH

서브쿼리에 이름을 붙여주고 사용 시 이름을 호출한다. 주로 인라인 뷰로 사용될 서브쿼리에 사용되며 사용하면 실행 속도가 빨라진다.

-- 전 직원의 급여 순위 TOP 10 조회

WITH TOP_SAL AS(SELECT EMP_ID, EMP_NAME, SALARY
                FROM EMPLOYEE
                ORDER BY SALARY DESC)
                                    
SELECT EMP_ID, EMP_NAME, SALARY
FROM TOP_SAL
WHERE ROWNUM <= 10;

RANK() OVER / DENSE_RANK() OVER

RANK() OVER : 동일한 순위 이후 등수를 동일한 인원 수만큼 건너 뛰고 순위를 계산한다.
DENSE_RANK() OVER : 동일한 순위 이후 등수를 건너 뛰지 않고 순위를 계산한다.

SELECT EMP_NAME, SALARY, 
    RANK() OVER(ORDER BY SALARY DESC) 순위 -- 급여 내림차순으로 순위 부여
FROM EMPLOYEE;

SELECT EMP_NAME, SALARY, 
    DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위 -- 급여 내림차순으로 순위 부여
FROM EMPLOYEE;

실습 문제

-- 1. 전지연 사원이 속해있는 부서원들을 조회하시오 (단, 전지연은 제외)
-- 사번, 사원명, 전화번호, 고용일, 부서명
SELECT EMP_ID, EMP_NAME, PHONE, HIRE_DATE, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME = '전지연')
AND NOT(EMP_NAME = '전지연');


-- 2. 고용일이 2000년도 이후인 사원들 중 급여가 가장 높은 사원의
-- 사번, 사원명, 전화번호, 급여, 직급명을 조회하시오.
SELECT EMP_ID, EMP_NAME, PHONE, SALARY, JOB_NAME, HIRE_DATE
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE 
WHERE EXTRACT (YEAR FROM HIRE_DATE) >= '2000');


-- 3. 노옹철 사원과 같은 부서, 같은 직급인 사원을 조회하시오. (단, 노옹철 사원은 제외)
-- 사번, 이름, 부서코드, 직급코드, 부서명, 직급명
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE M
JOIN JOB USING (JOB_CODE)
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE (JOB_CODE, DEPT_CODE) = (SELECT JOB_CODE, DEPT_CODE FROM EMPLOYEE E WHERE EMP_NAME = '노옹철')
AND NOT(EMP_NAME = '노옹철');


-- 4. 2000년도에 입사한 사원과 부서와 직급이 같은 사원을 조회하시오
-- 사번, 이름, 부서코드, 직급코드, 고용일
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT  DEPT_CODE, JOB_CODE FROM EMPLOYEE 
WHERE EXTRACT (YEAR FROM HIRE_DATE) = '2000');


-- 5. 77년생 여자 사원과 동일한 부서이면서 동일한 사수를 가지고 있는 사원을 조회하시오
-- 사번, 이름, 부서코드, 사수번호, 주민번호, 고용일
SELECT EMP_ID, EMP_NAME, DEPT_CODE, MANAGER_ID, EMP_NO, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, MANAGER_ID) = (SELECT DEPT_CODE, MANAGER_ID FROM EMPLOYEE
WHERE EMP_NO LIKE '77%' AND SUBSTR(EMP_NO, 8, 1) = '2');


-- 6. 부서별 입사일이 가장 빠른 사원의
-- 사번, 이름, 부서명(NULL이면 '소속없음'), 직급명, 입사일을 조회하고
-- 입사일이 빠른 순으로 조회하시오
-- 단, 퇴사한 직원은 제외하고 조회..
SELECT EMP_ID, EMP_NAME,  NVL(DEPT_TITLE, '소속없음'), JOB_NAME, HIRE_DATE
FROM EMPLOYEE
LEFT JOIN JOB USING (JOB_CODE)
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE HIRE_DATE < ANY (SELECT MIN(HIRE_DATE) FROM EMPLOYEE GROUP BY DEPT_CODE);



-- 7. 직급별 나이가 가장 어린 직원의
-- 사번, 이름, 직급명, 나이, 보너스 포함 연봉을 조회하고
-- 나이순으로 내림차순 정렬하세요
-- 단 연봉은 \124,800,000 으로 출력되게 하세요. (\ : 원 단위 기호)

SELECT EMP_ID, EMP_NAME, JOB_NAME, 
CEIL(MONTHS_BETWEEN (SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12) "나이",
TO_CHAR( (1 + NVL(BONUS ,0)) * SALARY * 12 , 'L999,999,999') 연봉
FROM EMPLOYEE
LEFT JOIN JOB USING(JOB_CODE)
WHERE (JOB_CODE, FLOOR(MONTHS_BETWEEN (SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12))
 = (SELECT MIN(FLOOR(MONTHS_BETWEEN (SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12)) 
 FROM EMPLOYEE  GROUP BY JOB_CODE);

Twitter Google+ LinkedIn

techhan

2020년 11월 03일

SUBQUERY

단일 행 서브쿼리(SINGLE ROW SUBQUERY)

다중 행 서브쿼리(MULTI ROW SUBQUERY)

다중 열 서브쿼리

다중 행 다중 열 서브쿼리

상관 서브쿼리(상호연관 서브쿼리)

스칼라 서브쿼리

인라인 뷰(INLINE VIEW)

WITH

RANK() OVER / DENSE_RANK() OVER

실습 문제

공유하기

댓글남기기

관련 글 더보기

[정올] 1658_최대공약수와 최소공배수 (JAVA)

[정올] 2809_약수 (JAVA)

[정올] 1402_약수 구하기 (JAVA)

[정올] 1071_약수와 배수 (JAVA)