DB Oracle
[Oracle] 서브쿼리
아잠만_
2024. 3. 25. 17:05
더보기
2020년 모든 상품별 매입/매출 집계를 조회하시오
Alias는 상품번호, 상품명, 매입수량, 매입금액, 매출수량, 매출금액
서브쿼리
SELECT A.PROD_ID AS 상품번호,
A.PROD_NAME AS 상품명,
NVL(B.BQTY,0) AS 매입수량,
NVL(B.BSUM,0) AS 매입금액,
NVL(C.CQTY,0) AS 매출수량,
NVL(C.CSUM,0) AS 매출금액
FROM PROD A, (SELECT A.PROD_ID AS BID,
SUM(B.BUY_QTY) AS BQTY,
SUM(B.BUY_QTY*A.PROD_COST) AS BSUM
FROM PROD A, BUYPROD B
WHERE A.PROD_ID=B.BUY_PROD
AND B.BUY_DATE BETWEEN TO_DATE('20200101') AND LAST_DAY(TO_DATE('20201201'))
GROUP BY A.PROD_ID ) B,
( SELECT A.PROD_ID AS CID,
SUM(C.CART_QTY) AS CQTY,
SUM(C.CART_QTY*A.PROD_PRICE) AS CSUM
FROM PROD A, CART C
WHERE A.PROD_ID=C.CART_PROD
AND SUBSTR(C.CART_NO,1,4)='2020'
GROUP BY A.PROD_ID) C
WHERE A.PROD_ID=BID(+)
AND A.PROD_ID=CID(+)
ORDER BY 1;ANSI FORMAT
SELECT A.PROD_ID AS 상품번호,
A.PROD_NAME AS 상품명,
NVL(B.BQTY,0) AS 매입수량,
NVL(B.BSUM,0) AS 매입금액,
NVL(C.CQTY,0) AS 매출수량,
NVL(C.CSUM,0) AS 매출금액
FROM PROD A
LEFT OUTER JOIN (SELECT A.PROD_ID AS BID,
SUM(B.BUY_QTY) AS BQTY,
SUM(B.BUY_QTY*A.PROD_COST) AS BSUM
FROM PROD A, BUYPROD B
WHERE A.PROD_ID=B.BUY_PROD
AND B.BUY_DATE BETWEEN TO_DATE('20200101') AND LAST_DAY(TO_DATE('20201201'))
GROUP BY A.PROD_ID ) B
ON (A.PROD_ID=B.BID)
LEFT OUTER JOIN (SELECT A.PROD_ID AS CID,
SUM(C.CART_QTY) AS CQTY,
SUM(C.CART_QTY*A.PROD_PRICE) AS CSUM
FROM PROD A, CART C
WHERE A.PROD_ID=C.CART_PROD
AND SUBSTR(C.CART_NO,1,4)='2020'
GROUP BY A.PROD_ID) C
ON(A.PROD_ID=C.CID)
ORDER BY 1;서브 쿼리
- 서브쿼리는 SQL구문 안에 또다른 SQL 구문이 포함된 형태
- 알려지지않은 조건에 근거하여 값들을 조회하는 쿼리가 필요한 경우
- 서브쿼리는 SELECT, INSERT, UPDATE, DELETE 문에서 사용됨
- 서브쿼리는 ' ( ) '로 묶어야 함 (예외 : INSERT와 CREATE 문에 사용되는 서브쿼리)
- 조건절에 서브쿼리가 사용될 경우 서브쿼리는 반드시 연산자 오른쪽에 기술 되어야 함
- 서브쿼리는 SELECT절(일반 서브쿼리) , FROM절(인라인 서브쿼리), WHERE절(중첩 서브쿼리)에 사용 가능
- 실행 순서는 해당 절이 수행될 때 서브 쿼리가 가장 먼저 수행 됨
서브쿼리의 분류
- 단일행/복수행
- 사용되는 연산자에 의한 구분 - 연관성 없는 서브쿼리/연관성 있는 서브 쿼리
- 메인쿼리와 서브쿼리에 사용된 테이블이 조인연산을 사용하는 여부에 따른 분류
단일행 서브쿼리
- 오직 한개의 행만을 반환하는 서브쿼리
- 단일행 연산자 ( =, >, >=, <, <=, != )만 사용 가능
사원테이블에서 평균임금보다 더 적은 급여를 받는 사원들을 조회하시오.
Alias는 사원번호, 사원명, 급여, 사원들의 평균급여
(WHERE절)
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
SALARY AS 급여,
(SELECT ROUND(AVG(SALARY))
FROM HR.EMPLOYEES) AS "사원들의 평균급여"
-- WHERE절에 만족하는 56명 수행
FROM HR.EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
FROM HR.EMPLOYEES)
--전체 107명 수행
ORDER BY 1;
--서브쿼리는 총 163번 수행되어짐(FROM절)
-- 독립실행되어야함 FROM절 안에있는 부분을 실행하면 반드시 실행되어야함
SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
A.SALARY AS 급여,
B.ASAL AS "사원들의 평균급여"
FROM HR.EMPLOYEES A, (SELECT ROUND(AVG(SALARY)) AS ASAL
FROM HR.EMPLOYEES) B
WHERE A.SALARY<B.ASAL
ORDER BY 1;
-- 비동등조인
-- 서브쿼리 1번 수행2020년 5월 회원별 구매액을 계산하여 구매액이 많은 5명의 회원정보를 조회하시오
Alias는 회원번호, 회원명, 주소, 직업, 마일리지, 구매액
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
A.MEM_ADD1 || ' ' || A.MEM_ADD2 AS 주소,
A.MEM_JOB AS 직업,
A.MEM_MILEAGE AS 마일리지,
CSUM AS 구매액
FROM MEMBER A, ( SELECT CART_MEMBER AS CID,
SUM(CART_QTY * PROD_PRICE) AS CSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND SUBSTR(CART_NO,1,6)='202005'
GROUP BY CART_MEMBER
ORDER BY 2 DESC) B
WHERE A.MEM_ID=B.CID
AND ROWNUM<=5;연관성있는 쿼리 / JOIN으로 이루어져있는 쿼리 ( 알려지지않은 조건을 근거하여 값을 조회한다)
서브쿼리를 사용하는 INSERT
INSERT INTO 테이블명 [(컬럼list)] 서브쿼리;서브쿼리의 SELECT절의 컬럼의 갯수, 순서와 INTO절에 사용된 (컬럼list)의 갯수, 순서와 반드시 일치
생성된 재고수불테이블에 다음자료를 삽입하시오
년도 : 2020
상품번호 : PROD 테이블의 상품번호
기초재고 : PROD 테이블의 PROD_PROPERSTOCK
입고/출고 수량 : 없음
현재고 : 기초재고와 같음
갱신일 : 2020년 1월 1일
INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID,REMAIN_J_00,
REMAIN_J_99,REMAIN_DATE)
SELECT '2020', PROD_ID, PROD_PROPERSTOCK, PROD_PROPERSTOCK, TO_DATE('20200101')
FROM PROD;
COMMIT;HR계정의 사원테이블에서 부서의 위치가 미국에 위치한 부서에 속한 사원의 평균 급여보다 더 많은 급여를 받는 미국 이외의 부서에 근무하는 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서명, 급여
SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
B.DEPARTMENT_NAME AS 부서명,
ROUND(BAVG) AS 평균급여,
A.SALARY AS 급여
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B, HR.LOCATIONS C,
(SELECT AVG(A.SALARY) AS BAVG
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B, HR.LOCATIONS C
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.LOCATION_ID=C.LOCATION_ID
AND C.COUNTRY_ID='US') D
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.LOCATION_ID=C.LOCATION_ID
AND C.COUNTRY_ID!='US'
AND A.SALARY>D.BAVG
ORDER BY A.DEPARTMENT_ID, A.SALARY;회원테이블에서 마일리지가 많은 5명의 회원이 2020년 4월 구매한 정보를 조회하시오
Alias는 회원번호, 회원명, 구매금액합계
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
SUM(B.CART_QTY*C.PROD_PRICE) AS 구매금액합계
FROM MEMBER A, CART B, PROD C
WHERE A.MEM_ID=B.CART_MEMBER
AND B.CART_PROD = C.PROD_ID
AND SUBSTR(CART_NO,1,6)='202004'
AND A.MEM_ID IN(SELECT DID
FROM (SELECT MEM_ID AS DID, MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE IS NOT NULL
ORDER BY MEM_MILEAGE DESC)
WHERE ROWNUM<=5)
GROUP BY A.MEM_ID, A.MEM_NAME, A.MEM_MILEAGE
ORDER BY 3 DESC;부서테이블에서 부서관리사원의 사원번호가 100인 부서에 속한 사원정보를 조회하시오
WHERE 조건의 IN
SELECT B.DEPARTMENT_NAME AS 부서이름,
A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원이름
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND A.DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID AS BDID
FROM HR.DEPARTMENTS
WHERE MANAGER_ID=100 );FROM
SELECT B.DEPARTMENT_NAME AS 부서이름,
A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원이름
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B,
( SELECT DEPARTMENT_ID AS BDID
FROM HR.DEPARTMENTS
WHERE MANAGER_ID=100 ) C
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.DEPARTMENT_ID=C.BDID;EXISTS 연산자
EXISTS 뒤에는 반드시 서브쿼리가 나와야함
메인테이블에 사용된 컬럼과 서브컬럼 사이에 조인이 되어야한다.
결과가 하나라도 있으면 참 / 하나도 없으면 거짓
SELECT DEPARTMENT_ID AS 부서번호,
EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원이름
FROM HR.EMPLOYEES A
WHERE EXISTS ( SELECT 1 --WHERE절이 참이면 1을 출력 >> 결과 존재
FROM HR.DEPARTMENTS B
WHERE MANAGER_ID=100 -- 늘 참이므로 전체 107명 출력
AND A.DEPARTMENT_ID=B.DEPARTMENT_ID); --100번인 부서번호와 부서번호 조건사원테이블에서 각 부서별 평균급여보다 더 많은 급여를 받는 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서번호, 부서평균급여, 급여
SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
A.DEPARTMENT_ID AS 부서번호,
B.BAVG AS 부서평균급여,
A.SALARY AS 급여
FROM HR.EMPLOYEES A, (SELECT DEPARTMENT_ID AS BID,
ROUND(AVG(SALARY)) AS BAVG
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID) B
WHERE A.DEPARTMENT_ID=B.BID
AND A.SALARY>B.BAVG;SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
A.DEPARTMENT_ID AS 부서번호,
(SELECT ROUND(AVG(SALARY)) AS BAVG
FROM HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID) AS 부서평균급여,
A.SALARY AS 급여
FROM HR.EMPLOYEES A
WHERE A.SALARY>(SELECT ROUND(AVG(SALARY)) AS BAVG
FROM HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID)
ORDER BY 3, 5 DESC;EXISTS 연산자 사용
SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
A.DEPARTMENT_ID AS 부서번호,
(SELECT ROUND(AVG(SALARY)) AS BAVG
FROM HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID) AS 부서평균급여,
A.SALARY AS 급여
FROM HR.EMPLOYEES A
WHERE EXISTS( SELECT 1
FROM HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND A.SALARY>(SELECT ROUND(AVG(SALARY)) AS BAVG
FROM HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID))
ORDER BY 3, 5 DESC;