Data Types

Every column of a STRICT table has one of five types:

• INTEGER (or INT) – whole numbers: prices in whole dollars, quantities, IDs, years.
• REAL – decimal numbers, such as 4.25 or 1.5.
• TEXT – text of any length: names, emails, comments.
• BLOB – raw bytes, such as a picture or another file, kept as they are. You'll rarely need it.
• ANY – any value, as in a table without STRICT. Use it only when a column really has to hold values of different kinds.

SQLite has no type for dates. A date is kept as TEXT in the form 'YYYY-MM-DD', like in the shop's columns released and ordered_at. You know why from the earlier lessons: dates in this form sort and compare correctly, and functions such as strftime() and date() understand them. A date with a time is written as '2025-11-28 14:30:00'.

There's no type for true and false either. A yes-or-no column is an INTEGER with 1 for yes and 0 for no, and a query asks for WHERE used = 1 or WHERE used = 0. The shop could keep its gift cards like this:

CREATE TABLE gift_cards (
    code TEXT,
    amount INTEGER,
    sold TEXT,
    used INTEGER
) STRICT;

INSERT INTO gift_cards (code, amount, sold, used)
VALUES ('GIFT-7Q2X', 25, '2025-12-01', 1),
       ('GIFT-K9LM', 50, '2025-12-18', 0),
       ('GIFT-3RTP', 25, '2025-12-21', 0);

SELECT code, amount, sold
FROM gift_cards
WHERE used = 0;

Why is amount an INTEGER and not a REAL? Gift cards, like the shop's prices, are in whole dollars. And when a shop needs cents, it often keeps them as whole numbers too: $25.99 is stored as 2599 cents. Decimal numbers in a computer aren't exact (0.1 + 0.2 is a tiny bit more than 0.3), and with money, every cent counts. REAL is right for values that are measured rather than counted: an average rating, hours played, a weight. A REAL column turns a whole number into a decimal one, so 3 comes back as 3.0.

Other databases have more types, and you'll meet them in other courses and at work:

• VARCHAR(n) – text of at most n characters, such as VARCHAR(100) for a name.
• DATE, TIME, TIMESTAMP – dates and times.
• BOOLEAN – TRUE or FALSE.
• DECIMAL(p, s) – exact decimal numbers, such as DECIMAL(10, 2) for amounts with cents.

A STRICT table in SQLite takes only the five types above (and INT); any other type is an error:

CREATE TABLE gift_cards (
    code VARCHAR(20),
    amount INTEGER
) STRICT;

Error on line 1: unknown datatype for gift_cards.code: "VARCHAR(20)"

So in SQLite, write TEXT for text, dates and times, INTEGER for whole numbers and yes-or-no values, and REAL for decimal numbers.

Typical mistake: a date in another form, such as '11/28/2025'. It's a text, so the database stores it without an error, but it doesn't sort as a date ('11/28/2025' comes before '12/01/2024'), and the date functions give NULL for it. Always write dates as 'YYYY-MM-DD'.