데이터베이스 20180817 (학원3)

2018-08-17

@ GROUP BY

 -> 그룹함수는 단 한개의 결과값 리턴

 -> 그룹함수를 이용해서 여러개의 결과값을 리턴받기위해 그룹의 기준을 지정

@ HAVING

 -> 그룹함수로 값을 구해올 그룹에 대해서 조건설정시에는 HAVING 절 조건을 기입

(그룹함수를 사용한 WHERE 절은 사용할 수 없음)

DEPT_CODE -> 급여 평균이 3000000원 이상인 그룹에 대해서 평균값 검색

@ ROLLUP / CUBE

ROLLUP : 가장 먼저 지정한 그룹별 합게와 총 합계를 구함

CUBE : 그룹에 지정된 모든 그룹에 대한 합계와 총합계를 구함

@ GROUPING

ROLLUP이나 CODE에 의한값이면 1이 리턴 아니면 0을 리턴

==============================================

-- 2018-08-17

select COUNT(SALARY) FROM EMPLOYEE;

select sum(SALARY) FROM EMPLOYEE;

select DEPT_CODE, SUM(SALARY) FROM EMPLOYEE; --이건 에러난다. SUM은 결과값이 하나고 매칭이안되서 그렇다.

select DEPT_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE;

--D1그룹의 총합 총합 이렇게 출력이 된다.

select DEPT_CODE, SUM(SALARY), FLOOR(AVG(SALARY)), count(*) FROM EMPLOYEE GROUP BY DEPT_CODE order by 2;

/*

EMPLOYEE 테이블에서 EMP_NO 8번째자리가 1이면 '남',2이면 '여'로 결과조회

성별별로 평균급여, 급여합계, 인원수 조회

*/

select * from EMPLOYEE;

select DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여') as 성별,FLOOR(AVG(SALARY)) as 평균급여, sum(SALARY) as 급여합계

, COUNT(*) 인원수 from EMPLOYEE Group by DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여');

select DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여') as 성별,FLOOR(AVG(SALARY)) as 평균급여, sum(SALARY) as 급여합계

, COUNT(*) 인원수 from EMPLOYEE Group by EMP_NO;

/*

DEPT_CODE가 D1이면서 J6인 인원수가 2명 이렇게 나온다.

*/

select DEPT_CODE, JOB_CODE, COUNT(*) FROM EMPLOYEE GROUP BY DEPT_CODE, JOB_CODE order by 1;

/*

1. 직급별 총 급여 및 연봉을 출력 -> JOB_CODE

*/

select JOB_CODE as 직급,sum(SALARY) as 급여, sum(salary*12) as 연봉 FROM EMPLOYEE group by JOB_CODE;

/*

2. 부서코드, 그룹별 급여의 합계, 평균, 인원수

( 부서명으로 오름차순 정렬)

*/

select * from EMPLOYEE;

select DEPT_CODE as 부서코드,sum(salary) 합계,AVG(salary) 평균, COUNT(*)인원수 FROM EMPLOYEE group by DEPT_CODE order by 1 desc;

/*

3. 부서코드별 보너스를 지급받는 사원수를 조회

*/

select DEPT_CODE as 부서코드,  count(*) FROM EMPLOYEE where BONUS is not null group by DEPT_CODE;

/*

4. 직급이 J1을 제외하고 직급, 직급별 사원수 및 평균급여

*/

select * from EMPLOYEE;

select JOB_CODE 직급 , avg(salary) 평균급여, count(*) from EMPLOYEE where JOB_CODE!='J1' group by JOB_CODE;

/*

5. 부서내 성별 인원수를 출력

EX)

D1 남 2

D2 여 1

*/

select * from EMPLOYEE;

select DEPT_CODE, DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여') as 성별

, COUNT(*) 인원수 from EMPLOYEE Group by DEPT_CODE, DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여') ORDER by 1;

/*

(그룹함수를 사용한 WHERE 절은 사용할 수 없음)

DEPT_CODE -> 급여 평균이 3000000원 이상인 그룹에 대해서 평균값 검색

*/

select DEPT_CODE, FLOOR(AVG(SALARY)) FROM EMPLOYEE GROUP BY DEPT_CODE

HAVING FLOOR(AVG(SALARY))>=3000000;

--HAVING같은경우 그룹함수 적용되고나서 조건을 넣을경우 HAVING을 넣는다.

select DEPT_CODE, COUNT(*) FROM EMPLOYEE GROUP BY DEPT_CODE ORDER BY 1;

select DEPT_CODE, COUNT(*) FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE) ORDER BY 1;

