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 .. 202 203 204 205 206 207 < 208 > 209 210 211 212 213 214 .. 241 >> Next

Decimal - Smallest 1 - 1031 1 - 1031 10-18
Decimal - Largest 1031-1 1031 -1 Decimal (n,m) n = 1-18 m = 0 - n

Table C-5 String Length Limits: Teradata SQL Vs. DB2

Specification DB2 V3 DB2 V2R3.0 Teradata SQL V2R2.0
Maximum length of CHAR 254 bytes 254 bytes 32,000 bytes
Maximum length of VARCHAR 4046 byte (4kb pg) 32,704 byte (32kb pg) 4046 byte (4kb pg) 32,704 byte (32kb pg) 32,000 bytes
Maximum length of a character constant 254 bytes 254 bytes 255 bytes
Maximum length of a concatenated character string 32,764 bytes 32,764 bytes 32,000+ bytes
Maximum length of a concatenated graphic string 16,382 DBCS characters 16,382 DBCS characters 16,000+ bytes

C-28

Teradata RDBMS for UNIX SQL Reference
Comparing Teradata SQL and DB2

Limitations: DB2 Vs. Teradata SQL

Table C-6 Datetime Limits: Teradata SQL Vs. DB2

Specification DB2 V3a 0 23. BR3 D2 V Teradata SQL V2R2.0
Smallest DATE Value 0001-01-01 0001-01-01 AD Jan 1,1
Largest DATE Value 9999-12-31 9999-12-31 AD Dec 31, 3500
Smallest TIME Value 00.00.00 00.00.00 00:00:00.00
Largest TIME Value 24.00.00 24.00.00 24:00:00.00

a. ISO format

Teradata RDBMS for UNIX SQL Reference

C-29
Comparing Teradata SQL and DB2

Limitations: DB2 Vs. Teradata SQL

C-30

Teradata RDBMS for UNIX SQL Reference
Sample Personnel Database

Appendix D

Sample Personnel Database

Teradata RDBMS for UNIX SQL Reference
Sample Personnel Database

TeradataR DBMS for UNIX SQL Reference
Sample Personnel Database

Personnel Database

Personnel Database

The Personnel sample database contains the following tables:

Introduction

Employee

Department

Project

Charges

Typical data entered for each table is shown after each CREATE TABLE statement.

The Employee table is defined as follows.

Employee Table

J CREATE TABLE Employee

(EmpNo SMALLINT FORMAT '9(5)' CHECK (EmpNo >= 10001 AND EmpNo <= 32001) NOT NULL,

Name VARCHAR(12) NOT NULL,

DeptNo SMALLINT FORMAT '999' CHECK (DeptNo >= 100 AND DeptNo <= 900),

JobTitle VARCHAR(12),

Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99'

CHECK (Salary >= 1.00 AND Salary <= 999000.00),

YrsExp BYTEINT FORMAT 'Z9' CHECK (YrsExp >= -99 AND YrsExp < = 99) , dob DATE FORMAT 'MMMbDDbYYYY' NOT NULL,

Sex CHAR UPPERCASE NOT NULL CHECK (Sex IN ('M','F')),

Race CHAR UPPERCASE,

MStat CHAR UPPERCASE CHECK (MStat IN ('S','M','D','U')),

EdLev BYTEINT FORMAT 'Z9' CHECK (EdLev >=0 AND EdLev <=22)

NOT NULL,

HCap BYTEINT FORMAT 'Z9' CHECK (HCap >= -99 AND HCap <= 99 ) UNIQUE PRIMARY INDEX (EmpNo),

INDEX (Name) ;

An example of typical data in the personnel.Employee table is:

EmpNo DeptNo JobTitle Salary YrsExp DOB MStat EdLev HCap
10019 600 Test Tech 38, .600.00 6 Aug 29, 1956 M 12 0
10011 100 Controllr 58, .600.00 6 Nov 09, 1953 S 16 0
10007 600 Manager 60, .600.00 6 Jan 03, 1950 M 16 0
10018 300 President 75, .600.00 6 Jan 23, , 1945 M 16 0
10022 700 SalesSupp 28, .600.00 6 Aug 02, 1953 S 16 0

Teradata RDBMS for UNIX SQL Reference

D-1
Sample Personnel Database

Personnel Database

Department Table

The Department table is defined as follows.

CREATE TABLE personnel.Department, FALLBACK

(DeptNo SMALLINT FORMAT '999' CHECK (DeptNo >= 100 AND DeptNo <=900) NOT NULL,

DeptName VARCHAR(14),

Loc CHAR(3),

MgrNo SMALLINT FORMAT '9(5)' CHECK (MgrNo >= 10001 AND MgrNo <=32001) NOT NULL)

UNIQUE PRIMARY INDEX (DeptNo);

An example of typical data in the personnel.Department table is:

DeptNo DeptName Loc MgrNo

100 Administration NYC 10011

600 Manufacturing CHI 10077

500 Engineering ATL 10012

Project Table

The Project table is defined as follows.

CREATE TABLE personnel.Project, FALLBACK

(Proj_Id CHAR(8) TITLE 'Project// Id' NOT NULL,

Description CHAR(25) TITLE 'Project Description',

RecDate DATE FORMAT 'YY/MM/DD' TITLE 'Received//Date',

DueDate DATE FORMAT 'YY/MM/DD' TITLE 'Due //Date',

ComDate DATE FORMAT 'YY/MM/DD' TITLE 'Compl//Date')

UNIQUE PRIMARY INDEX (Proj_Id);

An example of typical data in the personnel.Project table is:

ProjectID Proj.Description Rec'd Date Due Date Compl Date

OE1-0003 O/E Batch System 96/01/21 96/05/01 96/06/01

AP2-0002 A/P Payable Online 96/03/05 96/10/15 96/10/24

ENG-0004 Assemble-Test MCSA 95/11/02 96/10/15 96/09/30

D-2 Teradata RDBMS for UNIX SQL Reference
Sample Personnel Database

Personnel Database

Charges Table

The Charges table is defined as follows.

CREATE TABLE personnel.Charges, FALLBACK (EmpNo SMALLINT FORMAT '9(5)'

TITLE 'Employee//Id' CHECK (TITLE >= 10001 AND TITLE <= 32001) NOT NULL,

Proj_Id CHAR(8) TITLE 'Project// Id' NOT NULL,

WkEnd DATE FORMAT 'YY/MM/DD'

TITLE 'Week//Ending',

Hours DECIMAL(4,1) FORMAT 'ZZ9.9' CHECK (Hours >= 0.5 AND Hours <=999.5)

PRIMARY INDEX (EmpNo, Proj_Id)

INDEX (Proj__Id);

COMMENT ON Personnel.Employee 'Employee Information.';

COMMENT ON Personnel.Employee.EmpNo 'Employeeserial number.';
Previous << 1 .. 202 203 204 205 206 207 < 208 > 209 210 211 212 213 214 .. 241 >> Next