-- 각 부서별 인원수, 최대 급여
select min(sal), max(sal), sum(sal)/count(sal) as 평균, round(avg(sal),1), count(sal) from emp;
group by: 칼럼을 그룹으로 묶는다. 여러 칼럼으로 그룹을 묶을 수 있다.
having: group by 이후 조건 따지기
select deptno 부서번호, min(sal) min, max(sal) max, round(avg(sal),2) as 평균, count(sal) '사원 수'
from emp
group by deptno order by deptno;
select*from emp group by deptno; -- 부서번호를 그룹으로 했는데 모든 칼럼을 출력하려고 해서 에러
select에 칼럼을 적으려는 경우, 그룹화 한 칼럼만 적을 수 있다.
그룹화하지 않은 다른 칼럼을 적을 경우 에러가 난다.
문제)
-- 문) 부서별 근무 인원 출력 / 출력 예) 10 3명
select deptno, concat(count(deptno), '명') as 인원 from emp group by deptno order by deptno;
select deptno, concat(count(*), '명') as 인원 from emp group by deptno order by deptno;
count(*): 모든 칼럼을 비교해서 null값이 아닌 행의 수를 측정한다.
예제)
-- 10번 부서를 제외한 부서별 근무 인원 출력
select deptno, concat(count(*), '명') as 인원 from emp
where deptno<>10 group by deptno order by deptno;
-- 10번 부서 제외한 부서, 직급 별 근무 인원, 2명 이상
select deptno, job, concat(count(*), '명') as 인원 from emp
where deptno<>10 group by deptno, job having count(*)>1 order by deptno, job;
문제)
-- 문) 부서 내 직급별 인원수와 최대급여 검색, 부서번호 오름차순 검색
select deptno '부서번호', job '직급', count(*) '인원 수', max(sal) '최대급여'
from emp group by deptno, job order by deptno;
select deptno '부서번호', job '직급', count(*) '인원 수', max(sal) '최대급여'
from emp group by deptno, job with rollup;
with rollup: count, min 등 select에서 사용한 칼럼 그룹 단위로 집계 값을 출력한다.
단, order by와 같이 사용하지 못한다.
-- 부서별 입사일 1981-01-01 이후 입사한 사원이 2명 이상인 경우
select deptno '부서', count(*) '인원' from emp
where hiredate>'1981-01-01' group by deptno having count(*)>1;
hiredate가 날짜이기 때문에 문자열로 '1981-01-01'이라고만 해도 비교가 된다.
부서 별 2명 이상은 그룹을 묶은 후 비교를 할 수 있기 때문에 having으로 조건을 적는다.
-- 급여가 1000이상인 사원 중에서 부서별 중위급여가 2500미만이고
-- 평균 급여가 1200~2000 사이인 최대급여, 최소급여, 중위급여, 평균급여 검색
select deptno '부서', max(sal), min(sal),
round((max(sal)+min(sal))/2,2) '중위급여',
round(avg(sal),2) '평균급여', count(*) '인원'
from emp
where sal>=1000
group by deptno
having (max(sal)+min(sal))/2<2500 and avg(sal) between 1200 and 2000;
-- job이 salesman이 아닌 사원 중에서 job별로 급여 합계가 5000이상인 직급, 급여합계를 검색
-- 단, 급여 합계 내림정렬
select job '직급', sum(sal) '급여합계' from emp
where job<>'salesman' group by job having sum(sal)>=5000 order by '급여합계' desc;
상관(연관) 서브쿼리: 메인쿼리와 서브쿼리가 관계를 맺음, 서브쿼리 내에 메인쿼리의 칼럼 존재
처리 순서: 메인쿼리 -> 서브쿼리 -> 메인쿼리
예제)
-- jones가 근무하는 부서 이름 검색
select dname from dept where deptno=(select deptno from emp where ename='jones');
where에서 서브쿼리로 'jones'의 'deptno'를 검색(선택)하고 그것을 조건으로 사용했다.
문제)
-- 평균급여보다 많이 받는 사원 검색
select ename, sal from emp where sal>(select avg(sal) from emp);
-- jones보다 급여를 많이 받는 사원 검색 / 단, 급여 내림차 순
select ename, sal from emp where sal>(select sal from emp where ename='jones')
order by sal desc;
예제)
-- 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명, 부서번호를 출력
select d.deptno 부서번호, d.dname 부서명, emp.ename '사원 명'
from (select deptno, dname from dept where deptno=10) d, emp order by ename;
select*from emp e, (select deptno, dname from dept where deptno=10) d where e.deptno=d.deptno;
-- 10번 부서와 20번 부서 사원 중에서 직급이 같은 사원들의 이름, 부서번호, 직급을 검색
select e1.job, e1.deptno, e1.ename, e2.deptno, e2.ename
from (select ename, deptno, job from emp where deptno=10) e1,
(select ename, deptno, job from emp where deptno=20) e2
where e1.job=e2.job;
-- 직급별로 직급, 최대급여를 출력하되, 최대 급여가 salseman의 최대급여보다 큰 그룹만 출력
select job, max(sal) maxsal from emp group by job
having max(sal)>(select max(sal) from emp where job='salesman');
-- blake와 같은 부서에 있는 사원 이름, 입사일 출력 / 단, 본인 제외
-- blake가 서로 다른 부서에 있을 수 있다.
select ename, hiredate
from emp
where deptno in(select deptno from emp where ename='blake') and ename<>'blake';
'blake'가 동명이인으로 서로 다른 부서에 있을 수 있다는 것을 가정한 조건이 붙었다.
in을 통해 여러 조건 중 하나를 만족하면 조건에 충족하는 것으로 하고 서브쿼리를 통해 'blake'의 'deptno'를 검색/선택해왔다.
예제)
select (select dname from dept d where d.deptno = e.deptno) as dname, empno, ename
from emp e;
~ all: 서브쿼리의 모든 결과에 대해 ~하다.
~ any: 서브쿼리의 하나 이상의 결과에 대해 ~하다.
select ename, sal, deptno from emp
where deptno = any(select deptno from emp where sal>=3000);
where deptno in(select deptno from emp where sal>=3000); < 해당 코드와 두번째 코드가 동일하다.
in도 any도 괄호 안의 코드(값) 중 하나라도 만족하면 조건에 충족한다.
select ename, sal from emp
where sal>all(select sal from emp where deptno=30);
where sal>(select max(sal) from emp where deptno=30); < 해당 코드와 두번째 코드가 동일하다.
inner(내부) join: 양 쪽(테이블) 모두에 값이 있는 행(not null) 반환 / inner 생략 가능
-- 사원 이름, 호봉, 부서명 검색
select d.deptno, d.dname, e.ename, e.sal
from emp e join dept d on d.deptno=e.deptno
join salgrade s on e.sal between s.losal and s.hisal;
on에는 테이블을 합하는 join의 조건을 적는다.
해당 코드에서는 dept를 emp에 합할 때, 동일하게 가지고 있는 열인 deptno를 이용하여 합한다.
전체 피드백
밥 먹고 나서 집중력이 급격히 떨어지는 바람에 활용하는 부분에서 많이 헤맨 것 같다.
초반에는 쉬웠는데 역시 갈수록 많이 어려워지는 것 같다. db랑 자바랑 연결할 때도 크게 헤맬 것 같은 기분이 든다.
