SQL Cheat Sheet: Queries, Joins, Functions, and More

Quick Answer: SELECT * FROM users WHERE age > 18 ORDER BY name LIMIT 10; gets 10 adult users sorted by name. INSERT INTO users (name, age) VALUES ('Sam', 30); adds a row. UPDATE users SET age = 31 WHERE name = 'Sam'; updates it. DELETE FROM users WHERE id = 5; removes it.

SELECT (Reading Data)

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With alias
SELECT name AS full_name, email AS contact FROM users;

-- Unique values
SELECT DISTINCT country FROM users;

-- Count rows
SELECT COUNT(*) FROM users;

WHERE (Filtering)

-- Comparison
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name = 'Sam';
SELECT * FROM users WHERE age != 30;
SELECT * FROM users WHERE age >= 18 AND age <= 65;

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 18 AND 65;

-- IN (list of values)
SELECT * FROM users WHERE country IN ('US', 'UK', 'CA');

-- LIKE (pattern matching)
SELECT * FROM users WHERE name LIKE 'S%';      -- Starts with S
SELECT * FROM users WHERE email LIKE '%@gmail%'; -- Contains @gmail
SELECT * FROM users WHERE name LIKE '_am';       -- 3 chars ending in "am"

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- NOT
SELECT * FROM users WHERE country NOT IN ('CN', 'RU');
SELECT * FROM users WHERE name NOT LIKE '%test%';

ORDER BY and LIMIT

-- Sort ascending (default)
SELECT * FROM users ORDER BY name;

-- Sort descending
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple sort columns
SELECT * FROM users ORDER BY country ASC, name ASC;

-- Limit results
SELECT * FROM users LIMIT 10;

-- Pagination (offset)
SELECT * FROM users LIMIT 10 OFFSET 20;    -- Page 3 (skip 20, get 10)
-- MySQL shorthand:
SELECT * FROM users LIMIT 20, 10;

INSERT (Adding Data)

-- Single row
INSERT INTO users (name, email, age)
VALUES ('Sam', '[email protected]', 30);

-- Multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Alice', '[email protected]', 25),
    ('Bob', '[email protected]', 35),
    ('Charlie', '[email protected]', 28);

-- Insert from select
INSERT INTO archive (name, email)
SELECT name, email FROM users WHERE active = false;

UPDATE (Modifying Data)

-- Update specific rows (always use WHERE!)
UPDATE users SET age = 31 WHERE name = 'Sam';

-- Update multiple columns
UPDATE users SET name = 'Samuel', age = 31 WHERE id = 1;

-- Update with calculation
UPDATE products SET price = price * 1.10;    -- WARNING: updates ALL rows!

-- Update with subquery
UPDATE users SET status = 'premium'
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

DELETE (Removing Data)

-- Delete specific rows (always use WHERE!)
DELETE FROM users WHERE id = 5;

-- Delete with condition
DELETE FROM users WHERE last_login < '2025-01-01';

-- Delete all rows (keeps table structure)
DELETE FROM users;
-- Or faster:
TRUNCATE TABLE users;

JOINs

-- INNER JOIN (only matching rows from both tables)
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN (all from left table, matching from right)
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN (all from right table, matching from left)
SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- Multiple JOINs
SELECT u.name, o.total, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- Self JOIN
SELECT a.name AS employee, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;

GROUP BY and Aggregates

-- Count per group
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

-- Sum, average, min, max
SELECT category,
    COUNT(*) AS products,
    AVG(price) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    SUM(quantity) AS total_stock
FROM products
GROUP BY category;

-- Filter groups (HAVING, not WHERE)
SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

-- GROUP BY with JOIN
SELECT u.country, SUM(o.total) AS revenue
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.country
ORDER BY revenue DESC;

Subqueries

-- In WHERE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 500);

-- In FROM (derived table)
SELECT avg_total FROM (
    SELECT user_id, AVG(total) AS avg_total
    FROM orders
    GROUP BY user_id
) AS user_avgs
WHERE avg_total > 100;

-- Correlated subquery
SELECT name, (
    SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id
) AS order_count
FROM users;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Table Operations

-- Create table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,     -- MySQL
    -- id SERIAL PRIMARY KEY,              -- PostgreSQL
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Remove column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Change column type
ALTER TABLE users MODIFY COLUMN age BIGINT;          -- MySQL
-- ALTER TABLE users ALTER COLUMN age TYPE BIGINT;    -- PostgreSQL

-- Drop table
DROP TABLE users;
DROP TABLE IF EXISTS users;

Indexes

-- Create index
CREATE INDEX idx_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Composite index
CREATE INDEX idx_country_age ON users(country, age);

-- Drop index
DROP INDEX idx_email ON users;          -- MySQL
-- DROP INDEX idx_email;                -- PostgreSQL

-- View indexes
SHOW INDEX FROM users;                  -- MySQL
-- \d users                             -- PostgreSQL

Common Functions

String

CONCAT(first, ' ', last)          -- Combine strings
UPPER(name)                       -- UPPERCASE
LOWER(name)                       -- lowercase
LENGTH(name)                      -- Character count
TRIM(name)                        -- Remove whitespace
SUBSTRING(name, 1, 5)             -- First 5 chars
REPLACE(name, 'old', 'new')       -- Replace text

Date (MySQL Syntax)

NOW()                             -- Current datetime
CURDATE()                         -- Current date
DATE(created_at)                  -- Extract date from datetime
YEAR(created_at)                  -- Extract year
DATEDIFF(end, start)              -- Days between dates
DATE_ADD(date, INTERVAL 7 DAY)   -- Add 7 days
n-- PostgreSQL equivalents: CURRENT_DATE, end_date - start_date, date + INTERVAL '7 days'

Numeric

ROUND(price, 2)                   -- Round to 2 decimals
CEIL(4.2)                         -- 5
FLOOR(4.8)                        -- 4
ABS(-5)                           -- 5
MOD(10, 3)                        -- 1

Conditional

-- CASE
SELECT name,
    CASE
        WHEN age < 13 THEN 'Child'
        WHEN age < 18 THEN 'Teen'
        ELSE 'Adult'
    END AS age_group
FROM users;

-- COALESCE (first non-null)
SELECT COALESCE(phone, email, 'No contact') FROM users;

-- NULLIF
SELECT NULLIF(score, 0)    -- Returns NULL if score is 0
FROM tests;

Common Patterns

Upsert (Insert or Update)

-- MySQL
INSERT INTO users (id, name, email) VALUES (1, 'Sam', '[email protected]') AS new
ON DUPLICATE KEY UPDATE name = new.name, email = new.email;

-- PostgreSQL
INSERT INTO users (id, name, email) VALUES (1, 'Sam', '[email protected]')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

Ranking

SELECT name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank,
    RANK() OVER (ORDER BY score DESC) AS rank_with_ties,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players;

Running Total

SELECT date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

See Also

• JSON Guide
• Docker Cheat Sheet
• Bash Scripting Cheat Sheet