SQL JOIN 예제

-- SQL 기본 TABLE 이용
--1. 각 사원(employee)에 대해서 사번(employee_id), 이름(first_name), 
--업무명(job_title), 부서명(department_name)을 조회하시오.
--단 도시명(city)이 ‘Seattle’인 지역(location)의 부서(department)에 근무하는 직원을 사원번호 오름차순순으로 출력하시오.

SELECT E.employee_id 사번 , E.first_name 이름, job_title 업무명, department_name 부서명
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L, JOBS J
WHERE L.CITY = 'Seattle'
AND D.LOCATION_ID = L.LOCATION_ID
AND E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY employee_id;

SELECT * FROM DEPARTMENTS;
SELECT * FROM EMPLOYEES;
=================================================================================================

--2. 1997~2000년사이에 입사한 직원의 이름(first_name), 입사일(hire_date), 
--    관리자 사번(employee_id), 관리자 이름(fist_name)을 조회합니다.
--    단, 관리자가 없는 사원정보도 출력결과에 포함시켜 출력한다.

SELECT E.first_name, TO_CHAR(E.hire_date, 'YYYY') 입사일 , E1.employee_id, E1.first_name
FROM EMPLOYEES E, 
(SELECT EMPLOYEE_ID, FIRST_NAME FROM EMPLOYEES) E1
WHERE E.MANAGER_ID = E1.EMPLOYEE_ID(+)
AND TO_CHAR(E.hire_date, 'YYYY') BETWEEN 1997 AND 2000;

=================================================================================================

--3. 1999년에 입사한 직원들의 employee_id 사번, first_name 이름, last_name 성, department_name, 부서명을
--조회합니다.
--단, 배치받지 않은 사원의 부서명은 Not Assigned 로 표기하세요
(Self join)

SELECT e.employee_id 사번, e.first_name 이름, e.last_name 성, NVL(d.department_name, '<Not Assigned>') 부서명
FROM EMPLOYEES e, DEPARTMENTS d
WHERE TO_CHAR(E.hire_date, 'YYYY') = '1999'
AND e.DEPARTMENT_ID = d.DEPARTMENT_Id(+);


=================================================================================================

--4. 자신의 매니저보다 연봉 salary를 많이 받는
--직원들의 성 last_name 과 연봉 salary를 출력하세요
(Self join)

SELECT e.last_name 성, e.salary 연봉, e2.salary "매니저 연봉"
FROM EMPLOYEES e,
(SELECT employee_id, salary FROM EMPLOYEES) e2
WHERE e.MANAGER_ID = e2.employee_id
AND e.SALARY > e2.salary;

(Sub query)

SELECT last_name, salary
FROM EMPLOYEES e
WHERE salary > (SELECT salary
				FROM   EMPLOYEES m
                WHERE e.MANAGER_ID = m.EMPLOYEE_ID);

=================================================================================================


-- 6. job_title이 'Sales Representative' 이고 연봉(salary)가 9000이상 10000이하인
-- 직원의 first_name, last_name, salary 를 조회하세요

SELECT first_name, last_name, salary
FROM EMPLOYEES e, JOBS j
WHERE j.JOB_TITLE = 'Sales Representative'
AND e.JOB_ID = j.JOB_ID
AND e.SALARY BETWEEN 9000 AND 10000;


SELECT first_name, last_name, salary
FROM EMPLOYEES
WHERE SALARY BETWEEN 9000 AND 10000
AND job_id = (SELECT job_id FROM JOBS
               WHERE job_title = 'Sales Representative');

SELECT * FROM EMPLOYEES;

=================================================================================================

-- 7. 사원이 1명인 부서명(DEPARTMENT_NAME)과 직원수를 조회
-- 단, 부서가 없는 직원의 부서는 '신생부서'로 표기

SELECT NVL(d.DEPARTMENT_NAME, '신생부서') 부서 , COUNT(*) 직원수
FROM EMPLOYEES e, departments d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)
GROUP BY d.department_name
HAVING COUNT(*) = 1;



SELECT NVL ((
				SELECT department_name
                FROM DEPARTMENTS d
                WHERE e.department_id = d.DEPARTMENT_ID(+)) 
              , '신생부서') 부서명
, COUNT(*) 사원수
FROM EMPLOYEES e
GROUP BY department_id
HAVING COUNT(*) = 1;

=================================================================================================
-- 8. 부서별 가장 적은 급여를 받고 있는 직원의 이름, 부서이름, 급여를 출력하세요
-- 이름은 last name만 출력하며 부서이름으로 오름차순 정렬하고, 부서가 같은경우
-- 이름을 기준으로 오름차순 정렬하여 출력합니다.

--Group by 이용

SELECT e.last_name, d.DEPARTMENT_NAME, e.SALARY
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_Id
AND (e.department_id, e.salary) IN (
                                      SELECT department_id, MIN(salary) 급여
                                      FROM EMPLOYEES
                                      GROUP BY department_id  )
ORDER BY d.DEPARTMENT_NAME , e.LAST_NAME ASC;


-- rank over 이용

SELECT e.last_name 이름, d.department_name 부서명, e.salary
FROM(
SELECT RANK() OVER (PARTITION BY department_id ORDER BY salary asc) rank, last_name, salary, department_id
FROM EMPLOYEES) e, DEPARTMENTS d
WHERE e.department_id = d.DEPARTMENT_ID
AND e.RANK = 1
ORDER BY d.department_name, e.last_name;


=================================================================================================

--9. employee 테이블에서 급여를 많이 받는 순서대로 조회했을 때
--6번째 부터 10번째까지 5명의 last_name, first_name, salary를 조회
--단, salary 는 내림차순  (랭킹이용)

SELECT r rangking, last_name, first_name, salary
FROM (
SELECT RANK() OVER (ORDER BY salary desc) r, last_name, first_name, salary
FROM EMPLOYEES)
WHERE r BETWEEN 6 AND 10;