SQL Quest

Lessons, hints, explained solutions, the in-browser PostgreSQL editor and practice answer checks in your browser are available. Graded assessments, recorded progress from answer checks and new certificates aren't available yet.

Retrieving and Filtering Data

Most SQL written at work is a SELECT with a good WHERE clause. In this track you learn to calculate new columns, name them, remove duplicates, filter rows with comparisons, ranges, lists and patterns, handle NULL correctly, and sort and page through results. You practise on Corner Market's tiny tables, then on Wadi Market's online store and Masar Holding's HR data, where the traps are real: missing emails, cities typed three different ways, coupon codes with stray spaces and timestamps that make 'orders on 31 December' harder than it looks.

Start the track

Lessons

  1. Calculated columnsCompute new values in the SELECT list with arithmetic, text and date expressions, and understand how data types change the answer.16 min · 4 exercisesFree
  2. Column and table aliasesRename result columns and tables with AS, quote names that need spaces or capitals, and learn why WHERE cannot see an alias.14 min · 4 exercisesFree
  3. Removing duplicates with DISTINCTList the different values (or combinations of values) in a table with SELECT DISTINCT, and understand exactly what it treats as a duplicate.12 min · 4 exercisesFree
  4. Filtering rows with WHEREKeep only the rows you need with a WHERE condition, and write text, number and true/false values correctly.14 min · 4 exercisesFree
  5. Comparison operatorsFilter with =, <>, <, >, <= and >= on numbers, text, dates and timestamps, and compare one column with another.16 min · 4 exercisesFree
  6. Combining conditions: AND, OR, NOTCombine several conditions in one WHERE clause with AND, OR and NOT, and translate business requests into the right logic.15 min · 4 exercisesFree
  7. Operator precedence and parenthesesUnderstand why AND is evaluated before OR, spot the classic mixed AND/OR bug, and use parentheses to say exactly what you mean.15 min · 4 exercisesFree
  8. Ranges and lists: BETWEEN and INFilter by a range with BETWEEN and by a list of values with IN, and avoid the two traps that come with them: timestamps at the end of a range and NULL inside NOT IN.16 min · 4 exercisesFree
  9. Pattern matching with LIKE and ILIKEFind text that starts with, ends with or contains something using LIKE and ILIKE, the % and _ wildcards, and escaping when the data itself contains % or _.16 min · 4 exercisesFree
  10. NULL and three-valued logicUnderstand what NULL means, why = NULL never works, how TRUE / FALSE / UNKNOWN combine, and filter missing values correctly with IS NULL and IS DISTINCT FROM.20 min · 4 exercisesFree
  11. Sorting results with ORDER BYSort results by one or more columns, in either direction, control where NULLs go, and make the order fully predictable with tie-breakers.15 min · 4 exercisesFree
  12. Top-N and paging with LIMIT and OFFSETReturn only the first rows of a sorted result with LIMIT, page through results with OFFSET, include ties with FETCH FIRST … WITH TIES, and avoid the pagination pitfalls.15 min · 4 exercisesFree
  13. Filtering dates and timesFilter by days, months, years and times of day correctly: half-open ranges for periods, date functions for parts of a date, and computed boundaries with intervals.20 min · 4 exercisesFree
  14. Filtering messy text and calculated numbersBring everything together on real data: normalise inconsistent text before comparing it, filter on calculated amounts and percentages, and know when decimals compare exactly.20 min · 4 exercisesFree