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.
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.
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.
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?
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.
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.
Step 4 of 9
Capitals count
SQL itself does not care about capitals:
selectworks exactly likeSELECT.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 haveILIKE, 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.
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.
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) orOR(either one).Your turn: Show every column for Latte orders placed in Austin.
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. Soquantity * priceis quantity times price.ASnames the new column. Theorderstable 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.
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
DESCis biggest first. Leave it off (or writeASC) 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 DESCsorts by city, then by price within each city.Your turn: Show the order_id and quantity of every order, biggest quantity first.
Step 9 of 9
Challenge
Recap.
WHEREkeeps the rows that pass a test.quantity * price AS totalworks out a new column and names it.ORDER BY total DESCsorts, 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