[KH정보교육원] 19일차_SUBQUERY

2021.11.5(금)

1. 🌿 SUBQUERY

• 하나의 주된 SQL문 안에 포함된 또 하나의 SELECT문
• main SQL문의 보조 역할을 하는 쿼리문
• 서브쿼리를 수행한 결과값이 몇 행 몇 열이냐에 따라 구분 + 서브쿼리를 수행한 결과가 몇 행 몇 열이냐에 따라 사용 가능한 연산자도 달라짐

1.1. 🌱 단일행 (단일열) 서브쿼리

• 서브쿼리를 수행한 결과값이 오로지 1개일 때
• 일반 연산자(=, !=, >=, < 등) 사용 가능

      

      

      

    
    

      <code />
    
-- 문제 = 부서별 급여 합이 가장 큰 부서 하나의 부서코드, 부서명, 급여 합 조회
SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY)) -- HAVING절에도 서브쿼리 사용 가능
                      FROM EMPLOYEE
                      GROUP BY DEPT_CODE); -- 서브쿼리의 결과값 = 단일행; 17,700,000

SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+)
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY)) FROM EMPLOYEE GROUP BY DEPT_CODE); -- 서브쿼리의 결과값 = 17,700,000 = 단일행

1.2. 🌱 다중행 (단일열) 서브쿼리

• 서브쿼리의 조회 결과값이 여러 행일 때
• 사용 가능한 연산자
  • IN: 여러 개의 결과값 중에서 1개라도 일치하는 값이 있으면
    NOT IN: 아예 없으면
  • > ANY: 여러 개의 결과값 중에서 '하나라도' 클 경우 = 여러 개의 결과값의 최소값보다 큰 경우
    < ANY: 여러 개의 결과값 중에서 '하나라도' 작을 경우 = 여러 개의 결과값의 최대값보다 작은 경우
    (ANY는 OR와 비슷함)

      

      

      

    
    

      <code />
    
-- 문제 = 대리 직급임에도 불구하고 과장 직급의 급여보다 많이 받은 직원들의 사번, 이름, 직급명, 급여 조회
SELECT EMP_NO, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
LEFT JOIN JOB USING (JOB_CODE)
WHERE SALARY > ANY(SELECT SALARY FROM EMPLOYEE LEFT JOIN JOB USING (JOB_CODE) WHERE JOB_NAME = '과장') -- 서브쿼리의 결과값 = 다중행 단일열
 AND JOB_NAME = '대리';

• > ALL: 여러 개의 결과값 모두보다 큰 경우 = 여러 개의 결과값의 최대값보다 큰 경우
  < ALL: 여러 개의 결과값 모두보다 작은 경우 = 여러 개의 결과값의 최소값보다 작은 경우
  (ALL은 AND와 비슷함)

      

      

      

    
    

      <code />
    
-- 문제 = 과장 직급임에도 불구하고 모든(ALL) 차장 직급의 급여보다 많이 받은 직원들의 사번, 이름, 직급명, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
LEFT JOIN JOB USING (JOB_CODE)
WHERE SALARY > ALL(SELECT NVL(SALARY, 0)
                   FROM EMPLOYEE
                   LEFT JOIN JOB USING (JOB_CODE)
                   WHERE JOB_NAME = '차장') -- JOB_NAME이 '차장'인 사람들의 SALARY 조회하는 서브쿼리; 서브쿼리의 결과값 = 다중행 단일열; 2800000, 1550000, 2490000, 2480000
 AND JOB_NAME = '과장'; -- 대북혼

1.3. 🌱 (단일행) 다중열 서브쿼리

• 조회 결과 값은 1개 행이지만, 나열된 컬럼 수가 여러 개일 때
• 사용 가능한 연산자
  • (컬럼1, 컬럼2, ..) = (컬럼1 값, 컬럼2 값, ..)
  • (컬럼1, 컬럼2, ..) IN (컬럼1 값, 컬럼2 값, ..)

      

      

      

    
    

      <code />
    
