> # 서브쿼리
- SQL 문장안에 보조로 사용되는 또 다른 SQL문
- 최종결과를 출력하기 위해 사용되는 계산의 중간 값 등을 반환하기 위한 쿼리
- 알려지지 않은 조건에 근거한 값들을 검색하기 위한 SQL문에 사용
- 서브쿼리는 반드시 '( )' 안에 기술해야함 (단, INSERT INTO 문은 제외)
- 서브쿼리는 SELECT절(일반 서브쿼리), FROM 절(in-line 서브쿼리).
WHERE 절에(중첩 서브쿼리) 사용가능 - 메인쿼리에 사용된 테이블과 서브쿼리에 사용된 테이블 사이의 조인 여부에 따라
연관성 없는 서브쿼리(조인조건이 없는 경우), 연관성있는 서브쿼리(조인으로 연결된)로 구분1) 연관성 없는 서브쿼리 - 메인쿼리와 서브쿼리 사이에 조인이 발생되지 않는 서브쿼리
사용예) 사원테이블에서 전체 사원의 평균 급여보다 많은 급여를 받는 사원 정보 출력
SELECT EMPLOYEE_ID,
EMP_NAME,
SALARY
FROM HR.EMP
WHERE SALARY >=(SELECT AVG(SALARY) --중첩 서브쿼리
FROM HR.EMP); SELECT A.EMPLOYEE_ID,
A.EMP_NAME,
A.SALARY
FROM HR.EMP A, (SELECT AVG(SALARY) AS A1
FROM HR.EMP)B --인라인 서브쿼리의 결과값이 B라는 뷰로 만들어짐.
WHERE A.SALARY>=B.A1; -- NON eqit 조인
SELECT A.EMPLOYEE_ID,
A.EMP_NAME,
A.SALARY
FROM HR.EMP A
WHERE EXISTS (SELECT
FROM (SELECT AVG(SALARY) AS A1
FROM HR.EMP)B
WHERE A.SALARY>=B.A1); --연관성이 있는 서브쿼리. A,B가 조인
사용예) 2005년 6월 회원들의 평균구매금액보다 많은 금액을 구매한 회원정보를 출력하시오 --메인쿼리와 서브쿼리에서 담당할부분부터 파악
Alias는 회원번호, 회원명, 구매금액, 마일리지
(메인쿼리: 회원정보 출력, 조건: 구매금액이 평균구매금액보다 많음)
-- 2005년 5월 회원별 구매 금액 합계
SELECT A.MEM_ID AS AID,
A.MEM_NAME AS ANAME,
SUM(B.CART_QTY*C.PROD_PRICE) AS ASUM
FROM MEMBER A, CART B, PROD C --구매금액 구하려고 B,C 테이블 필요
WHERE A.MEM_ID=B.CART_MEMBER --조인1
AND B.CART_PROD=C.PROD_ID --조인2
AND B.CART_NO LIKE '200506%'
GROUP BY A.MEM_ID, A.MEM_NAME ;
(서브쿼리: 2005년 6월 평균구매금액) --회원별 집계를 내고 평균값을 구해야함
SELECT AVG(C.BSUM)AS A1 --AVG와 SUM은 중첩될수 없음
FROM (SELECT A.CART_MEMBER, --연관성 없는 서브쿼리
SUM(A.CART_QTY*B.PROD_PRICE) AS BSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '200506%'
GROUP BY A.CART_MEMBER) C;
(결합)
SELECT A.MEM_ID AS AID,
A.MEM_NAME AS ANAME,
SUM(B.CART_QTY*C.PROD_PRICE) AS ASUM
FROM MEMBER A, CART B, PROD C --구매금액 구하려고 B,C 테이블 필요
WHERE A.MEM_ID=B.CART_MEMBER --조인1
AND B.CART_PROD=C.PROD_ID --조인2
AND B.CART_NO LIKE '200506%'
GROUP BY A.MEM_ID, A.MEM_NAME
-- HAVING SUM(B.CART_QTY*C.PROD_PRICE) >=(서브쿼리)
HAVING SUM(B.CART_QTY*C.PROD_PRICE) >= (SELECT AVG(C.BSUM)AS A1
FROM (SELECT A.CART_MEMBER,
SUM(A.CART_QTY*B.PROD_PRICE) AS BSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '200506%'
GROUP BY A.CART_MEMBER)C);연관성 있는 서브쿼리
- 메인쿼리에 사용된 테이블과 서브쿼리에 사용된 테이블이 조인으로 연결된 서브쿼리
- 대부분의 서브쿼리 형태
사용예) 장바구니 테이블에서 2005년 회원별 최고 구매수량을 가진 회원의 회원번호, 회원명, 장바구니번호, 구매수량을 조회하시오
(메인쿼리: 회원의 회원번호, 회원명, 장바구니번호, 구매수량을 조회) -- 최종적으로 출력되는 부분
SELECT A.CART_MEMBER AS 회원번호,
B.MEM_NAME AS 회원명,
A.CART_NO AS 장바구니번호,
A.CART_QTY AS 구매수량
FROM CART A, MEMBER B
WHERE A.CART_MEMBER=B.MEM_ID
AND A.CART_QTY=(서브쿼리); -- 서브쿼리에서 계산된 카트 수량과 같은 값
(서브쿼리: 2005년 최고 구매수량)
SELECT MAX(C.CART_QTY)
FROM CART C
WHERE C.CART_NO LIKE '2005%'
AND C.CART_MEMBER=A.CART_MEMBER;
(결합)
SELECT A.CART_MEMBER AS 회원번호,
B.MEM_NAME AS 회원명,
A.CART_NO AS 장바구니번호,
A.CART_QTY AS 구매수량
FROM CART A, MEMBER B
WHERE A.CART_MEMBER=B.MEM_ID
AND A.CART_QTY=(SELECT MAX(C.CART_QTY)
FROM CART C
WHERE C.CART_NO LIKE '2005%'
AND C.CART_MEMBER=A.CART_MEMBER);
사용예) 2005년 5월 구매금액합계가 많은 5명의 2005년 4-7월 월별 구매현황을 조회하시오
-- SELECT 문의 결과는 뷰이자 커서. 어떤 SQL에 영향을 받는 행들의 집합
(2005년 5월 구매금액합계가 많은 5명)
SELECT A.AID
FROM (SELECT CART_MEMBER AS AID, --인라인 서브쿼리 5월에 구매한 사람 20명
SUM(CART_QTY*PROD_PRICE) AS ASUM
FROM CART, PROD
WHERE CART_NO LIKE '200505%'
AND CART_PROD=PROD_ID
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5; --20명중 5명
DECLARE
CURSOR CUR_CART01 IS
SELECT A.AID, A.AAID
FROM (SELECT CART_MEMBER AS AID,
SUM(CART_QTY*PROD_PRICE) AS ASUM
FROM CART, PROD
WHERE CART_NO LIKE '200505%'
AND CART_PROD=PROD_ID
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5;
V_NAME MEMBER.MEM_ID%TYPE;
V_SUMQ NUMBER:=0;
V_SUMC NUMBER:=0;
V_MID
BEGIN
FOR REC IN CUR_CART01 LOOP
V_MID:=REC.AAID
SELECT SUM(CART_QTY*PROD_PRICE),
SUM(CART_QTY) INTO V_SUMC, V_SUMQ
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_MEMBER=V_MID
AND SUSTR(CART_NO,1,6) BETWEEN '200504' AND '200507';
SELECT MEM_NAME INTO V_NAME
FROM MEMBER
DBMS_OUTPUT.PUT.LINE(
END LOOP;
END
**함수로 작성
CREATE OR REPLACE FUNCTOIN SUMC(
P_MID IN MEMBER.MEM_ID%TYPE,
P_MON IN VARCHAR2)
RETURN NUMBER
IS
V_SUM NUMBER:=0;
V_PERIOD CAHR(7):='2005'||P_MON||'%';
BEGIN
SELECT SUM(RPOD_PRICE*CART_QTY) INTO V_SUM
FROM CART, PROD
WHERE CART_MEMBER=P_MID
AND CART_NO LIKE V_PERIOD;
RETURN V_SUM;
END;
CREATE OR REPLACE FUNCTOIN SUMQ(
P_MID IN MEMBER.MEM_ID%TYPE,
P_MON IN VARCHAR2)
RETURN NUMBER
IS
V_SUM NUMBER:=0;
V_PERIOD CHAR(7):='2005'||P_MON||'%';
BEGIN
SELECT SUM(CART_QTY) INTO V_SUM
FROM CART, PROD
WHERE CART_NO LIKE V_PERIOD;
RETURN V_SUM;
END;
**실행
SELECT
FROM CART, PROD
WHERE CART_PROD=PROD_ID
사용예) 모든 거래처별 2005년 매입현황을 조회하시오 --"모든" 외부조인
Alias는 거래처코드 거래처명매입수량합계 매입금액합계
(내부조인)
-- BUYER테이블과 BUYPROD 테이블을 연결하는 PROD테이블
SELECT B.BUYER_ID AS 거래처코드,
NVL(SUM(A.BUY_QTY),0) AS 매입수량합계,
NVL(SUM(A.BUY_QTY*C.PROD_PRICE),0) AS 매입금액합계
FROM BUYER B,BUYPROD A,PROD C
WHERE A.BUY_PROD=C.PROD_ID
AND C.PROD_BUYER=B.BUYER_ID
AND EXTRACT(YEAR FROM A.BUY_DATE) = 2005
GROUP BY B.BUYER_ID ;
(외부조인)
SELECT D.BUYER_ID AS 거래처코드,
D.BUYER_NAME AS 거래처명,
NVL(E.SUMQ,0) AS 매입수량합계,
NVL(E.SUMC,0) AS 매입금액합계
FROM BUYER D, (SELECT B.BUYER_ID AS BID, --서브쿼리는 참조되어지기 때문에 별칭을 영어로 쓰는게 좋음
SUM(A.BUY_QTY) AS SUMQ,
SUM(A.BUY_QTY*C.PROD_PRICE) AS SUMC
FROM BUYER B,BUYPROD A,PROD C
WHERE A.BUY_PROD=C.PROD_ID
AND C.PROD_BUYER=B.BUYER_ID
AND EXTRACT(YEAR FROM A.BUY_DATE) = 2005
GROUP BY B.BUYER_ID)E
WHERE E.BID(+)=D.BUYER_ID
ORDER BY 1;
사용예) 미국 이외의 부서별 인원수 및 평균급여를 조회하시오
Alias는 부서코드, 부서명, 국가, 주소, 인원수, 평균급여
SELECT CC.DEPARTMENT_ID AS 부서코드,
CC.DEPARTMENT_NAME AS 부서명,
DD.COUNTRY_NAME AS 국가,
BB.STREET_ADDRESS||''||BB.CITY||', '||BB.STATE_PROVINCE AS 주소,
AA.CNT AS 인원수,
AA.SAL AS 평균급여
FROM ( SELECT A.DEPARTMENT_ID AS DID,
COUNT(*) AS CNT,
ROUND(AVG(B.SALARY)) AS SAL
FROM HR.EMP B, HR.DEPT A
WHERE B.DEPARTMENT_ID=A.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID) AA,
HR.LOCATIONS BB, HR.DEPT CC, HR.COUNTRIES DD
WHERE AA.DID=CC.DEPARTMENT_ID
AND CC.LOCATION_ID=BB.LOCATION_ID
AND BB.COUNTRY_ID=DD.COUNTRY_ID
AND DD.COUNTRY_NAME!='United States of America';
-- 아래처럼 SELECT절에 COUNT, AVG함수올경우 GROUP BY절 해줘야하는데
부서코드, 부서명만 GROUP BY하면 상관없는데
-- 국가 주소도 있음. => COUNT, AVG구하는 쿼리를 뷰로 만들어버림
SELECT A.DEPARTMENT_ID AS 부서코드,
A.DEPARTMENT_NAME AS 부서명,
B.COUNTRY_NAME AS 국가,
C.STREET_ADDRESS||''||BB.CITY||', '||BB.STATE_PROVINCE AS 주소,
COUNT(*) AS 인원수,
ROUND(AVG(D.SALARY) AS 평균급여
FROM HR.DEPT A, HR.COUNTRIES B , HR.LOCATIONS C, HR.EMP D
서브쿼리를 이용한 UPDATE 문
(사용형식)
UPDATE 테이블명
SET [(컬럼명[,컬럼명,...])=(서브쿼리)
[WHERE 조건];--업데이트할 행의개수
- UPDATE문은 많이 사용됨
- SET 절에 컬럼명이 복수개 사용되는 경우 컬럼명들을 '( )' 로 묶어 기술하고
서브쿼리의 SELECT 절의 컬럼 list와 갯수, 순서, 타입이 일치해야 함 - 서브쿼리가 사용되는 SET절에 단일 컬럼을 처리하는 경우 복수개의 서브쿼리가 필요
사용예) 사원테이블에서 사원들의 급여를 소속부의 평균급여로 갱신하시오.
(서브쿼리:부서별 평균급여)
SELECT DEPARTMENT_ID,
ROUND(AVG(SALARY)) AS ASAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID;
(메인쿼리: 사원의 급여 변경)
UPDATE HR.EMP A
SET A.SALARY=(SELECT B.ASAL
FROM (SELECT DEPARTMENT_ID, --부서별 평균급여 테이블
ROUND(AVG(SALARY)) AS ASAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID) B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID) --사원한명이 선택된 부서코드와 부서별 평균급여 테이블과 조인
사용예) 2005년 7월 사용자별 구매현황을 조회하여 회원들의 마일리지를 변경하시오
7월 사용자 구매현황 마일리지
UPDATE MEMBER A
SET A.MEM_MILEAGE= (SELECT D.ASAL+A.MEM_MILEAGE
FROM (SELECT B.CART_MEMBER AS CID,
SUM(B.CART_QTY*C.PROD_MILEAGE) AS ASAL
FROM CART B, PROD C
WHERE B.CART_PROD=C.PROD_ID
AND B.CART_NO LIKE '200507%'
GROUP BY B.CART_MEMBER)D
WHERE A.MEM_ID=D.CID)
WHERE A.MEM_ID IN(SELECT DISTINCT CART_MEMBER --2005년 7월에 구매한 회원 번호를 중복없이 11명만 추출. WHERE절에 포함된것만 업데이트해라
FROM CART
WHERE CART_NO LIKE '200507%');
(검증)
SELECT MEM_ID, MEM_MILEAGE
FROM MEMBER;
COMMIT;
사용예) 2005년 5월-7월 매출현황을 조회하여 재고수불테이블을 변경하시오
UPDATE REMAIN C
SET C.REMAIN_O, C.REMAIN_J_99, C.REMAIN_DATE =
(SELECT D.AA+C.REMAIN_O, C.REMAIN_J_99-D.AA , TO_DATE('20050701')
FROM (SELECT A.CART_PROD AS ID,
SUM(A.CART_QTY) AS AA --판매수량
FROM CART A
WHERE SUBSTR(CART_NO,1,6) BETWEEN ('200505') AND('200507')
GROUP BY A.CART_PROD)D
WHERE C.PROD_ID D.ID)WHERE C.PROD_ID IN ( SELECT CART_PROD
FROM CART
WHERE SUBSTR(CART_NO,1,6) BETWEEN ('200505') AND('200507'));
사용예) 2005년 5-7월 매입현황을 조회하여 재고수불테이블을 변경하시오
UPDATE REMAIN A
SET A.REMAIN_I, A.REMAIN_J_99,A.REMAIN_DATE =
(SELECT A.REMAIN_I+B.CNT ,A.REMAIN_J_99++B.CNT, TO_DATE('20050701')
FROM (SELECT BUY_PROD AS BID,
SUM(BUY_QTY) AS CNT --매입개수
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050731')
GROUP BY BUY_PROD)B
WHERE A.PROD_ID = B.BID)
WHERE A.PROD_ID IN ( SELECT BUY_PROD
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050731'));
서브쿼리를 이용한 INSERT
(사용형식)
INSERT INTO 테이블명[(컬럼명[,컬럼명,...])]
서브쿼리;
-
별로 사용안함. 데이터 초기화시 사용
-
INSERT 에 사용되는 서브쿼리는 '( )'안에 기술하지 않음
-
서브쿼리를 사용하는 INSERT 에는 VALUES 예약어가 생략
**테이블 복사
CREATE TABLE 테이블명 AS
서브쿼리
사용예) REMIAN 테이블을 REMAIN_TEMP 테이블로 생성시키고 내용을 복사하시오.
CREATE TABLE REMAIN_TEMP AS
SELECT * FROM REMAIN;
(검증)
SELECT * FROM REMAIN TEMP; DELETE REMAIN_TEMP;사용예) 재고수불테이블에서 현재고가 음수인 자료를 찾아서 REMAIN_TEMP테이블에 저장하시오
INSERT INTO REMAIN_TEMP
SELECT * FROM REMAIN
WHERE REMAIN_J_99<0;
삐약..뺙뺙