SQL data types

Columns have types that constrain stored values and affect sorting, storage, and index behavior. ANSI SQL defines common families; each engine adds aliases (e.g., TEXT vs VARCHAR).

Common scalar types

• INTEGER / BIGINT — whole numbers, often for keys and counts
• REAL / DOUBLE / DECIMAL — floating or fixed-precision numbers (money often uses DECIMAL)
• TEXT / VARCHAR — strings of varying length
• DATE, TIME, TIMESTAMP — calendar and clock values (timezone rules vary by engine)
• BOOLEAN — TRUE/FALSE (SQLite uses 0/1)

CREATE with types

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  starts_at TEXT,
  is_public INTEGER DEFAULT 1,
  ticket_price REAL
);

Practice: Run in SQLite, DB Fiddle, or Postgres. Create the customers/orders sample from the relational-model lesson if needed.

Type casting

SELECT CAST('42' AS INTEGER) AS n;

SELECT total,
       CAST(total AS INTEGER) AS total_int
FROM orders;

CAST converts values for comparisons and exports—watch precision loss on decimals.

Choosing types

Pick the smallest type that fits domain rules. Store timestamps in UTC at the application or database layer. Avoid storing money as binary floats when DECIMAL is available.

Important interview questions and answers

1. Q: TEXT vs VARCHAR?
   A: Often interchangeable in practice; VARCHAR may enforce max length depending on engine.
2. Q: Why DECIMAL for currency?
   A: Avoids floating-point rounding errors in financial calculations.

Self-check

1. Which type is appropriate for a product price with cents?
2. What does NOT NULL on a column mean?

Tip: Store money as DECIMAL where available; avoid REAL for currency in Postgres/MySQL.