Learning SQL

SQL is the one skill in this field that hasn’t needed replacing. Everything built on top of it has churned several times over and the language underneath is largely where it was.

This is roughly the order I’d learn it in, and the order matters more than the list does.

Start narrow

Every SQL journey starts with SELECT *, and the first thing to do is stop. Name your columns. Your future self and your warehouse bill will both benefit.

SELECT TOP 10 first_name, last_name
FROM users
WHERE signup_date > '2024-01-01'
ORDER BY last_name;

Learn SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY properly. And get curious early about the order the engine processes them, because it isn’t the order you write them in.

Aggregate

SUM, AVG, MIN, MAX, COUNT, paired with GROUP BY and HAVING. The distinction between WHERE and HAVING trips up more people than it should, and it comes straight from the processing order above.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Join

Start with INNER JOIN, get comfortable with LEFT JOIN, then look at APPLY when you need it.

SELECT u.first_name, o.order_date
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

If set theory from school is in there somewhere, UNION, EXCEPT, and INTERSECT will feel familiar.

Logical query processing

This is the one that turns SQL from memorization into understanding. Why does WHERE run before GROUP BY? Why can’t you reference a SELECT alias in your WHERE clause? Why does HAVING see aggregates and WHERE doesn’t?

One order explains all of it. Learn it early and a whole category of confusing errors stops being confusing.

Window functions

Where SQL gets powerful. Running totals, rankings, comparisons to the previous row, all without a self-join.

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    LAG(salary, 1)  OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary,
    LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    NTILE(4)    OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

The difference between RANK and DENSE_RANK on ties is a small thing that produces wrong numbers in reports for years if you get it backwards.

Subqueries and CTEs

A subquery is a query inside a query, usable in WHERE, FROM, or SELECT.

SELECT user_id, total_spent
FROM orders
WHERE total_spent > (SELECT AVG(total_spent) FROM orders);

CTEs do the same work and stay readable, which matters when someone else has to change your query in eight months.

WITH monthly_sales AS (
    SELECT MONTH(order_date) AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY MONTH(order_date)
)
SELECT month, revenue
FROM monthly_sales
WHERE revenue > 10000;

Default to CTEs. The readability wins almost every time.

Functions worth knowing

String work: SUBSTRING, CONCAT, REPLACE, TRIM, LEN.

Dates: DATEADD, DATEPART, DATEDIFF, and a healthy suspicion of time zones.

Conditional logic with CASE, and COALESCE for fallbacks:

SELECT
  CASE
    WHEN salary > 100000 THEN 'senior'
    WHEN salary BETWEEN 50000 AND 100000 THEN 'mid'
    ELSE 'junior'
  END AS band,
  COALESCE(nickname, first_name, 'unknown') AS display_name
FROM employees;

EXISTS for checking existence, which is what you want instead of COUNT(*) when the answer is yes or no:

SELECT order_id, customer_id
FROM orders
WHERE EXISTS (
    SELECT 1 FROM customers WHERE customers.id = orders.customer_id
);

PIVOT when someone wants rows as columns, which someone always does.

Then stored procedures

Reusable, parameterized, and the right place for logic that several things call. Worth learning after the above, not before.

---

If you get through that list you can do most of the SQL that most jobs need. The parts that take longer are reading an execution plan and knowing when the elegant query is the slow one, and neither of those comes from a tutorial.