Data Types and NULL

SQLite's Type System

SQLite has a unique approach to types called type affinity. Unlike most databases that enforce strict column types, SQLite is flexible: a column declared as INTEGER can store text, and a column declared as TEXT can store a number. The declared type is just a hint that influences how SQLite coerces values.

Storage Classes

SQLite has five storage classes — the actual types values are stored as:

Type Affinity

When you declare a column with a type name, SQLite maps it to one of five affinities: TEXT, NUMERIC, INTEGER, REAL, or BLOB. This determines how SQLite tries to coerce inserted values:

-- All of these declare INTEGER affinity
CREATE TABLE t (a INT, b INTEGER, c TINYINT, d BIGINT);

-- TEXT affinity
CREATE TABLE t (a TEXT, b VARCHAR(100), c CHAR(10));

-- REAL affinity
CREATE TABLE t (a REAL, b FLOAT, c DOUBLE);

-- NUMERIC affinity (can store either integer or real)
CREATE TABLE t (a NUMERIC, b DECIMAL(10,2));

TYPEOF()

The TYPEOF() function returns the storage class of any value:

SELECT
  TYPEOF(1),          -- 'integer'
  TYPEOF(1.5),        -- 'real'
  TYPEOF('hello'),    -- 'text'
  TYPEOF(NULL),       -- 'null'
  TYPEOF(x'ff00');    -- 'blob'

You can use TYPEOF() on column values to inspect what SQLite actually stored:

SELECT name, price, TYPEOF(price) FROM products;

NULL in SQLite

NULL represents a missing or unknown value. It propagates through expressions:

SELECT NULL + 1;     -- NULL
SELECT NULL = NULL;  -- NULL (not TRUE!)
SELECT NULL OR TRUE; -- TRUE
SELECT NULL AND FALSE; -- FALSE

Key rules:

• NULL = NULL is NULL, not TRUE. Use IS NULL to check for null.
• Any arithmetic with NULL returns NULL.
• Aggregate functions (COUNT, SUM, etc.) ignore NULL values, except COUNT(*).

COALESCE and IFNULL

Replace NULL with a default value:

-- COALESCE returns the first non-NULL argument
SELECT COALESCE(NULL, NULL, 42); -- 42

-- IFNULL is a SQLite shorthand for COALESCE with two arguments
SELECT IFNULL(NULL, 'default'); -- 'default'

-- Practical use: treat NULL prices as 0
SELECT name, IFNULL(price, 0) AS price FROM products;

Your Task

Use TYPEOF() to select the name, price, and the type of the price column as price_type from all products.