Top of the list, and how many
The shop manager wants two quick answers. Which products cost the most? And how many sales were made this week?
One answer needs a sorted list. The other needs a count. Both take one short line of code.
Key words
| Word | What it means |
|---|---|
| Database | an organised store of tables that you can ask questions |
| SQL | the language used to ask a database for data |
| Query | one question written in SQL |
| ORDER BY | sorts the rows by one or more columns |
| ASC | ascending: smallest or A first. This is the usual order. |
| DESC | descending: largest or Z first |
| COUNT | gives how many rows there are |
| Alias | a new name for a column in the answer, made with AS |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can sort rows with ORDER BY and count rows with COUNT.
Starter
Have a go
Starter
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.
ORDER BY sorts the answer
ORDER BY goes near the end of a query. Name the column to sort on. Add DESC for largest first. Without DESC, the order is ascending.
To sort on two columns, list both. The second column breaks ties in the first.
Try it: sort the products
SELECT name, price_pence FROM products
ORDER BY price_pence DESC;
-- Two columns: category first, then dearest first inside each category.
SELECT category, name, price_pence FROM products
ORDER BY category, price_pence DESC;
-- Remove DESC from the first query and run it again.
Check
Have a go
Check
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.