SQL Data Types: The Building Blocks of Your Database

The Story of the Magic Filing Cabinet

Imagine you have a magic filing cabinet that can store anything—numbers, words, dates, even pictures! But here’s the trick: each drawer only accepts ONE type of thing. Put a word in the numbers drawer? The cabinet gets confused!

SQL data types are like labels on those drawers. They tell the database exactly what kind of information goes where. Get it right, and your data stays organized forever.

🔢 Numeric Data Types: The Math Drawer

Think of this drawer as your calculator’s memory. It holds numbers you can add, subtract, multiply, and divide.

Whole Numbers (Integers)

These are counting numbers—no fractions, no decimals. Like counting apples!

Example:

age INT,
student_count SMALLINT,
world_population BIGINT

Why different sizes? Like using a shoebox vs. a shipping container—use what fits! Smaller = faster + less storage.

Decimal Numbers

For money, measurements, and precise calculations.

Example:

price DECIMAL(10,2),  -- $12345678.99
temperature FLOAT     -- 98.6°F

The Secret: DECIMAL(10,2) means 10 total digits, 2 after the dot. Like 12345678.99!

📝 Character Data Types: The Word Drawer

This drawer stores text—names, emails, stories, anything you can type!

Fixed-Length Text: CHAR

Like a form with boxes. Every box MUST be filled (with spaces if needed).

country_code CHAR(2)  -- 'US', 'UK', 'IN'

Best for: Codes that never change length.

Variable-Length Text: VARCHAR

Like a stretchy rubber band. Uses only what it needs!

first_name VARCHAR(50),
email VARCHAR(100)

Best for: Names, emails—anything with different lengths.

Big Text: TEXT/CLOB

For storing entire essays, articles, or books!

blog_post TEXT,
novel CLOB

Quick Comparison

📅 Date and Time: The Calendar Drawer

This drawer tracks when things happen—like a super-smart calendar!

DATE: Just the Day

Stores year, month, and day. No clock needed!

birthday DATE,  -- '2010-05-15'
hire_date DATE  -- '2024-01-20'

TIME: Just the Clock

Hours, minutes, seconds—no calendar.

alarm_time TIME,  -- '07:30:00'
lunch_break TIME  -- '12:00:00'

DATETIME / TIMESTAMP: Calendar + Clock

The complete package! When + What time.

order_placed DATETIME,
-- '2024-12-01 14:30:00'

last_login TIMESTAMP
-- Updates automatically!

The Magic Difference

✅ Boolean: The Yes/No Drawer

The simplest drawer! Only TWO things fit: TRUE or FALSE.

Like a light switch—ON or OFF. No “maybe”!

is_active BOOLEAN,
email_verified BOOLEAN,
has_subscription BOOLEAN

Real Example:

CREATE TABLE users (
  is_student BOOLEAN DEFAULT TRUE,
  is_premium BOOLEAN DEFAULT FALSE
);

Fun Fact: Some databases use 1 for TRUE and 0 for FALSE. Same idea!

📦 Binary and LOB: The Storage Room

For BIG stuff that isn’t text—images, videos, files!

BLOB: Binary Large Object

Stores raw data—like a mystery box.

profile_photo BLOB,
pdf_document BLOB,
music_file BLOB

Different Sizes

Think of it like:

• TINYBLOB = Thumbnail image
• BLOB = Regular photo
• MEDIUMBLOB = Short video
• LONGBLOB = Full movie

❓ NULL: The Empty Box

Here’s where it gets interesting. What if a drawer has… NOTHING?

NULL is NOT Zero!

NULL ≠ 0
NULL ≠ ""
NULL ≠ FALSE

NULL means “I DON’T KNOW”

The Three-Valued Logic

In SQL, there are THREE answers, not two!

The Tricky Part

-- If age is NULL:
age = 25    → UNKNOWN
age != 25   → UNKNOWN
age > 18    → UNKNOWN

Why? You can’t compare “I don’t know” to anything!

How to Find NULL

-- WRONG! Never works:
WHERE age = NULL

-- RIGHT! This works:
WHERE age IS NULL
WHERE age IS NOT NULL

NULL in Calculations

The Rule: Anything mixed with NULL becomes NULL (usually)!

🎯 Putting It All Together

Here’s a real table using ALL the data types:

CREATE TABLE students (
  -- Numbers
  id INT,
  grade DECIMAL(3,2),

  -- Text
  name VARCHAR(100),
  country CHAR(2),
  bio TEXT,

  -- Dates
  birthday DATE,
  enrolled_at TIMESTAMP,

  -- Boolean
  is_active BOOLEAN,

  -- Binary
  photo BLOB
);

🧠 Quick Memory Tricks

graph TD
    A[What are you storing?] --> B{Numbers?}
    B -->|Whole| C[INT family]
    B -->|Decimal| D[DECIMAL/FLOAT]
    A --> E{Text?}
    E -->|Short fixed| F[CHAR]
    E -->|Short variable| G[VARCHAR]
    E -->|Long| H[TEXT]
    A --> I{Date/Time?}
    I -->|Just date| J[DATE]
    I -->|Just time| K[TIME]
    I -->|Both| L[DATETIME]
    A --> M{Yes/No?}
    M --> N[BOOLEAN]
    A --> O{Files?}
    O --> P[BLOB]

🌟 Remember This!

1. Pick the RIGHT type = Faster queries + Less storage
2. NULL is special = It means “unknown”, not “empty”
3. Text choices matter = CHAR for fixed, VARCHAR for variable
4. Dates have friends = DATE, TIME, DATETIME, TIMESTAMP
5. BLOB is for bytes = Images, files, binary data

You’re now ready to create well-organized databases! Every piece of data has a home, and every home has the right label.

Your magic filing cabinet is ready! 🎉