SQL Cheatsheet: Queries, Joins, Aggregates & Subqueries

sql-cheatsheet.md

SQL Cheatsheet: Queries, Joins, Aggregates & Subqueries

A database-agnostic SQL reference (works on PostgreSQL, MySQL, SQL Server, SQLite): SELECTs, filtering, joins, grouping, subqueries, and the DDL/DML you use every day. Copy-paste ready.

SELECT basics

SELECT * FROM users;
SELECT name, email FROM users;
SELECT DISTINCT country FROM users;
SELECT name AS full_name FROM users;          -- alias
SELECT * FROM users LIMIT 10;                 -- MySQL/Postgres/SQLite
SELECT TOP 10 * FROM users;                   -- SQL Server

Filtering (WHERE)

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE country = 'US' AND active = true;
SELECT * FROM users WHERE country IN ('US','UK','CA');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE name LIKE 'A%';     -- starts with A
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE name LIKE '%son%';  -- contains

Sorting & paging

SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY country ASC, age DESC;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;   -- page 3

Joins

-- INNER: only matching rows in both tables
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

-- LEFT: all users, orders if any (NULL otherwise)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- RIGHT / FULL OUTER also exist

Join type	Returns
INNER JOIN	rows matching in both tables
LEFT JOIN	all left rows + matches
RIGHT JOIN	all right rows + matches
FULL OUTER JOIN	all rows from both
CROSS JOIN	every combination

Aggregates & GROUP BY

SELECT COUNT(*) FROM users;
SELECT country, COUNT(*) AS n
FROM users
GROUP BY country
HAVING COUNT(*) > 100          -- filter groups (not WHERE)
ORDER BY n DESC;

-- functions
SELECT AVG(total), SUM(total), MIN(total), MAX(total) FROM orders;

Subqueries & CTEs

-- subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- CTE (cleaner, reusable)
WITH big_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders GROUP BY user_id HAVING SUM(total) > 1000
)
SELECT u.name, b.spent
FROM big_spenders b JOIN users u ON u.id = b.user_id;

INSERT / UPDATE / DELETE

INSERT INTO users (name, email) VALUES ('Ada', 'a@x.com');
INSERT INTO users (name, email) VALUES ('A','a@x'), ('B','b@x');  -- bulk
UPDATE users SET active = false WHERE last_login < '2024-01-01';
DELETE FROM users WHERE active = false;

DDL: tables & indexes

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) DEFAULT 0,
  created_at TIMESTAMP
);

ALTER TABLE products ADD COLUMN sku VARCHAR(50);
CREATE INDEX idx_products_name ON products(name);
DROP TABLE products;

Common interview-level queries

-- 2nd highest salary
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- find duplicates
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- row number per group (window function)
SELECT name, country,
       ROW_NUMBER() OVER (PARTITION BY country ORDER BY age DESC) AS rn
FROM users;

-- running total
SELECT date, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales;

Quick reference table

Task	SQL
Unique values	SELECT DISTINCT col
Pattern match	WHERE col LIKE 'A%'
Paging	LIMIT 10 OFFSET 20
Count per group	GROUP BY col + COUNT(*)
Filter groups	HAVING
All left rows	LEFT JOIN
Find duplicates	GROUP BY x HAVING COUNT(*) > 1
Rank within group	ROW_NUMBER() OVER (PARTITION BY …)

---

Maintained by the team at EchoGlobal. Hiring database talent? See our curated lists of Top SQL Experts, Top PostgreSQL Experts, and Top MySQL Engineers on GitHub, or hire pre-vetted engineers in days.