Read the Manual

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Yelp

Problem

For the content team's tally of how-to walkthroughs, how many items in content_items have 'how' anywhere in their title, whatever its capitalization?

Some titles promise to walk you through it. Count the ones that say so out loud.

Tables

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

Expected output

Worked solution and explanation

It looks trivial. Two ways to quietly get it wrong.

Strip the costume and this is a case-insensitive substring count; the whole game is what 'contains how' means. Every matching title in content_items is a 'How to ...' walkthrough, capital H included, and no other title carries those letters. So the two quiet failures are stark. Test equality (title = 'how') and you match nothing, because the word lives inside longer titles. Match the substring but never fold case, and on any engine whose pattern match respects case, a bare LIKE '%how%' skips all 20, because every one is spelled 'How'. The count only lands when 'how' means h-o-w anywhere in the title, in any case. The tell of a junior answer is assuming the stored text matches the way the question was typed.

Break down the requirements

Step 1: Fold case, then match 'how' anywhere

WHERE LOWER(title) LIKE '%how%' lowercases each title before the test, so 'How to Data Science' becomes 'how to data science' and matches. The leading and trailing % make it a substring test rather than an exact match, and LOWER() is what keeps the capital H from slipping through.

Step 2: Collapse the matches to one number

COUNT(*) with no GROUP BY folds the surviving rows into a single value: the one-row, one-column howto_count the content team reads.

The solution

SELECT COUNT(*) AS howto_count
FROM content_items
WHERE LOWER(title) LIKE '%how%'

Common follow-up questions

• What if you needed the count per `content_type`? (Add `GROUP BY content_type` to break down how-to titles by format.)
• How would you handle titles with HTML entities like '&how'? (Tests data cleaning awareness before pattern matching.)
• What if the search needed to match only the whole word 'how' and not substrings like 'showcase'? (`LIKE '%how%'` matches the letters anywhere, so it also catches 'showcase' or 'somehow'; matching the standalone word needs word-boundary handling.)