Database Summary

그룹함수, 조인

조건 함수

: IF 문이나 CASE 문과 같이 조건에 따라서 SQL 문을 다르게 처리

int age = 20;
if (age<19){

"미성년자"
}else{
string data = "성인"
"성인"
}
출력 data;

Int age=20;
if (age<7){
String data= “유아”
}else if (age<19)){
String data= “미성년자”
} else{ Srting Data = “성인”
}
출력 data;

1. DECODE
   
   : 조건이 반드시 일치하는 경우에 사용하는 함수 (=)
   
   decode (컬럼, 비교값1, 결과값1, 
   비교값2, 결과값2,
   ...
   비교값n, 결과값n,
   기본결과값)
   
   select ename,sal, decode(sal, 2400, sal*0.3, 1700, sal* 0.2, sal)보너스
   from emp order by 2 desc;
   
   

CASE 함수 ★ ★ ★ ★ ★

case 컬럼 when 비교값1 then 결과값 1 
when 비교값2 then 결과값2
else 결과값 n
end

select ename, sal,
case sal when 800 then sal * 0.3
when 950 then sal * 0.2
else sal
end 보너스
from emp order by 2 desc;

-> 월급이 800 이면 bonus 0.2
월급이 950 이면 보너스 0.3
다른 월급은 보너스 없음

조건이 일치 하지 않는 경우

select ename, sal,
case when sal >= 3000 then 1000
when sal>= 2000 then 2000
when sal >= 1000 then 3000
else 4000
end 보너스
from emp order by 2 desc;

-> 숫자를 큰 순서로 배열하지 아니하면 위에서부터 아래로 내려오는 식을  쓸때 엉뚱한 숫자가 위에서 걸림.

select ename, sal,
case when sal between 3000 and 5000 then '상'
when sal between 2000 and 2999 then '중'
else '하'
end 등급
from emp order by desc;
-> 상중하 순으로 이름 월급 등급이 배열

select ename, sal, case when al in (5000, 3000) then '상'
when sal in (2975, 2850) then '중'
else 등급
from emp order by 2 desc;
-> in 안의 숫자에 해당하는 것만 등급을 부여

그룹함수

1. sum 함수 
   : 널값을 제외한 모든 함수의 값을 더함.
   
   select sum (distinct sal ), sum (all sal), sum (sal) from emp;
   distinct 사용해서 동일한 sal 가진 사원들은 제외되고 총액계산이 이루어짐
   따라서 all키워드를 사용한 총액보다 금액이 적게 출력
   
   
