Sybase 12.4.2 manual Using views, 129, To create a view in Sybase Central

CHAPTER 3 Working with Database Objects

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.

❖To create a view in Sybase Central:

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.

For more information, see the Sybase Central online Help.

Using views

Restrictions on SELECT statements

When you use views, you need to be aware of certain restrictions, both on the SELECT statements you can use to create them, and on your ability to insert into, delete from, or update them.

There are some restrictions on the SELECT statements that you can use as views. In particular, you cannot use an ORDER BY clause in the SELECT query. A characteristic of relational tables is that there is no significance to the ordering of the rows or columns, and using an ORDER BY clause would impose an order on the rows of the view. You can use the GROUP BY clause, subqueries, and joins in view definitions.

To develop a view, tune the SELECT query by itself until it provides exactly the results you need in the format you want. Once you have the SELECT query just right, you can add a phrase in front of the query to create the view. For example:

129