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 .. 176 177 178 179 180 181 < 182 > 183 184 185 186 187 188 .. 241 >> Next


BonusNo SMALLINT FORMAT 'Z9'CHECK (BonusNo BETWEEN 0 and 99)NOT NULL,

BonusAmt DECIMAL (6,2) CHECK (BonusAmt BETWEEN 1.00 AND 5000.00) UNIQUE PRIMARY INDEX (EmpNo) ;

8-250

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

SHOW MACRO SHOW TABLE SHOW VIEW

Example 3: Column Level Named Constraints

ALTER TABLE EmpBonus

ADD DeptNo SMALLINT ;

Now, when a SHOW TABLE statement is entered for the EmpBonus table, for example,

SHOW TABLE EmpBonus;

the following CREATE TABLE statement is returned:

CREATE SET TABLE Personnel.EmpBonus, FALLBACK (EmpNo SMALLINT FORMAT '9(5)' CHECK (EmpNo BETWEEN 10001 AND 32 0 01)N0T NULL,

BonusNo SMALLINT FORMAT 'Z9' CHECK (BonusNo BETWEEN 0 AND 99) NOT NULL,

BonusAmt DECIMAL (6,2) CHECK (BonusAmt BETWEEN 1.00 AND 5000.00), DeptNo SMALLINT)

UNIQUE PRIMARY INDEX (EmpNo);

The DeptNo column is included in the CREATE TABLE statement. In addition, default values are displayed (for example, FALLBACK).

The statement in this example names constraints at the column level.

CREATE TABLE good_1 ( field_1 INTEGER NOT NULL

CONSTRAINT primary_1 PRIMARY KEY, field_2 INTEGER NOT NULL

CONSTRAINT unique_1 UNIQUE, field_3 INTEGER

CONSTRAINT check_1 CHECK (field_3 > 0), field_4 INTEGER

CONSTRAINT reference_1 REFERENCES parent_1

)

SHOW TABLE good_1;

results in a display:

CREATE SET TABLE Personnel.good_1, NO FALLBACK,

NO BEFORE JOURNAL NO AFTER JOURNAL

(

field_1 INTEGER NOT NULL, field_2 INTEGER NOT NULL, field_3 INTEGER, field_4 INTEGER,

CONSTRAINT check_1 CHECK (field_3 >0),

CONSTRAINT reference___1 FOREIGN KEY (field_4)

REFERENCES Personnel.PARENT_1 (f2))

UNIQUE PRIMARY INDEX primary_1 (field_1)

UNIQUE INDEX unique_1 (field_2);

Teradata RDBMS for UNIX SQL Reference

8-251
Teradata SQL Syntax Guide

SHOW MACRO SHOW TABLE SHOW VIEW

Example 4: REFERENCES Constraint

Notes on the standard form of display:

• SHOW TABLE always indicates whether the table was created with SET (no duplicate rows), SET mode or MULTISET (duplicate rows if no uniqueness defined).

• SHOW TABLE displays fallback and journal status of the table.

• Named constraints become either table level constraints or are mapped to unique indexes. The display reflects the implementation.

In this case, the named constraints on field_1 and field_2 are shown as table level constraints in the display, after the list of fields.

The named constraints on field_3 and field_4 become unique indexes. Since a PRIMARY KEY and no PRIMARY INDEX was declared in the CREATE TABLE, the PRIMARY KEY is implemented in the form of a UNIQUE PRIMARY INDEX, and is so listed. The UNIQUE constraint is implemented in the form of a UNIQUE INDEX and is so listed.

Here, Table Tl is created with a referential constraint which specifies that Foreign Key columns (f1) reference the unique primary index columns in table Good_1.

CREATE TABLE T1 (f1 integer NOT NULL f2 integer)

PRIMARY INDEX (f1)

FOREIGN KEY (f1) REFERENCES Good_1;

SHOW TABLE T1;

results in display:

CREATE SET TABLE T1, NO FALLBACK,

NO BEFORE JOURNAL, NO AFTER JOURNAL (f1 integer NOT NULL, f2 integer)

PRIMARY INDEX (f1)

FOREIGN KEY (f1) REFERENCES Personnel.Good_1(field_1);

8-252

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

SHOW MACRO SHOW TABLE SHOW VIEW

Example 5: SHOW VIEW

The following SHOW VIEW example begins with the creation of the view, then progresses to the SHOW VIEW statement and finally presents the result of the report request.

CREATE VIEW staff_info

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

When the following is entered:

SHOW VIEW staff_info;

the following is displayed:

CREATE VIEW staff_info

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

Teradata RDBMS for UNIX SQL Reference

8-253
Teradata SQL Syntax Guide

UPDATE

UPDATE

Function

The UPDATE statement changes field values in existing rows of a table.

Syntax

-pUPDATE-ptname

I UPD—I

^as-I

lFROM- tname

LAS-I

SET

- co_name = expr -

----ALL------

'- WHERE—cond —1

FF07A040

where:

aname

aname

Syntax Element ... Specifies . . .
tname the name of the table to be updated, or the name of a view through which the table is accessed.
AS an optional keyword introducing aname.
aname the alias name of a temporary table whose rows are to be updated during a self-join of an actual table (tname). The aname option is a Teradata RDBMS extension to ANSI SQL.
FROM a keyword introducing the names of one or more tables or view from which expr is to be derived. The FROM syntax is a Teradata RDBMS extension to ANSI SQL.
Previous << 1 .. 176 177 178 179 180 181 < 182 > 183 184 185 186 187 188 .. 241 >> Next