1번
select department_id
from departments
minus
select department_id
from employees
where job_id = 'ST_CLERK';
2번
select country_id, country_name
from countries
where country_id not in
(select country_id
from locations
where location_id in
(select location_id
from locations
INTERSECT
select location_id
from departments));
(선생님 풀이)
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)
JOIN departments d
ON d.location_id=l.location_id;
3번
select job_id, department_id from employees
INTERSECT
select job_id, department_id from employees
where
department_id = 10 or
department_id = 20 or
department_id = 50;
(선생님 풀이)
SELECT distinct job_id, department_id
FROM employees
WHERE department_id = 10
UNION ALL
SELECT distinct job_id, department_id
FROM employees
WHERE department_id = 50
UNION ALL
SELECT distinct job_id, department_id
FROM employees
WHERE department_id = 20;
4번
select employee_id, job_id
from employees
intersect
select employee_id, job_id
from job_history;
5번
select last_name, department_id, null
from employees
union
select null, department_id, department_name
from departments;
(선생님 풀이)
SELECT last_name,department_id,TO_CHAR(null)
FROM employees
UNION
SELECT TO_CHAR(null),department_id,department_name
FROM departments;