수업 35일차 - 일반 함수, group by/having, join

 

-- COUNT
select count(*), count(mgr), count(comm)
from emp;

select count(*)
from emp
where empno=7788; -- 아이디 있는지 확인

 

-- max, min
select min(sal) "급여 최소", max(sal) "급여 최대"
from emp;

 

-- 게시판, 공지사항 -> 번호 자동 증가
select max(empno)+1
from emp;

 

--AVG, SUM
select sum(sal), round(avg(sal), 2)
from emp;

 

 

-- RANK(중복 후 건너 뜀)
select empno, ename, sal, rank() over(order by sal desc) "rank"
from emp;

 

-- RANK (중복 후 건너뛰지 않음)
select empno, ename, sal, dense_rank() over(order by sal desc) "rank"
from emp;

 

 

group by / having

-- group by / having
-- 개별로 하기
select count(*), sum(sal), round(avg(sal), 2)
from emp
where deptno=10;

-- 그룹별로 하기
select deptno, count(*), sum(sal), round(avg(sal), 2)
from emp
group by deptno; -- 부서 별로 위 값 구한다

 

-- 연도 별로 인원 수, 급여 최대, 최소
select to_char(hiredate, 'yyyy'), count(*), max(sal), min(sal)
from emp
group by to_char(hiredate, 'yyyy')
order by to_char(hiredate, 'yyyy') asc;

 

-- 직위 별 인원 수, 급여 최대, 최소
select job, count(*), max(sal), min(sal)
from emp
group by job;

 

-- 직위별 인원 수, 급여의 합, 급여 평균이 전체 평균보다 큰 부서
-- 그룹 별 조건 - having
select job, count(*), sum(sal), avg(sal)
from emp
group by job
having avg(sal)>(select avg(sal) from emp);

 

 

-- 인원이 한 명 이상인 그룹
select job, count(*), sum(sal), avg(sal)
from emp
group by job
having count(*)>1;

 

 

-- 부서 별, 직위 별로 그룹 (이중 그룹)
select deptno, job, count(*), sum(sal), avg(sal)
from emp
group by (deptno, job)
order by deptno asc;

 

-- 부서 별(10, 20, 30), 연도 별로 인원 수, 급여 합, 급여 평균
-- 인원 수가 1명 이상
select deptno, to_char(hiredate, 'yyyy'), count(*), sum(sal), avg(sal)
from emp
group by deptno, to_char(hiredate, 'yyyy')
having count(*)>1
order by deptno asc;

 

 

JOIN : 테이블 2개 이상 합치기

-- JOIN
select empno, ename, job, dname, loc
from emp, dept
where emp.deptno=dept.deptno;

select empno, ename, job, dname, loc
from emp join dept
on emp.deptno=dept.deptno;

select empno, ename, job, dname, loc
from emp natural join dept;

 

-- sal이 losal과 hisal 사이에 있는 값
select ename, sal, grade
from emp, salgrade
where sal between losal and hisal;

 

-- 2차 조인
-- 사번, 이름, 직위, 급여(emp, 부서명, 근무지(dept), 급여등급(salgrade)
select empno, ename, job, sal, dname, loc, grade
from emp, dept, salgrade
where emp.deptno=dept.deptno
and sal between losal and hisal;

-- ANSI 조인(oracle 아닌 db에서 사용)
select empno, ename, job, sal, dname, loc, grade
from emp join dept
on emp.deptno=dept.deptno
join salgrade
on sal between losal and hisal;

 

--self join
select e1.ename "본인명", e2.ename "사수명"
from emp e1, emp e2
where e1.mgr=e2.empno(+); -- (+): null 값 포함

 

-- KING 사원의 정보
-- emp: 사번, 이름, 직위, 급여
-- dept: 부서명, 근무지
select empno, ename, job, sal, dname, loc
from emp, dept
where emp.deptno=dept.deptno
and ename='KING';

 

 

-- 성과금 받는 사원의 이름, 급여, 성과금, 부서명
select ename, sal, comm, dname
from emp, dept
where emp.deptno=dept.deptno
and comm is not null and comm<>0;

 

-- left join
select ename, dname
from emp, dept
where emp.deptno=dept.deptno(+);

 

-- right join : null 값까지 모두 출력
select ename, dname
from emp, dept
where emp.deptno(+)=dept.deptno;