SQL cheat sheet

SELECT * FROM products;

SELECT COUNT(*) FROM customers;

CREATE TABLE notes (note_id SERIAL PRIMARY KEY, note TEXT NOT NULL);

INSERT INTO notes (note) VALUES ('first'), ('second');

SELECT policy_number, annual_premium FROM policies WHERE status = 'active' AND annual_premium > 500;

SELECT * FROM policies ORDER BY annual_premium DESC LIMIT 10 OFFSET 10;

SELECT * FROM customers WHERE name ILIKE 'sofia%';

SELECT claim_number, reported_date - loss_date AS days_to_report FROM claims;

SELECT COALESCE(broker_id, 0), ROUND(annual_premium / 12, 2) FROM policies;

SELECT c.claim_number, p.policy_number FROM claims c JOIN policies p USING (policy_id);

SELECT b.name, p.policy_id FROM brokers b LEFT JOIN policies p USING (broker_id);

SELECT cu.name FROM customers cu LEFT JOIN policies p USING (customer_id) WHERE p.policy_id IS NULL;

SELECT cause, COUNT(*), SUM(reserve), AVG(reserve) FROM claims GROUP BY cause;

SELECT broker_id, COUNT(*) FROM policies GROUP BY broker_id HAVING COUNT(*) > 400;

SELECT COUNT(*) FILTER (WHERE status = 'open') FROM claims;

SELECT * FROM claims WHERE reserve > (SELECT AVG(reserve) FROM claims WHERE status = 'open');

ALTER TABLE notes ADD CONSTRAINT note_not_empty CHECK (note <> '');

ALTER TABLE notes ADD CONSTRAINT note_unique UNIQUE (note);

CREATE INDEX IF NOT EXISTS claims_policy_id ON claims (policy_id);

WITH open_claims AS (SELECT * FROM claims WHERE status = 'open') SELECT cause, COUNT(*) FROM open_claims GROUP BY cause;

CREATE OR REPLACE VIEW open_claims AS SELECT * FROM claims WHERE status = 'open';

SELECT broker_id, COUNT(*), RANK() OVER (ORDER BY COUNT(*) DESC) FROM policies WHERE broker_id IS NOT NULL GROUP BY broker_id;

SELECT paid_on, amount, SUM(amount) OVER (ORDER BY paid_on, payment_id) AS running_total FROM claim_payments;

SELECT claim_id, paid_on, LAG(paid_on) OVER (PARTITION BY claim_id ORDER BY paid_on) FROM claim_payments;

CREATE OR REPLACE FUNCTION double_it(p NUMERIC) RETURNS NUMERIC LANGUAGE SQL AS $$ SELECT p * 2 $$;

CREATE OR REPLACE FUNCTION no_blank() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF TRIM(NEW.note) = '' THEN RAISE EXCEPTION 'blank note'; END IF; RETURN NEW; END $$;

CREATE OR REPLACE TRIGGER no_blank BEFORE INSERT OR UPDATE ON notes FOR EACH ROW EXECUTE FUNCTION no_blank();

EXPLAIN SELECT * FROM claims WHERE policy_id = 42;

EXPLAIN ANALYZE SELECT * FROM claims WHERE loss_date >= DATE '2025-12-01';

CREATE INDEX IF NOT EXISTS claims_open_by_date ON claims (loss_date) WHERE status = 'open';

ANALYZE claims;

SELECT doc ->> 'adjuster', doc -> 'fraud_flags' FROM claim_documents;

SELECT COUNT(*) FROM claim_documents WHERE doc @> '{"channel": "web"}';

SELECT i ->> 'item', (i ->> 'cost')::NUMERIC FROM claim_documents, JSONB_ARRAY_ELEMENTS(doc -> 'items') i;

CREATE INDEX IF NOT EXISTS claim_documents_doc ON claim_documents USING GIN (doc jsonb_path_ops);

CREATE EXTENSION IF NOT EXISTS pg_trgm; SELECT name FROM customers WHERE name % 'Sofia Schafer';

CREATE ROLE analyst NOLOGIN; GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

REVOKE SELECT ON customers FROM analyst;

SET ROLE analyst; SELECT COUNT(*) FROM claims;

BEGIN; UPDATE claims SET reserve = reserve * 1.1 WHERE status = 'open'; ROLLBACK;

CREATE TABLE claims_backup AS SELECT * FROM claims;