The owner asked one question
The owner asks: "Which drinks do my Hillside customers buy?" The records of the shop hold every sale, every customer and every item.
Your job is not to show all of it. It is to pick the part that answers her question, and shape it so she can read it.
Key words
| Word | What it means |
|---|---|
| Subset | a part of the data, picked because it is needed |
| Data wrangling | picking, joining, tidying and reshaping data so it can be used |
| Join | a link that puts rows from two tables side by side |
| Aggregate | to combine many rows into one value, such as a total |
| Query | a question you ask of data, written in SQL |
| SQL | a language for asking questions of data kept in tables |
| Requirement | something the finished work must do |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can pick the data a brief needs, join and total it, and refine the model when it can not answer the question.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Real spreadsheets are used on site
Important
The real task may ask for spreadsheet formulas such as lookups and totals. This platform has no spreadsheet. Here the same jobs are done in SQL. Practise the spreadsheet version on site.
Worked example: from question to query
| Step | For "drinks bought by Hillside customers" |
|---|---|
| 1 What is asked? | Which drinks, and which customers |
| 2 Which tables hold it? | customer (town), sale, sale_line, item (category and name) |
| 3 How do they join? | customer to sale to sale_line to item, by the keys |
| 4 Which rows? | town is Hillside and category is drink |
| 5 Which columns? | only the customer name and the item name |
The cells students fill in are left blank in this preview.
Run it: the subset
SELECT DISTINCT customer.name, item.item_name
FROM customer
JOIN sale ON sale.customer_id = customer.customer_id
JOIN sale_line ON sale_line.sale_id = sale.sale_id
JOIN item ON item.item_code = sale_line.item_code
WHERE customer.town = 'Hillside' AND item.category = 'drink'
ORDER BY customer.name, item.item_name;Totals by group
To total, aggregate: GROUP BY puts rows in groups, and SUM adds one column in each.
Spend on a line is quantity times price. Keep money in whole pence, and turn it into pounds only on the final page.