#030Level 7 of 7 · Scenario 5: The Gate Book
The coach service
Scenario 5 · The Gate Book
The museum has been quietly subsidising a coach from one town for two years, and nobody now working there can remember which town or why. The trustees have asked the obvious question before they renew it: where do our visitors actually come from? Four hundred and twelve bookings are in the gate book, typed by four different people over two years, and not one of them was ever checked.
Schema · 1 table
visits
- idpkINTEGER
- visitor_nameVARCHAR
- home_townVARCHAR
- ticket_typeVARCHAR
- priceDOUBLE
- party_sizeINTEGER
- visited_atTIMESTAMP
Toolkit
- SELECT
- Choose which columns come back.
- FROM
- Specify which table to read.
- WHERE
- Filter rows by a condition.
- ORDER BY
- Sort the results by a column.
- DESC
- Reverse the sort: largest or latest first.
- COUNT(*)
- How many rows there are.
- SUM
- Add up a numeric column.
- GROUP BY
- Split the rows into groups first, then aggregate each one.
- TRIM
- Strip whitespace from both ends of a string.
- UPPER
- Force a string to upper case.
- COUNT(DISTINCT x)
- How many different values a column holds.
Objective
Name the town that sends the museum the most people. Not the most bookings — the most people. Run as many queries as you need, and read the walkthrough afterwards even if you get it first time.
Your query
⌘ ⏎
The town
Results
No result yet.