CREATE TABLE: designing the menu

The sentence you have been pasting

Here it is, spread out so you can see the parts:

CREATE TABLE orders (
  item TEXT,
  qty  INTEGER,
  tbl  INTEGER
);

Read it left to right:

• CREATE TABLE orders says: make me a new table called orders.
• The parentheses hold the column list, one column per line here.
• Each column is a pair: a name (item) and a type (TEXT).
• Commas between columns, a semicolon at the end. That is the whole grammar.

Notice what is missing: data. CREATE TABLE writes no rows. It only draws the shape that rows will have to fit:

You run it once. From then on, INSERT fills the shape and SELECT reads from it, for as long as the table lives.

Types: what kind of fact goes in each column

A type tells the database what kind of value a column holds. Three cover almost everything at this level:

• TEXT for words: 'maki', 'window seat', a customer’s name.
• INTEGER for whole numbers: 2 plates, table 5, 130 seats.
• REAL for numbers with a decimal point: a price of 4.50, a weight of 0.2.

Dates are the odd one out: they are very often stored as text in a fixed pattern, like '2026-08-07', which happens to sort correctly all by itself. And one honest sentence about SQLite, the database you have been practicing in: it is famously relaxed about types and will often accept a value that does not match the column. Declare your types anyway. They are documentation for future you, and the bigger databases enforce them for real.

House rules: NOT NULL and DEFAULT

The column list can carry rules, not just types. The two you will use constantly:

CREATE TABLE orders (
  item TEXT NOT NULL,
  qty  INTEGER NOT NULL DEFAULT 1,
  tbl  INTEGER
);

NOT NULL means: this fact is required, refuse any row that arrives without it. You met the empty plate in lesson 06 and saw how much trouble it causes once it is inside your data. NOT NULL is the bouncer version of that lesson: stop nulls at the door instead of hunting them in every query afterwards.

DEFAULT is the house answer. If a new row does not bring a value for qty, the database quietly writes 1. Nobody orders zero plates, so one is a sensible default. No default and no NOT NULL on tbl means the column may stay empty, which is fine: takeaway orders have no table.

Naming your columns

Names are design too. A column called c7 works exactly as well as one called price, right up until a human reads the query. Three habits:

• Say what the fact is: item, price, ordered_at. Lowercase, short, no mystery.
• Stay consistent across tables: if one table says qty, do not say amount in the next.
• Dodge the words the database keeps for itself. This is the reason our table column has been called tbl since lesson 02: table is a reserved word, and naming a column after it is asking for a fight.

Try this yourself

Open your playground and paste the old faithful, but read it this time:

CREATE TABLE orders (item TEXT, qty INTEGER, tbl INTEGER);
INSERT INTO orders VALUES ('maki', 2, 5);
SELECT * FROM orders;

Every word in that first line now means something to you. That is the win.

Now design a table of your own. Here is a starter, then make it yours:

CREATE TABLE menu (
  dish  TEXT NOT NULL,
  price REAL NOT NULL,
  spicy INTEGER DEFAULT 0
);

INSERT INTO menu (dish, price) VALUES ('tamago', 3.50);
SELECT * FROM menu;

Look at the result: you never mentioned spicy, yet the row has a 0 in it. That is DEFAULT doing its quiet job. Then try inserting a dish with no price and watch NOT NULL bounce it. Finally, sketch a tiny table for something from your own life, three or four columns with sensible types: books you have read, games you own, plants you keep alive. That sketch is database design. You are doing it already.