Build and change a database

READ & UNDERSTAND

CAST converts a value; it does not certify that the source was valid. SQLite can turn nonnumeric text into zero. A non-negative CHECK would then accept a made-up zero unless the load inspected the original text first.

This tiny practice file uses an explicit rule: daily_rate must be nonempty text containing only unsigned integer digits. That rejects negative values, decimal notation and unknown text. It is a rehearsal rule, not a universal currency parser. Use your client dictionary to define the real accepted formats and ranges.

Validate syntax and business meaning separately, and keep the source row and reason for anything excluded. Do not add OR IGNORE just to make a script finish: that can hide which constraint rejected which row. With default ABORT behavior, a constraint failure rolls back the failing INSERT statement, not earlier successful statements in the script.

SELECT serial, daily_rate, CAST(daily_rate AS INTEGER) AS converted_rate FROM stg_items ORDER BY rowid;

Your question

List serial and daily_rate for source rows that violate the practice rate-text rule, ordered by rowid. Reject blanks and any character outside 0–9.

Explain your evidence

Which malformed value becomes zero after CAST? Why would checking only the converted value lose evidence?

Try a query to unlock the worked-answer discussion.