ASA Getting Started
  Selecting Data from Database Tables
    Selecting columns from a table

Using calculated columns

The columns of the result set do not need to be just columns in tables. They can also be expressions calculated from the underlying data. You can combine table columns into a single result-set column, and you can use a wide variety of functions and operators to control the results you get.

List the value in stock of each product

• In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

  SELECT id, ( unit_price * quantity ) AS value
  FROM product

  id	value
  300	252
  301	756
  302	1050
  400	1008
  ...	...

Notes 

• Columns can be given an alias    By default the column name is the expression listed in the select list, but for calculated columns the expression is cumbersome and not very informative. Here the calculated column is renamed in the select list as value. value is the alias for the column.

• Other operators are available    In the above example, the multiplication operator is used to combine the columns. You can use other operators, including the standard arithmetic operators as well as logical operators and string operators.

  For example, the following query lists the full names of all customers:

  SELECT id, (fname || ' ' || lname ) AS "Full name"
  FROM customer

  The || operator concatenates strings. In this query, the alias for the column has spaces, and so must be surrounded by double quotes. This rule applies not only to column aliases, but to table names and other identifiers in the database.

  For a complete list of operators, see Operators.

• Functions can be used    In addition to combining columns, you can use a wide range of built-in functions to produce the results you want.

  For example, the following query lists the product names in upper case:

  SELECT id, UCASE( name )
  FROM product

  id	UCASE(product.name)
  300	TEE SHIRT
  301	TEE SHIRT
  302	TEE SHIRT
  400	BASEBALL CAP
  ...	...

  For a complete list of functions, see Alphabetical list of functions.