SQL Data Types
Data Types
A data type is a set of representable values. Every representable value belongs to at least one data type and some belong to several data types. SQL supports three sorts of data types: predefined data types, constructed types, and user-defined types.
Predefined data types are sometimes called the "built-in data types", though not in this International Standard. Every predefined data type is a subtype of itself and of no other data types. It follows that every predefined data type is a supertype of itself and of no other data types.
User-defined data types can be defined by a standard, by an implementation, or by an application.
A constructed type is specified using one of SQL's data type constructors, ARRAY, REF, and ROW. The type is either an array type, a reference type or a row type, according to whether it is specified with ARRAY, REF, or ROW, respectively. Array types are the only examples of constructed types known generically as collection types.
Data types are used within the CREATE TABLE statement as part of column definitions :
CREATE TABLE <tablename>(
<column_name> <data_type> ... ,
<column_name> <data_type> ... ,
... );
What is Unicode?
According to unicode.org "Unicode provides a unique number for every character, no matter what the platform, no matter what the program, no matter what the language. The Unicode Standard has been adopted by such industry leaders as Apple, HP, IBM, JustSystems, Microsoft, Oracle, SAP, Sun, Sybase, Unisys and many others. Unicode is required by modern standards such as XML, Java, ECMAScript (JavaScript), LDAP, CORBA 3.0, WML, etc., and is the official way to implement ISO/IEC 10646. It is supported in many operating systems, all modern browsers, and many other products. The emergence of the Unicode Standard and the availability of tools supporting it are among the most significant recent global software technology trends.
Incorporating Unicode into client-server or multi-tiered applications and websites offers significant cost savings over the use of legacy character sets. Unicode enables a single software product or a single website to be targeted across multiple platforms, languages and countries without re-engineering. It allows data to be transported through many different systems without corruption."
Examples of SQL Data Types:
Literal | Examples |
Character string | '59', 'Python' |
Numeric | 48, 10.34, 2., .001, -125, +5.33333, 2.5E2, 5E-3 |
Boolean | TRUE, FALSE, UNKNOWN |
Datetime | DATE, '2016-05-14', TIME '04:12:00',TIMESTAMP ‘2016-05-14 10:23:54’ |
Interval | INTERVAL ‘15-3’ YEAR TO MONTH, INTERVAL ‘23:06:5.5’ HOUR TO SECOND |
SQL Data Types : New Features in SQL 2003 standard
- New data types
- BIGINT
- MULTISET
- Extensions to existing data types
- Unbounded ARRAY
- Deletion of existing types
- BIT
- BIT VARYING
Here we have discussed SQL 2003 standard data types with a short description and example.
SQL Data Types with example and explanation
Contents:
Character String Types:
A character string data type is described by a character string data type descriptor.
Data Type | Description |
CHARACTER | Character string, fixed length. A string of text in an implementer-defined format. The size argument is a single nonnegative integer that refers to the maximum length of the string. Values for this type must enclose in single quotes. |
CHARACTER VARYING (VARCHAR) | Variable length character string, maximum length fixed. |
CHARACTER LARGE OBJECT (CLOB) | A Character Large OBject (or CLOB) is a collection of character data in a database management system, usually stored in a separate location that is referenced in the table itself. |
NATIONAL CHARACTER (NCHAR) |
NATIONAL CHARACTER type is the same as CHARACTER except that it holds standardized multibyte characters or Unicode characters. |
NATIONAL CHARACTER VARYING (NCHAR VARYING) | NATIONAL CHARACTER VARYING type is the same as CHARACTER VARYING except that it holds standardized multibyte characters or Unicode characters. |
NATIONAL CHARACTER LARGE OBJECT (NCLOB) | NCLOB type is the same as CLOB except that it holds standardized multibyte characters or Unicode characters. |
SQL: Fixed-length character string :

SQL : Character strings of Varying length