-- 문제 = 박나라 사원과 같은 직급코드 및 같은 사수사번을 가진 사원들의 사번, 이름, 직급코드, 사수사번, 부서명 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, MANAGER_ID, DEPT_TITLE
FROM EMPLOYEE E, DEPARTMENT D
-- 단일행 다중열 result set이 나와야 함 = 박나라 사원의 JOB_CODE, MANAGER_ID
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '박나라') -- 서브쿼리의 결과값 = 단일행 다중열; J7, 207
 AND E.DEPT_CODE = D.DEPT_ID(+);

1.4. 🌱 다중행 다중열 서브쿼리

• 서브쿼리 조회 결과값이 여러 행 + 여러 컬럼일 경우
• 사용 가능한 연산자: (컬럼1, 컬럼2, ..) IN (컬럼1 값, 컬럼2 값, ..)

      

      

      

    
    

      <code />
    
-- 문제 = 각 직급별 최소 급여를 받는 사원들의 사번, 이름, 직급코드, 급여 조회
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); -- 서브쿼리 결과값 = 다중행 다중열

1.5. 📗 소감

• subquery 토픽 학습은 어려웠다 @.@
• 연습을 많이 해 봐야겠다!

📗 homework:

      

      

      

    
    

      <code />
    
-- 1번 문제: 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회
SELECT NVL(DEPT_CODE, 'N/A') "부서번호", COUNT(*) "인원수", ROUND(AVG(SALARY)) "급여의 평균", SUM(SALARY) "급여의 합"
FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 2번 문제: 자신이 속한 직급의 평균 급여보다 많이 받는 사원의 이름, (직급코드,) 직급명, 급여 정보 조회
-- GOOGLING해서 HINT 얻음(출처: https://github.com/ikhcho/Study/blob/master/src/study/db/sql/SQL_LAB8(%EC%97%B0%EC%8A%B5%EB%AC%B8%EC%A0%9C).sql)
-- 단계1) 각 직급의 평균 급여 구하기
SELECT JOB_CODE, AVG(SALARY) "직급별 평균 급여"
FROM EMPLOYEE
GROUP BY JOB_CODE; -- J2, 4850000
                   -- J7, 2017500
                   -- J3, 3600000
                   -- J6, 2624373.33333333333333333333333333333333
                   -- J5, 2820000
                   -- J1, 8000000
                   -- J4, 2330000

-- 단계2) 자신이 속한 직급의 평균 급여보다 많이 받는 사원의 이름, (직급코드,) 직급명, 급여 정보 조회
SELECT EMP_NAME, E.JOB_CODE, JOB_NAME, SALARY, S."직급별 평균 급여"
FROM EMPLOYEE E, JOB J, (SELECT JOB_CODE, ROUND(AVG(SALARY)) "직급별 평균 급여"
                         FROM EMPLOYEE
                         GROUP BY JOB_CODE) S
WHERE E.JOB_CODE = J.JOB_CODE(+)
 AND E.JOB_CODE = S.JOB_CODE(+)
 AND E.SALARY > S."직급별 평균 급여"
ORDER BY E.JOB_CODE;

