Teradata Architecture and SQL Training

For pricing and availability
Contact: Tera-Tom Coffing
Email: Tom.Coffing@CoffingDW.com
Phone: 513 300-0341

Description

You can expect three things from a Tom Coffing Teradata Architecture and SQL class: hands-on, hands-on, and hands-on training. Students will begin each chapter covering the fundamentals and continue to step up to the most advanced techniques in a building block approach. Each student can create tables, understand different Primary Index options, create secondary and join indexes, run thousands of queries, and join many tables combined with subqueries, derived tables, date functions and formats, and advanced analytics. We can also customize the course based on your needs. Welcome to one of the best classes you will ever attend.

Reference Guide

The Teradata Architecture and SQL book by Tom Coffing and John Nolan is a 1296-page masterpiece of clear explanations into the Teradata architecture, tips and tricks for performance tuning, and every SQL statement. The PDF delivered to each student will be the backbone of the class and provide an excellent reference guide for all work done on any Teradata on-premises or cloud system.

Objectives

Whether you are new to Teradata and SQL or already have enormous experience, Tom Coffing will enrich your knowledge immensely. One thing is for sure: you will be extremely confident and proficient at working with a Teradata system and writing Teradata SQL.

Audience, prerequisites, and duration

This course is for everyone who has the opportunity to work on an Teradata system, from beginners to experts. There are never any prerequisites in a Tom Coffing classroom because Tom has mastered mixing new and experienced students within the same classroom. The course ranges from 2-5 days, depending on your needs, but the more days you choose, the more incredible the outcome will be, as we will customize the classes based on your needs.

Topics

Parallel Processing
Nodes, AMPS, and Parsing Engines
Primary Index
Secondary Indexes
Columnar Options
Creating Tables
Performance Tuning
Data Dictionary
How to Read and Interpret EXPLAIN Plans
Perm, Spool, and Temp Space
Partitioned Primary Index Tables (PPI)
Collect Statistics
Native Object Store
Temporal Tables
Aggregation
Joins
Date and Time Functions
Format Functions
Analytics and Window Functions
Temporary Tables
Subqueries
Strings
Interrogating the Data
Views
Set Operators
Creating Tables
Data Manipulation Language (DML)
Stored Procedures
Statistical Aggregate Functions
Mathematical Functions

Teradata Architecture and SQL Course Outline

Chapter 1 – The Teradata Architecture

What is Parallel Processing?
The Basics of a Single Computer
Data in Memory is Fast as Lightning
Teradata Parallel Processes Data
Parallel Architecture
Teradata Architecture Components in Action
When a Table is Created it goes to Every AMP
When a Table is Loaded each AMP gets a Portion of the Rows
All Teradata Tables are Distributed across ALL AMPS
Teradata Systems can Add AMPs for Linear Scalability
Understand that Teradata can scale to incredible size
AMPs and Parsing Engines (PEs) live inside SMP Nodes
Each Node is Attached via a Network to a Disk Farm
There are many Nodes in a Teradata Cabinet
Two SMP Nodes Connected Become One MPP System
Inside a Teradata Node
The Parsing Engine
The AMPs Responsibilities
Teradata Architecture Visual

Chapter 2 – The Primary Index

You Define the Primary Index when you CREATE the Table
Primary Index in the WHERE Clause – Single-AMP Retrieve
Using EXPLAIN
A Non-Unique Primary Index (NUPI)
Primary Index in the WHERE Clause – Single-AMP Retrieve
Using EXPLAIN in a NUPI Query
A conceptual example of a Multi-Column Primary Index
Primary Index in the WHERE Clause – Single-AMP Retrieve
A Conceptual Example of a Table with NO PRIMARY INDEX
A Full Table Scan is likely on a table with NO Primary Index
An EXPLAIN that shows a Full Table Scan
Table CREATE Examples with four different Primary Indexes
What happens when you forget the Primary Index?
Why create a table with No Primary Index (NoPI)?
Teradata Tables Can Duplicate Across All AMPS
The Concept of a Replicated Table

Chapter 3 – Hashing of the Primary Index

The Hashing Formula Facts
The Hash Map Determines which AMP will own the Row
The Hash Map Determines which AMP will own the Row
Placing rows on the AMP
A Review of the Hashing Process
Non-Unique Primary Indexes have Skewed Data
The Uniqueness Value
The Row Hash and Uniqueness Value make up the Row-ID
A Row-ID Example for a Unique Primary Index
A Row-ID Example for a Non-Unique Primary Index (NUPI)
Two Reasons why each AMP Sorts their rows by the Row-ID
AMPs sort their rows by Row-ID to Group like Data
AMPs sort their rows by Row-ID to do a Binary Search
Table CREATE Examples with four different Primary Indexes
Null Values all Hash to the Same AMP
A Unique Primary Index (UPI) Example
A Non-Unique Primary Index (NUPI) Example
A Multi-Column Primary Index Example
A No Primary Index (NoPI) Example
Quiz
Answer to Quiz

Chapter 4 – Secondary Indexes

Creating a Unique Secondary Index (USI)
What is in a Unique Secondary Index (USI) Subtable?
A Unique Secondary Index (USI) Subtable is hashed
How the Parsing Engine Uses the USI Subtable
A USI is a Two-AMP Operation
Creating a Non-Unique Secondary Index (NUSI)
What is in a Unique Secondary Index (USI) Subtable?
Non-Unique Secondary Index (NUSI) Subtable is AMP Local
How the Parsing Engine Uses the NUSI Subtable
Creating a Value-Ordered NUSI
The Hash Map Determines which AMP will own the Row
Quiz – Answer the Tough USI Questions
Answer to Quiz – Answer the Tough USI Questions
A Picture with a Base Table, USI, and NUSI Subtable
Quiz – Tough Questions on the USI and NUSI Subtables
Answer – Tough Questions on the USI and NUSI Subtables
A Query Using an USI Only Moves Two Blocks
A Query Using A NUSI Always Uses All AMPs
Two Non-Unique Secondary Indexes (NUSI) on a Table
A NUSI BITMAP Query (1 of 3)
A NUSI BITMAP Theory (2 of 3)
A NUSI Bitmap in Action (3 of 3)
A Brilliant Technique for a Unique Secondary Index
The USI for Partitioned Tables Points to the Row Key
A Brilliant Technique for a Non-Unique Secondary Index
The NUSI for Partitioned Tables Points to the Row Key
How the PE Decides on the NUSI or the Full Table Scan
The Bigger Quiz
The Bigger Quiz Answers
Multiple Choice DBA
Multiple Choice DBA
What are the Big Three Tactical Queries?
What are the Big Three Tactical Queries?