Example : A table with columns of fixed and varying length size strings and a CLOB string
CREATE TABLE test (
id DECIMAL PRIMARY KEY,
col1 CHAR(8), -- exactly 8 characters
col2 VARCHAR(100), -- up to 100 characters
col3 CLOB -- very large strings
);
DBMS Character String Types:
DBMS and version | Types |
MySQL 5.7 | CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET |
PostgreSQL 9.5.3 | CHARACTER VARYING(n), VARCHAR(n), CHARACTER(n), CHAR(n), TEXT |
SQL Server | CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT |
Oracle 11g | CHAR, VARCHAR2, VARCHAR, NCHAR, NVARCHAR2, CLOB, NCLOB, LONG |
Boolean Type:
The data type boolean comprises the distinct truth values true and false .The boolean data type also supports the unknown truth value as the null value unless prohibited by a NOT NULL constraint.
Data Type | Description |
BOOLEAN | Stores truth values - either TRUE or FALSE. |
Example : A table using boolean type
CREATE TABLE test (
id DECIMAL PRIMARY KEY,
col1 BOOLEAN
);
DBMS Boolean Types:
DBMS and version | Types |
MySQL 5.7 | TINYINT(1) |
PostgreSQL 9.5 | BOOLEAN |
SQL Server 2014 | BIT |
Oracle 11g | BOOLEAN |
Binary large object Type:
A binary string is a sequence of octets that does not have either a character set or collation associated with it and is described by a binary data type descriptor.
Data Type | Description |
BINARY LARGE OBJECT (BLOB). | BLOB stores a long sequence of bytes. |
Example : A table with columns of fixed and variable size binary data and a BLOB
CREATE TABLE test (
id DECIMAL PRIMARY KEY,
col1 BINARY(8), -- exactly 8 byte
col2 VARBINARY(140), -- up to 140 byte
col3 BLOB -- very large data: store picture, sound ...
);
DBMS Binary Types:
DBMS and version | Types |
MySQL 5.7 | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
PostgreSQL 9.5 | BYTEA |
SQL Server 2014 | BLOB |
Oracle 11g | LOB |
Numeric Types:
Numeric data types represent numerical values.
Data Type | Description |
INTEGER | Represents an integer. The minimum and maximum values depend on the DBMS. |
SMALLINT | Same as INTEGER type except that it might hold a smaller range of values, depending on the DBMS. |
BIGINT | Same as INTEGER type except that it might hold a larger range of values, depending on the DBMS. |
DECIMAL(p, s) | Exact numerical, precision p, scale s. A decimal number, that is a number that can have a decimal point in it. The size argument has two parts : precision and scale. The scale can not exceed the precision. Precision comes first, and a comma must separate from the scale argument. |
NUMERIC(p, s) | Exact numerical, precision p, scale s. The maximum precision depends on the DBMS. |
FLOAT(p) | Approximate numerical, mantissa precision p. Precision is greater than or equal to 1 and the maximum precision depends on the DBMS. |
REAL | Same as FLOAT type except that the DBMS defines the precision. |
DOUBLE PRECISION | Same as FLOAT type (DBMS defines the precision) but greater than that of REAL. |
Example : Precision and Scale Examples for 235.89

