Skip to content

Data Manipulation Language (DML) 🚀

Prerequisites: Data Definition Language (DDL)

Mentor's Note: If DDL is building the house, DML is moving the furniture in, changing the sofa, or throwing out old trash. It's how you manage the actual Information! 💡

🌟 The Scenario: The Warehouse Manager 📦

Imagine you manage a grocery warehouse.

  • INSERT: A truck arrives with 50 crates of Apples 🍎. You add them to your inventory record.
  • UPDATE: The price of Milk 🥛 goes up. You change the label on the shelf.
  • DELETE: A box of Bananas 🍌 is rotten. You remove it from the records.
  • The Result: Your inventory stays current and accurate. ✅

📖 Key DML Commands

1. INSERT

Used to add new rows to a table. 

INSERT INTO students (id, name, age) 
VALUES (101, 'Vishnu', 25);

2. UPDATE

Used to modify existing records. Always use a WHERE clause or you will update every row! ⚠️ 

UPDATE students 
SET age = 26 
WHERE id = 101;

3. DELETE

Used to remove specific rows. 

DELETE FROM students 
WHERE id = 101;

🎨 Visual Logic: The DML Impact

graph TD
    A[Empty Table 📋] -- INSERT --> B[Table with Data 🍎]
    B -- UPDATE --> C[Corrected Data 💎]
    C -- DELETE --> D[Specific Row Removed 🗑️]

💻 Implementation: The Inventory Lab

-- 🛒 Scenario: Stocking a new product
-- 🚀 Action: Inserting a laptop record

INSERT INTO products (pid, pname, stock)
VALUES (5001, 'Dell XPS', 10);

-- 🛍️ Outcome: A new row appears in the table.

-- 🛒 Scenario: Flash Sale!
-- 🚀 Action: Discounting all laptops

UPDATE products
SET price = price * 0.9
WHERE pname LIKE '%Laptop%';

📊 Sample Dry Run (DELETE)

Command: DELETE FROM users WHERE status = 'Inactive'

ID Name Status Action
1 Vishnu Active KEEP ✅
2 John Inactive DELETE 🗑️
3 Sara Active KEEP ✅

📈 Technical Analysis

  • Transaction Control: In Oracle, DML changes are not permanent until you run the COMMIT command. If you make a mistake, you can undo it using ROLLBACK. 🛡️

🎯 Practice Lab 🧪

Task: Update the Grades

Task: You have a GRADES table. Student 105 was given B by mistake. Change it to A. Then, delete all records where the student has Left the school. Hint: Use UPDATE and DELETE with specific WHERE clauses. 💡

💡 Interview Tip 👔

"Interviewers love the 'Where Clause' question. What happens if you run UPDATE employees SET salary = 90000 without a WHERE clause? Answer: Everyone in the company gets the same salary! 😱 Always double-check your WHERE clause."

💡 Pro Tip: "The only thing constant is change." - Heraclitus

← Back: DDL | Next: PL/SQL Foundations →