Even with ORMs (Prisma, Sequelize, TypeORM):

→ You'll debug slow queries (need EXPLAIN)

→ You'll write raw queries for complex operations

→ You'll migrate data or fix production issues

→ ORMs generate bad SQL sometimes — you need to spot it



SQL is a superpower that keeps paying dividends.

One hour of learning saves ten hours of debugging.

-- SELECT basics

SELECT * FROM users;                          -- All columns, all rows

SELECT name, email FROM users;                 -- Specific columns

SELECT DISTINCT status FROM orders;            -- Unique values only



-- WHERE filtering

SELECT * FROM users WHERE role = 'admin';

SELECT * FROM users WHERE age >= 18 AND active = true;

SELECT * FROM users WHERE country IN ('US', 'CA', 'GB');

SELECT * FROM products WHERE price BETWEEN 10 AND 100;

SELECT * FROM users WHERE email LIKE '%@gmail.com';

SELECT * FROM posts WHERE title IS NOT NULL;



-- Sorting and limiting

SELECT * FROM users ORDER BY created_at DESC;

SELECT * FROM users ORDER BY last_login DESC LIMIT 10;

SELECT * FROM users LIMIT 20 OFFSET 40;        -- Pagination (page 3, 20 per page)



-- COUNT, SUM, AVG, MAX, MIN

SELECT COUNT(*) FROM users;                    -- Total count

SELECT COUNT(DISTINCT country) FROM users;     -- Unique countries

SELECT AVG(price) FROM products WHERE category = 'electronics';

SELECT MIN(price), MAX(price) FROM products;

SELECT category, COUNT(*) as total FROM products GROUP BY category;

SELECT category, COUNT(*) as total FROM products GROUP BY category HAVING COUNT(*) > 5;



-- Pattern matching

SELECT * FROM users WHERE name LIKE 'J%';      -- Starts with J

SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Ends with gmail.com

SELECT * FROM users WHERE phone LIKE '555-___'; -- _ = exactly one character

-- INNER JOIN: Only matching records from both tables

SELECT u.name, o.total

FROM users u

INNER JOIN orders o ON u.id = o.user_id;

-- Result: Users who HAVE orders + their order info



-- LEFT JOIN: All from left table + matches from right (NULL if no match)

SELECT u.name, o.total

FROM users u

LEFT JOIN orders o ON u.id = o.user_id;

-- Result: ALL users, NULL order fields for users without orders



-- RIGHT JOIN: All from right table + matches from left (rarely used)

-- FULL OUTER JOIN: Everything from both sides (SQLite doesn't support this!)



-- Multiple JOINs

SELECT u.name, p.name AS product_name, o.quantity

FROM users u

INNER JOIN orders o ON u.id = o.user_id

INNER JOIN order_items oi ON o.id = oi.order_id

INNER JOIN products p ON oi.product_id = p.id

WHERE u.id = 123;



-- Self JOIN (same table twice)

SELECT e.name AS employee, m.name AS manager

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.id;

-- INSERT

INSERT INTO users (name, email, created_at)

VALUES ('Alice', '[email protected]', NOW());



INSERT INTO users (name, email) VALUES

('Bob', '[email protected]'),

('Carol', '[email protected]'),

('Dave', '[email protected]');



-- INSERT from SELECT (copy data)

INSERT INTO archived_users (id, name, archived_at)

SELECT id, name, NOW() FROM users WHERE deleted_at IS NOT NULL;



-- UPDATE

UPDATE users SET email = '[email protected]' WHERE id = 42;

UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

UPDATE users SET login_count = login_count + 1, last_login = NOW()

WHERE id = 123;



-- DELETE

DELETE FROM sessions WHERE expires_at < NOW();

DELETE FROM cart_items WHERE user_id = 123 AND created_at < NOW() - INTERVAL '7 days';



-- ⚠️ Always use WHERE! Without it, deletes EVERY row!

-- Safe pattern: Run SELECT first with same WHERE, then change to DELETE

-- Subquery in WHERE

SELECT * FROM products

WHERE price > (SELECT AVG(price) FROM products);



-- Subquery in SELECT

SELECT name,

       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count

FROM users u;



-- IN subquery

SELECT * FROM users

WHERE id IN (SELECT user_id FROM orders WHERE total > 100);



-- EXISTS (often faster than IN for large datasets)

SELECT * FROM users u

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000);



-- CTE (Common Table Expression) — cleaner than nested subqueries

WITH monthly_revenue AS (

    SELECT DATE_TRUNC('month', created_at) as month,

           SUM(total) as revenue

    FROM orders

    GROUP BY DATE_TRUNC('month', created_at)

),

monthly_avg AS (

    SELECT AVG(revenue) as avg_revenue FROM monthly_revenue

)

SELECT mr.month,

       mr.revenue,

       ma.avg_revenue,

       CASE WHEN mr.revenue > ma.avg_revenue THEN 'Above' ELSE 'Below' END as performance

