[오라클복습 Day13] to_char를 이용해 날짜타입을 문자열로 변환해 출력하기, decode함수, case함수, join, left/right outer join

개발자로 가는 길(국비지원과정)/2. Oracle

[오라클복습 Day13] to_char를 이용해 날짜타입을 문자열로 변환해 출력하기, decode함수, case함수, join, left/right outer join

레아Leah 2022. 1. 17. 11:36

오라클 함수(function)

-단일행 함수

1) 문자관련함수(날짜에서도 데이터를 가져올 수 있음)

substr(컬럼명, 인덱스)

substr(컬럼명, 시작, 끝)

2) 숫자관련함수

abs, ceil, floor, round, sin, cos, tan

3) 변환함수

to_char(컬럼명, '변환형식')

SELECT TO_DATE('1995/12/02') FROM DUAL; --문자열
SELECT TO_CHAR(TO_DATE('1995/12/02'), 'YYYY-MM-DD DAY') FROM DUAL;

: TO_CHAR는 문자타입이 아닌 (날짜타입)을 문자열로 바꾸는 함수이기때문에 '1991/06/07' 이런 형식으로 문자열이 오면 오류가 난다. 그렇기 때문에 TO_DATE함수를 사용해 날짜함수로 바꾸어준 후 TO_CHAR를 사용해야 한다.

※변환요소의 종류

YYYY

MON

MONTH

DAY

HH/HH24/HH12

AM or PM

날짜도 근속일 계산 가능

SELECT TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') "입사일", CEIL(SYSDATE-HIRE_DATE) "근속일"
FROM EMPLOYEES;

4) null관련함수

nvl

DECODE함수

: SWITCH CASE와 비슷함

SELECT FIRST_NAME, DEPARTMENT_ID,
DECODE(DEPARTMENT_ID, 10, '경영진',
                      20, '마케팅',
                      30, '재무팀',
                      40, '인사부',
                      50, '무역팀') "부서명"
FROM EMPLOYEES;

▶in

SELECT FIRST_NAME, DEPARTMENT_ID,
DECODE(DEPARTMENT_ID, 10, '경영진',
                      20, '마케팅',
                      30, '재무팀',
                      40, '인사부',
                      50, '무역팀') "부서명"
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN  (10, 20, 30, 40, 50)
ORDER BY DEPARTMENT_ID;

▶default

SELECT FIRST_NAME, DEPARTMENT_ID,
DECODE(DEPARTMENT_ID, 10, '경영진',
                      20, '마케팅',
                      30, '재무팀',
                      40, '인사부',
                      50, '무역팀'
                      ,'미정') "부서명" --마지막에 사용한 값은 다른 모든 데이터(default)
FROM EMPLOYEES;

CASE함수

(case~when)

: IF문

SELECT FIRST_NAME, DEPARTMENT_ID,
case when DEPARTMENT_ID=10 then '경영진'
     when DEPARTMENT_ID=20 then '마케팅'
     when DEPARTMENT_ID=30 then '재무팀'
     when DEPARTMENT_ID=40 then '인사부'
     when DEPARTMENT_ID=50 then '무역팀'
     else '미정'
     end "부서명" --마지막에 사용한 값은 다른 모든 데이터(default)
FROM EMPLOYEES;

-그룹행함수

count, sum, average, min, max

select department_id, count(*)
from employees
group by department_id;

▶group by 조건에 들어가는 컬럼이라면 그룹행 함수와 함께 조회 및 사용할 수 있다.

▶그룹함수는 where절에 직접 쓸 수 없다. 대신에 having절에 사용해야 함.

--각 부서별 인원을 구하시오.(부서가 널인 경우는 조회하지 마세요.)
--단, 부서별 인원이 10명이 넘는 부서만 !
select department_id, count(*) "인원수"
from employees
where department_id is not null
group by department_id
having count(department_id) >= 10
order by department_id;

[순서]select from where groupby having order by

▶단일행 컬럼을 조회할 경우 select~from 사이의 단일행은 group by에 집어 넣는다.

--job_id별로 같은 사람의 수가 4명 이상인 업무와 인원수 조회(부서번호, job_id(업무), 인원수)

select department_id, job_id, count(job_id)
from employees
GROUP by department_id, job_id
having count(job_id) >= 4;

[join 조인함수]

: 둘 이상의 테이블로부터 데이터를 조회하는 것!

: 주로 pk, fk의 관례를 가진 테이블을 통한 검색 시 사용

조인의 종류

-cross join : 많이 사용되지 않음

-natural join : 두 테이블 사이에 컬럼명이 같은 경우(pk, fk) 조건절에 이 내용을 표기함.

-equi join(pk=fk) : ansi 표준

-self join

-outer join(left outer 왼쪽을 기준으로 pk가 있는 기준으로 데이터가 다 나오게 해주는 것 + , right outer)

별칭을 줘서 아래와 같이 간단하게 사용!

select d.department_name, e.first_name
from departments d, employees e
where d.department_id =e.department_id;

▶ansi join

: where절을 쓸 경우 +를 pk가 가지고 있는 쪽으로 사용한다.

select d.department_name, e.first_name
from departments d, employees e
where d.department_id(+) = e.department_id;

▶inner join [오라클 표준]

-join on jpin

select d.department_name, e.first_name
from departments d inner join employees e

on d.department_id(+) = e.department_id;

▶left, right outer join

정리!

왼쪽 테이블에 없는 데이터까지 출력

: ★null 데이터, fk를 가지고 있는 쪽으로 left, right를 사용해야 한다. ★

select d.department_name, e.first_name
from departments d right outer join employees e
on d.department_id(+) = e.department_id;

▶셀프조인

: 하나의 테이블 안에 pk, fk가 있어 조회시 사용

select e1.first_name "매니저명", e2.first_name "직원명"
from employees e1, employees e2
where e1.employee_id(+) = e2.manager_id;

select e1.first_name "매니저명", e2.first_name "직원명"
from employees e1 join employees e2
on e1.employee_id(+) = e2.manager_id;

<연습문제>

select e.first_name ||'은 '||l.city||'입니다.'
from employees e, departments d, locations l
where e.department_id(+) = d.department_id
and d.location_id = l.location_id
and e.employee_id = 101;

출력값

Neena은 Seattle입니다.

<서브쿼리>

: Main 쿼리 안에 들어가 있는 쿼리를 Sub쿼리라고 한다.

: from절, 컬럼, where절 어디에서든지 사용 가능

ex) 사원번호가 100번인 사원의 급여와 같은 사람의 급여

<단일행 서브쿼리 >

--단일행 서브쿼리
select first_name, salary
from employees
where salary = (select salary
from employees
where employee_id = 200);

출력값

Jennifer 4400

하나의 데이터값만 나올 경우만 =을 사용하고 아닐 경우는 다른 연산자 between and, in을 사용해준다.

서브쿼리의 질의 결과가 딱 하나일 경우에만! 사용!

<복수행 서브쿼리 >

--복수행 서브쿼리
select first_name, job_id from employees
where job_id in (select job_id from employees where department_id =30);

출력값

Den PU_MAN
Sigal PU_CLERK
Alexander PU_CLERK
Guy PU_CLERK
Karen PU_CLERK
Shelli PU_CLERK

노란 부분의 컬럼명이 같은 것이 와야 한다.

저작자표시