Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
An admin screen has a search box. Typing a letter should return every customer whose name contains it, regardless of case.
Return the customers whose full_name contains the letter a, ordered by customer_id.
Result columns · in this order
customer_id | The customer. |
full_name | Their name. |
How to approach it
Normalise the column, then match with wildcards on both sides of the letter.
Sample input
| customer_id | full_name | region | signed_up_on |
|---|---|---|---|
| c1 | Ada Okafor | North | 2025-11-02 |
| c2 | Bo Lindqvist | null | 2025-12-14 |
| c3 | Chen Wei | South | 2026-01-03 |
| c4 | Dara O'Neill | North | 2026-01-20 |
| c5 | Eve Marsh | null | 2026-02-01 |
5 rows — all rows shown.
Expected output
| customer_id | full_name |
|---|---|
| c1 | Ada Okafor |
| c4 | Dara O'Neill |
| c5 | Eve Marsh |
3 rows — all rows shown.
Constraints
LIKE is case-insensitive for ASCII; PostgreSQL's is not, and ILIKE is not portable either.Worked example
Ada Okafor, Dara O'Neill and Eve Marsh match; Bo Lindqvist and Chen Wei do not.
LIKE '%a%' happens to work here because SQLite folds ASCII case in LIKE. The same query on PostgreSQL returns only the names with a lowercase a, so it would silently drop Ada on that engine. Lowering the column first makes the intent explicit and the behaviour identical everywhere.
What this tests
Pattern matching with LIKE and its wildcards, plus the portability trap that case sensitivity is engine-defined.
Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.