Learning oracle programming language (Class-5)

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