Data Types in SQL

In SQL, data types define the kind of data that can be stored in a table column. Different SQL database systems may have slight variations in the names and capabilities of their data types, but the fundamental concepts are generally consistent across systems.

Common SQL Data Types

Numeric Data Types:

INT: Integer data type.

      INT, INTEGER, SMALLINT, TINYINT, BIGINT

Example: age INT

DECIMAL: Fixed-point number with a specified precision and scale.

      DECIMAL(p, s), NUMERIC(p, s)

Example: salary DECIMAL(10, 2)

FLOAT: Floating-point number.

      FLOAT, REAL, DOUBLE PRECISION

Example: temperature FLOAT

Character and String Data Types:

CHAR: Fixed-length character string.

      CHAR(n), CHARACTER(n)

Example: code CHAR(5)

VARCHAR: Variable-length character string.

      VARCHAR(n), CHARACTER VARYING(n)

Example: name VARCHAR(50)

TEXT: Large variable-length character string (specific to some SQL databases like PostgreSQL and MySQL).

      TEXT

Example: description TEXT

Date and Time Data Types:

DATE: Stores date values.

      DATE

Example: birth_date DATE

TIME: Stores time values.

      TIME

Example: appointment_time TIME

TIMESTAMP: Stores date and time values.

      TIMESTAMP, DATETIME

Example: created_at TIMESTAMP

INTERVAL: Represents a span of time (specific to some SQL databases like PostgreSQL).

      INTERVAL

Example: duration INTERVAL

Binary Data Types:

BINARY: Fixed-length binary data.

      BINARY(n)

Example: data BINARY(16)

VARBINARY: Variable-length binary data.

      VARBINARY(n)

Example: file VARBINARY(255)

BLOB: Binary large object for storing large binary data like images or files.

      BLOB

Example: image BLOB

Boolean Data Type:

BOOLEAN: Represents true or false values.

      BOOLEAN

Example: is_active BOOLEAN

Other Data Types:

ENUM: A string object with a value chosen from a list of permitted values (specific to some SQL databases like MySQL).

      ENUM('value1', 'value2', 'value3')

Example: status ENUM('active', 'inactive', 'pending')

JSON: Stores JSON-formatted data (specific to some SQL databases like PostgreSQL and MySQL).

      JSON

Example: data JSON

Example Table Definition

Here's an example of a table definition that uses various data types:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    hire_date TIMESTAMP,
    salary DECIMAL(10, 2),
    is_active BOOLEAN,
    profile_picture BLOB,
    job_title ENUM('Manager', 'Developer', 'Analyst', 'Clerk'),
    additional_info JSON
);

Understanding data types in SQL is fundamental for designing robust databases. Choosing the appropriate data type for each column ensures efficient storage, retrieval, and manipulation of data, leading to better database performance and integrity.