JOIN (오라클 및 ANSI 표준) -- '오라클 실습' 연습문제 풀이 겸

-- OUTER JOIN (오라클)

select a.emp_id, b.emp_id, b.comm, b.work_year

from temp a, tcom b

where b.work_year(+) = '2001'

and b.emp_id(+) = a.emp_id;

-- OUTER JOIN (ANSI 표준)

select a.emp_id, b.emp_id, b.comm

from temp a LEFT JOIN tcom b

ON a.emp_id = b.emp_id

and b.work_year = '2001';

-- EQUI JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev;

-- EQUI JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a JOIN emp_level b

ON a.lev = b.lev;

-- OUTER JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev(+);

-- LEFT OUTER JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a LEFT OUTER JOIN emp_level b

ON a.lev = b.lev;

-- JOINING TABLE ITSELT (SELF JOIN)

select * from tdept;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code

and b.dept_name = '영업';

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code

where b.dept_name = '영업';

select * from temp;

-- non equi join (Q/A : 03-8번)

select emp_id, emp_name, birth_date from temp;

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수 (SELF JOIN, NON-EQUI JOIN)

select a.emp_id, a.emp_name, a.birth_date, b.emp_id, b.emp_name, b.birth_date

from temp a, temp b

where a.birth_date > b.birth_date(+);

-- 20 + ... + 1 = 210 개

-- 19 + ... + 1 = 190 개

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수2 

-- 단순히 숫자만 COUNT 시 (SELF JOIN, NON-EQUI JOIN)

-- 1. GROUP BY 를 쓴다

select a.emp_id, a.emp_name, a.birth_date, count(*)

from temp a, temp b

where a.birth_date >= b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(*);

-- 2. 조금 더 조건을 명확히 나타내고 싶다면, >= 대신 OUTER JOIN을 함께 쓰고, null 이 나오는 컬럼을 count한다.

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a, temp b

where a.birth_date > b.birth_date(+)

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- 3. 2번을 ANSI 표준 SQL로 바꾸기

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a LEFT OUTER JOIN temp b

  ON a.birth_date > b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- Q/A : 03-9 #상위부서 찾기

select * from temp;

select * from tdept;

-- 1단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명

from temp a, tdept b

where a.dept_code = b.dept_code;

select * from tdept;

select * from temp;

-- 2단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a, tdept b, tdept c, temp d

where b.parent_dept = 'CA0001'

  and a.dept_code = b.dept_code

  and b.parent_dept = c.dept_code

  and c.boss_id = d.emp_id(+);

-- 2단계 (ANSI 표준--> 다중 테이블 조인 연습)

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a JOIN tdept b

  ON (a.dept_code = b.dept_code and b.parent_dept = 'CA0001') 

     JOIN  tdept c

  ON (b.parent_dept = c.dept_code)

     LEFT OUTER JOIN temp d

  ON c.boss_id = d.emp_id;

     --WHERE b.parent_dept = 'CA0001';

     --> 참고: ANSI 표준 SQL에서 where 절로 필터링 하고 싶을땐, 맨 밑에 where 절을 쓰거나 혹은 JOIN 시 ON 구문안에 and 절로 표시한다. 단 ON 절에 넣는 것은 데이터 검증 조건을 추가하는 것이며, 단순 검색 조건 추가일 경우 WHERE절을 쓸 것을 권고한다. (SQL 전문가 가이드 316p 참조)

-- 참조 부서장을 맡고 있는 부서원 및 해당 부서의 상위부서코드/명/부서장코드/부서장명   

select a.emp_id 사번, a.emp_name 부서장명, b.dept_name 부서명, b.parent_dept 상위부서코드, c.dept_name 상위부서명, d.emp_id 상위부서장코드, d.emp_name 상위부서장명 

from temp a, tdept b, tdept c, temp d

where a.emp_id = b.boss_id

  and b.parent_dept=c.dept_code

  and c.boss_id = d.emp_id(+);