CREATE TABLE

CREATE TABLE defines a new relation: column names, types, defaults, and table-level options. Migrations in Django and other frameworks emit equivalent DDL.

Basic table

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price REAL NOT NULL DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);

Practice: Run DDL in a fresh SQLite file or DB Fiddle schema pane. Drop test tables when experimenting.

ALTER TABLE

ALTER TABLE products ADD COLUMN stock INTEGER DEFAULT 0;

-- SQLite limited ALTER; Postgres/MySQL support more renames/types
-- See /postgresql/intro and /mysql/intro for dialect details

Production schema changes go through reviewed migrations, not ad-hoc ALTER on live databases.

DROP and IF NOT EXISTS

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

DROP TABLE IF EXISTS old_products;

IF NOT EXISTS / IF EXISTS make scripts rerunnable in dev—still use caution in production.

Important interview questions and answers

1. Q: CREATE vs migration tool?
   A: CREATE is raw DDL; migration tools version and apply DDL changes safely across environments.
2. Q: DEFAULT clause purpose?
   A: Supplies a value when INSERT omits the column.

Self-check

1. Which clause makes a column mandatory on INSERT?
2. What does PRIMARY KEY imply about uniqueness?

Tip: Version schema changes through migrations—see Django migrations lesson later.