INTERACTIVE LECTURES
Build and change a database
Guided class activity · apply the ideas from the full SQL lessons.
Open the detailed lessons for this activity
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
Preparing for Lab 10 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Preserve raw text before deciding what to clean
Step 5 of 9 · Database: Trail Gear: build rehearsal
READ & UNDERSTAND
The sqlite3 command-line .import command is not SQL. When its target does not exist, the CSV header supplies column names and SQLite creates text columns. Keep that staging copy while developing and auditing the transformation.
A blank CSV field arrives as an empty string. If blank means unknown for a field, NULLIF(TRIM(field), '') converts whitespace-only text to NULL. Apply that only where the dictionary supports it: a required identifier should not quietly become an accepted unknown.
Trail Gear keeps stg_items and load_audit for inspection. Its raw staging table deliberately has no application primary key; its rowid is used to trace this fixed import. Your client application tables still need the keys required by the ERD. The browser copy contains original fictional data and does not accept client uploads.
Worked example
SELECT serial, note, typeof(note) AS storage FROM stg_items ORDER BY rowid;
Every imported note starts as text, even a blank cell.
The SELECT previews a transformation without changing the raw evidence. Only accepted rows are later inserted into items.
Your question
Return serial and NULLIF(TRIM(note), '') AS clean_note for all six source rows, ordered by rowid.
Explain your evidence
Which source rows become NULL? Why should the raw staging copy stay available until the decisions are explained?
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.