Skip to content

SQL Data Types 🏗️

Prerequisites: Introduction to SQL

Mentor's Note: Choosing a data type is like picking a container. You don't put soup in a sieve, and you don't put a 10-digit phone number in a 2-digit integer. Choosing the right "box" saves space and prevents errors! 💡

🌟 The Scenario: The Storage Unit 📦

Imagine you are packing for a move.

  • Small Box: For your keys (Short String/ID).
  • Large Box: For your books (Long Text).
  • Heavy Duty Box: For your weights (Big Numbers).
  • Specialized Case: For your watch (Date & Time).

In SQL, every column needs a specific "box type" to hold its data.

🎨 Visual Logic: The Type Categories

mindmap
  root((SQL Data Types))
    Numeric
      INT/NUMBER
      DECIMAL/FLOAT
    String
      CHAR (Fixed)
      VARCHAR (Variable)
      CLOB (Large Text)
    Date-Time
      DATE
      TIMESTAMP
    Other
      BOOLEAN
      BLOB (Images)

💻 Common Data Types (Multi-Dialect)

1. String Types (The Text Boxes)

Type Use Case Scenario
CHAR(n) Fixed length "IND", "USA" (Country Codes) 🏳️
VARCHAR2(n) Variable length Name, Address (Saves space!) 🏠
CLOB Very long text Product Reviews, Biographies 📖

2. Numeric Types (The Calculators)

Type Use Case Scenario
INT / NUMBER Whole numbers Student Roll No, Quantity 🔢
DECIMAL(p,s) Precise decimals Salary (₹55000.50), Product Price 💰
FLOAT Scientific decimals Weight, Distance 📐

3. Date & Time Types (The Calendars)

Type Use Case Scenario
DATE Date only Birthday, Joined Date 📅
TIMESTAMP Date + Time Exact Login Time ⏱️

📊 Comparison: CHAR vs VARCHAR2

Scenario: Storing the word "CAT" (3 letters) in a column of size 10.

Feature CHAR(10) VARCHAR2(10)
Storage Uses 10 bytes (Adds 7 spaces) Uses 3 bytes (Saves 7 bytes!)
Performance Faster for fixed data Slightly slower, but efficient
Best For Status Codes (Y/N) Names, Descriptions

💡 Pro Tip

"Always use VARCHAR2 for names and addresses. Using CHAR for a Name field will waste gigabytes of storage over time because of trailing spaces!"

📈 Learning Path