/*===============================================================
TRANSACTIONS
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 10A β DML OPERATIONS & TRANSACTIONS
Topics Covered
--------------
β’ INSERT
β’ UPDATE
β’ DELETE
β’ COMMIT
β’ ROLLBACK
β’ SAVEPOINT
β’ Transaction control
===============================================================*/
/*---------------------------------------------------------------
DML INTRODUCTION
---------------------------------------------------------------*/
/*
DML stands for Data Manipulation Language.
Commands
INSERT
UPDATE
DELETE
MERGE
*/
/*---------------------------------------------------------------
CREATE TEST TABLE
---------------------------------------------------------------*/
CREATE TABLE employee_staging
(
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
/*---------------------------------------------------------------
QUESTION 1
Insert employee
---------------------------------------------------------------*/
INSERT INTO employee_staging
VALUES (100,'Steven','King');
/*---------------------------------------------------------------
QUESTION 2
Insert another employee
---------------------------------------------------------------*/
INSERT INTO employee_staging
VALUES (205,'John','Miller');
/*---------------------------------------------------------------
QUESTION 3
View inserted rows
---------------------------------------------------------------*/
SELECT *
FROM employee_staging;
/*---------------------------------------------------------------
COMMIT TRANSACTION
---------------------------------------------------------------*/
COMMIT;
/*---------------------------------------------------------------
QUESTION 4
Update employee name
---------------------------------------------------------------*/
UPDATE employee_staging
SET first_name='Mark'
WHERE employee_id = 100;
/*---------------------------------------------------------------
QUESTION 5
Check updated row
---------------------------------------------------------------*/
SELECT *
FROM employee_staging
WHERE employee_id = 100;
/*---------------------------------------------------------------
QUESTION 6
Rollback changes
---------------------------------------------------------------*/
ROLLBACK;
/*---------------------------------------------------------------
QUESTION 7
Verify rollback
---------------------------------------------------------------*/
SELECT *
FROM employee_staging
WHERE employee_id = 100;
/*---------------------------------------------------------------
SAVEPOINT
---------------------------------------------------------------*/
/*
SAVEPOINT allows partial rollback.
*/
/*---------------------------------------------------------------
QUESTION 8
Update salary for employee 101
---------------------------------------------------------------*/
UPDATE hr.employees
SET salary = salary + 200
WHERE employee_id = 101;
SAVEPOINT sp_after_raise;
/*---------------------------------------------------------------
QUESTION 9
Update salary for employee 102
---------------------------------------------------------------*/
UPDATE hr.employees
SET salary = salary + 300
WHERE employee_id = 102;
/*---------------------------------------------------------------
QUESTION 10
Rollback to savepoint
---------------------------------------------------------------*/
ROLLBACK TO sp_after_raise;
/*---------------------------------------------------------------
QUESTION 11
Verify employee salary
---------------------------------------------------------------*/
SELECT employee_id, salary
FROM hr.employees
WHERE employee_id IN (101,102);
/*---------------------------------------------------------------
DELETE OPERATIONS
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 12
Delete test employee
---------------------------------------------------------------*/
DELETE
FROM employee_staging
WHERE employee_id = 205;
/*---------------------------------------------------------------
QUESTION 13
Verify delete
---------------------------------------------------------------*/
SELECT *
FROM employee_staging;
/*---------------------------------------------------------------
QUESTION 14
Rollback deletion
---------------------------------------------------------------*/
ROLLBACK;
/*---------------------------------------------------------------
QUESTION 15
Verify rollback delete
---------------------------------------------------------------*/
SELECT *
FROM employee_staging;
/*---------------------------------------------------------------
INSERT SELECT
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 16
Insert employees into staging
---------------------------------------------------------------*/
INSERT INTO employee_staging
SELECT employee_id, first_name, last_name
FROM hr.employees
WHERE ROWNUM <= 5;
/*---------------------------------------------------------------
QUESTION 17
View staging data
---------------------------------------------------------------*/
SELECT *
FROM employee_staging;
/*---------------------------------------------------------------
QUESTION 18
Delete staging table rows
---------------------------------------------------------------*/
DELETE FROM employee_staging;
/*---------------------------------------------------------------
QUESTION 19
Rollback delete
---------------------------------------------------------------*/
ROLLBACK;
/*---------------------------------------------------------------
QUESTION 20
Commit transaction
---------------------------------------------------------------*/
COMMIT;
/*---------------------------------------------------------------
END OF PART 10A
---------------------------------------------------------------*/
/*===============================================================
PART 10B β MERGE & TRANSACTION WORKFLOWS
Continuation of PART 10A
Topics Covered
--------------
β’ MERGE statement
β’ Data synchronization
β’ Transaction workflows
β’ Read-only transactions
β’ Data validation
β’ SQL best practices
===============================================================*/
/*---------------------------------------------------------------
MERGE STATEMENT INTRODUCTION
---------------------------------------------------------------*/
/*
MERGE allows combining INSERT and UPDATE operations.
Syntax
MERGE INTO target_table t
USING source_table s
ON (join_condition)
WHEN MATCHED THEN
UPDATE ...
WHEN NOT MATCHED THEN
INSERT ...
*/
/*---------------------------------------------------------------
QUESTION 21
Create staging table
---------------------------------------------------------------*/
CREATE TABLE employee_stage1
AS
SELECT *
FROM hr.employees
WHERE 1=2;
/*---------------------------------------------------------------
QUESTION 22
Insert sample employees
---------------------------------------------------------------*/
INSERT INTO employee_stage1
SELECT *
FROM hr.employees
WHERE department_id = 90;
/*---------------------------------------------------------------
QUESTION 23
View staging data
---------------------------------------------------------------*/
SELECT *
FROM employee_stage1;
/*---------------------------------------------------------------
MERGE EXAMPLE
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 24
Merge staging data into employees
---------------------------------------------------------------*/
MERGE INTO hr.employees e
USING employee_stage1 s
ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN
UPDATE SET
e.first_name = s.first_name,
e.last_name = s.last_name
WHEN NOT MATCHED THEN
INSERT
(
employee_id,
first_name,
last_name,
department_id
)
VALUES
(
s.employee_id,
s.first_name,
s.last_name,
s.department_id
);
/*---------------------------------------------------------------
QUESTION 25
Verify merged data
---------------------------------------------------------------*/
SELECT *
FROM hr.employees
WHERE department_id = 90;
/*---------------------------------------------------------------
TRANSACTION WORKFLOW
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 26
Update employee salary
---------------------------------------------------------------*/
UPDATE hr.employees
SET salary = salary + 1000
WHERE department_id = 60;
/*---------------------------------------------------------------
QUESTION 27
Check salary changes
---------------------------------------------------------------*/
SELECT employee_id, salary
FROM hr.employees
WHERE department_id = 60;
/*---------------------------------------------------------------
QUESTION 28
Rollback salary update
---------------------------------------------------------------*/
ROLLBACK;
/*---------------------------------------------------------------
QUESTION 29
Verify rollback
---------------------------------------------------------------*/
SELECT employee_id, salary
FROM hr.employees
WHERE department_id = 60;
/*---------------------------------------------------------------
QUESTION 30
Update salary and commit
---------------------------------------------------------------*/
UPDATE hr.employees
SET salary = salary + 500
WHERE department_id = 90;
COMMIT;
/*---------------------------------------------------------------
READ ONLY TRANSACTIONS
---------------------------------------------------------------*/
/*
Ensures consistent reads.
*/
/*---------------------------------------------------------------
QUESTION 31
Start read-only transaction
---------------------------------------------------------------*/
SET TRANSACTION READ ONLY;
/*---------------------------------------------------------------
QUESTION 32
View employee data
---------------------------------------------------------------*/
SELECT *
FROM hr.employees;
COMMIT;
/*---------------------------------------------------------------
DATA VALIDATION QUERIES
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 33
Employees above company average
---------------------------------------------------------------*/
SELECT employee_id, salary
FROM hr.employees
WHERE salary >
(
SELECT AVG(salary)
FROM hr.employees
);
/*---------------------------------------------------------------
QUESTION 34
Departments with highest salaries
---------------------------------------------------------------*/
SELECT department_id, AVG(salary)
FROM hr.employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
/*---------------------------------------------------------------
QUESTION 35
Employees with top salaries
---------------------------------------------------------------*/
SELECT *
FROM
(
SELECT employee_id, salary
FROM hr.employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;
/*---------------------------------------------------------------
QUESTION 36
Department salary summary
---------------------------------------------------------------*/
SELECT
department_id,
COUNT(*) employee_count,
AVG(salary) avg_salary,
MAX(salary) max_salary
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
QUESTION 37
Employees hired recently
---------------------------------------------------------------*/
SELECT employee_id, hire_date
FROM hr.employees
WHERE hire_date >= ADD_MONTHS(SYSDATE,-12);
/*---------------------------------------------------------------
QUESTION 38
Employees without managers
---------------------------------------------------------------*/
SELECT employee_id, first_name
FROM hr.employees
WHERE manager_id IS NULL;
/*---------------------------------------------------------------
QUESTION 39
Employees with commission
---------------------------------------------------------------*/
SELECT employee_id, commission_pct
FROM hr.employees
WHERE commission_pct IS NOT NULL;
/*---------------------------------------------------------------
QUESTION 40
Department employee counts
---------------------------------------------------------------*/
SELECT department_id, COUNT(*)
FROM hr.employees
GROUP BY department_id;
/*---------------------------------------------------------------
BEST PRACTICES
---------------------------------------------------------------*/
/*
SQL Best Practices
1 Avoid SELECT *
2 Use indexes on filtering columns
3 Prefer UNION ALL over UNION when possible
4 Use bind variables in applications
5 Use analytical functions instead of complex subqueries
6 Use MERGE for ETL synchronization
7 Use transactions carefully
8 Always test with EXPLAIN PLAN
*/
/*---------------------------------------------------------------
OPTIONAL CLEANUP
---------------------------------------------------------------*/
-- DROP TABLE employee_stage1;
/*---------------------------------------------------------------
END OF PART 10
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/