SQL Data Types
A type describes what a column is meant to store and which operations make sense for it. Choose types from the meaning and range of the data, not just from the shortest example that accepts an INSERT.
Understand the idea
Use whole numbers for counts, an exact decimal or integer minor units for money, text for labels, and a deliberate date or timestamp representation for time. SQLite uses storage classes and type affinity; server databases generally enforce more fixed column types. SQLite STRICT tables add stronger type checks but accept a limited set of type names. A column named price is not enough to tell a reader its currency or units.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT typeof(42) AS whole_number,
typeof(42.5) AS approximate_number,
typeof('42') AS text_value,
typeof(NULL) AS missing_value;
Read the result
SQLite reports integer, real, text, and null. The text '42' looks numeric to a person but is a text literal here. Type affinity can affect how values are stored when inserted into a declared column.
Try one change
Choose types and constraints for a product identifier, unit count, price, email, and creation instant. Explain your choice of time zone and monetary precision.
Match the type to the fact
| Fact | Practical choice | Check before storing |
|---|---|---|
| Count | INTEGER or an appropriate server integer type | Range, non-negative rule, and NULL policy |
| Money | Integer minor units or exact DECIMAL | Currency, precision, rounding, and overflow |
| Human label | Unicode-capable text | Length, collation, and empty values |
| Calendar date | Native DATE, or consistent ISO text in SQLite | Invalid dates and local calendar meaning |
| Event instant | Appropriate timestamp representation | Time zone, precision, and conversion at the interface |
| Yes/no flag | Engine boolean type or constrained integer | Whether unknown is an allowed third state |
| Binary data | BLOB or engine binary type | Size limits and whether external object storage fits better |