A data type, in programming, is a classification that specifies which type of value a variable has and what type of mathematical, relational or logical operations can be applied to it without causing an error.
Each column in a database table is required to have a name and a data type.
SQL developers have to decide what types of data will be stored inside each and every table column when creating a SQL table.
The data type is a label and a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
It is necessary to specify the data type for each column when we create a table. So exactly what are the different types of data we can store in a table
We can nevertheless group the different data types into the following categories:
We can nevertheless group the different data types into the following categories:
Data types that fall in this category include Integer, Float, Real, Numeric, or Decimal. Common functions that operate on this type of data include COUNT, SUM, MAX, MIN, and AVG.
This type of data stores character values. The two common types are CHAR(n) and VARCHAR(n).
The CHAR(n) data type holds n characters, padded with spaces at the end if needed.
VARCHAR stands for Varying Char, meaning that the length of the field can vary. For example, a VARCHAR (10) data type holds up to 10 characters.
But if data is only 8 characters long, then it will only store 8 characters. Common functions that operate on this type of data are discussed in String Functions
The CHARACTER data type accepts character strings, including Unicode, of a fixed length.
The length of the character string should be specified in the data type declaration; for example, CHARACTER(n) where n represents the desired length of the character string.
If no length is specified during the declaration, the default length is 1.
The minimum length of the CHARACTER data type is 1 and it can have a maximum length up to the table page size.
Character strings that are larger than the page size of the table can be stored as a Character Large Object (CLOB).
NOTE: CHARACTER(0) is not allowed and raises an exception.
If you assign a value to a CHARACTER column containing fewer characters than the defined length, the remaining space is filled with blanks characters.
Any comparisons made to a CHARACTER column must take these trailing spaces into account.
Attempting to assign a value containing more characters than the defined length results in the truncation of the character string to the defined length.
If any of the truncated characters are not blank, an error is raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
BOOLEAN
The BOOLEAN data type supports the storage of two values: TRUE or FALSE.
No parameters are required when declaring a BOOLEAN data type.
Use the case insensitive keywords TRUE or FALSE to assign a value to a BOOLEAN data type.
Comparisons using the BOOLEAN data type should also use these keywords.
If you attempt to assign any other value to a BOOLEAN data type, an error is raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
BOOLEAN
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
SMALLINT
The SMALLINT data type accepts numeric values with an implied scale of zero.
It stores any integer value between the range 2^ -15 and 2^15 -1. Attempting to assign values outside this range causes an error.
If you assign a numeric value with a precision and scale to a SMALLINT data type, the scale portion truncates, without rounding.
NOTE: To store values beyond the range (2^-15) to (2^15)-1, use the INTEGER data type.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
INTEGER or INT
The INTEGER data type accepts numeric values with an implied scale of zero.
It stores any integer value between the range 2^ -31 and 2^31 -1.
Attempting to assign values outside this range causes an error.
If you assign a numeric value with a precision and scale to an INTEGER data type, the scale portion truncates, without rounding.
NOTE: To store integer values beyond the range (2^-31) to (2^31)-1, use the DECIMAL data type with a scale of zero.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
INTEGER or INT
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
DECIMAL [(p[,s])] or DEC [(p[,s])]
The DECIMAL data type accepts numeric values, for which you may define a precision and a scale in the data type declaration.
The precision is a positive integer that indicates the number of digits that the number will contain.
The scale is a positive integer that indicates the number of these digits that will represent decimal places to the right of the decimal point.
The scale for a DECIMAL cannot be larger than the precision.
DECIMAL data types can be declared in one of three different ways.
The declaration of it controls how the number is presented to an SQL query, but not how it is stored.
The DECIMAL data type accepts numeric values, for which you may define a precision and a scale in the data type declaration.
The precision is a positive integer that indicates the number of digits that the number will contain.
The scale is a positive integer that indicates the number of these digits that will represent decimal places to the right of the decimal point.
The scale for a DECIMAL cannot be larger than the precision.
DECIMAL data types can be declared in one of three different ways.
The declaration of it controls how the number is presented to an SQL query, but not how it is stored.
DECIMAL - Precision defaults to 38, Scale defaults to 0
DECIMAL(p) - Scale defaults to 0
DECIMAL(p, s) - Precision and Scale are defined by the user
In the above examples, p is an integer representing the precision and s is an integer representing the scale.
NOTE: If you exceed the number of digits expected to the left of the decimal point, an error is thrown. If you exceed the number of expected digits to the right of the decimal point, the extra digits are truncated.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
DECIMAL(10,3)
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
NUMERIC [(p[,s])]
PointBase treats the NUMERIC data type in exactly the same way as the DECIMAL data type
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
REAL
The REAL data type accepts approximate numeric values, up to a precision of 64.
No parameters are required when declaring a REAL data type.
If you attempt to assign a value with a precision greater than 64 an error is raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
REAL
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
FLOAT(p)
The FLOAT data type accepts approximate numeric values, for which you may define a precision up to a maximum of 64.
If no precision is specified during the declaration, the default precision is 64.
Attempting to assign a value lager than the declared precision will cause an error to be raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
FLOAT(8)
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
DOUBLE PRECISION
The REAL data type accepts approximate numeric values, up to a precision of 64.
No parameters are required when declaring a DOUBLE PRECISION data type.
If you attempt to assign a value with a precision greater than 64 an error is raised.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
DOUBLE PRECISION
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
DATE
The DATE data type accepts date values.
No parameters are required when declaring a DATE data type.
Date values should be specified in the form: YYYY-MM-DD.
However, PointBase will also accept single digits entries for month and day values.
Month values must be between 1 and 12, day values should be between 1 and 31 depending on the month and year values should be between 0 and 9999.
Values assigned to the DATE data type should be enclosed in single quotes, preceded by the case insensitive keyword DATE; for example, DATE '1999-04-04'.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
DATE
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
TIME
The TIME data type accepts time values.
No parameters are required when declaring a TIME data type.
Date values should be specified in the form: HH:MM:SS. An optional fractional value can be used to represent nanoseconds.
The minutes and seconds values must be two digits.
Hour values should be between zero 0 and 23, minute values should be between 00 and 59 and second values should be between 00 and 61.999999.
Values assigned to the TIME data type should be enclosed in single quotes, preceded by the case insensitive keyword TIME; for example, TIME '07:30:00'.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
TIME
Valid
Invalid
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
TIMESTAMP
The TIMESTAMP data type accepts timestamp values, which are a combination of a DATE value and a TIME value.
No parameters are required when declaring a TIMESTAMP data type.
Timestamp values should be specified in the form: YYYY-MM-DD HH:MM:SS.
There is a space separator between the date and time portions of the timestamp.
All specifications and restrictions noted for the DATE and TIME data types also apply to the TIMESTAMP data type.
Values assigned to the TIMESTAMP data type should be enclosed in single quotes, preceded by the case insensitive keyword TIMESTAMP; for example, TIMESTAMP '1999-04-04 07:30:00'.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples
TIMESTAMP
Valid
Invalid
CLOB [(length)] or CHARACTER LARGE OBJECT [(length)] or CHAR LARGE OBJECT [(length)]
The Character Large Object (CLOB) data type accepts character strings longer than those that are allowed in the CHARACTER [(length)] or VARCHAR (length) data types.
The CLOB declaration uses the following syntax to specify the length of the CLOB in bytes:
In the above syntax, n is an unsigned integer that represents the length.
K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively.
If K, M, or G is specified in addition to n, then the actual length of n is the following:
In the above syntax, n is an unsigned integer that represents the length.
K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively.
If K, M, or G is specified in addition to n, then the actual length of n is the following:
K = n * 1024
M = n * 1,048,576
G = n * 1,073,741,824
The maximum size allowed for CLOB data types is 2 gigabytes.
If a length is not specified, then a default length of one byte is used.
CLOB values can vary in length from one byte up to the specified length.
NOTE: The CLOB data type supports Unicode data.
BLOB [(length)] or BINARY LARGE OBJECT [(length)]
The Binary Large Object (BLOB) data type accepts binary values.
The BLOB declaration uses the following syntax to specify the length in bytes:
In the above syntax, n is an unsigned integer that represents the length. K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively. If K, M, or G is specified in addition to n, then the actual length of n is the following:
In the above syntax, n is an unsigned integer that represents the length. K, M, and G correspond to Kilobytes, Megabytes or Gigabytes, respectively. If K, M, or G is specified in addition to n, then the actual length of n is the following:
K = n * 1024
M = n * 1,048,576
G = n * 1,073,741,824
The maximum size allowed for BLOB data types is 2 gigabytes. If a length is not specified, then a default length of one byte is used. BLOB values can vary in length from one byte up to the specified length.
NOTE: BLOB data types cannot be used with SQL scalar functions.
This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf
Related Searches to Data Types in SQL
data types examplessql data formatsql boolean data typesql data type textdecimal sql data typesql data type intsql data type dateoracle sql data typessql server datatypessql server boolean data typesql server number data typessql server text data typesql server numeric data typesql data type timeboolean datatype in sql serversql decimal data typedate datatype in sql examplemssql datatypesdata type sql servertypes of sql databasessql data type numericnumeric data type sqlsql server decimal data typesql server data type sizesdata types in databasesql server double data typesql data types with examplesvarchar data typedata types in sql serverdate datatype in sql exampleoracle sql data typesdata types in mysqllist of data typessql boolean examplefloat data type exampledata types c++