First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 16. Introduction to Views       

Defining a view

Suppose that you frequently need to list a summary of employees and the departments they belong to. The following query produces the information you need.

  To list employees and the departments to which they belong:

1. Type the following:

   SELECT emp_fname, emp_lname, dept_name
   FROM employee JOIN department
   ON department.dept_id = employee.dept_id

emp_fname	emp_lname	department
Fran	Whitney	R&D
Matthew	Cobb	R&D
Robert	Breault	R&D
Natasha	Shishov	R&D
Kurt	Driscoll	R&D

You can create a view that produces the results of this command as follows:

CREATE VIEW emp_dept AS 
SELECT emp_fname, emp_lname, dept_name
FROM employee JOIN department
ON department.dept_id = employee.dept_id

This command creates a view called emp_dept that looks in many respects just like any other table in the database.

You can list everything in this view just as you do from a table:

  To list employees and the departments to which they belong:

1. Type the following:

   SELECT *
   FROM emp_dept

emp_fname	emp_lname	department
Fran	Whitney	R&D
Matthew	Cobb	R&D
Robert	Breault	R&D
Natasha	Shishov	R&D
Kurt	Driscoll	R&D

It is important to remember that the information in a view is not stored separately in the database. Each time you refer to the view, SQL executes the associated SELECT statement to find the appropriate data.

On one hand, this is good; it means that if someone modifies the employee table or the department table, the information in the emp_dept view will be automatically up to date. On the other hand, if the SELECT command is complicated it may take a long time for SQL to find the correct information every time you use the view.

Providing names for the view columns 

You can provide names for the view columns explicitly. First you must get rid of the original view definition as follows:

DROP VIEW emp_dept

You can redefine the view with the new column name as follows:

CREATE VIEW emp_dept(FirstName, LastName, Department) AS 
SELECT emp_fname, emp_lname, dept_name
FROM employee JOIN department
ON department.dept_id = employee.dept_id

You have changed the names of the columns in the view by specifying new column names in parentheses after the view name. Do SELECT * again to see the new column names.

More about views 

Views can be thought of as computed tables. Any SELECT command can be used in a view definition except commands containing ORDER BY. Views can use GROUP BY clauses, subqueries, and joins. Disallowing ORDER BY is consistent with the fact that rows of a table in a relational database are not stored in any particular order. When you use the view, you can specify an ORDER BY.

You can also use views in more complicated queries. Here is an example using a join:

SELECT LastName, dept_head_id
FROM emp_dept, department
WHERE emp_dept.Department = department.dept_name