SQL DDL and DML

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL
);

# Define a new table with columns and constraints.

ALTER TABLE users
ADD COLUMN last_login_at TIMESTAMP;

# Modify an existing table by adding a new column.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

# Attach a foreign key or check constraint after table creation.

DROP TABLE old_logs;

# Remove a table definition and its data.

TRUNCATE TABLE staging_events;

# Remove all rows quickly without dropping the table.

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

# Mark a column as the main row identifier.

ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

# Ensure a column or column set stays unique.

ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

# Restrict column values with a boolean rule.

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

# Add an index to improve lookup performance.

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

# Index multiple columns together.

INSERT INTO users (user_id, email, created_at)
VALUES (1, 'a@example.com', CURRENT_TIMESTAMP);

# Add a single row to a table.

INSERT INTO roles (role_id, role_name)
VALUES
  (1, 'admin'),
  (2, 'editor'),
  (3, 'viewer');

# Add several rows in one statement.

INSERT INTO active_customers (customer_id, email)
SELECT customer_id, email
FROM customers
WHERE status = "active";

# Populate a table using query results.

UPDATE users
SET status = 'inactive'
WHERE last_login_at < DATE '2024-01-01';

# Modify existing data using a WHERE clause.

DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;

# Remove rows that match a condition.

MERGE INTO inventory AS target
USING incoming_inventory AS src
ON target.sku = src.sku
WHEN MATCHED THEN UPDATE SET quantity = src.quantity
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (src.sku, src.quantity);

# Insert new rows and update existing ones in one logical operation.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

# Wrap multiple changes in one transaction.

BEGIN;
SAVEPOINT before_optional_step;
UPDATE orders SET status = "archived" WHERE order_id = 10;
ROLLBACK TO SAVEPOINT before_optional_step;
COMMIT;

# Create partial rollback points inside a transaction.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# Control how concurrent transactions interact.