Data Types In SQL

SQL (Structured Query Language) supports various data types to represent different types of values that can be stored in a database table.

Each column in a record must be assigned a data type, which determines what kind of data it can hold such as integer, text, boolean and date.

Character String Types:

CREATE TABLE CharacterValueTable (
    firstName CHAR(50),
    shortDescription VARCHAR(200),
    article TEXT
);

CHAR(n): Fixed-length character string.
VARCHAR(n): Variable-length character string with a maximum length of 'n'.
TEXT: Variable-length character string with no specified maximum length.

Example:

INSERT INTO CharacterValueTable 
(firstName, shortDescription, article) 
VALUES ("Alice", "Short Description", 'This is Article content text.');

Numeric Types:

CREATE TABLE NumericValueTable (
    age SMALLINT,
    units INT,
    likes BIGINT,
    price FLOAT,
    weight DECIMAL,
);

SMALLINT: It store small integer type values.
INT or INTEGER: It store Integer type values without decimal.
BIGINT: It stores Larger integer-type values such as likes, and comments.
FLOAT: It stores single-precision floating-point number.
DOUBLE or REAL: It stores double-precision floating-point number.
DECIMAL or NUMERIC: Exact numeric with a fixed number of decimal places.

Example:

INSERT INTO NumericValueTable 
(age, units, likes, price, weight) VALUES 
(22, 1000, 1000000, 199.2, 80.24);

Boolean Type:

CREATE TABLE BooleanValueTable (
    isAdmin BOOLEAN
);

BOOLEAN or BOOL: Represents true or false values.

Example:

INSERT INTO BooleanValueTable 
(isAdmin) VALUES (false);

Date and Time Types:

CREATE TABLE DateTimeValueTable (
    orderedDate DATE,
    orderedTime TIME,
    orderPlacedDateTime DATETIME,
    modified_at TIMESTAMP,
    orderYear YEAR,
);

TIME: It stores time in format (HH:MM:SS).
DATE: It store date in format (YYYY-MM-DD).
DATETIME or TIMESTAMP: It allows us to store date and time combined in a single column.
YEAR: It store YEAR in format (YYYY).

Example:

INSERT INTO DateTimeValueTable 
(orderedDate, orderedTime, orderPlacedDateTime, modified_at, orderYear) 
VALUES ('2023-12-12', '03:03:12', '2023-12-12 03:03:12', '2023-12-12 03:45:12', '2023');

Binary Data Types:

CREATE TABLE BinaryValueTable (
    Data BINARY(16),
    Image VARBINARY(MAX)
);

BLOB: Binary Large Object for variable-length binary data.
BINARY(n): Fixed-length binary string.
VARBINARY(n): Variable-length binary string with a maximum length of 'n'.

Example:

INSERT INTO BinaryValueTable 
(Data, Image) 
VALUES ('0x2B43436565543G23', '0x2B43436565543H2343445432F43');

Other Types:

CREATE TABLE OtherTypesTable (
    accountStatus ENUM('pending', 'active', 'deactivated', 'deleted')
);

ENUM: Enumeration, representing a set of predefined values.
SET: A set of values chosen from a predefined list.
JSON: JSON data type for storing JSON-formatted data.

Example:

INSERT INTO OtherTypesTable 
(accountStatus) VALUES ('deactivated');

<-- Views

Data Types In SQL

SQL (Structured Query Language) supports various data types to represent different types of values that can be stored in a database table.

Each column in a record must be assigned a data type, which determines what kind of data it can hold such as integer, text, boolean and date.

Character String Types:

CREATE TABLE CharacterValueTable (
    firstName CHAR(50),
    shortDescription VARCHAR(200),
    article TEXT
);

CHAR(n): Fixed-length character string.
VARCHAR(n): Variable-length character string with a maximum length of 'n'.
TEXT: Variable-length character string with no specified maximum length.

Example:

INSERT INTO CharacterValueTable 
(firstName, shortDescription, article) 
VALUES ("Alice", "Short Description", 'This is Article content text.');

Numeric Types:

CREATE TABLE NumericValueTable (
    age SMALLINT,
    units INT,
    likes BIGINT,
    price FLOAT,
    weight DECIMAL,
);

SMALLINT: It store small integer type values.
INT or INTEGER: It store Integer type values without decimal.
BIGINT: It stores Larger integer-type values such as likes, and comments.
FLOAT: It stores single-precision floating-point number.
DOUBLE or REAL: It stores double-precision floating-point number.
DECIMAL or NUMERIC: Exact numeric with a fixed number of decimal places.

Example:

INSERT INTO NumericValueTable 
(age, units, likes, price, weight) VALUES 
(22, 1000, 1000000, 199.2, 80.24);

Boolean Type:

CREATE TABLE BooleanValueTable (
    isAdmin BOOLEAN
);

BOOLEAN or BOOL: Represents true or false values.

Example:

INSERT INTO BooleanValueTable 
(isAdmin) VALUES (false);

Date and Time Types:

CREATE TABLE DateTimeValueTable (
    orderedDate DATE,
    orderedTime TIME,
    orderPlacedDateTime DATETIME,
    modified_at TIMESTAMP,
    orderYear YEAR,
);

TIME: It stores time in format (HH:MM:SS).
DATE: It store date in format (YYYY-MM-DD).
DATETIME or TIMESTAMP: It allows us to store date and time combined in a single column.
YEAR: It store YEAR in format (YYYY).

Example:

INSERT INTO DateTimeValueTable 
(orderedDate, orderedTime, orderPlacedDateTime, modified_at, orderYear) 
VALUES ('2023-12-12', '03:03:12', '2023-12-12 03:03:12', '2023-12-12 03:45:12', '2023');

Binary Data Types:

CREATE TABLE BinaryValueTable (
    Data BINARY(16),
    Image VARBINARY(MAX)
);

BLOB: Binary Large Object for variable-length binary data.
BINARY(n): Fixed-length binary string.
VARBINARY(n): Variable-length binary string with a maximum length of 'n'.

Example:

INSERT INTO BinaryValueTable 
(Data, Image) 
VALUES ('0x2B43436565543G23', '0x2B43436565543H2343445432F43');

Other Types:

CREATE TABLE OtherTypesTable (
    accountStatus ENUM('pending', 'active', 'deactivated', 'deleted')
);

ENUM: Enumeration, representing a set of predefined values.
SET: A set of values chosen from a predefined list.
JSON: JSON data type for storing JSON-formatted data.

Example:

INSERT INTO OtherTypesTable 
(accountStatus) VALUES ('deactivated');

<-- Views