Showing posts with label Single-Row Functions. Show all posts
Showing posts with label Single-Row Functions. Show all posts

Thursday 13 June 2013

Conditional Expression in SQL

  • Provide the use of IF-THEN-ELSE logic within a SQL statement.
  • Use two methods:
    • CASE expression.
    • DECODE function.

The CASE Expression

  • Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement.
Syntax:

CASE expr WHEN comparison_expr1 THEN return_expr1
                    [ WHEN comparison _expr2 THEN return_expr2
                     WHEN comparison_exprn THEN return_exprn
                     ELSE else_expr ]
END


  • In a simple CASE expression, Oracle searches for the first WHEN --- THEN pair for which expr is equal to comparison_expr and returns return_expr. 
  • If none of the WHEN --- THEN pairs meet this condition, and ELSE clause exists, then Oracle returns else_expr.
  • Otherwise, Oracle returns null.
  • You can't specify the literal NULL for all the return_exprs and the else_expr.
Example:



The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:

Syntax:

DECODE(col|expression, search1, result1
                                        [,search2,result2,...]
                                        [,default])

NOTE:
  • The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. 
  • The DECODE function decodes expression after comparing it to each search value.
  • If the expression is the same as search, result is returned.
  • If the default value is omitted, a null value is returned where a search value does not match any of the result value.
Example:



Thursday 30 May 2013

General Functions in SQL

These functions work with any data type and pertain to using nulls.

  • NVL (expr1,expr2): Converts a null value to an actual value
  • NVL2 (expr1,expr2,expr3): If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.
  • NULLIF (expr1,expr2): Compares two expressions and returns null if they are equal, or the first expression if they are not equal.
  • COALESCE ( expr1,expr2,....., expr n): Returns the first non-null expression in the expression list.

Using NVL Function

  • Data types that can be used are date, character, and number.
  • Data types must match:
    • NVL ( comm,0 )
    • NVL (hiredate,'01-JAN-13')
    • NVL(job,'No Job Yet')
Example:


Using the NVL2 function

  • The NVL2 function examines the first expression. 
  • If the first expression is not null, then the NVL2 function returns the second expression. 
  • If the first expression is null, then the third expression is returned.

Syntax:
NVL2(expr1,expr2,expr3)

In the syntax:

expr1 : is the source value or expression that may contain null.
expr2: is the value returned if exp1 is null.
expr3: is the value returned if expr2 is null.

Example:


Using the NULLIF Function

  • The NULLIF function compares two expressions. 
  • If they are equal, the function returns null. 
  • If they are not equal the function returns the first expression. 
  • You cannot specify the literal NULL for first expression.
Syntax: 
 NULLIF(expr1,expr2)

In the syntax:

expr1  is the source value compared to expr2.
expr2  is the source value compared with expr1. (if it is not equal to expr1, expr1 is returned.)

Example:


In the above example, no employee having salary=their annual commision. 
So everytime in the result tab, first value is returned. 
If in the case any employee having sal= his annual commission (12*comm) then we should have noticed null in RESULT tab.

Using the COALESCE Function

  • The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
  • If the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
  • This function finds the first non null expression and returns. Thats it
Syntax:
              COALESCE(expr1,expr2,......exprn)

In the Syntax:

expr1  returns this expression if it is not null.
expr2  returns this expression if the first expression is null and this expression is not null.
exprn  returns this expression if the preceding expression are null.

Example:



Conditional Expression  covers in Next post.
  • CASE expression
  • DECODE function



 


Conversion Function in SQL



In some cases, Oracle server uses data of one data type where it expects data of a different data type. When this happens, Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by Oracle server, or explicitly by the user.

Implicit Data Type Conversion

For assignments, the Oracle server can automatically convert the following:

                    From                                           To

        VARCHAR2 OR CHAR                   NUMBER
        VARCHAR2 OR CHAR                   DATE
        NUMBER                                         VARCHAR2
        DATE                                                VARCHAR2

The assignment succeed if the Oracle server can convert the data type of the value used in the assignment to that of the assignment target.

Explicit data type conversion covers in Next post.

Explicit Data Type Conversion

SQL Provides 3 functions to convert a value from one data type to another:

TO_CHAR (number | date, [, fmt] ) 

This function converts a number or date value to a VARCHAR2 character string with format model fmt.

TO_NUMBER (char, [fmt] )

This function converts a character string containing digits to a number in the format specified by the optional format model fmt.

TO_DATE (char, [fmt] )

This function converts a character string representing a date to a date according to the fmt specified. If fmt is omitted, the format is DD-MON-YY.

Using the TO_CHAR Function with Dates


TO_CHAR(date,'format_model')

The format model:
  • Must be enclosed in single quotation marks and is case sensitive.
  • Can include any valid date format element.
Example: 


TO_CHAR ( number,'format_model')

Example: here you can notice the both sal and salary printed with formatted result.



