A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity.The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation.
But that term was easy to confuse with "no affinity" and so it was renamed.) A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB.
If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.
A column with NUMERIC affinity may contain values using all five storage classes.
When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible.
For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved.
If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer.Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing.With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored. In SQLite, the datatype of a value is associated with the value itself, not with its container.The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite.However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.