Using Expressions to Define Columns

Go Up to Creating Views (Data Definition Guide)

An expression can be any SQL statement that performs a comparison or computation, and returns a single value. Examples of expressions are concatenating character strings, performing computations on numeric data, doing comparisons using comparison operators (<, >, <=, and so on) or Boolean operators (AND, OR, NOT). The expression must return a single value, and cannot be an array or return an array. Any columns used in the value expression must exist before the expression can be defined.

For example, suppose you want to create a view that displays the salary ranges for all jobs that pay at least $60,000. The view, GOOD_JOB, based on the JOB table, selects the pertinent jobs and their salary ranges:

CREATE VIEW GOOD_JOB (JOB_TITLE, STRT_SALARY, TOP_SALARY) AS
 SELECT JOB_TITLE, MIN_SALARY, MAX_SALARY FROM JOB
 WHERE MIN_SALARY > 60000;

Suppose you want to create a view that assigns a hypothetical 10% salary increase to all employees in the company. The next example creates a view that displays all of the employees and their new salaries:

CREATE VIEW 10%_RAISE (EMPLOYEE, NEW_SALARY) AS
 SELECT EMP_NO, SALARY *1.1 FROM EMPLOYEE;

Note: Remember, unless the creator of the view assigns INSERT or UPDATE privileges, the users of the view cannot affect the actual data in the underlying table.