Skip to content

SQL Commands: The 4 Pillars 🏛️

Prerequisites: SQL Syntax

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.

  1. DDL (Data Definition): The Architect. You build walls, add rooms, or demolish the garage. (Structure) 🏗️
  2. DML (Data Manipulation): The Mover. You bring in furniture, move the sofa, or throw out old trash. (Data) 📦
  3. DCL (Data Control): The Security Guard. You give keys to your family but lock the door for strangers. (Permissions) 🔑
  4. 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)."

📈 Learning Path