Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 137 138 139 140 141 142 < 143 > 144 145 146 147 148 149 .. 241 >> Next


Refer to Example 1 and 7, in the "Examples” section, below.

Teradata RDBMS for UNIX SQL Reference

8-111
Teradata SQL Syntax Guide

CREATE VIEW

Operators

SELECT expr Can Include Data Definitions

Locks

Overriding LOCKING FOR READ in a View

When defining a view that is not a modifier, one or more aggregate operators (SUM, AVG, MAX, MIN, and COUNT) may be used in the SELECT expr-list or in the conditional expression of a HAVING clause. They may not be used in the conditional expression of a WHERE clause.

Query expressions (set operators), UNION, INTERSECT, and EXCEPT (MINUS), can not be used to define a view.

When defining a view that is not a modifier, any expression in the SELECT expr-list may include a data definition (Data Type, FORMAT, TITLE, etc.). Data definitions determine the form of the view display.

For example, a TITLE phrase in the CREATE statement of the underlying table or view will not be displayed unless the TITLE phrase is also included in the CREATE VIEW statement.

The data definition for a view column may differ from that defined for the associated column of the underlying table or view. However, not all data definitions are relevant to view expressions.

For example, range constraints and DEFAULT declarations may not be used in the expression list. The WHERE clause, or the HAVING clause if GROUP BY is specified, should be used to define range constraints in a view.

When a view is defined with a LOCKING modifier, the specified lock is placed on the underlying table(s) each time the view is referenced in a SQL statement.

A LOCKING modifier which specifies locking for ACCESS is may be used in a CREATE VIEW statement to give ad hoc users and users who will modify the data, concurrent access. See also the “LOCKING Modifier”, later in this chapter.

A READ lock in a view can be overridden by placing a “LOCKING FOR ACCESS” modifier on the view.

For example, assume that an ad hoc user selects data at the same time a user modifying the data, attempts to modify the data. The READ lock placed by the ad hoc user will prevent the user modifying the data, from accessing the data, because update transactions require a WRITE lock. By creating a view for the ad hoc user that specifies an ACCESS lock, the ad hoc user can not prevent the user modifying the data from completing table modifications.

8-112

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE VIEW

An ACCESS lock allows data to be retrieved during write activities; be aware that a view defined with an ACCESS lock may display inconsistent results.

The following examples illustrate the use of CREATE VIEW:

Examples

The following statement creates a view of the Employee table so Example I that it provides access only to the names and job titles of the

employees in Department 300:

CREATE VIEW Dept300 (Name, JobTitle) AS SELECT Name, JobTitle FROM Employee WHERE DeptNo = 3 00 WITH CHECK OPTION;

The WITH CHECK OPTION prevents using this view to INSERT a row into Employee through the view, or to update any row of Employee for which DeptNo <> 300.

The following statement creates a view of the Department table. Example 2 Each column in the view is defined with a title. Therefore, the view

data is displayed with column titles that differ from those defined for the Department table.

CREATE VIEW DEPT (DeptNo, DeptName, Loc, MgrNo) AS SELECT DeptNo(TITLE 'Department Number'),

DeptName(TITLE 'Department Name'),

Loc (TITLE 'Department Location'),

MgrNo(TITLE 'Manager Number') FROM Department ;

The following statement creates a view that allows a personnel Example 3 executive to keep track of employees who have more experience on

the job than their supervisors. This statement defines a self-join of the Employee table. The temporary names “Workers” and “Managers”, created in the FROM clause, refer to the two temporary tables participating in the self-join.

CREATE VIEW EmpInfo (WorkerName,WorkerYrsExp,Department, ManagerName,ManagerYrsExp) AS SELECT Workers.Name, Workers.YrsExp, Workers.DeptNo, Managers.Name, Managers.YrsExp FROM Employee Workers, Employee Managers WHERE Workers.DeptNo = Managers.DeptNo AND Managers.JobTitle IN ('Manager', 'Vice Pres')

AND Workers.YrsExp > Managers.YrsExp ;

The following statement illustrates the use of aggregates in a view Example 4 definition. The result rows are grouped by department number and

include only those rows with an average salary of $35,000 or higher.

CREATE VIEW DeptSal (DeptNo, MinSal, MaxSal, AvgSal) AS SELECT DeptNo, MIN(Salary), MAX(Salary),

AVG(Salary) FROM Employee GROUP BY DeptNo HAVING AVG(Salary) >= 35000 ;

Teradata RDBMS for UNIX SQL Reference

8-113
Teradata SQL Syntax Guide

CREATE VIEW

Example 5

Example 6

SELECT * FROM DeptSal;

returns:

DeptNo MinSal MaxSal AvgSal

600 28,600.00 45,000.00 36,650.00

300 23,000.00 65,000.00 47,666.67

700 30,000.00 45,000.00 37,666.67
Previous << 1 .. 137 138 139 140 141 142 < 143 > 144 145 146 147 148 149 .. 241 >> Next