Creating views

A SELECT statement operates on one or more tables and produces a result set that is also a table: just like a base table, a result set from a SELECT query has columns and rows. A view gives a name to a particular query, and holds the definition in the database system tables.

Suppose that you frequently need to list the number of employees in each department. You can get this list with the following statement:

SELECT dept_ID, count(*)
FROM employee
GROUP BY dept_ID

You can create a view containing the results of this statement as follows:

CREATE VIEW DepartmentSize AS
SELECT dept_ID, count(*)
FROM employee
GROUP BY dept_ID

The information in a view is not stored separately in the database. Each time you refer to the view, the associated SELECT statement is executed to retrieve the appropriate data.

On one hand, this is good because it means that if someone modifies the employee table, the information in the DepartmentSize view will be automatically up to date. On the other hand, complicated SELECT statements may increase the amount of time SQL requires to find the correct information every time you use the view.

1. Connect to the database.

2. Click the Views folder for that database.

3. Double-click Add View.

4. Enter the tables and columns to be used. For instance, to create the same view as in the SQL example shown above, enter employee and dept_ID.

5. From the File menu select Execute Script and from the File menu select Close.