Databases Reference
In-Depth Information
function is designed to do. This syntax for evaluating expressions and functions
is not supported by all DBMS products.
SELF-CHECK
•
Explain the basic SELECT statement syntax.
•
Explain the importance of the WHERE clause when retrieving
SELECT results.
6.3 Understanding Operators and Functions
A key part of writing and understanding expressions is understanding opera-
tors. SQL operators include both arithmetic and logical operators. Logical oper-
ators are used in the WHERE clause to build search conditions that depend on
more than one qualifying condition. You can also categorize operators as the
following:
▲
Unary Operator:
an operator applied to only one operand at the time;
typically in the format operator operand. For example, NOT A.
▲
Binary Operator:
an operator applied to two operands at the time, typi-
cally in the format operand operator operand. For example, A OR B.
Because of variations between different DBMS products, specific examples in
this chapter are based primarily on Microsoft SQL Server.
6.3.1 Arithmetic Operators
Arithmetic operators are used for arithmetic computations. The use of the arith-
metic operators is very intuitive, and they can be used in virtually every clause
of a SQL statement. Because of its effect when used with numeric data, we've
also included a concatenation operator in our discussion. The full list of arith-
metic operators is given in Table 6-1.
While doing arithmetic in SQL is relatively easy, you must pay attention to
the data type used in the operations. For numeric values, this includes the pre-
cision and scale of the result; for datetime, the range of the resulting values; and
so on. Most DBMSs can convert similar data types automatically for evaluation
through a process known as
implicit conversion,
but dissimilar types require
explicit conversion
(manual conversion) to a compatible type.
To add two values, as you've already seen, you could:
SELECT 5
5 total_value
