SQL Queries Cheatsheet

Database SQL reference containing queries for JOIN, GROUP BY, subqueries, indexing, and transactional rollbacks.

Standard JOIN Queries

SELECT * FROM A INNER JOIN B ON A.id = B.a_id

Retrieve matching records present in both tables.

SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id

Retrieve all records from left table, with matching ones from right.

SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id

SELECT * FROM A FULL OUTER JOIN B ON A.id = B.a_id

Retrieve all records when there is a match in left or right.

SELECT u.name, o.total FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id

SELECT col, COUNT(*), AVG(x) FROM tab GROUP BY col HAVING COUNT(*) > n

Aggregate rows by values, filtering aggregates via HAVING.

SELECT cat, COUNT(*), AVG(price) FROM items GROUP BY cat HAVING COUNT(*) > 5

SELECT name, SUM(amount) FROM sales GROUP BY name

Calculate total sums grouped by specified attributes.

SELECT salesperson, SUM(revenue) FROM deals GROUP BY salesperson

INSERT INTO tbl (cols) VALUES (vals)

Insert new data records into databases.

INSERT INTO users (name, email) VALUES ('John', 'john@test.com')

UPDATE tbl SET col = val WHERE cond

Modify existing records that match a conditional query.

UPDATE users SET status = 'active' WHERE id = 5

DELETE FROM tbl WHERE cond

Remove records matching query conditions.

DELETE FROM users WHERE last_login < '2025-01-01'

CREATE INDEX idx_name ON tbl (col)

Establish table indices to speed up query execution.

CREATE INDEX idx_user_email ON users (email)