[Oracle DB] 집계함수 | Join

집계함수란? 

여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수이다.

COUNT : 검색된 행의 수를 반환하며, 유일한게 *을 사용할 수 있다. MAX : 컬럼 값 중에서 최대값을 반환한다. MIN : 컬럼 값 중에서 최소값을 반환한다. SUM : 검색된 컬럼의 합을 반환하며, 문자의 합을 구할 수는 없다.

MAX와 MIN함수에서는 문자에서도 사용이 가능하다.

Why???!!! 아스키코드가 있기 때문이다.

---

 SUM 함수 

단일행 함수 : null이 하나라도 있으면 결과값이 다 null이 된다. 여러행 함수 : 행 집합당 하나의 결과를 반환하는 것으로, null값이 있으면 제외되어 결과값이 출력된다.

따라서 아래는 여러행 함수이므로, NULL값이 제외되어 결과가 출력된다.

select sum(sal),sum(comm)
from emp;

---

 COUNT 함수 & MIN 함수 

위에서 말했듯이, MIN함수는 문자도 가능하다.

대표적인 예로, 아래와 같다.

select count(*), count(comm), min(ename), min(sal)
from emp;
-- 집계 함수 사용

---

 중복 제거 ( distinct ) 

distinct명령어를 사용하면 중복을 제거하여, 출력해준다.

select distinct job from emp;

---

 조인 ( join ) 

1. 교차조인 ( cross join )

2. 내부조인 ( inner join ) :  theta [non-]equi,natural

3. 외부조인( outer join ) : [ left | right | full ] [outer] join

4. 셀프조인( self join )

---

 예제 

아래 설계표를 참고하여, 진행된 예제이다.

---

 1. 우리 회사에 부서가 몇개야?? 

select count(deptno) from dept;

위 소스에서 deptno 대신 *을 쓰면, 전체를 출력해준다.

---

 2. 사원이 현재 소속되어 있는 부서는 몇개야?? 

select count(distinct deptno) from emp;

---

 3. 부서별로 사원의 sal이 낮은 순으로 deptno,dname,empno,ename,sal을 출력해라. 

select b.deptno,b.dname,a.empno,a.ename,a.sal 
from emp a join dept b 
on a.deptno=b.deptno order by 1,5 asc;

emp테이블의 별칭을 a, dept는 b로 칭하였다. 

참고로 오름차순은 asc, 내림차순은 desc이며, default는 asc이다.

---

 4. 부서별 사원이 몇명인지 deptno,dname부서별 인원수를 출력해라. 

select a.deptno, b.dname, count(*) as "부서별 인원 수" 
from emp a join dept b on a.deptno=b.deptno group by a.deptno, b.dname; 
--사람의 수를 뽑을 때는 emp테이블에서 뽑아야함

---

 4-1. 부서별 사원이 몇명인지 depno,dname,부서별 인원 수를 출력하되,  5명 이상인 부서만 부서별 인원수 높은 순으로 출력해라. 

select a.deptno, b.dname, count(*) as "부서별 인원 수" 
from emp a join dept b on a.deptno=b.deptno 
group by a.deptno, b.dname 
having count(*) >= 5
order by 3 desc; 

 

---

 4-2. 부서별 사원이 몇명인지 30번 이상인 부서번호만으로 부서별 인원수 높은 순으로 출력해라. 

select a.deptno, b.dname, count(*) as "부서별 인원 수" 
from emp a join dept b on a.deptno=b.deptno 
where a.deptno >= 30
group by a.deptno, b.dname 
having count(*) >= 5
order by 3 desc; 

아래 코드도 위와 같은 의미이다.

아래와 같이, where절에 있는 것을 having에 작성해주었다.

select a.deptno, b.dname, count(*) as "부서별 인원 수" 
from emp a join dept b on a.deptno=b.deptno 
--where a.deptno >= 30
group by a.deptno, b.dname 
having a.deptno >= 30 and count(*) >= 5
order by 3 desc; 

※ 참고      어떠한 값을 출력할 때, 기본적인 형식와 같다.   select ~from   where ( where은 개별적인 조건만 써야됨 )   group by ~~~~   having (그룹에 대한 조건)   order by ;   여기서 where절과 having에 대한 차이점에 대해서 알아보자.   where절에서는 개별적인 조건만 사용되며, having에서는 그룹에 대한 조건을 작성한다.

---

 4-4. 부서별 사원이 몇명인지 job이 'President'인 사원을 제외하고 deptno, dname, 부서별 인원수를 출력하되 5명이상인 부서만 부서별 인원수 높은 순으로 출력해라. 

집계함수와 같이 쓰는 select list에 있는 나머지 컬럼은 반드시 group by에 와야 한다.

오라클에서 같지 않다는 의미의 기호는 != 도 되고, <> 도 가능하다.

select a.deptno, b.dname, count(*) as "부서별 인원 수" 
from emp a join dept b on a.deptno=b.deptno 
where job != 'PRESIDENT'
group by a.deptno, b.dname
having count(*)>=5
order by 3 desc;

해당 문제에서는 부서별로 다르기 때문에, where조건문을 having에 넣으면 안된다. 

---

 5. 부서별로 각 job별 인원이 몇명인지를 deptno, dname, job, 인원수를 부서별 job별로 정렬하여 출력해라. 

