User's Guide

Part II. Tutorials

Chapter 11. Introduction to Views

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:

• 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:

• 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.

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