Saturday, October 15, 2016

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;


4 comments: