Creating Tables

CREATE TABLE table_name (
    column_name data_type [column_constraints] [DEFAULT value],
    ...,
    [table_constraints]
);

-- Minimal example
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255)   NOT NULL,
    price       DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
    description TEXT,
    created_at  TIMESTAMP      NOT NULL DEFAULT NOW()
);

-- With table-level constraints
CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    qty        INTEGER NOT NULL CHECK (qty > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

id SMALLINT,          -- 2 bytes, -32768 to 32767
id INTEGER,           -- 4 bytes, ~±2.1 billion
id BIGINT,            -- 8 bytes, ~±9.2 × 10^18
id SERIAL,            -- auto-increment INTEGER (PostgreSQL)
id BIGSERIAL,         -- auto-increment BIGINT (PostgreSQL)
id INTEGER GENERATED ALWAYS AS IDENTITY,    -- SQL standard auto-increment
id INTEGER GENERATED BY DEFAULT AS IDENTITY -- allows manual override

price DECIMAL(10, 2),   -- exact: 10 total digits, 2 after decimal
amount NUMERIC(15, 4),  -- same as DECIMAL

ratio REAL,             -- 4-byte float (~6 decimal digits)
score DOUBLE PRECISION, -- 8-byte float (~15 decimal digits)
value FLOAT(24),        -- at least 24 bits of mantissa

code   CHAR(5),         -- fixed-length, padded with spaces
name   VARCHAR(255),    -- variable-length, max 255 chars
bio    TEXT,            -- unlimited length
notes  NVARCHAR(200),   -- Unicode variable string (SQL Server)

dob        DATE,                         -- '2024-06-15'
start_time TIME,                         -- '14:30:00'
created_at TIMESTAMP,                    -- '2024-06-15 14:30:00' (no tz)
updated_at TIMESTAMPTZ,                  -- with time zone (PostgreSQL)
updated_at TIMESTAMP WITH TIME ZONE,     -- SQL standard
duration   INTERVAL,                     -- '3 days 4 hours' (PostgreSQL)

active BOOLEAN DEFAULT TRUE,  -- TRUE / FALSE / NULL
-- MySQL uses TINYINT(1); SQLite uses INTEGER 0/1

-- UUID
id UUID DEFAULT gen_random_uuid(),    -- PostgreSQL 13+
id UUID DEFAULT uuid_generate_v4(),   -- with uuid-ossp extension

-- JSON
metadata JSON,           -- stored as text
metadata JSONB,          -- binary JSON, indexed (PostgreSQL)

-- Arrays (PostgreSQL)
tags TEXT[],
scores INTEGER[],

-- Enum
status VARCHAR(20) CHECK (status IN ('pending','active','cancelled')),
-- Or MySQL ENUM type:
status ENUM('pending','active','cancelled'),
-- Or PostgreSQL custom type:
CREATE TYPE order_status AS ENUM ('pending', 'active', 'cancelled');
-- then:
status order_status,

-- PostgreSQL (SERIAL shorthand)
id SERIAL PRIMARY KEY,
id BIGSERIAL PRIMARY KEY,

-- PostgreSQL (SQL:2003 IDENTITY)
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
id BIGINT  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY,
id BIGINT AUTO_INCREMENT PRIMARY KEY,

-- SQLite
id INTEGER PRIMARY KEY AUTOINCREMENT,
-- (without AUTOINCREMENT, SQLite reuses deleted max+1 by default)

-- SQL Server
id INT IDENTITY(1,1) PRIMARY KEY,   -- starts at 1, increments by 1
id INT IDENTITY(100,5) PRIMARY KEY, -- starts at 100, increments by 5

created_at  TIMESTAMP   DEFAULT NOW(),
created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
status      VARCHAR(20) DEFAULT 'pending',
score       INTEGER     DEFAULT 0,
is_active   BOOLEAN     DEFAULT TRUE,
uuid_col    UUID        DEFAULT gen_random_uuid(),

-- Computed default (PostgreSQL generated column)
full_name   TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,

-- SQL Server computed column
full_name   AS (first_name + ' ' + last_name) PERSISTED,

CREATE TABLE IF NOT EXISTS users (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
-- No error if the table already exists; schema is NOT updated

-- Drop if exists
DROP TABLE IF EXISTS temp_results;

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN verified BOOLEAN NOT NULL DEFAULT FALSE;

-- Remove a column
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- Rename a column
ALTER TABLE users RENAME COLUMN username TO login_name;

-- Change column type (PostgreSQL)
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 4);

-- Change column type (MySQL)
ALTER TABLE products MODIFY COLUMN price DECIMAL(12, 4) NOT NULL;

-- Set / drop default
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Set / drop NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- Rename table
ALTER TABLE orders RENAME TO purchase_orders;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0);

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT uq_email;
ALTER TABLE users DROP CONSTRAINT IF EXISTS uq_email;

-- Drop table (error if it doesn't exist)
DROP TABLE products;

-- Drop table if it exists
DROP TABLE IF EXISTS products;

-- Drop multiple tables
DROP TABLE IF EXISTS order_items, orders, products;

-- Drop with CASCADE (also drops dependent views, FKs, etc.)
DROP TABLE users CASCADE;

-- DROP TABLE vs TRUNCATE vs DELETE:
-- DROP TABLE: removes the table and all data permanently
-- TRUNCATE: removes all rows, keeps the table structure
-- DELETE: removes rows, can be filtered, is transactional

-- Creates table with columns inferred from query (no constraints/indexes)
CREATE TABLE top_customers AS
SELECT user_id, SUM(total) AS lifetime_value
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING SUM(total) > 1000;

-- PostgreSQL: WITH NO DATA (creates structure only)
CREATE TABLE orders_backup AS
SELECT * FROM orders WITH NO DATA;

-- Then populate later:
INSERT INTO orders_backup SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2023;

-- MySQL equivalent
CREATE TABLE orders_archive AS SELECT * FROM orders WHERE 1 = 0;  -- empty

-- Session-scoped (most DBs)
CREATE TEMPORARY TABLE temp_results (
    id      INTEGER,
    score   DECIMAL(5,2)
);

-- PostgreSQL: also ON COMMIT DROP / DELETE ROWS / PRESERVE ROWS
CREATE TEMP TABLE batch_staging (
    raw_data TEXT
) ON COMMIT DROP;           -- auto-dropped at end of transaction

-- MySQL: same syntax as above
-- SQL Server: # prefix for local, ## for global temp tables
CREATE TABLE #temp_results (id INT, score FLOAT);
SELECT * FROM #temp_results;

-- PostgreSQL table partitioning (declarative, Postgres 10+)
CREATE TABLE orders (
    id         BIGSERIAL,
    created_at DATE NOT NULL,
    user_id    INTEGER,
    total      DECIMAL(12,2)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- List partitioning
CREATE TABLE customers (
    id     SERIAL,
    region TEXT NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE customers_us PARTITION OF customers FOR VALUES IN ('US', 'CA');
CREATE TABLE customers_eu PARTITION OF customers FOR VALUES IN ('UK', 'DE', 'FR');