SQL Data Types

A data type is the kind of value a column is built to hold. Dates in this database are stored as text in YYYY-MM-DD shape.

Whole numbers in INTEGER

This block builds a small table with an INTEGER column. The stored whole number reads back unchanged.

CREATE TABLE counters (
  counter_id INTEGER,
  total INTEGER
);
INSERT INTO counters VALUES (1, 42);
SELECT counter_id, total
FROM counters;

Decimal numbers in REAL

This block builds a small table with a REAL column. The stored decimal number reads back unchanged.

CREATE TABLE amounts (
  amount_id INTEGER,
  amount REAL
);
INSERT INTO amounts VALUES (1, 9.99);
SELECT amount_id, amount
FROM amounts;

Text and a date in TEXT

This block builds a small table with two TEXT columns. The date value is text in YYYY-MM-DD shape.

CREATE TABLE events (
  event_id INTEGER,
  event_name TEXT,
  event_date TEXT
);
INSERT INTO events VALUES (1, 'Sale starts', '2024-04-01');
SELECT event_id, event_name, event_date
FROM events;

Example

CREATE TABLE deliveries (
  delivery_id INTEGER,
  item TEXT,
  quantity INTEGER,
  cost REAL,
  delivery_date TEXT
);
INSERT INTO deliveries VALUES (1, 'Backpack', 10, 399.9, '2024-04-02');
INSERT INTO deliveries VALUES (2, 'Coffee Mug', 3, 26.25, '2024-04-05');
SELECT delivery_id, item, quantity, cost, delivery_date
FROM deliveries
ORDER BY delivery_id;

Key points

INTEGER: a type for whole numbers, like 42
REAL: a type for decimal numbers, like 9.99
TEXT: a type for text values, like a name
A date value is stored as TEXT in YYYY-MM-DD shape.

In this example, CREATE TABLE builds deliveries with one column per type. The id and quantity columns hold whole numbers. The item and delivery_date columns hold text, and each date uses the YYYY-MM-DD shape. The cost column holds decimal numbers. The select reads both stored rows back.

Note

SQLite accepts a value of any type in any column. Most engines refuse a value of the wrong type.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

CREATE TABLE deliveries (
  delivery_id INTEGER,
  item TEXT,
  quantity INTEGER,
  cost REAL,
  delivery_date TEXT
);
INSERT INTO deliveries VALUES (1, 'Backpack', 10, 399.9, '2024-04-02');
INSERT INTO deliveries VALUES (2, 'Coffee Mug', 3, 26.25, '2024-04-05');
SELECT delivery_id, item, quantity, cost, delivery_date
FROM deliveries
ORDER BY delivery_id;

Exercise

Which type is built for whole numbers?

INTEGER REAL TEXT YYYY-MM-DD