Data manipulation

✅ What is Data Manipulation?

Data Manipulation refers to adding, updating, deleting, and retrieving data from database tables.

In SQL, Data Manipulation Language (DML) provides commands that allow users to manage the data stored in tables.

🎯 Key SQL DML Commands:

📊 Detailed Explanation with Examples:

1. SELECT - Retrieve Data

Use SELECT to query data from tables.

sqlCopyEditSELECT first_name, last_name FROM employees;

• Retrieves first and last names of all employees.

✅ Add conditions:

sqlCopyEditSELECT * FROM employees
WHERE department = 'IT';

• Fetch employees in the IT department.

2. INSERT - Add New Data

Use INSERT INTO to add new records.

sqlCopyEditINSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Alice', 'Johnson', 'Marketing', 60000);

• Adds a new employee to the employees table.

🚨 Note: Make sure to respect data types and constraints like NOT NULL or UNIQUE when inserting.

3. UPDATE - Modify Existing Data

Use UPDATE to change existing records.

sqlCopyEditUPDATE employees
SET salary = 65000
WHERE employee_id = 10;

• Updates salary for employee with ID = 10.

⚠️ Warning: Without WHERE, it will update all rows in the table. Always use WHERE carefully.

4. DELETE - Remove Data

Use DELETE to remove rows from a table.

sqlCopyEditDELETE FROM employees
WHERE employee_id = 10;

• Deletes employee record with ID = 10.

⚠️ Warning: Like UPDATE, avoid running DELETE without a WHERE clause unless you intend to delete all records.

sqlCopyEdit-- Dangerous command, deletes all records:
DELETE FROM employees;

⚙️ Combined Example (Full Data Manipulation Workflow):

sqlCopyEdit-- 1. Add a new employee
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'HR', 50000);

-- 2. Give a salary raise to HR employees
UPDATE employees
SET salary = salary + 5000
WHERE department = 'HR';

-- 3. List HR employees
SELECT * FROM employees
WHERE department = 'HR';

-- 4. Remove an employee who left the company
DELETE FROM employees
WHERE employee_id = 15;

📦 Use Cases of Data Manipulation:

🏷️ Common Clauses with DML Commands:

✅ Good Practices for Data Manipulation:

1. Always use WHERE with UPDATE and DELETE to avoid affecting unintended rows.

2. Test queries with SELECT before running destructive actions.

3. Use transactions (BEGIN, COMMIT, ROLLBACK) for critical operations to ensure safe execution.

4. Backup important tables before large-scale updates/deletes.

5. Check constraints (like foreign keys, NOT NULL) to avoid insertion errors.

⚙️ Transactions for Safe Data Manipulation:

sqlCopyEditBEGIN;

UPDATE employees
SET salary = salary + 1000
WHERE department = 'Sales';

-- If something goes wrong
-- ROLLBACK;

-- If everything is fine
COMMIT;

🔑 Summary of DML Commands: