Expressions are used in the SQL language to calculate values that are retrieved from a database and to calculate values used in searching the database. For example, this query calculates the sales of each office as a percentage of its target:
SELECT CITY, TARGET, SALES, (SALES/TARGET) * 100
FROM OFFICES
and this query lists the offices whose sales are more than $50,000 over target:
SELECT CITY
FROM OFFICES
WHERE SALES > TARGET + 50000.00
The ANSI/ISO SQL standard specifies four arithmetic operations that can be used expressions: addition (X + Y), subtraction (X – Y), multiplication (X * Y), and division / Y). Parentheses can also be used to form more complicated expressions, like this one:
(SALES * 1.05) – (TARGET * .95)
Strictly speaking, the parentheses are not required in this query because the ANSI/ISO standard specifies that multiplication and division have a higher precedence than addition and subtraction. However, you should always use parentheses to make your expressions unambiguous because different SQL dialects may use different rules. The parentheses also increase the readability of the statement and make programmatic SQL statements easier to maintain.
The ANSI/ISO standard also specifies automatic data type conversion from integers to decimal numbers, and from decimal numbers to floating point numbers, as required. You can thus mix these data types in a numeric expression. Many SQL implementations support other operators and allow operations on character and date data. DB2, for example, supports a string concatenation operator, written as two consecutive vertical bar characters (||). If two columns named FIRST_NAME and LAST_NAME contain the values “Jim” and “Jackson”, then this DB2 expression:
(‘Mr./Mrs. ‘ || FIRST_NAME || ‘ ‘ || LAST_NAME)
produces the string “Mr./Mrs. Jim Jackson”. As already mentioned, DB2 also supports addition and subtraction of DATE, TIME, and TIMESTAMP data, for occasions when those operations make sense. This capability has been included in the SQL2 standard.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.