25 Set Operators

• ·         operator that returns all rows from both tables and eliminates duplicates-UNION
• ·         columns that were made up to match queries in another table that are not in both tables-TO_CHAR(NULL) or TO_DATE(NULL) or TO_NUMBER(NULL) –matching the select list
• ·         operator that returns all rows from both tables, including duplicates-UNION ALL
• ·         used to combine results into one single result from multiple SELECT statements-set operators
• ·         operator that returns rows that are unique to each table-MINUS
• ·         operator that returns rows common to both tables-INTERSECT

1.  Name the different Set operators?

UNION

UNION ALL

MINUS

INTERSECT

2.  Write one query to return the employee_id, job_id, hire_date, and department_id of all employees and a second query listing employee_id, job_id, start_date, and department_id from the job_hist table and combine the results as one single output. Make sure you sup-press duplicates in the output.

SELECT employee_id, job_id, hire_date, TO_DATE(NULL)  start_date, department_id

FROM employees

UNION

SELECT employee_id, job_id, TO_DATE(NULL), start_date,  department_id

FROM job_history

ORDER BY employee_id, hire_date, start_date NULLS FIRST;

But after reading next problem I changed it to:

SELECT employee_id, job_id, hire_date, department_id

FROM employees

UNION

SELECT employee_id, job_id,  start_date,  department_id

FROM job_history

ORDER BY employee_id, hire_date;

29 rows returned

3.  Amend the previous statement to not suppress duplicates and examine the output. How many extra rows did you get returned and which were they? Sort the output by employee_id to make it easier to spot.

SELECT employee_id, job_id, hire_date, department_id

FROM employees

UNION ALL

SELECT employee_id, job_id,  start_date,  department_id

FROM job_history

ORDER BY employee_id, hire_date;

30 rows returned

The duplicate:

This can be verified by:

SELECT employee_id, job_id, hire_date, department_id, COUNT(*)

FROM

(SELECT employee_id, job_id, hire_date, department_id

FROM employees

UNION ALL

SELECT employee_id, job_id,  start_date,  department_id

FROM job_history)

GROUP BY employee_id, job_id, hire_date, department_id

HAVING COUNT(*) > 1;

4.  List all employees who have not changed jobs even once. (Such employees are not found in the job_history table)

SELECT DISTINCT employee_id

FROM employees

MINUS

SELECT DISTINCT employee_id

FROM job_history;

15 rows returned

5.  List the employees that HAVE changed their jobs at least once.

SELECT DISTINCT employee_id

FROM employees

INTERSECT

SELECT DISTINCT employee_id

FROM job_history;

5 rows returned

6.  Using the UNION operator, write a query that displays the employee_id, job_id, and salary of ALL present and past employees. If a salary is not found, then just display a 0 (zero) in its place.

SELECT employee_id, job_id, NVL(salary, 0)

FROM employees

UNION

SELECT employee_id, job_id, 0

FROM job_history

ORDER BY employee_id;