Module 09 Activity

Scenario

Your city breakdown shows New Delhi in fourth place, behind Mumbai, Bengaluru, and Chennai. The business insists New Delhi is the largest market. Both of you are reading the same table.

Task

1. Produce the raw city counts and look at the top rows carefully. Then look again.
2. Identify every distinct spelling that refers to the same city.
3. Write a normalisation rule as an expression, not as a manual list of corrections.
4. Produce the counts again with the rule applied.
5. Record the distinct city count before and after.

Deliverable

A normalisation note: the rule as SQL, the before-and-after top-five table, and one sentence on what would happen to this rule if a new spelling arrived next month.

Check your work

Raw counts put Mumbai (1,070), Bengaluru (1,069), and Chennai (1,069) ahead of New Delhi. But New Delhi is split across three spellings that a GROUP BY treats as three separate cities:

Applying LOWER(TRIM(city)) merges them into 1,604 - making New Delhi the largest market by a clear margin, ahead of Mumbai's 1,070. The business was right; the query was wrong.

The trailing-space variant is the one that matters most, because it is invisible. Two values that look identical on screen group separately, and nothing warns you.

The sentence that matters

Your last deliverable line is the honest one: LOWER(TRIM(city)) handles case and padding, and nothing else. It will not catch 'N. Delhi', 'New Delhi' with a double space, or a misspelling. A normalisation rule is a stated assumption with known gaps, not a guarantee - write it down as one, and apply it in exactly one place so it can be changed in exactly one place.

Mark complete