SQL 2

-- 영어단어/품사 테이블
-- 영어단어/한국어 테이블

create table 영어품사(
    단어 varchar2(50),
    품사 varchar2(50),
    constraint pk_영어품사 primary key(단어)
);
desc 영어품사;

create table 영어사전(
    단어 varchar2(50),
    의미 varchar2(50),
    constraint pk_영어사전 primary key(단어)
);
desc 영어사전;

insert into 영어품사(단어, 품사) values('boy', '명사');
insert into 영어품사(단어, 품사) values('girl', '명사');
insert into 영어품사(단어, 품사) values('get', '동사');
insert into 영어품사(단어, 품사) values('have', '동사');
insert into 영어품사(단어, 품사) values('in', '전치사');
commit;
select * from 영어품사;

insert into 영어사전(단어, 의미) values('boy', '소년');
insert into 영어사전(단어, 의미) values('girl', '소녀');
insert into 영어사전(단어, 의미) values('get', '얻다');
insert into 영어사전(단어, 의미) values('earth', '지구');
insert into 영어사전(단어, 의미) values('on', '위에');
insert into 영어사전(단어, 의미) values('eat', '먹다');
commit;
select * from 영어사전;

select * from 영어품사
where 단어 = 'get';

select * from 영어사전
where 단어 = 'get';

select a.단어, a.품사, b.의미
from 영어품사 a inner join 영어사전 b
on a.단어 = b.단어
where a.단어 = 'get';

inner join

left outer join

right outer join

full outer join

-- 다수의 테이블 합쳐서 select쿼리
-- 1) 조인쿼리 : 가로(열) 결합 
-- inner join, outer join, cross join
-- 2) 집합쿼리 : 세로(행) 결합
-- union, minus, intersect
-- 3) 서브쿼리 : 쿼리안 자식쿼리
-- 인라인뷰, 스칼라뷰, 서브쿼리

select * from 영어품사;
select * from 영어사전;

select a.단어, a.품사, b.의미
from 영어품사 a inner join 영어사전 b
on a.단어 = b.단어;

select a.단어, a.품사, b.의미
from 영어품사 a left outer join 영어사전 b -- left join 약식가능
on a.단어 = b.단어;

select b.단어, a.품사, b.의미
from 영어품사 a right outer join 영어사전 b 
on a.단어 = b.단어;

select a.단어, a.품사, b.단어, b.의미
from 영어품사 a full outer join 영어사전 b 
on a.단어 = b.단어;

-- cross
select a.단어, a.품사, b.단어, b.의미
from 영어품사 a cross join 영어사전 b;


select * from emp;
select * from dept;
-- inner join
select e.*, d.*
from emp e inner join dept d
on e.deptno = d.deptno; -- 40번 부서에 소속된 직원이 없으니 40번부서에 정보도 누락

-- right outer join
select e.*, d.*
from emp e right outer join dept d
on e.deptno = d.deptno;

-- 부서에 소속되지 않은 사원까지 조회하기 위해서는 left outer join을 사용
insert into emp values(7777, '홍길동', 'CLERK', 7839, sysdate, 4000, null, null, '남');
commit;
-- left outer join
select e.*, d.*
from emp e left outer join dept d
on e.deptno = d.deptno;

cross join

-- cross
select a.단어, a.품사, b.단어, b.의미
from 영어품사 a cross join 영어사전 b;

오라클 문법

-- 오라클 조인문
-- ANSI(표준) inner join
select e.*, d.*
from emp e inner join dept d
on e.deptno = d.deptno;

-- 오라클 inner join
select e.*, d.*
from emp e, dept d
where e.deptno = d.deptno;
-- ANSI(표준) left join
select e.*, d.*
from emp e left join dept d
on e.deptno = d.deptno;

-- 오라클 left join
select e.*, d.*
from emp e, dept d
where e.deptno = d.deptno(+);
-- 오라클 right join
select e.*, d.*
from emp e, dept d
where e.deptno(+) = d.deptno;
-- 오라클 cross join
select e.*, d.*
from emp e, dept d

equi vs non equi

self join

3개이상의 inner join

대륙 > 국가 > 지역 > 상세주소

-- inner join 
-- 1) equi join = : 문자값, 숫자값
-- 2) non equi join !=, >, <,..... : 대부분 숫자값

create table 참가자(이름 varchar(50),등수 number(10));
create table 상금(등수 number(10),금액 number(10));
insert into 참가자 values('루피', 1);
insert into 참가자 values('조로', 2);
insert into 참가자 values('나미', 3);
insert into 상금 values(1, 1000);
insert into 상금 values(2, 500);
insert into 상금 values(3, 100);
commit;

select * 
from 참가자 inner join 상금
on 참가자.등수 = 상금.등수;

-- 1등은 2등,3등의 상금도 받는다. 2등은 3등의 상금도 받는다...
select 참가자.이름 이름,  참가자.등수 등수, 상금.금액 금액
from 참가자 inner join 상금
on 참가자.등수 <= 상금.등수;
-- 고객이 원하는 리포트 형태로...
select 이름, sum(금액)
from (select 참가자.이름 이름,  참가자.등수 등수, 상금.금액 금액
    from 참가자 inner join 상금
    on 참가자.등수 <= 상금.등수) t
group by 이름
order by sum(금액) desc; -- 인라인뷰


-- self join(같은 테이블)
select * from emp; -- 사원의 정보도 emp, 관리자 정보도 emp

select e1.ename 사원이름, e1.mgr, e2.empno, e2.ename 관리자이름
from emp e1 inner join emp e2
on e1.mgr = e2.empno;

-- 3개이상의 테이블의 대한 inner join
create table 대륙(
    대륙번호 number(10),
    대륙이름 varchar2(50)
);
create table 나라(
    나라번호 number(10),
    나라이름 varchar2(50),
    대륙번호 number(10)
);
create table 도시(
    도시번호 number(10),
    도시이름 varchar2(50),
    나라번호 number(10)
);
insert into 대륙 values(1, '아시아');
insert into 나라 values(1, '대한민국', 1);
insert into 도시 values(1, '서울', 1);
commit;

select * from 도시;

select *
from 도시 inner join 나라
on 도시.나라번호 = 나라.나라번호;

select *
from 도시 inner join 나라
on 도시.나라번호 = 나라.나라번호
                            inner join 대륙
                            on 나라.대륙번호 = 대륙.대륙번호;