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

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

CREATE TABLE test (   
id    DECIMAL PRIMARY KEY,   
col1 BOOLEAN
);

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

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:

Numeric Types:

Numeric data types represent numerical values.

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:

Datetime Types:

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

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:

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

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:

XML Types2

Example : A table using XML data type

CREATE TABLE test(
id    DECIMAL PRIMARY KEY,   
col1  XML 
);

Collection Types:

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous:SQL Home
Next: SQL Syntax