Note:
TO_NUMBER()
and TO_DATE()  

I'll upload the example if any one ask for me. In the comment section ask for me, and I'll upload it. 

Date Function - Working with Dates

SYSDATE is a function that returns the current database server date and time.

Example:

SELECT SYSDATE                                  * DUAL is a dummy table.
FROM DUL;

Result
----------------
30-MAY-13

Arithmetic with Dates

  • Add or subtract a number to or from a date for a resultant date values.
  • Subtract two dates to find the number of days between those dates.
  • Add hours to a date by dividing the number of hours by 24.
Example:

The example on the slide displays the last name and the number of weeks worked by the employee department number 10.

Date Functions

  • MONTHS_BETWEEN(date1,date2): Finds the number of months between date1 and date2.
  • ADD_MONTHS(date,n): Adds n number of calender months to date.
  • NEXT_DAY(date,'char'): Finds the date of the next specified day of the week.
  • LAST_DAY(date): Finds the date of the last day of the month that contains date.
  • ROUND(date [, 'fmt']): Returns date rounded to the unit specified by the format model fmt.
  • TRUNC(date [, 'fmt']): Returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Using Date Functions

MONTHS_BETWEEN



 ADD_MONTHS

NEXT_DAY


LAST_DAY


ROUND


TRUNC


Number Functions in SQL

  • ROUND : Rounds values to specified decimal.
                   ROUND(45.878,2)       ------------------->   45.88
  • TRUNC  :  Truncates value to specified decimal.
                   TRUNC(45.878,2)       -------------------->   45.87
  • MOD : Returns remainder of division.
                   MOD(1600,300)          -------------------->  100

Example:


ROUND
The Round Function rounds the column, expression, or value to n decimal place.
If the second argument is 0 or is missing, the value is round to zero decimal place.
If the second argument is 2, the value is rounded to two decimal places.
If the second argument is -2, the value is rounded to two decimal places to the left.


TRUNC
The TRUNC function truncates the column, expression, or value to n decimal places.
If the second argument is 0 or is missing, the value is truncated to zero decimal places.
If the second argument is 2, the value is truncated to two decimal places.
If the second argument is -2, the value is truncated to two decimal places to the left.






Character Functions in SQL

  • Case-manipulation functions
    • LOWER   :- Converts alpha character values to lowercase.
    • UPPER   :-  Converts alpha character values to uppercase.
    • INITCAP  :-  Converts alpha character values to uppercase for the first letter of               each word.
  • Character-manipulation functions
    • CONCAT  :-  Concatenates the first character values to second character value.
    • SUBSTR  :-  Return specified characters from character value.
    • LENGTH  :-  Returns the number of character in the expression.
    • INSTR  :-  Returns the numeric position of a named string.
    • LPAD  :-  Pads the character value right justified to a total width of n.
    • RPAD  :- Pads the character value left justified to a total width of n.
    • TRIM  :-  Enables you to trim heading or trailing characters from a character string.
    • REPLACE  :-  Searches a text expression for a character string and if found, replaces it with a specified replacement string.
Example:

LOWER
UPPER & INITCAP


CONCAT

SUBSTR


LENGTH


INSTR


LPAD & RPAD


TRIM




Unit 3 :- Single-Row Functions

Functions are very powerful feature of SQL and can be used to do the following:

  • Perform calculations on data.
  • Modify individual data items.
  • Manipulate output for groups of rows.
  • Format dates and numbers for display.
  • Convert column data types.
SQL functions sometimes take arguments and always return a value.

There are Two distinct types of functions:
  • Single-row functions.
  • Multiple-row functions.

Single-Row Functions

These functions operate on single rows only and return one result per row. There are different types single-row functions. This section covers the following ones:
  • Character
    • Case-manipulation functions
      • LOWER
      • UPPER
      • INITCAP
    • Character-manipulation functions
      • CONCAT
      • SUBSTR
      • LENGTH
      • INSTR
      • LPAD   |   RPAD
      • TRIM
      • REPLACE
  • Number
    • ROUND
    • TRUNC
    • MOD
  • Date
    • MONTHS_BETWEEN
    • ADD_MONTHS
    • NEXT_DAY
    • LAST_DAY
    • ROUND
    • TRUNC
  • Conversion
    • Implicit data type conversion
      • VARCHAR2 OR CHAR -----   TO -----   NUMBER
      • VARCHAR2 OR CHAR -----   TO -----   DATE
      • NUMBER                       -----    TO -----  VARCHAR2
      • DATE                              -----    TO ----    VARCHAR2
    • Explicit data type conversion
      • TO_CHAR
      • TO_DATE
      • TO_NUMBER
  • General
    • NVL
    • NVL2
    • NULLIF
    • COALESCE
    • CASE
    • DECODE

Multiple-Row Functions

Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions. This is covered in next Unit.