SQL – Datatypes and their syntax

DATATYPES:

• What kind of data to be assigned for a particular data
• There are 5 types of datatypes in the SQL
  • Char datatype.
  • Varchar datatype/Varchar2 datatype.
  • Number datatype.
  • Date datatype.
  • Large datatype.
    • Charlarge object.
    • Binarylarge object.

1.CHAR DATATYPE:

• Char datatype will accept ‘A-Z’, ‘a to z’, ‘0-9’, special characters, and alphanumeric.
• Char datatype will accept up to 2 thousand characters(size limit up to only 2 thousand).
• Char datatype follows fixed length memory allocation.

SYNTAX: char(size);

EXAMPLE: char(10);

Note:

• There is a drawback in char datatype i.e., there is a wastage of memory as the char datatype follows fixed length memory allocation.
• In char datatype, the used memory blocks are wasted.
• To overcome this drawback we used varchar datatype.

VARCHAR DATATYPE:

• Varchar datatype accepts ‘A to Z’, ‘a to z’, ‘0-9’, special characters, and Alphanumeric.
• Varchar datatype will accept up to two thousand characters.
• Varchar datatype follows variable length memory allocation.

SYNTAX: varchar(size);

A

L

E

X

A

1. In the varchar datatype, there is no wastage of memory, as the varchar datatype follows variable length memory allocation.
2. There is a drawback in varchar datatype i.e., we can’t use more than 2000 characters.
3. To overcome this drawback we are using varchar2 datatype.

VARCHAR2 DATATYPE:

• The Varchar2 datatype is the updated version of Varchar datatype.
• It is similar to varchar datatype.
• Varchar2 datatype accepts up to 4000 characters.
• Varchar2 datatype follows variable memory allocation.

SYNTAX:

varchar2(size);

A

L

E

X

A

• Whenever we use datatypes, we must provide size.

NUMBER DATATYPE:

• Number datatype accepts only numerical values.

SYNTAX: Number(precision,[scale]);

• Precision: It accepts only integer values.
• Scale:
  • It accepts only decimal values (or) decimal point values
  • By default, the scale value will be xero.

Example:

• Number(2,5) = 0.00011
• Number(5) = 111111
• Number(5,2) = 1111.11
• Number(5,5) = .111111
• Number(9,5) = 2101.40404

Note: It executes from right to left.

DATE DATATYPE:

• We use ORACLE date format.

SYNTAX: ‘DD-MON-YYYY’ or ‘DD-MON-YY’

LARGE OBJECT DATATYPE:

• In large object data, we have two types
  • Char large object datatype.
  • Binary large object datatype.
• Instead of using more than 4000 characters.

Char large object datatype:

• It accepts ‘A to Z’, ‘a to z’, ‘0-9’, special characters, and Alphanumeric.
• It accepts up to 4000 characters.

SYNTAX: CLOB(size);

Examples: files, videos, documents, etc.

Binary large object datatype:

• It accepts ‘A to Z’, ‘a to z’, ‘0-9’, special characters, alphanumeric.
• It accepts up to 4GB of characters.
  • example: files, videos, documents, etc.
• These types of data are stored in the form of binary digits i.e., 0’s and 1’s.

SYNTAX: BLOB(size);