Data type in SQL

Data Type - Datatype is category in which data fails. 

There are five type datatype

1. char
2. varchar and varchar2
3. Date
4. Number
5. Large Object

1.Char - Char is datatype accept size as an argument.

    Syntax - char(size) 

Example - char(8)

•  Using char data type we can store capital (A-Z) ,small(a-z), (0-9) number and special characters like #,$,%,&,* .........etc .
• The maximum size that we can pass for char is 2000 .

      char(2000)------- it is allow
      char(1000) ------ it is allow
      char(2001) -----it is not allow

• Char is fix length memory allocation the unuse block of memory will be wasted.

2.Varchar - varchar data type accept size of an argument.

    Syntax - varchar(size)

    varchar(8)

• Using char data type we can store capital (A-Z) ,small(a-z), (0-9) number and special characters like #,$,%,&,* .........etc .
• The maximum size that we can pass for char is 2000 .
• Varchar is variable length memory allocation. The unuse memory block of memory will not be wasted. There is zero memory loss.

Note - The disadvantages of char datatype is overcome with the help of varchar.

1. Varchar2 - varchar2 datatype accept size of an argument.

Syntax - varchar2(size)

                varchar2(8)

• Using varchar2 we can store capital (A-Z), small (a-z), 0-9 number and special symbols(@,#,$,*)
• The maximum size of varchar2 is 4000.
• varchar2 is memory length memory allocation there is zero memory loss.

3. Data datatype - 

• Date datatype will not accept any argument. 
• There are two format of date.

        DD-MON-YYYY ------ All Century format

        12-03-2022

        DD-MON-YY --------Current Century format

        12-03-22

• Date should be enclosed in single code(' ').

Note - We can store date by using varchar data type but we will not be able to perform the operation of date value .

So to perform the operation or date we have dedicated data type called as data we can only stored data value .

4.Number - Number is use to stored only the number value.

    Syntax - Number(Precision[,scale])

Case 1: Number (P) - -------------- scale is optional / scale is not mandatory

Case 2: Number(P,S) ---------------P>S

Case 3: Number(P,S) ---------------P=S

Case 4: Number(P,S) ---------------S>P

• Case 1: Number(P)

           Example -    Number(1) ---------- P=1

                    

Note - Using this case we can only stored hole number and negative integer.

 

    Number(10)                  Number(12)

    Contact                           Adhar_no

Using the above case we can not stored the no which are having decimal part.

• Case 2: Number(P,S)  P>S --Condition

         Example -   Number(7,4)        7>4                                                    P=7     S=4

                                                                                                                      P>S    7>4  

                                                                                                Operation = P-S=R

                                                                                                                       7 - 4 = 3

                             

• Case 3: Number(P,S)   P=S --Condition

            Example - Number(3,3)        3=3

            

            

• Case 4: Number(P,S)     S>P --Condition

          Example - Number(2,3)

5. Large Object - Large object data type has two type

1. Character large object.
2. Binary large object.

1.Character Large object  -

• Character Large object is similar to char
• Fix Size is 4GB
• It does not accept any argument.

Example-

 varchar(10)    Varchar(30)              CLOB

 Product_ID      P_Name             Product_Description

2.Binary Large Object -Binary large object data type if it is given for any column that column can accept binary value.

• If a column needs to kept confidential we can convert data to binary by using several encryption technique.
• The fix size of binary large object is 4GB.