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절에 급여합계를 넣어도 작동하지 않는다
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='박지성');