### Arithmetic Expressions

You may need to modify the way in which data is displayed, or you may want

to perform calculations or look at what-if scenarios. These are all

possible using arithmetic expressions. An arithmetic expression can

contain column names, constant numeric values, and the arithmetic

operators.

**Arithmetic Operators**

The slide lists the arithmetic operators that are available in SQL. You

can use arithmetic operators in any clause of a SQL statement (except the

FROM clause).

Note: With the DATE and TIMESTAMP data types, you can use the addition and

subtraction operators only.

**Using Arithmetic Operators**

The example in the slide uses the addition operator to calculate a salary

increase of $300 for all employees. The slide also displays a SALARY+300

column in the output.

Note that the resultant calculated column SALARY+300 is not a new column

in the EMPLOYEES table; it is for display only. By default, the name of a

new column comes from the calculation that generated itin this case,

salary+300.

**Note:** The Oracle server ignores blank spaces before and after the

arithmetic operator.

Operator Precedence

If an arithmetic expression contains more than one operator,

multiplication and division are evaluated first. If operators in an

expression are of the same priority, then evaluation is done from left to

right.

You can use parentheses to force the expression that is enclosed by

parentheses to be evaluated first.

**Rules of Precedence:**

Multiplication and division occur before addition and subtraction.

Operators of the same priority are evaluated from left to right.

Parentheses are used to override the default precedence or to clarify

the statement.

**Operator Precedence (continued)**

The first example in the slide displays the last name, salary, and annual

compensation of employees. It calculates the annual compensation by

multiplying the monthly salary by 12, plus a one-time bonus of $100. Note

that multiplication is performed before addition.

Note: Use parentheses to reinforce the standard order of precedence and to

improve clarity. For example, the expression in the slide can be written

as (12*salary)+100 with no change in the result.

**Using Parentheses**

You can override the rules of precedence by using parentheses to specify

the desired order in which operators are to be executed.

The second example in the slide displays the last name, salary, and annual

compensation of employees. It calculates the annual compensation as

follows: adding a monthly bonus of $100 to the monthly salary, and then

multiplying that subtotal by 12. Because of the parentheses, addition

takes priority over multiplication.

**Null Values**

If a row lacks a data value for a particular column, that value is said to

be null or to contain a null.

A null is a value that is unavailable, unassigned, unknown, or

inapplicable. A null is not the same as a zero or a space. Zero is a

number, and a space is a character.

Columns of any data type can contain nulls. However, some constraints (NOT

NULL and PRIMARY KEY) prevent nulls from being used in the column.

In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a

sales manager or sales representative can earn a commission. Other

employees are not entitled to earn commissions. A null represents that

fact.

**Null Values in Arithmetic Expressions**

If any column value in an arithmetic expression is null, the result is

null. For example, if you attempt to perform division by zero, you get an

error. However, if you divide a number by null, the result is a null or

unknown.

In the example in the slide, employee King does not get any commission.

Because the

COMMISSION_PCT column in the arithmetic expression is null, the result is

null. For more information, see Basic Elements of SQL in SQL Reference.

**Column Aliases**

When displaying the result of a query, SQL Developer normally uses the

name of the selected column as the column heading. This heading may not be

descriptive and, therefore, maybe difficult to understand. You can change

a column heading by using a column alias.

Specify the alias after the column in the SELECT list using a space as a

separator. By default, alias headings appear in uppercase. If the alias

contains spaces or special characters (such as # or $), or if it is

case-sensitive, enclose the alias in double quotation marks (" ").

**Column Aliases (continued)**

The first example displays the names and the commission percentages of all

the employees. Notice that the optional AS keyword has been used before

the column alias name. The result of the query is the same whether the AS

keyword is used or not. Also notice that the SQL statement has the column

aliases, name and comm, in lowercase, whereas the result of the query

displays the column headings in uppercase. As mentioned in a previous

slide, column headings appear in uppercase by default.

The second example displays the last names and annual salaries of all the

employees. Because Annual Salary contains a space, it has been enclosed in

double quotation marks. Notice that the column heading in the output is

exactly the same as the column alias.

**Concatenation Operator**

You can link columns to other columns, arithmetic expressions, or constant

values to create a character expression by using the concatenation

operator (||). Columns on either side of the operator are combined to make

a single output column.

In the example, LAST_NAME and JOB_ID are concatenated, and they are given

the alias Employees. Notice that the employee last name and job code are

combined to make a single output column.

The AS keyword before the alias name makes the SELECT clause easier to read.

Null Values with the Concatenation Operator

If you concatenate a null value with a character string, the result is a

character string. LAST_NAME

|| NULL results in LAST_NAME.

Next part start with - **Literal Character Strings**

### To be continued...

Class-5 Finished. To Be Continued...

**Main Author:**

- Salome Clement
- Chaitanya Koratamaddi
- Nancy Greenberg.

*copyright issue: i get a copy as an Oracle certified professional (OCP)
and can use for training course purpose.*

### Click the images to go to the previous classes

### Support @good-karma:

Witness

or you can directly go to the witness vote

page Type the username and click vote.