Free lesson

Just the rows you need

A manager wants only their shop's orders. Pick rows by a rule, work out totals, and put them in order.

8 min9 steps

In this lesson you will

  • Keep only the rows that match a rule
  • Combine rules with AND and OR
  • Work out new columns and sort the result

Words you'll meet

Filter
Keep only the rows that pass a test.
WHERE
The part of a query that holds the test.
ORDER BY
The part of a query that sorts the result.
Start the lesson

The lesson, step by step

Each step teaches one idea, then hands you a real database to try it on. The exercises run in your browser.

  1. Step 1 of 9

    A request

    Most requests a data engineer gets are not for a whole table. They are for a slice of it: one shop, one month, one product.

    An email arrives from the manager of the Austin shop:

    Can you send me this week's orders? Just mine, please.

    You have one table holding every shop's orders.

    Your turn: What should you do?

  2. Step 2 of 9

    Be the filter

    A filter works through a table one row at a time. For each row it asks a single yes-or-no question, the rule, and keeps the row only if the answer is yes.

    Here the rule is "is the city Austin?". Before the computer does it, do it yourself once.

    Your turn: Click every order from Austin, then press Check.

  3. Step 3 of 9

    WHERE

    In SQL, the rule goes after the word WHERE. Text goes in single quotes.

    SELECT * FROM orders WHERE item = 'Mocha'

    The database checks every row against the rule, exactly as you just did, and keeps the ones that pass.

    Text comparisons are exact. 'austin' does not match 'Austin', and neither matches 'Austin ' with a space on the end.

    Messy capitals and stray spaces are a common reason rows go missing from a report. You will fix exactly that in a later lesson.

    Your turn: Answer the email: show every column for the orders where the city is Austin.

  4. Step 4 of 9

    Capitals count

    SQL itself does not care about capitals: select works exactly like SELECT.

    Your data is different. To a database, 'Austin', 'AUSTIN' and 'austin' are three different pieces of text. A new till in the Austin shop types city names its own way, and two of its orders just arrived. WHERE city = 'Austin' would silently miss them.

    The fix: turn the text into lower case before comparing. LOWER(city) does that, so it matches however the city was typed:

    SELECT * FROM orders WHERE LOWER(city) = 'austin'

    UPPER() works the same way in the other direction. Many databases, including the one you are using, also have ILIKE, a case-insensitive match: WHERE city ILIKE 'austin'.

    Comparing with LOWER() is a patch at the point of use. The proper fix is to clean the values once, in the pipeline, so every table downstream already agrees on one spelling.

    Your turn: Show every column for all the Austin orders, however the city was typed.

  5. Step 5 of 9

    Rules with numbers

    (We tidied the two oddly typed orders away, so the rest of the lesson uses the original eight.)

    Head office wants large orders double-checked, in case someone mistyped a quantity.

    Numbers need no quotes. You can compare with =, <, >, <=, >= and <> (not equal).

    Your turn: Show every column for orders with a quantity of 2 or more.

  6. Step 6 of 9

    Two rules at once

    The Austin manager writes back:

    Thanks! Actually, I only need the lattes.

    Combine rules with AND (both must be true) or OR (either one).

    Your turn: Show every column for Latte orders placed in Austin.

  7. Step 7 of 9

    Work it out

    Next email:

    What did each order come to?

    The table has the quantity and the price of one drink, but no total. Nobody typed one in, and nobody needs to, because it can be worked out:

    SELECT order_id, quantity * price AS total FROM orders

    Careful: this * is not the one you met before. Alone after SELECT, * means every column. Between two values, it means multiply. So quantity * price is quantity times price.

    AS names the new column. The orders table itself is not changed. Your query describes a new table built from it, which is exactly what each step of a pipeline does.

    Why not just store the total? Because a stored copy of something that can be worked out goes stale. Correct the quantity and the stored total is now wrong, unless someone remembers to fix it too.

    The rule of thumb: store the facts, calculate the rest. Pipelines do sometimes save calculated results for speed, and then keeping them correct becomes the pipeline's job.

    Your turn: Show the customer and the order total (quantity times price) for every order.

  8. Step 8 of 9

    Put it in order

    Which were the biggest orders?

    A table has no natural order. If order matters, you have to ask for it:

    SELECT * FROM orders ORDER BY price DESC

    DESC is biggest first. Leave it off (or write ASC) for smallest first.

    Without ORDER BY, a database may hand rows back in any order, and that order can change from one run to the next. Code that relies on an accidental order is a classic bug.

    You can sort by more than one column: ORDER BY city, price DESC sorts by city, then by price within each city.

    Your turn: Show the order_id and quantity of every order, biggest quantity first.

  9. Step 9 of 9

    Challenge

    Recap. WHERE keeps the rows that pass a test. quantity * price AS total works out a new column and names it. ORDER BY total DESC sorts, biggest first. They go in that order: SELECT, FROM, WHERE, ORDER BY.

    One last email from Austin:

    Can I get my orders with their totals, biggest first?

    No hints on this one.

    Your turn: For Austin orders only, show order_id and the order total (quantity times price), largest total first.

Reading is the easy part.

Every step above has an exercise. Do it for real, free, no account needed.

Start the lesson