SQL Bible [Electronic resources] نسخه متنی

Arithmetic Operators

These operators, just as the name implies,
are used for arithmetic computations. The use of the arithmetic operators is
very intuitive (assuming that one did not flunk elementary school), and they
can be used in virtually every clause of the SQL statement. The full list of
arithmetic operators is given in
Table
11-1.

While doing arithmetic in SQL is relatively
easy, one must pay attention to the data type used in the operations; for
numeric values that would mean the precision and scale of the result; for
datetime, the range of the resulting values and so on.

Some databases (like Oracle) would perform
implicit conversion (whenever possible) if data types are not compatible with
operator (e.g., string value used with addition operator); the others (DB2 UDB
and SQL Server) would require explicit conversion into a compatible data type
to perform an operation.

Here are several examples of arithmetic
operator usage. To add two values in Oracle, the following query could be
used:

SELECT 5 + 5 total_value FROM
dual; total_value ----------- 10

The resulting
TOTAL_VALUE is of a numeric data type; if,
instead of the addition operator, the concatenation operator is used, the
result would be quite different:

SELECT 5 || 5 total_value FROM
dual; total_value ----------- 55

Here, Oracle implicitly converted numbers
into characters and the
TOTAL_VALUE is a result of this
concatenation of the character data type. DB2 UDB also recognizes the
concatenation operator, though it does not perform implicit conversion; this
example executed in UDB would generate an error, requiring explicit data type
conversion of the numbers into strings.

MS SQL Server works differently — it
overloads the addition operator, for example, the addition operator is also
used for concatenating strings. The decision to add operands or concatenate
them is made based upon the operand's data types: SQL Server will add numbers
and concatenate strings. The following examples demonstrate this functionality.
This query has two integers as operands, and SQL Server calculates the sum of
these (also an integer):

SELECT 5 + 5 total_value
total_value ----------- 10

The following query uses two characters
(signified by single quotes for literal values), and the result is
concatenation (character data):

SELECT '5' + '5' total_value
total_value ----------- 55

This operator also is used in date
arithmetic. While numerous functions could be employed to add and subtract
dates, the same functionality can be achieved with arithmetic operators. Here
is an example of adding 10 days to a date in MS SQL Server 2000 (date is given
as a literal and is converted to a datetime data type to ensure proper
handling):

SELECT CAST('09/10/2003 12:00 AM'
AS DATETIME) + 10 AS result_date result_date -------------------------
2003-09-20 00:00:00.000

The date arithmetic could be very
confusing. Oracle allows for extensive use of arithmetic for date manipulation,
as does IBM DB2 UDB — whereas Microsoft SQL Server clearly steers users to use
date- and time-related functions.

The following are examples of date
arithmetic in Oracle. The first query adds a specified number of days to the
specified date; the date might come from the table or be requested from the
RDBMS:

SELECT SYSDATE, (SYSDATE) + 10
result_date FROM dual; SYSDATE result_date ---------- ------------ 9/17/2003
9/27/2003

For instance, to add two hours, the
following operation could be used:

SELECT SYSDATE, (SYSDATE) + 2/24
result_date FROM dual; SYSDATE result_date ---------------------
--------------------- 9/22/2003 11:04:05 AM 9/22/2003 1:04:05
PM

By adding 24/24 (evaluating to 1), you are
essentially adding 1 day; henceforth 2/24 constitute 2 hours.

The same goes for the minutes and
seconds:

SELECT SYSDATE , (SYSDATE) +
1/(24*60) result_date FROM dual; SYSDATE result_date ---------------------
--------------------- 9/22/2003 11:08:26 AM 9/22/2003 11:09:26
AM

Of course, the same manipulations would
apply to other operators — as long as the operands are of compatible data
types. You cannot multiply or divide dates, for example; only addition and
subtraction is allowed. For the regular numeric data types, any arithmetic
operator is valid.

For example, if you would like to calculate
amount of sales tax (say, 8.5%) imposed on each of your transactions as
recorded in the ACME database, view
V_CUSTOMER_TOTALS:

SELECT order_number, total_price,
total_price * 0.085 tax FROM v_customer_totals order_number total_price tax
---------------- ----------- --------- 523720 7511.00 638.43500 523721 8390.00
713.15000 523722 6608.00 561.68000

The modulo operator (%) calculates the integer remainder of a division. This
is MS SQL Server-specific operator, as both Oracle and IBM use the
MOD function instead. The following query
calculates modulo of the integer 5 divided by 3 in MS SQL Server:

SELECT 5%3 remainder remainder
--------- 2

Which is absolutely identical to the Oracle
and IBM DB2 UDB function
MOD (where SYSDUMMY1 table is Oracle's
equivalent of
DUAL):

SELECT MOD(5,3) remainder FROM
sysibm.sysdummy1 remainder --------- 2