2019/07/23 Group by 명령어 & 서브쿼리

JAVA 교육/Sql

2019/07/23 Group by 명령어 & 서브쿼리

조별하 2019. 7. 24. 16:42

[전 수업] Orcle 함수에 대해 알아보았다

[본 수업] group by 명령어 와 서브쿼리를 공부해 보자

1. Group by
같은 데이터끼리 묶는 명령어이다 -->집계함수와 많이 사용한다
--sum() avg() max() min() count()

having : group by 와 한 쌍인 명령어 인데 group by로 묶인 데이터에서
따로 조건이 맞는 데이터를 추출하고 싶을 때 사용 (cf where 과 비슷)

view, index, tablejoin(정규형), 프로시저 등 앞으로 배워야 할 것

2. 문) 부서코드별로 급여정보를 조회

select deptno,count(*)
from emp
group by deptno;

depno로 그룹핑 시켜 depno가 몇명인지 조회

select deptno,count(*)
from emp
group by deptno
order by count(*);

부서코드별로 그룹핑한 인원수를 오름차순으로 정렬

3. group by 에러

위와 같이 deptno로 그룹핑을 해놓고 대표성이 없는 ename 는 사용이 불가능 하다

select deptno,ename
from emp
group by deptno
order by deptno;

부서코드 별로 묶어놓으면 대표성이 띄지 않는다

4. 실행순서 확인

select deptno 부서코드
        ,sum(sal) 합계
        ,round(avg(sal),0) 평균
        ,max(sal) 최대값
        ,min(sal) 최소값
        ,count(sal) 인원수 --실행순서 3
from emp --실행순서 1
group by deptno --실행순서 2
order by 부서코드 --실행순서 4

java 언어와 다르게 절차적으로 실행되지 않는다

5. 부서코드별 급여합계를 구하시오

select deptno,sum(sal)
from emp
group by deptno;

group by 와 집계함수와는 같이 사용할 수 있다

6. 부서코드별 급여합계를 구해서
그 합계값이 1500이상만 조회하시오

select deptno,sum(sal) 급여합계
from emp
group by deptno
having sum(sal)>=1500;

부서별로 그룹화시키고, 그 중의 급여합계가
1500이상인 조건을 having으로 걸어주었다

※실행순서가 having 절 먼저 되기 때문에 having절에 급여합계를 넣어도 작동하지 않는다

7. 문) 급여가 300이상 데이터중에서
부서코드별 급여평균을 구해서 급여순으로 조회하시오

select avg(sal),count(sal)
from emp
where sal>=300;

emp테이블에 where 조건절을 걸어 group by역할을 시킨다

8. 문) 부서별 급여총액(급여+커미션)이 가장 높은 급여총액과, 가장낮은 급여총액
급여총액의 평균금액을 반올림해서 소수점 1자리 까지만 출력

select deptno
        ,max(sal+nvl(comm,0)) max 급여총액
        ,min(sal+nvl(comm,0)) min 급여총액
        ,round(avg(sal+nvl(comm,0)),1) 평균급여총액
from emp
group by deptno
order by deptno;

nvl()함수를 이용하여 null값이 나온 데이터를 0으로 맞춤

9. 문) emp태이블에서 hiredate칼럼을 사용하여 월별로 입사한 인원수를 구하시오

※select hiredate from emp; --연 월 일 로 해서 날짜가 나옴