What Data types are available in SQL?

 

 

What is SQL ??

Tired of not knowing what SQL is? Well, SQL stands for Structured Query Language; it is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. What do you mean by accessing database systems?

SQL statements are used to retrieve data in a database.

What about manipulating database systems??

That is , SQL statements are also  used to update data in a database.

 

What are data types ??

In a database system, each object (such as column, variable, or parameter) has a related data type which refers to an attribute that specifies the type of data that the object can hold.  SQL works with database programs like MS Access, MS SQL Server, Oracle, Sybase, etc. Therefore there are different types of SQL for different database programs. But since we are using the Oracle database system, I will be concentrating on SQL data types in the Oracle database system.

But data types can be  categorized as:

  • strings, both fixed-length and variable-length text;
  • numbers, including integers and floating point representations;
  • date/time types; and
  • binary types for binary data.

This is an example of a SQL command defining the data types for these objects:

 CREATE TABLE registrations (
    username CHAR(20) NOT NULL,
    emailAddress CHAR(80) NOT NULL,
    zipCode CHAR(10),
    age INT,
    gender CHAR(1)
    );

While these table is by no means complete, the following table outlines some of the common names of data types between the various database platforms:

 

 

 

Access

SQL-Server

Oracle

MySQL

PostgreSQL

boolean

Yes/No

Bit

Byte

N/A

Boolean

integer

Number (integer)

Int

Number

Int
Integer (synonyms)

Integer
Int

float

Number (single)

Float
Real

Number

Float

Numeric

currency

Currency

Money

N/A

N/A

Money

string (fixed)

N/A

Char

Char

Char

Char

string (variable)

Text (<256)
Memo (65k+)

Varchar

Varchar
Varchar2

Varchar

Varchar

binary object

OLE Object
Memo

Binary (fixed up to 8K)
Varbinary (<8K)
Image (<2GB)

Long Raw

Blob
Text

Binary
Varbinary

 

 

 

References:

 

  1. Oracle PL/SQL Programming, Third Edition -- Steven Feuerstein
  2. Oracle9i PL/SQL Programming -- Scott Urman
  3. http://www.onlamp.com/pub/a/onlamp/2001/09/13/aboutSQL.html
  4. http://www.webopedia.com/TERM/D/data_type.html