Data Manipulation Language (DML)

📋 Table of Contents

1. Introduction to DML
2. INSERT Operations
3. SELECT Queries
4. UPDATE Operations
5. DELETE Operations
6. MERGE Operations
7. Best Practices

---

Introduction to DML

DML (Data Manipulation Language) is used to retrieve, insert, update, and delete data in database tables.

┌─────────────────────────────────────────────────────────┐
│                    DML OPERATIONS                        │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  INSERT  →  Add new records to table                    │
│  SELECT  →  Retrieve data from table                    │
│  UPDATE  →  Modify existing records                     │
│  DELETE  →  Remove records from table                   │
│  MERGE   →  Insert, update, or delete based on condition│
│                                                          │
└─────────────────────────────────────────────────────────┘

---

INSERT Operations

Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Single Row Insert

-- Insert with all columns
INSERT INTO customers (first_name, last_name, email, phone)
VALUES ('John', 'Doe', '[email protected]', '555-0100');

-- Insert with auto-increment (omit ID)
INSERT INTO customers (first_name, last_name, email)
VALUES ('Jane', 'Smith', '[email protected]');

Multiple Rows Insert

INSERT INTO products (name, price, stock)
VALUES 
    ('Laptop', 999.99, 50),
    ('Mouse', 29.99, 200),
    ('Keyboard', 79.99, 150);

Insert from SELECT

-- Copy data from one table to another
INSERT INTO customers_backup
SELECT * FROM customers WHERE registration_date < '2020-01-01';

-- Insert with transformation
INSERT INTO order_summary (customer_id, total_orders, total_spent)
SELECT customer_id, COUNT(*), SUM(total_amount)
FROM orders
GROUP BY customer_id;

INSERT IGNORE (MySQL)

-- Skip rows that would cause duplicate key errors
INSERT IGNORE INTO customers (email, first_name, last_name)
VALUES ('[email protected]', 'John', 'Doe');

UPSERT Operations

-- MySQL: INSERT ON DUPLICATE KEY UPDATE
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU001', 'Laptop', 999.99, 50)
ON DUPLICATE KEY UPDATE 
    price = VALUES(price),
    stock = stock + VALUES(stock);

-- PostgreSQL: INSERT ON CONFLICT
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU001', 'Laptop', 999.99, 50)
ON CONFLICT (sku) 
DO UPDATE SET 
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

---

SELECT Queries

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT number;

Basic SELECT Examples

-- Select all columns
SELECT * FROM customers;

-- Select specific columns
SELECT first_name, last_name, email FROM customers;

-- Select with alias
SELECT 
    first_name AS 'First Name',
    last_name AS 'Last Name',
    email AS 'Email Address'
FROM customers;

-- Select distinct values
SELECT DISTINCT city FROM customers;

WHERE Clause

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE stock <= 10;
SELECT * FROM customers WHERE city = 'New York';

-- Logical operators
SELECT * FROM products WHERE price > 50 AND stock > 0;
SELECT * FROM customers WHERE city = 'NYC' OR city = 'LA';
SELECT * FROM products WHERE NOT category = 'Electronics';

-- IN operator
SELECT * FROM customers WHERE city IN ('NYC', 'LA', 'Chicago');

-- BETWEEN operator
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- LIKE operator (pattern matching)
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE name LIKE 'Lap%';

-- IS NULL / IS NOT NULL
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM orders WHERE shipped_date IS NOT NULL;

ORDER BY

-- Ascending order (default)
SELECT * FROM products ORDER BY price;

-- Descending order
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns
SELECT * FROM customers ORDER BY last_name, first_name;

-- Order by expression
SELECT name, price, (price * 0.9) AS discounted_price
FROM products
ORDER BY discounted_price DESC;

LIMIT and OFFSET

-- Get first 10 rows
SELECT * FROM customers LIMIT 10;

-- Pagination: Skip 20, get next 10
SELECT * FROM customers LIMIT 10 OFFSET 20;

-- MySQL shorthand
SELECT * FROM customers LIMIT 20, 10;  -- offset, limit

Aggregate Functions

-- COUNT
SELECT COUNT(*) FROM customers;
SELECT COUNT(DISTINCT city) FROM customers;

-- SUM
SELECT SUM(total_amount) FROM orders;

-- AVG
SELECT AVG(price) FROM products;

-- MIN and MAX
SELECT MIN(price), MAX(price) FROM products;

-- GROUP BY
SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category;