select b.deptno,a.dname,b.job, count(*) as "인원수" 
from dept a join emp b on a.deptno=b.deptno
group by b.job, b.deptno, a.dname
order by 1,3;

---

 6. 아래 사진과 같이, 출력해라. 

select deptno, count(decode(job,'CLERK',0)) as CLERK, 
                count(decode(job,'MANAGER',0)) as MANAGER,
                count(decode(job,'PRESIDENT',0)) as PRESIDENT,
                count(decode(job,'ANALYST',0)) as ANALYST,
                count(decode(job,'SALESMAN',0)) as SALESMAN
from emp group by deptno order by 1;

---

 7. emp의 sal이 salgrade의 어떤 grade에 해당되는지 ename, job, sal, grade를 job별로 sal 높은 순으로 출력해라. 

select e.ename, e.job, e.sal, s.grade 
from emp e join salgrade s 
    on e.sal>=s.losal and e.sal<=hisal 
order by 2,3 desc;

위 조건문을 between으로 바꿔 사용도 가능하다.

select e.ename, e.job, e.sal, s.grade 
from emp e join salgrade s 
    --on e.sal>=s.losal and e.sal<=hisal 
    on e.sal between s.losal and s.hisal
order by 2,3 desc;

---

 7-1. emp의 sal이 salgrade의 어떤 grade에 해당되는지 ename, job, sal, grade, deptno, dname을 job별로  sal 높은 순으로 출력 

 이중 join에 대한 문제로, 아래와 같다. 

select e.ename, e.job, e.sal, s.grade,d.deptno, d.dname
from emp e join dept d on e.deptno=d.deptno 
           join salgrade s on e.sal between s.losal and s.hisal
order by 2,3 desc;

아래는 위와 같은 쿼리의 의미로, join의 위치만 바꿔서 작성한 것이다.

select e.ename, e.job, e.sal, s.grade,d.deptno, d.dname
from salgrade s join emp e on e.sal between s.losal and s.hisal
        	join dept d on e.deptno=d.deptno     
order by 2,3 desc;

여기까지가 inner 조인에 대한 예제이다. 

---

여기서 부터는 outer조인에 대한 예제이다.

 7-2. 모든 부서에 대하여 emp의 sal이 salgrade의 어떤 grade에 해당되는지deptno,dname,ename,job,sal,grade을 부서별로 job별로 sal 높은 순으로 출력해라.

select d.deptno, d.dname, e.ename, e.job, e.sal, s.grade
from emp e right outer join dept d on e.deptno=d.deptno 
            left outer join salgrade s on e.sal between s.losal and s.hisal
order by 1,4,5 desc;

아래는 left, right, full의 join에 대한 사진이다.

이해하는데, 좋은 사진이라서 가져왔다.^_^

참고 주소 : https://haenny.tistory.com/34

---

7-3. 모든 부서에 대하여 emp의 sal이 salgrade의 어떤 grade에 해당되는지 deptno,dname,ename,job,sal,grade을 부서별로 job별로 sal 높은 순으로 출력해라.(단, sal emp dept순으로 조인)

select d.deptno, d.dname, e.ename, e.job, e.sal, s.grade
from salgrade s 
        join emp e on e.sal between s.losal and s.hisal
        right join dept d on e.deptno=d.deptno     
order by 1,4,5 desc;

---

8. 모든 부서에 대하여 sal의 합계가 1000000이상이면 'good' , 5000이상이면 'average' , 1이상 5000미만이면 'bad' ,  0이면 'nopay' , 없으면 'not exists'로 평가하되 deptno,dname, 부서별 sal의 합계, 평가를 출력해라.

select d.deptno,d.dname, sum(e.sal) as "sal 합계",
    case 
    when sum(e.sal)>= 1000000 then 'good'
    when sum(e.sal)>= 5000 then 'average'
    when sum(e.sal) between 1 and 5000 then 'bad'
    when sum(e.sal) = 0 then 'no pay'
    when sum(e.sal) is null then 'not exists'
    end as 평가
from dept d left outer join emp e on e.deptno=d.deptno
group by d.deptno,d.dname
order by 1;

---

9. 모든 부서에 대하여 emp의 sal+sal*comm이 salgrade의 어떤 grade에 해당되는지 deptno, dname, ename, job, sal, sal+sal*comm, grade를 출력하되, 없는 grade는 '***'로 표시하고 부서별로 job별로 sal 이 높은 순으로 출력해라. 

select d.deptno,d.dname,e.ename,
    e.job,e.sal,e.sal+e.sal*nvl(e.comm,0)as 합,nvl(to_char(s.grade),'****') as 등급
from dept d left outer join emp e on d.deptno = e.deptno
    left outer join salgrade s on e.sal+e.sal*nvl(e.comm,0) between s.losal and s.hisal
    order by 1,4 desc;

---

여기서부터는 교차조인이다.

 

아래는 바로 위의 ex3과 같은 예제이다.

select b.deptno,b.dname,a.empno,a.ename,a.sal from emp a, dept b 

결과값이 너무 길어, 일부만 캡쳐한 결과이다.

이처럼 필요한 조건을 안 주면, 무작위 조인이 되어버린다는 문제점을 알아두자.