--마지막에 한줄 더생기는데 이건 전체 합이다.

select DEPT_CODE, COUNT(*) FROM EMPLOYEE GROUP BY CUBE(DEPT_CODE) ORDER BY 1;

--마지막에 한줄 더생기는데 이건 전체 합이다.CUBE도 동일하다.

select DEPT_CODE, JOB_CODE, COUNT(*) FROM EMPLOYEE GROUP by DEPT_CODE, JOB_CODE ORDER by 1;

select DEPT_CODE, JOB_CODE, COUNT(*) FROM EMPLOYEE GROUP by ROLLUP(DEPT_CODE, JOB_CODE) ORDER by 1;

--D1의 총합도 표시해준다.ROLLUP은 첫번째것만 기준을 정해서 하는데

select DEPT_CODE, JOB_CODE, COUNT(*) FROM EMPLOYEE GROUP by CUBE(DEPT_CODE, JOB_CODE) ORDER by 1;

--D1의 총합도 표시해준다. -> CUBE를 쓸경우 ROLLUP로 하고 있는걸 다 기준으로 정리를 해준다.

/*

부서내 직급별 급여합계 

*/

select * from EMPLOYEE;

select DEPT_CODE 직급, JOB_CODE,sum(salary) from EMPLOYEE GROUP by DEPT_CODE,JOB_CODE;

select DEPT_CODE 직급, sum(salary) from EMPLOYEE GROUP by CUBE(DEPT_CODE);

select DEPT_CODE, COUNT(*), GROUPING(DEPT_CODE) FROM EMPLOYEE GROUP BY DEPT_CODE;

select DEPT_CODE, COUNT(*), GROUPING(DEPT_CODE) FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE);

--새로생긴 값에 대해서는 1이 출력된다.

select DEPT_CODE, COUNT(*),CASE WHEN GROUPING(DEPT_CODE)=0 THEN '부서별합계' ELSE '총합계'

END FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE);

select NVL(DEPT_CODE,'인턴'), COUNT(*),CASE WHEN GROUPING(DEPT_CODE)=0 THEN '부서별합계' ELSE '총합계'

END FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE);

select NVL(DEPT_CODE,'인턴'), COUNT(*),CASE WHEN GROUPING(DEPT_CODE)=0 THEN '부서별합계' ELSE '총합계'

END FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE);

select DECODE(GROUPING(DEPT_CODE),0,NVL(DEPT_CODE,'인턴'),'합계'), COUNT(*) FROM EMPLOYEE GROUP by ROLLUP(DEPT_CODE);

--0인경우 DEPT코드를출력해줘서 인턴으로 표시하고 이게 아닐경우 합계란 것이 출력된다.

SELECT dept_code,  JOB_CODE, COUNT(*), GROUPING(DEPT_CODE), GROUPING(JOB_CODE)

FROM EMPLOYEE GROUP BY CUBE(DEPT_CODE,JOB_CODE) ORDER BY 1;

--인원수는 한명이고 그냥 그룹핑으로 나오는 녀석이라 0이고 0이다.

--D1이라는 녀석은 null이나왔고 이건 CUBE때문에 나왔던 녀석이라 그룹핑이 1이 나온다.

--합계로인해서 출력된 녀석이기때문에 이렇게 나온것이다.

--밑에꺼를 본다면 J1속에 NULL이면 어쩔수없이 1이 나온것이다.

--밑에꺼를 본다면 J2속에 NULL이여서 어쩔수없이 1이 나온것이다.

select DECODE(GROUPING(DEPT_CODE),0,NVL(DEPT_CODE,'인턴'),'합계') as 부서코드,

DECODE(GROUPING(JOB_CODE),0,NVL(JOB_CODE,'직급없음'),'합계') as 직급코드, count(*),

CASE WHEN GROUPING(DEPT_CODE)=0 AND GROUPING(JOB_CODE)=1 THEN '부서별 합계' 

WHEN GROUPING(DEPT_CODE)=1 AND GROUPING(JOB_CODE)=0 THEN '직급별 합계'

WHEN GROUPING(DEPT_CODE)=1 AND GROUPING(JOB_CODE)=1 THEN '직급별 합계'

ELSE '그룹별 합계' END as 구분

FROM EMPLOYEE GROUP BY CUBE(DEPT_CODE,JOB_CODE) ORDER BY 1;

/*

널값이 있다면 인턴으로 바꿔주고 그런거 아니면 합계로 출력해준다.

JOB코드를 기준으로 null이면 직급없음 아니면 합계로 표시를 해준다.

그후 인원수가 된다.

*/