Day24

Day24

2020. 10. 29. 19:37ㆍ교육과정/KOSMO

키워드 : 서브쿼리 / 렌트카 모델링 / 호텔 모델링

****

1. 서브쿼리

-- 하나의 SQL 문장 내부에 존재하는 또 다른 SELECT 문장
-- DML (SELECT, INSERT, UPDATE, DELETE)에 사용 가능

<서브쿼리 사용시 권장 사항>

- 서브쿼리는 ( ) 로 묶는다 [권장]

- 서브쿼리는 연산자의 오른쪽에 [권장]

- 단일 행인 경우 비교 연산자 가능 ( >, >=, <, <=, =, !=(<>) )

- 복수 행인 경우 IN, NOT IN, ANY, ALL, EXISTS

- ORDER BY에선 사용 못한다

SELECT ROUND(avg(sal)) 평균급여 FROM emp;
-- 전체 사원들 중 평균 급여보다 낮은 급여를 받는 사원의 명단을 조회
SELECT ename 사원명, sal 급여 FROM emp WHERE sal < (SELECT ROUND(avg(sal)) FROM emp);

-- 월급이 가장 많은 사원의 정보를 조회
SELECT max(sal) 최대급여 FROM emp;
SELECT ename 사원명 FROM emp WHERE sal = ( SELECT max(sal) FROM emp);

-- 평균 급여보다 높고, 최대 급여보다 낮은 월급을 받는 사원의정보를 조회
SELECT ROUND(avg(NVL(sal,0))) 평균급여 FROM emp;
SELECT max(sal) 최대급여 FROM emp;
-- 방법1 (SELECT절을 WHERE절에 삽입)
SELECT * FROM emp
WHERE sal > (SELECT ROUND(avg(NVL(sal,0))) FROM emp) AND sal < (SELECT max(sal) FROM emp);

SELECT ROUND(avg(NVL(SAL,0))) 평균급여, max(sal) 최대급여 FROM emp;
-- 방법2 (SELECT절을 가상 테이블처럼 취급)
SELECT * FROM emp e, (SELECT ROUND(avg(NVL(SAL,0))) avg, max(sal) max FROM emp) ext
WHERE e.sal > ext.avg AND e.sal < ext.max;

SELECT * FROM emp WHERE rownum < 11;
SELECT NVL(sal,0) FROM emp ORDER BY sal DESC;
-- 월급순으로 상위 10명의 명단을 출력
SELECT rownum, e.*
FROM (SELECT NVL(sal,0) FROM emp WHERE NVL(sal,0) <> 0 ORDER BY sal DESC) e
WHERE rownum < 11;

2. 서브쿼리 연습문제

-- 연습문제1. scott의 급여보다 많은 사원의 정보 중 사원번호, 이름, 담당업무, 급여를 출력

-- 연습문제2. 30번 부서의 최소 급여보다 각 부서의 최소 급여가 높은 부서를 출력

-- 연습문제3. 업무별로 평균 급여 중에서 가장 적은 급여를 가진 직업을 출력

-- 연습문제4. 사원번호가 7521의 업무와 같고, 사원번호7934인 직원보다 급여를 많이 받는 사원의 정보를 출력

-- 연습문제5. 'WARD'와 부서와 업무가 같은 사원 명단 출력

3. 서브쿼리의 다중행

※ 다중행인 경우

IN	반환된 목록의 각각과 비교
ANY	비교연산자( >, >=, <, <=, =, !=(<>) ) 와 반환된 목록의 각각과 비교 ( OR연산자와 비슷)
ALL	비교연산자( >, >=, <, <=, =, !=(<>) ) 와 반환된 모든 목록과 비교
EXISTS	적어도 한 개의 행을 리턴하면 true, 그렇지 않으면 false (NOT IN과 비슷)