-- 3번 문제: 회사에서 휴가철을 맞아 전 사원에게 해외여행을 보내주려고 합니다. 
-- 사원들의 급여에 맞춰 보내주려고 하여 S1, S2인 사원들은 미국, S3, S4인 사원들은 중국, S5, S6인 사원들은 일본에 보내려고 해요.
-- 단, 현재 근무하고 있는 지부의 국가가 해외여행을 보내주려는 국가와 같을 경우, 국내여행을 보내주며,
-- 대표는 휴가때도 계속해서 근무를 하기로 합니다.
-- EMPLOYEE 테이블에서 이와 같은 조건에 맞는 사번, 사원명, 부서명, 지부국가명(NATIONAL_NAME), 급여등급, 휴가여행지를 사번 오름차순으로 조회하세요.
SELECT EMP_ID "사번"
    , EMP_NAME "사원명"
    , DEPT_TITLE "부서명"
    , NATIONAL_NAME "지부국가명"
    , SAL_LEVEL "급여등급"
    , CASE WHEN J.JOB_NAME = '대표' THEN '근무'
           WHEN (E.SAL_LEVEL IN ('S1', 'S2') AND NVL(N.NATIONAL_NAME, 0) != '미국') THEN '미국'
           WHEN (E.SAL_LEVEL IN ('S1', 'S2') AND NVL(N.NATIONAL_NAME, 0) = '미국') THEN '한국'
           WHEN (E.SAL_LEVEL IN ('S3', 'S4') AND NVL(N.NATIONAL_NAME, 0) != '중국') THEN '중국'
           WHEN (E.SAL_LEVEL IN ('S3', 'S4') AND NVL(N.NATIONAL_NAME, 0) = '중국') THEN '한국'
           WHEN (E.SAL_LEVEL IN ('S5', 'S6') AND NVL(N.NATIONAL_NAME, 0) != '일본') THEN '일본'
           WHEN (E.SAL_LEVEL IN ('S5', 'S6') AND NVL(N.NATIONAL_NAME, 0) = '일본') THEN '한국'
           END "휴가지"
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
LEFT JOIN NATIONAL N ON (L.NATIONAL_CODE = N.NATIONAL_CODE)
ORDER BY EMP_ID;

-- 4번 문제: 모든 사원들 급여의 총합을 '￦00,000,000'형식으로 구하세요
SELECT TO_CHAR(SUM(SALARY), 'L999,999,999')
FROM EMPLOYEE;

-- 5번 문제: 부서 코드가 D9인 사원들의 급여 중 최저 급여보다 높은 급여를 받는 사원의 이름과 급여를 출력
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT MIN(SALARY)
                FROM EMPLOYEE
                GROUP BY DEPT_CODE
                HAVING DEPT_CODE = 'D9'); -- 서브쿼리의 결과값 = 3700000

-- 6번 문제: 'D6'번 부서의 사원 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT MAX(SALARY)
                FROM EMPLOYEE
                GROUP BY DEPT_CODE
                HAVING DEPT_CODE = 'D6'); -- 서브쿼리의 결과값 = 3900000

-- 7번 문제: 회사의 재정난으로 인하여 구조조정이 불가피해졌습니다.
-- 우선 현재 사원을 파악하기 위해 (EMPLOYEE 테이블의) 모든 직원의 사번, 사원명, 직급, 부서명, 근무지역을 직급코드와 사번의 내림차순으로 조회하세요.
SELECT EMP_ID 사번, EMP_NAME 사원명, JOB_NAME 직급, DEPT_TITLE 부서명, NATIONAL_NAME 근무지역
FROM EMPLOYEE E, JOB J, DEPARTMENT D, LOCATION L, NATIONAL N
WHERE E.JOB_CODE = J.JOB_CODE(+)
 AND E.DEPT_CODE = D.DEPT_ID(+)
 AND D.LOCATION_ID = L.LOCAL_CODE(+)
 AND L.NATIONAL_CODE = N.NATIONAL_CODE(+)
ORDER BY E.JOB_CODE DESC, EMP_ID DESC;

