SQL Functions Part6 (Conditional Functions)

Conditional functions are very useful and very important in writing real time SQL queries. They will provide the ability to get the different results in the same query based on the evaluating conditions. This is similar to the IF - THEN - ELSE logic present in other programming languages.

It is not possible to write the IF - THEN - ELSE logic directly in a SELECT statement. So, to achieve the conditional logic in SQL statements, we can use the conditional functions. We will get clear picture on what conditional functions are and how they will work, in below paragraphs.

There are two conditional functions available in SQL. They are,

                                          1.Decode

                                          2.Case

Let us see what each function can do and how we can use them.

Decode Function ::

-----------------------

This Decode function is the exact replica / replacement for the simple IF statement in other programming languages which involves single equality comparison operation like following.

if(a=b) then

 result1;

if (a=c) then

 result 2;

else

result 3;

end if;

end if;

To be clear we have added multiple if's , but basically here we are comparing (using = operator)

value of a with all other values using these if conditions. If we have any requirement to get this

type of data we can go for Decode function.

Using this function we can evaluate single condition only and also we can perform equality

operation only.

Syntax for this function is as below.

Decode (column or expression , value1 , result1, value2, result2, .... default result);

Here column or expression corresponds to the table column or expression whose value we want to compare with other values while giving the result (In the above example it is a).

value1, value2,... are the values which we are comparing to the column / expression value (In the above example b, c are those values).These values again can be either columns, expressions or general values.

result1, result2 ... are the results which we want to return when condition matches. These results again can be either columns, expressions or general values.

Default result value is the one which we want to use when all the conditions fail.

Let us see on example on how this function works.

Let us say we have Emp table which has empno, ename, deptno columns in it.

Let us say we know names of all the departments based on their deptno.

Now we want to display all the data from the emp table by including department name also

in the result.

For this we need to compare our values with deptno column.

Query to get this data can be written as the following.

Select empno,

           ename,

           deptno,

           Decode(deptno,10,'Accounting',20,'Sales','Marketing') Dname

From Emp;

So, Here in decode statement we have use he deptno as the comparison column and

10, 20 as the comparison values and 'Accounting' , 'Sales' as the results corresponding to

the comparison evaluation and 'Marketing' as the default result.

Now, let us say if data in Emp table is as below

1234  Ram   10

2345  Raj     20

1256  Balu   30

2367  Somu  20

4567  Suri    10

Result of the above query will be as shown below.

1234  Ram   10   'Accounting'

2345  Raj     20   'Sales'

1256  Balu   30   'Marketing'

2367  Somu  20  'Sales'

4567  Suri    10  'Accounting'

So, basically following if condition is evaluated using this.

If deptno = 10 then 'Accounting'

If deptno = 20 then 'Sales'

else 'Marketing'

So, like this using Decode function we can evaluate equality conditions on single columns in the SQL queries.

Case Function ::

--------------------

Case function is more generic function without any limitations. This means that, we can evaluate

any number of conditions involving any number of columns / expressions using this function. Also we can use any operator in this function. So, this function is exact replica / replacement for the IF - THEN - ELSE logic of other programming languages as below.

IF (a=b and b=c) Then

result1;

elsif (f=o and h=y) then

result2;

else

result3;

End;

Like this if we wan to evaluate multiple conditions on multiple columns / expression to give the

results in SQL queries then the function available is Case Function.

Syntax for this function is as shown below.

(Case When condition1 Then Value1

          When condition2 Then Value2

           ........

          Else  default_value

          End)

Here Condition1, Condition2... are the conditions (can be on single column or multiple

columns and can be combination of multiple conditions also) which we want to evaluate or

check for producing the result.

Here Value1, value2, ... are the results which we want to give when condition is met. These

values again can be either columns, expressions or general values.

default_value present in the Else clause is the one which we want to give as a result when all the conditions failed.

Let us see one example on how this function works. Let us use same example as that of Decode and see how we can write the same using case.

Select empno,

           ename,

           deptno,

           (Case when deptno=10 then 'Accounting'

                     when deptno=20 then 'Sales'

                     else 'Marketing'

                     End)

From Emp;

This query will produce the same result as that of the Decode query. Here as you can see we have the flexibility of writing complete condition (like deptno=10) which ensures that we can have complex conditions evaluated.

So, like this we can use Case function for implementing the IF-THEN-ELSE logic in the SQL statements.

So, these are the different Conditional Functions which are available in SQL.

SQL Functions Part5 (General Functions)

General functions are very useful while working with SQL and PL/SQL. These functions are used for working with null values and can handle any data type. We have 4 functions available in SQL which comes under general functions.

They are,

       --> NVL Function
       --> NVL2 function
       --> NULLIF function
       --> COALESCE function

Basically all these functions are used for avoiding wrong results in SQL statements which involves null
values. For example let us say we have two columns in Employee table named Sal and Bonus. Sal column will have values of employee salary and Bonus column will have values of bonus. If we want to find out total sal of employee we need to sum the values from these two columns.

Let us say we have following values stored in employee table.

Emp1      2000    null
Emp2      1500    200
Emp3      3500    null

Now if we write following query on this table result will be as shown.

SELECT ename,sal+bonus from emp;

Result ::      ename   sal+bonus
                  Emp1       null
                  Emp2       1700
                  Emp3        null

We can see from the result that total sals for employees Emp1 and Emp3 is coming as null. This is because
 we have null values stored for these employees in bonus column and in SQL null means nothing. If we add
 some value to null then result will be null only. To get correct results in such type of situations we will use
 general functions. Let us discuss these functions now.

NVL Function ::
===========

If any column contains null values then arithmetic expressions involving those columns will give inappropriate results as shown in previous example. To avoid this we need to handle those null values. NVL function can be used to handle null values present in the column while using them in SELECT statements.

SYNTAX ::   Syntax for NVL function is,

   NVL ( column or expression , default value)

Here column or expression will be the one which we are expecting to contain null values and which we want to handle using NVL function.

default value is corresponding to the value which we want to use in case null value is present in that particular column. We have to provide correct default values depending on requirement to get proper results.

In our previous example to get correct total salary we can use 0 as default value. Then query can be written as following.

SELECT ename, sal+nvl(bonus,0) from emp;

No this will give result as,
           
         Emp1     2000
         Emp2     1700
         Emp3     3500

Like this we can avoid wrong results in SQL statements involving null values.


NVL2 Function ::
============

This function is bit different from NVL function and will be used rarely in SQL. This functions contains 3 arguments and the result will always be decided on first value.

SYNTAX :: Syntax for this function is,

   NVL2(col1 or exp1, col2 or exp2 or val1, col3 or exp3 or val3)

Here first argument corresponds to column or expression based on which we want to decide the result and 2nd argument and 3rd argument will give us the results based on first argument.

How this NVL2 function works is if the value of first argument is not null then this function will give second argument value as result. If value of first argument is null then this function will give third argument as result.

For example if we write query on data present in previous example as below,

SELECT ename, NVL2(bonus,sal,5000) from Emp;

Then result will be as shown below.

   Emp1    5000
   Emp2    1500
   Emp3    5000

For Emp1 and Emp3 bonus is null so 5000 will come as result . For Emp2 bonus is not null so sal value which is 1500 will come as result.

This is how NVL2 Function will work.

NULLIF Function ::
==============

This function is also different from other general functions in the way how it works. Basically this function will be having two arguments as inputs and it will produce the results based on those two arguments.