Chapter 5 – Partition Primary Index (PPI) Tables

The Concept behind Partitioning a Table
Creating a PPI Table with Simple Partitioning
A Visual Display of Simple Partitioning
An SQL Example that Explains Simple Partitioning
Creating a PPI Table with RANGE_N Partitioning per Month
A Visual of One Year of Data with Range_N per Month
An SQL Example Explaining Range_N Partitioning per Month
A Partition # and Row-ID = Row Key
An AMP Stores its Rows Sorted in only Two Different Ways
Creating a PPI Table with RANGE_N Partitioning per Day
Creating a PPI Table with RANGE_N Partitioning per Week
A Visual of Range_N Partitioning Per Week
SQL Example that explains Range_N Partitioning per Week
A Clever Range_N Option
Creating a PPI Table with CASE_N
A Visual of Case_N Partitioning
An SQL Example that explains CASE_N Partitioning
How many partitions do you see?
Number of PPI Partitions Allowed
How many partitions do you see?
The No Case and Unknown Partition Options
A Visual of Case_N Partitioning
Combining Older Data and Newer Data in PPI
Multi-Level Partitioning Combining Range_N and Case_N
A Visual of Multi-Level Partitioning
The SQL on a Multi-Level Partitioned Primary Index
NON-Unique Primary Indexes (NUPI) in PPI
PPI Table with a Unique Primary Index (UPI)
Tricks for Non-Unique Primary Indexes (NUPI)
Another Clever Trick for PPI Tables
Character-Based PPI for RANGE_N
A Visual for Character-Based PPI for RANGE_N
TIMESTAMP Partitioning That is Deterministic
Altering a PPI Table the Hard Way
Altering a PPI Table the Easy Way With TO CURRENT
ALTER to CURRENT_DATE with Save
Altering a PPI Table to Add or Drop Partitions
Deleting a Partition
Deleting a Partition and saving its contents
Using the PARTITION Keyword in your SQL
SQL for RANGE_N
SQL for CASE_N
Primary Time Index (Teradata 16.20 release)
Primary Time Index Table Created on Time Bucket Parameter
PTI Table Created Jointly on Time Bucket and Column
Primary Time Index Table Created Only on the Column
Primary Time Index Distribution Strategies Across AMPs
Sorting Strategies Within AMPs
Primary Time Index Auto-Generated Columns
SHOW Table Example of Auto-Generated Columns
Auto-generated Columns

Chapter 6 – Columnar Tables

Columnar Tables
Auto-Compression
No Auto Compress
DDL of Previous Example
What does a Columnar Table look like?
Comparing Normal Table vs. Columnar Tables
Columnar can move just One Container to Memory
Containers on AMPs match up perfectly to rebuild a Row
Indexes can be used on Columns (Containers)
Indexes can be used on Columns (Containers)
Visualize a Columnar Table
Single-Column vs. Multi-Column Containers
Comparing Normal Table vs. Columnar Tables
Columnar Row Hybrid CREATE Statement
Columnar Row Hybrid Example
Columnar Row Hybrid Query Example
Review of Row-Based Partition Primary Index (PPI)
Visual of Row Partitioning (PPI Tables) by Month
CREATE Statement for both Row and Column Partition
Visual of Row Partitioning (PPI Tables) and Columnar
How to Load into a Columnar Table
Another Form of Creating a Table with NO AUTO COMPRESS
Auto Compress in Columnar Tables
When and When NOT to use Columnar Tables
Did you know Tom Coffing was a Professional Golf Caddie?

Chapter 7 – Space

When your System Arrives, there is only User named DBC
USER DBC
First Assignment is to create another User just under DBC
USER DBC
Perm and Spool Space
Perm Space is for Permanent Tables
Spool Space is workspace that builds a User’s Answer Sets
Users are Assigned Spool Space Limits
What is the Purpose of Spool Limits?
Why did my query Abort and say “Out of Spool”?
How can Skewed Data cause me to run “Out of Spool”?
Why did my Join cause me to run “Out of Spool”?
Finding out how much Space you have
Space per AMP on all tables in a Database shows Skew
DBC’s 2nd Assignment is to CREATE Users and Databases
The Teradata Hierarchy Begins
The Teradata Hierarchy Continues
Differences between PERM and SPOOL
Databases, Users, and Views
What are Similarities between a DATABASE and a USER?
What is the Difference between a DATABASE and a USER?
Objects that take up PERM Space
A Series of Quizzes on Adding and Subtracting Space
Answer 1 to Quiz on Space
Space Transfer Quiz
Answer to Space Transfer Quiz
Drop Space Quiz
Answers to Drop Space Quiz

Chapter 8 – The User Environment

DBC is the only user when the system first arrives
DBC will Create Databases and Give them Space
DBC will create some initial Users
A Typical Teradata Environment
What are Similarities between a DATABASE and a USER?
Roles
Create a Role and then Assign that Role Its Access Rights
Create a User and Assign them a Default Role
Granting Access Rights
There are Three Types of Access Rights
Description of the Three Types of Access Rights
Profiles
Creating a Profile and a User
ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
Accounts and their Associated Priorities
Account String Expansion (ASE)
The DBC.AMPUsage View
Teradata TASM provides a User Traffic System
Teradata Viewpoint

Chapter 9 – How Teradata Works Internally

All Teradata Tables are spread across All AMPs
The Table Header and the Data Rows are Stored Separately
An AMP Stores the Rows of a Table inside a Data Block
To Read a Data Block, an AMP Moves the Block into Memory
Nothing Happens on Disk because Everything Happens in Memory
Most Taxing thing for an AMP is Moving Blocks into Memory
A Full Table Scan Means All AMPs must Read All Rows
The “Achilles Heel and slowest process is Block Transfer
Each Table has a Primary Index
A Query Using the Primary Index is a Single AMP Retrieve.
As Rows are added a Data Block will Eventually Split
A Full Table Scan Means All AMPs must Read All Blocks
A Primary Index Query uses a Single AMP and Single Block
Each AMP Can Have Many Blocks for a Single Table
A Full Table Scan Means All AMPs must Read All Blocks
Quiz – How Many Blocks Move into FSG Cache?
Answer – How Many Blocks Move into FSG Cache?
Quiz – How Many Blocks Move Using the Primary Index?
Answer – How Many Blocks Move Using the Primary Index?
Synchronized Scan (Sync Scan)
EXPLAIN Using a Synchronized Scan
Intelligent Memory
Teradata Intelligent Memory Gives Data a Temperature
Data deemed VeryHot stays in each AMP’s Intelligent Memory
Intelligent Memory Stays in Memory
What is the Goal of a Teradata Physical Database Design?

