SQLMaxx
Sign in Start free
N012-H2 Tier 1 · Foundations · hard ecommerce · Brightlane

Return the name and price of any products that match the user's pattern as submitted

Part of BETWEEN, IN, and LIKE in SQL

The problem

A user submitted a product search using the lowercase pattern crest%. The catalogue stores all product names in title case (e.g., Crest 13).

Write a query to return the name and price of any products that match the user's pattern as submitted.

Assumptions:

  • The products table contains every product in Brightlane's catalogue.
  • All Crest-line product names are stored with a capital C; no name in the catalogue starts with a lowercase c.
  • Because the pattern is lowercase and every product name is title-case, no rows match the pattern; the result set is empty.

Output:

  • One row per qualifying product, with columns name and price.
  • The result set will be empty.
Schema · ecommerce 5 tables
categories
id integer
name text
parent_id? integer
products
id integer
name text
category_id integer
price numeric
stock_qty integer
attributes? jsonb
order_items
id integer
order_id integer
product_id integer
quantity integer
unit_price numeric
customers
id integer
name text
email text
city? text
country text
created_at timestamptz
is_active boolean
orders
id integer
customer_id integer
ordered_at timestamptz
status text
total_amount numeric
Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.
Worked solution Try it yourself first
Solution query
SELECT
  name,
  price
FROM
  products
WHERE
  name LIKE 'crest%'

The shape

The correct query is the one that runs the user's pattern as written — LIKE 'crest%' — and returns the empty set, because LIKE is case-sensitive in PostgreSQL and no name in the catalogue starts with a lowercase c.

Clause by clause

  • SELECT name, price returns the two columns the search result would normally surface: the product name and its price. No rows match, so no values land in either column.
  • FROM products reads the catalogue.
  • WHERE name LIKE 'crest%' keeps only the rows whose name starts with the literal text crest followed by zero or more characters. The catalogue stores every Crest-line product with a capital C — Crest Pro 14", Crest Air, and so on — and LIKE compares the pattern character-for-character with the column value. Lowercase c does not match uppercase C, so every row is dropped before SELECT shapes the output.

Why this and not ILIKE 'crest%' or LIKE 'Crest%'

Both alternatives return rows. ILIKE 'crest%' ignores case, so it would match Crest Pro 14" and Crest Air. LIKE 'Crest%' corrects the pattern to title case and matches the same two products. Either would surface the line the user was probably trying to find.

Neither is what the prompt asks for. The task is to run the user's pattern as submitted — 'crest%' — and report what LIKE does with it. What LIKE does is fail to match, because PostgreSQL's default string comparison is byte-for-byte exact on case. The empty result set is the correct answer.

The trap

A user types a search in lowercase, the catalogue stores names in title case, and the LIKE filter returns no rows. There is no error, no warning, no hint that the case mismatch is the cause. The empty result looks the same as a genuine "no such product" result. An analyst running this query against real user input would assume the catalogue doesn't carry the line at all, when actually it does.

The fix is to choose case sensitivity deliberately. If user input should be forgiving, normalise the case — ILIKE is the one-line answer in PostgreSQL. If the search is meant to be strict, leave LIKE in place and document the rule so the empty result is read correctly. The decision is a product decision, not a SQL one; the SQL just executes whichever rule was picked.

You practiced relying on LIKE's default case sensitivity to produce a literal, no-match result. The recurring choice between LIKE and ILIKE is the difference between a strict search and a forgiving one.

How you actually get good at SQL

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.