Creating Tables

Everything so far has used tables that already existed. CREATE TABLE defines a new one: its name, its columns, each column's type, and the rules its data must follow. Good table design makes every later query simpler and keeps bad data out.

A first table

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  published_year INTEGER,
  price DECIMAL(6, 2)
);

INSERT INTO books (title, author, published_year, price)
VALUES ('A Quiet Harbour', 'L. Moreno', 2021, 18.50);

SELECT * FROM books;

Each column has a name and a data type, optionally followed by constraints. This example starts from an empty database (no demo tables) to keep the focus on the new table.

Naming conventions

• Use lowercase snake_case: order_items, signup_date. Unquoted names are case-insensitive, and some databases fold them to upper or lower case.
• Be consistent about plural or singular table names (this course uses plurals).
• Avoid reserved words such as order, user or group as names — they need quoting everywhere.
• Name foreign keys after what they point to: customer_id.

Default values

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'todo',
  priority INTEGER NOT NULL DEFAULT 3,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO tasks (title) VALUES ('Write the report');

SELECT id, title, status, priority FROM tasks;

A DEFAULT is used whenever an insert doesn't supply the column. Timestamps defaulting to the current time are a common pattern for auditing.

IF NOT EXISTS

CREATE TABLE IF NOT EXISTS departments (id INTEGER PRIMARY KEY, name TEXT);

SELECT COUNT(*) AS departments FROM departments;

Creating a table that already exists is an error; IF NOT EXISTS makes the statement do nothing instead. Note that it doesn't compare definitions — the existing departments table is left exactly as it was.

Creating a table from a query

CREATE TABLE product_summary AS
SELECT category, COUNT(*) AS products, round(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

SELECT * FROM product_summary;

CREATE TABLE … AS SELECT creates and fills a table in one step — handy for snapshots and reports. The new table copies column names and (roughly) types, but not keys, constraints or indexes. SQL Server writes it SELECT … INTO new_table FROM ….

Temporary tables

CREATE TEMP TABLE big_orders AS
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
HAVING total > 100;

SELECT * FROM big_orders;

Temporary tables exist only for the current connection and disappear when it closes — useful for staging intermediate results in a long piece of work. (SQL Server uses names starting with #.)

Generated columns

CREATE TABLE invoice_lines (
  id INTEGER PRIMARY KEY,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(8, 2) NOT NULL,
  line_total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

INSERT INTO invoice_lines (quantity, unit_price) VALUES (3, 4.50);

SELECT * FROM invoice_lines;

A generated column is calculated from other columns automatically and can't be set directly, so it can never be out of date. Supported by PostgreSQL, MySQL, SQLite, SQL Server (as computed columns) and Oracle (virtual columns).

Schemas and databases

Larger systems group tables into schemas (namespaces) inside a database: CREATE SCHEMA sales; then CREATE TABLE sales.invoices (…). Creating a database itself is done with CREATE DATABASE shop; in server databases such as PostgreSQL, MySQL and SQL Server; SQLite databases are simply files.

Try it yourself

Design a table for product reviews: an id, the product reviewed, the customer, a rating from 1 to 5, an optional comment and the date it was written.