[Database] JOIN 문제풀이

[Database] JOIN

---

연습문제 1번

EMPLOYEES 테이블과, DEPARTMENTS 테이블은 DEPARTMENT_ID로 연결되어 있습니다.
EMPLOYEES, DEPARTMENTS 테이블을 alias를 이용해서 각각 INNER , LEFT OUTER, RIGHT OUTER, FULL OUTER 조인 하세요. 

SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SELECT * FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SELECT * FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SELECT * FROM EMPLOYEES E FULL JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

---

연습문제 2번

EMPLOYEES, DEPARTMENTS 테이블을 INNER JOIN하세요

조건1 ) employee_id가 200인 사람의 이름, department_id를 출력하세요 OUTER 조인 하세요. 
조건2) 이름 컬럼은 first_name과 last_name을 합쳐서 출력합니다

SELECT CONCAT(FIRST_NAME,LAST_NAME) AS NAME,  
       D.DEPARTMENT_ID 
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE EMPLOYEE_ID = 200;

---

연습문제 3번

EEMPLOYEES, JOBS테이블을 INNER JOIN하세요.

조건) 모든 사원의 이름과 직무아이디, 직무 타이틀을 출력하고, 이름 기준으로 오름차순 정렬

SELECT FIRST_NAME, E.JOB_ID, MANAGER_ID 
FROM EMPLOYEES E
JOIN JOBS J 
ON E.JOB_ID = J.JOB_ID
ORDER BY FIRST_NAME ASC;

---

연습문제 4번

JOBS테이블과 JOB_HISTORY테이블을 LEFT_OUTER JOIN 하세요

SELECT * FROM JOBS J
LEFT JOIN JOB_HISTORY JH
ON J.JOB_ID = JH.JOB_ID;

---

연습문제 5번

Steven King의 부서명을 출력하세요

SELECT D.DEPARTMENT_NAME  
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE E.EMPLOYEE_ID = 100;

---

연습문제 6번

EMPLOYEES 테이블과 DEPARTMENTS 테이블을 Cartesian Product(Cross join)처리하세요.

SELECT * FROM EMPLOYEES CROSS JOIN DEPARTMENTS;

---

연습문제 7번

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

SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY, E.DEPARTMENT_ID, L.CITY
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
WHERE E.JOB_ID LIKE 'SA%';

---

연습문제 8번

EMPLOYEES ,  JOBS 테이블을 조인 지정하고 job_title이 'Stock Manager', 'Stock Clerk'인 직원 정보만 출력하세요

SELECT FIRST_NAME, E.JOB_ID, J.JOB_TITLE 
FROM EMPLOYEES E
JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
WHERE J.JOB_TITLE LIKE ('Stock%');

---

연습문제 9번

DEPARTMENT 테이블에서 직원이 없는 부서를 찾아 출력하세요. 

SELECT E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.EMPLOYEE_ID IS NULL;

---

연습문제 10번

JOIN을 이용해서 사원의 이름과 그 사원의 매니저 이름을 출력하세요

SELECT E1.FIRST_NAME, E2.FIRST_NAME AS MANAGER_NAME 
FROM EMPLOYEES E1
LEFT JOIN EMPLOYEES E2
ON E1.MANAGER_ID = E2.EMPLOYEE_ID;

---

연습문제 11번

EMPLOYEES 테이블에서 left join하여 관리자(매니저)와, 매니저의 이름, 매니저의 급여 까지 출력하세요
매니저 아이디가 없는 사람은 배제하고 급여는 역순으로 출력하세요

SELECT E1.EMPLOYEE_ID, E1.FIRST_NAME, E2.EMPLOYEE_ID, E2.FIRST_NAME, E2.SALARY
FROM EMPLOYEES E1
LEFT JOIN EMPLOYEES E2
ON E1.MANAGER_ID = E2.EMPLOYEE_ID
WHERE E1.MANAGER_ID IS NOT NULL
ORDER BY E2.SALARY DESC;

---

연습문제 12번

윌리엄스미스의 계급도를 구하세요.

SELECT E3.FIRST_NAME || '>' || E2.FIRST_NAME || '>' || E1.FIRST_NAME
FROM EMPLOYEES E1
JOIN EMPLOYEES E2
ON E2.EMPLOYEE_ID = E1.MANAGER_ID
JOIN EMPLOYEES E3
ON E3.EMPLOYEE_ID = E2.MANAGER_ID
WHERE E1.FIRST_NAME = 'William' AND E1.LAST_NAME = 'Smith';