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.
