Arithmetic Calculations

This section covers the computational features of Texis. They are adequate to allow the user to perform computations on the data and/or retrieve rows based on conditions involving computations. For example, you can adjust salaries for a 5 percent across-the-board increase, or you can compute weekly salaries (i.e., salary divided by 52).

Arithmetic calculations are performed on fields, or columns, in the database. An arithmetic expression is used to describe the desired computation. The expression consists of column names and numeric constants connected by parentheses and arithmetic operators. Table here shows the arithmetic operators used in Texis.

Arithmetic Operation	Texis Operator	Example
Addition	`+`	`SALARY + 2000`
Subtraction	`-`	`SALARY - 1000`
Multiplication	`*`	`SALARY * 1.05`
Division	`/`	`SALARY / 26`

Table: Arithmetic Operators Supported in Texis

Typically, the arithmetic expression is used in the SELECT clause to perform calculations on data stored in the table.

Example: Next year every employee will receive a 5 percent salary increase. List the names of each employee, his or her current salary, and next year's salary.

Enter this statement:

SELECT  ENAME, SALARY, SALARY * 1.05
     FROM    EMPLOYEE ;

Where "SALARY * 1.05" is the arithmetic expression.

The results are:

ENAME               SALARY     SALARY * 1.05
  Aster, John A.      32000      33600
  Barrington, Kyle    45000      47250
  Chapman, Margaret   22000      23100
  Jackson, Herbert    30000      31500
  Price, Stella       42000      44100
  Sanchez, Carla      35000      36750
  Smith, Roberta      25000      26250

The expression "SALARY * 1.05" results in each value in the salary column being multiplied by 1.05. The results are then displayed in a new column that is labeled SALARY * 1.05.

If more than one arithmetic operator is used in an arithmetic expression, parentheses can be used to control the order in which the arithmetic calculations are performed. The operations enclosed in parentheses are computed before operations that are not enclosed in parentheses. For example, the expression:

12 * (SALARY + BONUS)

means bonus is added to salary, and then this result is multiplied by 12.

If parentheses are omitted or if several operations are included within the parentheses, the order in which calculations are performed is as follows:

First, all multiplication, division and modulo operations are performed.
Then, all addition and subtraction operations are performed.

For example, in the expression:

SALARY + SALARY * .05

the value in the SALARY column is multiplied by .05, and then the salary value is added to this intermediate result.

When two or more computations in an expression are at the same level (e.g., multiplication and division), the operations are executed from left to right. For example, in the expression:

SALARY / 12 * 1.05

the salary value is first divided by 12, and then this result is multiplied by 1.05.

Arithmetic calculation can also be used in a WHERE clause to select rows based on a calculated condition. In addition, arithmetic expressions can be used in the HAVING and ORDER BY clauses, which will be discussed in later sections of this chapter.

Example: List the names of all employees earning a monthly salary above $3000.

This query:

SELECT  ENAME
     FROM    EMPLOYEE
     WHERE   (SALARY/12) > 3000 ;

results in:

ENAME
  Barrington, Kyle
  Price, Stella

The rows in the EMPLOYEE table are retrieved if the condition "salary divided by 12" is greater than $3000. This was true only for Barrington and for Price, whose annual salaries (respectively $45,000 and $42,000) are greater than $3000 when divided by 12 months.