SYNTAX::   Syntax for this function is,

NULLIF (col1 or exp1, col2 or exp2)

Here both the arguments are responsible for the result. This function will compare the values of the two arguments and returns null if both are equal and returns first argument value if both are not equal.

For example if we write query as,

SELECT NULLIF(col1,col2) from tab;

if values of col1 and col2 are 20 and 30 then result will be 20
if values of columns is 10 and 10 then result will be null.

So, this is how NULLIF function will work.

COALESCE Function ::
=================

This function will work same as the NVL function. But only difference is that it will be having multiple arguments ( more than 2 unlike NVL). It will return first not null value from input argument list .

SYNTAX :: Syntax for this function is,

COALESCE(arg1, arg2, arg3, arg4,...)

Here we can pass any number of columns or expressions as arguments to this function. This will give us the first not null value from left of the argument list as result.

For example,

If values of arguments is like null,null,3,4.. then result will come as 3
If values are like 1,null,null,.... result will be like 1.

So, this works similar to NVL function only but the only difference is that it will have multiple inputs.

So, these are the different General Functions which are available in SQL. Among all these functions NVL is the most used function in SQL queries.

SQL Functions Part4 (Conversion Functions)

Conversion functions are very useful while working with SQL or PL/SQL. These functions are used for data type conversions. These functions play big role in writing SQL queries and PL/SQL blocks.

We have two types of data type conversions.

     1) Implicit Data Type Conversion
     2) Explicit Data Type Conversion

Implicit Data Type Conversion ::


    Implicit data type conversion is done by Oracle server. The data type conversion which is done implicitly by the Oracle server is called as Implicit Data Type Conversion.

    For assignments (means assigning values to variables) Oracle server can automatically do following conversions.

        VARCHAR2 or CHAR       --->       NUMBER
        VARCHAR2 or CHAR       --->       DATE
        NUMBER                         --->       VARCHAR2
        DATE                               --->        VARCHAR2

   These are the conversions which will be done by Oracle server automatically when doing assignment operations.

  For Ex,

      If we have declared a variable x of Varchar2 data type and if we assign value 4 (Number) to it as shown below,

     x =4 ;

    Then Server will store this value as character value ('4') because of implicit conversion.

  Note : For this implicit conversion to be successful valid value should be there. It means we cannot convert 'ab' to number as it did not contain valid number. We can convert '44' to number as it contain valid number.

 For expression evaluation (means calculations involving arithmetic operations) Oracle server can automatically convert the following.

   VARCHAR2 or CHAR     --->    NUMBER
   VARCHAR2 or CHAR     --->    DATE

  Note :  CHAR to NUMBER conversions will succeed only if character string represents a valid number.

 Although Implicit conversion available it is better to go for explicit data type conversion for reliability and better performance.


 Explicit Data Type Conversion ::


   The data type conversion which is done by users explicitly will be called as Explicit Data Type conversion. We will use data type conversion functions for doing Explicit conversion.

 There are 3 explicit data type functions available in SQL.

       1) To_Char
       2) To_Date
       3) To_Number

 To_Char Function ::

    This function is used to convert the data of NUMBER or DATE data type to VARCHAR2 data type.

 SYNTAX ::  To_Char (Number or Date, Format)

 Here Number or Date refers to the Number or Date data which we want to convert to the Character data.
  Format refers to the output format to which we want to convert date or Number data.

  Here format will contain valid format models of Number or Date conversions. We have many format models for Numbers and Dates. Here I will give some important ones which will be frequently used in conversions.

For Number conversions format models can be used are,

  $    -   Dollar symbol used for representing the money
  L    -  Local currency symbol will be shown if used
  9    -   Used as replacement for digits in the input number
  0    -   Used to place 0's in the output
  ,     -   comma seperator
  .     -   used in decimal point numbers

 For Ex,

 Let us say we want to convert number 44444.44 to character string like '$44,444.44' then we
 can use following,

   SELECT To_Char (44444.44, '$99,999.99') From Dual;

 This will give output as $44,444,44. So, this is how we can convert Numbers into Character Data
 using format models.

 For Date conversions format models can be used are,

 MM        -   2 digit month (Ex: 20)
 MON      -   3 character month (Ex: 'JUN')
 Month     -   Full name of Month (Ex: 'JUNE')
 DD         -   2 digit date (Ex: 11)
 DY         -   3 character day (Ex : 'MON')
 DAY       -   Full name of Day (Ex: 'MONDAY')
 YY         -   2 digit year (Ex: 99)
 YYYY    -   4 digit year (Ex: 1999)
 Year        -   year spelled out (Ex: Nineteen Ninty Nine)
  MI         -    Minutes
  SS         -    Seconds
  HH12    -    Hours in 12hr format
  HH24    -    Hours in 24hr format
  fm         -    can be used to suppress leading 0's in day of input date
                    (Ex: 09 will be shown as 9)
  sp          -   used to spell out the day in input date (Ex: seven)
  th          -    used to add th or nd or rd in output while spelling out day in input date
  -, . , /     -   separators used to separate day, month and year in output


 Ex,


  Select To_char ('14-jul-2012', 'fmddspth "of" Month YYYY') From dual;


 This will give output as 14th of July 2012. we can use "of" also in output string as shown.


 While using this function we must keep in mind that the format string which we will provide to the function will be that of in which format we want the output to be displayed.


 So, this is how we can convert Numeric or Date data into character data using To_Char function.


 To_Number and To_Date Functions ::


   To_Number function is used to convert character data to Numeric data. To_Date function is used to convert character data to date data. In order to convert Character data to Numeric or Date data input character data must contain valid Numbers or dates respectively.

  Syntax ::  To_Number (i/p char, format)
                 To_Date (i/p char, format)


   In both syntax i/p char refers to the input character data which we want to convert to  numeric data or Date data.


  format refers to the format in which we are providing input character data .


 Here also we use format models. These are same as which we have discussed in To_Char function.


 Ex : SELECT To_Number ('$46,567.67', '$99,999.99') From Dual;


 This will give output as 46567.67.


  SELECT To_Date ('19/04/1999','dd/mm/yyyy') From Dual;


  This will give output as '19-Apr-1999' (Remember this is default and valid format of dates in SQL).


 This is how we can convert Character data to Numeric or Date data.


Note : The most important point to keep in mind while working with To_Char, To_Number and To_Date functions is,


 --> In To_Char function the format we will provide is of output.
 --> In To_Date and To_Number functions format we will provide is of input.

SQL Functions Part3 (Date Functions)

We have many date functions available in SQL which are very useful while working with data of date type in SQL. Date functions will accept date data as input and will produce either date or numerical output depending on the function.

We have many date functions available in SQL. We can do arithmetic operations also on dates. So, First I will discuss about the arithmetic operations which we can do on date data. Before discussing about this we need to know about SYSDATE function.

SYSDATE function :


   SYSDATE is a function which will always give the current date according to the database system. So,if we want to use current date in any of the SQL queries then we can use SYSDATE function. This is the only function which will not contain any arguments.

 SELECT sysdate FROM dual;

This query will give the current date according to database system.

Arithmetic Operations On Date Data ::


   We can add a number to date by using + operator. If we add any number to date, then database will provide the result by adding those many number of days to that date.

Ex : SELECT sysdate +  4 From dual;

 This query will give the result by adding 4 days to sysdate . For ex , if it is 13-nov-2011 then we will get result as 17-nov-2011.

    We can subtract a number from a date by using - operator. If we subtract any number from date, then database will provide the result by subtracting those many number of days from that date.

