Chapter 1 - Teradata Parallel Architecture
Teradata Introduction
Teradata Architecture
Teradata Components
Parsing Engine Processor (PEP or PE)
Access Module Processor (AMP)
Message Passing Layer (BYNET)
A Teradata Database
CREATE / MODIFY DATABASE Parameters
Teradata Users
{ CREATE | MODIFY } DATABASE or USER (in common)
{ CREATE | MODIFY } USER (only)
Symbols Used in this Book
DATABASE Command
Use of an Index
Primary Index
Secondary Index
Determining the Release of Your Teradata System
Chapter 2 - Fundamental SQL Using SELECT
Fundamental Structured Query Language (SQL)
Basic SELECT Command
WHERE Clause
Compound Comparisons ( AND / OR )
Impact of NULL on Compound Comparisons
Using NOT in SQL Comparisons
Multiple Value Search (IN)
Using NOT IN
Using Quantifiers Versus IN
Multiple Value Range Search (BETWEEN)
Character String Search (LIKE)
Derived Columns
Creating a Column Alias Name
AS
NAMED
Naming conventions
Breaking Conventions
ORDER BY
TOP Rows Option
DISTINCT Function
Chapter 3 - On-line HELP and SHOW Commands
HELP commands
SET SESSION command
SHOW commands
EXPLAIN
Adding Comments
ANSI Comment
Teradata Comment
User Information Functions
ACCOUNT Function
DATABASE Function
SESSION Function
Chapter 4 - Data Conversions
Data Conversions
Data Types
CAST
Implied CAST
Formatted Data
Tricking the ODBC to Allow Formatted Data
TITLE Attribute for Data Columns
Transaction Modes
Case Sensitivity of Data
CASESPECIFIC
LOWER Function
UPPER Function
Chapter 5 - Aggregation
Aggregate Processing
Math Aggregates
The SUM Function
The AVG Function
The MIN Function
The MAX Function
The COUNT Function
Aggregates and Derived Data
GROUP BY
V2R6 GROUP BY Options
GROUP BY GROUPING SETS
GROUP BY ROLLUP
GROUP BY CUBE
Limiting Output Values Using HAVING
Statistical Aggregates
The KURTOSIS Function
The SKEW Function
The STDDEV_POP Function
The STDDEV_SAMP Function
The VAR_POP Function
The VAR_SAMP Function
The CORR Function
The COVAR Function
The REGR_INTERCEPT Function
The REGR_SLOPE Function
Using GROUP BY
Use of HAVING
Using the DISTINCT Function with Aggregates
Aggregates and Very Large Data Bases (VLDB)
Potential of Execution Error
GROUP BY versus DISTINCT
Performance Opportunities
Chapter 6 – Subquery Processing
Subquery
Using NOT IN
Using Quantifiers
Qualifying Table Names and Creating a Table Alias
Qualifying Column Names
Creating an Alias for a Table
Correlated Subquery Processing
Correlated Subquery To Find Duplicate Values
EXISTS
Chapter 7 – Join Processing
Join Processing
Original Join Syntax
Product Join
Newer ANSI Join Syntax
INNER JOIN
OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
Self Join
Alternative JOIN / ON Coding
Adding Residual Conditions to a Join
INNER JOIN
OUTER JOIN
OUTER JOIN Hints
Parallel Join Processing
Join Index Processing
Chapter 8 – Date and Time Processing
ANSI Standard DATE Reference
INTEGERDATE
ANSIDATE
DATEFORM
System Level Definition
User Level Definition
Session Level Declaration
DATE Processing
ADD_MONTHS
ANSI TIME
EXTRACT
Implied Extract of Day, Month and Year
ANSI TIMESTAMP
TIME ZONES
Setting TIME ZONES
Using TIME ZONES
Normalizing TIME ZONES
DATE and TIME Intervals
Using Intervals
INTERVAL Arithmetic with DATE and TIME
CAST Using Intervals
OVERLAPS
System Calendar
Chapter 9 – Character String Processing
Transforming Character Data
CHARACTERS Function
CHARACTER_LENGTH Function
OCTET_LENGTH Function
TRIM
SUBSTRING
SUBSTR
SUBSTRING and Numeric Data
POSITION
INDEX
SUBSTRING and POSITION or INDEX Used Together
Concatenation of Character Strings
Chapter 10 – OLAP Functions
On-Line Analytical Processing (OLAP) Functions
OLAP Functions
Cumulative Sum Using the CSUM Function
Cumulative Sum with Reset Capabilities
Using CSUM and GROUP BY
Generating Sequential Numbers with CSUM
Moving Sum Using the MSUM Function
Moving Sum with Reset Capabilities
Using MSUM and GROUP BY
Moving Average Using the MAVG Function
Moving Average with Reset Capabilities
Using MAVG and GROUP BY
Moving Difference Using the MDIFF Function
Moving Difference with Reset Capabilities
Using MDIFF and GROUP BY
Cumulative and Moving SUM Using SUM / OVER
Cumulative Sum with Reset Capabilities
SUM Using SUM / OVER and PARTITION BY
Moving Average Using AVG / OVER
Moving Average with Reset Capabilities
Using AVG and OVER / ROWS and PARTITION BY
Moving Linear Regression Using the MLINREG Function
Partitioning Data Using the QUANTILE Function
QUALIFY to Find Products in the top Partitions
Ranking Data using RANK
QUALIFY to Find Top Best or Bottom Worse
RANK with Reset Capabilities
Using RANK with GROUP BY
Ranking Data using RANK / OVER
QUALIFY to Find Top Best or Bottom Worse
RANK with Reset Capabilities
Using RANK/OVER with PARTITION BY
Internal RANK operations
Percentage of Total Rows Using PERCENT_RANK / OVER
Percent Rank with Reset Capabilities
Using PERCENT_RANK OVER and PARTITION BY
Counting of the Total Rows Using COUNT / OVER
COUNT OVER with Reset Capabilities
Using COUNT OVER and PARTITION BY
Finding the Largest Value Using MAX / OVER
Finding the Smallest Value Using MIN / OVER
Numbering of the Rows Using ROW_NUMBER / OVER
Sampling Rows using the SAMPLE Function
RANDOM Number Generator Function
Chapter 11 – SET Operators
Set Operators
Considerations for Using Set Operators
INTERSECT
UNION
EXCEPT
MINUS
Using Multiple Set Operators in a Single Request
Chapter 12 – Data Manipulation
Data Maintenance
Considerations for Data Maintenance
Safeguards
INSERT Command
Using Null for DEFAULT VALUES
INSERT / SELECT Command
Fast Path INSERT / SELECT
UPDATE Command
Fast Path UPDATE
DELETE Command
Fast Path DELETE
UPSERT
ANSI Vs Teradata Transactions
Performance Issues With Data Maintenance
Impact of FALLBACK on Row Modification
Impact of PERMANENT JOURNAL Logging on Row Modification
Impact of Primary Index on Row Modification
Impact of Secondary Indices on Row Modification
Chapter 13 – Data Interrogation
Data Interrogation
NULLIFZERO
NULLIF
ZEROIFNULL
COALESCE
CASE
Flexible Comparisons within CASE
Comparison Operators within CASE
CASE for Horizontal Reporting
Nested CASE Expressions
CASE used with the other DML
Using CASE to avoid a join
Chapter 14 – View Processing
Views
Reasons to Use Views
Considerations for Creating Views
Creating and Using VIEWS
Deleting Views
Modifying Views
Modifying Rows Using Views
DML Restrictions when using Views
INSERT using Views
UPDATE or DELETE using Views
WITH CHECK OPTION
Locking and Views
Chapter 15 – Macro Processing
Macros
CREATE MACRO
REPLACE MACRO
EXECUTE Macro
DROP MACRO
Generating SQL from a Macro
Chapter 16 – Transaction Processing
What is a Transaction
Locking
Transaction Modes
Comparison Chart
Setting the Transaction Mode
Teradata Mode Transactions
ANSI Mode Transactions
Aborting Teradata Transactions
Aborting ANSI Transactions
Chapter 17 – Reporting Totals and Subtotals
Totals and Subtotals
Totals (WITH)
Subtotals (WITH…BY)
Multiple Subtotals on a Single Break
Multiple Subtotal Breaks
Chapter 18 – Data Definition Language
Creating Tables
Table Considerations
Maximum Columns per Table
Table Naming Conventions
CREATE TABLE
Column Data Types
Specifying the Database in a CREATE TABLE Statement
PRIMARY INDEX considerations
Table Type Specifications of SET VS MULTISET
SET and MULTISET Tables
Protection Features
FALLBACK
Permanent Journal
BEFORE Journal
AFTER Journal
Internal Storage Options
DATABLOCKSIZE
FREESPACE PERCENTAGE
QUEUE Tables
Partitioned Primary Index (PPI)
Column Attributes
Constraints
UNIQUE Constraint
CHECK Constraint
Referential Integrity (RI) Constraint
Defining Constraints at the Column level
Defining Constraints at the Table Level
Utilizing Default Values for a Table
Secondary Indices
CREATE TABLE to Copy an existing table
Altering a Table
Modifying the Primary Index Partitioning
Revalidating the Primary Index
Dropping a Table
Dropping a Table versus Deleting Rows
Renaming a Table
Creating Secondary via CREATE INDEX
Join Index
Collecting Statistics
Hashing Functions
HASHROW
HASHBUCKET
HASHAMP
HASHBAKAMP
Chapter 19 – Temporary Tables
Temporary Tables
Creating Interim or Temporal Tables
Temporary Table Choices
Derived Tables
Derived Tables Using a Non-Recursive WITH
Derived Tables Using a Recursive WITH
FROM TABLE UDF Tables
Volatile Temporary Tables
Global Temporary Tables
GLOBAL Temporary Table Examples
General Practices for Temporary use Tables
Chapter 20 – Trigger Processing
Triggers
Terminology
Logic Flow
CREATE TRIGGER Syntax
Row Trigger
Statement Trigger
‘BEFORE’ Trigger
‘AFTER’ Trigger
‘INSTEAD OF’ Trigger
Cascading Triggers
Sequencing Triggers
Chapter 21 – Stored Procedures
Teradata Stored Procedures
CREATE PROCEDURE
Stored Procedural Language (SPL) Statements
BEGIN / END Statements
Establishing Variables and Data Values
DECLARE Statement to Define Variables
SET to Assign a Data Value as a Variable
Status Variables
Assigning a Data Value as a Parameter
Additional SPL Statements
CALL Statement
CASE / END CASE Statement
IF / END IF Statement
LOOP / END LOOP Statements
LEAVE Statement
REPEAT / END REPEAT Statement
WHILE / END WHILE Statement
FOR / END FOR Statements
ITERATE Statement
Using a Cursor
DECLARE CURSOR Statement
OPEN CURSOR Statement
CLOSE CURSOR Statement
FETCH Statement
Exception Handling
DECLARE HANDLER Statement
PRINT Statement
DML Statements
Using Column and Alias Names
Comments and Stored Procedures
Commenting in a Stored Procedure
Commenting on a Stored Procedure
On-line HELP for Stored Procedures
HELP on a Stored Procedure
HELP on Stored Procedure Language (SPL)
REPLACE PROCEDURE
ALTER PROCEDURE
DROP PROCEDURE
RENAME PROCEDURE
SHOW PROCEDURE
Dynamic SQL
Considerations When Using Stored Procedures
Compiling a Procedure
Temporary Directory Usage