5.join.sql.txt

-- 5.join.sql

/*
1. 조인이란?
	다수의 table간에  공통된 데이터를 기준으로 검색하는 명령어
	다수의 table이란?
		동일한 table을 논리적으로 다수의 table로 간주
			- self join
		물리적으로 다른 table간의 조인

2. 사용 table 
	1. emp & dept 
	  : deptno 컬럼을 기준으로 연관되어 있음

	 2. emp & salgrade
	  : sal 컬럼을 기준으로 연관되어 있음

  
3. table에 별칭 사용 
	검색시 다중 table의 컬럼명이 다를 경우 table별칭 사용 불필요, 
	서로 다른 table간의 컬럼명이 중복된 경우,
	컬럼 구분을 위해 오라클 엔진에게 정확한 table 소속명을 알려줘야 함
	- table명 또는 table별칭
	- 주의사항 : 컬럼별칭 as[옵션], table별칭 as 사용 불가


4. 조인 종류 
	1. 동등 조인
		 = 동등비교 연산자 사용
		 : 사용 빈도 가장 높음
		 : 테이블에서 같은 조건이 존재할 경우의 값 검색 
		 : 100% 동일한 데이터를 기준으로 검색


		 -- 81년 4월 1일 이후에 입사한 사원들이 가장 많은 부서의 부서명을 구하세요.
-- 다영씨 작품
-- dname의 개수를 카운팅해서 max 최대값 
-- ??? 보장 불가
select max(dname)
from emp, dept
where hiredate > '81/04/01' and emp.deptno = dept.deptno;

-- 단계별 풀이 방식
/* 
subquery : select절 내부에 select절
inline : from 절에 select 문장의 결과를 동적으로 적용하는 기술
*/
select hiredate from emp;
select hiredate from emp where hiredate > '81/04/01';

select deptno, hiredate from emp
where hiredate in (select hiredate from emp where hiredate > '81/04/01');

select deptno from emp
where hiredate in (select hiredate from emp where hiredate > '81/04/01');

-- group by 절 사용 : 집계함수 사용시 
select deptno, count(deptno) from emp
where hiredate in (select hiredate from emp where hiredate > '81/04/01')
group by deptno;


-- inline 방식의 subquery 사용
select deptno, count
from (select deptno, count(deptno) as count 
		from emp
		where hiredate in (select hiredate from emp where hiredate > '81/04/01')
		group by deptno);


