SQL Statements

Data Definition Language (DDL)

CREATE TABLE

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email TEXT,
    balance DOUBLE PRECISION DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Supported constraints: PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK

CREATE TABLE AS SELECT

CREATE TABLE active_users AS
SELECT id, username, balance
FROM users
WHERE balance > 0;

DROP TABLE

DROP TABLE users;
DROP TABLE IF EXISTS users;

TRUNCATE

TRUNCATE TABLE orders;

O(1) operation - drops all segments instantly.

CREATE INDEX

ScramDB supports three index types:

-- ART index (default, best for point lookups)
CREATE INDEX idx_users_email ON users (email);

-- B+Tree index (best for range queries)
CREATE INDEX idx_orders_date ON orders USING btree (order_date);

-- Hash index (best for equality checks)
CREATE INDEX idx_products_sku ON products USING hash (sku);

Index features:

• Composite indexes: CREATE INDEX idx ON t (a, b, c) - prefix matching on left-to-right columns
• Index-only scans when all projected columns are in the index
• Cost-based selection - optimizer chooses index vs. full scan automatically

DROP INDEX

DROP INDEX idx_users_email;

ALTER TABLE

ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME TO customers;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

CREATE / DROP VIEW

CREATE VIEW high_value_orders AS
SELECT * FROM orders WHERE amount > 10000;

DROP VIEW high_value_orders;

Views are expanded inline at query time.

---

Data Manipulation Language (DML)

SELECT

-- Basic
SELECT id, name, price FROM products WHERE price > 100;

-- Joins
SELECT o.id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.amount > 500;

-- Aggregation
SELECT department, AVG(salary), COUNT(*)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Window functions
SELECT name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;

-- CTEs (WITH clause)
WITH top_customers AS (
    SELECT customer_id, SUM(amount) as total
    FROM orders
    GROUP BY customer_id
    ORDER BY total DESC
    LIMIT 10
)
SELECT c.name, t.total
FROM top_customers t
JOIN customers c ON t.customer_id = c.id;

-- Subqueries
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'US'
);

-- Set operations
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

-- GROUPING SETS
SELECT brand, category, SUM(sales)
FROM products
GROUP BY GROUPING SETS ((brand), (category), (brand, category), ());

INSERT

-- Single row
INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'alice@example.com');

-- Multiple rows
INSERT INTO users (id, username, email)
VALUES (2, 'bob', 'bob@example.com'),
       (3, 'charlie', 'charlie@example.com');

-- Insert from select
INSERT INTO archive SELECT * FROM orders WHERE created_at < '2024-01-01';

UPDATE

UPDATE products
SET price = price * 1.1
WHERE category = 'electronics';

DELETE

DELETE FROM orders WHERE status = 'cancelled';

COPY (Bulk Import/Export)

-- Import CSV
COPY orders FROM '/path/to/orders.csv' (FORMAT CSV, HEADER);

-- Export CSV
COPY (SELECT * FROM orders WHERE amount > 1000) TO '/path/to/export.csv' (FORMAT CSV);

Streaming CSV parser with 64K batch size. Supports HEADER, DELIMITER options.

---

Transaction Control

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Or rollback
BEGIN;
DELETE FROM important_data;
ROLLBACK;

Isolation levels:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;      -- per-statement snapshot
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;     -- transaction-wide snapshot
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;        -- SSI with commit-time validation

---

Utility

EXPLAIN

EXPLAIN SELECT * FROM orders WHERE amount > 1000;

Shows the query execution plan.