오라클(Oracle) 서브쿼리를 활용해 테이블 조회해보자

안녕하세요

요즘 날씨가 갑자기 추어졌네요..

이제는 여름,겨울 2계절인것 같습니다.

모두 감기 조심하세요~!


이번 시간에는 오라클의 유용하게 쓰이는 서브쿼리 예제를 올려봅니다.

서브쿼리는 괄호 안에 있는 쿼리를 말합니다.


자 그럼 바로 예제 들어가겠습니다

ㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱ


사원 테이블에서 SCOTT의 급여보다 많은 사원의 사번, 이름, 업무, 급여를 출력하세요
SELECT SAL FROM EMP WHERE ENAME='SCOTT';

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL > 3000;

SELECT EMPNO, ENAME, JOB, SAL -- Main query
FROM EMP
WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SCOTT'); --Nested query

[1] EMP에서 사번이 7521인 사원과 업무가 같고, 급여가 7934인 사원보다 많은 사원의 사번, 이름, 업무, 급여를 출력하세요

SELECT JOB FROM EMP WHERE EMPNO=7521;

SELECT SAL FROM EMP WHERE EMPNO=7934;

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=7521)
AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7934);

--단일 행 서브쿼리
[2] EMP에서 사원들의 평균급여보다 적은 사원의 사번, 이름, 급여, 업무를 출력하세요

SELECT EMPNO, ENAME, SAL, JOB
FROM EMP
WHERE SAL < (SELECT AVG(SAL)FROM EMP);

[3] 사원 테이블에서 사원의 급여가 20번 부서의 최소 급여 보다 많은 부서의 부서번호와 최소급여를 출력하세요

SELECT DEPTNO, MIN(SAL) FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20);

다중 행 서브 쿼리
- 하나 이상의 행을 반환하는 서브쿼리
- 다중행 서브쿼리는 다중행 연산자를 사용해야 한다.
- 다중행 연산자는 하나 이상의 값을 요구한다.

#다중행 연산자
IN 연산자
ANY 연산자
ALL 연산자
EXIST 연산자

--다중행, 다중열 일 때는 IN 이 들어가야함
[4] 업무별로 최대 급여를 받는 사원의 사번과 이름, 급여를 출력하세요
SELECT JOB, EMPNO, ENAME, SAL
FROM EMP
WHERE (JOB, SAL) IN
(SELECT JOB, MAX(SAL)
FROM EMP
GROUP BY JOB);

ANY 연산자
- 서브쿼리 결과 값 중 어느 하나 값이라도 만족되면 결과값을 반환한다.

SELECT ENAME, SAL FROM EMP
WHERE DEPTNO<>20
--AND SAL > ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN');
AND SAL > (SELECT MIN(SAL) FROM EMP WHERE JOB = 'SALESMAN');

> ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN'); 의 의미
-> SAL > (SELECT MIN(SAL) FROM EMP WHERE JOB = 'SALESMAN')와 동일한 의미

