BASIC TERADATA EXTENSIONS
Describe usage of HELP, SHOW, and EXPLAIN commands and their expected outputs. List the differences between ANSI and Teradata modes in the areas of transaction protocol, defaults, conversions, and table creation. Describe usage of built-in Teradata key words (e.g., CURRENT_DATE).
DATA DEFINITION LANGUAGE (DDL) (NON-TEMPORAL)
Given a CREATE TABLE statement without a primary index declared, identify the column that Teradata will select as the primary index. Describe the attributes and constraints for a column in a table. Given a scenario, identify considerations using a CREATE TABLE AS definition. Given a scenario, identify the type of table that should be used (global temporary, volatile, and permanent).
DATA MANIPULATION LANGUAGE (DML) (NON-TEMPORAL)
Identify a correctly written INSERT statement, UPDATE statement, DELETE statement, SELECT statement, and MERGE INTO statement.
VIEWS AND MACROS (NON-TEMPORAL)
Identify uses of simple and parameterized macros. Identify the benefits of using views and macros. Identify the restrictions of creating views and macros. Describe the impact of using an access lock in a view or macro.
LOGICAL AND CONDITIONAL EXPRESSIONS
Identify the correct use of comparison operators in logical expressions. Evaluate expressions involving NULLs. Identify the correct use of multiple logical expressions in a conditional expression using AND, OR, NOT. Identify the correct use of the IN, NOT IN logical predicate.
DATA CONVERSIONS AND COMPUTATIONS
Given a scenario, identify the ramifications of implicit and explicit conversions from one data type to another. Given a scenario using CAST, identify the proper result. Identify the characteristics of ANSI CAST and Teradata extensions for data conversion.
CASE EXPRESSIONS
Given a scenario, identify the result of the CASE expression. Given a scenario, identify the use of different forms (searched and valued) of the CASE expression. Given a scenario, identify the use of specialized functions (COALESCE, NULLIF) of the CASE expression.
SUBQUERIES AND CORRELATED SUBQUERIES
Given a scenario, identify the SQL statement for a correlated subquery to qualify a subset of data. Given a scenario, identify the SQL statement for a noncorrelated subquery to qualify a subset of data. Given a scenario, identify the SQL statement for a correlated scalar subquery to qualify a subset of data. Given a scenario, identify the SQL statement for a noncorrelated scalar subquery to qualify a subset of data. Identify the characteristics of a scalar subquery.
JOINS (NON-TEMPORAL)
Given a scenario, determine the type of join to code to get the desired result set. Identify proper and improper use of aliasing in table joins. Given a scenario, identify the evaluation order of various join types. Given a scenario, identify the use of a derived table using single or multiple WITH form. Given a scenario, identify the use of a derived table using the FROM clause form. Given a scenario, identify the use of a derived table using a recursive WITH form. Identify the characteristics of outer joins.
ATTRIBUTE AND STRING FUNCTIONS
Identify the attribute functions and identify how they work within SQL. Identify the correct use of string manipulation functions and attributes. Identify formatting options for internationalization of various data types. Identify the correct use of new embedded services functions.