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:

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

Character String Types:

A character string data type is described by a character string data type descriptor.

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 
);

Explanation:

• This SQL code creates a new table named "test" in the current schema.
• The table consists of three columns: id, col1, col2, and col3.
• Here's a breakdown of the table structure and column specifications:
• id: This column is of the DECIMAL data type and is designated as the PRIMARY KEY for the table. The PRIMARY KEY constraint ensures that each value in the column is unique and not null.
• col1: This column is of the CHAR data type with a length of 8 characters. CHAR columns store fixed-length character strings, padding shorter strings with spaces if necessary to meet the specified length.
• col2: This column is of the VARCHAR data type with a maximum length of 100 characters. VARCHAR columns store variable-length character strings, allowing storage of up to 100 characters.
• col3: This column is of the CLOB (Character Large Object) data type. CLOB columns are used to store very large strings, typically exceeding the maximum length allowed by VARCHAR.

DBMS Character String Types:

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.

Example : A table using boolean type

-- Creating a new table named "test" in the current schema
CREATE TABLE test (
    -- Defining a column named "id" of the DECIMAL data type, designated as the PRIMARY KEY
    id DECIMAL PRIMARY KEY,
    -- Defining a column named "col1" of the BOOLEAN data type
    col1 BOOLEAN
);

Explanation:

• This SQL code creates a new table named "test" in the current schema.
• The table consists of two columns: id and col1.
• Here's a breakdown of the table structure and column specifications:
• id: This column is of the DECIMAL data type. DECIMAL is used for numeric data with a fixed precision and scale. It is designated as the PRIMARY KEY for the table, implying that each value in this column must be unique and not null.
• col1: This column is of the BOOLEAN data type. BOOLEAN columns store true/false or 1/0 values, representing logical values.

DBMS Boolean Types:

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.

Example : A table with columns of fixed and variable size binary data and a BLOB

-- Creating a new table named "test" in the current schema
CREATE TABLE test (
    -- Defining a column named "id" of the DECIMAL data type, designated as the PRIMARY KEY
    id DECIMAL PRIMARY KEY,
    -- Defining a column named "col1" of the BINARY data type with a fixed length of 8 bytes
    col1 BINARY(8),
    -- Defining a column named "col2" of the VARBINARY data type with a maximum length of 140 bytes
    col2 VARBINARY(140),
    -- Defining a column named "col3" of the BLOB data type, which can store very large binary data such as pictures or sounds
    col3 BLOB
);

Explanation:

• This SQL code creates a new table named "test" in the current schema.
• The table consists of four columns: id, col1, col2, and col3.
• Here's a breakdown of the table structure and column specifications:
• id: This column is of the DECIMAL data type. DECIMAL is used for numeric data with a fixed precision and scale. It is designated as the PRIMARY KEY for the table, implying that each value in this column must be unique and not null.
• col1: This column is of the BINARY data type with a fixed length of 8 bytes. BINARY columns store fixed-length binary data, suitable for storing byte sequences.
• col2: This column is of the VARBINARY data type with a maximum length of 140 bytes. VARBINARY columns store variable-length binary data, allowing storage of up to 140 bytes.
• col3: This column is of the BLOB (Binary Large Object) data type. BLOB columns are used to store very large binary data, such as pictures or sounds. They can hold large amounts of data, limited only by the database's storage capacity.

DBMS Binary Types:

Numeric Types:

Numeric data types represent numerical values.

Example : Precision and Scale Examples for 235.89

Example : A table using numeric data types

-- Creating a new table named "test" in the current schema
CREATE TABLE test (
    -- Defining a column named "id" of the DECIMAL data type, designated as the PRIMARY KEY
    id DECIMAL PRIMARY KEY,
    -- Defining a column named "name" of the VARCHAR data type with a maximum length of 100 characters
    name VARCHAR(100),
    -- Defining a column named "col1" of the DECIMAL data type with 5 total digits, 2 of which are after the decimal point
    col1 DECIMAL(5,2),
    -- Defining a column named "col2" of the SMALLINT data type
    col2 SMALLINT,
    -- Defining a column named "col3" of the INTEGER data type
    col3 INTEGER,
    -- Defining a column named "col4" of the BIGINT data type
    col4 BIGINT,
    -- Defining a column named "col5" of the FLOAT data type with at least 2 digits after the decimal point
    col5 FLOAT(2),
    -- Defining a column named "col6" of the REAL data type
    col6 REAL,
    -- Defining a column named "col7" of the DOUBLE PRECISION data type
    col7 DOUBLE PRECISION
);

Explanation:

