Lesson 9 · Clean and reshape text

Clean text without changing the evidence

Task 1 of 2 · deliveries · 0 results checked · 0 explanations saved

Opening your saved work…

Read the idea

One addresses row is one recorded location; one scans row is one tracking event. A scalar function transforms a value for each row. An aggregate summarizes rows. LOWER(name) does not mean the same kind of operation as COUNT(name), even though both use parentheses.

TRIM removes ordinary spaces from the ends; LOWER and UPPER standardize letter case. Nest functions from the inside out: LOWER(TRIM(type)) trims before changing case. These SELECT expressions change the report, not the stored value. Keep the original beside the cleaned output so another analyst can check what changed.

Case cleanup is not identity matching: two addresses with the same normalized label can still be different locations. SQLite’s built-in LOWER/UPPER cover ASCII letters; do not assume this is complete international name normalization.

Worked example

SELECT id, type, LOWER(TRIM(type)) AS clean_type FROM addresses ORDER BY id LIMIT 5;

The first five address records keep their identifiers and raw types. A different clean_type reveals a display transformation you can inspect.

Re-run SELECT id, type FROM addresses to inspect the stored values. Cleaning for a report and editing source evidence are separate decisions.

Your task

Return id, name, and LOWER(TRIM(name)) AS login_name for every driver. Order by id. Predict the number of rows using the schema.

Predict or plan before trying

Give me a hint

Keep the original name and calculate a separate output column. No GROUP BY is needed.

Compare with a worked answer

SELECT id, name, LOWER(TRIM(name)) AS login_name FROM drivers ORDER BY id;

Using an answer is useful practice. Record what you learned; it does not demonstrate independent mastery.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Run your query to see the result here.

Explain your evidence

What evidence would show that this SELECT did not overwrite a driver’s name? Why should a login identifier need more checks than lowercasing?

My explanation or revision I would like help with this task

Your instructor can review account-saved work and help requests. Saving does not submit to Canvas.