What are
DML Commands ?
DML commands only affect the data within a table.
DML (Data Manipulation Language) commands are used to insert,
update, delete, and retrieve data stored in Oracle database tables
- INSERT
→ Adds new records.
- UPDATE
→ Modifies existing records.
- DELETE
→ Removes specific records.
- MERGE
→ Conditionally inserts/updates/deletes records.
- SELECT
→ Retrieves records (data fetching).
1. INSERT – Add New Data
·
Used to add new records into a table.
INSERT INTO employees
(employee_id, name, salary) VALUES (101,
'John Doe', 50000);
2. UPDATE – Modify Existing Data
·
Used to change existing values in a
table.
UPDATE employees SET salary =
60000 WHERE employee_id = 101;
3. DELETE – Remove Specific Data
·
Used to remove rows from a table based on
a condition.
DELETE FROM employees WHERE
employee_id = 101;
🚨 Note: If the
WHERE clause is omitted, all rows in the table will be deleted!
4. MERGE – Perform Conditional Insert/Update/Delete
Used for upsert operations (insert if not exists,
update if exists).
Updates salary if employee exists, otherwise inserts a new
record.
MERGE INTO employees e
USING (SELECT 101 AS
employee_id, 'John Doe' AS name, 55000 AS salary FROM dual) s
ON (e.employee_id =
s.employee_id)
WHEN MATCHED THEN
UPDATE SET e.salary = s.salary
WHEN NOT MATCHED THEN
INSERT (employee_id, name, salary) VALUES
(s.employee_id, s.name, s.salary);
5. SELECT – Retrieve Data from Tables
Although SELECT is not technically a modifying DML
command, it is often grouped with DML because it deals with data retrieval.
SELECT name, salary FROM employees WHERE salary > 50000;
Different
ways to insert records in table:
1. Simple INSERT (Single Row Insert)
Used to insert a single row with hardcoded values.
INSERT INTO employees
(employee_id, name, salary)
VALUES (101, 'John Doe',
50000);
2. INSERT ... SELECT (Insert from Another Table)
Used to insert data from another table.
INSERT INTO employees (employee_id,
name, salary)
SELECT emp_id, emp_name,
emp_salary FROM temp_employees
WHERE status = 'Active';
3. INSERT USING CASE (Conditional Values in a Column)
Controls which values get inserted, but always
inserts a row.
INSERT INTO employee_bonus (employee_id, employee_name,
bonus_amount)
SELECT employee_id, employee_name,
CASE
WHEN salary
> 80000 THEN salary * 0.10 -- 10%
bonus for salaries above 80K
WHEN salary
BETWEEN 50000 AND 80000 THEN salary * 0.07
-- 7% bonus for salaries between 50K-80K
ELSE salary
* 0.05 -- 5% bonus for salaries below
50K
END
FROM employees;
4. MERGE (Upsert – Insert or Update Based on Condition)
Used when data should be inserted if it does not exist,
otherwise updated.
Example:
MERGE INTO employees e
USING (SELECT 103 AS employee_id, 'Mike' AS name, 60000 AS
salary FROM dual) src
ON (e.employee_id = src.employee_id)
WHEN MATCHED THEN
UPDATE SET
e.salary = src.salary
WHEN NOT MATCHED THEN
INSERT
(employee_id, name, salary) VALUES (src.employee_id, src.name, src.salary);
5. INSERT ALL (Multi-Table Insert – Unconditional)
Used to insert the same data into multiple tables at
once.
INSERT ALL
INTO employees
(employee_id, name, salary) VALUES (201, 'Alice', 70000)
INTO employee_log
(employee_id, action) VALUES (201, 'New Hire')
SELECT * FROM dual;
6. INSERT FIRST (Multi-Table Insert – Conditional)
Used to insert into only one table based on conditions.
Inserts into only one table based on the first
matched condition.
INSERT FIRST
WHEN salary >
80000 THEN
INTO
high_salary_employees (id, name, salary) VALUES (id, name, salary)
WHEN salary
BETWEEN 50000 AND 80000 THEN
INTO
mid_salary_employees (id, name, salary) VALUES (id, name, salary)
ELSE
INTO
low_salary_employees (id, name, salary) VALUES (id, name, salary)
SELECT id, name, salary FROM employees;
WHERE department = 'Sales';
7. INSERT ... VALUES DEFAULT (Default Column Values
Insert)
Used when inserting a row but using default values for some
columns.
Works if the table has default constraints on
columns.
INSERT INTO employees DEFAULT VALUES;
No comments:
Post a Comment