1. 조건부 표현식
-case-when-then 조건문
select mgr,
case nvl(mgr,0) when 0 then 'n' --case 변수 when 값1 then 결과1
else 'y'
end as mgr_yn
from emp;-조건 비교
select sal,
case when (sal between 1000 and 2000) then '1'
when (sal between 1000 and 2000) then '2'
when (sal between 1000 and 2000) then '3'
else '5'
end as kr
from emp;<주의해야 할 점>
-조건문과 조건문 사이에는 콤마(,) 를 사용하지 않는다.
-CASE 문은 반드시 END 로 끝내야 한다.
-CASE 표현식은 ANSI SQL 형식도 지원한다.
-decode 함수 (Oracle only)
select deptno,
decode(deptno,10,'십',20,'이십',30,'삼십','기타') as kr
from emp;:deptno가 10이면 십,20이면 이십,30이면 삼십,나머지는 기타.
2. 그룹 함수
-max(),min(),avg(),sum(),count(1)
그룹함수는 select 키워드 뒤에 배치한다. 여러 그룹 함수를 쉼표로 구분하여 함께 사용할 수 있다. 게다가 함수를 중첩해서 쓸 수 있다. ex) max(avg( ) )
-distinct(): 중복 키워드 제거
select count(distinct department_id)
from empoyees;3. GROUP BY절
group by절로 행을 그룹화 한다. 먼저 행이 부서 번호별로 그룹화되고, 이어서 행이 부서 번호 그룹에서 직무 ID별로 그룹화된다.
**group by 컬럼은 select에 복붙.
group by 컬럼1, 컬럼 2 -> select 컬럼1, 컬럼2 즉, group by 뒤에 쓴 컬럼이 select 뒤에 와야 한다. 한편 그룹 함수 (5개)는 group by와 무관하게 select 뒤에 사용가능하며, group by를 쓰면 목적에 맞는 그룹 함수를 (select 뒤에) 넣어야 한다.
*where절은 그룹을 제한하는 데 사용할 수 없으므로, 그룹을 제한하려면 having절을 사용한다. where절에서 그룹 함수를 사용할 수 없다.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id **
HAVING MAX(salary)>10000 ; **
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id **
HAVING SUM(salary) > 13000 **
ORDER BY SUM(salary):4. Join
1) inner join
- 각 테이블에서 조인 조건에 일치되는 데이터만 가져온다.
select *
from emp,dept --2개 이상의 테이블이 from절에 있을 때 join을 사용한다.
where emp.deptno = dept.deptno --join & 조건 추가 가능 (and~)
order by emp.deptno asc, dept.deptno asc;양쪽 테이블에 겹치는 컬럼을 사용할 경우, 반드시 테이블명.컬럼을 명확히 기재해야 한다.
-ANSI 문법기준
select dname,e.deptno,ename
from dept d
join emp e
on d.deptno=e.deptno --join
where d.deptno = 10; --연결한 후의 조건2) self join
- 자기 자신을 마치 다른 테이블처럼 취급하여 서로 조인한다.
select e1.empno,e1.ename,e1.mgr,e2.ename,e1.deptno
from emp e1, emp2
where e1.mgr= e2.empno and e1.deptno = 10; --join & 조건-ANSI 문법기준
select e1.empno,e1.ename,e1.mgr,e2.ename,e1.deptno
from emp e1
join emp e2
on e1.mgr= e2.empno
where e1.deptno = 10; --조건3) outer join
- 조인 조건에 일치하는 데이터 및 일치하지 않은 데이터를 모두 SELECT하고, 조인 조건에 일치하는 데이터가 없다면 NULL로 가져온다.
select *
from emp e, dept d
where e.deptno (+) = d.deptno;조인시킬 값이 없는 조인 쪽에 (+)를 넣어준다. 즉, emp테이블에서 deptno는 10,20,30 이며, dept테이블에서는 10,20,30,40 데이터 값이 있다.
emp의 deptno 개수가 적으므로 여기에 (+)를 넣는다.
-ANSI 문법기준
deptno 개수, 즉 종류가 더 많은 쪽을 기준으로 한다. = right
select *
from emp e
right outer join dept d --오른쪽 기준
on e.deptno = d.deptno;왼쪽 기준이면?
select *
from dept d
left outer join emp e -- 왼쪽 기준으로 outer join
on e.deptno = d.deptno;5. Subquery
-단일행 서브쿼리: 서브쿼리 결과가 1행
-다중행 서브쿼리: 서브쿼리 결과가 여러 행
*단일행 서브쿼리에서는 부등호 사용 가능하나, 다중행 서브쿼리에서는 부등호 단독으로 사용 못한다. 따라서 in,all,any 등을 써야 한다. (in은 단일/다중 쿼리에서 모두 사용 가능)
-ANY: 그 중에 하나 만족, ALL: 모두 만족
-서브쿼리가 from절에 오는 경우: inline view (인라인 뷰)
-서브쿼리가 select절에 오는 경우: 스칼라 서브쿼리
-보통 서브쿼리는 where절에 많이 온다.
--10번 부서 사람이랑 직업이 같은 사람
select *
from emp
where job in (select job from emp where deptno = 10);
--EMP 테이블에서 가장 많은 사원을 갖는 MGR번호 출력
select mgr
from emp
group by mgr
having count(1) = (select max(count(1)) from emp group by mgr);
--dual 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력
select (select count(1) from emp where deptno = 10) as CNT10,
(select count(1) from emp where deptno = 30) as CNT30 from dual;
--EMP 테이블에서 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력
select empno,ename,deptno,min(hiredate)
from emp
group by deptno,empno,ename,deptno
having min(hiredate) in (select min(hiredate) from emp group by deptno);
--CHICAGO 지역에 근무중인 사원 중 BLAKE가 직속상관인 사원들의 사원번호(empno), 이름(ename), 직무(job) 출력
select empno,ename,job
from emp
where deptno = (select deptno from dept where loc='CHICAGO')
and mgr = (select empno from emp where ename = 'BLAKE')
--3000 이상의 급여를 받는 사원들과 같은 부서에 근무하고 있는 사원의 사원번호(empno), 이름(ename), 급여(sal)출력
select empno,ename,sal
from emp
where deptno in (select deptno from emp where sal>=3000);