[03-1] Oracle DML (SELECT) SUBQUERY

국비/Oracle

[03-1] Oracle DML (SELECT) SUBQUERY

박머루 2022. 5. 3. 20:58

서브쿼리 SUBQUERY

하나의 주된 SQL문 안에 포함된 또 하나의 SELECT문
메인 SQL문을 보조해주는 쿼리문

* 서브쿼리 구분
    서브쿼리를 수행한 결과값이 몇 행 몇 열이냐에 따라 분류

    - 단일행 서브쿼리 : 서브쿼리를 수행한 결과값이 오로지 1개일 때
    - 다중행 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행일때
    - 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 열일 때
    - 다중행 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행 여러 열일 때

    => 서브쿼리를 수행한 결과가 몇 행 몇 열이냐에 따라 사용가능한 연산자도 달라짐

-- 전체 사원의 평균급여보다 더 많은 급여를 받고 있는 사원드르이 사번, 이름, 직급코드 조회

SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY)
                FROM EMPLOYEE);

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

서브쿼리의 조회 결과값이 오로지 1개일 때
일반 연산자 사용 가능 (=, !=, >=, <)

-- 전 직원의 평균 급여보다 더 적게 받는 사원들의 사원명, 직급코드, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY < (SELECT AVG(SALARY)
                FROM EMPLOYEE); -- 결과값 1행 1열, 딱 1개 있는 값
                

-- 박머루 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서코드 , 급여 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
                FROM EMPLOYEE
                WHERE EMP_ID = '박머루');
                

-- 박머루와 같은 부서인 사원들의 사번, 사원명, 전화번호, 직급명 조회(단, 박머루 제외)
SELECT EMP_ID, EMP_NAME, PHONE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE DEPT_CODE LIKE(SELECT DEPT_CODE
                FROM EMPLOYEE
                WHERE EMP_NAME = '박머루')
    AND EMP_NAME != '박머루';
    
    
-- 부서별 급여 합이 가장 큰 부서 하나만을 조회, 부서코드, 부서명, 급여합 조회
SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE 
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
                     FROM EMPLOYEE
                     GROUP BY DEPT_CODE);

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

서브쿼리의 조회 결과값이 여러 행일 때

-- IN : 여러 개의 결과값 중에서 한 개라도 일치하는 값이 있는 경우

-- NOT IN : 일치하는 값이 아예 없는 경우

-- > ANY : 여러 개의 결과값 중 하나라도 클 경우

-- < ANY : 여러 개의 결과값 중 하나라도 작을 경우

-- ALL : 전체 비교

-- 1) 과장 직급의 급여 조회
SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장';   -- 2200000, 2500000, 3760000
                          -- 단일열 다중행

-- 2)위의 급여보다 높은 급여를 받는 직원들 조회(사번, 이름, 직급명, 급여)
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY > ANY(2200000, 2500000, 3760000);

-- 3) 합치기

SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY > ANY(SELECT SALARY
                 FROM EMPLOYEE
                 JOIN JOB USING(JOB_CODE)
                 WHERE JOB_NAME = '과장')
AND JOB_NAME = '대리';

-- 각 부서별 최고급여를 받는 사원의 이름, 직급코드, 급여 조회
-- 1) 각 부서별 최고급여
SELECT MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 2) 위의 급여를 받는 사원들 조회(사원의 이름, 직급코드, 급여)
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (SELECT MAX(SALARY)
                 FROM EMPLOYEE
                 GROUP BY DEPT_CODE);


-- 박머루 또는 박포도 사원과 같은 부서인 사원들 조회(사원명, 부서코드 , 급여)
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME IN ('박머루', '박포도');

SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE IN(SELECT DEPT_CODE
                FROM EMPLOYEE
                WHERE EMP_NAME IN ('박머루', '박포도'));


-- 사원 < 대리 < 과장 < 차장 < 부장
-- 과장 직급임에도 불구하고 모든 차장 직급의 급여보다 많이 받는 직원 조회 (사번, 이름, 직급명, 급ㅁ여)

SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '차장';

SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)                -- ANY : 하나라도 (AND)
WHERE SALARY > ALL(SELECT SALARY        -- ALL : 전체보다 커야함 (OR)
                    FROM EMPLOYEE
                    JOIN JOB USING(JOB_CODE)
                    WHERE JOB_NAME = '차장')
AND JOB_NAME = '과장';

3. 다중열 서브쿼리

조회 결과 값은 한 행이지만 나열된 컬럼 수가 여러 개일 때

-- 박머루 사원과 같은 부서코드 AND 직급 코드에 해당하는 사원들 조회

SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '박머루';

SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' AND JOB_CODE = 'J5';

-- 합치기
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE
                                 FROM EMPLOYEE
                                WHERE EMP_NAME = '박머루');


