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 DepartmentID, COUNT(*)
FROM Employees
GROUP BY DepartmentID

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

CREATE VIEW DepartmentSize AS
SELECT DepartmentID, COUNT(*)
FROM Employees
GROUP BY DepartmentID

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