SELECT name, city FROM customers;Pick columns from a table.
SQL cheat sheet
Standard SQL that runs in SQLite, PostgreSQL and MySQL with little or no change. SELECT examples use the sample shop database in the SQL playground (customers, orders, products).
27 entries
Querying
SELECT * FROM products WHERE price > 20 AND category = 'Books';Filter rows with WHERE, AND, OR, NOT.
SELECT name FROM customers WHERE city IN ('Berlin', 'Paris');IN matches any value in a list.
SELECT name FROM products WHERE name LIKE '%lamp%';LIKE with % (any run) and _ (one char).
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;Sort and limit.
SELECT DISTINCT city FROM customers;Remove duplicate rows.
SELECT name, price, CASE WHEN price > 50 THEN 'premium' ELSE 'standard' END AS tier FROM products;CASE expressions.
Aggregates
SELECT COUNT(*), AVG(price), MIN(price), MAX(price) FROM products;Aggregate functions.
SELECT category, COUNT(*) AS n FROM products GROUP BY category;One row per group.
SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id HAVING SUM(total) > 100;HAVING filters groups (WHERE filters rows).
Joins
SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;INNER JOIN: only rows with a match on both sides.
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;LEFT JOIN: all left rows, NULLs where no match.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Anti-join: customers with no orders.
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;FULL OUTER JOIN (SQLite 3.39+, PostgreSQL; not MySQL).
SELECT c.name, p.name FROM customers c CROSS JOIN products p;CROSS JOIN: every combination.
Subqueries, CTEs & windows
SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);Scalar subquery.
WITH big AS (SELECT * FROM orders WHERE total > 50)
SELECT customer_id, COUNT(*) FROM big GROUP BY customer_id;Common table expression (WITH).
SELECT name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
FROM products;Window function: rank inside each category.
SELECT id, total, SUM(total) OVER (ORDER BY id) AS running FROM orders;Running total.
Changing data
INSERT INTO customers (name, city) VALUES ('Lin', 'Oslo');Insert a row.
UPDATE products SET price = price * 1.1 WHERE category = 'Books';Update matching rows (always use WHERE).
DELETE FROM orders WHERE total = 0;Delete matching rows.
BEGIN;
UPDATE products SET price = 0 WHERE id = 1;
ROLLBACK;Transactions: COMMIT keeps, ROLLBACK undoes.
Schema
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
done INTEGER DEFAULT 0,
owner_id INTEGER REFERENCES customers(id)
);Create a table with constraints.
ALTER TABLE tasks ADD COLUMN due TEXT;Add a column.
CREATE INDEX idx_orders_customer ON orders(customer_id);Index columns you filter or join on.
DROP TABLE IF EXISTS tasks;Remove a table.
Frequently asked questions
What is the order of SQL clauses?
Written order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Logical evaluation order: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, which is why you cannot use a SELECT alias in WHERE.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and can use aggregates like SUM() or COUNT().
Can I practise these queries online?
Yes. SELECT snippets open in the QuickRef.dev SQL playground, which runs SQLite in your browser against a small sample shop database.