230412 서른 일곱 번째 수업_SQL

mary·2023년 4월 17일
0

국비 수업

목록 보기
37/72

서브쿼리(SUBQUERY):

  • 하나의 SQL문 안에 포함된 또다른 SELECT문
  • 메인 SQL문을 위해 보조 역할을 하는 쿼리문
박정보 사원과 같은 부서에 속한 사원들의 이름 조회
--1. 박정보 사원의 부서코드 조회
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '박정보';
--2. 부서코드가 D9인 사원들의 이름 조회
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

--위의 단계를 하나의 쿼리문으로
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '박정보');
전 직원의 평균급여보다 더 많은 급여를 받는 사원들의 사번, 이름, 직급코드, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= (SELECT AVG(SALARY)
                    FROM EMPLOYEE);


서브쿼리 구분:

서브쿼리를 수행한 결과값이 몇행 몇열이냐에 따라 분류
서브쿼리의 종류가 무엇이냐에 따라 서브쿼리 앞에 붙는 연산자가 달라짐

  • 단일행 서브쿼리: 서브쿼리 조회 결과값이 오로지 1개일 때(한행 한열)
  • 다중행 서브쿼리: 서브쿼리 조회 결과값이 여러행일 때(여러행 한열)
  • 다중열 서브쿼리: 서브쿼리 조회 결과값이 여러열일 때(한행 여러열)
  • 다중행 다중열 서브쿼리: 서브쿼리 조회 결과값이 여러행 여러열일 때

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

서브쿼리 조회 결과값이 오로지 하나일 때
일반 비교연산자 사용가능
=, !=, >, =< 등

1) 전직원의 평균급여보다 급여를 더 적게 받는 사원들의 이름, 직급코드, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY < (SELECT AVG(SALARY)
                    FROM EMPLOYEE)
ORDER BY SALARY;
2) 최저급여를 받는 사원의 사번, 이름, 급여, 입사일 조회
SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY = (SELECT MIN(SALARY)
                FROM EMPLOYEE);
3) 박정보 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 급여 조회
SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
                FROM EMPLOYEE
                WHERE EMP_NAME = '박정보');

JOIN과 함께 단일행 서브쿼리

4) 박정보 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서코드, 부서명, 급여 조회

-->>오라클 전용 구문

SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE, SALARY
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID 
    AND SALARY > (SELECT SALARY
                  FROM EMPLOYEE
                 WHERE EMP_NAME = '박정보');

-->>ANSI 구문

SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE, SALARY
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID) 
WHERE SALARY > (SELECT SALARY
                  FROM EMPLOYEE
                 WHERE EMP_NAME = '박정보');
5) 구정하와 같은 부서원들의 사번, 이름, 전화번호, 부서명 조회(단, 구정하는 제외)
SELECT EMP_ID, EMP_NAME, PHONE, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID
    AND DEPT_CODE = (SELECT DEPT_CODE
                        FROM EMPLOYEE
                        WHERE EMP_NAME = '구정하')
    AND EMP_NAME != '구정하';
                        
SELECT EMP_ID, EMP_NAME, PHONE, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE DEPT_CODE = (SELECT DEPT_CODE
                        FROM EMPLOYEE
                        WHERE EMP_NAME = '구정하')
    AND EMP_NAME != '구정하';

GROUP BY와 함께 단일행 서브쿼리

6) 부서별 급여합이 가장 큰 부서의 부서코드, 급여합 조회
--6.1 부서별 급여합 중 가장 큰 값
SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE;
--6.2 부서별 급여합이 17700000인 부서 조회
SELECT DEPT_CODE, SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING SUM(SALARY)= 17700000;

--위의 쿼리문을 하나로
SELECT DEPT_CODE, SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING SUM(SALARY)= (SELECT MAX(SUM(SALARY))
                        FROM EMPLOYEE
                        GROUP BY DEPT_CODE);


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

