SQL Cheat Sheet — Every Query You Need to Know

SELECT * FROM users;
SELECT name, email FROM users;
SELECT DISTINCT country FROM users;
SELECT name AS full_name FROM users;
SELECT COUNT(*) AS total FROM users;

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE country IN ('US', 'UK', 'BE');
SELECT * FROM users WHERE name LIKE 'A%';       -- starts with A
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE age > 18 AND active = true;
SELECT * FROM users WHERE age > 65 OR role = 'admin';

SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY country, name;
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- pagination

-- Count users per country
SELECT country, COUNT(*) AS total
FROM users
GROUP BY country;
— Average age per country, only countries with 10+ users
SELECT country, AVG(age) AS avg_age
FROM users
GROUP BY country
HAVING COUNT(*) >= 10;
— Aggregate functions: COUNT, SUM, AVG, MIN, MAX

Returns only rows that have matching values in both tables.

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

Returns all rows from the left table, with matching rows from the right (NULL if no match).

-- All users, even those without orders
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- All orders, even without matching users
SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
— All rows from both tables
SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

-- Find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);
— Multiple rows
INSERT INTO users (name, email) VALUES
(‘Bob’, ‘bob@example.com’),
(‘Charlie’, ‘charlie@example.com’);
— Insert from select
INSERT INTO archive (name, email)
SELECT name, email FROM users WHERE active = false;

UPDATE users SET active = false WHERE last_login < '2025-01-01';
UPDATE users SET age = age + 1;  -- increment all
UPDATE users SET name = 'Bob', email = 'new@email.com' WHERE id = 5;

⚠️ Always use WHERE — without it, you update every row.

DELETE FROM users WHERE id = 5;
DELETE FROM users WHERE active = false;
DELETE FROM users;  -- deletes ALL rows (careful!)
TRUNCATE TABLE users;  -- faster, resets auto-increment

⚠️ Always use WHERE — without it, you delete everything.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER DEFAULT 0,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
— With foreign key
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total DECIMAL(10, 2) NOT NULL
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
DROP INDEX idx_users_email;

Add indexes on columns you frequently filter or join on.

-- Users who have placed orders
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
— Users with above-average age
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);
— Correlated subquery
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

WITH active_users AS (
  SELECT * FROM users WHERE active = true
),
user_orders AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders GROUP BY user_id
)
SELECT au.name, uo.total_spent
FROM active_users au
JOIN user_orders uo ON au.id = uo.user_id;

CTEs make complex queries readable. Use them instead of nested subqueries.

-- Row number
SELECT name, ROW_NUMBER() OVER (ORDER BY created_at) AS row_num FROM users;
— Rank (with gaps for ties)
SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM leaderboard;
— Running total
SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales;
— Previous/next row
SELECT date, amount,
LAG(amount) OVER (ORDER BY date) AS prev_amount,
LEAD(amount) OVER (ORDER BY date) AS next_amount
FROM sales;
— Partition
SELECT department, name, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- Combine results (removes duplicates)
SELECT name FROM customers UNION SELECT name FROM suppliers;
— Keep duplicates
SELECT name FROM customers UNION ALL SELECT name FROM suppliers;
— Only in both
SELECT name FROM customers INTERSECT SELECT name FROM suppliers;
— In first but not second
SELECT name FROM customers EXCEPT SELECT name FROM suppliers;