Chapter 10 – Inside the AMPs Disk

Rows are Stored in Data Blocks which are stored in Cylinders
An AMP’s rows are stored inside a Data Block in a Cylinder
An AMP’s Master Index is used to find the Right Cylinder
The Row Reference Array (RRA) Does the Binary Search?
A Block Splits into Two Blocks at Maximum Block Size
Data Blocks Maximum Block Size has Changed (V14.10)
The New Block Split with Teradata V14.10
The Block Split with Even More Detail
Teradata V14.10 Block Split Defaults
There is One Master Index and Thousands of Cylinder Indexes
Blocks Continue to Split as Tables Grow Larger
FYI – Some Advanced Information about Data Block Headers
A top-down view of Cylinders
There are Hot, Warm, and Cold Cylinders
Cylinders are used for Perm, Spool, Temp, and Journals
Each AMP has a Master Index
Each Cylinder on an AMP has a Cylinder Index
Quiz – What Two Things Does and AMP Read?
Answer – What Two Things Does and AMP Read?
Quiz – How Many Row Reference Arrays do you see?
Answer – How Many Row Reference Arrays do you see?
Quiz – How Many Row Reference Arrays are there Now?
Answer – How Many Row Reference Arrays do you see?
Quiz – How Many Row Reference Arrays in Total?
Answer – How Many Row Reference Arrays in Total?
Quiz – How Many Cylinder Indexes are here?
Answer – How Many Cylinder Indexes are here?
A More Detailed Illustration of the Master Index
A Real-World View of the Master Index
An Even More Realistic View of an AMP’s Master Index
The Cylinder Index
An Even More Realistic View of a Cylinder Index
How a Query using the Primary Index works
How the AMPs Do a Full Table Scan
How an AMP Reads Using a Primary Index

Chapter 11 – Temporal Tables Create Functions

Three types of Temporal Tables
CREATING a Bi-Temporal Table
PERIOD Data Types
Bi-Temporal Data Type Standards
Bi-Temporal Example – Tera-Tom buys!
A Look at the Temporal Results
Bi-Temporal Example – Tera-Tom Sells!
Bi-Temporal Example – How the data looks!
Normal SQL for Bi-Temporal Tables
NONSEQUENCED SQL for Temporal Tables
AS OF SQL for Temporal Tables
NONSEQUENCED for Both
Creating Views for Temporal Tables
Bi-Temporal Example – Socrates is DELETED!
Bi-Temporal Results – Socrates is DELETED

Chapter 12 – How Joins Work Internally

Teradata Join Quiz
Teradata Join Quiz Answer
The Joining of Two Tables
Teradata Moves Joining Rows to the Same AMP
Imagine Joining Two NoPI Tables that have No Primary Index
Both Tables are redistributed to Join Rows on the Same AMP
How do you join if One Table is Big and One Table is Small?
Duplicate the Small Table on Every AMP (like a mirror)
What Could You Do If Two Tables Joined 1000 Times a Day?
Joining Two Tables with the same PK/FK Primary Index
A Join with No Redistribution or Duplication
A Performance Tuning Technique for Large Joins
The Joining of Two Tables with an Additional WHERE Clause
An Example of the Fastest Join Possible
Using a Simple Volatile Table
A Volatile Table with a Primary Index
Using a Simple Global Temporary Table
Two Brilliant Techniques for Global Temporary Tables
The Joining of Two Tables Using a Global Temporary Table
Quiz – How Much Data Moves Across the BYNET?
Answer – How Much Data Moves Across the BYNET?
Join Feature PRPD

Chapter 13 – Join Indexes

Creating a Multi-Table Join Index
Visual of a Join Index
Outer Join Multi-Table Join Index
Visual of a Left Outer Join Index
Compressed Multi-Table Join Index
A Visual of a Compressed Multi-Table Join Index
Creating a Single-Table Join Index
Conceptual of a Single Table Join Index on an AMP
Single Table Join Index Great For LIKE Clause
Single Table Join Index with Value Ordered NUSI
Aggregate Join Indexes
Compressed Single-Table Join Index
Aggregate Join Index
Aggregate Join Index
Sparse Join Index
A Global Multi-Table Join Index
Creating a Hash Index
Join Index Details

Chapter 14 – Collect Statistics

The Teradata Parsing Engine (Optimizer) is Cost Based
The Purpose of Collect Statistics
When Teradata Collects Statistics it creates a Histogram
The Interval of the Collect Statistics Histogram
Histogram Quiz
Answers to Histogram Quiz
What to COLLECT STATISTICS On?
Why Collect Statistics?
How do you know if Statistics were collected on a Table?
A Huge Hint that No Statistics Have Been Collected
The Basic Syntax for COLLECT STATISTICS
COLLECT STATISTICS Examples for a better Understanding
The New Teradata Way to Collect Statistics
Where Does Teradata Keep the Collected Statistics?
The Official Syntax for COLLECT STATISTICS
How to Recollect STATISTICS on a Table
Teradata Always Does a Random AMP Sample
Random Sample is kept in the Table Header in FSG Cache
Multiple Random AMP Samplings
How a Random AMP gets a Table Row count
Random AMP Estimates for NUSI Secondary Indexes
USI Random AMP Samples are Not Considered
There’s No Random AMP Estimate for Non-Indexed Columns
The PE’s Plan if No Statistics Were Collected?
Stale Statistics Detection and Extrapolation
Extrapolation for Future Dates
How to Copy a Table with Data and the Statistics?
How to Copy a Table with NO Data and the Statistics?
COLLECT STATISTICS Directly From another Table
When to COLLECT STATISTICS Using only a SAMPLE
Examples of COLLECT STATISTICS Using only a SAMPLE
Examples of COLLECT STATISTICS
How to Collect Statistics on a PPI Table on the Partition
Teradata Statistics Enhancements
Teradata Statistics Enhancements Continued
Summary Statistics
MaxValueLength
MaxIntervals
Sample N Percent
Statistics Collection Improvements
Statistics Collection Improvements
AutoStats feature
Teradata Statistics Wizard

Chapter 15 – Native Object Store

Native Object Store (NOS)
Native Object Store (NOS) Capabilities
The Two Ways to Read Data From an External Object Store
Native Object Store (NOS) Security
Security Example
Finding all Objects in an Object Store

Chapter 16 – Introduction to SQL

