오라클 Group by 절과 Join!

Group by 절!

Group by 전에  SQL에는 집계 함수가 있습다

• 종류는 SUM,AVG,MIN,MAX,COUNT가  있습니다.
• SUM은 총합을, AVG는 평균, MIN,MAX는 최소 최대 COUNT는 갯수를 의미합니다

--(예) 가장 최근 입사한 입사일, 가장 오래된 입사일 출력?
select max(hiredate) as "최근 입사일" , min(hiredate) as "오래된 입사일" from emp;

--(예) 30번 부서 소속의 사원중에서 커미션을 받는 사원의 수?
select count(comm) from emp where deptno='30' and not comm='0';

--(예) 30번 부서 소속의 월급 평균
select round(avg(sal),2) from emp where deptno='30'
--round는 (값, 반올림할 자리)를 의미합니다.

• 집계 함수는 null이 포함되어도 계산이 가능합니다!
• 집계 함수는 단일행 함수이기도 한데 여러행의 결과에는 사용을 못합니다!
• 예를 들어 밑 코드의 경우 ename,max(sal) 여러개의 행을 결과로 나타내야하므로 사용이 안됩니다!

select sum(sal) from emp; - 사용가능 단일 행이므로
select ename,max(sal) from emp; --x max는 단일 그륩 함수이기 때문에 
select max(hiredate) as "최근 입사일" , min(hiredate) as "오래된 입사일" from emp; -단일행 끼리는 가능

•  이를 해결하기 위해선 Group by 를 사용해주면됩니다!

 select ename,max(sal) 
 from emp
 group by ename
 
 group by ename을 해줌으로서 DBMS에서 ename이 같은 값끼리 그륩화를 해줍니다
 

 

•  또한 Group by 다음 명령어로 having을 써줌으로서 조건에 맞는 값만 출력 할 수 있습니다.

 select ename,max(sal) 
 from emp
 group by ename
 having ename ='FORD';

• Group by 와 Having에는 몇가지 규칙이 있습니다.
• Group by에 경우  튜플을 그륩으로 묶은 후 select 절에는 그륩으로 묶인 튜플 혹은 집계 함수만 나올 수 있습니다.

select custid,sum(sal)
from orders                               (o)
group by custid;

 select ename,sum(sal) 
from orders                               (x)
group by custid;

• Having 은 반드시 Group by 절과 같이 작생해야하고 where절보다 뒤에 있어야합니다 
• 또한 Having 조건문에는 집계함수가 오는걸 권장합니다 

select deptno,min(sal) 최소급여 ,max(sal) 최대급여
from emp                                                   -(o)
group by deptno
having max(sal) >=2900;

select deptno,min(sal) 최소급여 ,max(sal) 최대급여
from emp                                                   -(x)
group by deptno
having max(sal) >=2900
where deptno = 30;

 

Join ! : 한개 이상의 테이블에서 테이터를 조회하기 위해서 사용하는 방식 (ORACLE 한정)

Emp

dept

salgrade

• equi join :  동등한 조건에 의하여 테이블 조인을 뜻합니다.( 동일한 칼럼)

select emp.deptno,ename,dname,loc
from emp,dept
where  emp.deptno=dept.deptno and ename ='SCOTT';

emp , dept 테이블에서  동등하게 있는 deptno를 참조함으로써 각각의 테이블에 있는 칼럼을 합칠 수 있습니다.

 

 

• non equi join:  조인 조건에 특정 범위내에 있는지를 조사하기 위해 where절의 조인 조건으로 = 연산자와 비교연산자 사용

    --(예) 사원들의 급여가 몇 등급인지?
    
    select ename,sal,grade
    from emp,salgrade
    where sal between losal and hisal;
    
    where절에 비교 연산자를 사용하여 조인 하여 사원들의 급여에 따른 등급을 정합니다

• Self join: 자기 자신 테이블과 조인을 맺는것을 뜻합니다.

     -- (예) 이름이 SMITH 인 사원의 MANAGER 이름 알아내기
    
     select manager.empno,manager.ename 
     from emp staff , emp manager
     where staff.mgr = manager.empno and staff.ename ='SMITH';
     
     from절에서 테이블 선언 후 변수명을 적음으로서 각각의 원하는 값을 where절에 적을 수 있습니다.
     
          --(예) Manager 가 King인 사원의 이름 ,직급(job)
     
     select staff.ename,staff.job
     from emp staff , emp manager
     where staff.mgr = manager.empno and manager.ename ='KING'

 

•  Cross Join: 2개 이상의 테이블이 조인될때 WHERE절에 의해 공통되는 컬럼에 의한 결합이 발생하지 않는 경우  그냥 물리적으로 섞어 버리는 조인입니다.

 select * from emp,dept;

 

ANSI JOIN : 모든 DB에서 사용가능한 조인입니다!

• Cross Join! 

  select * from emp,dept; --oracle
  select * from emp cross join dept ; --ANSI join 규칙
    
  오라클과 달리 테이블 선언 다음에 cross  join를 써줍니다!

 

 

• inner Join : 오라클의 Equi Join과 동일합니다!

    select ename,dname
    from emp , dept 
    where emp.deptno = deptn.deptno;
    --> equi 조인
    
    select ename,dname
    from emp inner join dept 
    on emp.deptno = deptn.deptno; --ANSI join 규칙
    
    table 선언 다음에 inner join을 써주며 where 대신 on를 써줍니다
    
     select ename, dname,deptno
    from emp inner join dept
    using (deptno)  --공통 칼럼
    where ename = 'SCOTT';
    
    using문을 써줄수도 있습니다

 

• - Outer Join : LEFT,RIGHT , FULL 조인  기존 조인에서 모든 레코드가 반드시 출력 되어야 할때 (+) 사용
•  

   select customer.name , saleprice
   from customer left outer join orders
   on customer.custid= orders.custid;
   
   outer join 은 두개이상의 테이블에서 한쪽 테이블에 정보(알고싶은 칼럼)이 부족할때 사용합니다