9. CREATE TABLE

So far you've queried tables someone else built. Now you'll build your own. CREATE TABLE defines a new table: its name, its columns, and each column's type.

The basic shape

CREATE TABLE product (
  id     INTEGER,
  name   VARCHAR,
  price  DECIMAL(6,2)
);

That's it — an empty table with three typed columns. Put data in with INSERT (a whole module comes next), then read it back:

CREATE TABLE product (
  id     INTEGER,
  name   VARCHAR,
  price  DECIMAL(6,2)
);

INSERT INTO product (id, name, price) VALUES
  (1, 'Notebook', 4.50),
  (2, 'Pen',      1.25),
  (3, 'Backpack', 39.00);

SELECT * FROM product ORDER BY price;

Run it. Every run starts from a clean database, so you can create product again and again without clashes.

Removing a table

DROP TABLE deletes the table and all its data — permanently. Add IF EXISTS so it doesn't error when the table isn't there:

DROP TABLE IF EXISTS product;

A common pattern when experimenting is DROP TABLE IF EXISTS x; right before CREATE TABLE x — a clean slate every time.

Naming and rules

• Table and column names are lowercase by convention, words joined with underscores: order_item, not OrderItem.
• Every column needs a type. Choose the tightest one that fits (last lesson) — it's your first line of defense against bad data.

Try it

Create a table city_pop with columns name (VARCHAR) and population (INTEGER). Insert two rows — ('Oslo', 700000) and ('Lima', 8900000) — then select both, ordered by population.

-- create the table, insert two rows, then select ordered by population

CREATE TABLE city_pop (
  name       VARCHAR,
  population INTEGER
);
INSERT INTO city_pop (name, population) VALUES
  ('Oslo', 700000),
  ('Lima', 8900000);
SELECT * FROM city_pop ORDER BY population;