11/25 오라클 [2]

어제에 이어서 오라클 수업을 진행했다.

 

-- 형 변환함수 TO_CHAR,TO_NUMBER, TO_DATE

-- 날짜를 문자로 TO_CHAR(값, 형식)

SELECT TO_CHAR(sysdate) FROM dual;

SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH:MI:SS') FROM dual;

SELECT TO_CHAR(sysdate, 'YY-MM-DD A.M. HH:MI:ss') FROM dual;

SELECT first_name, TO_CHAR(hire_date, 'YYYY"년" MM"월" DD"일"')

FROM employees;

--숫자를 문자로 TO_CHAR(값, 형식)

SELECT TO_CHAR(20000, '99999') FROM dual;

--주어진 자릿수에 숫자를 모두 표기할 수 없어서 모두 #으로 표기됩니다.

SELECT TO_CHAR(20000,'9999') FROM dual;

SELECT TO_CHAR(20000, '99999.9') FROM dual;

--문자를 숫자로 TO_NUMBER(값, 형식)

SELECT '2000' + 2000 FROM dual; --자동 형변환

SELECT TO_NUMBER('2000') + 2000 FROM dual; -- 명시적 형 변환

SELECT '$3,300' + 2000 FROM dual; --에러

SELECT TO_NUMBER('$3,300', '$9,999') + 2000 FROM dual;

--문자를 날짜로 변환하는 함수 TO_DATE(값, 형식)

SELECT TO_DATE('2021-11-25') FROM dual; --기본 패턴

SELECT sysdate - TO_DATE('2021-03-25') FROM dual; --날짜로 변환해야 연산이 가능합니다.

SELECT TO_DATE('2020/12/25', 'YY/MM/DD') FROM dual;

--주어진 문자열을 모두 변환해야 합니다.

SELECT TO_DATE('2021-03-31 12:23:50', 'YYYY-MM-DD HH:MI:SS') FROM dual;

-- xxxx년 xx월 xx일 문자열 형식으로 변환해 보세요.

-- 조회 컬럼명은 dateInfo로 하겠습니다.

SELECT '20050103' FROM dual;

SELECT

TO_CHAR(

TO_DATE ('20050103', 'YYYY/MM/DD'),

'YYYY"년" MM"월"DD"일"') AS dateInfo

FROM dual;

--NULL 제거 함수 NVL(컬럼, 변환할 타겟값)

SELECT null FROM dual;

SELECT NVL(null, 0) FROM dual;

SELECT * FROM employees;

SELECT first_name, NVL(commission_pct, 0) AS comm_pct

FROM employees;

--NULL 제거 함수 NVL(컬럼, null이 아닐 경우 값, null일 경우 값)

SELECT NVL2(50, '널아님', '널') FROM dual;

SELECT first_name, NVL2(commission_pct, 'true', 'false')

FROM employees;

SELECT

first_name,

commission_pct,

NVL2(commission_pct, salary + (salary * commission_pct), salary) AS real_salary

FROM employees;

SELECT

first_name,

salary + (salary * commission_pct) as real_salary

FROM employees;

-- DECODE(컬럼 혹은 표현식, 항목1, 결과1, 항목2, 결과2 .... default)

SELECT

DECODE('B', 'A', 'A입니다', 'B', 'B입니다', 'C', 'C입니다', '모르겠는데요')

FROM dual;

SELECT

job_id,

salary,

DECODE(job_id, 'IT_PROG', salary*1.1, 'FI_MRG', salary*1.2, 'AD_VP', salary*1.3, salary)

AS result

FROM employees;

--CASE WHEN THEN END

SELECT

first_name,

job_id,

salary,

(CASE job_id

WHEN 'IT_PROG' THEN salary*1.1

WHEN 'FI_MGR' THEN salary * 1.2

WHEN 'FI_ACCOUNT' THEN salary * 1.3

WHEN 'AD_VP' THEN salary * 1.4

ELSE salary

END) AS result

FROM employees;

 

--1개 이상의 테이블에서 데이터를 조회하기 위해서

-- employees 테이블의 부서 id와 일치하는 departments 테이블의 부서 id를 찾아서

-- SELECT이하에 있는 컬럼들을 출력하는 쿼리문입니다.

SELECT

e.first_name, e.last_name, e.hire_date,

e.salary, e.job_id, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

SELECT

e.first_name, e.last_name, e.hire_date,

e.salary, e.job_id, e.department_id, d.department_name

FROM employees e INNER JOIN departments d

ON e.department_id = d.department_id;

