Column Expressions

Besides retrieving the values of individual columns, a SELECT statement allows you to specify expressions that can perform arithmetic operations on the columns in a table. The normal arithmetic operators (+, -, *, /) along with a wide range of scalar functions can be included in a SELECT column expression. The complete syntax for column expressions is given below.

select_stmt:
          SELECT value_expr [alias_name] [, value_expr [alias_name] ]… FROM table_name
value_expr:
          arith_expr | dt_expr | string_expr
arith_expr:                                              /* involving only numeric operands and operations */
          operand [{+ | - | * | /} operand]…
dt_expr:                                                 /* involving only date/time/timestamp operands and operations */
          operand [{+ | -} operand]…
string_expr:                                            /* involving only string operands and operations */
          operand [ || operand]…
numeric_function See Built-in Numeric Functions table below
datetime_function See Built-in Date and Time Functions table below
string_function See Built-in String Functions table below
system_function See Built-in System Functions table below
alias_name An identifier used to label the expression

The built-in numeric functions that are available in RaimaDB SQL are listed in the following table.

Built-in Numeric Functions Table
Function Description
abs Returns the absolute value of an expression.
acos Returns the arccosine of an expression.
asin Returns the arcsine of an expression.
atan Returns the arctangent of an expression.
atan2 Returns the arctangent of an x-y coordinate pair.
ceil | ceiling Finds the upper bound for an expression.
cos Returns the cosine of an angle.
cot Returns the cotangent of an angle.
degrees Convert radians to degrees
exp Returns the value of an exponential function.
floor Finds the lower bound for an expression.
ln | log Returns the natural logarithm of an expression.
mod Returns the remainder of arith_expr1/arith_expr2.
pi Returns the value of pi.
power Raises numeric value to specified power.
rand Returns next random floating-point number. Non-zero num is seed.
round Returns arith_expr rounded to the nearest integer.
sign Returns the sign of an expression (-1, 0, +1).
sin Returns the sine of an angle.
sqrt Returns the square root of an expression.
tan Returns the tangent of an angle.

The RaimaDB SQL data and time manipulation functions are listed below. Note that date_expr is an arith_expr that involves only date columns and values and time_expr is an arith_expr that involves only time, and timestamp columns and values.

Built-in Date and Time Functions Table
Function Description
age Returns the age (in full years).
curdate Returns the current date.
curtime Returns the current time.
curtimestamp Returns the current date and time
dayofmonth Returns the day of the month.
dayofweek Returns the day of the week.
dayofyear Returns the day of the year.
hour Returns the hour.
minute Returns the minute.
month Returns the month.
quarter Returns the quarter.
second Returns the second.
trunctime Truncates a time or timestamp value based on a specified interval.
week Returns the week.
year Returns the year.

The RaimaDB SQL string manipulation functions are listed below.

Built-in String Functions Table
Function Description
ascii Returns the numeric ASCII value of a character
char Returns the ASCII character with numeric value num
character_length Return the number of characters in a string
concat Concatenates two strings
convert Convert expression to a char or wchar string of maximum length width and formatted as specified in "format".
insstr Replace num2 chars from string_expr2 in string_expr1 beginning at position num1 (1st position is 1 not 0)
lcase Converts a string to lowercase
left Returns the leftmost num characters from the string
length Returns the length of the string
locate Locate string_expr1 from position num in string_expr2
ltrim Removes all leading spaces from string
octet_length Return the minimum value in each aggregate
position Find start position of string_expr2 within string_expr1.
repeat Repeats string num times
replace Replace string_expr2 with string_expr3 in string_expr1
right Returns the rightmost num characters from string
rtrim Removes all trailing spaces from string
substring Returns num2 characters from string_expr beginning at position num1.
trim If num == 1, trim leading occurrences of char from string_expr. If num == 2, trim trailing occurrences of char from string_expr. If num == 3, trim both leading and trailing occurrences of char from string_expr.
ucase Convert string to uppercase
unicode Returns the numeric Unicode value of a character
wchar Returns a Unicode character with numeric value num.

System functions (i.e., those which do not fit into any of the prior three categories) provided in RaimaDB SQL are described in the table below.

