SQL Queries Every Developer Should Know (With Examples)

You don't need to be a DBA. But you DO need to write queries.

The Basics

-- SELECT: Read data
SELECT name, email FROM users WHERE active = 1;

-- INSERT: Add data
INSERT INTO users (name, email, role) VALUES ('Alex', '[e-mail]', 'user');

-- UPDATE: Change data
UPDATE users SET role = 'admin' WHERE id = 123;

-- DELETE: Remove data
DELETE FROM users WHERE id = 123;

Filtering and Sorting

-- WHERE clauses
SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE name LIKE 'A%';          -- Starts with 'A'
SELECT * FROM users WHERE email LIKE '[e-mail]'; -- Ends with @gmail.com
SELECT * FROM orders WHERE status IN ('pending', 'shipped');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE bio IS NULL;               -- Check for NULL (not = NULL)

-- AND / OR / NOT
SELECT * FROM users WHERE active = 1 AND (role = 'admin' OR role = 'moderator');

-- ORDER BY
SELECT * FROM posts ORDER BY created_at DESC;        -- Newest first
SELECT * FROM products ORDER BY price ASC, name ASC; -- Sort by price, then name

-- LIMIT and OFFSET (pagination)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- Page 3

JOINs — The Queries That Matter Most

-- INNER JOIN: Only matching rows
SELECT 
  u.name AS user_name,
  o.total AS order_total,
  o.created_at AS order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- LEFT JOIN: All users, even without orders
SELECT 
  u.name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

-- Multiple JOINs: Real-world query
SELECT 
  u.name AS customer,
  p.name AS product,
  oi.quantity,
  oi.price,
  o.created_at AS order_date
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
WHERE o.status = 'completed'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC;

Aggregation — GROUP BY is Your Friend

-- Count per status
SELECT status, COUNT(*) AS count FROM orders GROUP BY status;

-- Revenue by month
SELECT 
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month DESC;

-- Top customers
SELECT 
  u.name,
  u.email,
  COUNT(o.id) AS total_orders,
  SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id
HAVING lifetime_value > 1000        -- Filter AFTER grouping
ORDER BY lifetime_value DESC
LIMIT 10;

Subqueries

-- Users who haven't ordered in 90 days
SELECT name, email, last_order_date
FROM (
  SELECT 
    u.name, u.email,
    MAX(o.created_at) AS last_order_date
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id
) AS user_orders
WHERE last_order_date < DATE('now', '-90 days')
   OR last_order_date IS NULL;

-- Products more expensive than average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

Window Functions (Advanced but Essential)

-- Row numbers for ranking
SELECT 
  name,
  total_spent,
  ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS rank
FROM customer_summary;

-- Running total
SELECT 
  DATE(created_at) AS day,
  SUM(total) AS daily_revenue,
  SUM(SUM(total)) OVER (ORDER BY DATE(created_at)) AS running_total
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at)
ORDER BY day;

-- Percentile within group
SELECT 
  department,
  name,
  salary,
  PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Useful Utility Queries

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

-- Delete duplicates (keep latest)
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

-- Upsert (insert or update)
INSERT INTO users (id, name, email)
VALUES (1, 'Alex', '[e-mail]')
ON CONFLICT(id) DO UPDATE SET name = excluded.name, email = excluded.email;

-- Conditional counting
SELECT 
  COUNT(*) AS total_users,
  SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) AS admins,
  SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_users,
  SUM(CASE WHEN created_at >= DATE('now', '-30 days') THEN 1 ELSE 0 END) AS new_users
FROM users;

-- Date arithmetic
SELECT * FROM orders 
WHERE created_at >= DATE('now', '-7 days')     -- Last 7 days
  AND created_at < DATE('now', '-1 day');      -- Before yesterday

-- String manipulation
SELECT 
  UPPER(name) AS uppercase_name,
  LOWER(email) AS lowercase_email,
  TRIM(bio) AS clean_bio,
  SUBSTR(description, 1, 100) AS short_desc,
  REPLACE(url, 'http://', 'https://') AS secure_url
FROM users;

Performance Tips

-- EXPLAIN: See how your query executes
EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

-- Create indexes for slow queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

-- Common mistakes:
-- ❌ SELECT * (selects all columns — slow)
-- ✅ SELECT id, name, email (only what you need)

-- ❌ LIKE '%keyword%' (can't use index)
-- ✅ LIKE 'keyword%' (can use index for prefix)

-- ❌ Subquery in SELECT (runs for every row)
-- ✅ JOIN instead of correlated subquery

-- ❌ ORDER BY on unindexed column
-- ✅ Add index or avoid sorting huge result sets

The Quick Reference

Query Type	Example
Filter	WHERE price > 100
Pattern match	WHERE name LIKE 'A%'
Sort	ORDER BY created_at DESC
Pagination	LIMIT 20 OFFSET 40
Join	INNER JOIN orders ON ...
Aggregate	COUNT(*), SUM(price)
Group	GROUP BY status
Having	HAVING COUNT(*) > 5
Subquery	WHERE price > (SELECT AVG(price) ...)
Upsert	ON CONFLICT DO UPDATE
Window	ROW_NUMBER() OVER (...)
Index	CREATE INDEX idx ON table(col)

---

What's your most-used SQL query? Share it below!

Follow @armorbreak for more developer content.