/* 각각의 테이블에 독립적으로 존재하는 컬럼의 경우에는 생략을 해도 무방합니다.

그러나, 해석의 명확성을 위해 테이블 이름을 작성하셔서 소속을 표현해 주는 것이 바람직합니다.

테이블 이름이 너무 길 시에는 ALIAS를 작성하여 칭합니다.

두 테이블 모두 가지고 있는 컬럼의 경우 반드시 명시해 주셔야 합니다.

*/

--3개의 테이블을 이용한 내부 조인

--내부 조인: 두 테이블 모두에서 일치하는 값을 가진 행만 반환합니다.

SELECT

e.first_name, e.last_name, e.department_id,

d.department_name,

j.job_title

FROM employees e, departments d, jobs j

WHERE e.department_id = d.department_id

AND e.job_id = j.job_id;

--조인 조건과 함꼐 사용되는 일반 조건이 있을 경우

SELECT

e.first_name, e.last_name, e.department_id,

d.department_name, e.job_id, j.job_title, loc.city

FROM

employees e,

departments d,

jobs j,

locations loc

WHERE e.department_id = d.department_id

AND e.job_id = j.job_id --3,4

AND d.location_id = loc.location_id --2

AND loc.state_province = 'California'; --1

/* 1. loc테이블의 province = 'California' 조건에 맞는 값을 대상으로

2. location_id값과 같은 값을 가지는 데이터를 departments에서 찾아서 조언합니다

3. 위의 결과와 동일한 department_id를 가진 employees 테이블의 데이터를 찾아 조인합니다.

4. 위의 결과와 jobs 테이블을 비교하여 조인하고 최종 결과를 출력합니다.*/

--외부 조인

/* 상호 테이블 간에 일치되는 값으로 연결되는 내부 조인과는 다르게

어느 한 테이블에 공통 값이 없더라도 해당 row들이 조회 결과에 모두 포함되는 조인을 말합니다.*/

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(+)

/* employees 테이블에는 존재하고, departments 테이블에는 존재하지 않아도

(+)가 붙지 않은 테이블을 기준으로 하여 departments 테이블이

조인에 참여하라는 의미를 부여하기 위해 기호를 붙입니다.

외부 조인을 사용했더라도, 이후에 내부 조인을 사용하면 내부 조인이 우선으로 인식됩니다.

*/

 

이 다음 과정도 배우고 선생님께서 예제문제를 풀라고 시간을 주셨다.

근데 오라클을 담기에 컴퓨터가.. 안되는지.. 저장도 안 했는데 강제 종료를 당했다

부득이하게 선생님께 코드를 받아서 문제를 풀었다.

 

 

-- 이너 조인

SELECT * FROM info

INNER JOIN auth

ON info.auth_id = auth.auth_id;

-- 오라클 문법 (잘 사용 안합니다.)

SELECT *

FROM info, auth

WHERE info.auth_id = auth.auth_id;

-- auth_id 컬럼을 그냥 쓰시면 모호하다 라고 뜹니다.

-- 그 이유는 양쪽 테이블에 모두 존재하기 때문입니다.

-- 이럴 때 컬럼에 테이블 이름을 쓰시던지, 별칭을 쓰셔서

-- 지목을 확실하게 해 주세요.

SELECT info.auth_id, title, content, name

FROM info

INNER JOIN auth

ON info.auth_id = auth.auth_id;

-- 필요한 데이터만 조회하겠다! 라고 한다면

-- WHERE 구문을 통해 일반 조건 걸어주시면 됩니다.

SELECT

i.auth_id, i.title, i.content, a.name

FROM info i

INNER JOIN auth a

ON i.auth_id = a.auth_id

WHERE a.name = '홍길자';

-- 아우터 조인

SELECT *

FROM info i LEFT JOIN auth a

ON i.auth_id = a.auth_id;

-- 오라클 문법

SELECT *

FROM info i, auth a

WHERE i.auth_id = a.auth_id(+);

-- 좌측 테이블과 우측 테이블 데이터를 모두 읽어 중복된 데이터는 삭제되는 외부 조인

SELECT *

FROM info i FULL OUTER JOIN auth a

ON i.auth_id = a.auth_id;

-- CROSS JOIN은 JOIN조건을 설정하지 않기 때문에

-- 모든 컬럼에 대해 JOIN을 진행합니다.

-- 실제로는 거의 사용하지 않습니다.

SELECT * FROM info

CROSS JOIN auth

ORDER BY id ASC;

-- 여러 테이블 조인 -> 키 값만 찾아서 구문을 연결해서 쓰면 됩니다.

SELECT *

FROM employees e

LEFT OUTER JOIN departments d ON e.department_id = d.department_id

LEFT OUTER JOIN locations loc ON d.department_id = loc.location_id;

 

오늘도 역시 많은 걸 배웠다.

확실히 수업 끝나고 복습 차원에서 티스토리를 적으니까

아까 이해가 안됐던 outer join에 대해서 이해가 된다!