Teradata Example Tables
Employee Table DDL:
CREATE MULTISET TABLE TUTORIAL_DB.EMPLOYEE(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER
)
Primary Index(emp_no);
ALTER TABLE TUTORIAL_DB.EMPLOYEE ADD create_ts timestamp(0) default current_timestamp(0);
Department Table DDL:
CREATE MULTISET TABLE TUTORIAL_DB.DEPARTMENT(
dept_no INTEGER,
department_name VARCHAR(30),
loc_name VARCHAR(30)
)
Primary Index(dept_no);
Error Table DDL:
CREATE MULTISET TABLE TUTORIAL_DB.RUNTIME_ERROR(
session_nr DECIMAL(18,0),
procedure_name VARCHAR(50),
proc_step_txt VARCHAR(1000),
error_cd INTEGER
)
Primary Index(session_nr);
Department Table DML:
INSERT INTO TUTORIAL_DB.DEPARTMENT VALUES(100, 'ACCOUNTS', 'JAIPUR');
INSERT INTO TUTORIAL_DB.DEPARTMENT VALUES(200, 'R & D', 'NEW DELHI');
INSERT INTO TUTORIAL_DB.DEPARTMENT VALUES(300, 'SALES', 'BENGALURU');
INSERT INTO TUTORIAL_DB.DEPARTMENT VALUES(400, 'INFORMATION TECHNOLOGY', 'BHUBANESWAR');
Employee Table DML:
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000245, 'PRADEEP', 'PRESIDENT', null, '1981-11-17', 5000, null, 100);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000258, 'BLAKE', 'SENIOR MANAGER', 1000245, '1981-05-01', 2850, null, 300);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000262, 'CLARK', 'MANAGER', 1000245, '1981-06-09', 2450, null, 100);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000276, 'JONES', 'MANAGER', 1000245, '1981-04-02', 2975, null, 200);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000288, 'SCOTT', 'SYSTEM ANALYST', 1000276, '1987-07-13', 3000, null, 200);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000292, 'FORD', 'SYSTEM ANALYST', 1000276, '1981-12-03', 3000, null, 200);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000294, 'SMITH', 'LDC', 1000292, '1980-12-17', 800, null, 200);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000299, 'ALLEN', 'SALESMAN', 1000258, '1981-02-20', 1600, 300, 300);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000310, 'WARD', 'SALESMAN', 1000258, '1981-02-22', 1250, 500, 300);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000312, 'MARTIN', 'SALESMAN', 1000258, '1981-09-28', 1250, 1400, 300);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000315, 'TURNER', 'SALESMAN', 1000258, '1981-09-08', 1500, 0, 300);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000326, 'ADAMS', 'LDC', 1000288, '1987-07-13', 1100, null, 200);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000336, 'JAMES', 'LDC', 1000258, '1981-12-03', 950, null, 300);
INSERT INTO TUTORIAL_DB.EMPLOYEE VALUES( 1000346, 'MILLER', 'LDC', 1000262, '1982-01-23', 1300, null, 100);
Target Table DDL & Data:
CREATE MULTISET TABLE target_table(
id INTEGER,
name VARCHAR(100)
)
PRIMARY INDEX(id)
;
INSERT INTO target_table VALUES(1,'Teradata');
INSERT INTO target_table VALUES(1,'Teradata');
INSERT INTO target_table VALUES(2,'Database');
INSERT INTO target_table VALUES(3,'Oracle');
INSERT INTO target_table VALUES(4,'Vertica');
INSERT INTO target_table VALUES(4,'Vertica');
Temp Table 1 DDL & Data:
CREATE MULTISET TABLE temp_table_1(
id INTEGER,
name VARCHAR(100)
)
PRIMARY INDEX(id)
;
INSERT INTO temp_table_1 VALUES(1,'Teradata');
INSERT INTO temp_table_1 VALUES(1,'Teradata');
INSERT INTO temp_table_1 VALUES(2,'Database');
INSERT INTO temp_table_1 VALUES(5,'DB2');
INSERT INTO temp_table_1 VALUES(6,'MYSQL');
Temp Table 2 DDL & Data:
CREATE MULTISET TABLE temp_table_2(
id DECIMAL(38),
name VARCHAR(100)
)
PRIMARY INDEX(id)
;
INSERT INTO temp_table_2 VALUES(1,'Teradata');
INSERT INTO temp_table_2 VALUES(1,'Teradata');
INSERT INTO temp_table_2 VALUES(2,'Database');
INSERT INTO temp_table_2 VALUES(52343434232343242,'DB2');
INSERT INTO temp_table_2 VALUES(6635645646464,'MYSQL');