Overview - Advanced SQL Engine - Teradata Database

This section provides examples of various operations that involve temporal tables.

Some of the examples use the following tables:

   CREATE MULTISET TABLE Policy(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Policy_Term PERIOD(DATE) NOT NULL AS VALIDTIME
      )
   PRIMARY INDEX(Policy_ID);

   CREATE MULTISET TABLE Policy_Types (
      Policy_Name VARCHAR(20),
      Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME
      )
   PRIMARY INDEX (Policy_Name);

   CREATE MULTISET TABLE Policy_History(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME
      )
   PRIMARY INDEX(Policy_ID);

Policy is a valid-time table, Policy_Types is a transaction-time table, and Policy_History is a bitemporal table. These tables are simplified forms of tables that might be used by an insurance application.