SQL ORDER by

SQL-Data Types

In this tutorial, we will learn about different data types. Here we will learn data types available in ORACLE SQL.

What is a Data Type?

The data type define domain of values that that each column can store. Each value in SQL in manipulated by a Data Type.

The value of one data type is different from another data type.

Built-in Data Types in Oracle

  • CHARACTER Data Types
  • NUMBER Data Types
  • LONG and RAW Data Types
  • DATETIME Data Types
  • LARGE OBJECT Data Types
  • ROWID Data Types

CHARACTER Data Types

It is used to store Alphanumeric Data and information is stored in string with Byte collection as values.It can belong to either 7 – Bit ASCII character set or EBCDIC (extended binary coded decimal interchange code).

The different character Data Types are:

  • CHAR : Fixed size and should be specified, if data is less than specified size Blank Pads are applied. Range – 1 bytes to 4 bytes and Default length is 1 Byte and the maximum is 2000 Bytes.
  • NCHAR : It contains Unicode data and length is determined by National Character Set Definition. Size is fixed and Padded if data is shorter than specified. Maximum size is 2000 Bytes.
  • VARCHAR2 : Size is variable and occupies only supplied space. Size varies from 1 Byte to 4000 Bytes.
  • NVARCHAR2 : Contains only Unicode data and minimum size is 1 byte and maximum size is 4000 Bytes.

NUMERIC Data Types

  • Number Data Types : It stores zero, positive, negative fixed and floating point numbers.
  • Float Data Type : It facilitates to have a decimal point from the first to last digit.
  • Long Data Type : This data type stores very lengthy Text Strings.

 DATE & TIME Data Types

  • DATE Data Types: It stores Date and Time information. The information reveled by date is Century, Year, Month, Date, Hour, Minute, Second.
  • TIMESTAMP  Data Types : It is an extension of date data type, it stores the data in the form of Century, Year, Month, Date, Hour, Minute, Second.

RAW & LONG RAW Data Types

  • RAW & LONG RAW Data types are used to store Binary data or Byte string.
  • These are variable length data types.
  • Mostly used to store graphics, sound, scanned documents etc.
  • Each Hexadecimal Character represents four bits of RAW data.

LARGE OBJECT (LOB) Data Types

  • The built-in LOB types are BLOB, CLOB and NCLOB and they store data internally.
  • The Bfile is a LOB which stores data externally.
  • Maximum size of these data types are up to 4 GB.
  • BLOB : Stores unstructured Binary Large Objects.
  • CLOB : Stores single byte or Multi bytes character data.
  • NCLOB : It stores Unicode data using national character set.
  • BFILE : It enable access to Binary File LOB’s which are stored in the File System outside ORACLE.

ROWID Data Types

  • Each row in the database has an address and these row address can be queried using the PSEUDO Column ROWID.
  • ROWID’s efficiently support Partitioned table and Indexes.

Below is a comparison of different data types supported by different DATABASE Vendors.

Data type Access SQLServer Oracle MySQL PostgreSQL
BOOLEAN Yes/No BIT BYTE N/A BOOLEAN
INTEGER NUMBER INT NUMBER INT
INTEGER
INT
INTEGER
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, VARBINARY,  IMAGE LOB’s
RAW
BLOB, TEXT BINARY, VARBINARY

 

One Reply to “SQL-Data Types”

Leave a Reply