Three questions before lunch
You have a Saturday job at a small online gift shop. On Monday the manager sends three questions. What is nearly sold out? How many orders are still waiting? How much did we really take?
The answers are all in the shop's database. One short query each finds them in a second.
By the end of this lesson
Note
I can answer a manager's question with a query.
Key words
| Word | What it means | Example |
|---|---|---|
| Query | a question you ask a database in SQL | |
| Report | an answer set out for somebody to act on | |
| Condition | a test each row passes or fails | stock < 5 |
| Aggregate | a function that turns many rows into one value | SUM |
| Group | the rows that share one value | GROUP BY status |
| Alias | a new name for a column in the answer | AS orders |
The cells students fill in are left blank in this preview.
Sign in to the shop
Start the lab if it is not running. If your terminal is new, press these two Runs again. They let the terminal trust the lab's server and make the short name sql.
Trust the lab's server
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for signing in
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Turn the question into a condition
"What is nearly sold out?" is too vague for a database. Decide what "nearly" means first. Say fewer than 5 left.
Then the query writes itself. WHERE stock < 5 keeps those rows. ORDER BY stock puts the emptiest first. LIMIT 3 keeps only the first three rows.
Which products are nearly sold out?
sql "SELECT name, stock FROM products WHERE stock < 5 ORDER BY stock, name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The three dearest products
sql "SELECT name, price FROM products ORDER BY price DESC LIMIT 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
An aggregate turns rows into one number
"How many orders are waiting?" wants one number, not a list. COUNT(*) counts the rows that pass the condition.
GROUP BY goes one step further. It splits the rows into groups that share a value, then counts each group. One query answers "how many of each?"
How many orders are waiting?
sql "SELECT COUNT(*) AS waiting FROM orders WHERE status = 'pending'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
How many orders of each status?
sql "SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY orders DESC"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Where do the customers live?
sql "SELECT country, COUNT(*) AS customers FROM customers GROUP BY country ORDER BY customers DESC, country"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Try it: a café till
-- COUNT(*) counts the rows: how many times each item was rung up.
-- Change COUNT(*) to SUM(qty) and press Run: now it counts the items sold.
SELECT item, COUNT(*) AS sales
FROM sales
GROUP BY item
ORDER BY sales DESC, item;Check the answer fits the question
A query can run with no error and still give the wrong answer. "How much did we take?" means money that really came in.
A cancelled order was never paid. So the first Run below adds up every order line, and the second leaves the cancelled orders out.
Every order line added up
sql "SELECT SUM(quantity * unit_price) AS takings FROM order_items"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The takings without cancelled orders
sql "SELECT SUM(order_items.quantity * order_items.unit_price) AS takings FROM order_items JOIN orders ON orders.id = order_items.order_id WHERE orders.status <> 'cancelled'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think a query that runs is a query that is right. The database only does what you wrote. Before you send a report, say in one line which rows it counts and which it leaves out, such as "not counting cancelled orders".
Going further: takings by year
YEAR(ordered_at) turns a date and time into just the year. Group by it to compare the shop's years.
Write a query in the lab that shows the takings for each year, with the cancelled orders left out. Which year was best, and why might 2023 look small?