Curriculum
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.
The character string data type represents the character data type including fixed-length and varying-length character types.
The fixed-length character data type stores fixed-length character strings. The following illustrates the SQL fixed-length character data type:
CHARACTER (n)
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
is equivalent to the following:
CHARACTER (1)
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)
The following defines a column with the fixed-length character data type, which can store up to five characters:
column_name CHARACTER(5)
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.
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.
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)
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 NUMBER, INTEGER, REAL, and DECIMAL.
The following are the SQL numeric data 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)
In this syntax:
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)
The salary column can store a number with the value up to 99,999,999.9999
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
Some variations of the integer data types are BIGINT and SMALLINT.
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)
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:
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'
For example, the following DATE value is December 31, 2020:
'2020-12-31'
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'
An optional fractional value can be used to store nanoseconds such as:
'10:59:30.9999'
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'
Notice that there is a space separator between the date and time parts.