FROM monthly_revenue mr

CROSS JOIN monthly_avg

ORDER BY mr.month;



-- CTE for complex data pipeline

WITH active_users AS (

    SELECT id, name FROM users WHERE last_login > NOW() - INTERVAL '30 days'

),

user_orders AS (

    SELECT user_id, COUNT(*) as cnt, SUM(total) as spent

    FROM orders

    WHERE created_at > NOW() - INTERVAL '30 days'

    GROUP BY user_id

)

SELECT au.name, COALESCE(uo.cnt, 0) as orders, COALESCE(uo.spent, 0) as total_spent

FROM active_users au

LEFT JOIN user_orders uo ON au.id = uo.user_id

ORDER BY total_spent DESC NULLS LAST;

-- Running total

SELECT date,

       amount,

       SUM(amount) OVER (ORDER BY date) as running_total

FROM transactions

WHERE account_id = 1

ORDER BY date;



-- Row number per group

SELECT *,

       ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank

FROM products;

-- Result: Each product ranked within its category



-- Moving average (7-day window)

SELECT date,

       revenue,

       AVG(revenue) OVER (

         ORDER BY date

         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

       ) as avg_7day

FROM daily_revenue;



-- First/last value in group

SELECT user_id,

       action,

       created_at,

       FIRST_VALUE(created_at) OVER (

         PARTITION BY user_id ORDER BY created_at

       ) as first_action_time,

       LAG(action, 1) OVER (

         PARTITION BY user_id ORDER BY created_at

       ) as previous_action

FROM user_events;



-- Dense rank (no gaps in ranking)

SELECT name,

       score,

       DENSE_RANK() OVER (ORDER BY score DESC) as rank

FROM leaderboard;

-- If two people tie for #2, next is #3 (not #4 like ROW_NUMBER)

-- Create index on single column

CREATE INDEX idx_users_email ON users(email);  -- For lookups by email



-- Composite index (order matters!)

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Good for: WHERE user_id = ? AND created_at > ?

-- Also covers: WHERE user_id = ? ORDER BY created_at (sort is free!)



-- Unique index (enforces uniqueness)

CREATE UNIQUE INDEX idx_users_username ON users(username);

CREATE UNIQUE INDEX idx_slug ON posts(slug);



-- Partial index (index only subset of data)

CREATE INDEX idx_active_users ON users(last_login)

WHERE active = true;



-- Covering index (query satisfied entirely from index)

CREATE INDEX idx_products_cat_price ON products(category, price INCLUDE (name));

-- Query: SELECT name, price FROM products WHERE category = ? → No table lookup needed!



-- When indexes DON'T help:

-- → Querying more than ~5% of the table (full scan is faster)

-- → Using functions on indexed columns (LOWER(email) can't use email index)

-- → LIKE '%term' (leading wildcard prevents index usage)

-- → OR conditions (sometimes — depends on optimizer)

-- → Small tables (< few hundred rows)



-- Check if your query uses an index:

EXPLAIN QUERY PLAN

SELECT * FROM orders WHERE user_id = 123 AND created_at > '2026-01-01';



-- Analyze table statistics (helps query planner):

ANALYZE users;

ANALYZE;

-- ❌ Slow: N+1 problem (one query per user)

-- (Run in application loop: SELECT * FROM orders WHERE user_id = ? for each user)



-- ✅ Fast: One query with all data

SELECT u.name, o.*

FROM users u

JOIN orders o ON u.id = o.user_id

WHERE u.active = true;



-- ❌ Slow: SELECT *

-- Fetches all columns (wastes memory/bandwidth)



-- ✅ Fast: Select only what you need

SELECT id, name, email FROM users WHERE active = true;



-- ❌ Slow: Missing LIMIT on unbounded queries

SELECT * FROM logs; -- Could return millions of rows!



-- ✅ Fast: Always paginate

SELECT * FROM logs ORDER BY created_at DESC LIMIT 50;



-- Use EXPLAIN to find slow queries

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- Shows: actual time, rows scanned, whether index was used



-- Common anti-patterns:

-- → SELECT * in a subquery (select only needed columns)

-- → DISTINCT when GROUP BY would work (GROUP BY is often faster)

-- → ORDER BY RAND() (extremely slow on large tables)

-- → NOT IN subquery (NOT EXISTS is usually faster)

-- → Correlated subqueries in SELECT (CTEs are clearer and often faster)

BEGIN;



-- Transfer money between accounts

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;



-- Insert transaction record

INSERT INTO transfers (from_account, to_account, amount)

VALUES (1, 2, 100);



COMMIT; -- Both updates happen atomically (or neither does)



-- On error:

ROLLBACK; -- Undo everything since BEGIN



-- Transaction with savepoints

BEGIN;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;

SAVEPOINT after_inventory;

-- If payment fails:

ROLLBACK TO SAVEPOINT after_inventory;

-- Inventory update undone, but still in transaction

COMMIT;