SQL Data Types

SQL data types specify the type of data that a column or variable can hold in a SQL database. These data types include numeric, character and string, date and time, binary, boolean, enumerated, array, and JSON types. Each data type has a specific range of values and uses. Choosing the appropriate data type for a column or variable is essential for accurate and efficient data storage and retrieval.

Types of SQL Data Types

SQL data types can be broadly categorized into several categories:

1. Numeric data types: It stores numeric values, such as integers and floating-point numbers. Examples include INT, BIGINT, FLOAT, NUMERIC, and DECIMAL.
2. Character and string data types: It stores character strings, such as names and addresses. Examples include CHAR, VARCHAR, and TEXT.
3. Date and time data types: It stores date and time values. Examples include DATE, TIME, DATETIME, and TIMESTAMP.
4. Binary data types: It stores binary data, such as images and files. Examples include BLOB, BINARY, and VARBINARY.
5. Boolean data types: It stores true/false values. Examples include BOOL and BOOLEAN.
6. Enumerated data types: It stores a predefined set of values. Examples include ENUM.
7. Array data types: It stores multiple values of the same data type. Examples include ARRAY.
8. JSON data types: It stores JSON data. Examples include JSON and JSONB.

SQL Data Types

Here are some examples of common SQL data types and their uses:

It's worth noting that SQL data types can have additional constraints and options, such as UNSIGNED for numeric types and CHARACTER SET for character and string types; also, different SQL implementations may have different data types or variations of data types.

SQL Data Types Example

When creating a table, the data types for each column must be specified. For example, the following SQL statement creates a table called "customers" with several columns, each with a specific data type:

Example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE,
    date_of_birth DATE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    last_login TIMESTAMP
);

• In the example above, the "customer_id" column has an INT data type and is set as the primary key, meaning it must contain a unique value for each row in the table.
• The "first_name" and "last_name" columns have VARCHAR data types with a maximum length of 50 characters and are both set as "NOT NULL", meaning they must contain a value.
• The "email" column has a VARCHAR data type with a maximum length of 255 characters and is set as "UNIQUE", meaning it must contain a unique value across the entire table.
• The "date_of_birth" column has a DATE data type.
• The "is_active" column has a BOOLEAN data type and is set as "NOT NULL" and "DEFAULT TRUE".
• The "last_login" column has a TIMESTAMP data type.