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 .. 138 139 140 141 142 143 < 144 > 145 146 147 148 149 150 .. 241 >> Next


500 22,000.00 56,000.00 38,285.71

The following request:

SELECT DeptNo, MinSal, MinSal+10000, AvgSal from DeptSal WHERE AvgSal > (MinSal + 10000) ;

returns:

DeptNo MinSal (MinSal+10000) AvgSal

500 22,000.00 32000.00 38,285.71

300 23,000.00 33000.00 47,666.67

The next example shows how use of a view name in an UPDATE, INSERT, or DELETE statement allows you to add, change, or remove data in the table (or tables) upon which the view is based. For example, updating data via a view changes data in the underlying table. Inserting or deleting rows via a view adds or removes rows from the underlying table.

Consider the following Staff_Info view, which gives a personnel clerk retrieval access to employee numbers, names, job titles, department numbers, sex, and dates of birth for all employees except vice presidents and managers:

CREATE VIEW staff_info

(number, name, position, department, sex, dob) AS SELECT employee.empno, name, jobtitle, deptno, sex, dob WHERE jobtitle NOT IN ('Vice Pres', 'Manager')WITH CHECK OPTION ;

If the owner of Staff_Info has the insert privilege on the Employee table, and if the clerk has the insert privilege on Staff_Info, then the clerk may use this view to add new rows to Employee. For example, entering the statement:

INSERT INTO staff_info

(number, name, position, department, sex, dob)

VALUES

(10024, 'Crowell N', 'Secretary', 200,

'F', 'Jun 03 1960') ;

8-114

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE VIEW

Caution:

inserts a row in the underlying Employee table that contains the specified information.

The constraint on Staff_Info illustrated by the following WHERE clause applies to any insert using this view that includes the WITH CHECK OPTION phrase.

WHERE jobtitle NOT IN ('Vice Pres', 'Manager')

Therefore, the preceding INSERT statement would fail if the Position entered for Crowell was Vice Pres or Manager.

If this view were defined to not include the WITH CHECK OPTION, and a user had UPDATE privilege, that user could update a job title to ‘Vice Pres’ or ‘Manager’. The user would be unable to access the changed row through the view.

Entering the following statement changes the department number (from 200 to 300) entered for Crowell in the preceding INSERT statement.

UPDATE staff_info

SET department = 3 00 WHERE number = 10024 ;

Entering the following statement removes the entire row for Crowell from Employee.

DELETE FROM staff_info WHERE number = 10024 ;

A view is a useful medium for allowing other users access to table data. However, as the preceding examples suggest, granting another user insert, update, and delete privileges on a view means relinquishing some control over your data. Carefully consider granting such privileges.

In prior releases, all updatable views were created as though they included WITH CHECK OPTION. Starting with V2R2.0, the default is not to constrain updated or inserted values unless the view definition explicitly includes WITH CHECK OPTION.

Teradata RDBMS for UNIX SQL Reference

8-115
Teradata SQL Syntax Guide

DATABASE

DATABASE

Function

The DATABASE statement establishes a new default database for the current session for all SQL statements that are entered without fully qualified table, view, or macro names. When sessions are logged on an initial default database is set during the logon. Normally, this is the database of the user.

The DATABASE statement is the only way to modify the default database (or user). This default database is used until the end of the session, or until a subsequent DATABASE statement is entered.The DATABASE statement can be included in the startup string for logging on. See also “CREATE USER”.

DATABASE is flagged as non-ANSI when the SQL flagger is enabled.

Syntax

where:

DATABASE-

-dbname -

UJ

FF07A016

Syntax Element . . . Specifies . . .
dbname the name of the default database.

2PC Mode

In 2PC mode, since DATABASE is treated as a DDL statement, and as such, it cannot be used.

Resolving Unqualified References

The default database of the executing user, not the creating user, is used to resolve unqualified references to database objects in a macro data definition statement (DDL). It is therefore recommended that object references in a macro data definition statement be fully qualified in the macro body (see “CREATE MACRO” statement).

Example

The following statement establishes Personnel as the default database for the current session:

DATABASE Personnel;

8-116

Teradata RDBMS for UNIX SQL Reference
Function

Basic DELETE

DELETE With Implied Join Condition

Syntax

Teradata SQL Syntax Guide

DELETE

DELETE

DELETE removes rows from a table. There are two main forms of DELETE:

This form of DELETE is ANSI-compliant and should be used for all delete’s referencing a single table, or referencing tables other than the one deleted from only by subqueries in the condition.
Previous << 1 .. 138 139 140 141 142 143 < 144 > 145 146 147 148 149 150 .. 241 >> Next