A cafe that wants to know
Sunrise Cafe is a made-up cafe with four drinks and a week of orders. The owner asks three things.
Which drink earns the most? Should I put more effort into takeaway or eat in? How do I keep this data in good order? You will plan, ask, show and look after.
By the end of this lesson
Note
I can plan an analysis, make a report, lay out a dashboard and plan its upkeep for one organisation.
Key words
| Word | What it means | Example |
|---|---|---|
| Information requirement | what the reader needs to know, and why | |
| Query | a question you ask a table of data | |
| Aggregate function | a function that adds up or counts many records | SUM |
| Dashboard | one screen that shows key numbers and charts | |
| Schedule | a plan that says what is done, by whom and how often | |
| Backup | a spare copy of data kept in case of loss | |
| Archive | to move old data to separate storage |
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.
Step 1: write the information requirement
Start with the question and not the chart. Write down the problem, who needs the answer, what they will decide, and which data you need.
Then check that the data exists and who may see it. Only then write a query.
The requirement for Sunrise Cafe
| Part | Sunrise Cafe |
|---|---|
| Problem | The owner does not know which drinks earn the most |
| Reader | The owner, who is busy and wants one page |
| Decision | Which drinks to promote |
| Data needed | drinks and orders tables |
| Who may see it | The owner and the manager only |
The cells students fill in are left blank in this preview.
Step 2: make the report
-- Revenue for each drink, biggest first. One # is 3 pounds.
SELECT drinks.drink_name AS Drink,
SUM(drinks.price * orders.quantity) AS Revenue,
REPLACE(HEX(ZEROBLOB(SUM(drinks.price * orders.quantity) / 3)), '00', '#') AS Chart
FROM orders
JOIN drinks ON orders.drink_id = drinks.drink_id
GROUP BY drinks.drink_id
ORDER BY Revenue DESC, Drink;
-- Add a WHERE orders.place = 'Takeaway' line before GROUP BY and run it again.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.