Data Manipulation in SQL

(INSERT, UPDATE, DELETE)

Data Manipulation Language (DML) commands are used to add, modify, and remove data stored in database tables. These commands directly affect the data inside tables but do not change the table structure. DML is the most frequently used part of SQL in real-world applications.

---

INSERT Statement

Adding Data to Tables

The INSERT statement is used to add new rows into a table. You can insert a single row, multiple rows, or data retrieved from another table.

---

INSERT – Adding a Single Row

INSERT INTO employees 
(employee_id, first_name, last_name, email, hire_date, salary, department)
VALUES 
(101, 'John', 'Smith', 'john.smith@company.com', '2024-01-15', 55000.00, 'IT');

Explanation

• Inserts one complete row into the employees table
• Column order must match the values
• Missing columns will get NULL or default values (if defined)

---

INSERT – Multiple Rows at Once

INSERT INTO employees 
(employee_id, first_name, last_name, email, hire_date, salary, department)
VALUES 
(102, 'Jane', 'Doe', 'jane.doe@company.com', '2024-01-20', 60000.00, 'Sales'),
(103, 'Bob', 'Johnson', 'bob.j@company.com', '2024-02-01', 52000.00, 'Marketing'),
(104, 'Alice', 'Williams', 'alice.w@company.com', '2024-02-15', 58000.00, 'IT');

Explanation

• Inserts multiple records in one query
• Faster than executing multiple single INSERT statements
• Improves performance and reduces network overhead

---

INSERT from SELECT (Data Copy)

INSERT INTO high_performers (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 70000;

Explanation

• Copies filtered data from one table to another
• Column count and data types must match
• Very useful for reporting, archiving, and backups

---

INSERT Best Practices

• Always specify column names
• Validate data before inserting
• Use transactions for bulk inserts
• Avoid inserting duplicate primary keys

---

UPDATE Statement

Modifying Existing Data

The UPDATE statement is used to change existing records in a table.

---

UPDATE – Modify a Single Row

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

Explanation

• Updates salary for only one employee
• WHERE clause ensures limited impact
• Without WHERE, all rows would be updated

---

UPDATE – Modify Multiple Columns

UPDATE employees
SET 
    salary = salary * 1.10,
    department = 'Senior IT'
WHERE employee_id = 101;

Explanation

• Updates more than one column at the same time
• Calculations can be applied during update
• Common in promotions and role changes

---

UPDATE – With Calculations

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales';

Explanation

• Uses existing column values in calculations
• Applies changes to multiple rows
• Useful for salary hikes and bonuses

---

UPDATE Using Subquery (Extra)

UPDATE employees
SET salary = salary * 1.10
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE location = 'New York'
);

Explanation

• Combines UPDATE with subquery
• Applies changes based on related table data

---

UPDATE Safety Tips

• Always run a SELECT with same WHERE first
• Use transactions (BEGIN, ROLLBACK)
• Avoid updating primary keys unless required

---

DELETE Statement

Removing Data from Tables

The DELETE statement is used to remove rows from a table.

---

DELETE – Remove a Single Row

DELETE FROM employees
WHERE employee_id = 101;

Explanation

• Deletes only the specified employee
• Safe if WHERE clause is correct

---

DELETE – Remove Multiple Rows

DELETE FROM employees
WHERE hire_date < '2015-01-01';

Explanation

• Deletes all employees hired before 2015
• Useful for cleanup and data archiving

---

DELETE Using Subquery (Extra)

DELETE FROM employees
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE location = 'Closed Office'
);

Explanation

• Deletes data based on related table conditions
• Powerful but dangerous if not tested

---

DELETE vs TRUNCATE

-- Deletes rows one by one
DELETE FROM employees;

-- Removes all rows instantly
TRUNCATE TABLE employees;

Key Differences

Feature	DELETE	TRUNCATE
WHERE clause	Supported	Not supported
Rollback	Possible	Not possible
Speed	Slower	Faster
Logging	Row-level	Minimal
Resets auto-increment	No	Yes

---

Transactions and Safety (Very Important)

BEGIN;

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT';

ROLLBACK; -- Undo changes
-- OR
COMMIT;   -- Save changes

Explanation

• Transactions protect data integrity
• Always use transactions in production

---

Common Mistakes in DML

• Forgetting WHERE clause
• Updating/deleting wrong rows
• Not backing up data
• Ignoring constraints and triggers
• Running TRUNCATE accidentally

---

Performance Tips

• Use indexes on WHERE columns
• Batch large INSERTs
• Avoid UPDATE on large tables without filters
• Use TRUNCATE only when sure

---

Real-World Use Cases

• Employee onboarding (INSERT)
• Salary revision (UPDATE)
• Data cleanup (DELETE)
• Archiving old records
• Data migration

---

Interview Questions on DML

1. Difference between DELETE and TRUNCATE?
2. Can UPDATE work without WHERE?
3. How to rollback DELETE?
4. INSERT vs INSERT SELECT?
5. What happens to auto-increment after TRUNCATE?