-- 부서별 최대값 보유한 count값만 출력 
select max(count)
from (select deptno, count(deptno) as count 


	2. not-equi 조인
		: = 연산식 사용 불가 
		: 100% 일치하지 않고 특정 범위내의 데이터 조인시에 사용
		: between ~ and(비교 연산자)

	3. self 조인 
		: 동일 테이블 내에서 진행되는 조인
		: 동일 테이블 내에서 상이한 칼럼 참조
			emp의 empno[사번]과 mgr[사번] 관계

	4. outer 조인 
		: 두개 이상의 테이블이 조인될때 특정 데이터가 모든 테이블에 존재하지 않고 컬럼은 존재하나 null값을 보유한 경우
		  검색되지 않는 문제를 해결하기 위해 사용되는 조인
		  null 값이기 때문에 배제된 행을 결과에 포함 할 수 있드며 (+) 기호를 조인 조건에서 정보가 부족한 컬럼쪽에 적용
		
		: oracle DB의 sql인 경우 데이터가 null 쪽 table 에 + 기호 표기 */

-- 1. dept table의 구조 검색
desc dept;
-- dept, emp, salgrade table의 모든 데이터 검색
select * from dept;
select * from emp;
select * from salgrade;






--*** 1. 동등 조인 ***
-- = 동등 비교
-- 2. SMITH 의 이름(ename), 사번(empno), 근무지역(부서위치)(loc) 정보를 검색
-- emp : ename, empno
-- dept : loc
/* 두개의 상이한 table/공통 데이터(기준) : deptno
   조건 : SMITH
   검색 컬럼은 중복된 컬럼을 요하지는 않음
   */



select ename, empno, loc
from emp, dept
where ename='SMITH' and emp.deptno=dept.deptno;

-- 3. deptno가 동일한 모든 데이터(*) 검색
-- emp & dept 
select * from emp, dept;


-- 4. 2+3 번 항목 결합해서 SMITH에 대한 모든 정보(ename, empno, sal, comm, deptno, loc) 검색하기
/* 고려사항
	: deptno는 두개의 table에 중복
	  검색에 deptno가 emp? dept?에서 검색
   해결책 : 중복되는 컬럼에 table 명시 또는 table의 별칭 명시
*/
select ename, empno, sal, comm, deptno, loc
from emp, dept
where ename='SMITH' and emp.deptno=dept.deptno;

select ename, empno, sal, comm, dept.deptno, loc
from emp, dept
where ename='SMITH' and emp.deptno=dept.deptno;
-- 5.  SMITH에 대한 이름(ename)과 부서번호(deptno), 부서명(dept의 dname) 검색하기
select ename, dept.deptno, dname
from emp, dept
where ename='SMITH' and emp.deptno=dept.deptno;

-- 6. 조인을 사용해서 뉴욕에 근무하는 사원의 이름과 급여를 검색 
-- loc='NEW YORK', ename, sal
select ename, sal from emp, dept
where loc='NEW YORK' and emp.deptno=dept.deptno 


-- 7. 조인 사용해서 ACCOUNTING 부서(dname)에 소속된 사원의 이름(ename)과 입사일(hiredate) 검색
select ename, hiredate
from emp, dept
where dname='ACCOUNTING' and emp.deptno=dept.deptno;

-- 8. 직급이 MANAGER인 사원의 이름, 부서명 검색
select ename, dname
from emp, dept
where job='MANAGER' and emp.deptno=dept.deptno;

-- 9. ? 미션 : 아정씨와 희성씨를 위한 동등조인 문제 1문제씩 제시하기 
시카고에 근무하는 사원의 이름과 직업 연봉을 검색 단 연봉이 가장 큰 사람부터 정렬


select count(ename)as 부서원수, dname
from emp, dept
where hiredate > '81/04/01' and emp.deptno = dept.deptno
group by dname;
-- *** 2. not-equi 조인 ***

-- salgrade table(급여 등급 관련 table)
-- 9. 사원의 급여가 몇 등급인지 검색
-- between ~ and : 포함 
select ename, sal , grade
from emp, salgrade
where sal between losal and hisal;




--동등조인 문제
-- 10. 사원(emp) 테이블의 부서 번호(deptno)로 부서 테이블을 참조하여 사원명, 부서번호, 부서의 이름(dname) 검색
-- 사원명, 부서번호, 부서의 이름(dname) 검색
select ename, emp.deptno, dname
from emp, dept
where emp.deptno=dept.deptno;


-- *** 3. self 조인 ***
/* 하나의 table에서 조인하는 상황을 의미
해결책 : table은 하ㅏ이나 논리적으로 여러개인듯 하게 연출해서 해결
*/
-- 11. SMITH 직원의 메니저 이름 검색
select m.ename
from emp e, emp m
where e.ename='SMITH' and e.mrg=m.empno;



-- 12. 메니저 이름이 KING(m ename='KING')인 사원들의 이름(e ename)과 직무(e job) 검색
/* emp m : 매니저 table		
		매니저 이름이 KING(m ename='KING')
   emp e : 사원 table
		사원들의 이름, 직무
*/

select e.ename, e.job
from emp e, emp m
where m.ename='KING' and e.mgr=m.empno;

select e.ename, e.job, e.mgr, m.empno, m.ename
from emp e, emp m
where m.ename='KING' and e.mgr=m.empno;




-- 13. SMITH와 동일한 근무지(e.deptno=m=.deptno)에서 근무하는 사원(동료)의 이름 검색
--SMITH 이름이라는 이름은 절대 검색 금지
select e.ename, loc from emp e, emp m, dept
where e.deptno=m.deptno and  m.ename='SMITH'
and m.deptno=dept.deptno and e.ename !='SMITH';


--*** 4. outer join ***
-- 14. 모든 사원명과 사원의 매니저 명 검색, 단 메니저가 없는 사원도 검색되어야 함
/* emp table에는 모든 사원명 존재(KING 포함)
emp table에는 mgr컬럼 존재
KING을 제외한
모든 사원의 mgr값은 상사 table의 사번과  일치
= 사원들의 상사 번호(mgr)는 상사 table의 사원번호(empno)로 존재하나
KING의 mgr(null)값이 상사 table사번에 미존재
데이터가 있다 없다(표기만으로도 데이터 표현) 관점에선 null에 해당하는 상사 table의 사번 미존재
데이터 없는 쪽이 상사 table의 사번
이곳에 (+)기호 적용해서 결과 출력 




select e.ename as 사원명, m.ename as 매니저명
from emp e, emp m
where e.mgr=m.empno;
	
select e.ename as 사원명, m.ename as 매니저명
from emp e, emp m
where e.mgr=m.empno(+);	

select e.ename as 사원명, m.ename as 매니저명
from emp e, emp m
where e.mgr(+)=m.empno;

-- 15. 모든 직원명(ename), 부서번호(deptno), 부서명(dname) 검색
-- 부서 테이블의 40번 부서와 조인할 사원 테이블의 부서 번호가 없지만,
-- outer join이용해서 40번 부서의 부서 이름도 검색하기 
-- 40번 번호가 없는 table의 컬럼은? emp table의 deptno는 없음
select e.ename, dept.deptno, dname
from emp e, dept
where e.deptno(+)=dept.deptno 




-- *** hr/hr 계정에서 test 
--16. 직원의 이름(employees first_name)과 직책(job_title)을 출력(검색)
--	단, 사용되지 않는 직책이 있다면 그 직책이 정보도 검색에 포함
--     검색 정보 이름(2개)들과 job_title(직책) 
	-- 문제 풀이를 위한 table의 컬럼값들 확인해 보기

--탐색 단계

--desc로 job_title이 어떤 table에 포함되어 있는지 확인
desc employees;
desc jobs;

-- employees와 jobs의 table 관계도, 데이터 확인
select count(*) from employees; --직원수 확인
select * from jobs; --19개의 job_id 보유 확인

--회사내에 존재하는 모든 job을 수행하는 직원들 존재
select job_id from employees;
select distinct job_id from employees;

select first_name, job_title
from employees e, jobs j
where e.job_id = j.job_id;


--17. 직원들의 이름(first_name), 입사일, 부서명(department_name) 검색하기
select first_name, hire_date, department_name
from employees e, departments d 
where e.department_id(+) = d.department_id;

-- 단, 부서가 없는 직원이 있다면 그 직원 정보도 검색에 포함시키기

-- connet SCOTT/TIGER
/* 3인 1팀
1. 구글drive의 excel 파일로 생성도 가능
	- table을 excel로 생성 및 test data 다 작성

2. table에 rdbms 즉 오라클에 존재한다는 가저하에 외부조인(outer join) 문제
두문제 도출 및 모범답안 만들기