Nexus Migrates Automatically To Teradata
Introduction
SELECT * (All Columns) in a Table
SELECT Specific Columns in a Table
Commas in the Front or Back?
Place your Commas in front for better Debugging Capabilities
Sort the Data with the ORDER BY Keyword
Two Examples of ORDER BY using Different Techniques
Changing the ORDER BY to Descending Order
NULL Values Sort Last in Ascending Mode (Default)
Changing the ORDER BY to Descending Order
NULL Values Sort Last in Ascending Mode (Default)
Using the NULLS LAST Command
NULL Values Sort Last in Descending Mode (DESC)
Using the NULLS FIRST Command
Major Sort vs. Minor Sort
Multiple Sort Keys using Names Vs. Numbers
An Order By That Uses an Expression
Sorts are Alphabetical, NOT Logical
Using A Valued CASE Statement to Sort Logically
Using A Searched CASE Statement to Sort Logically
Quiz – Can you Add a Minor Sort?
Answer – Can you Add a Minor Sort?
Order By Decode
Quiz – Can you Add Two Minor Sorts Using Decode?
Answer – Can you Add Two Minor Sorts Using Decode?
How to ALIAS a Column Name
Using an Alias in the WHERE and ORDER BY Clau
Using an Alias in the ORDER BY Clause with Decode
A Missing Comma Can Become an Alias by Mistake
Comments using Double Dashes are Single Line Comments
Comments for Multi-Lines
Comments for Multi-Lines As Double Dashes Per Line
Comments are a Great Technique for Finding SQL Errors

Chapter 17 – The WHERE Clause

Nexus Writes the SQL For You
The WHERE Clause limits Returning Rows
Numbers Don’t Need Single Quotes
Not Equal
Searching for NULL Values Using Equality Returns Nothing
Use IS NULL or IS NOT NULL when dealing with NULLs
Using Greater Than Or Equal To (>=)
AND in the WHERE Clause
Troubleshooting AND
OR in the WHERE Clause
Troubleshooting OR
WHY OR must utilize the Column Name Each Time
Troubleshooting Character Data
Using Different Columns in an AND Statement
Quiz – How Many Rows will Return?
Answer to Quiz – How Many Rows will Return?
What is the Order of Precedence?
Using Parentheses to change the Order of Precedence
Using an IN List in place of OR
The IN List is an Excellent Technique
IN List vs. OR Brings the Same Result
ANY
The IN List Can Use Character Data
Using a NOT IN List
Using a NOT = ALL List
A Technique for Handling Nulls with a NOT IN List
Technique 2 for Handling Nulls with a NOT IN List
The BETWEEN Statement is Inclusive
The NOT BETWEEN Statement is also Inclusive
The BETWEEN Statement Works for Character Data
The Like Command Wildcards are Percent and Underscore
LIKE command Underscore is Wildcard for one Character
CASE Doesn’t Matter with the LIKE Command
LIKE Command to Find Multiple Characters
Clever LIKE Syntax to Find Multiple Characters
LIKE Command to Find Either Character
Clever LIKE Syntax to Find Either Character
Finding Anyone Who Has A First_Name Ending in ‘Y’
Finding Anyone Who Has A Last_Name Ending in ‘Y’
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command

Chapter 18 – Distinct, Group By, Top, and Sample

Nexus Pivots Your Data With A Drop-and-Drag Interface
The Distinct Command
DISTINCT vs. GROUP BY
Quiz – How many rows come back from the Distinct?
Answer – How many rows come back from the Distinct?
Top Command
Top Command and Order By
Top Command and Order By Plus Nulls Last
Top Command With Ties
The Top Command Will Not Work With Certain Commands
TOP vs. SAMPLE
Sample Syntax
Sample For n Rows or a Percentage
Getting Multiple Samples
A SAMPLE Example Using the SAMPLEID
Using SAMPLE WITH REPLACEMENT
Using SAMPLE RANDOMIZED ALLOCATION
SAMPLE With Conditional Logic
Extreme Pivot Challenge

Chapter 19 – Aggregation

Nexus Changes Color Like a Chameleon
Quiz – Calculate the Answer Set in your Mind
Answer – Calculate the Answer Set in your Mind
Quiz 2 – Calculate the Answer Set in your Mind
There are Five Aggregates
Casting a Data Type
GROUP BY Delivers One Row Per Group
GROUP BY DEPT_NO or GROUP BY 1 are Equivalent
Limiting Rows and Improving Performance with WHERE
WHERE Clause in Aggregation limits unneeded Calculations
Keyword HAVING tests Aggregates after they are Totaled
Keyword HAVING is like an Extra WHERE Clause for Totals
Excluding Non-Aggregate Columns in the GROUP BY
GROUP BY GROUPING SETS
GROUP BY GROUPING SETS Super Query
GROUP BY GROUPING SETS Super Query Answer
GROUP BY ROLLUP
GROUP BY ROLLUP Answer Set
GROUP BY ROLLUP Super Query
GROUP BY CUBE Super Query

Chapter 20 – Joining Tables

Nexus Joins Tables Across Systems – Federated Queries
A Two-Table Join Using Traditional Syntax
A Two-Table Join Using Traditional Syntax and Aliasing
Best Practice is to Use a Table Alias
Even Better Practice – Qualify All Columns
A Join using ANSI Syntax
Both Queries Have the Same Results and Performance
Quiz – Can You Finish the Join Syntax?
Answer to Quiz – Can You Finish the Join Syntax?
Quiz – Can You Find the Error?
Answer to Quiz – Can You Find the Error?
Super Quiz – Can You Find the Difficult Error?
Answer to Super Quiz – Can You Find the Difficult Error?
Quiz – Which Rows from Both Tables Won’t Return?
Answer– Which Rows from Both Tables Won’t Return?
LEFT OUTER JOIN
LEFT OUTER Join Results
RIGHT OUTER JOIN
RIGHT OUTER Join Example and Results
FULL OUTER JOIN
FULL OUTER Join Results
Which Tables are Left and Which are Right?
Answer – Which Tables are Left and Which are Right?
INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional WHERE Clause
OUTER JOIN with Additional WHERE Clause
OUTER JOIN with Additional AND Clause
The DREADED Product Join
The DREADED Product Join Results
Cartesian Product Join with Traditional Syntax
Cartesian Product Join with ANSI Syntax
The CROSS JOIN
The CROSS JOIN Answer Set
The SELF JOIN
The SELF JOIN with ANSI Syntax
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the Three-Table Join?
Answer to Quiz – Can you Write the Three-Table Join?
Quiz –Write the Three-Table Join Using ANSI Syntax?
Answer –Write the Three-Table Join to ANSI Syntax?
Quiz – Can you Place the ON Clauses at the End?
Answer – Can you Place the ON Clauses at the End?
The Five-Table Join – Logical Insurance Model
Quiz – Write a Five Table Join Using ANSI Syntax
Answer – Write a Five Table Join Using ANSI Syntax
Quiz – Write a Five Table Join Using Traditional Syntax
Answer – Write a Five Table Join Using Non-ANSI Synt
Quiz – Re-Write this putting the ON clauses at the END
Answer – Re-Write this putting the ON clauses at the END

