Pierce Technical
All tracks

SQL

Ask questions of data: select, filter, join, and summarize.

Learning plan overview

38 lessonsabout 5 hr 40 min of readingPractice in every lesson

One path from your first SELECT to the patterns analysts use every day. Every lesson uses the same small sales database and ends with a query you write and check right on the page, so there is nothing to install.

What you'll be able to do

  • Pull exactly the rows and columns you need
  • Turn raw rows into totals, averages and rates a report can use
  • Join tables without losing rows or counting them twice
  • Answer ranking, trend and "compared to last month" questions
  • Build tables and change data safely

The path

  1. 1BeginnerLessons 1–13about 1 hr 30 minSelecting, filtering, sorting, NULLs, totals and groups, your first joins, and changing rows.
  2. 2IntermediateLessons 14–28about 2 hr 25 minCASE logic, percentages, dates and text, subqueries and CTEs, EXISTS, and the joins that multiply rows.
  3. 3AdvancedLessons 29–38about 1 hr 50 minWindow functions, ranking, running totals, medians, streaks, pivots, and building tables.
Start lesson 1Know the basics? Skip ahead

Beginner

13 lessons · about 88 min

Start here. No experience needed.

  1. Picking columns with SELECTThe first query everyone writes, and the habits that make it a good one.6 min
  2. Filtering rows with WHEREReturn only the rows that match a condition.7 min
  3. How a query actually runsSQL is written in one order and run in another. Knowing both explains most beginner errors.6 min
  4. Ranges, lists and patternsBETWEEN, IN and LIKE make WHERE clauses shorter and easier to read.7 min
  5. Missing values and NULLNULL means unknown. It never equals anything, and it's behind most wrong counts.7 min
  6. Sorting and limiting resultsORDER BY sorts the rows you get back. LIMIT keeps only the first few.5 min
  7. DISTINCT and aliasesRemove duplicate rows, and give columns and tables short, clear names.6 min
  8. Comments and commenting out codeLeave notes for the next reader, and switch lines off to debug a query one piece at a time.5 min
  9. Data typesA column's type decides what it can hold and how it compares, sorts and adds up.8 min
  10. Calculations and roundingArithmetic row by row, and the four traps behind most wrong numbers in reports.8 min
  11. Counting and totalingCOUNT, SUM, AVG, MIN and MAX turn many rows into one number.9 min
  12. Grouping with GROUP BYOne row per account, per rep or per month, with totals for each.8 min
  13. Filtering groups with HAVINGWHERE filters rows before grouping. HAVING filters the groups afterwards.6 min

Intermediate

15 lessons · about 143 min

For when the basics feel comfortable.

  1. Joining tables with INNER JOINCombine rows from two tables by matching an ID, and know what one row of the result means.9 min
  2. Keeping every row with LEFT JOINKeep all rows from one table, find what's missing, and pick the right join for the question.9 min
  3. Adding, changing and removing rowsINSERT, UPDATE and DELETE, plus the one habit that stops you deleting the wrong data.7 min
  4. If-then logic with CASELabel rows, build your own groups, sort in a custom order and count only the rows you want.10 min
  5. Ratios, percentages and ratesTurn a business question into a formula, and dodge the traps behind most wrong report numbers.12 min
  6. Filling in and creating NULLsCOALESCE swaps a missing value for one you choose. NULLIF turns a value you don't want into NULL.8 min
  7. Converting types with CASTTurn text into numbers, numbers into text, and know what each database does when a value won't convert.8 min
  8. Working with datesFilter, group, shift and measure dates, and avoid the timestamp traps that make month-end numbers wrong.11 min
  9. Cleaning and splitting textChange case, trim spaces, join columns together and pull pieces out of text.10 min
  10. Queries inside queriesUse one query's answer inside another, as a single value, a list, or a whole table.10 min
  11. Breaking a query into steps with WITHName each step of a hard question, then read the query top to bottom like a recipe.10 min
  12. Stacking and comparing resultsUNION stacks two results into one. INTERSECT and EXCEPT find what two results share, or what one has that the other doesn't.9 min
  13. Checking for matches with EXISTSAsk "is there at least one?" or "are there none?" without duplicating rows, and dodge the NOT IN trap.10 min
  14. Full outer, self and cross joinsThree joins for special jobs, reconciling two lists, matching a table to itself and building every combination.10 min
  15. When joins multiply rowsWhy totals come out too high after a join, how to spot it, and the aggregate-first pattern that fixes it.10 min

Advanced

10 lessons · about 108 min

Deeper patterns for real-world work.

  1. Totals beside every row with OVERWindow functions calculate across a group of rows but keep every row, so you can compare each one to its group.11 min
  2. Ranking rows and top N per groupNumber rows within each group with ROW_NUMBER, RANK and DENSE_RANK, then keep the top few or remove duplicates.11 min
  3. Comparing with the previous row using LAG and LEADLook one row back or ahead, to measure change between periods or time spent between events.10 min
  4. Running totals and moving averagesAdd ORDER BY and a frame inside OVER to build year-to-date totals and smooth out bumpy numbers.11 min
  5. Medians, percentiles and bucketsWhen a few big values skew the average, use the median, percentiles and NTILE buckets for a more honest picture.10 min
  6. Recursive CTEs and date spinesA CTE that refers to itself, for generating every date in a range and walking hierarchies of any depth.12 min
  7. Streaks and gapsGroup consecutive rows into streaks, and find the gaps between them, with one subtraction trick.11 min
  8. Turning rows into columnsPivot with SUM and CASE to put categories side by side, and unpivot with UNION ALL to stack them back.10 min
  9. Creating tables with keys and rulesDefine a table's columns, types and constraints, build tables and views from queries, and know which rules your database actually enforces.12 min
  10. Transactions and upsertsMake several changes succeed or fail together, and insert-or-update rows in one statement.10 min

Practice