Summary: in this tutorial, you will learn about most commonly used SQL data types including character string data types, numeric data types, and date time data types.
In a database, each column of a table has a specific data type. A data type specifies the type of data that column can hold such as character strings, numeric values, and date time values.
SQL supplies a set of basic data types that you can use for defining columns of tables. In this tutorial, we will cover the most commonly used SQL data types.
Character string data type
The character string data type represents the character data type including fixed-length and varying-length character types.
Fixed-length character data type
The fixed-length character data type stores fixed-length character strings. The following illustrates the SQL fixed-length character data type:
In this syntax,
n represents the number of characters that the column can store. The
n parameter is optional. If you skip it, the database system uses one by default.
So the following declaration:
is equivalent to the following:
The maximum value of n depends on the implementation of the database system.
Most database systems use
CHAR instead of
CHARACTER for representing the fixed-length character data type:
The following defines a column with the fixed-length character data type, which can store up to five characters:
If you store a string whose length is two in the column above, then the database system will pad the three spaces to the string to ensure that each value in a column has a fixed length of five.
An example of using the fixed-length character data type is to store state abbreviations because all state abbreviations are two characters e.g., CA, NY, and TX.
Varying-length character or VARCHAR
To store varying-length strings in a column, you use the varying-length character data type. The following shows the syntax of SQL varying-length character:
CHARACTER VARYING (n)
In this syntax, n represents the maximum number of characters that the column can store. Some database systems allow you to specify the number of bytes that the column can store.
The following example defines a column with the
VARCHAR data type:
If you store a value whose length is 20 in the
first_name column, the database system stores that value without padding any spaces.
However, if you store a value whose length is greater than 50, the database system may issue an error.
Numeric values are stored in the columns with the type of numbers, typically referred to as
The following are the SQL numeric data types:
- BIT VARYING (n)
- DECIMAL (p,s)
- DOUBLE PRECISION (p,s)
DECIMAL data type is used to store exact numeric values in the database e.g., money values.
The following defines a column with the
DECIMAL data type:
column_name DECIMAL (p,s)
In this syntax:
- p is the precision that represents the number of significant digits.
- s is the scale which represents the number of digits after the decimal point.
The maximum values of
s depend on the implementation of each database system.
The following defines the salary column with 12 digits which include 4 digits after the decimal point:
salary DECIMAL (12,4)
The salary column can store a number with the value up to
Integer data type stores whole numbers, both positive and negative. The examples of integers are 10, 0, -10, and 2010.
Most database systems use
INT for representing the integer type:
Some variations of the integer data types are
Floating-point data types
The floating-point data types represent approximate numeric values. The precision and scale of the floating point decimals are variable in lengths and virtually without limit.
The following are examples of the
FLOAT data type:
Date and Time types
The date and time data types are used to store information related to dates and times. SQL supports the following date and time data types:
DATE data type
DATE data type represents date values that include three parts: year, month, and day. Typically, the range of the
DATE data type is from
The date value generally is specified in the form:
For example, the following
DATE value is
December 31, 2020:
TIME data type
TIME data type store values representing a time of day in hours, minutes, and seconds.
TIME values should be specified in the following form:
An optional fractional value can be used to store nanoseconds such as:
TIMESTAMP data type
TIMESTAMP data type represents timestamp values which include both
TIMESTAMP values are specified in the following form:
TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
Notice that there is a space separator between the date and time parts.
Now, you should have a brief overview of the SQL data types including character string data types, numeric data types, and date time data types.