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', 'alice@example.com', NOW());

INSERT INTO users (name, email) VALUES
('Bob', 'bob@example.com'),
('Carol', 'carol@example.com'),
('Dave', 'dave@example.com');

-- 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 = 'new@email.com' 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;