NULL
1. GROUP BY
GROUP BY와 HAVING 그리고 ORDER BY
ORDER BY절 : SELECT한 컬럼을 가지고 정렬할 때 사용함
ORDER BY 컬럼명| 컬럼별칭| 컬럼나열순번 [ASC]<<묵시적 | [DESC] <<명시적
ORDER BY 컬럼명정렬방식, 컬럼명 정렬방식, 컬럼명정력방식 ....
첫번째 기준으로 하는 컬럼에 대해 정렬하고
같은 값들에 대해 두번째 기준으로 하는 컬럼에 대해 다시정렬
SELECT 구문 맨 마지막에 위치하고 맨마지막에 실행됨
EX)
SELECT 구문 (동작순서 및 들어갈수 있는 값) 암기할 것!
5:SELECT 컬럼명 AS 별칭, 계산식 , 함수식으로 나열할수 있음
1:FROM 참조할테이블명
2:WHERE 컬럼명 | 함수식 뒤에는 비교연산자 비교값
3:GROUP BY 그룹을 묶을 컬럼명
4:HAVING 그룹함수식 비교연산자 비교값
6:ORDER BY 컬럼명 | 별칭 | 그리고 컬럼순번 정렬방식 그리고 [NULLS FIRST |LAST]
GROUP BY 예제)
SELECT
COUNT(*) 사원수
, DEPT_CODE
FROM EMPLOYEE
GROUP BY DEPT_CODE;
----------------------------------------------------------------
GROUP BY는 두개를 묶어서도 쓸 수 있다
EX)
SELECT
DEPT_CODE 1
, JOB_CODE 2
, SUM(SALATY) 3
, COUNT(*) 4
FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE
ORDER BY 1; (여기에 쓰여있는숫자는 우리가 SELECT절에서 작성한 순서를 의미한다.)
실습)
1.직원테이블에서 부서코드별 그룹을 지정하여 부서코드,
그룹별 급여의 합계,
그룹별 급여의평균(정수처리),인원수 조회하고 부서코드 순으로
오름차순 정렬
SELECT
DEPT_CODE
, SUM(SALARY)
, TRUNC(AVG(SALARY))
, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE ASC;
**2. 직원테이블에서 직급코드, 보너스를 받는 사원수를 조회하여
직급코드 순으로 오름차순 정렬하세요
SELECT
JOB_CODE
, COUNT(*)
FROM EMPLOYEE
WHERE BONUS IS NOT NULL
GROUP BY JOB_CODE
ORDER BY JOB_CODE ASC;
**3.직원 테이블에서 주민번호 8번째 자리를 조회하여 1이면 남,
2이면 여로 결과조회하고, 성별별로 급여 평균(정수처리)
,급여합계 인원수 조회한뒤 인원수로 내림차순정렬하세요
SELECT
COUNT(*)
, FLOOR(AVG(SALARY))
, SUM(SALARY)
, DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여'
FROM EMPLOYEE
GROUP BY DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여'
ORDER BY COUNT(*) DESC
GROUP BY절 : 같은 값들이 여러개 기록된 컬럼을 가지고
하나의 그룹을 지정하기 위한 절
GROUP BY 컬럼명 | 함수식, ...
여러 개의 값을 묶어서 하나의 그룹으로 처리할 목적으로 사용한다.
그룹으로 묶은 값에 대해서 SELECT 절에서 그룹함수를 사용한다.
-----------------------------------------------------------------------------------
2. HAVING 절 : 그룹함수로 구해올 그룹에 대해 조건을 설정할 때 사용
HAVING 컬럼명 | 함수식 비교연산자 비교값
HAVING절이 있는거와 없는거에 대해 차이를 알아보자
1)HAVING절 없는 경우
SELECT
DEPT_CODE
, FLOOR(AVG(SALARY))
1 FROM EMPLOYEE
2 WHERE SALARY > 3000000
WHERE가 먼저 기술되어있으면 그룹하기전에 먼저 행들을 판단해 결과추출
그 결과를 GROUP BY에서 그룹화함
3 GROUP BY DEPT_CODE
ORDER BY 1;
2)HAVING절이 있는 경우
SELECT
DEPT_CODE
, FLOOR(AVG(SALARY))
1 FROM EMPLOYEE
2 GROUP BY DEPT_CODE
(그룹을 지정하고 나서 지정된 그룹에서)
3.HAVING FLOOR(AVG(SALARY) > 3000000
(그 그룹에 선택하는조건)
ORDER BY 1;
실습)
부서별 그룹의 급여합계중 9백만원을 초과한부서의
부서코드와 급여합계 조회
1.
SELECT
DEPT_CDOE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING SUM(SALARY) > 9000000;
2. 급여 합계가 가장많은 부서의 부서코드와 급여 합계를 구하세요
1)SELECT
MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
2) SELECT
DEPT_CODE
, MAX(SUM (SALARY))
FROM EMPLYEE
GROUP BY DEPT_CODE
HAVING MAX(SUM(SALATY)) = 17700000;
--------------------------------------------------------------------------------
SELECT
DEPT_CODE
, MAX(SUM (SALARY))
FROM EMPLYEE
GROUP BY DEPT_CODE
HAVING MAX(SUM(SALATY)) = (SELECT
MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE);
--------------------------------------------------------------------------------
3. 집계함수
ROLL UP 함수 : 그룹별로 중간집계 처리하는 함수
GROUP BY절에서 사용하는 함수
그룹별로 묶여진값에 대한 중간집계와 총집계를 구할때 사용
그룹별로 계산 된 결과값들에 대한 총 집계가 자동으로 추가됨
EX)
SELECT
JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(JOB_CODE)
ORDER BY 1;
출력예시(마지막줄 NULL의 값은 모든 JOB_CODE를 의미한다.
마지막줄 값은 모든 급여의 합을 의미)
J1 8000000
J2 9700000
J3 10800000
J4 9320000
J5 8460000
J6 15746240
J7 8070000
NULL 70096240
***** 인자로 전달한 그룹 중에서 가장 먼저 지정한 그룹별 합계와
총 합계를 구하는 함수 (ROLLUP)
EX)
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(DEPT_CODE, JOB_CODE)
ORDER BY 1;
출력예시
DEPT_CODE 인자가 첫번째에 지정해서 DEPT_CODE 별로 중간합계 설정후
마지막 전쳬합계 설정된다.
D1 J6 6440000
D1 J7 1380000
D1 NULL 7820000 >>>>>>>>>>>> D1부서의 모든직급 급여의합
D2 J4 6520000
D2 NULL 6520000 >>>>>>>>>>>> D2부서의 모든직급 급여의합
D5 J3 3500000
D5 J5 8460000
D5 J7 3800000
D5 NULL 15760000 >>>>>>>>>>>> D5부서의 모든직급 급여의합
D6 J3 7300000
D6 J4 2800000
D6 NULL 10100000 >>>>>>>>>>>> D6부서의 모든직급 급여의합
D8 J6 6986240
D8 NULL 6986240 >>>>>>>>>>>> D8부서의 모든직급 급여의합
D9 J1 8000000
D9 J2 9700000
D9 NULL 17700000 >>>>>>>>>>>> D9부서의 모든직급 급여의합
NULL J1 8000000
NULL J2 9700000
NULL J3 10800000
NULL J4 9320000
NULL J5 8460000
NULL J6 2320000
NULL J6 15746240
NULL J7 2890000
NULL J7 8070000
NULL NULL 5210000
NULL NULL 70096240 모든값의 합
---------------------------------------------------------------------------------
4.CUBE 함수 : 그룹별 산출한 결과를 집계하는 함수이다.
SELECT
JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(JOB_CODE)
ORDER BY 1;
그룹으로 지정된 모든그룹에 대한 집계와 총 합계를 구하는 함수
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
출력예시
CUBE함수는 두인자 모두 중간 합계를 구해주고 마지막에 총합계를 구해준다
ROLLUP함수에서 + 두번째 인자중간합계가 추가된게 = CUBE함수
출력예시
D1 J6 6440000
D1 J7 1380000
D1 NULL 7820000
D2 J4 6520000
D2 NULL 6520000
D5 J3 3500000
D5 J5 8460000
D5 J7 3800000
D5 NULL 15760000
D6 J3 7300000
D6 J4 2800000
D6 NULL 10100000
D8 J6 6986240
D8 NULL 6986240
D9 J1 8000000
D9 J2 9700000
D9 NULL 17700000
NULL J1 8000000
NULL J2 9700000
NULL J3 10800000
NULL J4 9320000
NULL J5 8460000
NULL J6 2320000
NULL J6 15746240 >>>>>>>>>>>>>>>>DEPT_CODE인자의 정렬된 JOB_CODE부분에
J6부분의 모든 합(나머지도 이와같다)
NULL J7 2890000
NULL J7 8070000
NULL NULL 5210000
NULL NULL 70096240
---------------------------------------------------------------------------------------
5.GROUPING 함수 : ROOL UP이나 CUBE에 의한 산출물이 인자로 전달받은
컬럼 집합의산출물이면 0을반환하고 아니면 1반환하는 함수
EX)
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
, CASE
WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별합계'
WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(J0B_CODE) = 0 THEN '직급별합계'
WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING (JOB_CODE) = 0 THEN '그룹별합계'
ELSE '총합계'
END AS 구분
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
출력예시
:
D1 J6 6440000 그룹별합계
D1 J7 1380000 그룹별합계
D1 NULL 7820000 부서별합계
D2 J4 6520000 그룹별합계
D2 NULL 6520000 부서별합계
D5 J3 3500000 그룹별합계
D5 J5 8460000 그룹별합계
D5 J7 3800000 그룹별합계
D5 NULL 15760000 부서별합계
D6 J3 7300000 그룹별합계
D6 J4 2800000 그룹별합계
D6 NULL 10100000 부서별합계
D8 J6 6986240 그룹별합계
D8 NULL 6986240 부서별합계
D9 J1 8000000 그룹별합계
D9 J2 9700000 그룹별합계
D9 NULL 17700000 부서별합계
NULL J1 8000000 직급별합계
NULL J2 9700000 직급별합계
NULL J3 10800000 직급별합계
NULL J4 9320000 직급별합계
NULL J5 8460000 직급별합계
NULL J6 2320000 그룹별합계
NULL J6 15746240 직급별합계
NULL J7 2890000 그룹별합계
NULL J7 8070000 직급별합계
NULL NULL 5210000 부서별합계
NULL NULL 70096240 총합계
SQL
복사