-- HAVING (filter groups)
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

Joins

-- INNER JOIN
SELECT o.order_id, c.first_name, c.last_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- LEFT JOIN
SELECT c.first_name, c.last_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

-- Multiple joins
SELECT 
    o.order_id,
    c.first_name,
    p.name AS product_name,
    od.quantity,
    od.unit_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id;

Subqueries

-- Subquery in WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery in FROM
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_avg
WHERE avg_price > 100;

-- Correlated subquery
SELECT c.first_name, c.last_name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

---

UPDATE Operations

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Update Examples

-- Update single column
UPDATE customers
SET email = '[email protected]'
WHERE customer_id = 1;

-- Update multiple columns
UPDATE products
SET price = 899.99, stock = 100
WHERE product_id = 5;

-- Update with expression
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

-- Update with CASE
UPDATE orders
SET status = CASE
    WHEN DATEDIFF(NOW(), order_date) > 30 THEN 'archived'
    WHEN shipped_date IS NOT NULL THEN 'shipped'
    ELSE 'pending'
END;

-- Update with subquery
UPDATE products
SET price = (SELECT AVG(price) FROM products WHERE category = 'Electronics')
WHERE product_id = 10;

-- Update with JOIN (MySQL)
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
SET o.discount = 10
WHERE c.loyalty_tier = 'Gold';

---

DELETE Operations

Basic Syntax

DELETE FROM table_name
WHERE condition;

Delete Examples

-- Delete specific rows
DELETE FROM customers
WHERE customer_id = 5;

-- Delete with condition
DELETE FROM orders
WHERE order_date < '2020-01-01';

-- Delete with subquery
DELETE FROM products
WHERE product_id IN (
    SELECT product_id FROM products
    WHERE stock = 0 AND last_updated < DATE_SUB(NOW(), INTERVAL 1 YEAR)
);

-- Delete with JOIN (MySQL)
DELETE o
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.is_active = FALSE;

-- Delete all rows (use with caution!)
DELETE FROM temp_table;

---

MERGE Operations

MERGE Syntax (SQL Server)

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column = source.column
WHEN NOT MATCHED THEN
    INSERT (column1, column2) VALUES (source.column1, source.column2)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

MERGE Example

-- Synchronize product inventory
MERGE INTO products AS target
USING product_updates AS source
ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET 
        target.price = source.price,
        target.stock = source.stock,
        target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (sku, name, price, stock, created_at)
    VALUES (source.sku, source.name, source.price, source.stock, CURRENT_TIMESTAMP)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

---

Best Practices

1. Always Use WHERE in UPDATE/DELETE

-- ❌ BAD: Updates all rows
UPDATE products SET price = 0;

-- ✅ GOOD: Updates specific rows
UPDATE products SET price = 0 WHERE product_id = 5;

2. Use Transactions for Multiple Operations

-- ✅ GOOD: Wrap related operations in transaction
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100);

COMMIT;

3. Use Prepared Statements

-- Prevents SQL injection
PREPARE stmt FROM 'SELECT * FROM customers WHERE email = ?';
SET @email = '[email protected]';
EXECUTE stmt USING @email;

4. Optimize with Indexes

-- Create index on frequently queried columns
CREATE INDEX idx_customer_email ON customers(email);
CREATE INDEX idx_order_date ON orders(order_date);

END IF;

END;

### 7. Use Bulk Operations

```sql
-- ✅ GOOD: Single INSERT for multiple rows
INSERT INTO products (name, price) VALUES
    ('Product 1', 10.00),
    ('Product 2', 20.00),
    ('Product 3', 30.00);

-- ❌ BAD: Multiple single INSERTs
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 20.00);
INSERT INTO products (name, price) VALUES ('Product 3', 30.00);

---

Summary

This guide covered:

• ✅ INSERT operations (single, multiple, from SELECT, UPSERT)
• ✅ SELECT queries (WHERE, ORDER BY, LIMIT, aggregates, joins, subqueries)
• ✅ UPDATE operations (single, multiple columns, with expressions)
• ✅ DELETE operations (with conditions, subqueries, joins)
• ✅ MERGE operations (synchronize data)
• ✅ Best practices for safe and efficient DML operations

Key Takeaways:

1. Always use WHERE clause with UPDATE/DELETE
2. Use transactions for related operations
3. Leverage bulk operations for better performance
4. Create indexes on frequently queried columns
5. Validate data before modifications
6. Use prepared statements to prevent SQL injection

---