Practise · The invalid rate hides behind zero

Opening your saved work…

The reading

CAST converts; it does not certify

TG-105's rate arrived as the word unknown. CAST(daily_rate AS INTEGER) turns it into 0, with no error and no warning. CAST reads digits from the start of the text and stops at the first character that does not fit; when there are no digits at all, the answer is zero.

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

TG-105's unknown becomes 0, and nothing in the converted column says it was ever a word. Open in the playground

items has a CHECK on daily_rate: an integer, zero or more. That CHECK sees the converted 0 and accepts it, because a CHECK can only see the value it is handed, never the text in the file. An item that rents for nothing a day enters the database, and every report built on it treats the gear as free. TG-104's -3 is refused, but only because this rule happens to forbid negatives.

So test the text before you convert it. The rehearsal's rule: after TRIM, the rate is not empty and every character is a digit from 0 to 9. TRIM(daily_rate) = '' OR TRIM(daily_rate) GLOB '*[^0-9]*' finds the rows that break it. GLOB matches a pattern: * stands for any run of characters, and [^0-9] for one character that is not a digit. TG-104 breaks the rule with its minus sign, TG-105 with every letter.

SELECT serial, daily_rate
FROM stg_items
WHERE TRIM(daily_rate) = ''
   OR TRIM(daily_rate) GLOB '*[^0-9]*'
ORDER BY rowid;

Two rows break the rule: the minus sign and the word. Open in the playground

The rule is this rehearsal's, not a universal one. It refuses decimals, which suits whole-dollar daily rates and would be wrong for a wage with cents. Your client's dictionary says what a valid value looks like, and your rule follows it. Whatever the rule, keep the rejected row and its reason: load_audit gives both TG-104 and TG-105 the reason 'Rate is not an unsigned whole number'.

Rule of thumb. Test the text, then convert it. A CHECK sees only what CAST handed it.

Going deeper (optional, not on the exam)

GLOB is case-sensitive and uses * and ? where LIKE uses % and _. LIKE has no brackets, so it cannot say one character that is not a digit.

CAST('21,50' AS REAL) is 21.0. The conversion stops at the comma, and a wage written with a decimal comma loses its cents without an error.

Do not add OR IGNORE to make a load script finish. INSERT OR IGNORE skips every row that breaks a UNIQUE, NOT NULL or CHECK constraint without saying which row or which rule, so the count comes out short and the evidence is gone. It does nothing for a foreign key, which still refuses the statement.

By default a failed constraint aborts the one statement that hit it, so an INSERT ... SELECT that meets one bad row loads none of its rows. Statements that ran earlier in the script stay done. That is SQLite's ABORT behaviour, and it is why the load filters the bad rows out with a WHERE instead of waiting for them to fail.

The invalid rate hides behind zero · CAST, GLOB, why a CHECK cannot see the source · level 6 of 6

Wages with cents

Level 6: Transfer it · Exercise 1 of 1

Same idea as The invalid rate hides behind zero, new data.

Northshore Coffee's new-hire file is staged in stg_new_hires, every column as text. Its dictionary says a wage is written with digits and a decimal point, such as 15.00, and nothing else. List first_name, last_name and hourly_wage for the hires whose wage text breaks that rule, in file order.

Finish line: 2 rows · columns first_name, last_name, hourly_wage · in the order the question gives

Data: Northshore Coffee.

I would like help with this exercise

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

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.