Defining a view

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

To list employees and the departments to which they belong, type:

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

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, type the following:

SELECT *
FROM emp_dept

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.

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

Next, you can redefine the view with the new column names. Let's use Sybase Central to do this.