Operations and Expressions

Part:   1   2  3 

This chapter describes:

• Expressions.
• Arithmetic operations.
• Predicate operations.
• Date and time operations.
• Operation examples.

All notes in chapter are based on SQL-92 and MySQL server.

Value Expressions

Value Expressions: Syntax forms to express operations against data values. SQL divides value expressions into several categories: numeric expressions, character string expressions, bit string expressions, date and time expressions, and time interval expressions.

1. Numeric value expressions are operations that involve numeric values. Examples:

1 + 1
2 / 3
100 * (1 + 0.05) * (1 + 0.06)

2. Character string expressions are operations that involve character strings. Examples:

'Hello' || ' world!' -- Concatenation, not supported by MySQL

3. Bit string expressions are operations that involve bit strings. Examples:

~ x'01' -- Negation

3. Date and time expressions are operations that involve data and time values, and time interval values. Examples:

date '1999-01-01' + interval '1' year; -- adding 1 year
timestamp '1999-01-01 01:02:03' + interval '1' minute -- adding 1 minute

Since expressions are made up by operations, let's look at some of the commonly used operations.

Arithmetic Operations

As any other computer languages, SQL defines 4 arithmetic operations:

• Addition: numeric_value + numeric_value
• Subtraction: numeric_value - numeric_value
• Multiplication: numeric_value * numeric_value
• Division: numeric_value / numeric_value
• Integer division: numeric_value DIV numeric_value
• Modulo: numeric_value MOD numeric_value

(Continued on next part...)

Part:   1   2  3