SQL Cheatsheet

SQL is written SELECT-first but executed FROM-first. This is the single most useful mental model in the whole language — it explains why you can't filter on an alias in WHERE but can in HAVING, and why aggregates aren't allowed in WHERE.

The clauses that shape which rows come back and in what order.

SELECT * FROM employees
WHERE salary > 60000;

SELECT DISTINCT dept
FROM employees;

SELECT name, salary FROM employees
ORDER BY salary DESC, name ASC;

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 20;

SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 90000;

SELECT * FROM employees
WHERE dept IN ('Sales','Eng');

SELECT * FROM employees
WHERE name LIKE 'A%';    -- starts with A

SELECT * FROM employees
WHERE manager_id IS NULL;

WHERE dept = 'Eng'
  AND (salary > 80000 OR manager_id IS NULL);

Joins decide which rows from each side survive when the two tables are matched on a key. Picking the wrong one is the most common cause of a "why are rows missing" bug.

Aggregates collapse many rows into one value. Used bare, they summarize the whole table; paired with GROUP BY, they summarize each group separately.

SELECT COUNT(*) FROM employees;

SELECT SUM(amount) FROM orders;

SELECT AVG(salary) FROM employees;

SELECT MIN(hire_date), MAX(salary)
FROM employees;

SELECT COUNT(DISTINCT dept)
FROM employees;

GROUP BY buckets rows sharing a value; every non-aggregated column in SELECT must appear in GROUP BY. HAVING filters those buckets — it's WHERE's counterpart, just running one stage later.

SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
HAVING COUNT(*) > 3
ORDER BY avg_salary DESC;

Function names vary slightly by engine (MySQL vs Postgres vs SQL Server) — the MySQL spelling is shown, with the notable alternate named where it differs.

SELECT CONCAT(name, ' — ', dept) FROM employees;

SELECT UPPER(name) FROM employees WHERE LOWER(dept) = 'sales';

SELECT name FROM employees WHERE LENGTH(name) > 10;

SELECT SUBSTRING(name, 1, 3) FROM employees;

SELECT TRIM('  Priya  ');

SELECT REPLACE(name, 'a', '@') FROM employees;

SELECT LEFT(name, 1) AS initial FROM employees;

SELECT INSTR(name, 'a') FROM employees;

SELECT CAST(salary AS CHAR) FROM employees;

Rounding, sign, and arithmetic helpers you'll reach for constantly in reporting queries.

SELECT ROUND(salary / 12, 2) FROM employees;

SELECT CEIL(4.2), FLOOR(4.8);

SELECT ABS(salary - 70000) FROM employees;

SELECT * FROM employees WHERE id % 2 = 0;

SELECT POWER(2, 10), SQRT(144);

Dates are the most engine-specific corner of SQL — MySQL syntax shown, Postgres equivalents noted.

SELECT * FROM orders WHERE order_date = CURDATE();

SELECT DATEDIFF(NOW(), hire_date) AS days_employed FROM employees;

SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;

SELECT * FROM orders WHERE YEAR(order_date) = 2026;

SELECT DATE_FORMAT(order_date, '%Y-%m') FROM orders;

SELECT EXTRACT(QUARTER FROM order_date) FROM orders;

SQL's if/else — branch on a value or fill in for NULLs inline, without leaving the SELECT list.

SELECT name,
  CASE
    WHEN salary >= 100000 THEN 'senior'
    WHEN salary >= 60000  THEN 'mid'
    ELSE 'junior'
  END AS band
FROM employees;

SELECT COALESCE(manager_id, 0) FROM employees;

SELECT amount / NULLIF(quantity, 0) FROM orders;

SELECT IFNULL(manager_id, -1) FROM employees;

A query nested inside another. The key distinction is whether the inner query can run once on its own (independent) or needs a value from the outer row on every iteration (correlated).

Window functions compute across a set of rows without collapsing them the way GROUP BY does — every input row survives, with a calculated column added alongside it.

WITH ranked AS (
  SELECT name, dept, salary,
         RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
  FROM employees
)
SELECT * FROM ranked WHERE dept_rank <= 2;   -- top 2 earners per dept

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)

RANK() OVER (ORDER BY salary DESC)

LAG(amount) OVER (ORDER BY order_date)

SUM(amount) OVER (ORDER BY order_date)

WITH big_orders AS (
  SELECT * FROM orders WHERE amount > 1000
)
SELECT * FROM big_orders;

Stack two result sets vertically instead of joining them side by side. Both sides need the same number of columns with compatible types.

SELECT name FROM employees WHERE dept = 'Sales'
UNION
SELECT name FROM employees WHERE dept = 'Eng';

Data Definition Language shapes the schema itself — tables, columns, and the rules that guard what can go into them.

CREATE TABLE employees (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(100) NOT NULL,
  dept        VARCHAR(50)  DEFAULT 'Unassigned',
  salary      DECIMAL(10,2) CHECK (salary >= 0),
  manager_id  INT REFERENCES employees(id),
  email       VARCHAR(255) UNIQUE
);

ALTER TABLE employees ADD COLUMN hire_date DATE;
ALTER TABLE employees DROP COLUMN email;
DROP TABLE employees;      -- removes table + data + structure
TRUNCATE TABLE orders;     -- wipes rows, keeps structure, resets auto-increment

Data Manipulation Language changes the rows themselves. Wrap multi-statement changes in a transaction so they succeed or fail as one unit.

INSERT INTO employees (name, dept, salary)
VALUES ('Priya', 'Eng', 95000);

UPDATE employees SET salary = salary * 1.1
WHERE dept = 'Eng';

DELETE FROM orders WHERE order_date < '2020-01-01';

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Two ways to make a schema faster to query or easier to work with, without changing the underlying data.

CREATE INDEX idx_employees_dept ON employees(dept);

CREATE VIEW high_earners AS
SELECT name, dept, salary FROM employees
WHERE salary > 100000;

SELECT * FROM high_earners;