ASA SQL User's Guide
  Working with Database Objects
    Working with views

Creating views

When you browse data, 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 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 using either Sybase Central or Interactive SQL.

To create a new view (Sybase Central)

1. Connect to a database.

2. Open the Views folder for that database.

3. From the File menu, choose New > View.

   The View Creation wizard appears.

4. Follow the instructions in the wizard. When the wizard exits, you can edit the code on the SQL tab in the right pane.

5. Complete the code by entering the table and the columns you want to use. For the example above, type employee and dept_ID.

6. From the File menu, choose Save View.

   New views appear in the Views folder.

To create a new view (SQL)

1. Connect to a database.

2. Execute a CREATE VIEW statement.

Example 

Create a view called DepartmentSize that contains the results of the SELECT statement given at the beginning of this section:

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

Since the information in a view is not stored separately in the database, referring to the view executes the associated SELECT statement 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 is automatically brought 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.

For more information, see CREATE VIEW statement.