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:
CHARACTER (n)
Code language: SQL (Structured Query Language) (sql)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:
CHARACTER
Code language: SQL (Structured Query Language) (sql)is equivalent to the following:
CHARACTER (1)
Code language: SQL (Structured Query Language) (sql)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:
CHAR (n)
Code language: SQL (Structured Query Language) (sql)The following defines a column with the fixed-length character data type, which can store up to five characters:
column_name CHARACTER(5)
Code language: SQL (Structured Query Language) (sql)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)
Code language: SQL (Structured Query Language) (sql)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.
Most database systems use VARCHAR for representing the varying-length character data type such as MySQL and Microsoft SQL Server. Oracle uses both VARCHAR2 and VARCHAR.
The following example defines a column with the VARCHAR data type:
first_name VARCHAR(50)
Code language: SQL (Structured Query Language) (sql)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 Types
Numeric values are stored in the columns with the type of numbers, typically referred to as NUMBER, INTEGER, REAL, and DECIMAL.
The following are the SQL numeric data types:
- BIT(n)
- BIT VARYING (n)
- DECIMAL (p,s)
- INTEGER
- SMALLINT
- BIGINT
- FLOAT(p,s)
- DOUBLE PRECISION (p,s)
- REAL(s)
Decimal types
The 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)
Code language: SQL (Structured Query Language) (sql)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 p and 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)
Code language: SQL (Structured Query Language) (sql)The salary column can store a number with the value up to 99,999,999.9999
Integer
Integer data type stores whole numbers, both positive and negative. The examples of integers are 10, 0, -10, and 2010.
Most database systems useINT for representing the integer type:
INT
Code language: SQL (Structured Query Language) (sql)Some variations of the integer data types are BIGINT and SMALLINT.
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:
FLOAT
FLOAT(10)
FLOAT(50)
Code language: SQL (Structured Query Language) (sql)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
- TIME
- TIMESTAMP
DATE data type
The DATE data type represents date values that include three parts: year, month, and day. Typically, the range of the DATE data type is from 0001-01-01 to 9999-12-31.
The date value generally is specified in the form:
'YYYY-DD-MM'
Code language: SQL (Structured Query Language) (sql)For example, the following DATE value is December 31, 2020:
'2020-12-31'
Code language: SQL (Structured Query Language) (sql)TIME data type
The TIME data type store values representing a time of day in hours, minutes, and seconds.
The TIME values should be specified in the following form:
'HH:MM:SS'
Code language: SQL (Structured Query Language) (sql)An optional fractional value can be used to store nanoseconds such as:
'10:59:30.9999'
Code language: SQL (Structured Query Language) (sql)TIMESTAMP data type
The TIMESTAMP data type represents timestamp values which include both DATE and TIME values.
The TIMESTAMP values are specified in the following form:
TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
Code language: SQL (Structured Query Language) (sql)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.
