SQL/MX Quick Start (G06.24+, H06.03+)

Creating Database Objects
HP NonStop SQL/MX Quick Start—523724-002
9-3
Creating a View
Creating a View
A view provides an alternate way of looking at data in one or more tables. A view does
not store the data separately but retrieves it from the underlying tables. You must have
SELECT privileges on the underlying tables
Create a view and then select data from the view by using a SELECT statement:
Example
Suppose you often want to know the names of department managers. You can create
a view that includes this information. At the MXCI prompt, enter:
CREATE VIEW MYCAT.MYSCH.MGRLIST
(FIRST_NAME, LAST_NAME, DEPTNAME)
AS SELECT FIRST_NAME, LAST_NAME, DEPTNAME
FROM MYCAT.PERSNL.EMPLOYEE, MYCAT.PERSNL.DEPT
WHERE EMPLOYEE.EMPNUM = DEPT.MANAGER;
---SQL operation complete.
Now you can select from the view you created. At the MXCI prompt, enter:
SELECT FIRST_NAME, LAST_NAME FROM MGRLIST
WHERE DEPTNAME='PERSONNEL';
The selected row is:
FIRST_NAME LAST_NAME
--------------- --------------------
ROBERT WHITE
--- 1 row(s) selected.
Tip
•
To define a view, specify a SELECT statement after the beginning CREATE VIEW
MYVIEW AS. This SELECT statement is part of the definition. At any time, the view
consists of the rows that would result if the SELECT statement were executed.
•
This SELECT statement selects data from more than one table. The predicate
EMPLOYEE.EMPNUM = DEPT.MANAGER joins the EMPLOYEE table and the
DEPT table.