17.10 - Using Data Definition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

The following CREATE TABLE statement uses Teradata SQL data definition phrases to define the columns of the Employee table:

CREATE TABLE Employee
 (EmpNo PRIMARY KEY SMALLINT FORMAT '9(5)'
   CHECK(EmpNo BETWEEN 1000 AND 32001),
  Name VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
  DeptNo SMALLINT FORMAT '999'
   CHECK (DeptNo BETWEEN 100 AND 900),
  JobTitle VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
 Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99'
   CHECK (Salary BETWEEN 1.00 AND 999000.00),
  YrsExp BYTEINT FORMAT 'Z9'
   CHECK (YrsExp BETWEEN -99 AND 99),
  DOB DATE FORMAT ’MMMbDDbYYYY’ NOT NULL,
  Sex CHAR CHARACTER SET LATIN UPPERCASE NOT NULL,
  Race CHAR CHARACTER SET LATIN UPPERCASE,
  MStat CHAR CHARACTER SET LATIN UPPERCASE,
  EdLev BYTEINT FORMAT 'Z9'
   CHECK (EdLev BETWEEN 0 AND 2) NOT NULL,
  HCap BYTEINT FORMAT 'Z9'
   CHECK (HCap BETWEEN -99 AND 99)
INDEX (Name) ;

The following SELECT statement uses Teradata SQL data definition phrases to modify the heading of the EmpNo column and the format of the results of the Salary column:

SELECT EmpNo (TITLE 'Employee Number'),
  Salary (FORMAT 'GLLZ(I)D9(F)')
FROM Employee;