서브쿼리 조회 결과값이 여러행일 때(여러행 한열)

  • IN 서브쿼리: 여러개의 결과값 중에서 한 개라도 일치하는 값이 있다면
  • '>' ANY 서브쿼리: 여러개의 결과값 중에서 "한개라도" 클 경우
    (여러개의 결과값 중에서 가장 작은값 보다 클 경우)
  • '<' ANY 서브쿼리: 여러개의 결과값 중에서 "한개라도" 작을 경우
    (여러개의 결과값 중에서 가장 작은값 보다 작을 경우)

비교대상 > ANY(값1, 값2, 값3)

=비교대상 > 값1 OR 비교대상 > 값2 OR 비교대상 > 값3
(가장 작은 값보다 크면 됨)

대리직급임에도 과장직급의 급여들 중 최소급여보다 많이 받는 사원의 사번, 이름, 직급명, 급여 조회 (IN)
SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
WHERE JOB_NAME = '과장'; -- 2200000, 2500000, 3760000


--직급이 대리이면서 급여값이 위 값들 중에서 하나라도 큰 사원의 위 조건 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리' 
    AND SALARY > ANY (2200000, 2500000, 3760000);
                
-- 위의 쿼리문을 하나로
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리' 
    AND SALARY > ANY (SELECT SALARY
                        FROM EMPLOYEE
                        JOIN JOB USING (JOB_CODE)
                        WHERE JOB_NAME = '과장');
-- MIN을 넣어 단일행 서브쿼리로도 가능
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리' 
    AND SALARY > (SELECT MIN(SALARY)
                        FROM EMPLOYEE
                        JOIN JOB USING (JOB_CODE)
                        WHERE JOB_NAME = '과장');
과장 직급임에도 차장 급여보다 더 많이 받는 사원의 사번, 이름, 직급명, 급여 조회

--ANY: 차장의 최소급여보다 많이 받는 과장
-- 비교대상 > 값1 OR 비교대상 > 값2 OR 비교대상 > 값3

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

--ALL: 차장의 최대급여보다 많이 받는 과장
--비교대상 > 값1 AND 비교대상 > 값2 AND 비교대상 > 값3

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


다중열 서브쿼리:

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

장정보 사원과 같은 부서코드, 같은 직급에 해당하는 사원들의 사원명, 부서코드, 직급코드, 입사일 조회

SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
  FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE --장정보의 부서코드 구하기
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '장정보')
    AND JOB_CODE = (SELECT JOB_CODE --장정보의 직급코드 구하기
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '장정보');
--위의 쿼리문을 다중열 서브쿼리로
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 EMP_ID, EMP_NAME, JOB_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '지정보');


다중행 다중열 서브쿼리:

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

1. 각 직급별 최소급여 금액을 받는 사원의 사번, 이름, 직급코드, 급여 조회

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY)
                                FROM EMPLOYEE
                                GROUP BY JOB_CODE);

2. 각부서별 최고급여를 받는 사원의 사번, 이름, 부서코드, 급여 조회

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


인라인 뷰(INLINE VIEW):

(VIEW:사용자한테 보여주는 가상테이블)
FROM절에 서브쿼리 작성
서브쿼리를 수행한 결과를 마치 테이블처럼 사용

사원들의 사번, 이름, 보너스포함연봉(별칭부여), 부서코드 조회

연봉이 NULL이 없도록 함
조건: 연봉이 3000만원 이상인 사원들만 조회

-WHERE절에 별칭 사용하고 싶으면 INLINE VIEW사용

SELECT *
  FROM (SELECT EMP_ID, EMP_NAME, (SALARY*NVL(1+BONUS,1))*12 연봉, DEPT_CODE
        FROM EMPLOYEE) --TABLE처럼 사용
WHERE 연봉 >= 30000000;
SELECT 이름, 연봉 --FROM의 테이블에 있는 컬럼만 가져올 수 있음(PHONE은 가져올 수X)
  FROM (SELECT EMP_ID, EMP_NAME 이름, (SALARY*NVL(1+BONUS,1))*12 연봉, DEPT_CODE
        FROM EMPLOYEE) --TABLE처럼 사용
WHERE 연봉 >= 30000000;

-INLINE VIEW를 주로 사용하는 예 => TOP-N분석(상위 몇위만 가져오기)

