Defining a view

Suppose you frequently need to list employees by department. The following query creates a list of all employees by department and state that you can run against iqdemo:

SELECT Departments.DepartmentID,
  Departments.DepartmentName, Employees.EmployeeID,
  Employees.GivenName,Employees.Surname,
  Employees.Street,Employees.City,
  Employees.State,Employees.Phone
FROM Departments, Employees
ORDER BY Employees.State

Running the query creates a results set that looks like this:

In Interactive SQL, you can use a CREATE VIEW statement to create and store a view. This example creates a view called emp_dept in iqdemo:

CREATE VIEW emp_dept AS
  SELECT Departments.DepartmentID,
  Departments.DepartmentName, Employees.EmployeeID,
  Employees.GivenName,Employees.Surname,
  Employees.Street,Employees.City,
  Employees.State,Employees.Phone
FROM Departments, Employees
ORDER BY Employees.State

In Sybase Central, you can use a wizard to create a view. To start the wizard, connect to iqdemo, then do one of the following:

• In Tasks view, double-click the Views folder, then choose Create a view from the list of View Design Tasks.

• In Folders view, right-click Views, point to New, choose View.

When the wizard starts, follow the instructions on the screen.

You can query a view just like a table:

SELECT *
FROM emp_dept

Remember that the information in a view is not stored separately in the database. Each time you refer to the view, SQL executes the associated SELECT statement to find the appropriate data. This means that if the sales data changes, information in the view will be automatically up to date.

To see the results in Sybase Central, right-click the View, choose View Data in Interactive SQL.

In Interactive SQL, you can use an ALTER VIEW statement to change a view definition with a modified version. The emp_dept view lists all employees by department. You can use the following command to change the view to display only those employees in the Sales department:

ALTER VIEW emp_dept AS  
  SELECT Departments.DepartmentID,
   Departments.DepartmentName, Employees.EmployeeID,
   Employees.GivenName,Employees.Surname,
   Employees.Street,Employees.City,
   Employees.State,Employees.Phone
FROM Departments, Employees
WHERE Departments.DepartmentID = 200
ORDER BY Employees.State

ALTER VIEW replaces the existing SELECT statement in the view with the SELECT you defined in the ALTER VIEW command. Existing permissions on the view are maintained.

To change a view in Sybase Central, right-click the View, and choose Edit in New Window.

To delete a view from the database in Interactive SQL, use the DROP statement:

DROP VIEW <view name>

To drop a view in Sybase Central, right-click the view you want to delete, and choose Delete from the shortcut menu.