-- 8번 문제: 사기 충전의 의미로 한달에 한번 사내 이벤트가 진행됩니다. 랜덤 추첨을 통한 이벤트 당첨자 발표를 위해 다음을 조회하세요.
-- (사원 정보는 최소한만 노출되게 하기 위해 다음 조건을 만족해야 합니다: 당첨자의 사번, 사원명(가운데 글자는 '*'으로 표시), 아이디(이메일에서 아이디만 추출), 연락처(가운데 4자리는 '****'으로 표시)
-- 이번 달 당첨자는 주민번호 마지막 자리가 '1'인 사원들입니다.
SELECT EMP_ID "사번"
    , SUBSTR(EMP_NAME, 1, 1) || '*' || SUBSTR(EMP_NAME, 3, 1) "사원명"
    , SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') - 1) "ID"
    , CASE WHEN PHONE IS NOT NULL THEN SUBSTR(PHONE, 1, 3) || '****' || SUBSTR(PHONE, 8, 4)
           WHEN PHONE IS NULL THEN 'N/A'
      END "연락처"
FROM EMPLOYEE
WHERE EMP_NO LIKE ('%1');

-- 9번 문제: 국가명, 부서명, 인원수, 급여합계, 급여평균을 조회하되,
-- 국가명과 부서명 별로 묶어서 조회하고,
-- 정렬은 국가명 내림차순, 부서명 내림차순 순서로 지정하세요.
SELECT NATIONAL_NAME 국가명, DEPT_TITLE 부서명, COUNT(*) 인원수, SUM(SALARY) 급여합계, ROUND(AVG(SALARY)) 급여평균
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
LEFT JOIN NATIONAL N ON (L.NATIONAL_CODE = N.NATIONAL_CODE)
GROUP BY NATIONAL_NAME, DEPT_TITLE
ORDER BY NATIONAL_NAME DESC NULLS LAST, DEPT_TITLE DESC NULLS LAST;

-- 10번 문제: 대표 '선동일'은 최근 사내에서 지연으로 의심되는 상황을 엿듣게 되었다. 누군지 찾아내기 위해 믿을만한 김기사에게 비밀리에 사원들의 정보를 정리해오라고 명령했다.
-- 사번, 사원명, 부서명, 직급명, 급여, 보너스, 성별, 출생지를 조회하고, 정렬은 출생지 오름차순, 급여 내림차순 순서로 정렬한다.
-- 출생지는 주민번호 뒷자리 2번째부터 2자리를 기준으로 다음과 같다.
--서울 00~08 / 부산 09~12 / 인천 13~15 / 경기 16~25 / 강원 26~34 / 충북 35~39 / 대전 40 /
--충남 41~47 / 전북 48~54 / 전남 55~64 / 광주 65~66 / 대구 67~69, 76 /
--경북 70~75, 77~81 / 경남 82~84, 86~92 / 울산 85 / 제주 93~95 / 그 외에는 '확인요망'으로 표시
SELECT EMP_ID 사번, EMP_NAME 사원명, DEPT_TITLE 부서명, JOB_NAME 직급명, SALARY 급여, BONUS 보너스
    , DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남성', '2', '여성') 성별
    , CASE WHEN '00' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '08' THEN '서울'
           WHEN '09' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '12' THEN '부산'
           WHEN '13' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '15' THEN '인천'
           WHEN '16' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '25' THEN '경기'
           WHEN '26' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '34' THEN '강원'
           WHEN '35' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '39' THEN '충북'
           WHEN SUBSTR(EMP_NO, -2, 2) = '40' THEN '대전'
           WHEN '41' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '47' THEN '충남'
           WHEN '48' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '54' THEN '전북'
           WHEN '55' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '64' THEN '전남'
           WHEN SUBSTR(EMP_NO, -2, 2) IN ('65', '66') THEN '광주'
           WHEN SUBSTR(EMP_NO, -2, 2) IN ('67', '68', '69', '76') THEN '대구'
           WHEN '70' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '75' OR '77' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '81' THEN '경북'
           WHEN SUBSTR(EMP_NO, -2, 2) IN ('82', '83', '84') OR '86' <= SUBSTR(EMP_NO, -2, 2) AND SUBSTR(EMP_NO, -2, 2) <= '92'  THEN '경남'
           WHEN SUBSTR(EMP_NO, -2, 2) <= '85' THEN '울산'
           WHEN SUBSTR(EMP_NO, -2, 2) IN ('93', '94', '95') THEN '제주'
           ELSE '확인 요망'
      END 출생지
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
ORDER BY 출생지, SALARY DESC NULLS LAST;