Ⅰ. Multi Table JOIN(다중 테이블 조인)
-- 3개 이상의 테이블(뷰)을 가지고 조인 시켜 주는 것이다.
부서번호, 부서명, 국가명, 부서주소, 사원번호, 사원명, 기본급여를 나타내 보세요.
부서번호 : departments.department_id (P.K), employees.department_id (F.K)
부서명 : departments
국가명 : countries
부서주소 : locations
사원번호, 사원명, 기본급여 : employees
HR
select *
from departments;
HR
select *
from locations;
-- departments.location_id (F.K)와 locations.location_id (P.K)를 연결고리로 사용한다.
HR
select *
from countries;
-- locations.country_id (F.K)와 countries.country_id (P.K)를 연결고리로 사용한다.
▷ SQL 1992 code 방식
HR
select E.department_id AS 부서번호
,D.department_name AS 부서명
,C.country_name AS 국가명
,L.street_address || ' ' || L.city || ' ' || L.state_province AS 부서주소
,E.employee_id AS 사원번호
,E.first_name || ' ' || E.last_name AS 사원명
,E.salary AS 기본급여
from employees E, departments D, locations L, countries C
where E.department_id = D.department_id AND
D.location_id = L.location_id AND
L.country_id = C.country_id
order by 1;
▷ SQL 1999 code 방식
HR
select E.department_id AS 부서번호
,D.department_name AS 부서명
,C.country_name AS 국가명
,L.street_address || ' ' || L.city || ' ' || L.state_province AS 부서주소
,E.employee_id AS 사원번호
,E.first_name || ' ' || E.last_name AS 사원명
,E.salary AS 기본급여
from employees E
JOIN departments D
ON E.department_id = D.department_id
JOIN locations L
ON D.location_id = L.location_id
JOIN countries C
ON L.country_id = C.country_id
order by 1;
-- 부서번호 NULL인 Kimberely Grant 출력
▷ SQL 1992 code 방식
HR
select E.department_id AS 부서번호
,D.department_name AS 부서명
,C.country_name AS 국가명
,L.street_address || ' ' || L.city || ' ' || L.state_province AS 부서주소
,E.employee_id AS 사원번호
,E.first_name || ' ' || E.last_name AS 사원명
,E.salary AS 기본급여
from employees E, departments D, locations L, countries C
where E.department_id = D.department_id(+) AND
D.location_id = L.location_id(+) AND
L.country_id = C.country_id(+)
order by 1;
▷ SQL 1999 code 방식
HR
select E.department_id AS 부서번호
,D.department_name AS 부서명
,C.country_name AS 국가명
,L.street_address || ' ' || L.city || ' ' || L.state_province AS 부서주소
,E.employee_id AS 사원번호
,E.first_name || ' ' || E.last_name AS 사원명
,E.salary AS 기본급여
from employees E
LEFT JOIN departments D
ON E.department_id = D.department_id
LEFT JOIN locations L
ON D.location_id = L.location_id
LEFT JOIN countries C
ON L.country_id = C.country_id
order by 1;
salary가 6000 이상인 사원들에 대해서만 부서번호, 부서명, 부서도시명, 사원번호, 사원명, 기본급여가 나오도록 하세요.
▷ 첫 번째 방법
HR
select E.department_id AS 부서번호
, D.department_name AS 부서명
, L.city AS 부서도시명
, E.employee_id AS 사원번호
, E.first_name || ' ' || E.last_name AS 사원명
, E.salary AS 기본급여
from employees E
LEFT JOIN departments D
ON E.department_id = D.department_id
LEFT JOIN locations L
ON D.location_id = L.location_id
where E.salary >= 6000
order by 1;
▷ 두 번째 방법
HR
select E.department_id AS 부서번호
, D.department_name AS 부서명
, L.city AS 부서도시명
, E.employee_id AS 사원번호
, E.ENAME AS 사원명
, E.salary AS 기본급여
from (select department_id
, employee_id
, first_name || ' ' || last_name AS ENAME
, salary
from employees
where salary >= 6000) E
LEFT JOIN departments D
ON E.department_id = D.department_id
LEFT JOIN locations L
ON D.location_id = L.location_id
order by 1;
Ⅱ. NON-EQUI JOIN
-- 조인조건절에 사용되어지는 컬럼의 값이 '같다, 같지 않다'가 아니라 특정 범위에 속할 때 사용되어지는 JOIN이다.
-- 소득세율 지표 관련 테이블을 생성
HR
create table tbl_taxindex
(lowerincome number -- 연봉의 최저
,highincome number -- 연봉의 최대
,taxpercent number(2,2) -- 세율 -0.99 ~ 0.99
);
insert into tbl_taxindex(lowerincome,highincome,taxpercent)
values(1, 99999, 0.02);
insert into tbl_taxindex(lowerincome,highincome,taxpercent)
values(100000, 149999, 0.05);
insert into tbl_taxindex(lowerincome,highincome,taxpercent)
values(150000, 199999, 0.08);
insert into tbl_taxindex(lowerincome,highincome,taxpercent)
values(200000, 10000000000000000, 0.1);
commit;
select *
from tbl_taxindex;
사원번호, 사원명, 연봉, 연소득세율, 연소득세액을 나타내세요.
▷ SQL 1992 code 방식
HR
select E.employee_id AS 사원번호
, E.ENAME AS 사원명
, E.YEARPAY AS 연봉
, T.taxpercent AS 연소득세율
, E.YEARPAY * T.taxpercent AS 연소득세액
from
(
select employee_id
, first_name || ' ' || last_name AS ENAME
, nvl(salary+(salary*commission_pct), salary) * 12 AS YEARPAY
from employees ) E , tbl_taxindex T
where E.YEARPAY BETWEEN T.LOWERINCOME AND T.HIGHINCOME
order by 3;
▷ SQL 1999 code 방식
HR
select E.employee_id AS 사원번호
, E.ENAME AS 사원명
, E.YEARPAY AS 연봉
, T.taxpercent AS 연소득세율
, E.YEARPAY * T.taxpercent AS 연소득세액
from
(
select employee_id
, first_name || ' ' || last_name AS ENAME
, nvl(salary+(salary*commission_pct), salary) * 12 AS YEARPAY
from employees ) E JOIN tbl_taxindex T
ON E.YEARPAY BETWEEN T.LOWERINCOME AND T.HIGHINCOME
order by 3;
Ⅲ. SELF JOIN(자기 조인)
사원번호, 사원명, 급여, 직속상관사원번호(manager_id), 직속상관사원명을 나타내세요.
▷ SQL 1992 code 방식
HR
select E1.employee_id AS 사원번호
, E1.first_name || ' ' || E1.last_name AS 사원명
, E1.salary AS 급여
, E2.employee_id AS 직속상관사원번호
, E2.first_name || ' ' || E2.last_name AS 직속상관사원명
from employees E1, employees E2
where E1.manager_id = E2.employee_id(+)
order by E1.employee_id;
▷ SQL 1999 code 방식
HR
select E1.employee_id AS 사원번호
, E1.first_name || ' ' || E1.last_name AS 사원명
, E1.salary AS 급여
, E2.employee_id AS 직속상관사원번호
, E2.first_name || ' ' || E2.last_name AS 직속상관사원명
from employees E1 LEFT JOIN employees E2
ON E1.manager_id = E2.employee_id
order by E1.employee_id;
Ⅳ. Stored VIEW(저장된 뷰)
뷰(VIEW)란?
: 테이블은 아니지만 SELECT 되어진 결과물이 테이블형태로 보여지기 때문에 SELECT 문을 마치 테이블처럼 간주하는 것이다.
뷰(VIEW)를 사용하는 목적
1) 복잡한 SELECT 문을 간단하게 해서 재사용하기 위해서
2) 보안목적상 접근할 수 있는 행과 접근할 수 있는 컬럼만 다른 데이터베이스 사용자에게 SELECT 할 수 있도록 권한을 주기 위해서
HR
create or replace view view_panmae_2019
as
select jepumname
, to_char(panmaedate, 'yyyy-mm') AS 판매월
, sum(panmaesu) AS 판매량
from
(
select *
from tbl_panmae_201907
UNION
select *
from tbl_panmae_201908
UNION
select *
from tbl_panmae
) V
group by jepumname, to_char(panmaedate, 'yyyy-mm')
order by 1, 2;
HR
select jepumname, 판매량
from view_panmae_2019
where jepumname != '새우깡' AND 판매량 >= 30;
▷ 생성되어진 view를 수정하기
HR
create or replace view view_panmae_2019
as
select jepumname
, to_char(panmaedate, 'yyyy-mm') AS PANMAEMONTH
, sum(panmaesu) AS TOTALPANMAESU
from
(
select *
from tbl_panmae_201907
UNION
select *
from tbl_panmae_201908
UNION
select *
from tbl_panmae
) V
group by jepumname, to_char(panmaedate, 'yyyy-mm')
order by 1, 2;
select *
from view_panmae_2019;
-- 생성과 수정이 같다.
-- 생성은 create 또는 create or replace 둘 다 가능하지만 수정은 create or replace만 가능하다. 따라서 생성과 수정을 할 때 create or replace하는 것이 편하다.
-- view를 생성이나 수정하려고 할 때, 같은 이름이 없으면 생성하고 같은 이름이 있다면 수정한다.
▷ 생성되어진 view의 목록을 조회하기
HR
select *
from user_views;
▷ 뷰의 소스를 조회하기
HR
select text
from user_views
where view_name = 'VIEW_PANMAE_2019';
▷ 생성되어진 뷰를 삭제하기
HR
drop view VIEW_PANMAE_2019;
select *
from user_views;
보안목적상 다른 데이터베이스 사용자에게 접근할 수 있는 행, 컬럼을 SELECT 할 수 있도록 권한을 준다.
HR
create or replace view view_emps
as
select employee_id, first_name, last_name, hire_date, salary, commission_pct, manager_id, department_id
from employees
where employee_id != 100;
select *
from view_emps;
HR
grant select on view_emps to myorauser;
▷ 결과
Grant을(를) 성공했습니다.
MYORAUSER
select *
from user_tab_privs_recd;
MYORAUSER
select *
from HR.view_emps;
VIEW를 통해서 DML(insert, update, delete)가 가능하다.
HR
create or replace view view_emps2
as
select *
from employees
where department_id in (10,20,30);
select *
from view_emps2;
HR
insert into view_emps2 (employee_id, last_name, email, hire_date, job_id, department_id)
values (1234, '길동', 'kildong@naver.com', sysdate, 'PU_CLERK', 10);
commit;
select *
from view_emps2;
-- VIEW 를 사용하여 DML(INSERT, UPDATE, DELETE)을 하면 VIEW 의 원본소스 테이블에 DML(INSERT, UPDATE, DELETE)이 발생되어진다.
HR
insert into view_emps2 (employee_id, last_name, email, hire_date, job_id, department_id)
values (1235, '순신', 'ss@naver.com', sysdate, 'PU_CLERK', 50);
commit;
select *
from view_emps2;
-- '순신'은 부서번호가 50이므로 view에 조회되지 않는다.
HR
select *
from employees;
-- employees table에 보면 view에 insert 했던 '길동'과 '순신'이 있는 것을 확인할 수 있다.
VIEW 생성시 WHERE 절 다음에 WITCH CHECK OPTION 을 주어서 생성해본다.
HR
create or replace view view_emps2
as
select *
from employees
where department_id in (10,20,30)
with check option constraint view_emps2_check;
▷ 결과
View VIEW_EMPS2이(가) 생성되었습니다.
HR
insert into view_emps2 (employee_id, last_name, email, hire_date, job_id, department_id)
values (1236, '정화', 'jh@naver.com', sysdate, 'PU_CLERK', 30);
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into view_emps2 (employee_id, last_name, email, hire_date, job_id, department_id)
values(1237, '봉길', 'bk@naver.com', sysdate, 'PU_CLERK', 50);
-- VIEW 생성시 WITCH CHECK OPTION을 주면 VIEW의 WHERE 절이 마치 CHECK 제약처럼(실제는 제약의 종류는 V)으로 사용되어 WHERE절에 위배가 되어지는 DML은 사용불가(오류발생)하다.
-- WHERE절에 만족하는 데이터만 DML이 가능하다.
HR
select *
from user_constraints
where table_name = 'VIEW_EMPS2';
-- CONSTRAINT_TYPE : V 는 WITCH CHECK OPTION이다.
HR
update view_emps2 set department_id = 20
where employee_id = 1236;
commit;
▷ 결과
1 행 이(가) 업데이트되었습니다.
HR
update view_emps2 set department_id = 70
where employee_id = 1236;
-- VIEW 생성시 WITCH CHECK OPTION을 주면 VIEW의 WHERE 절이 마치 CHECK 제약처럼(실제는 제약의 종류는 V)으로 사용되어 WHERE절에 위배가 되어지는 DML은 사용불가(오류발생)하다.
-- WHERE절에 만족하는 데이터만 DML이 가능하다.
읽기전용(select 만 가능한) VIEW 를 생성하려면 WITH READ ONLY 를 사용하여 만든다.
HR
create or replace view view_emps2
as
select *
from employees
where department_id in (10,20,30)
with read only;
HR
insert into view_emps2 (employee_id, last_name, email, hire_date, job_id, department_id)
values (1238, '중근', 'jk@naver.com', sysdate, 'PU_CLERK', 30);
VIEW 생성시 사용하는 create or replace view은 create or replace noforce view와 같다.
: VIEW 소스의 원본테이블이 존재할때만 VIEW를 생성해주는 것이다.
HR
create or replace view view_emps2
as
select *
from employees1234
where department_id in (10,20,30);
HR
create or replace force view view_emps2
as
select *
from employees1234
where department_id in (10,20,30);
▷ 결과
경고: 컴파일 오류와 함께 뷰가 생성되었습니다.
-- VIEW 생성시 force 를 사용하면 VIEW 소스의 원본테이블(또는 원본 VIEW) 의 존재유무와 관계없이 무조건 VIEW를 생성해 준다.
HR
select * from tab;
HR
select text
from user_views
where view_name = 'VIEW_EMPS2';
HR
drop view view_emps2; -- 뷰 삭제하기
create or replace view view_emps2
as
select *
from employees
where department_id in (10,20,30);
create table employees_copy3
as
select *
from employees; -- 백업
HR
drop table employees;
-- VIEW_EMPS2 뷰의 원본테이블을 삭제한다.
HR
select *
from user_views
where view_name = 'VIEW_EMPS2';
-- 뷰는 삭제되지 않고 그대로 남아있다.
HR
select * from view_emps2;
-- VIEW 소스의 원본테이블이 존재하지 않는 관계로 오류가 발생하였다.
HR
drop view view_emps2; -- VIEW 삭제하기
flashback table employees to before drop; -- 휴지통에 있던 특정테이블을 복원하기
Ⅴ. Transaction(트랜잭션)
Transaction(트랜잭션) 이란?
: 어떤 "A" 라는 테이블에 DML(insert, update, delete)가 발생이 되어지면 "A" 테이블과 연관된 다른 "B" 테이블에도 DML(insert, update, delete)을 발생시켜준다.
이때 "A" 테이블에 발생한 DML 작업과 "B" 테이블에 발생한 DML(insert, update, delete) 작업이 모두 성공했을때에만
"A" 테이블과 "B" 테이블에 발생한 DML 작업을 모두 COMMIT; 을 해 주고,
"A" 테이블에 발생한 DML 작업과 "B" 테이블에 발생한 DML 작업중 하나라도 실패를 한다라면 "A" 테이블과 "B" 테이블에 발생한 DML 작업을 모두 ROLLBACK; 을 해주어야 한다.
이와 같이 여러 테이블에 저장된 데이터값을 변화시키기 위해서 수행하는 하나의 작업 단위를 Transaction(트랜잭션)이라고 부른다.
HR
create table tbl_cafe_member
(userid varchar2(20)
,passwd varchar2(20) not null
,name varchar2(20)
,point number default 0
,constraint PK_tbl_cafe_member primary key(userid)
);
insert into tbl_cafe_member(userid, passwd, name)
values('hongkd','1234','홍길동');
insert into tbl_cafe_member(userid, passwd, name)
values('leess','1234','이순신');
commit;
select *
from tbl_cafe_member;
HR
create table tbl_cafe_board
(boardno number
,fk_userid varchar2(20)
,title Nvarchar2(10)
,contents Nvarchar2(2000)
,registerday date default sysdate
,constraint PK_tbl_cafe_board primary key(boardno)
,constraint FK_tbl_cafe_board foreign key(fk_userid)
references tbl_cafe_member(userid)
);
HR
insert into tbl_cafe_board(boardno, fk_userid, title, contents)
values(1, 'hongkd', '길동입니다.', '홍길동 입니다. ㅎㅎㅎ 좋은 하루되세요~~');
update tbl_cafe_member set point = 10
where userid = 'hongkd';
▷ 결과
1 행 이(가) 삽입되었습니다.
1 행 이(가) 업데이트되었습니다.
HR
commit;
-- 글쓰기와 포인트 적립이 동시에 이루어져야 한다. 한 과정이 이루어지면 다른 과정도 이루어져야 하고, 한 과정이 실패하면 다른 과정도 실패해야 한다.
-- 삽입과 업데이트가 둘 다 성공하였으므로 commit을 한다.
HR
insert into tbl_cafe_board(boardno, fk_userid, title, contents)
values(2, 'leess', '안녕하세요? 이순신 인사드립니다.', '이순신 입니다. 즐겁고 좋은 하루되세요~~');
update tbl_cafe_member set point = 10
where userid = 'leess';
HR
rollback;
-- 업데이트는 성공하였으나 삽입은 실패하였으므로 rollback을 한다.
