SQL Data Types

SQL Data Types

SQL Data Types are very useful while working with the SQL statements. Before learning about DDL statements, we need to learn Data Types which will help us in understanding  DDL statements better. In my next post I want to discuss about DDL statements in SQL. So, I am discussing SQL Data Types in this post as these are essential to learn DDL.

Data Type as specified by its name , will give us an idea about the type of the data. As, discussed in introductory topics table columns will be associated with the data types. These data types will decide the type of data which is going to be stored in those particular columns. Each column of the table will be associated with a specific data type.

  In SQL we have many data types. But in this post I will discuss only about most important data types of SQL which will be vastly used in creating the columns of the table. We have 4 important data types available in SQL.
They are,

               1. Number
               2. Char
               3. Varchar2
               4. Date

These are the most important data types in SQL.

1. NUMBER :: 


           Number data type will corresponds to the numeric data. It means the column created with NUMBER data type can store numeric data only. So, If we want to store numeric data like salary, empno... etc we can create columns with NUMBER data type.

          We have two different syntax's for using this Number data type.

                   NUMBER (m);
                   NUMBER(m,n);

      These two syntax's will be used for two different purposes. In numeric data we will be having two types of numbers called Whole numbers and Decimal point numbers. Whole numbers means numbers with out any decimal point . (Ex : 520, 44, 22,.....). Decimal point numbers means numbers with decimal point (Ex : 120.12, 34.2, 10.01, ......). So, to store these different Syntax's will be used.

     To store Whole numbers we will use the first syntax. That is, NUMBER(m). Here m will specify total number of digits allowed in the number to be stored.

    Ex : if we have created column with data type NUMBER(6), then in this column we can store numeric data up to 6 digit length like 1, 333, 22, 4444, 55555, 666666....... .
 we cannot store 1234567, as it contains 7 digits in it.

   This syntax cannot be used to store decimal point data as, it will give you improper results. If you store decimal point data using above syntax, it will store it as a whole number only.

  Ex : if we store 10.56 in a column created using above syntax, then it will store it as 10 only. So, we will get improper results.

So, If we want to store decimal point numbers we have to use the second syntax. NUMBER(m,n). Here m will specify total number of digits including digits after decimal point allowed in the number to be stored and n will specify number of digits allowed after decimal point in the number in m digits. Let me explain this with an example.

  Ex : If we have created a column with data type NUMBER(10,3) . Then in this column we can store decimal point data up to 10 digit length. In these 10 digits before decimal point 7 digits are allowed and after decimal point 3 digits are allowed.

  1235467.812  we can store . 12345678.1 we cannot store as we have 8 digits before decimal point. we can have any number of digits after the decimal point, but data base will do appropriate rounding off operation before storing that data. But the number of digits before decimal point should be <=7 in this case. In general should be <=m-n.

 Using Number data type columns we can store numeric data up to 38 digit length. If you want to use maximum size for numeric column (38 digits), then you can create column with datatype specified as NUMBER without giving any braces and size. In this case by default we can store number up to 38 digit length in that column.

2.CHAR :: 


       CHAR data type will be used to store character type of data or string data like names etc. CHAR data type specifically used for storing the fixed length character data. Fixed length character data means, data which will not vary in size at any point of time. Best example for this type of data is, Gender column which will be used to store the genders of employees. For this column values will be either 'M' or 'F'. So, at any point of time gender data will contain single character only which is fixed. To handle such type of fixed length character data we will use CHAR data type.

    The syntax for CHAR data type is,

                            CHAR(n);

     Here n means maximum number of characters allowed in the data to be stored in that particular column created using this data type. while handling character data in SQL we have to handle that data using single quotes. Wherever we use character data, we must enclose it in single quotes.

       Ex : 'Ramu', 'Raj', 'Bala', ...... etc.

   If we have any spaces between the strings in single quote then data base will treat each space also as a character (called as null character).

     Ex : 'Ram Babu' --- length of this string will be 8 (including one space)

  So, if we have created a column with data type as CHAR(20) then we can store fixed length character data up to 20 character in it.

  We can store variable length character data also in CHAR columns, but it is not advisable. Let me explain this with a small example.

  Let us say we have created a column with data type as CHAR(10). Now I want to store the name 'Ramu' in it. Then how data base will store this name is, It will all the 10 character space to store this 4 character name 'Ramu'. It will keep remaining 6 characters as null. So, here 6 character memory will be wasted. So, the problem with this data type is , It will use all the space specified while creating the column, irrespective of the length of input string.

  So, this is the disadvantage of CHAR datatype. This is the reason why it is used to store fixed length character data only.

 Using this data type we can store fixed length character data up to 2000 character length.

3. VARCHAR2 ::
       
             VARCHAR2 data type will also be used to store character or string data only. But, this data type will be specifically used to store the variable length character data. Variable length character data is a data, which varies in size from one value to another value. Example for this data is ename which can be of different sizes for different employees. To handle such type of variable length character data we will use VARCHAR2 data type.

        The syntax for using VARCHAR2 data type is,

                         VARCHAR2(n);

      Here n represents maximum number of characters allowed in the string which we want to store. As, I mentioned earlier this will be used to store variable length character data.Let me explain this with small example.

    Let us say we have created a column with data type VARCHAR2(10) and we want to store name 'Ramu' in it. Then what data base will do is, it will use only 4 characters space to store 4 character length name 'Ramu'. So, here memory is not wasted. This is the reason for using VARCHAR2 data type to store variable length character data.

     So, if we create a column with data type VARCHAR2(20), then we can store character data up to 20 character length in that particular column.

   Using this data type we can store variable length character data up to 4000 character length.
we will use
        


4. DATE ::


       DATE data type will be used to store dates in the data base. While handling dates in SQL, like handling in character data, we need to enclose them in single quotes. So, wherever we use dates in SQL, we must enclose them in single quotes. We cannot use dates in whatever format we want in SQL. There is specific format for handling the dates in SQL, which is a database understandable format. We have to use dates in that particular format only.

     The format for using dates in SQL is,

                 'dd-mon-yy'  or  'dd-mon-yyyy'.

     Here dd ---- 2 digit date, mon --- 3 character month and yy or yyyy ---- 2 digit year or 4 digit year respectively.

      Ex : '12-aug-11' ,  '22-sep-2011', ....... etc.

   So, in order to handle dates in SQL we must enclose them in single quotes and we must use above formats only.

    To store these dates in the data base we need to have columns created in the data base with DATE data type. The syntax for using this data type is,

                     DATE
 So, this data type will be used to store dates in the data base.


With this all the 4 important data types in SQL are completed. In my next post I will discuss about DDL statements in SQL.