lesson 16 Calculations & data quality · intermediate

CAST, text types & NULL arithmetic

Convert types and reason about arithmetic with missing values.

the idea

CAST changes a value’s representation. SQLite uses type affinity rather than SQL Server’s strict column-type model. CAST alone is not validation: nonnumeric text can become zero. NULL propagates through arithmetic unless a business rule replaces it.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT id,CAST(price AS TEXT) AS price_text
FROM lab_items;

Watch for: Do not assume CAST rejects malformed data; validate source text first.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Read numeric prices as stored. CAST changes how a value is represented in the result. The stored price stays numeric until you cast it.

   step-1.sql Run

   sql›

   SELECT id, price
   FROM lab_items
   ORDER BY id;

2. 2

   Cast price to text. CAST(price AS TEXT) returns a text form for display or concatenation. It is not validation by itself.

   step-2.sql Run

   sql›

   SELECT id, CAST(price AS TEXT) AS price_text
   FROM lab_items;