Ex : SELECT sysdate -  4 From dual;

 This query will give the result by subtracting 4 days from sysdate . For ex , if it is 13-nov-2011 then we will get result as 09-nov-2011.

   We can subtract two dates also using - operator. This will give us the difference between those two dates in terms of number of days. So, if we want to find date difference in terms of number of days then we have to use - operator.

 Ex : SELECT to_date(17-jun-2011)-to_date(14-jun-2011) from dual;

  This will give us the result  as 3. Here I have used to_date function because, generally arithmetic operators will expect numeric values. So, even if we provide date values in valid format it will treat it as number and will throw invalid number exception. To avoid it, we have to convert these two dates using to_date function. So, that it will consider these two values as dates. I will discuss about this to_date function in detail in my coming posts.

So, this is about the arithmetic operations which we can do on date data.

Date Functions ::

  We have many date functions available in SQL. I will explain about some important date functions in this post.

ROUND() Function :


   Round function is used to rounding off  the date values. We can round the date values depending on either month or year . For this function input is date and output is also a date.

Syntax : Round ( date , 'MONTH' or 'YEAR')

  In this syntax,

   ---->  we have to give second argument as either 'MONTH' or 'YEAR' depending on with respect to
             what we want to round the date.

    The logic for rounding of with month is,

   * if date in input date is <15 then we will get first of current month in input as output.
   * if date in input date is >=15 then we will get first of next month  as output.

  Ex : SELECT ROUND(to_date('14-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011' as day<15.

    SELECT ROUND(to_date('16-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-aug-2011' as day>=15.

    The logic for rounding of with year is,

   * if month in input date is <6 then we will get first of current year in input as output.
   * if month in input date is >=6 then we will get first of next year  as output.

   Ex : SELECT ROUND(to_date('14-mar-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011' as month<6.

    SELECT ROUND(to_date('16-jul-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2012' as month>=6.

TRUNC() Function :


   Trunc function is used to truncate  the date values. We can truncate the date values depending on either month or year . For this function input is date and output is also a date.

Syntax : Trunc ( date , 'MONTH' or 'YEAR')

  In this syntax,

   ---->  we have to give second argument as either 'MONTH' or 'YEAR' depending on with respect to
             what we want to truncate the date.

    The logic for truncating of with month is,

   * irrespective of date in input date, we will get first of current month in input as output.

  Ex : SELECT TRUNC(to_date('14-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011'.

    SELECT TRUNC(to_date('16-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011' .

    The logic for rounding of with year is,

   *  irrespective of month in input date, we will get first of current year in input as output.

   Ex : SELECT TRUNC(to_date('14-mar-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011'.

    SELECT TRUNC(to_date('16-jul-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011'.     


  MONTHS_BETWEEN() Function :


      Months_Between() function is used to find difference between two dates in terms of number of months. For this function input is dates and the output is number.

 Syntax : MONTHS_BETWEEN ( date1, date2 )

 Ex : SELECT MONTHS_BETWEEN ( to_date('14-jul-2011'), to_date('21-sep-2011')) FROM dual;

  For this query we will get output as -2.2258065. We got negative value because we gave least value as the  first  input.

  ADD_MONTHS() Function :

      Add_Months() function is used to add certain number of months to the input date.  For this function input is date and output is also a date.

 Syntax : ADD_MONTHS(date, number_of_days)

  In this syntax ,

   number_of_days is the number which will decide number of months to be added to input date.

 Ex : SELECT ADD_MONTHS (to_date('12-jun-2011'), 4) FROM dual;

 This will give output as '12-oct-2011' by adding 4 months to input date.

  NEXT_DAY() Function :


    Next_day() function is used to find the next day as specified in the syntax. For this function input is a date and output is also a date.

  Syntax : NEXT_DAY(date,'DAY')

  In this syntax,

     --> 'DAY' refers to the day which we want after the specified date. List of values for this day are,

           'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT, 'SUN'.

      This means that, if we give day as 'THU' in the syntax, then the database will provide the output as the date on which next Thursday will come from the input date.

  Ex : SELECT NEXT_DAY(sysdate, 'FRI') FROM dual;

  Let us say here sysdate is '13-nov-2011' which is Sunday. So, we will get the output for the above query as '18-nov-2011' . This is the date on which we will get immediate next friday after the date '13-nov-2011'.


  LAST_DAY() Function :


     Last_Day() function is used to find the date of last day of the month specified in the input date. For this function input is a date and output is also a date.

   Syntax :  LAST_DAY (date);

  Ex : SELECT LAST_DAY (to_date('15-jul-2011')) From dual;

  This will give us the output as '31-jul-2011' as it is the last day of july month which is specified in the input
  date.

 These are the some of the important date functions which are available in SQL.

  

SQL Functions Part2 (Character Functions)

We have many character functions available in SQL which are very useful while working with the SQL statements. In this post I will discuss about those functions.

As the name it self specifying, character functions will work on character data . Character functions will accept character data as input and will produce output as either character or numeric data based on the type of function we are using.

Basically character functions are divided into two types.

    1. Case manipulation Functions
    2. Character manipulation Functions

Case manipulation Functions ::

   Case manipulation functions will work on the case of the characters. These are used for case conversions.

We have three functions available under this category.

   a) UPPER()
   b) LOWER()
   c) INITCAP()

UPPER() :

   This function will convert all the characters of input string to upper case letters. For this function input is a character and output is also a character.

 Syntax :  UPPER(i/p string)

 Ex : SELECT UPPER('Ram Babu') FROM dual;

 This will give result as 'RAM BABU'.

 LOWER() :

    This function will convert all the characters of input string to lower case letters. For this function input is a character and output is also a character.

 Syntax :  LOWER(i/p string)

 Ex : SELECT LOWER('Ram Babu') FROM dual;

 This will give result as 'ram babu'.

 INITCAP() :

     This function will convert first character of input string to upper case letter and remaining characters to lower case letters. For this function input is a character and output is also a character.

 Syntax :  INITCAP(i/p string)

 Ex : SELECT INITCAP('rAM Babu') FROM dual;

 This will give result as 'Ram babu'.   
      
  These are the three case manipulation functions available in SQL. These are very useful in writing SELECT statements. For example let us say we want to find out all the employees from employee table whose name is 'Abc'. As I already discussed in my previous posts character comparison is case sensitive. We don't know how the names were stored in the employee table. the same name Abc can be stored in many ways like ABC, abc, Abc. But we want all those results. In this case case manipulation functions will help us.

Query for this scenario can be written as,

  SELECT * FROM Emp WHERE upper(ename) = 'ABC';

 In this query I am converting the ename to upeer case letters and comparing it with 'ABC'.So, in this query irrespective of case of names in the table we will get all the employees with name abc using the above query.

So, like this we can work with case manipulation functions.

Character manipulation Functions ::

   Character manipulation functions will accept character inputs and will produce either character or numeric outputs. We have many character manipulation functions available in SQL. I will discuss important character manipulation functions in this post.

CONCAT() Function :

   This function is used to combine two strings into a single string. For this function input is a character string and output is also a character string. Using this function we can combine only two strings at a time.

  Syntax :  CONCAT ( str1, str2 )

 Ex : SELECT CONCAT ('Ram','Babu') FROM dual;

 This will give the output as 'RamBabu'.

 If I want to combine more than two strings then I have to go for nesting of functions.

 Nesting of functions ::

   If we call a function inside another function, then it will be called as nesting of functions. In this we will give output of one function as input to other function.

So, in case of CONCAT() function if I want to combine more than two strings then I have to go for nesting.

  CONCAT(str1,CONCAT(str2,str3))

Ex : SELECT CONCAT ('Kandimalla',CONCAT('Ram','Babu')) FROM dual;

  So, this will give output as KandimallaRamBabu. Here I gave output of the one CONCAT function as input to another CONCAT function. Like this I can do nesting of the functions.

SUBSTR() Function :

  This function is used to extract some part of input string. This function will accept character input and will produce character output. Syntax for this is,

 Syntax : SUBSTR(i/pstring , starting_pt_of_extraction, o/pstring_length)

 Here in this syntax,

  --> starting_pt_of_extraction is a number which will specify the position in input string from which the
        extraction should be started.
  --> o/pstring length will decide number of characters to be there in output string.

 Ex : SELECT SUBSTR('Hello World', 3, 5) From dual;

  Here we gave second argument as 3. It means extraction should start from 3rd position. So, it wil start from letter 'l'. we gave 3rd argument as 5. It means we need 5 characters to be present in output. So, we
will get output as,

    'llo W'.

This is how substring function will work.

 * If we exclude 3rd argument in the syntax, then by default total string from the starting point of extraction
    will be extracted.

   Ex : SELECT SUBSTR('Hello World',3) FROM dual;

  This will give output as,  'llo World'.

 We can extract the strings from ending also using SUBSTR function. For this we just need to give two arguments. One is input string and other one is number of characters to be there in output string (-ve value).

 Ex : SELECT SUBSTR('Hello World',-4) FROM dual;

 This will give output as, 'orld'.

 INSTR() Function :

     Instring function is used to find the position of a string in the input string. For this function input is a character string but the output is a number. (As we are finding position).

 Syntax :  INSTR (i/pstring, search_string, starting_pt_of_search, occurance)

  In this syntax,

  --> i/pstring refers to the Input string
  --> search_string refers to the string which we want to search in the input string
  --> starting_pt_of_search is a number which refers to the position from which we want to start searching
  --> occurance refers to which occurance of the string we want.

  This function will always give us the position of the string by calculating it from first position only irrespective of the starting_pt_of_search. This starting_pt_of_search is used for deciding occurance of the string. Let me explain this with an example.

 Let us say we have string 'Hello World'. In this string I want to search for string 'l'. But we have three 'l' s in the input string. So, which 'l' position we want ???. This will be decided by the occurance. So, if I say occurance as 1 it means I want position of first 'l'. If it is 2 then I want position of 2nd 'l'.
Again this occurance will be decided by starting_pt_of_search. If I start searching from 4th position then I will be having two occurances of 'l' only as one 'l' is in 3rd position. In any case final position of the string will be calculated from first position only.

 Ex : SELECT INSTR('Hello World','l',4,3) From dual;

  This will give result as 0. Because from 4th position we don't have 3rd occurance for 'l'.

    SELECT INSTR('Hello World','l',3,3) From dual;

  This will give result as 10. As third 'l' from position 3 is at 10th position in whole string.

    SELECT INSTR('Hello World','l',4,1) From dual;

  This will give result as 4. As 1st 'l' from position 4 is at 4th position in whole string.

If we exclude 4th argument in the syntax then by default it will give the position of first occurance of the string
specified by starting_pt_of_search.

   SELECT INSTR('Hello World','l',2) From dual;

  This will give us result as 3. As 1st 'l' from position 2 is at 3rd position in whole string.

If we exclude both 2nd and 3rd arguments in the syntax then by default we will get 1st occurance of the string from 1st position.

  SELECT INSTR('Hello World','l') From dual;

  This will give us result as 3. As 1st 'l' from position 1 is at 3rd position in whole string.

 This is how we can find the position of a string in the input string using INSTR() Function.

Length() Function :

    Length function is used to find the length of the input string in number of characters. For this function input is a character string and output is a number.

Syntax : Length(i/pstring)

 Ex : SELECT LENGTH('Hello World') FROM dual;

 This will give us the output as 11 as space is also a character.

LPAD() and RPAD() Functions :

   These functions as used to pad a character or set of characters to either left (LPAD) or right (RPAD) of the input string. This is similar to concatenation. But here we will concatenate same string number of times on either left or right side depending on the output length specified.

Syntax : LPAD(i/pstring, length_of_o/p_string, padding_string)
               RPAD(i/pstring, length_of_o/p_string, padding_string)

 Here in this syntax,

 --> length_of_o/p_string is a number which will specify the total length of output string we want after
       padding.
 --> padding_string will specify the string to be padded.

 Ex : SELECT LPAD('Ramu', 9 , '*') From dual;

  This will give output as, '*****Ramu' as we have mentioned total output string length as 9, we will get 5 stars padded to the input string on left side.

    SELECT RPAD('Ramu', 9 , '*') From dual;

  This will give us the output as, 'Ramu*****'.

 If we want to pad the same string on both sides of the input string then we have to use nesting of the functions concept. So, here first we have to use LPAD or RPAD and then we have to give output of these as input to RPAD or LPAD respectively to get desired result. This can be written as shown below.

  SELECT RPAD(LPAD('Ram',9,'*'),14,'*') From dual;

  This will give us the output as, '*****Ramu*****'.

If we have not specified the sufficient output string length to accomadate all the character of padding string then some part of string will be padded depending on the length.

  SELECT LPAD('Hello',7,'abcd') From dual;

  For this we will get output as 'abHello'. Even though we have 4 characters in the padding string, only two characters were padded to input string because of the output length we have specified.

TRIM(), LTRIM() and RTRIM() Functions :

   Trim() Function is used to trim heading and trailing characters from the input string.

 Syntax : TRIM(Leading or Trailing or both, trim_character from i/pstring)

   In this syntax,

   --> if we gave leading then character in first position will be compared for removal. If we gave trailing
         then character in last position will be compared for removal. if we gave 'both' then both first and
         last characters will be compared for removal.

   Ex : SELECT TRIM(Leading 'H' from 'Hello WorldH') From dual;

    This will give us output as 'ello WorldH'.

    SELECT TRIM(Trailing 'H' from 'Hello WorldH') From dual;

    This will give us output as 'Hello World'.

    SELECT TRIM('H' from 'Hello WorldH') From dual;

    This will give us output as 'ello World'. Here no need of specifying 'both' keyword as it is default.

  This TRIM() function can be used to remove heading and trailing spaces from the input string.

  Syntax : TRIM(i/pstring)

   So, if we give like this then both heading and trailing spaces if any will be removed from input string.

  Ex : SELECT TRIM('   Hello World    ') From dual;

   This will give output as 'Hello World'.

 LTRIM() function is used to remove all the spaces which are at the starting of the input string. (left side).

 Syntax : LTRIM(i/pstring)

  Ex : SELECT LTRIM('   Hello World    ') From dual;

   This will give output as 'Hello World    '.

 RTRIM() function is used to remove all the spaces which are at the ending of the input string. (right side).

 Syntax : RTRIM(i/pstring)

  Ex : SELECT RTRIM('   Hello World    ') From dual;

   This will give output as '    Hello World'.

REPLACE() Function :

  Replace function is used to replace some part of the input string with other string.

 Syntax : REPLACE(i/p string, search_string, replacement_string)

  Here in this syntax,

    ---> search_sting refers to the string which we want to replace in the input string
    ---> replacement_string refres to the replacement string for the search_string

  Ex : SELECT REPLACE('Ram Babu','Ram','Mahesh') From dual;

  This will give us the output as 'Mahesh Babu'. This is how we can use REPLACE function.

So, this is about the some of the important character functions available in SQL.

Note :Here in this post I have covered all the important character functions. If any information is required on any other character functions, post in comments. I will provide the required explanation.

SQL Functions Part 1 (Number Functions).

We have many built in functions available in SQL which are very useful while working with the SELECT statements. Depending on how they are working and how they are producing the results, SQL functions are divided into two types.

    1. Single row Functions
    2. Multiple row Functions.

In this post I want to discuss about some of the single row functions available in SQL.

Single row Functions ::


     The functions which will work on each row of a table and which will produce a result per each row are called as Single row functions. These functions will produce a result for each row they are effecting.

We have six types of Single row Functions available in SQL.

   1. Number Functions
   2. Character Functions
   3. Date Functions
   4. Conversion Functions
   5. General Functions
   6. Conditional Functions

In this post I want to discuss about Number functions. I will discuss about remaining functions in my coming posts.

1. Number Functions ::


  The functions which will work on numeric data are called as Number Functions. These functions will accept numeric input and will produce the numeric output.

 There are three numeric functions available in SQL.

 a) Round()
 b) Trunc()
 c) Mod()

Round() Function :


   This function is used to round the column or value or result of an expression to specified decimal places.
This will follow the mathematical rounding of operation rules.

  Ex : if we are rounding 423.456734 to 2 decimal places then result will be 423.46. (In rounding of operation 1 will be added to preceding number if current number is >=5).

 Syntax :: Round(input number, precision)

  Here precision will decide the number of decimal places to be present in the output.

 Ex : SELECT Round(2345.76234,2) FROM dual;

  This query will give result as 2345.76.

 If we exclude the precision in the syntax, then by default database will take it as 0.

 Ex : SELECT Round(2345.76234) FROM dual;

 This query will give result as 2346.

 If we give precision as negative value then the numbers to the left of decimal point will be rounded.

  Ex : SELECT Round(2345.7634,-2) FROM dual;

  This query will give result as 2300. Let me explain this. If, we gave negative precision then the database will replace those many digits to the left of decimal point with '0' and will round that number to nearest whole number. In this example we gave precision as -2. So, it will replace 4,5 with two '0' s. After replacing this, preceding number will be decided by nearest whole number concept. Nearest whole number to 345 is 300.
So, we will get result as 2300.

   SELECT Round(2366.7634,-2) FROM dual;

 This query will give result as  2400 as nearest whole number to 366 after replacing two '0' s is 400.

This is how we will use Round() function.

Trunc() Function ::


  This function is used to truncate the value or column or result of an expression to specified decimal places. This function is similar to Round() function but the only difference is here rounding of rules in mathematics are not applicable.

 Ex : if we are truncating 423.456734 to 2 decimal places then result will be 423.45.

 syntax :: Trunc(input number, precision).

   Here also the precision will decide the number of decimal places to be present in the output.

Ex : SELECT Trunc(4235.67895,2) From dual;

  This will give us result as 4235.67. If we do rounding of on this we will get, 4235.68 due to rounding of rules. But truncate will just truncate the number to specified decimal places.

  If we exclude the precision in the syntax then by default database will take it as zero.

 Ex : SELECT Trunc(4235.67895) FROM dual;

  This will give us result as 4235.

 If we give precision as negative value then the numbers to the left of decimal point will be truncated.

 Ex : SELECT Trunc(4235.67985) FROM dual;

  This will give us result as 4200. Here also nearest whole number concept will not be followed. This will just replace those many number of digits specified in negative precision with '0' s.

 Mod() Function ::


    This function will be used to find the remainder of a division of a number with another number. This will contain two input numeric arguments.

 Syntax ::  Mod(number1,number2).

    This will give us the remainder of division number1/number2.

  Ex : Select Mod(17,5) From Dual;

    This will give us the result as 2.

 Division operator which we have discussed in previous posts will give us quotient of the division. If we want to find out the remainder of the division then we have to use the Mod function.

 These are the different numeric functions which are available in SQL.

Working with SELECT statement ...

In my previous post I have discussed about different operators available in SQL. In this post I want discuss about some important concepts related to writing the SELECT statement.

WHERE clause :


      Where clause can be used with all DML and DRL statements to limit  the effect of those statements to desired rows except INSERT statement. It means, we can have restrictions on rows to be displayed when using SELECT statement or we can specify which rows to be modified when doing UPDATE or we can specify which rows to be deleted using DELETE. So, WHERE clause is very useful while working with the SELECT statement.

     We can use any of the operators which we have discussed in my previous post in the conditions of WHERE clause.

   EX : SELECT * FROM Emp WHERE deptno = 10 AND sal BETWEEN 1800 AND 3000;

DISTINCT Key word :


      DISTINCT key word can be used to eliminate duplicates in the SELECT list. If we want to check set of distinct values in the column of a table then we can use DISTINCT key word. This will give us the distinct values present in particular column.

Ex : Let us say we want to find out different jobs present in a company by checking Emp table. But if we write SELECT statement on job column of Emp table then we will get lot of values because of the duplicity. This is because many employees will be there under single job. So, it is difficult to identify the unique jobs in the company. In this scenario DISTINCT will be very useful. This will display only unique or distinct values present in job column.

   SELECT DISTINCT job FROM Emp;

 We can have only one DISTINCT key word in a SELECT statement. We can list any number of columns after the DISTINCT key word.

Ex : SELECT DISTINCT job, deptno FROM Emp;

   This query will give us all distinct combinations of job, deptno from the employee table.

 Similarly we can use * also instead of column list as shown below.

     SELECT DISTINCT * FROM Emp;

  This will give us all the distinct rows from Emp table. If any row is present multiple times then it will show it only once in SELECT list.

  So, in this way we can eliminate the duplicates in SELECT list.

Column Aliasing :


     Column aliasing is very useful while writing the SELECT statements involving the expressions. Generally column aliases are used for display purpose in the SELECT statements. We can provide some meaningful name as alias for an expression in the SELECT list, to make it understandable for the users who are seeing the result.

    Aliasing is nothing but giving some other name to a column or expression. The scope of this new name will be only for that particular SELECT statement where we have used aliasing.

 Ex : Let us say we want to display the employee names and their total salaries from employee table. For this  we need to add sal and bonus columns of the employee table. The query can be written as,

   SELECT ename, sal + bonus FROM Emp;

But If we run this query then we will get total salary under column with name as sal+bonus . This will not be a proper column name to display as end users cannot understand it. So, here column aliasing will be very useful. Same query can be written using aliasing as shown below.

   SELECT ename , sal+bonus total_sal FROM Emp;

So, here I gave alias as total_sal to the expression sal+bonus. So, result of this expression will be displayed under a column with name total_sal which is a meaningful name understandable to any users.

If we are using the above syntax we cannot have spaces in the name of column alias. If we want spaces also in the name of column alias we have to use following syntax.

   SELECT ename, sal+bonus "Total Sal" FROM Emp;

  As, shown alias name should be enclosed in double quotes if we want to have spaces in it.

This is how we can use column aliases.

ORDER BY Clause :


     ORDER BY clause will be used for sorting the rows retrieved by SELECT statement. Using this clause we can sort the result of a SELECT statement in either ascending or descending order of single column or multiple columns. To sort the results in ascending order we will use ASC  keyword and to sort the results in descending order we will use DESC keyword.

     The default ordering using ORDER BY clause is ascending order. It means if we have not specified any thing about ordering after ORDER BY clause, then by default rows will be ordered in ascending order. So, ASC key word is not required to use as it is defaulting order. If we want to sort the result in descending order then we have to use DESC keyword.

  EX : Let us say we want to display details of all the employees in the ascending order of their salaries. Then,

     SELECT * FROM Emp ORDER BY sal ;

   If we want to sort in the descending order then,

     SELECT * FROM Emp ORDER BY sal desc;

  We can use any number of columns in the ORDER BY clause.

     SELECT * FROM Emp ORDER BY deptno, sal desc;

  If we have used more than one column in ORDER BY clause then the sorting will be done by using columns from lest to right . Let us consider above query for example.

   In this, first result will sorted in the ascending order of deptno and then in each department rows will be sorted in the descending order of the salary. So, like this we can sort the result using any number of columns.

In any SELECT statement the ORDER BY clause must be last clause. It means it should be placed at the end of the SELECT statement after all the conditions.

  The only place where we can use the column aliases which we have specified is ORDER BY clause.

    EX : SELECT empno, sal+bonus total_sal FROM Emp ORDER BY total_sal;

This is how we can sort the results using the ORDER BY clause.


   

Operators in SQL....

In this post I want to discuss about different operators which are available in SQL. These operators are very useful while writing the SQL statements depending on the requirement.

  The different types of operators which are available in SQL are,

        1. Concatenation Operator
        2. Arithmetic Operators
        3. Logical Operators
        4. Comparison Operators

  Before discussing about these operators we need to know about a table named 'DUAL' which is very
  important in writing SELECT statements.

  Dual ::


       Dual is a pseudo table which is having single row and single column. This is system table of database.
This table will not contain any data on its own. If we select the data using select statement from dual
table then we will see data as 'x'. This table is used to evaluate expression on the data which is not stored
in the database tables. So, this table is used for evaluation of general expressions.

      As, dual table will not contain any data, always it will display the output of the expression which we
are evaluating using dual.

EX : Let us say we want to check the sum of two numbers 234567 and 876954. If we want to add these
two numbers we can use + operator. But these two are independent numbers which are not stored in the
database. So, we need to evaluate arbitrary expression here. For this purpose dual table will be used.

   SELECT 234567 + 876954 FROM Dual;

So, this statement will give us the result of the sum of those 2 numbers. Like this, If we want to evaluate the
expressions involving all arbitrary values where data base tables not involved, we can use Dual table to evaluate them.

Now Let us discuss about the Operators in SQL.

Concatenation Operator ::


   Concatenation operator is used to combine two different strings into a single string. Using this operator we can combine multiple strings into single string. ' || ' will be used as concatenation operator in SQL.

 SYNTAX :  string1 || string2 || string3 ....

 EX : SELECT 'Ram ' || 'Babu' || ' Kandimalla' FROM dual;

  If we execute this statement then we will get output as , Ram Babu Kandimalla. So, here we have concatenated 3 strings into a single string using this operator. Whenever we have a requirement of combining two or more strings we can use concatenation operator.

 Ex : Let us say ,We have Emp table having many columns like empno , ename, sal, hiredate ..... . From this table I want to display the output in the format  like 'Ramu is earning sal of 5000'. This I can achieve with following query using concatenation operator.

  SELECT ename||' is earning sal of '|| sal FROM Emp;

This query will give us the required result. So, like this wherever we have the requirement of combining two strings or values into single string or value we can use concatenation operator.

Arithmetic Operators ::


    Arithmetic Operators are used to perform arithmetic operations on numeric columns or numeric values. We have 4 arithmetic operators available.

   1. Addition Operator ( + )
   2. Subtraction Operator ( - )
   3. Multiplication Operator ( * )
   4. Division Operator ( / )

All these operators are used to perform arithmetic operations.

 Ex : SELECT 2345667 + 543276 FROM dual;
        SELECT sal + bonus FROM Emp;
        SELECT 2358976 / 4567 FROM dual;
        SELECT sal * 5 FROM Emp;

Precedence of Arithmetic Operators ::


   Precedence means, which arithmetic operator will be executed first, if we have all the 4 arithmetic operators in an expression as shown below.

   SELECT 12897654 + 234567 * 45321 / 567 - 2345 FROM dual;

  In this case first division operator (/) will be executed first . So, here 45321/567 will be executed first. after that multiplication operator will be executed. 234567 * (result of division) . Then either + or - will be executed. Like this the expression will be evaluated and result will be displayed.

 So, Order of precedence of the arithmetic operators evaluation is,

    /     *   +   -

 If we want to override this precedence then we have to enclose the operators in brackets. Then precedence will be override accordingly.

Ex : SELECT (1298677+23456) * 45678 / (567-234) FROM dual;

     Now even though we have all the 4 operators in this expression, we gave  + and - operators in brackets. So, first they will be executed. and after that / followed by * will be executed. Like this we can override the precedence.


  Comparison Operators ::


     Comparison Operators are used to perform comparison operations in SQL.  While writing SQL statements, comparison operations are very useful depending on the requirement. To, perform these comparison operations we will use these operators. We have many comparison operators in SQL.

    1 . Equal to operator ( = ) :: used to match single value
    2.  Not equal to Operator ( != or <> )
    3.  Less than Operator ( < )
    4. Greater than Operator ( > )
    5. Less than or equal to Operator ( <= )
    6. Greater than or equal to Operator ( >= )
    7. In operator
    8. Like Operator
    9. Between And Operator
    10. Is Null Operator

 All these Operators are used to perform Comparison operations in SQL. Operators from 1 to 6 are general comparison operators which we all know about what they are used for. So, I will just give examples for these operators.

While performing comparison operation with character data, we must remember that this operation is case sensitive. It meane 'RAMU' is not equal to 'ramu' as first is containing upper case letters and second one is containing lower case letters. So, we need to be careful while performing comparison operation with character data. We must use correct case for the letters. Similarly we must be careful while comparing date data . We must use default format of dates for comparison.

    EX : SELECT * FROM Emp WHERE ename = 'RAMU';

            The above query will give the row corresponding to employee with name 'RAMU' from Emp table.

            SELECT * FROM Emp WHERE ename <> 'RAMU';
            SELECT * FROM Emp WHERE hiredate != '13-JAN-2011';

            The above two queries are performing not equal to operation.,

            SELECT * FROM Emp WHERE sal < 15000;
            SELECT * FROM Emp WHERE sal > 1500;
            SELECT * FROM Emp WHERE sal <= 15000;
            SELECT * FROM Emp WHERE sal >= 15000;

    Now let us discuss about remaining comparison operators.

  In Operator ::

      In Operator is used for matching multiple values to single column. Using '=' operator we can match single value only. If you want to compare multiple values in single comparison then we can use IN operator.

  EX : If we want to find out all the employees from departments 10, 20 and 30 . Here we have to match deptno column with values 10,20 and 30 at a time. In this case we can use In operator.

    SELECT * FROM Emp WHERE deptno IN ( 10, 20, 30 );

 This is the syntax for using the IN operator. Like this using IN operator we can match multiple values in single comparison.

 IS NULL Operator ::


     This operator is used to check the presence of null values in a column. If we want to check whether null values are present in any column we can use this operator.

  EX : If we want to check if null values are present comm column of emp table in any of the rows we can use following statement.

   SELECT * FROM Emp WHERE comm IS NULL;

 So, this will give us all the rows of Emp table where comm column contains null values.

 Between - And Operator ::


      This operator is used to match range of values to a column in single comparison. Unlike In condition where we will match selective multiple values, we will match range of values using this operator.

 EX : If we want to find out all the employees from emp table who are earning sal in the range of 1500 to 2000. We cannot do this using In as we have to list 500 values in In condition. So, here Between And operator will be useful. We can write the select statement for this requirement as shown below.

      SELECT * FROM Emp WHERE sal BETWEEN 1500 AND 2000;

   This operation is inclusive of comparison values. It means employees with sal's 1500 and 2000 also will come in the result.
     
  Like Operator ::


    Like operator will be used to match single or multiple characters with character or numeric columns. If we want to match single character or multiple characters to get the result without matching entire words we can use Like operator . Using this Like Operator we can match entire words also as shown below.

     SELECT * FROM Emp WHERE ename LIKE 'RAMU';

  But main use of like operator comes with its ability to match partial values . This can be achieved using two special characters called as Wild cards. So, While discussing about Like operator in detail , we will discuss about Wild cards also.

  We will use two wild cards in SQL to work with the Like Operator. They are,

       %     and   _ (under score)

   % will be used for matching the partial characters. If, we place % after set of characters like 'ab%' then it means that we want all the strings starting with ab. If we place % before the set of characters like '%ab' then it means that we want all the string starting with ab. If we place % on both sides like '%ab%' then it will give us all the strings containing string 'ab' in any position. Like this we can use % for matching partial characters depending on the requirement.

Let me explain this with an example.

 EX : Let us say we want to find out all the employees from Emp table , whose names starts with letter 'R' then we can write the query as,,

       SELECT * FROM Emp WHERE ename LIKE 'R%';

  If we want to find out employees whose name ends with 'H' then we can write the query as,

      SELECT * FROM Emp WHERE ename LIKE '%H';

 If we want to find out employees whose name contains 'BA' in any position of their name we can write the query as,

      SELECT * FROM Emp WHERE ename LIKE '%BA%';

 Depending on the requirement we can use this % operator accordingly.

If we want to find out employees whose name starts with 'A' and ends with 'K' then we can write this query as,

     SELECT * FROM Emp WHERE ename LIKE 'A%K';

If we want to find out out employees whose names starts with 'S' and ends with 'R' and containing 'H' in any position of their names we can write the query as,

     SELECT * FROM Emp WHERE ename LIKE 'S%H%R';

So, position of % in comparison condition will decide  the position of searching characters in comparison strings. So, we must place % in appropriate position to get desired result.

 _ (under score) wild card will be used to skip a position in the string from the comparison. Let me explain this with examples. Generally this will be used in conjunction with % wild card.

 Ex : Let us say we want to find out all the employees whose names contain letter 'A' in second position. In this case we dont bother about the letter in the first place. So, we need to skip that from comparison. In this case _ will be very useful. We can write the query for this as shown below.

  SELECT * FROM Emp WHERE ename LIKE '_A%';

 So, this will give us all the employees whose names contain letter 'A' in second position. So, like this _ wild card will be used to skip certain positions of string from the comparison.

   So, we can use _ and % wild cards in conjunction along with Like operator depending on the requirements to get the desired results.

Logical Operators ::


     Logical operators are used for performing logical operations in SQL . These are very useful while writing the SQL statements with comparison operators. We have 3 logical operators available in SQL.

    1. AND operator
    2. OR operator
    3. NOT operator

 AND and OR logical operators are used to combine or evaluate multiple conditions together. we can combine two conditions using AND or OR operators. If we want to combine more, we have to use brackets.

If we have combined two conditions using AND operator, then if both the conditions are satisfied then only we will get the result.

EX :  SELECT * FROM Emp WHERE empno = 123 AND deptno=20;

      This statement will produce the result only if the table contains a row having empno as 123 and deptno as 20. Other wise this statement will not produce any result.

If we have combined two conditions using OR operator then we will get the result if any of the  two or both the conditions satisfied.

EX : SELECT * FROM Emp where empno=123 OR deptno=20;

This statement will give us all the employees from deptno = 20 along with employee with empno as 123.

NOT logical operator will be used to perform negation operation on the conditions mentioned. Let me explain this with examples. we can use this operator in conjunction with certain comparison operators to get the negative comparison results.

EX :  SELECT * FROM Emp WHERE deptno NOT IN (10,20);
           
          The above statement will give details of all the employees from emp table except from depts 10 and 20.
         SELECT * FROM Emp WHERE ename NOT LIKE 'A%';
 
          The above statement will give us all the employees from emp table except those employees  whose name starts with A.

         SELECT * FROM Emp WHERE comm IS NOT NULL;  

          The above statement will give us all the employees who are having comm as not null value.

Order of Precedence of all the operators ::


     If we have all the above mentioned operators in single statement then order of execution of these operators will be as shown below.

    ---> Arithmetic Operators
    ---> Concatenation Operator
    ---> 1 to 6 comparison operators
    ---> IS (NOT) NULL, LIKE , (NOT) IN operators
    ---> (NOT) Between operator
    ---> NOT Logical operator
    ---> AND Logical Operator
    ---> OR Logical Operator

We can override precedence of any of these operators by enclosing  them in brackets.

SQL Statements Part3 (DRL,TCL and DCL)....

In this post I want to discuss about DRL, TCL and DCL statements.

DRL ::

   DRL means Data Retrieval Language. This will be used for the retrieval of the data from the database.

In order to see the data present in the database, we will use DRL statement. We have only one DRL

statement.

             SELECT is the only DRL statement in SQL.

SELECT ::

   Select statement is used to see the data present in the database. Syntax for writing this statement is,

    SELECT col_list FROM tab_name;

 If we want to see the data present in all the columns of the table,then we can use following syntax,

    SELECT * FROM tab_name;

 Ex ::

 To check data present in all the columns of Emp table,

 SELECT * FROM Emp;

To check data present in specific columns of the table, we will use first syntax.

 Ex ::

  SELECT empno,ename FROM Emp;

In this I am just checking data present in empno and ename columns.

TCL ::

    TCL means Transaction Control Language. These statements are used to control the transactions made by the DML statements to the database. Any change which is made to the database by a DML statement will be called as a transaction. We need to control these transactions made by DML statements.

    Whatever change made to the database by a DML statement will not reside permanently in the database, unless auto commit option enabled in the session. Those changes will reside in temporary memory. So, we need to handle these changes.

    TCL statements will be used for this purpose. We have 3 TCL statements available.

                      1 COMMIT
                      2 ROLLBACK
                      3 SAVEPOINT

 1. COMMIT ::

     Commit will be used to make the changes made by DML statements permanent to the database. After performing DML operations , if we issue commit then those changes will be made permanent to the database. If Auto commit option for session is enabled, then this is not required as commit takes place automatically.

Note : DDL statements will carry implicit commit. So, if we issue DDL statement against the database, after doing some DML operations , then due to implicit commit of DDL, previous DML statements also will be commited as commit of DDL applies for the session till that point.

 COMMIT;

2. ROLLBACK ::


     Rollback will be used to discard the changes made by DML statements to the database. If auto commit option enabled in the session , then there is no use with Rollback statements as transactions will be commited automatically.

    So, If we give Rollback then any pending changes to the database which are made by DML statements will be discarded.

  ROLLBACK;

3. SAVEPOINT ::

     To Rollback the changes to a certain transaction point, we will use SAVEPOINT. I will discuss about this in future posts while discussing PL/SQL.

DCL ::


    DCL means Data Control Language. These statements are used for controlling the access to database objects for users. Generally these statements will be used by DBA people to control the access of users to the database.

   We have 2 DCL statements available in SQL.

         1. GRANT
         2. REVOKE

1. GRANT ::


      Grant will be used for giving permissions on the database to users. Using this we can give any type of permissions to the users on the database.

 For ex if we want to give Select permission on all the database tables to a user,

  GRANT SELECT ANY TABLE TO user_name;

If we want to give select permission on specific table then

  GRANT SELECT ON tab_name TO user_name;

If we want to give Create table permission then,

   GRANT CREATE TABLE TO user_name;

Like this we can give any privilege to the users on the database.

2. REVOKE ::

     Revoke will be used to remove the permissions which are granted previously to the users on the database.

   IF we want to remove permissions given in the above statements, then we can use following statements.

    REVOKE SELECT ANY TABLE FROM user_name;

    REVOKE SELECT ON tab_name FROM user_name;

    REVOKE CREATE TABLE FROM user_name.

Like this using DCL statements we can control the access of users to the database.


Difference Between DELETE and TRUNCATE ::


---> Delete is DML where as Truncate is DDL.
---> Delete requires transaction control as it is DML. Truncate does not require transaction control as it is
        DDL
---> If we remove data using DML, then we can get back it by giving Rollback (if commit not done). But
        we cannot get the data removed by Truncate (auto commit).
---> Using Delete we can remove specific rows (using where clause) or total rows also. But using Truncate
        we can remove entire rows only. (we cannot use where clause with truncate).
---> Truncate is faster than Delete.




   

SQL Statements Part2 (DDL) .....

In this post I want to discuss about DDL statements in SQL.

DDL ::

     DDL means Data Definition Language. These statements will work on structure of the tables . Structure of the table means, Name of the table, columns and their corresponding data types and constraints on the table ... etc. These statements will not effect the data present in the tables. Data definition involves, Creating the new tables, modifying the existing tables like changing names of tables or columns , adding new columns to the table, removing existing columns from the table, removing tables from the database ..... etc.

    DDL statements will carry implicit commit. So, these statements will not require any transaction control. Implicit commit means, whatever DDL statement issued against the database will be automatically committed. There is no rollback for the DDL statements. So, while working with the DDL statements we need to be very careful.

NOTE ::  One important point to remember here is, the implicit commit which is carried by DDL statements will be applicable to total transactions happened in that session till that point. It means that If we have any un handled (using TCL) DML statements issued against the database before issuing the DDL statements, they will also be committed to the database due to the implicit commit carried by DDL. So, we need to take care of this. If any pending DML transactions are there then first we have to handle them using commit or rollback before issuing any DDL operation in that session.

  We have 5 types of DDL statements available in SQL . They are,

                           

                              1. CREATE

                              2. ALTER

                              3. TRUNCATE

                              4. DROP

                              5. RENAME

1. CREATE :: 

            Create will be used to create new tables in the Database. We can also say that Create will be used to create new objects in the database. Objects means Table, Views, Indexes, Synonyms..... etc. We can create any of these objects using Create statement.  But in this post I will discuss only about tables. In upcoming posts I will discuss about remaining database objects.

          The syntax for creating new table using CREATE statement is,

            CREATE TABLE tab_name (
             col1 datatype,
             col2 datatype,
             ---------------
             ---------------
             coln datatype);

 So, using this syntax we can create new tables in the database. We can use any of the data types which we have discussed in the previous post to create columns of the database depending on type of data we want to store in those columns.

Example for Create statement is,

         CREATE TABLE Emp(
         empno number(10),
         ename varchar2(20),
         hiredate date,
         sal number(10,2),
         deptno number(10));

  This statement will create Emp table with columns empno (numeric type), ename (character type), hiredate (date type), sal (numeric) and deptno (numeric) .

This is how we can create new tables in the database.

If we want to see the structure of the tables without seeing the data present in the tables, we can use the describe command. Syntax for that is,

    DESC tab_name;

 Ex :   DESC Emp;

 This statement will show us the structure of Emp table.

ALTER ::


      ALTER command will be used to modify the structure of the existing tables. Using ALTER we can do many things. In this post I will discuss about basic things which we can do with ALTER. In coming posts I will discuss about remaining. Alter can be used to,

   1. Add new column to the table
   2. Remove existing column from the table
   3. Modify size of character columns of the table
   4. change the name of a column of the table
   5. change the name of the table itself.

 The syntax for adding new column to the table is,

 ALTER TABLE tab_name ADD col_name datatype;

 EX :  ALTER TABLE Emp ADD gender CHAR(1);

 This statement will add new column gender of character type to Emp table.

 The syntax for removing existing column from the table is,

 ALTER TABLE tab_name DROP COLUMN col_name;

 EX : ALTER TABLE Emp DROP COLUMN gender;

 This statement will remove gender column from the Emp table.

 The syntax for modifying size of character column from the table is,

 ALTER TABLE tab_name MODIFY col_name datatype with new size;

 EX : ALTER TABLE Emp MODIFY ename VARCHAR2(30);

 This statement will be used to change the size of ename column to 30.

 This statement can be used to change the size of any column to >= max(size) of the data already present in that column. For example if the max(size) of data already present in the column is 6 , then we can modify its size to >=6.

 The syntax for changing the name of the column is,

 ALTER TABLE tab_name RENAME COLUMN old_name TO new_name;

 EX : ALTER TABLE Emp RENAME COLUMN sal TO salary;

 This will change the name of sal column of Emp to salary.

 The syntax for changing name of a table using ALTER is,

 ALTER TABLE tab_name RENAME to new_name;

 EX : ALTER TABLE Emp RENAME to Employee;

 This statement will change the name of Emp table to Employee.

 So, these are the some basic things which we can do with ALTER statement.

TRUNCATE ::

      TRUNCATE will be used to remove the data from the tables. Truncate will be used to remove all the data present in the tables. We cannot remove specific rows of a table using TRUNCATE command. It means that we cannot write WHERE clause with this statement.

So, using truncate we can remove entire rows from the table only. The syntax for writing TRUNCATE is,

TRUNCATE TABLE tab_name;

EX : TRUNCATE TABLE Emp;

This will remove all the rows of Emp table.

 Even though TRUNCATE is similar to DELETE statement of DML, there are some advantages of TRUNCATE over DELETE which I will discuss after discussing TCL statements.

DROP ::

    DROP will be used to remove tables from the database. This will remove entire table from the database along with data present in it. Syntax for DROP statement is,

 DROP TABLE tab_name;

 EX : DROP TABLE Emp;

 This will remove Emp table from the database.

 RENAME ::


    RENAME will be used to change the names of database objects like tables, views, ..... etc. Using this command we can change names of database objects only. We cannot change names of columns of the table.

 Syntax for this statement is,

  RENAME old_name TO new_name;

EX : RENAME Emp TO Employee;

 This statement will change the name of Emp table to Employee.

 We can change name of table using ALTER also, but we cannot change names of remaining database objects using ALTER statement. Similarly we cannot change name of column of table using RENAME statement. We have to use ALTER only for that.

So, These are the DDL statements available in SQL.

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.