First Queries

After connecting to ScramDB (see Quick Start), let's explore SQL with joins, subqueries, and analytical functions.

Create Sample Tables

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(50),
    created_at DATE
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id INTEGER,
    amount DOUBLE PRECISION,
    status VARCHAR(20),
    order_date DATE
);

INSERT INTO customers VALUES
    (1, 'Alice', 'US', '2024-01-10'),
    (2, 'Bob', 'UK', '2024-02-15'),
    (3, 'Charlie', 'US', '2024-03-20'),
    (4, 'Diana', 'DE', '2024-01-05');

INSERT INTO orders VALUES
    (1, 1, 250.00, 'completed', '2024-03-01'),
    (2, 1, 175.00, 'completed', '2024-03-15'),
    (3, 2, 500.00, 'completed', '2024-03-10'),
    (4, 3, 89.99, 'pending', '2024-03-20'),
    (5, 1, 320.00, 'completed', '2024-04-01'),
    (6, 4, 450.00, 'cancelled', '2024-03-25');

Joins

-- Inner join: customers with their orders
SELECT c.name, o.amount, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
ORDER BY o.amount DESC;

-- Left join: all customers, including those without orders
SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
GROUP BY c.name
ORDER BY total_spent DESC;

Subqueries

-- Customers who have spent more than average
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
HAVING SUM(o.amount) > (
    SELECT AVG(amount) FROM orders WHERE status = 'completed'
);

-- EXISTS: customers with at least one completed order
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.status = 'completed'
);

Window Functions

-- Running total per customer
SELECT
    c.name,
    o.order_date,
    o.amount,
    SUM(o.amount) OVER (
        PARTITION BY c.id ORDER BY o.order_date
    ) AS running_total
FROM customers c
JOIN orders o ON c.id = o.customer_id
ORDER BY c.name, o.order_date;

Aggregation with CASE

SELECT
    c.country,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.country;

Create an Index

-- Speed up lookups by customer_id
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- The optimizer automatically uses the index when it's beneficial
SELECT * FROM orders WHERE customer_id = 1;

Session Settings

ScramDB accepts the session-level SHOW and SET commands a PostgreSQL user reaches for most often:

-- Isolation level for the current transaction or session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW transaction_isolation;

-- Cap how long a statement is allowed to run
SET statement_timeout = '5s';
SHOW statement_timeout;

-- Identify and manage the current backend
SHOW backend_pid;
SELECT pg_backend_pid();
SELECT pg_cancel_backend(12345);

-- Application-level locking
SELECT pg_advisory_lock(42);
SELECT pg_try_advisory_lock(42);

-- Reset session state
DISCARD ALL;
RESET ALL;
DEALLOCATE ALL;

This is the confirmed set of SHOW/SET targets ScramDB understands. It does not implement the full PostgreSQL GUC table (arbitrary settings like client_min_messages), so treat any setting not listed above as unverified rather than assuming it works.

Next Steps

• SQL Reference - Full SQL compatibility reference
• Functions - All 80+ scalar, aggregate, and window functions