Chapter 21 – Date Functions

Nexus Joins Excel Spreadsheets to Database Tables
CURRENT_DATE
CURRENT_DATE and CURRENT_TIMESTAMP
Date and Time as a Teradata Extension
Dates are stored Internally as INTEGERS from a Formula
CAST of Dates
The FORMAT Command
More FORMAT Examples
Formatting a Date Using TO_CHAR
The TO_CHAR Command to Format Dollar Signs
The TO_CHAR Command for Formatting Numbers
Displaying Dates for INTEGERDATE and ANSIDATE
DATEFORM
Changing the DATEFORM in Client Utilities such as BTEQ
Timestamp Differences
Add or Subtract Days from a Date
The ADD_MONTHS Command
ADD_MONTHS to Add a Year to a Date
ADD_MONTHS to Add Five Years to a Date
ADD_MONTHS Vs. OADD_MONTHS
A Summary of Math Operations on Dates
Age in Days vs. Age in Years
Find What Day Of The Week You Were Born
The EXTRACT Command
EXTRACT from DATES and TIME
EXTRACT or Math Can Accomplish the Same Thing
EXTRACT with DATE and TIME Literals
EXTRACT Month on Aggregate Queries
The System Calendar
Using the System Calendar In Its Simplest Form
How to really use the Sys_Calendar.Calendar
Another Method For Advanced Dates
Changing the Date to a Timestamp
Different Time Zones
CURRENT_TIMESTAMP at LOCAL
LAST_DAY
NEXT_DAY
MONTHS_BETWEEN
Selecting the First and Last Day of the Current Month
Finding the Last Day of the Previous Month
Finding the Previous Friday Date From Today
The Trunc Function
The Round Function
Using CASE and Extract to Reformat Dates
Converting a String to a Date that has a Different Format
Combining Date and Time to Make a Timestamp
Storing Dates Internally
Converting a Timestamp into Time
Storing Time Internally
Storing TIME With TIME ZONE Internally
Storing Timestamp Internally
Storing Timestamp with TIME ZONE Internally
Storing Date, Time, and Timestamp with Zone Internally
Time Zones
Setting Time Zones at the System Level
Setting Time Zones at the User Level
Setting Time Zones at the Session Level
Seeing your Time Zone
Creating a Sample Table for Time Zone Examples
Inserting Rows in the Sample Table for Time Zone Examples
Inserting Rows in the Sample Table for Time Zone Examples
Normalizing our Time Zone Table with a CAST
Intervals for Date, Time, and Timestamp
Interval Data Types and the Bytes to Store Them
Using Intervals
Troubleshooting The Basics of a Simple Interval
Interval Arithmetic Results
A Date Interval Example
A Time Interval Example
A DATE Interval Example
A Complex Time Interval Example using CAST
A Complex Time Interval Example using CAST
The OVERLAPS Command
An OVERLAPS Example that Returns No Rows
The OVERLAPS Command using TIME
The OVERLAPS Command using a NULL Value

Chapter 22 – Temporary Tables

Nexus Converts Table Structures in Seconds
There are Three types of Temporary Tables
CREATING A Derived Table
Derived Query Examples with Three Different Techniques
Most Derived Tables Are Used To Join To Other Tables
The Three Components of a Derived Table
Visualize This Derived Table
Quiz – Derived Table Challenge
Answer to Quiz – Derived Table Challenge
Two Derived Tables Using a WITH Statement
WITH RECURSIVE Derived Table Hierarchy
WITH RECURSIVE Derived Table Query
WITH RECURSIVE Derived Table Definition
WITH RECURSIVE Derived Table Seeding
WITH RECURSIVE Derived Table Looping
WITH RECURSIVE Derived Table Looping in Slow Motion
WITH RECURSIVE Derived Table Looping Continued
WITH RECURSIVE Derived Table Looping Continued
WITH RECURSIVE Derived Table Ends the Looping
WITH RECURSIVE Derived Table Final SELECT
WITH RECURSIVE Results
Creating a Volatile Table
You Populate a Volatile Table with an INSERT/SELECT
The Three Steps to Use a Volatile Table
Why Would You Use the ON COMMIT DELETE ROWS?
HELP Volatile Table Command Shows your Volatile Tables
A Volatile Table with a Primary Index
The Joining of Two Tables Using a Volatile Table
You Can Collect Statistics on Volatile Tables
The New Teradata Way to Collect Statistics
Some Great Examples of Creating a Volatile Table Quickly
Creating Partitioned Primary Index (PPI) Volatile Tables
A Volatile Table That Only Populates Some of the Rows
A Volatile Table With No Data
A Volatile Table With Some of the Columns
A Volatile Table With No Data and Zeroed Statistics
A Multiset Volatile Table With Statistics Example
Using a Volatile Table to Get Rid of Duplicate Rows
CREATING A Global Temporary Table
Many Users Can Populate the Same Global Temporary Table
Global Temporary Table with a Primary Index

Chapter 23 – Sub-query Functions

Drag an Answer Set to the System Tree to Create a Table
An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
An IN List Ignores Duplicates
The Subquery
The Three Steps of How a Basic Subquery Works
These are Equivalent Queries
The Answer Set from the Subquery
Quiz- Answer the Difficult Question
Answer to Quiz- Answer the Difficult Question
Should you use Subquery or Join?
Quiz – Write the Subquery
Answer to Quiz- Write the Subquery
Quiz – Write the More Difficult Subquery
Answer to Quiz – Write the More Difficult Subquery
Quiz – Write the Extreme Subquery
Answer To Quiz – Write the Extreme Subquery
Quiz – Write the Subquery with an Aggregate
Answer to Quiz- Write the Subquery with an Aggregate
Quiz – Write the Correlated Subquery
Answer to Quiz- Write the Correlated Subquery
The Basics of a Correlated Subquery
The Top Query always runs first in a Correlated Subquery
Correlated Subquery Example vs. a Join with a Derived Table
Quiz- A Second Chance To Write a Correlated Subquery
Answer – A Second Chance to Write a Correlated Subquery
Quiz- A Third Chance To Write a Correlated Subquery
Answer – A Third Chance to Write a Correlated Subquery
Quiz- Last Chance To Write a Correlated Subquery
Answer – Last Chance to Write a Correlated Subquery
Quiz – Write the Extreme Correlated Subquery
Answer To Quiz – Write the Extreme Correlated Subquery
NOT IN Subquery Returns Nothing when NULLs are Present
Fixing a NOT IN Subquery with Null Values
Quiz- Write the NOT IN Subquery
Answer to Quiz- Write the NOT Subquery
Quiz – Write the Subquery using a WHERE Clause
Answer – Write the Subquery using a WHERE Clause
Quiz – Write the Subquery with Two Parameters
Answer – Write the Subquery with Two Parameters
Quiz – Write the Two Parameter Subquery With an Aggregate
Answer – Two Parameter Subquery and an Aggregate
How the Double Parameter Subquery Works
More on how the Double Parameter Subquery Works
Quiz – Write the Triple Subquery
Answer to Quiz – Write the Triple Subquery
IN is equivalent to = ANY
Using a Correlated Exists
How a Correlated Exists Matches Up
The Correlated NOT Exists

