GROUP BY 예제 - Oracle

1. 평균 연봉 이상 연봉을 받는 직원을 출력하면?

select
    *
from
    employee
where
    salary>=(select avg(salary) from employee);

2. 연봉 총합, 평균 연봉, 최대 연봉, 최저 연봉, 최저 입사일을 출력하면?

select
    sum(salary) "연봉총합"
    , avg(salary) "평균연봉"
    , max(salary) "최대연봉"
    , min(salary) "최저연봉"
    , min(hire_date) "최저입사일"
from
    employee;

3. NULL 값은 계산에서 빠짐. 만약 NULL 값을 0으로 놓고 계산에 참여하고 싶다면?

select
    sum(nvl(salary,0)) "연봉총합"
    , avg(nvl(salary,0)) "평균연봉"
    , max(nvl(salary,0)) "최대연봉"
    , min(nvl(salary,0)) "최저연봉"
    , min(hire_date) "최저입사일"
from
    employee;

4. 고객 총수, 담당 직원 있는 고객 총수, 고객담당 직원수(중복제거)를 출력하면?

select
    count(*) "고객총수"
    , count(emp_no) "담당직원있는고객총수"
    , count(distinct(emp_no)) "고객담당직원수"
from
    customer;

5. 부서별로 부서번호, 급여합, 평균 급여, 인원수를 출력하면?

select
    dep_no "부서번호"
    , sum(salary) "급여합"
    , avg(salary) "평균급여"
    , count(*) || '명' "인원수"
from
    employee
group by dep_no;

* select 절에 일반 컬럼과 그룹함수 컬럼이 등장하면 group by 뒤에 반드시 일반 컬럼이 나와야함

6. 직급별로 직급, 급여합, 평균급여, 인원수를 출력하면?

select
    jikup "직급"
    , sum(salary) "급여합"
    , avg(salary) "평균급여"
    , count(*) || '명' "인원수"
from
    employee
group by jikup;

7. 부서별, 직급별, 부서번호, 직급, 급여합, 평균 급여, 인원수를 출력하면?

select
    dep_no "부서번호"
    , jikup "직급"
    , sum(salary) "급여합"
    , avg(salary) "평균급여"
    , count(*) || '명' "인원수"
from
    employee
group by dep_no, jikup;

8. 부서별로 직급별 부서번호, 직급, 급여합, 평균 급여, 인원수를 출력하되 인원수를 3명 이상을 출력하면?

select
    dep_no "부서번호"
    , jikup "직급"
    , sum(salary) "급여합"
    , round(avg(salary), 1) "평균급여"
    , count(*) "인원수"
from
    employee
group by dep_no, jikup having count(*) >= 3;

* having 절에 별칭을 사용하지 않음

select
    *
from
    (select dep_no "DEP_NO" , jikup "JIKUP" , sum(salary) "SALARY_SUM" , round(avg(salary), 1) "SALARY_AVG" , count(*) "CNT" from employee group by dep_no, jikup)
where
    cnt>=3;

9. 부서별, 성별로 부서 번호, 성, 급여합, 평균 급여, 인원수를 출력하면?

select
    dep_no "부서번호"
    , (case substr(jumin_num, 7, 1) when '1' then '남' when '3' then '남' else '여' end) "성별"
    , sum(salary) "급여합"
    , avg(salary) "평균급여"
    , count(*) "인원수"
from
    employee
group by dep_no, (case substr(jumin_num, 7, 1) when '1' then '남' when '3' then '남' else '여' end)
order by "부서번호";

10. 입사연도별로 입사년도(일년단위), 인원수를 출력하고 년도별로 오름차순 하면?

select
    extract(year from hire_date) "입사년도"
    , count(*) "인원수"
from
    employee
group by extract(year from hire_date)
order by "입사년도";

 

11. 직원명, 생일(년-월-일), 나이를 출력하면?

select
    emp_name "직원명"
    , (to_char( to_date( case when (sysdate - to_date(extract(year from sysdate) || substr(jumin_num, 3, 4), 'YYYY-MM-DD')) < 0 then extract(year from sysdate) else (extract(year from sysdate) + 1) end || substr(jumin_num, 3, 4)) , 'YYYY-MM-DD')) "생일"
    , (extract(year from sysdate) - to_number( (case substr(jumin_num, 7, 1) when '1' then '19' when '2' then '19' when '3' then '20' when '4' then '20' end || substr(jumin_num, 0, 2)))+1) "나이"
