Two kinds of SQL command

SQL has two halves:

• DDL (Data Definition Language) changes the structure — the tables themselves.
• DML (Data Manipulation Language) works with the data inside the tables — reading it with SELECT, and adding, changing, or removing it with INSERT/UPDATE/DELETE. Everything so far (SELECT) is DML.

CREATE TABLE is the main DDL command. It lists each column with a data type and marks the primary key:

CREATE TABLE customer (
  id   INTEGER PRIMARY KEY,
  name VARCHAR(20),
  city VARCHAR(15)
);

Data types and foreign keys

Common data types:

• INTEGER — whole numbers
• VARCHAR(n) / CHAR(n) — text, up to / exactly n characters
• DECIMAL(p, s) — a number with s decimal places
• DATE, BOOLEAN

To link to another table, add a FOREIGN KEY:

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER,
  FOREIGN KEY (customer_id) REFERENCES customer(id)
);

The checker reads back your table's structure to see which columns you created.

Common mistakes

• Every column needs a data type (e.g. INTEGER, TEXT).
• Declare the PRIMARY KEY when you create the table.