2. AVG
   select sum(sal )총하ㅂ,trunc(avg(sal) 평균 from emp;
   
   
3. MAX MIN 
   select max(sal) , min(sal) from emp;
   결과 하나씩 출력
   
   
4. COUNT ★ ★ ★ ★ ★
   특정컬럼의 행의 개수 변환. 널값이 제외된!!!!!
   
   
   select coumt(empno) from emp;
   -> 12
   
   select count (comm) from emp;
   -> 2
   
   select coumt(ename), coumt(comm) from emp;
   ->12명 중에 2명
   
   select count (job), count(distinct job) from emp;
   -> distinct 를 이용해 중복된 직업을 제거하고 카운트
   
   select count(8) from emp;(중요!!!!!!)
   -> null 갑시 있어도 포함한 전체행의 개수
   
   

GROUP BY

1. 단순컬럼과 그룹함수
   단순컬럼과 그룹함수를 같이 사용할 수 없지만 GROUP절을 사용하면 동시사용가능
   
   
2. GROUP절
   특정 컬럼값을 기준으로 그룹으로 묶을 때 사용
   
   select [단순컬럼 , ]  그룹함수, 그룹함수 2
   from 테이블명
   [where 조건식]
   [group by 단순컬럼]
   [order by 표현식];
   
   그룹으로 묶은 후 행을 제외시키려면 having 절 사용
   
   부서별 평균 월급 구하기
   select deptno 부서번호 , avg(sal) 평균월급
   from emp
   group by deptno 
   order by 1;
   
   부서별로 평균 급여를 구하려면 먼저 전체 사원을 소속 부서별로 그룹핑해야
   따라서 group by 뒤에 부서번호 를 지정하면 부서별로 그루핑되고 
   avg 함수에 의해 평균값을 반환하게 됨.
   
   select deptno 부서번호, max(sal) 최대월급, min(sal)최소월급 
   feom emp group by deptno order by 1;
   
   select to_char(hiredate, 'YYYY")년,
   to_char(hiredate, 'MM')월, sum(sal)
   from emp
   group by to_char(hiredate, 'YYYY"),
   to_char(hiredate, 'MM')
   order by 년 asc;
   -> 사원테이블에서 년도별 월별 월급합계를 출력
   
   
3. HAVING 절(기억이안남..)
   
   group by 절에 의해서 생성된 결과 중에서 조건과 일치되는 데이터를 추출할 때 사용.
   
   select [단순컬럼,] 그룹함수, 그룹함수2
   from 테이블 명
   where 조건식
   group by  단순컬럼
   having 조건식
   order by 표현식;
   
   select deptno, count (*) from emp group by deptno having count(*)> 5;
   -> 5명 초과인 부서만 표시
   
   select deptno , sum(sal) from emp group by deptno having sum(sal)> = 8000;
   -> 총 월급을 더 했을 때 8000 이상인 부서만 표시
   
   select deptno , sum(sal) from emp where sal >= 1000  group by deptno
   having sum(sal) > 5000 order by 1;
   -> 월급이 1000 이상인 사원에 대해서만 부서별 월급총액이 5000 이상인 부서조회

조인

사원테이블과 부서 테이블 간의 관계

1. 조 당하는 부서테이블이 부모 테이블, 참조하는 사원 테이블이 자식 테이블

기본(primary key)키
중복 불가.

중복되는 컬럼을 합쳐 두개의 테이블을 11개의 컬럼인 한개의 테이블처럼 사용

2. 조인 종류

 

select * from emp, dept;

-> 잘못된 한사람당 deptn0o 부서 4개로 돼서 48명이

select ename, deptno, dname from emp; dept;

-> error 어떤 dept 컬럼을 쓸건지 안 써서..

=> select ename, emp.deptno, dname from emp, dept;

오라클 조인

 

1. EQUI 조인
   
   두 테이블에서 공통적으로 존재하는 컬럼의 값이 반드시 일치하는 행을 연결하여 데이터를 반환
   
   참조하는 키: 외래키
   기본키는 중복이 안되고 null도 안됨
   
   select 테이블1 컬럼 , 테이블2 컬럼
   from 테이블1, 테이블2
   
   select * from emp, dept where emp.deptno= dept.deptno;
   -> 컬럼은 11개가 나오지만 레코드가 12개나옴
   
   select empno, ename, dname from emp, dept where emp.deptno = dept.deptno;
   
   select empno 사원번호,ename 이름, sal 월급, dname 부서이름, dept.deptno 부서번호
   from emp, dept where emp.deptno=dept.deptno;
   
   1) 공통컬럼 사용시 모호성 제거
   2) 별칭 사용
   
   select ename, e.deptno from emp e, dept d where
   e.deptno=d.deptno;
   별칭 썼으면 끝까지 별칭을
   
   select dname 부서명, count(empno) 인원수
   from emp e, dept d
   where e.deptno = d.deptno
   and TO_CHAR (hiredate, 'YYYY')<1982
   group by dname;
   -> 1982 년 전에 입사한 사원늬 수를 부서 별로 묶어 추출하는데 입사연도 앞자리 4개를 추출하여 
   비교한 것으로 화면에 표시한다.
   조인 -> 1982 년도보다 일찌기 입사한 사람만 -> 부서별로 그룹핑
   
   3) 검색조건 추가
   select dname 부서명, avg(sal) 평균
   from emp e, dept d 
   where e.deptno = d.deptno
   and e.deptno=10
   group by dname;
   -> 10 번 부서의 sal을 합해서 구하다
   
   select empno 사원벙호, empno 이름, loc 근무지역
   from emp e, dept d
   where e.deptno = d.deptno
   and loc = 'DALLS';
   ->달라스에 사는 사람들을 보여주다.
   
   
   
