#029Level 6 of 7 · Scenario 5: The Gate Book
Ones, twos and coach parties
Scenario 5 · The Gate Book
The gallery capacity review needs the gate book grouped the way the rooms actually fill: someone on their own, a pair, a small group the stewards can let straight through, and anything big enough to need holding at the door. The gate book records a number. Nobody has ever written the bands down.
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.
- CASE WHEN
- Test conditions in order and return the first match: CASE WHEN x THEN 'a' WHEN y THEN 'b' ELSE 'c' END.
Objective
Return every booking with the visitor's name tidied, the party size, and a band: 'single' for one, 'pair' for two, 'small group' for three or four, 'large group' for five or more. In visit order.
Your query
⌘ ⏎
Results
No result yet.