There are several rationales in SQLite for defining datatypes, or as SQLite calls them, "storage classes".
If you read this page, it provides an insight into both the function of and reasoning behind SQLite datatypes. In particular, the following sections of that page are interesting.
However, the dynamic typing in SQLite allows it to do things which are
not possible in traditional rigidly typed databases.
I.e. like the C programming language, SQLite allows you to do very stupid things because it also allows you to do very clever things - compare C with Java and SQLite with other RDBMSs for example.
A storage class is more general than a datatype.
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.
And so for the most part, "storage class" is indistinguishable from
"datatype" and the two terms can be used interchangeably.
N.B. "for the most part..."
In order to maximize compatibility between SQLite and other database
engines, and so that the example above will work on SQLite as it does
on other SQL database engines, SQLite supports the concept of "type
affinity" on columns. The type affinity of a column is the recommended
type for data stored in that column. The important idea here is that
the type is recommended, not required.
So, a very good reason for this genericity is backwards compatibility with virtually all engines on the market - and it the default doesn't suit you, you are free to change the code (Public Domain licence). Another reason why SQLite is the world's most popular RDBMS!
Yet another interesting titbit:
A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation. (As an internal optimization, small floating point
values with no fractional component and stored in columns with REAL
affinity are written to disk as integers in order to take up less
space and are automatically converted back into floating point as the
value is read out. This optimization is completely invisible at the
SQL level and can only be detected by examining the raw bits of the
So, a column with REAL affinity (as opposed to INTEGER) actually does make a functional difference to the results of a query and/or calculation. For example, the string '456.567' (SQLite stores BLOBs and numbers as binary just like every other SQL database engine) would be treated as 456.567 for NUMERIC calculations, but no attempt would be made to coerce '6&%$$$#3' into any sort of NUMERIC.
An important assumption that SQLite makes is that the programmer is king and not the engine! If, for some reason, you want to convert '6&%$$$#3' to an INTEGER, then you can make up your own algorithm to so, but SQLite assumes that if you wish to treat '6&%$$$#3' as an INTEGER, then you must have a good reason for making that choice!
A succinct way of putting this is that SQLite gives you very powerful tools, but like any powerful tool, one has to exercise caution and restraint. Basically, SQLite says "you are an adult - if you want to shoot yourself in the head with the nailgun I've given you, go ahead, but don't blame me if you get hurt!".
Apart from the SQLite page, these links are also helpful (1, 2).