DSA Factory
All free lessons
DBMS

Free SQL queries lessons

29 lessons, about 4½ hours in all. Each teaches one idea with a visual, then gives you a problem or a few questions on it. Read the lessons here, then create a free account to practise and keep your progress.

Start free with lesson 1See the full SQL queries path

Stage 0Reading a table

  1. Pick the columns you needAsk a table for just the columns you want with SELECT.7 min
  2. Keep only the rows that passFilter rows with WHERE and a comparison.7 min
  3. Combine conditionsUse AND, OR, NOT and brackets to say exactly which rows you want.8 min
  4. Sort and keep the top fewOrder the answer with ORDER BY and cut it short with LIMIT.8 min

Stage 1Shaping the results

  1. Show each value onceRemove repeated rows from an answer with DISTINCT.6 min
  2. NULL means unknownTest for missing values with IS NULL, and fill them in with COALESCE.9 min
  3. Match text with patternsFind rows by part of a text with LIKE, and join or reshape text with functions.8 min
  4. Compute new columnsCalculate values in SELECT and label rows with CASE WHEN.9 min

Stage 2Counting and grouping

  1. Summarise with COUNT, SUM and AVGTurn many rows into one with aggregate functions, and know what NULL does to them.9 min
  2. One answer per groupSplit rows into groups with GROUP BY and summarise each group.9 min
  3. Filter the groupsKeep only the groups that pass a test with HAVING, and know when WHERE is the right tool.8 min
  4. Rank the groupsSort groups by their total and keep the top ones, minding ties.9 min

Stage 3Joining tables

  1. Bring two tables togetherCombine rows of two tables that belong together with JOIN and ON.9 min
  2. Keep the rows with no matchUse LEFT JOIN to keep unmatched rows, and to find what has no partner.10 min
  3. Join a table to itselfRelate rows of one table to other rows of the same table with aliases.9 min
  4. Join through a link tableQuery a many-to-many relationship by chaining two joins through a middle table.10 min

Stage 4Subqueries and window functions

  1. A query inside a queryUse a subquery in WHERE with a comparison or IN, and beware NOT IN with NULLs.9 min
  2. Does a matching row exist?Test for the presence or absence of related rows with EXISTS and NOT EXISTS.9 min
  3. Build the answer in steps with WITHName an intermediate result with a CTE and reuse it.9 min
  4. Rank within a groupNumber rows inside each group with ROW_NUMBER, RANK and DENSE_RANK.11 min
  5. Running totals and the row beforeAccumulate with SUM over an ordered window, and compare a row with the previous one using LAG.10 min

Stage 5Changing data

  1. Add rows with INSERTPut new rows into a table with INSERT, one at a time or from another query.8 min
  2. Change rows with UPDATEChange columns of the rows that match, and never forget the WHERE.8 min
  3. Remove rows with DELETEDelete just the rows you mean to, and check before you do.8 min
  4. Insert or updateAdd a row, or update it when it already exists, in one statement.9 min

Stage 6Mastery

  1. The second highest, with tiesFind the Nth highest value, counting distinct values, and return NULL when there isn't one.10 min
  2. Find the duplicatesFind values that repeat, and the rows that hold them, even when they differ in case.10 min
  3. Streaks of consecutive daysFind runs of consecutive values with the day-minus-row-number trick.12 min
  4. Top N in each groupPick the top few rows of every group, and choose how ties are treated.11 min

All of SQL queries is free

That’s the whole topic, from the first idea to the mastery stage, with no card needed. Create a free account to practise, keep your progress and carry on to the next topic.

Create your free account