Oracle 기본정리 - Group By 응용편

Oracle 기본정리 - Group By 응용편

-hr 계정의 테이블 사용

--부서별 연도별(01~03, 04~06, 07~) 인원을 출력하시오

select department_id, count(*),count(decode( to_char(hire_date,'yy'),'01',1,'02',1,'03',1)) as "01~03" ,
                               count(decode( to_char(hire_date,'yy'),'04',1,'05',1,'06',1)) as "04~06" ,
                               count(decode( to_char(hire_date,'yy'),'07',1)) as "07~"
                               from employees group by department_id  order by department_id asc;

--부서 20,50,80 및 90에 대해 직무,부서에 해당하는 급여 및 해당 직무에 대한 총 급여를 표시

select job_id, sum(decode(department_id,20,salary)) as dept20,
               sum(decode(department_id,50,salary)) as dept50,
               sum(decode(department_id,80,salary)) as dept80,
               sum(decode(department_id,90,salary)) as dept90,
               sum(salary) as total
               from employees group by job_id;

--사원의 총수와 2004년,2005년,2006,2007년에 채용한 사원수를 출력

select * from employees
select  count(decode(to_char(hire_date,'yyyy'),'2004',1)) as "2004"
        , count(decode(to_char(hire_date,'yyyy'),'2005',1)) as "2005"
        , count(decode(to_char(hire_date,'yyyy'),'2006',1)) as "2006"
        , count(decode(to_char(hire_date,'yyyy'),'2007',1)) as "2007"
        , count(*) as total
        from employees;

--급여가 2000이상인 사원의 부서별 평균급여

select * from emp;
select deptno, avg(sal) from emp where sal>2000 group by deptno;

--부서별 평균급여가 2000이상인 부서의 부서코드와 평균급여

select deptno, trunc(avg(sal)) from emp group by deptno having avg(sal)>2000;

--각부서별 인원수를 계산
--전체인원수 d10 d20 d30

select count(*) as "전체인원수",decode(deptno,10,1,0) as d10, decode(deptno,20,1,0) as d20, decode(deptno,30,1,0) as d30 from emp group by deptno;

--각 부서별 평균 급여를 계산
--전체인원수 d10 d20 d30

select count(*) as 전체인원수, trunc(decode(deptno,10,avg(sal),0)) as d10,
                             trunc(decode(deptno,20,avg(sal),0)) as d20,
                             trunc(decode(deptno,30,avg(sal),0))as d30 
                             from emp group by deptno;