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.

SQL statements Part 1 (DML)...

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

DML ::


       DML means Data Manipulation Language. These statements will be used to modify the data present in the tables. These statements will work on the data, there is no relation for these statements with the structure of the tables. Data manipulation involves Inserting data into tables, modifying the data present in the tables and removing the data from the tables. DML statements require Transaction control.

     Any change which is made to the database by a DML statement will be called as a transaction. So, any change made by DML statement needs to be controlled by TCL statements (Transaction Control). I will discuss about these TCL statements in my future posts. In order to handle the changes done to the database by DML statements , we need transaction control.

    We have 4 types of DML statements available in the SQL. They are,

                  1. Insert
                  2. Delete
                  3. Update
                  4. Merge

1. Insert ::


        Insert will be used to insert new data into the tables. In other words we can say, Insert will be used to create new rows in the tables. We cannot effect the existing rows from the table using Insert statement.

   We have two different syntax's for writing the Insert statement. They are,
         1. INSERT INTO tab_name values ( val1, val2, ...... valn);
         2. INSERT INTO tab_name (col1, col2, col3, ....., coln) VALUES (val1, val2, ..... , valn);

 First syntax is the general syntax of insert statement. In this type of syntax we must pass values to all the columns of the table, and that also in the order of presence of these columns in the table. Otherwise, we will get error. (In this post I don't want to discuss about duplicate rows, not null values... etc. This I will discuss while discussing the constraints of the table).

   For Example, Let us say we have a table called EMP in the database. The columns present in this table are Empno, Ename, Sal and Deptno. Now If I want to insert new row into this table using first syntax then,

   INSERT INTO Emp VALUES ( 1111, 'RAM', 5000, 20);

Now, I will discuss about the 2nd syntax. In this second syntax we can enter values into the columns of the table while creating new rows in our own desired order irrespective of the order of presence of these columns in the table. This we can achieve by mentioning the our desired order between tab_name and VALUES as shown in the syntax. In this syntax we can also give values to specific columns of the table only. This means, If  we have 4 columns in the table and while creating the new row If you want to give values only to 3 columns we can do that by using second syntax.

 Ex : INSERT INTO Emp ( Empno, Ename, Sal) Values (2222, 'RAJ', 2000);

        INSERT INTO Emp (Empno, Sal, Ename) Values (3333, 1500, 'KRISH');

 We have one more syntax for creating new rows in the table using Insert statement.  That is shown below.

      INSERT INTO Emp VALUES ( &val1, &val2, ..... &valn).

     In this syntax, if we run the above statement then system will ask us to enter the values one by one. after entering all the values row will be created in the table. after that if we hit '/' at the command prompt then system will ask us to enter the values again to create another row. Like this we can create n number of rows.

 In this way we can create new rows in the table using different syntax's.

2. Delete ::

           Delete will be used to remove the data from the tables. Using Delete we can remove single row or multiple rows from the table. We have two syntax's for Delete statement.

    1. DELETE FROM tab_name;
    2. DELETE FROM tab_name WHERE condition ;

If we use first syntax then all the rows from the table will be removed. If we want to remove specific rows only then we have to use the second syntax.

  Ex :  DELETE FROM Emp;

     If we execute this statement then all the rows from the EMP table will be removed.

           DELETE FROM Emp WHERE empno = 1111;

    If we execute this statement then the row belongs to employee with Empno 1111 will be removed. So, using this syntax we can remove specific rows from the table by specifying the Where clause.

    Using Delete we can remove entire row from the table only. we cannot remove values from specific columns of the table using Delete statement.

3. Update ::


           Update will be used to modify the data present in the tables. using single update statement we can modify the data present in the single column or multiple columns of the table. Similarly, using single update we can modify data present in single row or multiple rows.

    Different syntax's for Update statement are,

     1. UPDATE tab_name SET col_name = val ;
     2. UPDATE tab_name SET col_name = val WHERE condition;
     3. UPDATE tab_name SET col1 = val1, col2 = val2 , .... ;
     4. UPDATE tab_name SET col1 = val1, col2 = val2,... WHERE condition;

  If we use the first syntax, then in all the rows of the table the column specified (col_name) will be updated to the provided value (val).
  If we use second syntax, then for the rows which will satisfy the condition specified in the WHERE clause column specified (col_name) will be updated to the provided value(val).
 Third and Fourth syntax's are similar to first and second syntax's only , only difference here is instead of updating single column we are updating multiple columns of the table.

 EX : UPDATE Emp SET deptno = 20; (This will update deptno to 20 in all the rows of Emp table).
         UPDATE Emp SET sal = 5500 WHERE ename = 'RAM'; (This will update sal of Ram to 5500).
         UPDATE Emp SET sal = 6000, deptno = 30 ;
         UPDATE Emp SET sal = 7000, deptno = 40 WHERE ename = 'RAM';

 As I discussed before, If we want to insert value to a particular column of existing row we cannot use Insert statement.We have to use Update statement to update the column to that particular value. Similarly If we want to remove values from specific column then we cannot do it using Delete statement. We have to update that column or columns to null.

    Ex : UPDATE Emp SET deptno = null WHERE empno = 3333;

4. Merge ::


           Merge statement will be used to combine data from two similar tables into single table. In order to do merge operation two tables containing similar columns  are required. Generally we will use merge statement to insert data from one table into other similar table. While doing this If same row is present in both the tables then we cannot perform insert operation due to the constraints of the table. This is where the merge operation is very useful.

         Merge is the combination of both Insert and Update operations. So, while merging data from one table into another table, If we have same row in both the tables then we will perform UPDATE operation. If different row is present, then we will perform INSERT operation. This is the concept involved with MERGE statement.

   Syntax for writing the merge statement is,

         MERGE INTO tab1
         USING tab2
         ON condition
         WHEN MATCHED THEN
         UPDATE SET tab1. col  = tab2. col
         WHEN NOT MATCHED THEN
         INSERT VALUES ( tab2. col1, tab2.col2, ........ );

   Let me explain this merge statement with an example. Let us say we have two similar tables Emp and Emp1 . I want to merge data from Emp1 to Emp table. Then statement used for this is,

      MERGE INTO Emp
      USING Emp1
      ON emp.empno = emp1.empno
      WHEN MATCHED THEN
      UPDATE SET emp.sal = emp1.sal
      WHEN NOT MATCHED THEN
      INSERT VALUES ( emp1.empno, emp1.ename, emp1.sal, emp1.deptno);

 So, here I am merging data from Emp1 table into Emp table. ON will be used to check the common thing between the two tables to decide when to do update and when to do Insert. Here using Empno column I am deciding this. Generally we will use primary key columns where unique values will  be present to compare in ON condition.

    Now if the condition is satisfied then, WHEN MATCHED condition will be satisfied and the UPDATE statement will be performed. Here we are taking sal from emp1 table and updating it to emp table. If condition is not satisfied then WHEN NOT MATCHED condition will be satisfied and new row will be inserted into Emp table from Emp1 table.

    So, This is how Merge statement will be worked.  So, in case of Merge operation if similar rows are there we will perform Update operation and if different row is there then we will perform Insert operation.
     
  So, these are the different DML statements available in SQL.

Overview of SQL topics....

I am going to discuss lot of topics in SQL in my  future posts.... In this post I want to give overview of those topics....

1. SQL Statements :: 
           SQL statements are the base for SQL . In this topic I will discuss about different types of SQL statements available in SQL and their usage. To give some idea, we have 5 types of SQL statements.
                           1. DRL (Select)
                           2. DML ( Insert, Delete, Update, Merge)
                           3. DDL (Create, Alter, Truncate, Drop, Rename)
                           4. DCL (Grant, Revoke)

                           5. TCL (Commit, Rollback, Savepoint)

        These statements are very useful while working with the database. We will not write any statement in SQL without using any of these statements.

2. SQL Functions ::
            There are many types of SQL functions which are very useful while writing the SQL queries.
       Depending on number of results they are producing for each row / group of rows, functions are divided into two types.
       1. Single row functions      2. Multiple row functions
   These functions again depending on type of data they are effecting divided into,
       1. Numeric Functions like Round, Trunc ....
       2. Character Function like Substr, Instr ....
       3. Date functions like Months_between, last_day......
   We have some more functions like,
       Conversion Functions used for data conversions like to_date, to_char, ........
       General functions used for handling null values like NVL, NVL2 .....
       Conditional Functions like Case and Decode
       Group Functions like Sum, Avg, .....

3.  Where , Group by, Order by and Having clauses..

4.  Joins which will be used to get data from one or more tables
      We will discuss about different joins like,
       Inner Join, Outer Join, Self Join, Equi Join, Non Equi Join........
     This is one of the most important topics of SQL. Because in real life most of the times queries which we will write will contain joins only.....

5. Sub Queries :: This is also a most important topic of SQL. We have two types of Sub queries.
             1. Correlated Sub queries    2. Non Correlated Sub queries

6. Set Operators :: 
            Set operators will be used to combine the results from 2 queries into single result.
      Different Set Operators in SQL are,

          Union, Union All, Minus, Intersect

7.  Views :: In this we will discuss about what are views , why we need views and how we can create them.


8.  Indexes :: These are very important in improving the performance of the queries. We have different indexes like,
               Function Based Indexes
               Bit map Indexes
               B-tree Indexes

9.  Synonyms :: used for aliasing


10. Sequences :: Used for generating the unique sequence of values.

11. ROWNUM and ROWID pseudo columns

12. Analytical Functions ::
                 This topic will come under advanced SQL. In this we will discuss about Rank and Dense_Rank functions.

13. Hierarchical Retrieval ::

              This is also advanced SQL topic. In this we will discuss about Connect By Prior clause.

14. Constraints ::

              In this we will discuss about Primary key, Foreign Key, Check constraint, Unique key and Not Null constraints.

15. Datatypes ::

     

            In this we will discuss about some useful datatypes in SQL. like Number, Date, ... etc.

        Apart from these topics I will also discuss about design models like ER Modeling,... etc. Also, I will try to post about different advanced topics in SQL.
           
  

Introduction to SQL.....

            Before learning the concepts of SQL and PL/SQL we need to know the importance of the ORACLE language and also about few introductory things. So, In this post I am going to give Introduction to SQL which will give  the better understanding of why to learn this language. Also I will provide some basic information about different topics related to SQL which I am going to discuss about in my upcoming posts.
           As I mentioned in my previous post we need to have a database in our system to practice the SQL or PL/SQL. So, for us database is the most important thing. So, we need to know about what is a Database and why we need it.

What is a Database :: ?
            The answer to this question is very simple. Database is the place where we can store our data. The Oracle database is designed in such a way that any data we want to store in it, we can store it in the form of 2 dimensional tables (In database terminology we will call them as Relations).
            A table will contain Rows (In database terminology we will call them as Tuples) and Columns (In database terminology we will call them as Attributes). I will explain this concept with small example.
            Basically tables will contain structure and data. The structure of the table includes Name of the table, Columns of the table(along with their names and data types along with constraints if any). The name of the table should be unique (duplicate names are not allowed). The name should be in such a way that by seeing the name itself we must be able to identify the type of information  stored in the tables.
 For EX: See the following table.

               EMP:    Empno

Empno	Ename	Deptno	Hire_date	Sal
1	Ram	10	10-jul-2011	25000
2	Raj	20	15-jul-2011	15000
3	Rao	30	20-jul-2011	10000

 

  Name of this table is EMP, means employee. So, by seeing this name itself we will know that Employee details were stored in this table.

     This table contain 5 columns. Generally each column of a table will specify one unique attribute of the data which we are going to store in the table. If you observe this table we have columns named as Empno (which will give us employee number of the employee), Ename (name of employee), Deptno (dept to which employee belongs), Hire_date( joining date of employee) and Sal(sal of employee). Like this each column is specifying the unique feature of a particular employee. Regarding data type we will discuss about it in coming posts. But to give some idea, The data type will specify the type of data which we are going to store in that particular column (Ex : Numeric, character.... etc).

      Now coming to the data part of the table, Rows will contain the data in the tables. Each row will contain some unique information of the type of the data which we are going to store. In our example each row will contain unique information of  an employee of the company. Generally we will maintain only one row for an employee in the table. (This can be maintained with the constraints about which we will discuss in coming posts)

      We can store any type of data in the database in form of tables . We can store pictures and files also.

So, By this we have got some idea about what is a database. Now next question is why we need database?

Why we need a Database :: ?

    Let me explain this with small example. Let us take the example of GMAIL, through which we can send mails, we can chat with friends.... by logging in with our credentials(Username and Password).

    Here in Gmail our Usernames should be unique. While registering it will ask to enter unique user name by checking the availability and each username will be associated with a password. So, where this information will be stored?? This information will be stored in the tables of a database server which is located at the company. So, each time when you are trying to login then it will validate the information with the data stored in the database and it will do appropriate action.

   Similarly in real life almost in all the places database becomes a mandatory thing as we can store data into it from any place and we can access that data from any place. Take Banking, Railways ticket reservations, Bus ticket reservations, Cinema ticket reservations and information, Hospitals..... any place you take... they need a database to maintain the information.

So, Now we understand the importance of the database in our real life. Now, here comes most importane question. What is SQL?

What is SQL :: ?

   

      SQL means Structured Query Language. The statements which we are going to write in this language will be called as Queries. Each query will be having its own structure. That is why this language is called as Structured Query Language. Ok fine. Now we know what is SQL. The next question is Why we need SQL?

Why we need SQL :: ?

     So as discussed in the above sections,we will store the information in the database in the form of tables. How will you store?? we cannot take it by hand and we cannot put it in the database right.. Similarly How will you see the information?? How will you modify the information?? To do all these things we need a mode of communication with the database. We need to interact with the database by some medium. Here comes the usage of a Database language by which we can perform all the above mentioned tasks on the database. One such type of Language is SQL.

     So, SQL is a communication language which will be used to interact with the database.

So, As part of SQL we are going to learn about how to perform all the above mentioned tasks. 

So, this is a small introduction to what we are going to discuss in my upcoming posts. If you have any queries you can post them through comments.