• This SQL code creates a new table named "test" in the current schema.
• The table consists of multiple columns with different data types and specifications.
• Here's a breakdown of the table structure and column specifications:
• id: This column is of the DECIMAL data type and serves as the primary key for the table.
• name: This column is of the VARCHAR data type with a maximum length of 100 characters, suitable for storing textual data.
• col1: This column is of the DECIMAL data type with 5 total digits, 2 of which are after the decimal point, allowing for precise numeric data storage.
• col2: This column is of the SMALLINT data type, suitable for storing small integer values without decimal points.
• col3: This column is of the INTEGER data type, suitable for storing integer values without decimal points.
• col4: This column is of the BIGINT data type, suitable for storing large integer values without decimal points.
• col5: This column is of the FLOAT data type with at least 2 digits after the decimal point, suitable for storing floating-point numeric data with varying precision.
• col6: This column is of the REAL data type, suitable for storing single-precision floating-point numeric data.
• col7: This column is of the DOUBLE PRECISION data type, suitable for storing double-precision floating-point numeric data.

DBMS Numeric Types:

Datetime Types:

The datetime data types represent the date and time of day.

Example : A table using Datetime data types

-- Creating a new table named "test" in the current schema
CREATE TABLE test (
    -- Defining a column named "id" of the DECIMAL data type, designated as the PRIMARY KEY
    id DECIMAL PRIMARY KEY,
    -- Defining a column named "col1" of the DATE data type, capable of storing year, month, and day
    col1 DATE,
    -- Defining a column named "col2" of the TIME data type, capable of storing time without a date component
    col2 TIME,
    -- Defining a column named "col3" of the TIMESTAMP data type with precision of 9 digits after the decimal point for seconds
    col3 TIMESTAMP(9),
    -- Defining a column named "col4" of the TIMESTAMP WITH TIME ZONE data type, which includes timezone information
    col4 TIMESTAMP WITH TIME ZONE
);

Explanation:

• This SQL code creates a new table named "test" in the current schema.
• The table consists of multiple columns with different data types related to date and time.
• Here's a breakdown of the table structure and column specifications:
• id: This column is of the DECIMAL data type and serves as the primary key for the table.
• col1: This column is of the DATE data type, capable of storing year, month, and day information without any time component.
• col2: This column is of the TIME data type, capable of storing time information without any date component.
• col3: This column is of the TIMESTAMP data type with a precision of 9 digits after the decimal point for seconds, capable of storing date and time information with high precision.
• col4: This column is of the TIMESTAMP WITH TIME ZONE data type, capable of storing date and time information along with the name of a timezone, allowing for accurate representation of time in different time zones.

DBMS Date and Time Types:

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.

Fields in Year-month INTERVAL values:

Fields in day-time INTERVAL values:

Valid values for fields in INTERVAL values:

Valid operators involving datetimes and intervals:

Example : A table using Interval data types

-- Creating a new table named "test" in the current schema
CREATE TABLE test (
    -- Defining a column named "id" of the DECIMAL data type, designated as the PRIMARY KEY
    id DECIMAL PRIMARY KEY,
    -- Defining a column named "col1" of the INTERVAL YEAR TO MONTH data type, capable of storing intervals in terms of years and months
    col1 INTERVAL YEAR TO MONTH,
    -- Defining a column named "col2" of the INTERVAL DAY TO SECOND data type with precision of 6 digits after the decimal point for seconds
    col2 INTERVAL DAY TO SECOND(6)
);

Explanation:

• This SQL code creates a new table named "test" in the current schema.
• The table consists of multiple columns with different data types related to intervals.
• Here's a breakdown of the table structure and column specifications:
• id: This column is of the DECIMAL data type and serves as the primary key for the table.
• col1: This column is of the INTERVAL YEAR TO MONTH data type, capable of storing intervals in terms of years and months, without specifying any specific date or time.
• col2: This column is of the INTERVAL DAY TO SECOND data type with a precision of 6 digits after the decimal point for seconds, capable of storing intervals in terms of days, hours, minutes, and seconds, with fractional seconds included.

DBMS Interval Types:

XML Types2

Example : A table using XML data type

-- Creating a new table named "test" in the current schema
CREATE TABLE test (
    -- Defining a column named "id" of the DECIMAL data type, designated as the PRIMARY KEY
    id DECIMAL PRIMARY KEY,
    -- Defining a column named "col1" of the XML data type
    col1 XML
);

Explanation:

• This SQL code creates a new table named "test" in the current schema.
• The table consists of two columns: id and col1.
• Here's a breakdown of the table structure and column specifications:
• id: This column is of the DECIMAL data type and serves as the primary key for the table.
• col1: This column is of the XML data type, designed for storing XML data.

Collection Types:

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous:SQL Home
Next: SQL Syntax