#025Level 2 of 7 · Scenario 5: The Gate Book
Booking references
Scenario 5 · The Gate Book
Family tickets get a printed voucher at the desk, and the vouchers have been going out with nothing on them but a number. The desk supervisor wants a reference that says where the party came from at a glance: the first three letters of the town in capitals, a hyphen, then the booking number.
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.
- UPPER
- Force a string to upper case.
- SUBSTRING
- Cut a piece out of a string: SUBSTRING(text, start, length). Counting starts at 1, not 0.
- ||
- Join two strings end to end. Both sides have to be strings.
- CAST
- Convert a value to another type: CAST(id AS VARCHAR) turns a number into text.
Objective
Return a reference in that form for every family ticket, alongside the visitor's name with its spaces stripped. Sort by the reference.
Your query
⌘ ⏎
Results
No result yet.