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

Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR

NCR Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR, 1998. - 315 p.
Download (direct link): inntroduktionteradata1998.pdf
Previous << 1 .. 22 23 24 25 26 27 < 28 > 29 30 31 32 33 34 .. 76 >> Next

TITLE Specifies a title for displayed or printed results that differs from the default column name
DEFAULT Supplies a default value for a field when an INSERT statement does not specify a value.

You should avoid using NULLs if possible because they can complicate the meaning of a result table.

Because NULLs have no value, their meaning is ambiguous and easily misinterpreted by users—especially when you are dealing with an outer join problem, which naturally generates many nulls of its own.

4-4

Introduction to the Teradata RDBMS for UNIX
Data Definition

Teradata SQL Data Types

When you “define” a piece of data as a null, the null can be interpreted at least two different ways:

• the information is missing because its value is not known, but that value is applicable (missing but applicable)

• the information is missing because its value is inapplicable in the current row (missing and inapplicable)

Introduction to the Teradata RDBMS for UNIX

4-5
Data Definition

Creating New Tables

Creating New Tables

This topic describes how to create new tables using the SQL data Introduction definition language (DDL) capabilities.

The examples in this chapter use a Personnel database consisting of Example Database two tables named Employee and Department.

Employee Table

EmpNo Name DeptNo JobTitle Salary YrsExp
10001 Peterson J 100 Payroll Clerk 25,000.00 5
10002 Moffit H 100 Recruiter 35,000.00 3
10003 Leidner P 300 Secretary 23,000.00 13
10004 Smith T 500 Engineer 42,000.00 10
10005 Omura H 500 Programmer 40,000.00 8
10006 Kemper R 600 Assembler 29,000.00 7
10007 Aguilan J 600 Manager 45,000.00 11
10008 Phan A 300 Vice President 55,000.00 12
10009 Marston A 500 Secretary 22,000.00 12
10010 Reed C 500 Technician 30,000.00 4
10011 Chin M 100 Controller 38,000.00 11
10012 Watson L 500 Vice President 56,000.00 8
10013 Regan R 600 Purchaser 44,000.00 10

Department Table

DeptNo DeptName EmpCount Loc MgrNo
100 Administration 3 NYC 10004
300 Exec Office 2 NYC 10012
500 Engineering 5 ATL 10008
600 Manufacturing 3 CHI 10009

4-6

Introduction to the Teradata RDBMS for UNIX
Data Definition Creating New Tables

Creating the Employee and Department Tables

The following CREATE TABLE statement creates the Employee table illustrated above.

CREATE TABLE Employee, FALLBACK (EmpNo INTEGER,

Name VARCHAR(12),

DeptNo SMALLINT,

JobTitle VARCHAR(12),

Salary DECIMAL (8,2),

YrsExp BYTEINT

)

UNIQUE PRIMARY INDEX (EmpNo)

INDEX (Name)

;

The unique primary index for this table is the EmpNo column, with Name being a nonunique secondary index. The table has fallback enabled.

The following CREATE TABLE statement creates the Department table illustrated above.

CREATE TABLE Department, FALLBACK (DeptNo SMALLINT,

DeptName VARCHAR(14),

EmpCount INTEGER,

Loc CHAR (3) ,

MgrNo INTEGER

)

UNIQUE PRIMARY INDEX (DeptNo)

;

The unique primary index for this table is the DeptNo column. There is no secondary index on the table. The table has fallback enabled.

Note that column definitions follow the table name. Each column defined is characterized by one or more attributes, including a mandatory data type.

Introduction to the Teradata RDBMS for UNIX

4-7
Data Definition Altering Tables

Altering Tables

Introduction

This topic describes the SQL statement used to change the characteristics of an existing table.

Capabilities of the ALTER Statement

The ALTER statement supports changes to the following table entities:

• Columns (add or drop)

• Column attributes (add or drop; FORMAT, TITLE, and DEFAULT)

• Fallback (add or drop)

Named CONSTRAINTs can be dropped using their name.

Example: Adding a Column

This example alters the Department table to add a column called Budget.

ALTER TABLE Department ADD Budget DECIMAL(9,2)

After you perform this statement, the Department table looks like this:

Department Table

DeptNo DeptName EmpCount Loc MgrNo Budget
100 Administration 3 NYC 10004
300 Exec Office 2 NYC 10012
500 Engineering 5 ATL 10008
600 Manufacturing 3 CHI 10009

Note that the rows all contain nulls for the Budget column.

Example: Dropping a Column

This example alters the Department table to drop the Budget column.

ALTER TABLE Department DROP Budget

4-8

Introduction to the Teradata RDBMS for UNIX
Data Definition

Indexes

Introduction

What Is An Index?

Indexes

The Teradata RDBMS supports five types of indexes: _

• Unique Primary Index (UPI)

• Unique Secondary Index (USI)

• Nonunique Primary Index (NUPI)

• Nonunique Secondary Index (NUSI)

• Join Index I

This section defines these different index types and explains how to use them.

Indexes on tables in a relational database function much like book indexes—they speed up information retrieval.

In general, an index is used to perform the following:

• Distribute data rows (primary index only)
Previous << 1 .. 22 23 24 25 26 27 < 28 > 29 30 31 32 33 34 .. 76 >> Next