Built-in System Functions Table
Function Description
convert Converts expression to the specified data type.
last_insert_id Returns the last auto-generated rowid value. If table_name is specified then it returns the last auto-generated rowid value for that particular table.
last_insert_guid Returns the most recently generated guid value. This may have been either auto-generated from an insert of a guid column value or it was that generated from a more recent call to new_guid().
new_guid Generates a new guid value.
ifnull Returns expression2 if expression1 is null, else expression1.
rownumber Returns current row number beginning at 1 of the current executing SELECT statement.
typeof Returns a string that specifies the data type of the expression result.

Arithmetic operators that are specified in an expression are evaluated based on the precedence given in the following table.

Precedence of Arithmetic Operators
Priority Operator Use
Highest () Parenthetical expressions
High + Unary plus
High - Unary minus
Medium * Multiplication
Medium / Division
Lowest + Addition
Lowest - Subtraction

The usual mixed-mode arithmetic rules apply as specified in the next table. The intersection of each column and row gives the data type that results from any arithmetic operation (+, -, *, /) on the two data types.

 

tinyint

smallint

integer

bigint

real

float

decimal

tinyint

integer

integer

integer

bigint

real

float

decimal

smallint

integer

integer

integer

bigint

real

float

decimal

integer

integer

integer

integer

bigint

real

float

decimal

bigint

bigint

bigint

bigint

bigint

real

float

decimal

real

real

real

real

real

real

float

float

float

float

float

float

float

float

float

float

decimal

decimal

decimal

decimal

decimal

float

float

decimal

Rules for the result precision and scale in operations involving two decimal types are as follows. Note that dn(pn,sn) represents a decimal value with precision pn and scale sn.

d1(p1, s1) + d2(p2, s2) = (d1 + d2)(max(p1,p2)+1, max(s1,s2))

d1(p1, s1) - d2(p2, s2) = (d1 - d2)(max(p1,p2), max(s1,s2))

d1(p1, s1) * d2(p2, s2) = (d1 * d2)(p1+p2, max(s1,s2))

d1(p1, s1) / d2(p2, s2) = (d1 * d2)(p1+p2, based on result value)

Okay, we know. That's a lot of detail to have to wade through but you're through it now and so we'll illustrate column expressions with a couple of examples. More sophisticated examples will be given in subsequent sections.

The following query computes the sales tax based on a rate of 9.1% for each book.

select bookid, price, price*0.091 tax from book;

BOOKID                     PRICE                     TAX
alcott01                 1200.00                  109.20
alcott02                 1075.00                   97.82
alcott03                 1550.00                  141.05
alcott04                 1250.00                  113.75
alcott05                  850.00                   77.35
alcott06                  875.00                   79.62
austen01                12500.00                 1137.50
austen02                13500.00                 1228.50
	...
wilde04                 22500.00                 2047.50
wilde05                  2000.00                  182.00
woolf01                  3250.00                  295.75
woolf02                  1750.00                  159.25
woolf03                 32500.00                 2957.50

The next query computes both the raw profit and the percentage profit margin for each book based on the price and cost columns in each row of the book table.

select bookid, price, cost, price-cost profit, ((price-cost)/cost)*100 margin from book;

BOOKID               PRICE            COST           PROFIT          MARGIN
alcott01           1200.00          960.00           240.00           25.00
alcott02           1075.00          860.00           215.00           25.00
alcott03           1550.00         1240.00           310.00           25.00
alcott04           1250.00         1000.00           250.00           25.00
alcott05            850.00          708.00           142.00           20.00
alcott06            875.00          729.00           146.00           20.00
austen01          12500.00         9615.00          2885.00           30.00
austen02          13500.00        10384.00          3116.00           30.00
    ...
wilde04           22500.00        17307.00          5193.00           30.00
wilde05            2000.00         1600.00           400.00           25.00
woolf01            3250.00         2600.00           650.00           25.00
woolf02            1750.00         1400.00           350.00           25.00
woolf03           32500.00        25000.00          7500.00           30.00

Notice any pattern when you compare the profit margin percentage with the price? The higher the price, the larger the profit margin.