4)MAX(col), MIN(col)
- 컬럼 col의 값 중 최대값(MAX), 최소값(MIN)을 반환
- 중첩불가능.
사용예) 각 부서별 최대급여와 최소급여를 조회하시오
SELECT DEPARTMENT_ID AS 부서코드,
MAX(SALARY) AS 최대급여,
MIN(SALARY) AS 최소급여
FROM HR.EMP
GROUP BY DEPARTMENT_ID
ORDER BY 1;
사용예) 각 부서별 최대급여와 최소급여를 받는 사원을 조회하시오 --두 개의 쿼리문
(부서별 최소급여)
SELECT DEPARTMENT_ID AS DID,
MIN(SALARY)AS NSAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID ;
(부서별 최대급여)
SELECT DEPARTMENT_ID AS DID,
MAX(SALARY)AS MSAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID ;
(최대급여 사원정보)
SELECT B.EMPLOYEE_ID AS 사원번호,
B.EMP_NAME AS 사원명,
A.DID AS 부서코드,
A.MSAL AS 최대급여
FROM (SELECT DEPARTMENT_ID AS DID,
--인라인서브쿼리. 단독 실행했을때 실행되어야함.
MAX(SALARY)AS MSAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID)A,-- 테이블조인
HR.EMP B
WHERE A.DID=B.DEPARTMENT_ID
--조인조건1)같은 부서코드 컬럼 추출
AND A.MSAL=B.SALARY --조인조건2)
ORDER BY 3;
(최소급여 사원정보)
SELECT B.EMPLOYEE_ID AS 사원번호,
B.EMP_NAME AS 사원명,
A.DID AS 부서코드,
A.MSAL AS 최대급여
FROM (SELECT DEPARTMENT_ID AS DID,
MIN(SALARY)AS MSAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID )A,
HR.EMP B
WHERE A.DID=B.DEPARTMENT_ID
AND A.MSAL=B.SALARY
ORDER BY 3;
사용예) 급여가 많은 5명의 사원정보 조회
SELECT A.EMP_NAME A.SALARY
FROM (SELECT EMP_NAME, --서브쿼리
SALARY
FROM HR.EMP
ORDER BY 2 DESC)A
WHERE ROWNUM <=5;
5) ROLLUP(col[,col,...])
- 다양한 종류의 집계를 반환
- GROUP BY 절에서만 사용됨
- 기술된 col의 순서(왼쪽->오른쪽)가 레벨을 결정하고 그 레벨별 집계를 반환
- 사용된 col의 갯수가 n개 일때 n+1 가지의 집계반환
ex) GROUP BY ROLLUP(C1,C2,C3) =>
C1,C2,C3를 기준으로 한 집계 =1
C1,C2를 기준으로 한 집계 =2
C1를 기준으로 한 집계 =3
전체집계 =4 --레벨사용안된것
사용예) 장바구니테이블에서 2005년 월별, 회원별, 상품별 판매집계(수량*단가)를 조회하시오.
(GROUP BY 절)
SELECT SUBSTR(A.CART_NO,5,2) AS 월,
A.CART_MEMBER AS 회원번호,
A.CART_PROD AS 상품코드,
SUM(A.CART_QTY) AS 판매수량합계,
SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '2005%'
GROUP BY SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD
ORDER BY 1 ,2;
(GROUP BY ROLL UP 절)
SELECT SUBSTR(A.CART_NO,5,2) AS 월,
A.CART_MEMBER AS 회원번호,
A.CART_PROD AS 상품코드,
SUM(A.CART_QTY) AS 판매수량합계,
SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '2005%'
GROUP BY ROLLUP(SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD)
ORDER BY 1 ,2;
(부분 ROLLUP: 일부 분류기준 컬럼이 ROLLUP 밖에 기술된 경우)
SELECT SUBSTR(A.CART_NO,5,2) AS 월,
A.CART_MEMBER AS 회원번호,
A.CART_PROD AS 상품코드,
SUM(A.CART_QTY) AS 판매수량합계,
SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '2005%'
GROUP BY SUBSTR(A.CART_NO,5,2), ROLLUP(A.CART_MEMBER,A.CART_PROD)
-- SUBSTR절과 ROLLUP절은 동등. -> 전체합 안나옴
-- 롤업: 처음엔 다 포함된 집계, 오른쪽에서 하나를 제거한집계, 다제거한 집계
ORDER BY 1 ,2;
6) CUBE(col[,col,...])
- 주어진 컬럼들을 조합해 나올 수 있는 모든 경우의 가지수만큼 다양한 집계반환
- 사용된 컬럼의 수가 n개일때 집계의 종류는 2^n 가지임
SELECT SUBSTR(A.CART_NO,5,2) AS 월,
A.CART_MEMBER AS 회원번호,
A.CART_PROD AS 상품코드,
SUM(A.CART_QTY) AS 판매수량합계,
SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '2005%'
GROUP BY CUBE(SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD)
ORDER BY 1 ,2;