Example : A table using numeric data types
CREATE TABLE test (
id DECIMAL PRIMARY KEY,
name VARCHAR(100), -- up to 100 characters
col1 DECIMAL(5,2), -- three digits before the decimal and two behind
col2 SMALLINT, -- no decimal point
col3 INTEGER, -- no decimal point
col4 BIGINT, -- no decimal point.
col5 FLOAT(2), -- two or more digits after the decimal place
col6 REAL,
col7 DOUBLE PRECISION
);
DBMS Numeric Types:
DBMS and version | Types |
MySQL 5.7 | INTEGER(TINYINT, SMALLINT, MEDIUMINT, INT BIGINT, INTEGER) FIXED-POINT(DECIMAL, NUMERIC) FLOATING-POINT(FLOAT, DOUBLE) BIT-VALUE(BIT), |
PostgreSQL 9.5.3 | SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, SMALLSERIAL, SERIAL, BIGSERIAL |
SQL Server 2014 | EXACT NUMERICS(BIGINT, BIT, DECIMAL, INT, MONEY, NUMERIC, SMALLINT, SMALLMONEY, TINYINT) APPROXIMATE NUMERICS(FLOAT, REAL ) |
Oracle 11g | NUMBER FLOATING-POINT(BINARY_FLOAT, BINARY_DOUBLE) |
Datetime Types:
The datetime data types represent the date and time of day.
Data Type | Description |
DATE | Represents a date. Format : yyyy-mm-dd |
TIME WITHOUT TIME ZONE | Represents a time of day without time zone. Format : hh:mm:ss |
TIME WITH TIME ZONE | Represents a time of day with time zone. Format : yyyy-mm-dd AT TIME ZONE -06:00. |
TIMESTAMP WITHOUT TIME ZONE | Represents a combination of DATE and TIME values separated by a space. Format : yyyy-mm-dd hh:mm:ss |
TIMESTAMP WITH TIME ZONE | Represents a combination of DATE and TIME values separated by a space with time zone. Format : yyyy-mm-dd hh:mm:ss AT TIME ZONE -06:00. |
Example : A table using Datetime data types
CREATE TABLE test (
id DECIMAL PRIMARY KEY,
col1 DATE, -- store year, month and day (Oracle: plus hour, minute and seconds)
col2 TIME,
col3 TIMESTAMP(9), -- a timestamp with 9 digits after the decimal of seconds
col4 TIMESTAMP WITH TIME ZONE -- a timestamp including the name of a timezone
);
DBMS Date and Time Types:
DBMS and version | Types |
MySQL 5.7 | DATE, TIME, DATETIME, TIMESTAMP, YEAR |
PostgreSQL 9.5.3 | TIMESTAMP [ WITHOUT TIME ZONE ], TIMESTAMP WITH TIME ZONE, DATE, TIME[ WITHOUT TIME ZONE ], TIME WITH TIME ZONE, |
SQL Server | DATE, DATETIME2, DATETIME, DATETIMEOFFSET, SMALLDATETIME, TIME |
Oracle 11g | DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE |
Interval Type:
It stores the quantity of time between two datetime values. For example between 10:00 and 12:30 is an interval of 02:30 (2 hours and 30 minutes). There are two classes of intervals. The first one called year-month intervals, includes no fields other than YEAR and MONTH, though not both are required. The other class called day-time intervals that can include any fields other than YEAR or MONTH.
Data Type | Description |
Year-month | Includes no fields other than YEAR and MONTH, though not both are required. |
Day-time | Includes any fields other than YEAR or MONTH. These intervals can contain a day value, hour value, minute value, second value, or some combination thereof. |
Fields in Year-month INTERVAL values:
Keyword | Meaning |
YEAR | years |
MONTH | months |
Fields in day-time INTERVAL values:
Keyword | Meaning |
DAY | Days |
HOUR | Hours |
MINUTE | Minutes |
SECOND | Seconds and possibly fractions of a second |
Valid values for fields in INTERVAL values:
Keyword | Valid values of INTERVAL fields |
YEAR | Unconstrained except by <interval leading field precision> |
MONTH | Months (within years) (0-11) |
DAY | Unconstrained except by <interval leading field precision> |
HOUR | Hours (within days) (0-23) |
MINUTE | Minutes (within hours) (0-59) |
SECOND | Seconds (within minutes) (0-59.999...) |
Valid operators involving datetimes and intervals:
Operand1 | Operator | Operand2 | Result Type |
Datetime | - | Datetime | Interval |
Datetime | + or - | Interval | Datetime |
Interval | + | Datetime | Datetime |
Interval | + or - | Interval | Interval |
Interval | * or / | Numeric | Interval |
Numeric | * | Interval | Interval |
Example : A table using Interval data types
CREATE TABLE test (
id DECIMAL PRIMARY KEY,
col1 INTERVAL YEAR TO MONTH,
col2 INTERVAL DAY TO SECOND(6) -- an interval with 6 digits after the decimal of seconds
);
DBMS Interval Types:
DBMS and version | Types |
MySQL 5.7 | NOT SUPPORTED |
PostgreSQL 9.5 | INTERVAL |
SQL Server 2014 | NOT SUPPORTED |
Oracle 11g | INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND |
XML Types2
XML | Stores XML data. It can be used wherever a SQL datatype is allowed, such as a column of a table. |
Example : A table using XML data type
CREATE TABLE test(
id DECIMAL PRIMARY KEY,
col1 XML
);
Collection Types:
COLLECTION ( ARRAY, MULTISET ) |
ARRAY(offered in SQL99) is a set-length and ordered a collection of elements, MULTISET (added in SQL2003) is a variable-length and unordered collection of elements. Both the elements must be of a predefined datatype. |
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous:SQL Home
Next: SQL Syntax
SQL: Tips of the Day
Difference between natural join and inner join
One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-
Consider:
TableA TableB +------------+----------+ +--------------------+ |Column1 | Column2 | |Column1 | Column3 | +-----------------------+ +--------------------+ | 1 | 2 | | 1 | 3 | +------------+----------+ +---------+----------+
The INNER JOIN of TableA and TableB on Column1 will return
SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1); SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+ | a.Column1 | a.Column2 | b.Column1| b.Column3| +------------------------+---------------------+ | 1 | 2 | 1 | 3 | +------------+-----------+----------+----------+
The NATURAL JOIN of TableA and TableB on Column1 will return:
SELECT * FROM TableA NATURAL JOIN TableB +------------+----------+----------+ |Column1 | Column2 | Column3 | +-----------------------+----------+ | 1 | 2 | 3 | +------------+----------+----------+
Ref: https://bit.ly/3AG5CId
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook