/* =====================================================================
ORACLE DML MASTER NOTES + PRACTICE QUESTIONS
Author : Vinayak Vishweshwara Dabgar
Database Expert | Oracle Apps DBA | DBA | Developer
=====================================================================
DATA MANIPULATION LANGUAGE (DML)
--------------------------------
Used to manipulate data stored in tables.
Commands Included
-----------------
INSERT
UPDATE
DELETE
MERGE
INSERT ALL
INSERT INTO SELECT
TRUNCATE
TRANSACTIONS (COMMIT / ROLLBACK)
LOCK TABLE
FLASHBACK
RETURNING CLAUSE
PARALLEL DML
DIRECT PATH INSERT
ERROR LOGGING
---------------------------------------------------------------------
PRACTICE TABLE SETUP
---------------------------------------------------------------------
*/
CREATE TABLE DEPARTMENT_TBL (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50)
);
CREATE TABLE EMPLOYEE_TBL (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER(10,2),
dept_id NUMBER,
FOREIGN KEY (dept_id) REFERENCES DEPARTMENT_TBL(dept_id)
);
/* ---------------------------------------------------------------------
QUESTIONS
---------------------------------------------------------------------
1. What is DML in Oracle?
2. What is the difference between DML and DDL?
3. Which SQL commands are categorized as DML?
4. Why are primary and foreign keys important for DML operations?
*/
/* =====================================================================
INSERT STATEMENT
=====================================================================
Purpose:
Insert new rows into a table.
Syntax:
INSERT INTO table (col1,col2,...)
VALUES (value1,value2,...)
*/
/* ---------------------------------------------------------------------
INSERT WITH COLUMN NAMES
--------------------------------------------------------------------- */
INSERT INTO DEPARTMENT_TBL (dept_id, dept_name)
VALUES (1, 'HR');
INSERT INTO EMPLOYEE_TBL (emp_id, emp_name, salary, dept_id)
VALUES (101, 'Juian', 45000, 1);
/*
Questions
---------
1. Why is specifying column names recommended during INSERT?
2. What happens if a NOT NULL column is omitted?
3. What happens if inserted data violates a constraint?
*/
/* ---------------------------------------------------------------------
INSERT WITHOUT COLUMN NAMES
--------------------------------------------------------------------- */
INSERT INTO DEPARTMENT_TBL
VALUES (2, 'IT');
INSERT INTO EMPLOYEE_TBL
VALUES (102, 'Rahul', 55000, 2);
/*
Questions
---------
1. What risk exists when inserting rows without column names?
2. What happens if column order changes in table definition?
*/
/* ---------------------------------------------------------------------
INSERT USING SELECT
--------------------------------------------------------------------- */
INSERT INTO EMPLOYEE_TBL (emp_id, emp_name, salary, dept_id)
SELECT 103, 'SJane', 60000, 1 FROM dual;
/*
DUAL is a dummy Oracle table used for selecting constant values.
Questions
---------
1. What is the purpose of the DUAL table?
2. Can SELECT in INSERT come from multiple tables?
3. What restrictions exist between source and target columns?
*/
/* ---------------------------------------------------------------------
INSERT USING SUBSTITUTION VARIABLES
--------------------------------------------------------------------- */
INSERT INTO DEPARTMENT_TBL
VALUES (&dept_id, 'AC');
INSERT INTO DEPARTMENT_TBL
VALUES (&dept_num, '&dept_name');
/*
Questions
---------
1. What is a substitution variable?
2. Which tools support substitution variables?
*/
/* =====================================================================
INSERT ALL (MULTI ROW INSERT)
=====================================================================
Allows inserting multiple rows or multiple tables in one statement.
*/
INSERT ALL
INTO DEPARTMENT_TBL (dept_id, dept_name) VALUES (6, 'Finance')
INTO DEPARTMENT_TBL (dept_id, dept_name) VALUES (7, 'Marketing')
INTO EMPLOYEE_TBL (emp_id, emp_name, salary, dept_id) VALUES (104, 'John', 47000, 6)
SELECT * FROM dual;
/*
Questions
---------
1. What is the purpose of INSERT ALL?
2. How is INSERT ALL different from multiple INSERT statements?
*/
/* ---------------------------------------------------------------------
MULTIPLE ROW INSERT
--------------------------------------------------------------------- */
INSERT ALL
INTO emplys (emp_id, emp_name, salary) VALUES (103, 'Tom', 4500)
INTO emplys (emp_id, emp_name, salary) VALUES (104, 'Anna', 4700)
INTO emplys (emp_id, emp_name, salary) VALUES (105, 'Sara', 4900)
SELECT * FROM dual;
/*
Questions
---------
1. What is the difference between INSERT ALL and INSERT FIRST?
2. Can INSERT ALL insert into multiple tables?
*/
/* ---------------------------------------------------------------------
CONDITIONAL INSERT
--------------------------------------------------------------------- */
INSERT ALL
WHEN department_id = 10 THEN
INTO sales_emplys VALUES (emp_id, emp_name, salary)
WHEN department_id = 20 THEN
INTO hr_emplys VALUES (emp_id, emp_name, salary)
SELECT emp_id, emp_name, salary, department_id
FROM emp_staging;
/*
Questions
---------
1. What is conditional multi-table insert?
2. How does Oracle decide which WHEN clause executes?
*/
/* =====================================================================
UPDATE STATEMENT
===================================================================== */
UPDATE EMPLOYEE_TBL
SET salary = 50000
WHERE emp_name = 'Julian';
UPDATE employee_tbl
SET emp_name = 'Julian'
WHERE emp_id = 101;
/*
Questions
---------
1. What happens if WHERE clause is omitted in UPDATE?
2. How can UPDATE affect multiple rows?
*/
/* ---------------------------------------------------------------------
UPDATE MULTIPLE ROWS
--------------------------------------------------------------------- */
UPDATE employee_tbl
SET salary = salary + 5000;
/*
Question
--------
What is the effect of arithmetic expressions in UPDATE?
*/
/* ---------------------------------------------------------------------
CONDITIONAL UPDATE
--------------------------------------------------------------------- */
UPDATE employee_tbl
SET salary = salary + 3000
WHERE dept_id = 2;
/*
Question
--------
How can UPDATE be restricted to specific rows?
*/
/* ---------------------------------------------------------------------
UPDATE USING SUBQUERY
--------------------------------------------------------------------- */
UPDATE emplys
SET salary = (SELECT AVG(salary) FROM emplys)
WHERE department_id = 10;
/*
Questions
---------
1. Can subqueries return multiple rows in UPDATE?
2. How does Oracle evaluate scalar subqueries?
*/
/* =====================================================================
DELETE STATEMENT
===================================================================== */
DELETE FROM EMPLOYEE_TBL
WHERE emp_name = 'Rahul';
DELETE FROM EMPLOYEE_TBL
WHERE dept_id = 3;
/*
Questions
---------
1. What happens if DELETE has no WHERE clause?
2. How does DELETE interact with foreign keys?
*/
/* ---------------------------------------------------------------------
DELETE ALL ROWS
--------------------------------------------------------------------- */
DELETE FROM emplys;
/*
Notes
-----
DELETE can be rolled back before COMMIT.
DELETE fires triggers.
*/
/* =====================================================================
FOREIGN KEY DELETE ISSUE
===================================================================== */
DELETE FROM EMPLOYEE_TBL WHERE dept_id = 6; -- FAILS
/*
Reason
------
Child rows exist referencing the department.
*/
/* ---------------------------------------------------------------------
CASCADE DELETE
--------------------------------------------------------------------- */
DROP TABLE EMPLOYEE_TBL;
CREATE TABLE EMPLOYEE_TBL (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER(10,2),
dept_id NUMBER,
FOREIGN KEY (dept_id)
REFERENCES DEPARTMENT_TBL(dept_id)
ON DELETE CASCADE
);
insert into department_tbl values (6, 'FIN');
insert INTO EMPLOYEE_TBL VALUES (110, 'John', 47000, 6);
insert INTO EMPLOYEE_TBL VALUES (111, 'Jane', 48000, 6);
insert INTO EMPLOYEE_TBL VALUES (112, 'Joe', 49000, 6);
/*
Questions
---------
1. What is ON DELETE CASCADE?
2. When should cascading deletes be avoided?
*/
/* =====================================================================
TRUNCATE
===================================================================== */
TRUNCATE TABLE EMPLOYEE_TBL;
/*
Properties
----------
DDL operation
Implicit commit
Cannot rollback
Faster than DELETE
*/
/*
Questions
---------
1. What is the difference between DELETE and TRUNCATE?
2. Does TRUNCATE fire triggers?
*/
/* =====================================================================
MERGE (UPSERT)
===================================================================== */
MERGE INTO emplys e
USING emp_staging s
ON (e.emp_id = s.emp_id)
WHEN MATCHED THEN
UPDATE SET e.salary = s.salary, e.emp_name = s.emp_name
WHEN NOT MATCHED THEN
INSERT (emp_id, emp_name, salary)
VALUES (s.emp_id, s.emp_name, s.salary);
/*
Questions
---------
1. What is UPSERT?
2. When should MERGE be used?
3. How does Oracle determine MATCHED vs NOT MATCHED?
*/
/* =====================================================================
TRANSACTION CONTROL
===================================================================== */
COMMIT;
ROLLBACK;
SAVEPOINT before_update;
ROLLBACK TO before_update;
/*
Questions
---------
1. What is a transaction in Oracle?
2. When should COMMIT be used?
3. What is the purpose of SAVEPOINT?
*/
/* =====================================================================
LOCK TABLE
===================================================================== */
LOCK TABLE EMPLOYEE_TBL IN EXCLUSIVE MODE;
/*
Questions
---------
1. Why would a table need to be locked?
2. What are different lock modes in Oracle?
*/
/* =====================================================================
FLASHBACK QUERY
===================================================================== */
SELECT *
FROM EMPLOYEE_TBL
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);
/*
Questions
---------
1. What is Oracle Flashback?
2. What dependency does Flashback rely on?
*/
/* =====================================================================
RETURNING CLAUSE
===================================================================== */
INSERT INTO EMPLOYEE_TBL(emp_id,emp_name,salary,dept_id)
VALUES (200,'Raj',50000,1)
RETURNING emp_id INTO :new_emp;
/*
Questions
---------
1. What is RETURNING INTO used for?
2. Why is it useful in PL/SQL?
*/
/* =====================================================================
DIRECT PATH INSERT
===================================================================== */
INSERT /*+ APPEND */ INTO EMPLOYEE_TBL
SELECT * FROM EMPLOYEE_TBL;
/*
Questions
---------
1. What is direct path insert?
2. Why is APPEND faster?
*/
/* =====================================================================
PARALLEL DML
===================================================================== */
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL */ INTO EMPLOYEE_TBL
SELECT * FROM EMPLOYEE_TBL;
/*
Questions
---------
1. What is Parallel DML?
2. When should parallel DML be used?
*/
/* =====================================================================
ERROR LOGGING
===================================================================== */
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('EMPLOYEE_TBL');
END;
/
/*
Questions
---------
1. What is DML error logging?
2. Why is it useful in ETL operations?
*/
/* =====================================================================
FINAL DATA CHECK
===================================================================== */
SELECT * FROM DEPARTMENT_TBL;
SELECT * FROM EMPLOYEE_TBL;
/*
FINAL QUESTIONS FOR REVISION
----------------------------
1. What are the main DML commands?
2. What is UPSERT?
3. Difference between DELETE and TRUNCATE?
4. What is Flashback Query?
5. What is Direct Path Insert?
6. What is Parallel DML?
7. What is conditional insert?
8. What are transaction control commands?
9. What is a foreign key constraint?
10. What happens if UPDATE runs without WHERE?
===================================================================== */
/*******************************************************************************************
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.
*******************************************************************************************/