Every query so far has read tables that already existed — employees, departments, projects. But where did those tables come from? Somebody defined them once with a single statement: CREATE TABLE. Until you can create a table, you cannot store anything of your own.
A table is a grid of named columns, each with a type. You describe that grid in the schema, and the database holds every later row to that shape. Get the shape right and your data stays clean for the life of the application; get it wrong and you will fight every query that touches it, because redesigning a table full of real data is slow and risky.
This lesson is the first of two on schema design. Here you will create tables and choose their types; the next lesson adds the constraints — keys, uniqueness, checks — that keep bad rows out.
flowchart TD C["CREATE TABLE products"] --> L["columns:<br/>name TEXT,<br/>price INTEGER,<br/>in_stock INTEGER"] L --> R["every row must fit<br/>these three columns"] style C fill:#0e7490,color:#fff style R fill:#1e293b,color:#fff
The shape of the statement
A CREATE TABLE statement names the table, then puts a parenthesised list of columns inside it. Each column is a name followed by a type, separated by commas, and the whole thing ends with a semicolon — the same punctuation rhythm you already know from INSERT.
Column names follow the same conventions as everywhere else: lowercase with underscores, no spaces. The order you list columns in is the order they will appear in SELECT *, so lead with the obvious ones — an id, then the human-readable name — and tuck bookkeeping columns like created_on at the end.
Once the statement runs, the empty table exists and is ready for rows. Try to create a table whose name already exists and SQLite stops you with an error — protection against accidentally clobbering real data.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price INTEGER,
in_stock INTEGER
);
INSERT INTO products (id, name, price, in_stock) VALUES
(1, 'Wrench', 150, 40),
(2, 'Hammer', 320, 12);
SELECT name, price, in_stock FROM products;
The id column convention
Nearly every table in this course begins with id INTEGER PRIMARY KEY. That combination does something special in SQLite: an INTEGER PRIMARY KEY becomes an alias for the table's hidden rowid, a number the engine assigns automatically. Omit id from your INSERT and SQLite fills in the next available value, so you rarely type ids yourself.
The PRIMARY KEY half also marks the column as the table's identity — each row gets a distinct id, and the database builds a fast lookup around it. We unpack what primary keys guarantee, and how they pair with foreign keys, in the next lesson; for now treat id INTEGER PRIMARY KEY as the standard opening line of almost every CREATE TABLE you write.
Storage classes and type affinity
This is the part of SQLite that surprises people arriving from other databases. SQLite stores every value in one of five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. There is no separate DATE or BOOLEAN — a date is stored as TEXT like '2024-01-15', and a boolean is an INTEGER 0 or 1.
More surprising still is type affinity. When you declare a column INTEGER, SQLite does not force every value to be an integer — it nudges them. Insert the text '42' into an INTEGER column and SQLite quietly converts it to the number 42. Insert 'hello' and it stays text, because nothing can convert. The declared type sets an affinity SQLite applies where it can, rather than a strict rule it enforces.
flowchart LR A["DATE or TEXT"] -->|"affinity"| B["stored as TEXT"] C["BOOLEAN or INTEGER"] -->|"affinity"| D["stored as INTEGER"] E["REAL"] -->|"affinity"| F["stored as REAL"] style B fill:#0e7490,color:#fff style D fill:#0e7490,color:#fff
CREATE TABLE demo (
label TEXT,
quantity INTEGER
);
INSERT INTO demo (label, quantity) VALUES
('wrench', '42'), -- text '42' coerced to integer 42
('hammer', 7), -- already an integer
('saw', 'seven'); -- no conversion possible, kept as TEXT
SELECT label, quantity, typeof(quantity) FROM demo;
Choosing a type for each column
Types are chosen by intent, not by what happens to parse. A few habits cover almost every case. Use INTEGER for counts, identifiers, and anything you will add or compare — salaries, quantities, ages. Use REAL only when genuine fractions matter, like a rating of 4.7; for money, prefer INTEGER cents to dodge rounding errors. Use TEXT for anything free-form: names, descriptions, and dates written as YYYY-MM-DD.
Two mistakes recur. The first is storing a phone number or zip code as INTEGER — the leading zero in '07200' vanishes the moment it becomes a number. The second is reaching for REAL for currency, where 0.1 + 0.2 is not exactly 0.3. When a column feels ambiguous, ask whether you will ever do arithmetic on it: if not, TEXT is the safer home.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL,
amount_cents INTEGER NOT NULL,
placed_on TEXT,
notes TEXT
);
INSERT INTO orders (id, customer_name, amount_cents, placed_on, notes)
VALUES (1, 'Ada Nilsen', 1999, '2024-05-02', 'rush');
SELECT customer_name, amount_cents, placed_on FROM orders;
Create a table called members with three columns: an id that is INTEGER PRIMARY KEY, a name of type TEXT that cannot be empty, and an email of type TEXT.
CREATE TABLE members (
-- define the three columns here
);
Each column is
name typeon its own line, separated by commas.INTEGER PRIMARY KEYis the conventional id column.End the whole statement with a semicolon.
CREATE TABLE members (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT
);
You are adding a phone column. Phone numbers look numeric ('07200 55123') but should NOT be stored as INTEGER. Why?
Leading zeros (07200) and non-digit characters (spaces, +, #) are meaningful in a phone number but are lost or rejected when treated as a number. Store them as TEXT.
This inventory table was created with poor type choices — unit_price and quantity are both TEXT, so you can never sum them. Rewrite it so both numeric columns become INTEGER, leaving id and sku exactly as they are.
CREATE TABLE inventory (
id INTEGER PRIMARY KEY,
sku TEXT,
unit_price TEXT,
quantity TEXT
);
Only the types of
unit_priceandquantityneed to change.Columns you will sum or compare should be INTEGER.
Leave
idandskuuntouched.
CREATE TABLE inventory (
id INTEGER PRIMARY KEY,
sku TEXT,
unit_price INTEGER,
quantity INTEGER
);
You run INSERT INTO t (n) VALUES ('42') where column n was declared INTEGER. What is stored?
SQLite applies type affinity: text that parses as an integer is coerced to one under INTEGER affinity. It stays text only when it cannot be converted, like 'hello'.
Design a time_logs table for tracking work sessions. It needs an id (INTEGER PRIMARY KEY), an employee_id (INTEGER), a minutes spent (INTEGER that cannot be empty), and a logged_on date stored as TEXT. Pick types that match how each value is used.
CREATE TABLE time_logs (
-- four columns, well-typed
);
employee_idandminutesare numbers you will sum and compare — INTEGER.A date is stored as TEXT like '2024-05-02'.
minutesmatters on every row, so protect it with NOT NULL.
CREATE TABLE time_logs (
id INTEGER PRIMARY KEY,
employee_id INTEGER,
minutes INTEGER NOT NULL,
logged_on TEXT
);
Recap
CREATE TABLE name (col type, ...)defines a table's columns once; the database holds every later row to that shape.- SQLite has five storage classes —
NULL,INTEGER,REAL,TEXT,BLOB— and no nativeDATEorBOOLEAN; dates are TEXT, booleans are 0/1 INTEGERs. - Declaring a type sets an affinity that coerces where it can, so
'42'becomes 42 — a flexibility that can silently hide bad data. - Choose types by intent: numbers you sum as
INTEGER, money as integer cents, free text and dates asTEXT, and never store phone numbers or zip codes as numbers.
Next you will tighten these columns with constraints — primary keys, foreign keys, and checks that reject bad rows before they are ever stored.