전체 명령에서 사용될 수 있다.
어느 부분이 서브쿼리이고 메인쿼리인지 분별하기
사원테이블에서 전체 사원의 평균 급여보다 많은 급여를 받는 사원 정보출력
=> 급여가 서브쿼리의 결과보다 많은 사람, 서브쿼리 = 전체사원의 평균급여
FROM -> WHERE -> SELECT 순으로 실행됨
사용예)사원테이블에서 전체 사원의 평균 급여보다 많은 급여를 받는 사원 정보출력
SELECT EMPLOYEE_ID,
EMP_NAME,
SALARY
FROM HR.EMP
WHERE SALARY>=(SELECT AVG(SALARY)
--전체 사원의 평균급여를 알아야 함. 조인으로 연결될 필요는 없음.
FROM HR.EMP);
SELECT EMPLOYEE_ID,
EMP_NAME,
SALARY
FROM HR.EMP A
WHERE EXISTS (SELECT 1
FROM (SELECT AVG(SALARY)AS AI
FROM HR.EMP) B
WHERE A.SALARY>=B.AI);
(in-line 서브쿼리)
SELECT A.EMPLOYEE_ID,
A.EMP_NAME,
A.SALARY
FROM HR.EMP A ,(SELECT AVG(SALARY)AS AI
--두 개의 테이블이 있기 때문에 반드시 조인해줘야 함
FROM HR.EMP) B
WHERE A.SALARY>=B.AI; -- NON-EQUI 조인
사용예)2005년 6월 회원들의 평균구매금액보다 많은 금액을 구매한 회원정보를 출력하시오. --메인쿼리부분, 서브쿼리부분을 구분
Alias는 회원번호,회원명,구매금액,마일리지
(메인쿼리: 회원정보를 출력, 조건: 구매금액이 평균구매금액보다 많은)
SELECT A.MEM_ID AS AID,
A.MEM_NAME AS ANAME,
SUM(B.CART_QTY*C.PROD_PRICE) AS ASUM -- 구매금액: CART, PROD 필요
FROM MEMBER A, CART B, PROD C
WHERE A.MEM_ID=B.CART_MEMBER
AND B.CART_PROD=PROD_ID
AND B.CART_NO LIKE '200506%'
GROUP BY A.MEM_ID,A.MEM_NAME;
(서브쿼리: 2005년 6월 평균구매금액) --연관성 없는 서브쿼리
SELECT ROUND(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;
(결합)
SELECT A.MEM_ID AS AID,
A.MEM_NAME AS ANAME,
SUM(B.CART_QTY*C.PROD_PRICE) AS ASUM -- 구매금액: CART, PROD 필요
FROM MEMBER A, CART B, PROD C
WHERE A.MEM_ID=B.CART_MEMBER
AND B.CART_PROD=PROD_ID
AND B.CART_NO LIKE '200506%'
GROUP BY A.MEM_ID,A.MEM_NAME
HAVING SUM(B.CART_QTY*C.PROD_PRICE)>=
(SELECT ROUND(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); 2)연관성 있는 서브쿼리
. 메인쿼리에 사용된 테이블과 서브쿼리에 사용된 테이블이 조인으로 연결된 서브쿼리
. 대부분의 서브쿼리 형태
사용예)장바구니테이블에서 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
(결합) - 각 사람별 2005년도에 회원이 구매한 값 중에 가장 큰 값
SELECT A.CART_MEMBER AS 회원번호,
B.MEM_NAME AS 회원명,
A.CART_NO AS 장바구니번호,
A.CART_QTY AS 구매수량
FROM CART A, MEMBER B --CART에서 회원번호 하나를 출력해서 서브쿼리와 비교
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월 월별 구매현황을 조회하시오
--커서CURSOR: SELECT문의 결과는 VIEW(익명 커서) = CURSOR: 어떤 SQL문의 영향을 받는 행들의 집합
(2005년 5월 구매금액합계가 많은 5명)
SELECT A.AID
FROM (SELECT CART_MEMBER AS AID, --내림차순으로 정렬
SUM(CART_QTY*PROD_PRICE) AS ASUM
FROM CART,PROD
WHERE CART_NO LIKE '200505%'
--WHERE절이 ORDER BY절보다 먼저 실행되기 때문에 서브쿼리로 추출해야함
AND CART_PROD=PROD_ID
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5; --5명순으로 컷
(PL/SQL 문)
DECLARE
CURSOR CUR_CART01 IS --하나의 행을 가리킴
SELECT A.AID AS 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 MEMBER.MEM_ID%TYPE;
BEGIN
FOR REC IN CUR_CART01 LOOP --커서가 가리키고 있는 행을 REC가 대신 가리키게 함
V_MID:=REC.AAID; --REC에 있는 회원번호를 V_MID에 넣어라
SELECT SUM(CART_QTY*PROD_PRICE),
SUM(CART_QTY) INTO V_SUMC,V_SUMQ --금액합계와 수량합계를 V_SUMC와 V_SUMQ에 대입
FROM CART,PROD
WHERE CART_PROD=PROD_ID
AND CART_MEMBER=V_MID --맨 처음 V_MID는 f001
AND SUBSTR(CART_NO,1,6) BETWEEN '200504' AND '200507'; --4~7월달 사이의 자료
SELECT MEM_NAME INTO V_NAME
FROM MEMBER
WHERE MEM_ID=V_MID;
DBMS_OUTPUT.PUT_LINE('회원번호 : '||V_MID);
DBMS_OUTPUT.PUT_LINE('회원명 : '||V_NAME);
DBMS_OUTPUT.PUT_LINE('구매수량합계 : '||V_SUMQ);
DBMS_OUTPUT.PUT_LINE('구매금액합계 : '||V_SUMC);
END LOOP;
END;** 함수로 작성
CREATE OR REPLACE FUNCTION 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
WHERE CART_NO LIKE V_PERIOD;
RETURN V_SUM;
END; ** 실행
SELECT SUBSTR(CART_NO,5,2) AS 월,
SUM(CART_QTY) AS 수량합계,
SUM(CART_QTY*PROD_PRICE) AS 금액합계
FROM (SELECT A.AID AS 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)B,CART,PROD
WHERE B.AAID=CART_MEMBER
AND PROD_ID=CART_PROD
AND SUBSTR(CART_NO,1,6) BETWEEN '200504' AND '200507'
GROUP BY SUBSTR(CART_NO,5,2);
사용예)모든 거래처별 2005년 매입현황을 조회하시오
--모든: OUTER JOIN /~별: GROUP BY(일반조건이 있어서 OUTER조인 그냥 사용 어려움)
Alias는 거래처코드,거래처명,매입수량합계,매입금액합계 --3개 테이블
SELECT D.BUYER_ID AS 거래처코드, --밖에서 GROUP BY절을 사용할 필요 없음
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,
--OUTER JOIN에서는 NULL값이 있는 행이 있기 때문에 NVL를 써줘야함
SUM(A.BUY_QTY*C.PROD_COST) AS SUMC
FROM BUYPROD A, BUYER B, PROD C --OUTER JOIN에서 (+)를 두 번 쓸 수 없음
WHERE A.BUY_PROD=C.PROD_ID
AND C.PROD_BUYER=B.BUYER_ID
AND EXTRACT(YEAR FROM A.BUY_DATE)=2005
GROUP BY BUYER_ID)E
WHERE E.BID(+)=D.BUYER_ID
ORDER BY 1; 사용예)미국 이외의 부서별 인원수 및 평균급여를 조회하시오
Alias는 부서코드,부서명,국가,주소,인원수,평균급여
--한 문장으로 GROUP BY절 쓸 수 없음 ->국가와 주소는 일반컬럼(그룹화할 수 없음)
SELECT CC.DEPARTMENT_ID AS 부서코드,
CC.DEPARTMENT_NAME AS 부서명,
DD.COUNTRY_NAME AS 국가,
BB.STREET_ADDRESS||' '||CITY||', ' ||STATE_PROVINCE AS 주소,
AA.CNT AS 인원수,
AA.SAL AS 평균급여
FROM (SELECT A.DEPARTMENT_ID AS DID, --모든 부서의 평균급여와 인원 수를 계산한 서브쿼리
COUNT(B.EMPLOYEE_ID) 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 --서브쿼리 안의 부서코드와 DEPT의 부서코드가 같음
AND CC.LOCATION_ID=BB.LOCATION_ID
AND BB.COUNTRY_ID=DD.COUNTRY_ID
AND DD.COUNTRY_NAME != 'United States of America';