Chapter 3: SQL Data Types

Understanding data types

Every column in a table has a data type, and the data type determines what kind of data the column can store. Different data types are designed for different kinds of data, such as numbers, dates, and strings. You need to understand them to design tables and queries that work well with the data in your database.

Common SQL data types

Some of the most common data types in SQL are:

• INTEGER for whole numbers, such as 1, 2, and 3.
• FLOAT or REAL for decimal numbers, such as 1.23 and 4.56.
• CHAR or VARCHAR for character strings, such as “hello” and “world”.
• DATE for dates, such as “2022-03-14”.

SQL has many other data types, depending on the database system you use. Some databases also have variations on these basic types, such as TEXT instead of VARCHAR, or TIMESTAMP instead of DATE.

Creating tables with the correct data types

When you create a table, choose the right data type for each column so that the data is stored and manipulated correctly. A table for customer information, for example, might look like this:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(255),
    phone VARCHAR(20)
);

This creates a table called customers with four columns: id, name, email, and phone. The id column is the table’s primary key, and the name, email, and phone columns use the VARCHAR data type. The name column is limited to 50 characters and the email column to 255. The phone column is limited to 20 characters, which should be enough for most phone numbers.

With appropriate data types, the data is stored accurately and efficiently. Storing whole numbers in a FLOAT column instead of an INTEGER column, for example, risks losing precision. A FLOAT holds an approximate binary value, so large whole numbers can’t all be stored exactly, and comparing two floats with = can fail when you expect it to succeed. A VARCHAR column that is too short for a value would truncate it, and you could lose data.