Database_61_SQL 활용 실습 8

 

    -- PRACTICE 8
--  SET 연산자를 사용하여 업무 ID ST_CLERK을 포함하지 않는 부서의 ID를 나열
SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees
WHERE job_id='ST_CLERK';

-- SET 연산자를 사용하여 해당 지역에 부서가 없는 지역 ID와 지역 이름을 표시
SELECT country_id, country_name
FROM countries
MINUS
SELECT l.country_id, c.country_name
FROM locations l JOIN countries c
ON (l.country_id=c.country_id);

-- 입사 이후 현재 업무와 같은 업무를 담당한 적이 있는 사원(업무가 변경되었다가 현재의 업무로 복귀된 사원)의 사원 ID와 업무 ID를 나열
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;

-- 다음을 모두 나열하는 혼합 질의를 작성하시오.
    /*
    - 소속된 부서에 상관없이 EMPLOYEES 테이블에 있는 모든 사원의 이름과 부서 ID
    - 소속된 사원에 상관없이 DEPARTMENTS 테이블에 있는 모든 부서의 부서 ID와 부서 이름
    */
SELECT last_name, department_id, TO_CHAR(null) AS department_name
FROM employees
UNION
SELECT TO_CHAR(null), department_id, department_name
FROM departments;