Chapter 24 – Analytic and Window Functions

Nexus Gives You Analytics for Free
ROW_NUMBER
Quiz – How did the Row_Number Reset?
A Clever Trick Using QUALIFY
Quiz – Return Only the last Two Days Per Product_ID
Answer – Return Only the last Two Days Per Product_ID
Quiz – Return Two Students Per Class_Code with Highest Grades
Answer – Return Two Students Per Class_Code with Highest Grades
Using a Derived Table
Teradata Syntax – The RANK Command
How to get Rank to Sort in Ascending Order
Teradata Rank and Group By to Reset
Teradata RANK, GROUP BY, and QUALIFY
Best Practice – RANK Using ANSI Syntax
Dense_Rank
RANK vs. DENSE_RANK
RANK() OVER and PARTITION BY
RANK() OVER, PARTITION BY, and QUALIFY
Quiz –Eliminate the RANK Values in the Answer Set
Answer –Eliminate the RANK Values in the Answer Set
RANK() OVER, PARTITION BY, and QUALIFY
Using a Derived Table
DENSE_RANK() OVER and PARTITION BY
PERCENT_RANK() OVER with 14 rows in Calculation
PERCENT_RANK() OVER with 21 rows in Calculation
PERCENT_RANK and PARTITION BY
Teradata Syntax for CSUM Ordered Analytic
Ordered Analytics Sort the Data Before the Calculations
After the Sort the CSUM is Calculated
CSUM – Major and Minor Sort Keys
GROUP BY Using Teradata Syntax Resets CSUM Calculation
Best Practice – Cumulative Sum Using ANSI Syntax
Cumulative Sum – The Sort Explained
Cumulative Sum – Rows Unbounded Preceding Explained
Cumulative Sum – Major and Minor Sort Keys
Reset with a PARTITION BY Statement
Totals and Subtotals through Partition By
The Moving SUM – Teradata Syntax
GROUP BY in the Moving SUM does a Reset
Best Practice – Moving Sum Using ANSI Syntax
Moving SUM every 3-rows vs. a Continuous Average
Partition By Resets the Calculations
The Moving Average – Teradata Syntax
GROUP BY in the Moving Average does a Reset
Best Practice – Moving Average Using ANSI Syntax
The Moving Window is Current Row and Preceding n
How Moving Average Handles the Order By
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Quiz – How is that 4th Row Calculated?
Answer to Quiz – How is that 4th Row Calculated?
Moving Average every 3-rows Vs. a Continuous Average
The Partition By Statement
Partition By Resets an ANSI OLAP
Teradata Syntax – Moving Difference (MDIFF)
Teradata Syntax – Moving Difference With GROUP BY
Best Practice – Moving Difference ANSI Syntax
Moving Difference with Partition By
XMLAGG
XMLAGG in DESC Mode
Finding a Value of a Column in the Next Row with MIN
Finding a Next Row Value with MIN and PARTITION BY
Finding Multiple Values of a Column in Upcoming Rows
Finding The Next Date using MAX
COUNT OVER for a Sequential Number
The MAX OVER Command
MAX OVER with PARTITION BY Reset
The MIN OVER Command
The MIN OVER Command with PARTITION BY
Finding Gaps Between Dates
CSUM For Each Product_ID For the First Three Days
FIRST_VALUE
Using FIRST_VALUE
FIRST_VALUE With Partitioning
Daily_Sales Minus FIRST_VALUE With Partitioning
FIRST_VALUE After Sorting by the Highest Value
FIRST_VALUE Combined with Row_Number and Qualify
FIRST_VALUE, Qualify, and Row_Number with Trick
FIRST_VALUE and a Derived Table
Last_Value Can Be Confusing
Last_Value Working Properly
Last_Value With Partitioning
Last_Value And First_Value with Partitioning
First and Last Value Difference
CUME_DIST
CUME_DIST With a Tie Value
CUME_DIST With Qualify
CUME_DIST and a Derived Table
CUME_DIST with 14 Rows
CUME_DIST With a Partition on 7 Rows
CURRENT ROW AND UNBOUNDED FOLLOWING
Different Windowing Options
Width_Bucket
The Quantile Function and Syntax
A Quantile Example
A Quantile Example using DESC Mode
QUALIFY to Find Products in the Top Partitions
QUALIFY to Find Products in the Top Partitions Sorted DESC
QUALIFY to Find Products in the Top Partitions Sorted ASC
QUALIFY to Find Products in Top Partitions with A Tiebreaker
Using Quartiles (Partitions of Four)
How Quantile Works

Chapter 25 – Strings

UPPER and lower Functions
The Length Command Counts Characters
LENGTH Does Not Work on Fixed Length Columns
OCTET_LENGTH
The TRIM Command Trims Leading and Trailing Spaces
The RTRIM and LTRIM Command Trims Spaces
Concatenation
Concat for Concatenation
The SUBSTRING or SUBSTR Commands
How SUBSTRING Works with NO ENDING POSITION
Using SUBSTR and CHAR_LENGTH Together
The POSITION Command finds a Letters Position
The POSITION Command is brilliant with SUBSTR
The RIGHT Function
The LEFT and RIGHT Functions
The REPLACE Function
SOUNDEX Function to Find a Sound
The REVERSE String Function
Initcap Function
LPAD and RPAD
NGRAM
OTRANSLATE
REGEXP_REPLACE Example
Another REGEXP_REPLACE Example
REGEXP_INSTR
REGEXP_SUBSTR
The ASCII Function

Chapter 26 – Interrogating the Data

