2019. 4. 4. 19:22ㆍDateBase
-----------------------------------기타함수---------------------------------------
==============nvl(컬럼, 값)=================
nvl이란
지정된 값(컬럼)이 만약 null이라면 지정된값으로 변환한다.
지정된 값(컬럼)이 null이 아니면 원래 값을 제공한다.
예시)
select nvl(30,0), nvl(null,0)
from dual;
예시2) 사원의 이름, 급여, 커미션을 조회하기, 커미션이 없는 사원은 0으로 표현하기
select first_name, salary, nvl(commission_pct, 0) 커미션
from employees;
예시3) 사원의 이름, 급여, 커미션, 총급여(보너스가 포함된 급여)를 조회하기
select first_name, salary, nvl(commission_pct,0) 커미션, nvl(commission_pct,0)*salary + salary 총급여
from employees
order by 커미션 desc, 총급여 desc;
=============nvl2(컬럽, 값1, 값2)==============
nvl2란 (잘안쓴다.)
지정된 칼럼의 값이 만약 null이라면 값2 대체해서 제공한다.
지정된 컬럼의 값이 null 아니면 값1로 대체해서 제공한다.
예제) 사원의 이름, 급여, 커미션을 받는지 여부를 "유", "무"로 조회하기
select first_name, salary, nvl2(commission_pct, '유', '무') 커미션_유무
from employees;
==============case ~ when=================
if ~ else if ~ else 문의 역할을 수행한다.
----구성----
case
when 비교식 then 값1
when 비교식 then 값2
when 비교식 then 값3
else 값4
end
예시) 부서번호가 50번인 부서는 'A팀', 60, 70번인 부서는 'B팀', 80번인부서는 'C팀'으로 조회하기
select first_name, department_id,
case
when department_id = 50 then 'A팀'
when department_id in (60, 70) then 'B팀'
when department_id = 80 then 'C팀'
end team
from EMPLOYEES
where department_id in (50, 60, 70, 80);
order by team;
예시2) 사원들의 급여 수준을 상중하로 조회하기
사원 이름, 급여, 급여수준을 조회하고, 급여수준 순으로 정렬하기
급여 수준은 상 - 12000이상 중 - 5000이상 하 - 5000미만
select first_name, salary,
case
when salary >= 12000 then '상'
when salary >= 5000 then '중'
else '하'
end 급여수준
from EMPLOYEES
order by 급여수준;
예시3) 사원 이름, 급여, 급여인상분을 조회하고, 급여인상분 순으로 정렬하기
인상비율 상 : 12000달러 이상 - 5%, 중 : 5000달러 인산 - 10% 하 : 5000달러 미만 -15%
select first_name, salary,
case
when salary >= 12000 then salary * 0.05
when salary >= 5000 then salary * 0.1
else salary * 0.15
end 급여인상분
from EMPLOYEES
order by 급여인상분 desc;
===============decode 함수=================
if ~ else if ~ else를 감편하게 구현할 수 있는 함수
※. ~~ 이상 ~~ 이하는 case when문을 쓰지만 ~~는 ~~다. 라는 구문은 decode를 많이쓴다.
구조
decode(칼럼 혹은 표현식, 비교값1, 결과값1,
비교값2, 결과값2,
결과값3)
예시) 부서번호가 50번인 부서는 'A팀', 60, 70번인 부서는 'B팀', 80번인부서는 'C팀'으로 조회하기
(case when문에 쓰인 예시 1번)
select first_name, department_id,
decode(department_id, 50, 'A',
60, 'B',
70, 'B',
80, 'C') team
from EMPLOYEES
where department_id in (50, 60, 70, 80)
order by team;
예시2) 각 부서 아이디에 해당하는 부서명을 출력한다.
select first_name, department_id,
decode(department_id, 50, (select department_name from departments A where A.department_id = 50),
60, (select department_name from departments A where A.department_id = 60),
70, (select department_name from departments A where A.department_id = 70),
80, (select department_name from departments A where A.department_id = 80)) team
from EMPLOYEES
where department_id in (50, 60, 70, 80)
order by team;
----------------------------테이블의 조인--------------------------------
※. 무척 중요!
테이블의 조인
- 여러 테이블에서 데이터를 조회할때 사용된다.
- 조회할 데이터가 2개 이상의 테이블을 사용해야하지만 획득되는 경우 테이블의 조인을 사용한다.
조인방법
- from 절에 데이터획득에 필요한 테이블을 나열하기만 하면 된다.
- 조인조건을 제공해야 된다.(n개의 테이블을 조인하면 n-1개의 조인조건이 있어야 한다.)
- 조인조건은 조인된 행들 중에서 의미있게 연결된 행만 선택하게 한다.
<참고>
/////////////////////////////////////////////////등가 조인
- 사원의 아이디, 사원의 이름, 부서아이디, 부서이름을 조회하기
{ employees 테이블 }{departments 테이블}
예시)사원 부서아이디에 맞는 부서 테이블을 열결하라
select employees.employee_id,
employees.first_name,
employees.department_id,
departments.department_name
from employees, departments
where employees.department_id
= departments.department_id;
예시2) 예시1과 같으나 컬럼에 별칭을 붙이듯 테이블에도 별칭을 붙여 간편히 작성이 가능하다.
select E.employee_id,
E.first_name,
E.department_id,
D.department_name
from employees E, departments D
where E.department_id = D.department_id;
예시3) 사원의 아이디, 사원이름, 직종, 급여, 직종제목, 직종최저급여, 직종최고급여 조회하기
--{ employees테이블 }{ jobs 테이블 }
select E.employee_id,
E.first_name,
E.job_id, E.salary,
J.job_title,
J.min_salary,
J.max_salary
from employees E, jobs J
where E.job_id = J.job_id;
예시4) 부서아이디, 부서명, 부서담당자 아이디, 부서 담당자의 이름(사원), 부서 담당자(사원)의 연락처를 조회하기
-- { departments } { employees }
SELECT D.DEPARTMENT_ID,
D.DEPARTMENT_NAME,
D.MANAGER_ID,
E.FIRST_NAME,
E.PHONE_NUMBER
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID
ORDER BY D.MANAGER_ID;
///////////////////////////////////////////비등가 조인
비등가 조인
연산자가 아닌 다른 연산자를 사용해서 조인조건을 정의하는 조인,
등가 조인은 n개를 조인하면 n-1개의 조건이 있어야하지만 비등가 조건은 갯수가 상관없다.
※. 주로 범위를 조인할때 사용된다.
===========================예시를 위해 생성한 테이블
-- 비등가조인 실습을 위한 테이블 생성
create table job_grades(
gra char(1) not null primary key,
lowest_salary number(7),
highest_salary number(7)
);
-- 테이블 삭제
--drop table job_grades;
insert into job_grades (gra, lowest_salary, highest_salary) values('A',1000,2999);
insert into job_grades (gra, lowest_salary, highest_salary) values('B',3000,5999);
insert into job_grades (gra, lowest_salary, highest_salary) values('C',6000,9999);
insert into job_grades (gra, lowest_salary, highest_salary) values('D',10000,14999);
insert into job_grades (gra, lowest_salary, highest_salary) values('E',15000,24999);
insert into job_grades (gra, lowest_salary, highest_salary) values('F',25000,40000);
commit;
=======================================================
예시) 사원의 이름, 사원의 급여, 급여등급을 조회하기
등호(=)를 사용하는 것이 아니라, 급여가 최소급여와 최대급여 사이에 해당하는 등급관련행이 조인되어야함
select e.first_name, e.salary, jg.gra
from employees e, job_grades jg
where e.salary >= jg.LOWEST_SALARY
and e.salary <= jg.highest_salary
order by e.salary desc;
/////////////////////////////////////////////////포괄 조인
포괄조인(아웃터조인 : Outer Join)
포괄조인을 사용하면 조인 조건을 만족하지 않는 행도 조회된다.
포괄조인 연산자는 (+) 기호다. null행을 생성하는 조인
※. 포괄조인은 쿼리의 실행속도를 현저하게 떨어트린다.
예시) 사원이름, 부서아이디, 부서이름, 조회하기
select e.first_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+); -- (+)붙여서 포괄조인 사용
예시2) 부서 아이디, 부서이름, 부서담당자 아이디, 부서담당자 이름, 부서 담당자 직종아이디 조회하기
SELECT d.department_id, d.department_name, d.manager_id, e.first_name, e.JOB_ID
FROM employees e, departments d
WHERE d.manager_id = e.employee_id(+);
예시3) 부서아이디, 부서이름, 부서담당자 아이디, 부서담당자 이름, 부서의 주소를 조회하기
select d.department_id, d.department_name, d.manager_id, e.first_name, l.street_address
FROM departments d, locations l, employees e
where d.manager_id = e.EMPLOYEE_ID(+)
and d.location_id = l.LOCATION_ID(+)
order by d.department_id;
예제4) 사원의 이름, 사원의 급여, 급여등급, 소속부서이름을 조회하기
SELECT e.first_name, e.salary, jg.gra, d.department_name
FROM employees e, job_grades jg, departments d
WHERE e.salary >= jg.LOWEST_SALARY
and e.salary <= jg.HIGHEST_SALARY
and d.department_id(+) = e.department_id
order by first_name;
/////////////////////////////////////////////////셀프 조인
셀프조인
자기 자신의 테이블과 다시 조인을 해야하는 경우 사용된다.
결국 같은 테이블을 서로 조인하기 때문에 테이블마다 적절한 역할을 부여해서 조인에 참여시켜야한다.
사용예 : 상하관계(재귀관계)가 있는 조직도, 카테고리, 메뉴구성 정보 등 자기 상위 단계가 자기안에 있는것을
통하여 데이터를 조회할 수 있는 경우
예시) 100번 부서에 근무하는 사원의 아이디, 사원의 이름, 그 사원의 상사아이디, 그 사원의 상사이름을 조회하기
SELECT 부하.employee_id 부하_아이디, 부하.first_name 부하_이름, 부하.DEPARTMENT_ID 부서,
상사.employee_id 상사_아이디, 상사.first_name 상사_이름
FROM employees 부하, employees 상사
WHERE 부하.manager_id = 상사.employee_id
AND 부하.department_id = 100;
예시2) 90번 부서에 근무하는 직원에게 보고하는 직원들의 아이디, 이름, 급여, 부서명, 상사이름을 조회하기
SELECT 부하.employee_id 부하_ID, 부하.first_name 부하_이름, 부하.salary, D.department_name, 상사.first_name 상사_이름
FROM employees 부하, employees 상사, departments D
WHERE 부하.manager_id = 상사.employee_id
and 부하.department_id = D.department_id
and 상사.department_id = 90;