-- 박머루 사원과 같은 직급코드 ,같은 사수사번을 가진 사원들의
-- 사번, 이름, 직급코드, 사수사번 조회

SELECT JOB_CODE, MANAGER_ID
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE EMP_NAME = '박머루';

SELECT EMP_ID, EMP_NAME, JOB_CODE, MANAGER_ID
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID
                                FROM EMPLOYEE
                                JOIN JOB USING(JOB_CODE)
                                WHERE EMP_NAME = '박머루');

4. 다중행 다중열 서브쿼리

서브쿼리 조회 결과값이 여러 행 여러 컬럼일 경우

-- 각 직급별 최소급여를 받는 사원들 조회 (사번, 이름, 직급코드, 급여)
-- 1) 각 직급별 최소 급여 조회
SELECT JOB_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE;

-- 2) 위의 목록들 중에 일치하는 사원 // 사번, 이름, 직급코드, 급여
-- 이름
SELECT EMP_NAME
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY)
                               FROM EMPLOYEE
                           GROUP BY JOB_CODE);

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

SELECT NVL(DEPT_CODE, '부서없음'), MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

SELECT EMP_ID, EMP_NAME, NVL(DEPT_CODE, '부서없음'), SALARY
FROM EMPLOYEE
WHERE (SALARY, NVL(DEPT_CODE, '부서없음')) IN(SELECT MAX(SALARY), NVL(DEPT_CODE, '부서없음')
                      FROM EMPLOYEE
                 GROUP BY DEPT_CODE)
ORDER BY SALARY ASC;

5. 인라인 뷰 (INLINE - VIEW)

FROM 절에 서브쿼리 제시
서브쿼리를 수행한 결과인 RESULT SET 을 테이블 대신 사용

TOP-N 분석 : 데이터베이스상에 존재하는 자료 중 최상위 몇 개 자료를 보기 위해 사용

SELECT ROWNUM, EMP_NAME, SALARY     --3
FROM EMPLOYEE                       --1
WHERE ROWNUM <=5                    --2
ORDER BY SALARY DESC;               --4

SELECT ROWNUM, EMP_NAME, SALARY     --3
FROM (SELECT EMP_NAME, SALARY       --1
      FROM EMPLOYEE
      ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;                  --2


-- 각 부서별 평균급여가 높은 3개의 부서코드, 평균 급여 조회
SELECT ROWNUM, DEPT_CODE, ROUND("평균 급여")
FROM (SELECT DEPT_CODE, AVG(SALARY) "평균 급여"
      FROM EMPLOYEE
      GROUP BY DEPT_CODE
      ORDER BY AVG(SALARY) DESC)
WHERE ROWNUM <= 3;

6. 순위 매기는 함수

** SELECT 절에서만 사용 가능!!!

    RANK() OVER(정렬기준)
    DENSE_RANK() OVER(정렬기준)

    RANK() OVER : 공동 1등이 2명이라고 한다면 다음 순위를 3위로 하겠다
    DENSE_RANK()OVER : 공동 1위가 2명이라고 해도 그 다음 순위를 2위로 하겠다

-- 사원들의 급여가 높은 순서대로 순위를 매겻, 사원명, 급여, 순위 조회

SELECT EMP_NAME, SALARY, RANK()OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE;

-- 5위까지만 조회하겠다 ***********************************************잘기억하기!!~!~!~
SELECT *
FROM (SELECT EMP_NAME, SALARY, RANK()OVER(ORDER BY SALARY DESC) "순위"
     FROM EMPLOYEE)
WHERE 순위 <= 5;

저작자표시 비영리 변경금지

'국비 > Oracle' 카테고리의 다른 글

[04-1] Oracle DDL (CREATE) 제약조건, 서브쿼리(SUBQUERY) (0)	2022.05.06
[03-2] ORacle DDL (CREATE) 자료형, COMMENT ON COLUMN, INSERT INTO (0)	2022.05.03
[02-3] Oracle DML (SELECT) JOIN (0)	2022.05.02
[02-2] Oracle DML (SELECT) , GROUP BY (0)	2022.05.02
[02-1] Oracle DML (SELECT) 그룹함수 ☆☆ (0)	2022.05.02

현재글[03-1] Oracle DML (SELECT) SUBQUERY

#비전공자 #IT입문기록

LogicalOperator, CompoundAssignmentOperator, 식별자, println, LogicalNegationOperator, if-else, java, 출력메소드, identifer, HelloWorld, Promotion, 배열복사, 프로그래밍기초, ComparisonOperator, ArithmeticOperator, TernaryOperator, IndecreaseOperator, do-while, 탈출문, if-else if,

Today :
Yesterday :

일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

ㅇㅇ