Nexus Can Connect to a Nexus Server for Blistering Speeds
Quiz – What Would the Answer Be?
Answer to Quiz – What Would the Answer Be?
The NULLIFZERO Command Changes Zeros to Nulls
Quiz – Fill in the Answers for the NULLIF Command
Answer – Fill in the Answers for the NULLIF Command
COALESCE
COALESCE in a Real-World Example
The COALESCE Command
COALESCE is Equivalent to this CASE Statement
Some Great CAST (Convert And Store) Examples
A Rounding Example Using CAST
CAST will Round Values Up or Down
Valued Case vs. Searched Case
Combining Searched Case and Valued Case
The DECODE Command
DECODE
A Trick for getting a Horizontal Case
Extreme CASE Challenge
Answer – Extreme CASE Challenge
Put a CASE in the ORDER BY
Using A Searched CASE Statement to Sort Logically
Quiz – Can you Add a Minor Sort?
Answer – Can you Add a Minor Sort?
Order By DECODE
Quiz – Can you Add Two Minor Sorts Using Decode?
Answer – Can you Add Two Minor Sorts Using Decode?
CASE Challenge
Answer – CASE Challenge
Quiz – Advanced Case Challenge
Quiz – Advanced Case Challenge
Quiz – Advanced Case Challenge
Answer – Advanced Case Challenge

Chapter 27 – Views

Nexus Shows Views Visually and Builds the SQL
The Fundamentals of Views
Creating a Simple View to Restrict Sensitive Columns
Creating a Simple View to Restrict Rows
Creating a View to Join Tables Together
Sometimes We Create Views for Formatting
Basic Rules for Views
The Exception to the ORDER BY Rule inside a View
Another Exception to the ORDER BY Rule is TOP
Derived Columns in a View Must Have a Column Alias
The Standard Way Most Aliasing is Done
Another Way to Alias Columns in a View CREATE
What Happens When a View Column gets Aliased Twice?
How to Change A View

Chapter 28 – Set Operators

Import Excel and Save it as a Table
Rules of Set Operators
Quiz – Intersect Explained Logically
Answer – Intersect Explained Logically
Quiz – Union Explained Logically
Answer – Union Explained Logically
Quiz – Union ALL Explained Logically
Answer – Union ALL Explained Logically
Quiz – Except Explained Logically
Answer – Except Explained Logically
Quiz – Minus Explained Logically
Answer – MINUS Explained Logically
Quiz – Testing Your Knowledge
Answer – Testing Your Knowledge
Rule 1 – Equal Number of Columns in Both SELECT Lists
Rule 2 – Top Query Handles Aliasing
Rule 3 – The Bottom Query does the ORDER BY
Intersect Challenge
Answer – Intersect Challenge
Quiz – Set Operator Challenge
Answer – Set Operator Super Challenge
Quiz – Advanced Set Operator Challenge
Answer – Advanced Set Operator Challenge
UNION Vs. UNION ALL
Using UNION ALL and Literals
Using UNION ALL for Speed in Merging Data Sets
Great Trick: Place your Set Operator in a Derived Table
A Great Example of How EXCEPT and MINUS Work
Using UNION to be same as GROUP BY GROUPING SETS
USING Multiple SET Operators in a Single Request
Changing the Order of Precedence with Parentheses

Chapter 29 – Creating Tables

Creating a Table With A Unique Primary Index (UPI)
Creating a Table With A Non-Unique Primary Index (NUPI)
Creating a Table With A Multi-Column Primary Index
Creating a Table With No Primary Index
Creating a Table Without Entering a Primary Index Definition
Creating a SET Table
Creating a MULTISET Table
Creating a SET Table With a Unique Primary Index
Creating a Table With Multiple Secondary Indexes
Creating a PPI Table with Simple Partitioning
Creating a PPI Table with RANGE_N Partitioning per Month
Creating a PPI Table with RANGE_N Partitioning per Day
Creating a PPI Table with RANGE_N Partitioning per Week
A Clever Range_N Option
Creating a PPI Table with CASE_N
The No Case and Unknown Partition Options
Partitioning of Older and Newer Data Separately
Multi-Level Partitioning
Almost All PPI Tables have a Non-Unique Primary Index
PPI Table With a Unique Primary Index (UPI)
Clever Trick for PPI Tables
Another Clever Trick for PPI Tables
Character Based PPI for RANGE_N
TIMESTAMP Partitioning That is Deterministic
Altering a PPI Table the Hard Way
Altering a PPI Table the Easy Way With TO CURRENT
Using the PARTITION Keyword in your SQL
SQL for RANGE_N
SQL for CASE_N
Primary Time Index Table Created on Time Bucket Parameter
PTI Table Created Jointly on Time Bucket and Column
Primary Time Index Table Created Only on the Column
Primary Time Index Distribution Strategies Across AMPs
Creating a Columnar Table
Creating a Columnar Table With Multi-Column Containers
Columnar Row Hybrid CREATE Statement
CREATE Statement for both Row and Column Partition
CREATING a Bi-Temporal Table
Creating a Table With Fallback
Creating a Table With No Fallback
Creating a Table With a Before Journal
Creating a Table With a Dual Before Journal
Creating a Table With an After Journal
Creating a Table With a Dual After Journal
Creating a Table With a Journal
Why Use Journaling?
Table Customization of the Data Block Size
Table Customization with FREESPACE Percent
Creating a QUEUE Table
You Can Select From a Queue Table
Exploring the Real Purpose of a Queue Table
Column Attributes
An Example of a Table With Column Attributes
An Example of a Table With Column Level Constraints
An Example of a Table With Table Level Constraints
Create Table AS (CTAS)
Creating Partitioned Primary Index (PPI) Volatile Table
A Volatile Table That Only Populates Some of the Rows
A Volatile Table With No Data
A Volatile Table With Some of the Columns
Data Types
Data Types Continued
Data Types Continued
Major Data Types and the number of Bytes they take up

Chapter 30 – Inserts, Updates, and Deletes

INSERT Syntax # 1
INSERT Example with Syntax 1
INSERT Syntax # 2
INSERT Example with Syntax 2
INSERT Example with Syntax 3
Using NULL for Default Values
INSERT/SELECT Command
INSERT/SELECT Example using All Columns (*)
INSERT/SELECT to Build a Data Mart
Fast Path INSERT/SELECT
NOT quite the Fast Path INSERT/SELECT
UNION for the Fast Path INSERT/SELECT
BTEQ for the Fast Path INSERT/SELECT
The UPDATE Command Basic Syntax
Two UPDATE Examples
Subquery UPDATE Command Syntax
Example of Subquery UPDATE Command
Join UPDATE Command Syntax
Example of an UPDATE Join Command
Fast Path UPDATE
The DELETE Command Basic Syntax
A DELETE Example Deleting only Some of the Rows
Subquery and Join DELETE Command Syntax
Example of Subquery DELETE Command
Example of Join DELETE Command
Fast Path DELETE
Fast Path DELETE Example # 1
Fast Path DELETE Example # 2
Fast Path DELETE Example # 3
MERGE INTO
MERGE INTO Example that Matches
MERGE INTO Example that does NOT Match
OReplace
User Defined Types (UDTs)
Formatting for Internationalizations

