INTERACTIVE LECTURES
SQL functions & delivery evidence
Guided class activity · apply the ideas from the full SQL lessons.
Preparing for Lab 4 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Clean text without changing the evidence
Step 1 of 7 · Database: North Shore Deliveries
READ & UNDERSTAND
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 question
Return id, name, and LOWER(TRIM(name)) AS login_name for every driver. Order by id. Predict the number of rows using the schema.
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?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.