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

ALEXA
Here in the above example, Alexa used the 5 memory blocks and the remaining is unused memory blocks.

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

ALEXA
NOTE:
  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);

ALEXA
NOTE:
  • 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);

      Leave a comment