Introduction to NonStop SQL/MP

Using Views
How to Use NonStop SQL
2–6 113425 Tandem Computers Incorporated
Shorthand Views A shorthand view (the type of view discussed so far) provides convenient data
selection from any number of tables and other views. (You cannot update data using a
shorthand view.)
Figure 2-4 shows a shorthand view derived from the EMPLOYEE and DEPT tables.
Figure 2-4. A Shorthand View Derived From Two Tables
007
View contains
only manager
rows.
Asterisk selects
all columns and
rows in the view.
ERIC
ROGER
JERRY
RACHEL
MARY
JANE
THOMAS
•••
SELECT * FROM MGRLIST ;
View defines
new column
name.
•••
FIRST_NAME LAST_NAME
DEPTNAME
MGRLIST View
BROWN
GREEN
HOWARD
MCKAY
MILLER
RAYMOND
RUDLOFF
•••
PLANNING
CORPORATE
FINANCE
PERSONNEL
SHIPPING
MARKETING
INVENTORY
•••
CREATE VIEW MGRLIST (FIRST_NAME, LAST_NAME, DEPTNAME)
AS SELECT FIRST_NAME, LAST_NAME, DEPTNAME
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.EMPNUM = DEPT.MANAGER
CATALOG PERSNL ;
The CREATE VIEW statement in Figure 2-4 stores a join as a view named MGRLIST.
(The SELECT statement in Figure 2-4, while similar to the one shown in Figure 2-3,
does not order the result by department number.)
After you create a view, you can refer to it as if it were a table. For example, you can
request the name of the personnel manager by entering the following SELECT
statement:
SELECT FIRST_NAME, LAST_NAME
FROM MGRLIST
WHERE DEPARTMENT = "PERSONNEL" ;