SQL cheat sheet

The everyday SQL on one page, for people who have done the lessons and need a reminder. Each heading links to the lesson that explains it. The table and column names are those of the practice database.

The order of a query

Written order, which never changes:

SELECT   columns, aggregates
FROM     table
JOIN     other_table ON matching rule
WHERE    conditions on rows
GROUP BY columns
HAVING   conditions on groups
ORDER BY columns
LIMIT    number OFFSET number;

The database carries it out in a different order: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. That is why an alias made in SELECT cannot be used in WHERE.

Reading data

SELECT, WHERE, AND, OR, IN, BETWEEN, LIKE, NULL, ORDER BY, LIMIT, DISTINCT.

SELECT * FROM products;
SELECT name, price * 1.18 AS with_tax FROM products;
SELECT DISTINCT city FROM customers;
SELECT name FROM products WHERE price > 500 AND category = 'Home';
SELECT name FROM customers WHERE city IN ('Delhi', 'Mumbai');
SELECT name FROM products WHERE price BETWEEN 100 AND 300;
SELECT name FROM customers WHERE name LIKE 'A%';      -- starts with A
SELECT name FROM customers WHERE email IS NULL;       -- never = NULL
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
SELECT COALESCE(email, 'none') FROM customers;

Wildcard in LIKE	Means
`%`	any run of characters
`_`	exactly one character

Summaries

Functions, aggregates, GROUP BY, HAVING.

SELECT COUNT(*), SUM(stock), AVG(price), MIN(price), MAX(price) FROM products;
SELECT COUNT(DISTINCT city) FROM customers;
SELECT category, COUNT(*) AS n FROM products GROUP BY category;
SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 400;
SELECT UPPER(name), LENGTH(name), SUBSTR(name, 1, 3), ROUND(price / 3.0, 2) FROM products;

WHERE filters rows before grouping. HAVING filters groups after it. Every column in the SELECT must be in the GROUP BY or inside an aggregate.

Joins

INNER JOIN, LEFT JOIN, many tables and self joins.

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;             -- only rows that match

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id            -- every customer
GROUP BY c.id, c.name;

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;                                    -- customers with no orders

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;         -- self join

Join	Keeps
`JOIN` (inner)	rows with a match on both sides
`LEFT JOIN`	all rows of the first table
`RIGHT JOIN`	all rows of the second table
`FULL JOIN`	all rows of both
`CROSS JOIN`	every pairing

Subqueries and set operations

Subqueries, UNION.

SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT city FROM customers UNION SELECT 'Goa';        -- no repeats
SELECT name FROM customers UNION ALL SELECT name FROM employees;
SELECT id FROM customers EXCEPT SELECT customer_id FROM orders;

Changing data

INSERT, UPDATE and DELETE.

INSERT INTO products (name, category, price, stock)
VALUES ('Desk Lamp', 'Home', 599, 15);

UPDATE products SET price = 70 WHERE id = 5;          -- always with WHERE
DELETE FROM orders WHERE id = 9;                      -- always with WHERE

Write the WHERE as a SELECT first and look at the rows. An UPDATE or DELETE without WHERE changes every row.

Tables

CREATE TABLE, ALTER, DROP and indexes.

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id),
  stars INTEGER NOT NULL CHECK (stars BETWEEN 1 AND 5),
  comment TEXT,
  created TEXT NOT NULL DEFAULT '2026-01-01'
);
ALTER TABLE customers ADD COLUMN phone TEXT;
CREATE INDEX idx_orders_customer ON orders (customer_id);
DROP TABLE reviews;

Constraint	Rule
`PRIMARY KEY`	unique, not null, identifies the row
`NOT NULL`	a value is required
`UNIQUE`	no repeats
`DEFAULT x`	value used when none is given
`CHECK (cond)`	the condition must be true
`REFERENCES t(col)`	must exist in another table

Going further

CASE, dates, views and WITH, window functions.

SELECT name, CASE WHEN price >= 1000 THEN 'premium' ELSE 'standard' END AS band FROM products;
SELECT SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered FROM orders;

WITH totals AS (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id)
SELECT AVG(n) FROM totals;

CREATE VIEW cheap AS SELECT * FROM products WHERE price < 150;

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS place,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

First N rows in each database

Database	Form
SQLite, MySQL, PostgreSQL	`... ORDER BY price DESC LIMIT 3`
SQL Server	`SELECT TOP 3 ... ORDER BY price DESC`
Oracle, standard SQL	`... ORDER BY price DESC FETCH FIRST 3 ROWS ONLY`

More differences are on the page about SQL in different databases.

Six habits that prevent most mistakes

Text in single quotation marks. Numbers without.
IS NULL, never = NULL.
Brackets whenever AND and OR meet.
Table aliases and table.column in every join.
Count a column of the right-hand table after a LEFT JOIN, not *.
SELECT before every UPDATE and DELETE.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.