[ORACLE] 조인과 서브쿼리 예제

조인 예제

문1) employees 와 departments 테이블을 조인하여 사원명과 부서명을 출력.

SELECT
    last_name,
    department_name
FROM
    employees   emp,
    departments dept
WHERE
    emp.department_id = dept.department_id;

 

문2) employees 와 departments 테이블을 조인하여 이름이 whalen 인 사원 정보 출력.

SELECT
    last_name,
    department_name
FROM
    employees   emp,
    departments dept
WHERE
        emp.department_id = dept.department_id
    AND emp.last_name = 'Whalen';

 

문3) employees 와 departments 테이블을 조인하여 부서별로 2005년 이전 입사 사원의 인원수 출력.

☞ 부서별로 표현해야하므로 GROUP BY로 묶어준 후 SELECT 해준다

SELECT
    emp.department_id,
    COUNT(*)
FROM
    employees   emp,
    departments dept
WHERE
        emp.department_id = dept.department_id
    AND emp.hire_date <= '2005-12-31'
GROUP BY
    emp.department_id;

 

 

문4) employees 와 departments, jobs 를 조인하여 last_name, salary, department_name, job_title 출력.

SELECT
    last_name,
    salary,
    department_name,
    job_title
FROM
    employees   emp,
    departments dept,
    jobs
WHERE
        emp.department_id = dept.department_id
    AND emp.job_id = jobs.job_id;

 

 

문5) employees 에서 사원별 담당 관리자 출력.

☞ (SELF JOIN)

SELECT
    emp.last_name employee,
    mgr.last_name manager
FROM
    employees emp,
    employees mgr
WHERE
    emp.manager_id = mgr.employee_id;

 

 

문6) employees 에서 담당 관리자가 없는 사원도 포함하여 사원별 담당관리자 출력.

☞ null 이어도 출력하고 싶은 컬럼에 (+) 를 붙이면 null 값도 출력됨 (OUTER JOIN)

SELECT
    emp.last_name employee,
    mgr.last_name manager
FROM
    employees emp,
    employees mgr
WHERE
    emp.manager_id = mgr.employee_id (+);

 

 

문7) employees 에서 사원명과 중간관리자명, 그리고 중간관리자의 관리자명을 출력.

☞ SELF JOIN으로 3개 만듦

SELECT
    emp.last_name,
    mgr.last_name,
    mgr_mgr.last_name
FROM
    employees emp,
    employees mgr,
    employees mgr_mgr
WHERE
        emp.manager_id = mgr.employee_id (+)
    AND mgr.manager_id = mgr_mgr.employee_id (+);

 

 

문8) employees 와 departments 테이블을 조인하여 department_id 가 90인 사원의 last_name, department_name, department_id 출력.

SELECT
    emp.last_name,
    dept.department_name,
    dept.department_id
FROM
    employees emp,
    departments dept
WHERE
    emp.department_id = dept.department_id
    and emp.department_id = 90;

 

---

서브 쿼리 예제

문1) employees 에서 사원들의 평균 월급보다 많은 월급을 받는 사원 출력.

SELECT
    last_name,
    salary
FROM
    employees
WHERE
    salary > (
        SELECT
            AVG(salary)
        FROM
            employees
    );

 

문2) employees 에서 부서번호가 100인 사원들 중에서 최대 월급을 받는 사원과 동일한 월급을 받는 사원을 조회

SELECT
    last_name,
    salary
FROM
    employees
WHERE
    salary = (
        SELECT
            MAX(salary)
        FROM
            employees
        WHERE
            department_id = 100
    );

 

문3) employees 에서 Whalen 사원보다 늦게 입사한 사원 정보 출력.

SELECT
    last_name,
    hire_date
FROM
    employees
WHERE
    hire_date > (
        SELECT
            hire_date
        FROM
            employees
        WHERE
            last_name = 'Whalen'
    );

 

문4) employees 에서 이름이 whalen 또는 fay 인 사원과 같은 월급을 받는 모든 사원들의 정보출력.

SELECT
    last_name,
    salary
FROM
    employees
WHERE
    salary IN (
        SELECT DISTINCT
            salary
        FROM
            employees
        WHERE
            last_name IN ( 'Whalen', 'Fay' )
    );

 

문5) employees 에서 월급이 13000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는모든 사원 정보 출력.

SELECT
    last_name,
    salary,
    department_id
FROM
    employees
WHERE
    department_id IN (
        SELECT DISTINCT
            department_id
        FROM
            employees
        WHERE
            salary >= 13000
    );

 

문6) employees 에서 직업이 IT_prog 인 사원의 최대 월급보다 많은 월급을 받는 사원들의 정보출력.

 

SELECT
    last_name,
    salary,
    job_id
FROM
    employees
WHERE
    salary > (
        SELECT
            MAX(salary)
        FROM
            employees
        WHERE
            job_id = 'IT_PROG'
    );