전직원 중 급여 상위 5위 조회

★ROWNUM : 오라클에서 제공해주는 컬럼, 조회된 순서대로 1부터 순번을 부여해주는 컬럼

SELECT ROWNUM, EMP_NAME, SALARY
  FROM EMPLOYEE;
SELECT ROWNUM, EMP_NAME, SALARY --ROWNUM으로 넘버링 된 값은 정렬 할당해도 바뀌지 않음
  FROM EMPLOYEE
ORDER BY SALARY DESC; --정렬은 맨 마지막에 실행하기 때문에. 그래서 INLINE VIEW 사용


--ORDER BY절이 다 실행된 결과로 ROWNUM을 부여한 후 5명 추출
SELECT ROWNUM, EMP_NAME, SALARY
  FROM (SELECT EMP_NAME, SALARY
        FROM EMPLOYEE
        ORDER BY SALARY DESC)
 WHERE ROWNUM <=5;
                  
--테이블 별칭 부여하여 간결하게        
SELECT ROWNUM, E.*
  FROM (SELECT EMP_NAME, SALARY
        FROM EMPLOYEE
        ORDER BY SALARY DESC) E        
 WHERE ROWNUM <=5;

연습문제: 가장 최근에 입사한 사원 5명의 사원명, 급여, 입사일 조회

SELECT ROWNUM, E.*
FROM (SELECT EMP_NAME, SALARY, HIRE_DATE
        FROM EMPLOYEE
        ORDER BY HIRE_DATE DESC) E
WHERE ROWNUM <= 5;

푸는 법 두 가지

연습문제: 각 부서별 평균급여가 높은 3개의 부서의 부서코드, 평균급여 조회

SELECT *
FROM (SELECT DEPT_CODE, CEIL(AVG(SALARY)) 평균급여
        FROM EMPLOYEE
        GROUP BY DEPT_CODE
        ORDER BY 2 DESC) E
WHERE ROWNUM <= 3;


WITH:

서브쿼리에 이름을 붙여주고 인라인 뷰로 사용시 서브쿼리의 이름으로 FROM 절에 기술

-장점
같은 서브쿼리가 여러번 사용될 경우 중복 작성을 피할 수 있고
실행속도도 빨라진다

WITH 서브쿼리명 AS (서브쿼리)

각 부서별 평균급여가 높은 3개의 부서의 부서코드, 평균급여 조회

WITH SAL3 AS (SELECT DEPT_CODE, CEIL(AVG(SALARY)) 평균급여
                FROM EMPLOYEE
                GROUP BY DEPT_CODE
                ORDER BY 2 DESC)
SELECT DEPT_CODE, 평균급여            
FROM SAL3
WHERE ROWNUM <= 3;


순위를 매기는 함수(WINDOW FUNCTION):

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

두 함수는 무조건 SELECT절에서만 사용!

-RANK() OVER(정렬기준): 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 순위 계산
EX) 공동 12위가 2명이면 그 다음은 14위
-DENSE_RANK() OVER(정렬기준): 동일한 순위 이후의 등수는 인원수와 상관없이 1을 증가시킨 순위
EX) 공동 12위가 2명이면 그 다음은 13위

급여가 높은 순서대로 순위를 매겨서 조회

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


20위 건너뛴 결과가 나옴

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


총 인원은 23명인데 22위까지 밖에 없음

급여가 상위 5위인 사원들의 이름, 급여, 순위 조회

SELECT *
  FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) 순위 
        FROM EMPLOYEE)
WHERE 순위 <= 5; --WHERE절에 RANK조건을 줄 수 없어서 INLINE VIEW를 통해 조회

WITH와 함께 사용

WITH TOP_SALARY AS (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) 순위 
                    FROM EMPLOYEE)
                    
SELECT 순위, EMP_NAME, SALARY
  FROM TOP_SALARY
WHERE 순위 <= 5;
profile
mary
내 인생을 망치러 온 나의 구원, 개발
이전 포스트

230407 서른 다섯 번째 수업_SQL

다음 포스트

230413 서른 여덟 번째 수업_SQL

0개의 댓글