Defining Data Types (e.g., INT, VARCHAR, DATE, etc.)
In SQL tables, data types define the storage type of data used in table columns. Data types store data and information in individual columns of a table. In the created table, each column is given a specific data type behaviour, which determines what type of table column values it will store in an existing SQL table. Choosing the right column data type is important for maintaining table data integrity, optimizing table performance, and ensuring that the data is up to date. You can manually specify the column data type according to the table you create.
SQL provides you with a wide range of data types in SQL tables, each of which is designed to store a specific type of data. In which the database designer gets options to create tables such as numeric, text, date/time types, and binary data types.
So let us know the most commonly used data types in SQL.
Numeric data types are used in SQL tables to store numeric data column information. Numeric data types are divided into two primary group categories, namely exact numeric and approximate numeric data types. Exact Numeric Data Types These data types store table numeric column values with precision and scale.
Exact data types are used when you need to store exact table column values, such as in financial data.
INT (INTEGER) – It stores complete integer (integer) column values without decimal points. The size of numeric data types depends on the specific database system (e.g. in MySQL, INT can store numeric data column values ranging from -2,147,483,648 to 2,147,483,647).
Int data type example.
CREATE TABLE employe (
employe_id INT,
salary float,
age INT
);
SMALLINT SQL data type – The INT integer data type is used to store small integer column values in SQL tables. For example, in MySQL, SMALLINT can store integer values from -32,768 to 32,767.
Small int data type example.
CREATE TABLE student (
student_id INT,
percent SMALLINT,
total samllint
);
BIGINT SQL data type – The bigINT big integer data type is used to store large integer column values in SQL tables. It typically stores table column values in the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
BIGINT SQL data type example.
CREATE TABLE money_transactions (
money_id BIGINT,
amount BIGINT
);
Decimal/Numeric SQL data type – Used to store exact numeric values with decimal places in fixed numeric values in SQL tables. These data types are often used to store money prices or other precise calculations.
Decimal/Numeric SQL data type example.
CREATE TABLE inventory (
inventry_id INT,
inv_price DECIMAL(11, 3) — Precision of 11 digits, 3 of which are after the decimal point display
);
Money SQL data type – In some databases (such as SQL Server), this type is used to store currency numeric values in SQL tables. It provides a fixed numeric value after the decimal point. It is equivalent to the decimal data type in SQL tables.
Approximate numeric data types in SQL tables.
These data types store numeric values in SQL tables that can contain a fractional data type element but cannot store fixed values, making them a better choice for scientific and statistical data types in SQL tables.
FLOAT SQL data type – SQL tables store floating-point numeric table column values with variable precision. It can store approximate values with floating decimal points in an SQL table. The size and precision of the float data type in a table depends on the database system and the specific operation of the type.
FLOAT SQL data type example.
CREATE TABLE employe (
employe_id INT,
salary FLOAT
);
REAL SQL data type – Similar to FLOAT data type in SQL tables, but stores numeric with decimal point table column values with lower precision. Whereas REAL data type typically processes single precision floating-point numeric values.
REAL SQL data type example.
CREATE TABLE temperature (
temp_id INT,
temperature REAL
);
Character data type in SQL tables.
Character data types in SQL tables are used to store and process text or string values in tables.
CHAR (Fixed-Length Character) SQL data type – Character data type stores and processes a fixed-length character string in SQL tables. You can use character data type to store text and string information in a table. If the string is shorter than the defined length, the remaining space is automatically filled with blank spaces.
CHAR (Fixed-Length Character) SQL data type example.
CREATE TABLE employe (
employe_id INT,
emp_name CHAR(30),
department CHAR(17) – it used to Stores exactly 17 characters
);
VARCHAR (Variable-Length Character) SQL data type – Stores a specific character length string character in an existing SQL table, which means it stores only as many character string data as is required for the existing string. VARCHAR data type is usually used in SQL tables to store large size text fields where the length of character data varies.
VARCHAR (Variable-Length Character) SQL data type example.
CREATE TABLE employe (
employee_id INT,
emp_age int,
name VARCHAR(40) – it used to Stores up to 40 characters
);
TEXT SQL data type – Used to store large amount of text data type in SQL tables. Whereas TEXT can store more data information than VARCHAR data type, and TEXT data type in SQL tables is often used to store long string data information like descriptions, articles, or notes.
TEXT SQL data type example.
CREATE TABLE post (
post_id INT,
content TEXT – here content Can store large amounts of text data type
);
Date and time data types in SQL tables.
Date and time data types in SQL tables are used to display date and time information in a particular table column. Specifying date and time data types often previews a date in a specific format.
DATE SQL data type – Previews current date values in an existing table in (year, month, date) format without specifying time information in the SQL table.
DATE SQL data type example.
CREATE TABLE event (
event_id INT,
event_date DATE – it Stores only the date in (YYYY-MM-DD) format
);
TIME SQL data type – Previews time information in an existing SQL table by specifying it in (hour, minute, second) format.
TIME SQL data type example.
CREATE TABLE appointment_time (
appointment _id INT,
start_time TIME – it Stores only the time in (HH:MM:SS) format
);
DATETIME SQL data type – Previews both date and time information in an existing SQL table in (year, month, day, hour, minute, second) order.
DATETIME SQL data type example.
CREATE TABLE appointments (
appointment_id INT,
appointment_datetime DATETIME – it Stores both date and time in (YYYY-MM-DD HH:MM:SS) format
);
TIMESTAMP SQL data type – Similar to the DATETIME data type in a SQL table, but includes time field information in some databases. The timestamp data type is used to track repeated changes to documents, and automatically generates the current date and time when a table row is created or updated.
TIMESTAMP SQL data type example.
CREATE TABLE audit_report (
auditlog_id INT,
action TIMESTAMP – it Stores both date and time (with optional time zone) format
);
YEAR SQL data type – Converts year values in an existing SQL table to 4-digit or 2-digit format.
YEAR SQL data type example.
CREATE TABLE event (
event_id INT,
event_year YEAR – it only Stores the year
);
Binary data type in SQL tables.
Binary data types in SQL tables are used to represent binary data, such as images, files, or other types of non-text binary data type information.
BINARY SQL data type – Similar to the CHAR data type in SQL tables, but to convert binary data you must define it as a binary column. Binary data displays information by swinging as a fixed-length binary string.
BINARY SQL data type example.
CREATE TABLE file (
file_id INT,
file_data BINARY(40) – it Stores exactly 40 bytes of binary data in file
);
VARBINARY SQL data type – It is similar to VARCHAR data type in SQL tables, but it is used to manipulate data based on length.
VARBINARY SQL data type example.
CREATE TABLE file (
file_id INT,
file_data VARBINARY(800) – it Stores up to 800 bytes of binary data in table column field
);
BLOB (Binary Large Object) SQL data type – Used to measure large volumes of binary data (such as images, videos, or other multimedia data) in a SQL table where the size of the data may vary.
BLOB (Binary Large Object) SQL data type example.
CREATE TABLE multimedia (
multimedia_id INT,
multimedia_data BLOB – it use t Stores large binary data (e.g., image or video file) in sql table
);
Boolean data type in SQL tables.
Boolean SQL data type – Binary data type in SQL tables defines binary values. Generally, binary data types in SQL tables store information in true or false order. In SQL tables, it is indicated as 1 (for true) and 0 (for false).
Boolean SQL data type example.
CREATE TABLE values (
values_id INT,
is_active BOOLEAN – it used to Stores TRUE or FALSE table column values
);
Other data types in SQL tables.
ENUM SQL data type – Used to create a group of lists of information in SQL tables. While enum data types are mangled as strings, SQL creates them internally as integers.
ENUM SQL data type example.
CREATE TABLE stock (
stok_id INT,
status ENUM(‘available’, ‘out_of_stock’, ‘discontinued’)
);
UUID SQL data type – It is created as a Universally Unique Identifier designator in SQL tables. It is used to create Universally Unique Keys in SQL tables.
UUID SQL data type example.
CREATE TABLE orders (
order_id UUID,
customer_id INT
);