from
    employee;

12. 부서별로 부서 번호, 평균 근무년수를 출력하면?
<조건> 근년수는 소수점 첫째 자리까지 반올림할 것

select
    dep_no "부서번호"
    , round(avg((sysdate - hire_date)/365), 1) || '년 근무' "평균근무년수"
from
    employee
group by dep_no;

13. 입사 분기별로 입사 분기, 인원수를 출력하면?

select
    to_char(hire_date, 'Q') || '/4분기' "입사분기"
    , count(*) "인원수"
from
    employee
group by to_char(hire_date, 'Q') || '/4분기';

14. 만약 10번 부서 입사 분기별 입사 분기, 인원수를 출력하면?

select
    to_char(hire_date, 'Q') || '/4분기' "입사분기"
    , count(*) "인원수"
from
    employee
where
    dep_no = 10
group by to_char(hire_date, 'Q') || '/4분기';

15. 입사 분기별로 입사 분기, 인원수를 출력하면?
<조건> 단, 1분기만 보여라

select
    to_char(hire_date, 'Q') || '/4분기' "입사분기"
    , count(*) "인원수"
from
    employee
group by to_char(hire_date, 'Q')
having to_char(hire_date, 'Q') || '/4분기' = '1/4분기';

16. 입사 연대별, 성별로 입사 연대, 성, 연대별 입사 자수 출력하면?

select
    trunc(extract(year from hire_date), -1) "입사연대"
    , (case substr(jumin_num, 7, 1) when '1' then '남' when '3' then '남' else '여' end) "성별"
    , count(*) "연대별입사자수"
from
    employee
group by trunc(extract(year from hire_date), -1) , (case substr(jumin_num, 7, 1) when '1' then '남' when '3' then '남' else '여' end)
order by "입사연대";

17. 직원명, 입사일(년-월-일 ~/4분기 ~요일), 퇴직일(년-월-일) 출력하면?
<조건> 퇴직일은 입사 후 20년 5개월 10일 후

select
    emp_name "직원명"
    , to_char(hire_date, 'YYYY-MM-DD Q') || '/4분기 ' || to_char(hire_date, 'DAY', 'NLS_DATE_LANGUAGE = Korean') "입사일"
    , to_char(add_months(hire_date + (365*20), 5) + 10, 'YYYY-MM-DD') "퇴직일"
from
    employee;

※ to_char() to_char( date자료형, ‘DAY’) : 요일을 ‘SUNDAY’ ~ ‘SATHURDAY’로 리턴
to_char( date자료형, ‘DY’) : 요일을 ‘SUN’ ~ ‘SAT’로 리턴
to_char( date자료형, ‘D’) : 요일을 ‘1’ ~ ‘7’로 리턴
to_char( date자료형, ‘DAY’, ‘NLS_DATE_LANGUAGE = Korean’) : 요일을 ‘일요일’ ~ ‘토요일’로 리턴
to_char( date자료형, ‘DY’, ‘NLS_DATE_LANGUAGE = Korean’) : 요일을 ‘일’ ~ ‘토’로 리턴
to_char( date자료형, ‘D’, ‘NLS_DATE_LANGUAGE = Korean’) : 요일을 ‘1’ ~ ‘7’로 리턴

18. 직원들이 있는 부서별로 부서 번호, 부서위치, 직원수를 출력하면?

select
    d.dep_no "부서번호"
    , d.loc "부서위치"
    , count(e.emp_no) "직원수"
from
    employee e, dept d
where
    e.dep_no = d.dep_no
group by d.dep_no, d.loc;

select
    d.dep_no "부서번호"
    , d.loc "부서위치"
    , (select count(*) from employee e where e.dep_no=d.dep_no) "직원수"
from
    dept d
where
    (select count(*) from employee e where e.dep_no=d.dep_no) > 0;

19. 모든 부서별로 부서번호, 부서위치, 직원수를 출력하면?

select
    d.dep_no "부서번호"
    , d.loc "부서위치"
    , count(e.emp_no) "직원수"
from
    employee e, dept d
where
    e.dep_no(+) = d.dep_no
group by d.dep_no, d.loc;

select
    d.dep_no "부서번호"
    , d.loc "부서위치"
    , (select count(*) from employee e where e.dep_no=d.dep_no) "직원수"
from
    dept d;