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 .. 134 135 136 137 138 139 < 140 > 141 142 143 144 145 146 .. 241 >> Next


A user/database can contain only one journal table.

The LOCAL single AFTER image journal is supported analogously to single BEFORE image journal:

• The Access Rights required to create or drop LOCAL single AFTER image journal are the same as for the analogous operation on single BEFORE image journal.

• LOCAL single AFTER image journaling is restricted to nonfallback data tables.

• Archive/Recovery rules for LOCAL single AFTER image journal and single BEFORE image journal are the same, except that LOCAL single AFTER image journal is used with ROLLFORWARD only, while single BEFORE image journal is used with ROLLBACK only.

Assume that a Teradata RDBMS has just been installed. The system administrator has logged on as user DBC, and has submitted a CREATE USER statement to create the administrative user called Sysadmin. As user Sysadmin, the administrator submits CREATE DATABASE statements to create such required databases as Finance, Executive, Engineering, Manufacturing, and Marketing.

8-102

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE USER

The administrator adds a new user to the finance database via the CREATE USER statement, as follows:

CREATE USER FROM AS

PERMANENT =

,PASSWORD =

,SPOOL =

,FALLBACK PROTECTION ,DUAL AFTER JOURNAL ,DEFAULT JOURNAL TABLE = ,DEFAULT DATABASE = ,STARTUP =

,ACCOUNT =

marks

finance

1000000

finanl

1200000

finance.journals finance 'EXEC setpf;'

'$mfinance','$hfinance'

Once user Marks is created, Sysadmin submits the following statement to grant Finance privileges with the WITH GRANT OPTION to Marks:

GRANT ALL ON finance TO marks WITH GRANT OPTION ;

Teradata RDBMS for UNIX SQL Reference

8-103
Teradata SQL Syntax Guide

CREATE VIEW

CREATE VIEW

The CREATE VIEW statement defines a relational view of data in

Function the database.

8-104

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE VIEW

Syntax

CREATE VIEW -------viewname -

— ( -Lcol_name —) —

-AS-

-SELECT— SEL—

— DISTINCT — ALL

expr -

AS

- tname. *

Vfrom

- tname

AS

-joined_table

INNER — LEFT-

-RIGHT-

lFULL-1

I—OMTFR---1

- JOIN-joined_table-ON -search_cond -

OUTER

CROSS JOIN single_table

AS

—( subquery ) T--------ă derivedjtname

I-AR-I

D

~\

>

Single

Tables

Joined

Tables

— ( -L col n,

( -L co_name -)-1

D

U

WHERE search_cond

lgroup by-

-col_name-col_pos expr

} Derived Tables

-HAVING— cond-

-WITH CHECK OPTION-

1ORDER BY-

— expr—
-col_name- -ASC-
col_pos DESC

UT

FF07B077

*

aname

aname

Teradata RDBMS for UNIX SQL Reference

8-105
Teradata SQL Syntax Guide

CREATE VIEW

where:

Syntax Element . . . Specifies . . .
viewname the name of the new view. If viewname is not fully qualified, the default database is used.
col_name the name of a view column. If more than one column is specified, list the col_names in the order in which each column is to be displayed for the view. It is recommended that a view have a maximum of 50 columns (see Usage Notes).
AS an introduction to the view definition.
LOCKING the type of lock to be placed on a database, table, view, or row. This setting will override any default usage lock placed on that object by the Teradata RDBMS (see Usage Notes below, and the section on “LOCKING Modifier”).
DATABASE TABLE VIEW the name of a database, table, or view that is to be locked
ROW a row or rows to be locked in accordance with the defining statement (see below).
dbname the name of the database (or user) to be locked.
tname the name of the table to be locked.
vname the name of the view to be locked.
FOR IN the type of lock to be placed.
ACCESS READ SHARE WRITE EXCLUSIVE the type of lock to be placed. The SHARE keyword may be used as a synonym for READ.
MODE an optional keyword.
NOWAIT that if the indicated lock cannot be obtained, the statement should be aborted. Used for situations where it is not desirable to have a statement wait for resources, possibly also tying up resources in the process of waiting.
SELECT that the expr(s) to be associated with col_name(s) are to be selected from one or more existing tables and/or views.
DISTINCT that only one row is to be returned from any set of duplicates that might result from a given expr-list. Two rows are considered duplicates only if each value in one is equal to the corresponding value in the other.
ALL that all rows, including duplicates, are to be returned in the results of the expr-list. This is the default value.

8-106

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE VIEW

Syntax Element . . . Specifies . . .
expr an expression. Aggregate and arithmetic operators may be used with expressions.
* that all columns of all tables referenced in the FROM clause be returned. When qualified by tname, specifies that all columns of only tname are to be returned. View columns are explicitly enumerated when views are defined; therefore, if a table is changed after a view is defined, those changes will not appear if the SELECT * construct is used.
Previous << 1 .. 134 135 136 137 138 139 < 140 > 141 142 143 144 145 146 .. 241 >> Next