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

[전 수업] 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; --연 월 일 로 해서 날짜가 나옴

select to_char(hiredate,'mm') || '월',count(*)  from emp  group by to_char(hiredate,'mm')  order by to_char(hiredate,'mm');

emp테이블을  to_char을 사용해 hiredate character형의  'mm'형태로 조회한다

 

---

서브쿼리

10. 문) emp테이블에서 월급을 가장 많이 받는 사원정보를 조회하시오

1)  select max(sal) from emp;

2)   select *  from emp  where sal=(select max(sal) from emp);

 

 

11. 문)  평균 급여보다 많은 급여를 받는 사원의 이름,부서명,급여를 조회하시오

1) select avg(sal) from emp;

2)  select ename,deptno,sal 급여   from emp   where sal>(select avg(sal) from emp);

 

 

12. 문) 부서코드 10의 최고급여보다 더 많은 급여를 받는 직원목록을 조회하시오

1) select max(sal) from emp where deptno=10;

2)  select ename,sal  from emp  where sal>=(select max(sal) from emp where deptno=10);

 

 

13. 문)  손흥민과 같은 입사일에 입사한 사람들 중에서 
           박지성보다 급여를 적게 받는 사람의 이름,급여,입사일을 조회하시오

1) select hiredate from emp where ename='손흥민'     select sal from emp where ename='박지성'

2) select ename,sal,hiredate from emp where hiredate=(select hiredate from emp where ename='손흥민') AND     sal<(select sal from emp where ename='박지성');

※맞는 데이터가 없어 조회되지 않는다