day04

📌 Join

• 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법이다. 다시 말해, 두 개 이상의 테이블들을 연결하여 데이터를 검색하는 것

📌 Join의 기본 사용 방법

• 두개의 테이블에 하나라도 같은 컬럼이 있어야한다.
• 두 컬럼의 값은 공유되어야 한다.
• 보통 종인을 위해 기본키(Primary key)와
  외래키(Foreign Key)를 활용한다.

📢 inner join

• 각 테이블에 조인 조건에 일치되는 데이터만
  가져온다.
• inner Join은 교집합이라고 말한다.

SELECT	e.EMPLOYEE_ID   ,  	e.DEPARTMENT_ID 
	,	d.DEPARTMENT_ID ,	d.DEPARTMENT_NAME 	
FROM 	EMPLOYEES e 
	INNER JOIN DEPARTMENTS d 
	ON	e.DEPARTMENT_ID  = d.DEPARTMENT_ID
WHERE	e.EMPLOYEE_ID = 178 
;

📢 outer join

• join 조건에 일치하는 데이터 및 일치하지 않은
  데이터를 모두 select 한다.
• join 조건이 일치하는 데이터가 없다면 NULL로
  가져온다.
• Outer Join 은 Inner Join과는 다르게 주(main)
  테이블이 어떤 테이블인지가 중요하다. 그래서
  어떤 테이블이 중심이 되느냐에 따라 다시
  Left Outer Join, Right Outer Join, Full Outer Join
  으로 세분화 할 수 있다.
• Left Outer Join은 왼쪽에 있는 테이블이,
  Right Outer Join은 오른쪽에 있는 테이블이,
  Full Outer Join은 양쪽 테이블이 모두가 중심(주)
  이라는 뜻이다.

💡 Left Outer Join

• 왼쪽 테이블이 기준이 된다.
• Join 조건에 부합하는 데이터가 Join 당하는
  테이블(오른쪽)에 있으면 해당 데이터를, 부재하면
  NULL로 select된다.

 select * 
 from table_A t1
	left outer join table_B t2 --  table_A가 기준점

예시버전

SELECT	e.EMPLOYEE_ID 	,	e.FIRST_NAME 
	,	d.DEPARTMENT_ID ,	d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN	DEPARTMENTS d 
	ON e.EMPLOYEE_ID = d.DEPARTMENT_ID -- EMPLOYEE_ID이 기준점이 된다.
;

💡 Right Outer Join

• 오른쪽 테이블이 기준이 된다.
• Join 조건에 부합하는 데이터가 Join 당하는
  테이블(왼쪽)에 있으면 해당 데이터를, 부재하면
  NULL로 select된다.

  select * from table_A t1
	right outer join table_B t2

예시버전

SELECT	e.EMPLOYEE_ID 	,	e.FIRST_NAME 
	,	d.DEPARTMENT_ID ,	d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	RIGHT OUTER JOIN	DEPARTMENTS d 
	ON e.EMPLOYEE_ID = d.DEPARTMENT_ID -- DEPARTMENT_ID 가 기준점이 된다.
;

💡 full outer join

• 양쪽 테이블이 기준이 된다.
• Join 조건에 부합하는 데이터가 Join 당하는
  테이블(왼쪽 or 오른쪽 )에 있으면 해당 데이터를, 부재하면
  NULL로 select된다.

  select * from table_A t1
	full outer join table_B t2 -- 양쪽 다 기준점

⭐ 예시문제

• 우리 회사에 있는 사원 중에서 사원 이름, 사원 번호,
  부서이동 정보(start_date, end_date, job_id)를
  출력하시오

SELECT  e.EMPLOYEE_ID 	,	e.FIRST_NAME  -- 사원중 사원이름과 사원번호
	,	e.HIRE_DATE 
	,	jh.START_DATE 	,	jh.END_DATE   -- 부서이동 정보 
                      					 --  (start_date, end_date, 
                                         --  job_id)를 가져온 모습이다.
	,	jh.JOB_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN JOB_HISTORY jh  - 기준점 EMPLOYEES e
	ON	  e.EMPLOYEE_ID = jh.EMPLOYEE_ID -- 공통점을 맺는점(교집합부분맺기)
;

📌 ANSI Join vs Oracle Join

• SQL은 데이터베이스를 관리하기 위해 만들어진
  프로그래밍 언어이며, 데이터베이스를 관리해주는
  대부분의 DMMS들은 SQL을 사용한다. 물론 DBMS
  자체의 특수성 때문에 SQL의 사용법이 조금씩
  다르기도 하지만, 큰 틀에서 보면 나름대로의
  보편성을 가지고 있다.
• ANSI 미국 국립 표준 협회에서도 SQL에
  대한 보편적인 문법을 제시하고 있는데, 그것이
  바로 ANSI Query이다. Join도 마찬가지로
  ANSI문법을 사용하는 경우가 대부분이다.