SELECT DEPTNO, ENAME, SAL FROM EMP
WHERE DEPTNO<>20
AND SAL < ANY (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');
==>SAL<(SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN')과 동일

#ALL 연산자
- 서브쿼리 결과값 중 모든 결과값이 만족되어야만 결과값을 반환한다.

SELECT ENAME, SAL FROM EMP
WHERE DEPTNO<>20
AND SAL>ALL(SELECT SAL FROM EMP WHERE JOB='SALESMAN'); -- 최대값보다 크냐  부등호가 < 이면 최소값보다 작냐



# EXISTS 연산자
-EXISTS 연산자를 사용하면, 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만을 결과로 반환해준다
-SUBQUERY에서 적어도 1개의 행을 RETURN 하면 논리식은 참이고 그렇지 않으면 거짓입니다.

[5] 사원을 관리할 수 있는 사원의 정보를 보여주세요
SELECT ENAME, JOB FROM EMP E -- 여기서 E는 관리자를 의미(관리자의 이름과 업무를 보여주자)
WHERE EXISTS (SELECT EMPNO FROM EMP WHERE E.EMPNO=MGR); -- EMP의 사번 = MGT(관리자 사번) -> 관리자냐를 물어보는 것임

#다중 열 서브쿼리
-서브쿼리 결과값이 두 개 이상의 컬럼을 반환하는 경우

[6] EMP에서 급여와 보너스가 30번 부서의 있는 사원의 급여, 보너스와 일치하는 사원의 이름, 부서번호를 출력하세요
SELECT ENAME, DEPTNO, SAL, COMM
FROM EMP
WHERE (SAL, COMM)
IN (SELECT SAL, COMM FROM EMP WHERE DEPTNO=30 AND COMM IS NOT NULL);

[7] 업무별로 최소 급여를 받는 사원의 사번, 이름, 업무, 급여를 출력하세요. 단 업무별로 정렬하세요
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE (JOB, SAL) IN
(SELECT JOB, MIN(SAL) FROM EMP GROUP BY JOB)
ORDER BY JOB;

[실습]
84] 고객 테이블에 있는 고객 정보 중 마일리지가 가장 높은 금액의 고객 정보를 보여주세요.
SELECT NAME, JOB, MILEAGE FROM MEMBER
WHERE MILEAGE = (SELECT MAX(MILEAGE) FROM MEMBER);

85] 상품 테이블에 있는 전체 상품 정보 중 상품의 판매가격이 판매가격의 평균보다 큰  상품의 목록을 보여주세요.
 단, 평균을 구할 때와 결과를 보여줄 때의 판매 가격이 50만원을 넘어가는 상품은 제외시키세요.
   
SELECT PRODUCTS_NAME, OUTPUT_PRICE
FROM PRODUCTS
WHERE OUTPUT_PRICE > (SELECT AVG(OUTPUT_PRICE)FROM PRODUCTS
WHERE OUTPUT_PRICE <=500000)
AND OUTPUT_PRICE<=500000;

86] 상품 테이블에 있는 판매가격에서 평균가격 이상의 상품 목록을 구하되 / <-- 여기 먼저 구하자
평균을 구할 때 판매가격이 최대인 상품을 제외하고 평균을 구하세요.
--서브쿼리 안에 서브쿼리가 들어간다

SELECT * FROM PRODUCTS
WHERE OUTPUT_PRICE >=
(SELECT AVG(OUTPUT_PRICE) FROM PRODUCTS
WHERE OUTPUT_PRICE <> (SELECT MAX(OUTPUT_PRICE)FROM PRODUCTS));

87] 상품 카테고리 테이블에서 카테고리 이름에 컴퓨터라는 단어가 포함된 카테고리에 속하는 상품 목록을 보여주세요.

SELECT * FROM PRODUCTS WHERE CATEGORY_FK IN-- SELECT * FROM PRODUCTS WHERE CATEGORY_FK = -> '=' 단일 행 연산자 일때, 'IN' 다중 행
(SELECT CATEGORY_CODE FROM CATEGORY WHERE category_name LIKE '%컴퓨터%');

88] 고객 테이블에 있는 고객정보 중 직업의 종류별로 가장 나이가 많은 사람의 정보를 화면에 보여주세요.

SELECT NAME, JOB, AGE FROM MEMBER WHERE (JOB, AGE) IN -- 다중행 IN
(SELECT JOB, MAX(AGE) -- 다중행
FROM MEMBER GROUP BY JOB);

DELETE문에서 사용
(DELETE문은 직접 실행해보시고 ROLLBACK으로 취소해보세요)

89] 상품 테이블에서 공급가가 가장 큰 상품은 삭제 시키는 SQL 문을 작성하세요

DELETE FROM PRODUCTS WHERE INPUT_PRICE =
(SELECT MAX(INPUT_PRICE) FROM PRODUCTS);

ROLLBACK;

90] 상품 테이블에서 상품 목록을 공급업체(EP_CODE_FK)별로 정리한 뒤
각 공급업체 별로 최소 공급가(INPUT_PRICE)를 가진 상품을 삭제하세요

SELECT * FROM PRODUCTS;
DELETE FROM PRODUCTS WHERE (EP_CODE_FK, INPUT_PRICE) IN
(SELECT EP_CODE_FK, MIN(INPUT_PRICE) FROM PRODUCTS GROUP BY EP_CODE_FK);
ROLLBACK;