Three readers, three reports
The owner of Harbour Sports (a made-up shop) wants to know how much money came in. The stock manager wants to know what to reorder. The marketing helper wants to know where the customers live.
All the data is in one set of linked tables. Each person needs a different question asked of it.
By the end of this lesson
Note
I can write a query (a question you ask a table of data) that produces a report for a chosen reader.
Key words
| Word | What it means | Example |
|---|---|---|
| Stakeholder | a person who needs or is affected by the report | |
| Aggregate function | a function that adds up or counts many records | SUM, COUNT |
| Join | a link that brings fields from two tables together | |
| Alias | a plain name you give a column in the report | AS Revenue |
| Calculated field | a column worked out from other columns |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Aggregates turn many rows into one answer
A report seldom shows every sale. It shows a total for each group. An aggregate function works on many records at once. COUNT counts them and SUM adds them. AVG finds the average. MAX and MIN find the biggest and smallest.
GROUP BY makes one answer for each group, such as each product.
Try it: units sold for each product
-- A join brings the product name into the sales.
SELECT products.product_name AS Product,
SUM(sales.quantity) AS Units
FROM sales
JOIN products ON sales.product_id = products.product_id
GROUP BY products.product_id
ORDER BY Units DESC, Product;
-- Change SUM(sales.quantity) to COUNT(*) to count sales instead.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.