Oracle SQL | Week 3 Lesson 2 - Set Operators and DML

Lesson Overview

In this lesson, students learn how to combine the output of multiple queries using UNION, UNION ALL, INTERSECT, and MINUS. They also learn how to insert, update, and delete records from tables, and how to manage transactions safely using COMMIT, ROLLBACK, and SAVEPOINT.

UNION UNION ALL INTERSECT MINUS INSERT UPDATE DELETE COMMIT ROLLBACK SAVEPOINT

Trainer

Mohamed Alswaify

Mobile: 0564842804

Group: 36

Lesson: Week 3 Lesson 2

Main SQL Structure

Set Operators

UNION combines results and removes duplicates
UNION ALL combines results and keeps duplicates
INTERSECT returns common rows only
MINUS returns rows from first query not found in second query

DML Statements

INSERT adds new rows
UPDATE changes existing rows
DELETE removes rows
COMMIT saves changes permanently
ROLLBACK cancels changes
SAVEPOINT creates a point to roll back to

Important Notes Before Using Set Operators

The number of columns in both SELECT statements must be the same.
The data types of corresponding columns must be compatible.
The column names in the final result are taken from the first SELECT statement.
ORDER BY should come at the end of the full set operation.

Examples

Example 1 - UNION

Combine employees and job history rows, while removing duplicates.

SELECT employee_id, hire_date, job_id FROM employees UNION SELECT employee_id, start_date, job_id FROM job_history;

Example 2 - UNION ALL

Combine employees and job history rows, while keeping duplicates.

SELECT employee_id, hire_date, job_id FROM employees UNION ALL SELECT employee_id, start_date, job_id FROM job_history;

Example 3 - INTERSECT

Return only common rows between employees and job_history.

SELECT employee_id, hire_date, job_id FROM employees INTERSECT SELECT employee_id, start_date, job_id FROM job_history;

Example 4 - MINUS

Return rows موجودة في employees وغير موجودة في job_history.

SELECT employee_id, hire_date, job_id FROM employees MINUS SELECT employee_id, start_date, job_id FROM job_history;

Example 5 - Departments Not Found in Employees

Return department IDs الموجودة في departments وغير موجودة في employees.

SELECT department_id FROM departments MINUS SELECT department_id FROM employees;

Example 6 - Employees Who Are Not Managers

Return employee IDs that do not appear as manager IDs.

SELECT employee_id FROM employees MINUS SELECT manager_id FROM employees;

Example 7 - Department IDs Without ST_CLERK

The HR department needs a list of department IDs for departments that do not contain the job ID ST_CLERK.

SELECT department_id FROM employees MINUS SELECT department_id FROM employees WHERE job_id = 'ST_CLERK';

Example 8 - Departments Not Managed by Manager 100

Return department IDs for departments that do NOT have manager 100.

SELECT department_id FROM departments MINUS SELECT department_id FROM departments WHERE manager_id = 100;

Example 9 - INSERT

Insert a new department into the departments table.

INSERT INTO departments (department_id, department_name) VALUES (280, 'Marketing');

Example 10 - SAVEPOINT

Create a savepoint before updating data.

SAVEPOINT a;

Example 11 - UPDATE

Update department name for the inserted row.

UPDATE departments SET department_name = 'Mark' WHERE department_id = 280;

Example 12 - COMMIT and ROLLBACK

Use COMMIT to save changes or ROLLBACK to undo them.

COMMIT; ROLLBACK TO a;

Example 13 - DELETE

Delete the inserted department row.

DELETE FROM departments WHERE department_id = 280;

Additional Practice Examples

Extra Example 1 - UNION with Similar Columns

SELECT job_id FROM employees UNION SELECT job_id FROM job_history;

Extra Example 2 - MINUS to Find Unused Jobs

SELECT job_id FROM jobs MINUS SELECT job_id FROM employees;

Extra Example 3 - UPDATE More Than One Column

UPDATE departments SET department_name = 'Sales', manager_id = 100 WHERE department_id = 280;

Extra Example 4 - DELETE with Condition

DELETE FROM employees WHERE department_id = 50 AND salary < 3000;

Key Explanations

UNION vs UNION ALL

UNION removes duplicate rows, while UNION ALL keeps all rows. If performance matters and duplicates are acceptable, UNION ALL is faster.

MINUS

MINUS returns rows that exist in the first query but do not exist in the second query. It is very useful for reports like “departments without employees” or “jobs not used yet.”

INSERT / UPDATE / DELETE

These are DML statements because they change the data inside tables. Always be careful when using them, especially UPDATE and DELETE without a WHERE clause.

COMMIT / ROLLBACK / SAVEPOINT

COMMIT saves the changes permanently. ROLLBACK cancels changes. SAVEPOINT lets you return to a specific point in the transaction.

Questions

The HR department needs a list of department IDs for departments that do not contain the job ID ST_CLERK. Use the set operators to create this report.

Write a query to display all job IDs that exist in the jobs table but are not currently used in the employees table.

Insert a new department with your own department ID and name, then update its name, and finally delete it.

Explain the difference between UNION and UNION ALL with an SQL example.

Tasks

Task 1

Write a query to return all department IDs that appear in employees but do not appear in departments.

Task 2

Insert a new department, then create a savepoint, update the department name, and finally roll back to the savepoint.

Task 3

Write two queries using UNION and UNION ALL on the same data, then compare the outputs.

Challenge Task

Build a full report that finds department IDs that do not have:

employees with job ST_CLERK
manager 100
any rows in the employees table

Hint: Use MINUS more than once and compare your output carefully.

Oracle SQL: Set Operators, DML Statements, and Transaction Control