Database data types

Database data can be stored in a column of a table. Each database management system (DBMS) like MySQL, PostgreSQL, or SQL Server has its own set of data types, but many are similar across systems. Let’s dive into the most common database data types:

Numeric Data Types (Used for storing numbers)

=> Integer Types

• INT or INTEGER: Stores whole numbers (e.g., 5, -10).

• TINYINT: A smaller range of integers (e.g., 0 to 255).

• SMALLINT: A slightly larger range than TINYINT.

• BIGINT: A very large range of integers.

=> Floating-Point Types

• FLOAT: Stores approximate decimal numbers (e.g., 3.14).

• DOUBLE or REAL: Stores larger or more precise decimal numbers.

• DECIMAL(p, s) or NUMERIC(p, s): Stores exact decimal numbers with precision (p) and scale (s). For example, DECIMAL(5, 2) can store 123.45.

String Data Types

=> Fixed-Length Strings

• CHAR(n): Stores a fixed-length string of n characters (e.g., CHAR(10) for "Hello____").

=> Variable-Length Strings

• VARCHAR(n): Stores a variable-length string up to n characters (e.g., VARCHAR(255) for "Hello").

• TEXT: Stores large text data (e.g., paragraphs or documents).

Date and Time Data Types

=> Date

• DATE: Stores a date in the format YYYY-MM-DD (e.g., 2023-10-25)

=> Time

• TIME: Stores a time in the format HH:MM:SS (e.g., 14:30:00).
  => Date and Time

• DATETIME: Stores both date and time (e.g., 2023-10-25 14:30:00).

• TIMESTAMP: Similar to DATETIME, but often used for tracking changes (e.g., row updates).

Binary Data Types
Used for storing binary data like images, files, or serialized objects.

• BINARY(n): Stores fixed-length binary data

• VARBINARY(n): Stores variable-length binary data.

• BLOB: Stores large binary objects (e.g., images or files)

Boolean Data Type

• BOOLEAN or BOOL: Stores TRUE or FALSE values.

Specialized Data Types

=> Enumerated Types

• ENUM: Stores a value from a predefined list (e.g., ENUM('Red', 'Green', 'Blue')).

=> JSON

• JSON: Stores JSON-formatted data (e.g., {"name": "Alice", "age": 25}).

=> UUID

• UUID: Stores universally unique identifiers (e.g., 123e4567-e89b-12d3-a456-426614174000).

. Examples in SQL
Here’s how you might define a table with different data types in SQL:

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Integer
FirstName VARCHAR(50), -- Variable-length string
LastName VARCHAR(50), -- Variable-length string
BirthDate DATE, -- Date
Salary DECIMAL(10, 2), -- Decimal number
IsActive BOOLEAN, -- Boolean
ProfilePicture BLOB, -- Binary data
CreatedAt TIMESTAMP -- Date and time
);