2. NON-EQUI 조인
   
   where 절 조인 조건을 지정할 때 동등 연산자 (=) 이외의 연산자를 사용하는 조인
   
   select *from emp, salgrade where sal between salgrade.losal and salgrade.hisal;
   
   select ename , sal , dname, grade from emo e, dept d, salgrade g
   where e.deptno = d.deptno
   and e.sal between g.losal and g.hisal;
   
   3개의 테이블을 조인하기 위하여 from 절에 테이블 명을 지정하고
   where 절에는 emp 테이블과 dept 테이블 에 대해서 equi 조인을 하고
   emp테이블과 salgrade 테이블은 non-equi 조인으로 처리.
   
   select ename 이름, sal 월급, grade 등급 dname 부서명
   from emp e, salgrade g, dept d
   where e,deptno = d.deptno
   and e.sal between g.losal and g.hisal and ename ='SMITH';
   
   
3. SELF 조인
   
   select e.ename 사원명, e.mgr 관리자번호, m.name 관리자명, e.sal 사원월급, m.sal 관리자월급
   from emp e, emp m where e.mgr = m.empno;
   
   select *from emp e, emp m, emp p where e.mgr = m.empno and m.mgr = p.empno;
   -> 첫번째 사원의 번호 = 관리자의 번호
   
   select e.ename "사원이름", m.ename "사원관리자이름", mm.ename "관리자의 관리자 이름"
   from emp e, emp m, emp mm where e.mgr = m.empno and e.egr = mm.empno;
   -> 사원 매니저 의 매니저 의 매니저를 찾
   
   
4. OUTER 조인
   
   select * from emp, dept where emp.deptno = dept.deptno(+);
   (+) 강제로 null값 보이게
   
   
   

ANSI 조인

 

여러 테이블을 연결하는 조인 조건을 where 절에 명시하지 않고 다른 방법을 통하여 기술하며
검색조건을 지정하는 경우에 where 절을 사용한다. 따라서 조인조건과 검색조건을 분리하여 지정하기 
때문에 가독성이 향상된다.

 

 

1. natural 조인
   
   select * from emp natural join dept;
   
   어떤 컬럼을 조인하는지 표시하지 않았는데 조인이 됨.
   하나의 테이블 총 10개의 컬럼이 됨
   
   select ename, dname, deptno, from emp NATURAL JOIN ㅇ뎃;
   -> 잘 안씀 별칭으로 구분 불가
   
   30번 부서의 사원명 사원번호 부서
   
   cross 조인 = 아까 48개
   
   
2. USING ( 컬럼 ) 절
   
   select * from emp join dept using (DEPTNO);
   -> 컬럼 10개
   
   select ename , dname, deptno from emp join dept using (deptno);
   -> 앞에 표시안해도 ㄱㅊ
   
   select ename, deptno, dname from emp join dept using (deptno)
   where ename = 'SMITH';
   using (deptno)
   
   
   
3. ON 절
   
   논이큐 조인이나 임의조건
   테이블명 지정 필수
   
   .select ename , dname, e.deptno from e join dept d on e.deptno = d.deptno;
   
   select ename  dname, e.deptno from emp e join dept d on e.deptno =d.deptno
   where ename = 'SMITH';
   
   on절에 조인조건을 지정하고 검색조건을 추가하기 위해서
   WHERE 절을 사용했다. 오라클 조인과는 다르게 조인조겅과 검색조건 지정을 분리해서 표현가능
   
   select dname, ename from emp e join dept d on e.deptno = d.deptno where e.deptno=20;
   
   ON은 이큐조인 처럼 e, d, 이렇게 붙여야 하고 ON 뒤에도 필요
   
   select * from emp e inner join dept d
   on e.deptno = d.deptno
   inner join salgrade g |
   on e.sal between g.losal and g.hisal;
   
   select ename 이름, e.deptno 부서번호, dname 부서명, sal 월급, grade 월급등급
   from emp e inner join dept d on e.deptno = d.deptno
   inner join salgrade g on e.sal between g.losal and g.hisal;
   
   select e.ename 사원명, m.ename 관리자명, p.ename "관리자의 관리자명"
   from emp e inner join emp m on e.mgr = m.empno inner join emp p on m.mgr = p.empno;
   
   
   select ename 이름, e.deptno 부서번호, dname 부서명, sal 월급, grade 월급등급
   from emp e join dept d on e.deptno = d.deptno join salgrade on e.sal between losal and hisal;
   
   select ename 이름, dname 부서명, deptno 부서번호, sal 월급, grade 월급등급
   from emp inner join dept USING(deptno) inner join salgrade g on e.sal between g.losal and g.hisal;