#024Level 1 of 7 · Scenario 5: The Gate Book
As it was typed
Scenario 5 · The Gate Book
Admissions has kept the gate book on a terminal at the front desk since it replaced the paper one, and four different people have worked that desk. Names went in however they were typed: some shouted, some whispered, most with a stray space at one end. Marketing wants a mailing list of the larger parties and will not accept it looking like this.
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. Only the ends — anything in the middle stays.
- UPPER
- Force a string to upper case.
- LOWER
- Force a string to lower case.
Objective
Return every booking for a party of three or more, with the visitor's name tidied to upper case and no leading or trailing spaces, and the home town with its spaces stripped. Sort by the tidied name.
Your query
⌘ ⏎
Results
No result yet.