EXTRACT
HAVING절
GROUP BY에서만 사용 가능하고, COUNT(), SUM()등의 GROUP함수에 조건 부여 가능
- Having절 기술 시 처리 순서
- Row들이 Group되어 진다
- Group에 대해 Group Function이 적용된다
- Having절을 만족하는 Group을 선택한다
SELECT column1명, column2명…, group함수(column명)
FROM table1명…
GROUP BY column1명, column2명…
HAVING 그룹조건식
--HAVING절 정리
--상품분류가 컴퓨터제품('P101')인 상품의 2020년도 일자별 판매 조회
SELECT SUBSTR(CART.CART_NO, 1, 8) 판매일
, SUM(CART.CART_QTY ?* PROD.PROD_SALE) 판매금액
, SUM(CART.CART_QTY) 판매수량
, COUNT(*) 판매횟수
--, TO_CHAR(SUM(CART.CART_QTY * PROD.PROD_SALE), 'L999,999,999') 판매금액
--, SUM(CART.CART_QTY) 판매수량
FROM PROD INNER JOIN CART
ON(PROD.PROD_ID = CART.CART_PROD)
--FROM PROD, CART
WHERE CART.CART_NO LIKE '2020%'
AND PROD.PROD_ID = CART.CART_PROD
--AND PROD.PROD_LGU = 'P101'
GROUP BY SUBSTR(CART.CART_NO, 1, 8)
HAVING SUM(CART.CART_QTY * PROD.PROD_SALE) > 5000000
ANDSUM(CART.CART_QTY) > 50
AND COUNT(*) >= 8
ORDER BY SUBSTR(CART.CART_NO, 1, 8);서브쿼리(Subquery)
: 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장
: SELECT, UPDATE, DELETE, INSERT와 같은 DML문과 CREATE TABLE 또는 VIEW에서 이용
: FROM 절에 사용하는 경우 View와 같이 독립된 테이블처럼 활용 => inline view
: 알려지지 않은 조건에 근거한 값들을 검색하는 SELECT 문장을 작성하는데 유용
: MAIN QUERY가 실행되기 이전에 한번 실행
: 괄호로 묶어야 하고 연산자의 오른쪽에 나타나야 함
: Main query 와 Sub query 사이의 참조성 여부에 따라 연관(Correlated) 또는 비연관 (Noncorrelated) 서브쿼리로 구분
: 반환하는 행의 수, 컬럼수에 따라 단일행/다중행, 단일컬럼/다중컬럼으로 구분
비교 연산자들 사용
- 단일 행 연산자(=,>, >=, <, <=, <>, !=)
- 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)
단일 행(Sing-Row) 서브쿼리
- 오직 한개의 행(값)을 반환
- 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용 할 수 있음
SELECT ENAME, JOB
FROM EMP
WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7521);다중 행(Multiple-Row) 서브쿼리
- 하나 이상의 행을 RETURN하는 SUBQUERY : 다중 행 SUBQUERY
- 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS) 사용 가능
IN 연산자의 사용 예제
예제)부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제
SELECT empno,ename,sal,deptno
FROM emp
WHERE sal IN (SELECT MAX(sal)
FROM emp
GROUP BY deptno);
ANY 연산자의 사용 예제
- ANY연산자는 서브쿼리의 결과값중 어느 하나의 값이라도 만족이 되면 결과값을 반환
SELECT ENAME, SAL
FROM EMP WHERE DEPTNO != 20
AND SAL > ANY(SELECT SAL FROM EMP WHERE JOB = '영업사원');
ALL 연산자의 사용 예제
- ALL연산자는 서브쿼리의 결과값중 모든 결과 값이 만족 되야만 결과값을 반환
SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ALL(SELECT sal FROM emp WHERE job='SALESMAN');
EXISTS 연산자의 사용 예제
- EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가 여부를 먼저 따져 존재하는 값들만을 결과로 반환
- SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓
SELECT empno, ename, sal
FROM emp e
WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr);
Pairwise(쌍비교) Subquery
서브쿼리가 한번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 줍니다
Nonpairwise(비쌍비교) Subquery
서브쿼리가 여러 조건별로 사용 되어서 결과값을 주 쿼리로 넘겨 줍니다.
Null Values in a Subquery
서브쿼리에서 null값이 반환되면 주 쿼리 에서는 어떠한 행도 반환되지 않습니다.FROM절상의 서브쿼리(INLINE VIEW)
INLINE VIEW란 FROM절상에 오는 서브쿼리로 VIEW처럼 작용
예제)급여가 20부서의 평균 급여보다 크고 사원을 관리하는 사원으로서 20부서에 속하지 않은 사원의 정보
SELECT B.EMPNO, B.ENAME, B.JOB, B.SAL, B.DEPTNO
FROM(SELECT EMPNO FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20)) A, EMP B
WHERE A.EMPNO = B.EMPNO
AND B.MGR IS NOT NULL
AND B.DEPTNO != 20;상관관계 서브쿼리
: 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식
=> 주 쿼리에서 서브 쿼리를 참조하고 이 값을 다시 주 쿼리로 반환을 의미
예제) 사원을 관리할 수 있는 사원의 평균급여보다 급여 많이 받는 사원 정보를 출력
SELECT EMPNO, ENAME, SAL
FROM EMP E
WHERE SAL > (SELECT AVG(SAL) SAL FROM EMP WHERE E.EMPNO = MGR);집합 쿼리(UNION, INTERSECT, MINUS)
:집합 연산자를 사용시 집합을 구성할 컬러의 데이터 타입이 동일해야 함
UNION : 두 테이블의 결합을 나타냄 => 중복되지 않은 값들을 반환
SELECT DEPTNO FROM EMP UNION
SELECT DEPTNO FROM DEPT;UNION ALL : UNION과 같으나 두 테이블의 중복되는 값까지 반환
SELECT DEPTNO FROM EMP UNION ALL
SELECT DEPTNO FROM DEPT;INTERSECT : 두 행의 집합중 공통된 행을 반환
SELECT DEPTNO FROM EMP INTERSECT
SELECT DEPTNO FROM DEPT;MINUS : 첫번째 SELECT문에 의해 반환되는 행중에서 두번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 보여줌
SELECT DEPTNO FROM DEPT MINUS
SELECT DEPTNO FROM EMP;-- 상품코드, 상품명, 분류명을 조회
--(Inner Join)
SELECT P.PROD_ID, P.PROD_NAME, L.LPROD_NM
FROM PROD P
INNER JOIN LPROD L
ON (P.PROD_LGU = L.LPROD_GU);
--(Subquery)
SELECT PROD.PROD_ID, PROD.PROD_NAME
, (SELECT LPROD.LPROD_NM FROM LPROD WHERE PROD.PROD_LGU = LPROD.LPROD_GU)
FROM PROD;
--상품Table에서 판매가가 상품평균판매가 보다 큰 상품을 검색
SELECT A.PROD_NAME 상품명
, TO_CHAR(A.PROD_SALE, '999,999,999') 판매가
, TO_CHAR(B.AVG_SALE, '999,999,999') 평균판매가
FROM PROD A
, (SELECT AVG(PROD_SALE) AVG_SALE FROM PROD) B
WHERE A.PROD_SALE > B.AVG_SALE;
--회원Table에서 마일리지가 평균마일리지 보다 큰 회원을 검색
SELECT M.MEM_NAME 회원명
, M.MEM_MILEAGE 마일리지
, A.AVG_MILEAGE 평균마일리지
FROM MEMBER M,
(SELECT AVG(MEM_MILEAGE) AVG_MILEAGE FROM MEMBER) A
WHERE M.MEM_MILEAGE > A.AVG_MILEAGE;
--위 평균마일리지는 2명의 정보가 빠져있다. 수정하시오.
--(마일리지는 천단위 구분, 평균마일리지는 소숫점이하 2자리로) : NULL인 사람도 처리
SELECT M.MEM_NAME 회원명
, M.MEM_MILEAGE 마일리지
, TO_CHAR(A.AVG_MILEAGE, '999,999,999,00') 평균마일리지
FROM MEMBER M
, (SELECT ROUND(AVG(NVL(MEM_MILEAGE, 0)), 2) AVG_MILEAGE FROM MEMBER) A
WHERE M.MEM_MILEAGE > A.AVG_MILEAGE;
기록