ASA SQL User's Guide
Queries: Selecting Data from a Table
The SELECT list: specifying columns
The expressions in the select list can be more complicated than just column names or strings. For example, you can perform computations with data from numeric columns in a select list.
To illustrate the numeric operations you can carry out in the select list, we start with a listing of the names, quantity in stock, and unit price of products in the sample database.
SELECT name, quantity, unit_price FROM product
name | quantity | unit_price |
---|---|---|
Tee Shirt | 28 | 9 |
Tee Shirt | 54 | 14 |
Tee Shirt | 75 | 14 |
Baseball Cap | 112 | 9 |
... | ... | ... |
Suppose the practice is to replenish the stock of a product when there are ten items left in stock. The following query lists the number of each product that must be sold before re-ordering:
SELECT name, quantity - 10 AS "Sell before reorder" FROM product
name | Sell before reorder |
---|---|
Tee Shirt | 18 |
Tee Shirt | 44 |
Tee Shirt | 65 |
Baseball Cap | 102 |
... | ... |
You can also combine the values in columns. The following query lists the total value of each product in stock:
SELECT name, quantity * unit_price AS "Inventory value" FROM product
name | Inventory value |
---|---|
Tee Shirt | 252 |
Tee Shirt | 756 |
Tee Shirt | 1050 |
Baseball Cap | 1008 |
... | ... |
When there is more than one arithmetic operator in an expression, multiplication, division, and modulo are calculated first, followed by subtraction and addition. When all arithmetic operators in an expression have the same level of precedence, the order of execution is left to right. Expressions within parentheses take precedence over all other operations.
For example, the following SELECT statement calculates the total value of each product in inventory, and then subtracts five dollars from that value.
SELECT name, quantity * unit_price - 5 FROM product
To avoid misunderstandings, it is recommended that you use parentheses. The following query has the same meaning and gives the same results as the previous one, but some may find it easier to understand:
SELECT name, ( quantity * unit_price ) - 5 FROM product
For more information on operator precedence, see Operator precedence.
You can concatenate strings using a string concatenation operator. You can use either || (SQL/92 compliant) or + (supported by Adaptive Server Enterprise) as the concatenation operator.
The following example illustrates the use of the string concatenation operator in the select list:
SELECT emp_id, emp_fname || ' ' || emp_lname AS Name FROM employee
emp_id | Name |
---|---|
102 | Fran Whitney |
105 | Matthew Cobb |
129 | Philip Chin |
148 | Julie Jordan |
... | ... |
Although you can use operators on date and time columns, this typically involves the use of functions. For information on SQL functions, see SQL Functions.