안녕하세요
https://blog.naver.com/sysysy0302 여니입니다 :)
/*
<SUBQUERY(서브쿼리)>
하나의 주된 SQL(SELECT, CREATE, INSERT, UPDATE....) 안에 포함된 또하나의 SELECT문
메인 SQL문을 위해서 보조 역할을 하는 SELECT문
*/
-- 노옹철 사원과 같은 부서인 사원들
-- 1)먼저 노옹철 사원의 부서코드 조회
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'; -- 'D9';
-- 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 = '노옹철');
-- 전체 사원의 평균 급여보다 더 많은 급여를 받고 있는 사원들의 사번, 이름, 직급코드 조회
-- 1) 전체사원 급여평균구하기
SELECT AVG(SALARY)
FROM EMPLOYEE;
-- 2) 급여가 3047662.60869 큰 사원들 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE
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);
-- 전 직원중 최저급여를 받는 사원의 사원명, 직급코드, 급여조회(서브쿼리를 활용)
SELECT *
FROM EMPLOYEE
WHERE SALARY = (SELECT MIN(SALARY)
FROM EMPLOYEE);
-- 노옹철사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서명, 급여 조회
SELECT EMP_ID, EMP_NAME, EMP_TITLE, SALARY
FROM EMPLOYEE, DEPARTMENT
WHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAM='노옹철') AND
DEPT_CODE = DEPT_ID(+);
SELECT EMP_ID, EMP_NAME, EMP_TITLE, SALARY
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE
WHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME='노옹철');
--------------------------------------------------------------------------
-- 부서별 급여 합이 가장 큰 부터 하나만을 조회, 부서코드, 부서명, 급여의 합
SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPT_CODE = DEPARTMENT.DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY)=(SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE);
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;
/*
2. 다중행 서브쿼리 (MULTI ROW SUBQUERY)
서브쿼리의 조회 결과값이 여러 행일 경우
- IN (10,20,30) 서브쿼리 : 여러개의 결과값 중에서 하나라도 일치하는 것이 있다면
- (> OR <) ANY(10,20,30) 서브쿼리 : 여러개의 결과값 중에서 "하나라도" 클 경우
즉, 여러개의 결과값 중에서 가장 작은 값보다 클경우
- (> OR <) ALL(10,20,30) : 여러개의 결과값의 모든 값보다 클경우 혹은 작을경우
*/
-- 각 부서별 최고급여를 받는 사원의 이름, 직급코드, 급여조회
SELECT MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE; -- 다중행 서브쿼리
-- 위의 급여를 받는 사원들 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (2890000, 3660000, 8000000, 3760000, 3900000, 2490000, 2550000);
-- 두 코드를 합치기
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (SELECT MAX(SALARY) -- = 기호나 대소비교를 하면 에러남
FROM EMPLOYEE
GROUP BY DEPT_CODE);
---------
-- 이오리 또는 하동운 사원과 같은 직급인 사원들을 조회하시오(사원명, 직급코드, 부서코드, 급여)
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE JOB_CODE IN (SELECT JOB_CODE
FROM EMPLOYEE
WHERE EMP_NAME IN('이오리','하동운')
);
-- 사원 < 대리 < 과장 < 차장 < 부장
-- 대리 직급임에도 불구하고 과장 직급의 급여보다 많이 받는 사원들 조회 (사번, 이름, 직급명, 급여)
-- 1)과장직급인 사원들의 급여를 조회
SELECT SALARY
FROM EMPLOYEE E,JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND JOB_NAME = '과장';
SELECT SALARY
FROM EMPLOYEE E,JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND JOB_NAME = '대리';
-- 2) 위의 급여들보다 "하나라도" 높은 급여를 받는 직원들을 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY >= ANY(2200000, 2500000, 3760000)
AND JOB_NAME = '대리';
-- 3) 위 내용물들을 하나의 쿼리문으로 합치기
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY >= ANY(SELECT SALARY
FROM EMPLOYEE E,JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND JOB_NAME = '과장')
AND JOB_NAME = '대리';
-- 과장직급임에도 불구하고 "모든" 차장직급의 급여보다도 더 많이 받는 직원 조회(사번,이름,직급명,급여)
-- ANSI구문
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE JOB_NAME = '과장';
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE JOB_NAME = '차장';
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY > ALL (SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '차장'
)
AND JOB_NAME = '과장';
/*
3. (단일행) 다중열 서브쿼리
서브쿼리 조회결과가 값은 한행이지만, 나열된 컬럼의 갯수가 여러개일 경우
*/
-- 하이유 사원과 같은 부서코드, 같은 직급코드에 해당되는 사원들을 조회(사원명, 부서코드, 직급코드, 고용일)
--1) 하이유 사원의 부서코드와, 직급코드를 먼저 조회 => 다중열 서브쿼리
SELECT DEPT_CODE, JOB_CODE -- D5 | J5
FROM EMPLOYEE
WHERE EMP_NAME = '하이유';
--2) 부서코드가 D5이면서 직급코드도 J5인 사원들 조회
SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' AND JOB_CODE = 'J5';
--3) 위 내용물을 하나의 쿼리문으로 합치기
SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '하이유')
AND JOB_CODE = (SELECT JOB_CODE -- D5 | J5
FROM EMPLOYEE
WHERE EMP_NAME = '하이유');
-- 다중열 서브쿼리
-- (비교대상칼럼1, 비교대상칼럼2, ...) = (비교할값1, 비교할값2, ... => 서브쿼리형식으로 제시해야함)
SELECT *
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE FROM EMPLOYEE WHERE EMP_NAME = '하이유');
-- 박나라 사원과 같은 직급코드, 같은 사수사번을 가진 사원들의 사번, 이름, 직급코드, 사수사번 조회
-- 단일행 다중열서브쿼리로 작성!!
SELECT JOB_CODE, MANAGER_ID
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 = '박나라');
/*
4. 다중행, 다중열 서브쿼리
서브쿼리 조회 결과가 여러행, 여러 컬럼일 경우
*/
-- 각 직급별 최소급여를 받는 사원들 조회(사번, 이름, 직급코드, 급여)
-- 1) 각 직급별 최소 급여를 조회
SELECT JOB_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE;
-- 2) 위의 목록들 중에서 일치하는 사원
-- 2-1) 조건 나열
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (('J2',3700000),('J7',1380000));
-- 3) 위 내용을 가지고 쿼리문으로 합치기
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) 각 부서별 최고 급여 조회
SELECT DEPT_CODE, MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;
-- 2) 위 조건을 만족하는 사원들만 추리기(IN연산자 사용)
SELECT EMP_ID, EMP_NAME, NVL(DEPT_CODE, '없음'), SALARY
FROM EMPLOYEE
WHERE (NVL(DEPT_CODE, '없음'), SALARY) IN (SELECT NVL(DEPT_CODE, '없음'), MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE);
-- 3) 두 쿼리문을 합치기