SQL Commands: The 4 Pillars 🏛️¶

Mentor's Note: SQL isn't just one thing. It's a collection of sub-languages. Knowing the difference between "Building the House" (DDL) and "Moving Furniture In" (DML) is the first step to database mastery. 💡

🌟 The Scenario: Building a House 🏠¶

Think of a database like constructing and living in a house.

DDL (Data Definition): The Architect. You build walls, add rooms, or demolish the garage. (Structure) 🏗️
DML (Data Manipulation): The Mover. You bring in furniture, move the sofa, or throw out old trash. (Data) 📦
DCL (Data Control): The Security Guard. You give keys to your family but lock the door for strangers. (Permissions) 🔑
TCL (Transaction Control): The Insurance. You verify everything arrived safely before signing the receipt. (Save/Undo) 📝

🎨 Visual Logic: The Command Hierarchy¶

mindmap
  root((SQL Commands))
    DDL (Structure)
      CREATE
      ALTER
      DROP
      TRUNCATE
    DML (Data)
      SELECT
      INSERT
      UPDATE
      DELETE
    DCL (Access)
      GRANT
      REVOKE
    TCL (Save/Undo)
      COMMIT
      ROLLBACK
      SAVEPOINT

💻 1. DDL: Data Definition Language 🏗️¶

Commands that define the database schema.

Command	Action	Analogy
CREATE	Creates a new table/db	Building a new room
ALTER	Modifies table structure	Painting the walls
DROP	Deletes table structure	Demolishing the room
TRUNCATE	Wipes all data, keeps structure	Emptying the room completely

-- DDL Example
CREATE TABLE users (id INT, name VARCHAR(50));

💻 2. DML: Data Manipulation Language 📦¶

Commands that manipulate the data within schema objects.

Command	Action	Analogy
SELECT	Retrieves data	Looking for your keys
INSERT	Adds new data	Buying a new chair
UPDATE	Modifies existing data	Reupholstering the sofa
DELETE	Removes specific data	Throwing away trash

-- DML Example
INSERT INTO users VALUES (1, 'Vishnu');

💻 3. DCL: Data Control Language 🔑¶

Commands that deal with rights, permissions, and other controls.

Command	Action	Analogy
GRANT	Gives access privileges	Giving a spare key
REVOKE	Withdraws access privileges	Changing the locks

-- DCL Example
GRANT SELECT ON users TO intern_user;

💻 4. TCL: Transaction Control Language 📝¶

Commands that deal with the transaction of the database.

Command	Action	Analogy
COMMIT	Saves work permanently	Signing the contract
ROLLBACK	Restores to last commit	Ctrl+Z (Undo)
SAVEPOINT	Sets a checkpoint	Bookmark in a book

-- TCL Example
UPDATE users SET name = 'VD';
COMMIT; -- Changes are now permanent

💡 Interview Tip 👔¶

"The most common interview question: What is the difference between DELETE and TRUNCATE?

DELETE is DML. It can be rolled back. It scans row by row (Slower).

TRUNCATE is DDL. It cannot be rolled back (in Oracle). It resets the table instantly (Faster)."