MS Excel: ABS function

DESCRIPTION

The Microsoft Excel ABS function returns the absolute value of a number

SYNTAX

The syntax for the ABS function in Microsoft Excel is:

ABS( number )

PARAMETERS or ARGUMENTS

number - A numeric value used to calculate the absolute value.

EXAMPLES

Let's look at some Excel ABS function examples and explore how to use the ABS function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following ABS examples would return:

=ABS(A1)

 Result: 120

=ABS(A2)

 Result: 3.5

=ABS(A3)

 Result: 45

=ABS(-6.9)

 Result: 6.9

=ABS(5-15)

 Result: 10

MS Excel: IF function

DESCRIPTION

The Microsoft Excel IF function returns one value if a condition is true and another value if is not.

SYNTAX

The syntax for the IF function in Microsoft Excel is:

IF(logical_test, value_if_true, [value_if_false])

PARAMETERS or ARGUMENTS

logical_test     - The condition you want to test. You can use other logical functions within this argument, including AND, OR and XOR functions.

value_if_true - The value that you want to be returned if the result of logical_test is TRUE.

value_if_false - The value that you want to be returned if the result of logical_test is FALSE. This parameter is optional.

NOTE

You can put another IF function in IF function in the place of some of the arguments.

=IF(E2>=85,"A",IF(E2>=75,"B","C"))

EXAMPLES

Let's look at some Excel IF function examples and explore how to use it

The following IF examples would return:

=IF(A2>B2,"Over Budget","OK")

  Result: Over Budget

=IF(A4=500,B4-A4,"")

  Result: 425

=IF(A3>200, "Larger", "Smaller")

  Result: Larger

=IF(A2=1500, "Equal", "Not Equal")

  Result: Equal

Oracle DB: REPLACE Function

DESCRIPTION

The Oracle REPLACE function replaces a sequence of characters in a string with another set of characters.

SYNTAX

REPLACE( string1, string_to_replace, replacement_string )

PARAMETERS or ARGUMENTS

string1 - The string to replace a sequence of characters with another set of characters.

string_to_replace - The string that will be searched for in string1.

replacement_string – This parameter is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the REPLACE function simply removes all occurrences of string_to_replace, and returns the resulting string.

EXAMPLES

REPLACE('123123test', '12');    
     Result: '33test'

REPLACE('123work123', '123');   
     Result: 'work'

REPLACE('222oracle', '2', '3'); 
     Result: '333oracle'

REPLACE('0000102300', '0');     
     Result: '123'

REPLACE('0000888', '0', ' ');   
     Result: '    888'

Oracle DB: NVL Function

DESCRIPTION

NVL replaces NULL value with other value in the results of a query.

SYNTAX

NVL(expr1, expr2)

PARAMETERS or ARGUMENTS

If expr1 is NULL, then NVL returns expr2. If expr1 is not NULL, then NVL returns expr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.

The implicit conversion is implemented as follows:

-          If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

-          If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

EXAMPLES

SQL> SELECT NVL(supplier_city, 'n/a')

       FROM suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a NULLvalue. Otherwise, it would return the supplier_city value.

MS Excel: SUM function

DESCRIPTION

The Microsoft Excel SUM function adds all numbers in a range of cells and returns the result.

SYNTAX
The syntax for the SUM function in Microsoft Excel is:
SUM( number1, [number2, ... number_n] )

OR

SUM ( cell1:cell2, [cell3:cell4], ... )

PARAMETERS or ARGUMENTS

number    -  A numeric value that you wish to sum.

cell           -  The range of cells that you wish to sum.

NOTE

You can sum combinations of both numbers and ranges of cells using the SUM function.

EXAMPLES

Let's look at some Excel SUM function examples and explore how to use the SUM function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following SUM examples would return:

=SUM(A2, A3)            
  Result: 17.7
=SUM(A3, A5, 45)        
  Result: 57.6
=SUM(A2:A6)             
  Result: 231.2
=SUM(A2:A3, A5:A6)      
  Result: 31.2
=SUM(A2:A3, A5:A6, 500) 
  Result: 531.2

Execute a SQL script files in sqlplus

Question: How do I execute a SQL script file in SQLPlus?

Answer: To execute a script file in SQLPlus, type @ and then the file name.

SQL> @{yourfile}

For example, if your file was called yourscript.sql, you'd type the following command at the SQL prompt:

SQL> @yourscript.sql

The above command assumes that the file is in the current directory, i.e. the current directory is usually the directory that you were located in before you launched SQLPlus.

If you need to execute a script file that is not in the current directory, you would type:

SQL> @{path}{yourfile}

For example:

SQL> @/oracle/scripts/yourscript.sql

This command would run a script file called yourscript.sql that was located in the /oracle/scripts directory.