Chapter 31 – Macros

The Rules of Macros
CREATING and EXECUTING a Simple Macro
Multiple SQL Statements inside a Macro
Complex Joins inside a Macro
Passing an INPUT Parameter to a Macro
Troubleshooting a Macro with INPUT Parameters
An UPDATE Macro with Two Input Parameters
Executing a Macro with Named (Not Positional) Parameters
Macro Parameter Restrictions
Troubleshooting a Macro
More Troubleshooting of a Macro
Clever Trick To Break Macro Rules

Chapter 32 – Stored Procedures

Stored Procedures Vs. Macros
Creating a Stored Procedure
How you CALL a Stored Procedure
Label all BEGIN and END statements Except the First Ones
How to Declare a Variable
How to Declare a Variable and then SET the Variable
An IN Variable is passed to the Procedure during the CALL
The IN, OUT, and INOUT Parameters
Using IF inside a Stored Procedure
Two Stored Procedures Using Different Techniques
Using Loops in Stored Procedures
You Leave a Loop and Repeat Until
Stored Procedure Workshop
Stored Procedure Workshop Answer
Stored Procedure Workshop Alternative Answer
Select Cursor
Select Cursor for Multiple Result Sets
Select Cursor for Set Operator Query
Cursor Example 1 of 4
Cursor Example 2 of 4
Cursor Example 3 of 4
Cursor Example 4 of 4
Error Handling
Calling a Stored Procedure with OUT and INOUT Parameters
Rules for Stored Procedures with OUT/INOUT Parameters
Running Stored Procedures from a BTEQ Script
Running A Stored Procedure from a Macro

Chapter 33 – User-Defined Functions

User-Defined Function Example
Creating a User-Defined Function For The MOD Function
User-Defined Function For POWER
Details about Keywords in the User-Defined Function
User-Defined Function To Provide Raise Estimates
User-Defined Function For LN
User-Defined Function For SQRT Function
User-Defined Function For RANDOM
User-Defined Function For ROUND
User-Defined Function For FLOOR
User-Defined Function For CEIL Using a CASE
User-Defined Function For EXP of Euler’s Number e
User-Defined Function To Convert Radians To Degrees
User-Defined Function For ABS
User-Defined Function For ACOS
User-Defined Function For ASIN
User-Defined Function For ATAN
User-Defined Function For COS
User-Defined Function For SIGN
User-Defined Function For TRUNC

Chapter 34 – Unstructured Data – JSON

Teradata JSON Data Type
JSON and the Internet of Things
Why Use JSON?
JSON Types
JSON Syntax Explanation
Building a Complex Object
Creating a Table and Inserting JSON Data
Querying the JSON Table
Continuing to Build a Complex Object
Creating a Table and Inserting JSON Data
Querying the JSON Table
How Nexus Displays the Query
Creating and Using JSON Data Types
Creating a Table and Inserting JSON Data
Querying the JSON Table
Creating a Table and Inserting CLOB Data
Querying the CLOB Table
Updating the CLOB Table
Creating a More Complex Object
How to Query Specific Columns
How to Format JSON Data
Teradata Does Not Support Multi-Value Inserts
Querying Semi-Structured Data With a WHERE Clause
JSONExtractValue
JSONExtract at the Root Level
JSON Extract with a Filter
JSONExtract With Null Data
New JSON
ExistValue
The KEYCOUNT Function
The METADATA Function
A Breakdown of the METADATA Characteristics
The ARRAY_TO_JSON Function
The ARRAY_TO_JSON Function Answer Set
ARRAY_AGG Function
JSON_COMPOSE Function

Chapter 35 – Statistical Aggregate Functions

The STATS_TABLE
The KURTOSIS Function
A KURTOSIS Example
The SKEW Function
SKEW Example
The STDDEV_POP Function
STDDEV_POP Example
The STDDEV_SAMP Function
A STDDEV_SAMP Example
The VAR_POP Function
A VAR_POP Example
The VAR_SAMP Function
The CORR Function
A CORR Example
Another CORR Example so you can Compare
The COVAR_POP Function
A COVAR_POP Example
Another COVAR_POP Example so you can Compare
The COVAR_SAMP Function
A COVAR_SAMP Example
Another COVAR_SAMP Example so you can Compare
The REGR_INTERCEPT Function
A REGR_INTERCEPT Example
Another REGR_INTERCEPT Example so you can Compare
The REGR_SLOPE Function
A REGR_SLOPE Example
Another REGR_SLOPE Example so you can Compare
The REGR_AVGX Function
A REGR_AVGX Example
Another REGR_AVGX Example so you can Compare
The REGR_AVGY Function
A REGR_AVGY Example
Another REGR_AVGY Example so you can Compare
The REGR_COUNT Function
A REGR_COUNT Example
The REGR_R2 Function
A REGR_R2 Example
The REGR_SXX Function
A REGR_SXX Example
The REGR_SXY Function
A REGR_SXY Example
The REGR_SYY Function
A REGR_SYY Example
Using GROUP BY

Chapter 36 – Mathematical Functions

Numeric Manipulation Functions
ABS
ACOS
ACOSH
ASIN
ASINH
ATAN
ATAN2
ATANH
Ceil
COS
COSH
DEGREES
EXP
Floor
LN
MOD
POWER
RADIANS
RANDOM
ROUND
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC or TRUNCATE

Chapter 37 – Locking

The Four Major Locks of Teradata
The Read Lock
The Read Lock and Joins
The Write Lock
The Exclusive Lock
The Three Levels of Locking
Locking at the Row Hash Level
Locking at the Table Level
Locking at the Database Level
The Ongoing Battle between Read and Write Locks
Compatibility between Read Locks
Why Read Locks Wait on Write Locks
Why Write Locks Wait on Read Locks
The Access Lock is Different From the Other Locks
What is the Purpose of an Access Lock?
Locking Modifiers – Locking Row For Access
All Views Should Consider the Locking for Access Statemen
What is a Dead Lock or a Deadly Embrace?
Pseudo Tables are Designed to Minimize Dead Locks