📌 문제

📢 문제1)

사원들의 이름, 부서번호, 부서명을 출력하라

SELECT 	e.FIRST_NAME , e.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN  DEPARTMENTS d 
	ON e.EMPLOYEE_ID = d.DEPARTMENT_ID 
;

📢 문제2)

30번 부서의 사원들의 이름,직업,부서명을 출력하라

SELECT 	e.EMPLOYEE_ID , e.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON 	e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE e.DEPARTMENT_ID = 30
;

📢 문제3)

커미션을 받는 사원의 이름, 직업, 부서번호,부서명을 출력하라

SELECT 	e.FIRST_NAME , e.JOB_ID  
	, e.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON 	e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE COMMISSION_PCT IS NOT NULL 
;

📢 문제4)

지역번호 2500 에서 근무하는 사원의 이름,
직업,부서번호,부서명을 출력하라

SELECT 	e.FIRST_NAME , e.DEPARTMENT_ID 
	,	d.DEPARTMENT_NAME 	, e.JOB_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON 	e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE LOCATION_ID = 2500
;

📢 문제5)

이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라

SELECT 	e.FIRST_NAME , e.DEPARTMENT_ID 
	,	d.DEPARTMENT_NAME 	, e.JOB_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON 	e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	FIRST_NAME LIKE '%A%'
;

📢 문제6)

사원이름과 그 사원의 관리자 이름을 출력하라

SELECT 	e.EMPLOYEE_ID , e2.FIRST_NAME , e.FIRST_NAME  ,e.MANAGER_ID 
FROM 	EMPLOYEES e 
	INNER JOIN EMPLOYEES e2             -- e 와 e2
	ON e.EMPLOYEE_ID = e2.MANAGER_ID    -- e 그냥 네임 e2는 매니저네임 꺼내오는것
;

📢 문제7)

사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라

SELECT e.FIRST_NAME , d.DEPARTMENT_NAME , e.SALARY
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.SALARY >= 3000
ORDER BY e.SALARY
;

📢 문제8)

급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라

SELECT	FIRST_NAME , d.DEPARTMENT_NAME
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON	e.DEPARTMENT_ID  = d.DEPARTMENT_ID  
WHERE e.SALARY BETWEEN 3000 AND 5000
;

📢 문제9)

급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라

SELECT 	e.FIRST_NAME , e.SALARY , d.LOCATION_ID  , l.CITY 
FROM 	DEPARTMENTS d  
	RIGHT OUTER JOIN EMPLOYEES e 
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID  
	LEFT OUTER JOIN LOCATIONS l 
	ON d.LOCATION_ID = l.LOCATION_ID 
WHERE 	SALARY <= 3000 
;

📢 문제10)

Steven King의 부서명을 출력하라.

SELECT 	d.DEPARTMENT_NAME ,e.FIRST_NAME ,e.LAST_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON	e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
WHERE e.LAST_NAME = 'King' 
AND	e.FIRST_NAME = 'Steven'
;

📢 문제11)

IT부서에서 근무하고 있는 사람들을 출력하라.

SELECT FIRST_NAME , LAST_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON	e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
WHERE d.DEPARTMENT_NAME = 'IT'
;

📢 문제12)

EMPLOYEES 테이블에서 사원번호,이름,업무,
EMPLOYEES 테이블의 부서번호,
DEPARTMENTS 테이블의 부서번호,부서명,근무지를 출력하여라

SELECT 	e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID 
	,	e.DEPARTMENT_ID 
	,	d.DEPARTMENT_NAME 
	,	d.LOCATION_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID  
ORDER BY EMPLOYEE_ID 
;

📢 문제13)

EMPLOYEES 테이블과 DEPARTMENTS 테이블의 부서번호를 조인하고
SA_MAN 사원만의 사원번호,이름,급여,부서명,근무지를 출력하라.

SELECT 	e.EMPLOYEE_ID , e.FIRST_NAME  ,e.SALARY
	,	d.DEPARTMENT_NAME , d.LOCATION_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID  
WHERE e.JOB_ID  = 'SA_MAN'
;

📢 문제14)

EMPLOYEES 테이블과 DEPARTMENTS 테이블에서
DEPARTMENTS 테이블 기준으로 사원번호,이름,업무,
부서번호,부서명,근무지를
EMPLOYEES 테이블의 부서번호 출력하여라

SELECT 	e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID 
	, 	d.DEPARTMENT_NAME  , d.LOCATION_ID 
	,  	e.DEPARTMENT_ID 
FROM 	EMPLOYEES e 
	RIGHT OUTER JOIN DEPARTMENTS d
	ON	e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
;