Filter
Top Products
CHAPTER 3 Working with Database Objects
129
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
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.
Restrictions on
SELECT statements
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: