Contents
Passing Your Teradata Certification Tests
SELECT * (All Columns) in a Table
SELECT Specific Columns in a Table
Place your Commas in front for better Debugging Capabilities
Sort the Data with the ORDER BY Keyword
ORDER BY Defaults to Ascending
Use the Name or the Number in your ORDER BY Statement
Two Examples of ORDER BY using Different Techniques
Changing the ORDER BY to Descending Order
NULL Values sort First in Ascending Mode (Default)
NULL Values sort Last in Descending Mode (DESC)
Multiple Sort Keys using Names vs. Numbers
Sorts are Alphabetical, NOT Logical
Using A CASE Statement to Sort Logically
A Missing Comma can by Mistake become an Alias
The Title Command and Literal Data
Comments using Double Dashes are Single Line Comments
Comments for Multi-Lines As Double Dashes Per Line
A Great Technique for Comments to Look for SQL Errors
The WHERE Clause limits Returning Rows
Using a Column ALIAS throughout the SQL
Double Quoted Aliases are for Reserved Words and Spaces
Character Data needs Single Quotes in the WHERE Clause
Character Data needs Single Quotes, but Numbers Don’t
Comparisons Against a Null Value
NULL means UNKNOWN DATA so Equal (=) won’t Work
Use IS NULL or IS NOT NULL when dealing with NULLs
NULL is UNKNOWN DATA so NOT Equal won’t Work
Use IS NULL or IS NOT NULL when dealing with NULLs
Using Greater Than Or Equal To (>=)
Using GE as Greater Than or Equal To (>=)
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 Results
The IN List Can Use Character Data
Null Values in a NOT IN List Bring Back No Rows
A Technique for Handling Nulls with a NOT IN List
An IN List with the Keyword ANY
A NOT IN List with the Keywords NOT = ALL
A NOT IN List with the Keywords NOT = ALL and NULL
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
LIKE command Underscore is Wildcard for one Character
LIKE Command Works Differently on Char Vs Varchar
Troubleshooting LIKE Command on Character Data
Quiz – What Data is Left Justified and What is Right?
Numbers are Right Justified and Character Data is Left
Answer – What Data is Left Justified and What is Right?
An Example of Data with Left and Right Justification
A Visual of CHARACTER Data vs. VARCHAR Data
Use the TRIM command to remove spaces on CHAR Data
LIKE ALL means ALL conditions must be Met
These Two Queries Do Exactly The Same Thing
LIKE ANY means ANY of the Conditions can be Met
These Two Queries Do Exactly The Same Thing
IN ANSI Transaction Mode Case Matters
In Teradata Transaction Mode Case Doesn’t Matter
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command
ANSWER – To Find that Wildcard
Chapter 3 – Distinct, Group By and TOP
Quiz – How many rows come back from the Distinct?
Answer – How many rows come back from the Distinct?
TOP Command is brilliant when ORDER BY is Used!
What is the Difference Between TOP and Sample?
SAMPLE Does NOT Use the ORDER BY Correctly
The TOP Command will NOT work with Certain Commands
Quiz – You calculate the Answer Set in your own Mind
Answer – You calculate the Answer Set in your own Mind
Quiz – How many rows come back?
Answer – How many rows come back?
GROUP BY when Aggregates and Normal Columns Mix
GROUP BY Delivers one row per Group
GROUP BY Dept_No or GROUP BY 1 the same thing
Limiting Rows and Improving Performance with WHERE
WHERE Clause in Aggregation limits unneeded Calculations
Keyword HAVING tests Aggregates after they are Totaled
Aggregates Return Null on Empty Tables
Keyword HAVING is like an Extra WHERE Clause for Totals
Getting the Average Values Per Column
Average Values Per Column For all Columns in a Table
Three types of Advanced Grouping
Big Table Small Table Join Strategy
Duplication of the Smaller Table across All-AMPs
If the Join Condition is the Distribution Key no Movement
Matching Rows That Are On The Same AMP Naturally
What if the Join Condition Columns are Not Primary Indexes
Strategy 1 of 4 – The Merge Join
Quiz – Redistribute the Employees by their Dept_No
Quiz – Employees' Dept_No landed on AMP with Matches
Quiz – Redistribute the Orders to the Proper AMP
Answer to Redistribute the Employees by their Dept_No Quiz
Strategy 2 of 4 – The Hash Join
Strategy 3 of 4 – The Nested Join
Strategy 4 of 4 – The Product Join
A Two-Table Join Using Traditional Syntax
A two-table join using Non-ANSI Syntax with Table Alias
You Can Fully Qualify All Columns
A two-table 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 to Quiz – Which rows from both tables Won’t Return?
RIGHT OUTER JOIN Example and Results
Which Tables are the Left and Which are the Right?
Answer - Which Tables are the Left and Which are the 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
OUTER JOIN with Additional AND Clause Results
Quiz – Why is this Considered an INNER JOIN?
Evaluation Order For Outer Queries
The DREADED Product Join Results
The Horrifying Cartesian Product Join
The ANSI Cartesian Join will ERROR
Quiz – Do these Joins Return the Same Answer Set?
Answer – Do these Joins Return the Same Answer Set?
The Self Join with ANSI Syntax
Quiz – Will both queries bring back the same Answer Set?
Answer – Will both queries bring back the same Answer Set?
Quiz – Will both queries bring back the same Answer Set?
Answer – Will both queries bring back the same Answer Set?
How would you Join these two tables?
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the 3-Table Join?
Answer to Quiz – Can you Write the 3-Table Join?
Quiz – Can you Write the 3-Table Join to ANSI Syntax?
Answer – Can you Write the 3-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 5-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 Non-ANSI Syntax
Answer - Write a Five Table Join Using Non-ANSI Syntax
Quiz –Re-Write this putting the ON clauses at the END
Answer –Re-Write this putting the ON clauses at the END
The Nexus Query Chameleon Writes the SQL for Users
Date, Time, and Current_Timestamp Keywords
Dates are stored Internally as INTEGERS from a Formula
Displaying Dates for INTEGERDATE and ANSIDATE
Changing the DATEFORM in Client Utilities such as BTEQ
Date, Time, and Timestamp Recap
Add or Subtract Days from a date
A Summary of Math Operations on Dates
Find What Day of the week you were Born
Using the ADD_MONTHS Command to Add 1 Year
Using the ADD_MONTHS Command to Add 1 Year
Using the ADD_MONTHS Command to Add 5 Years
Another Version of the EXTRACT Command
EXTRACT or Math Can Accomplish the Same Thing
EXTRACT with DATE and TIME Literals
CAST the Date of January 1, 2011 and the Year 1800
EXTRACT of the Month on Aggregate Queries
Using the System Calendar In Its Simplest Form
How to really use the Sys_Calendar.Calendar
Storing TIME With TIME ZONE Internally
Storing Timestamp with TIME ZONE Internally
Storing Date, Time, and Timestamp with Zone Internally
Setting Time Zones at the System Level
Setting Time Zones at the User Level
Setting Time Zones at the Session Level
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
Troubleshooting The Basics of a Simple Interval
A Complex Time Interval Example using CAST
A Complex Time Interval Example using CAST
An OVERLAPS Example that Returns No Rows
The OVERLAPS Command using TIME
The OVERLAPS Command using a NULL Value
The Basics of the FORMAT Command
Quiz – How will the Date Appear after Formatting
Answer to Quiz – How will the Date Appear after Formatting
Quiz – How will the Date Appear after Formatting
Answer to Quiz – How will the Date Appear after Formatting
Formatting with MMM for the Abbreviated Month
Answer to Quiz – How will the Date Appear after Formatting
Formatting with MMMM for the Full Month Name
Formatting with MMMM for the Full Month
Formatting with DDD for the Julian Day
Formatting with DDD for the Julian Day
Formatting with EEE or EEEE for the Day of the Week
EEEE for the Abbreviated or Full Day of the Week
Placing Spaces inside your Formatting Commands with a B
Troubleshooting when Formatted Data Overflows
Troubleshooting when Formatted Data Overflows
Formatting with X or x Results
Formatting with $ and Commas Visual
Formatting with $ and Commas and 9
Formatting with $ and Commas and 9 with Zero Dollars
A Great Formatting Example for Day, Month and Year
A Trick to get SQL Assistant to Format Data
Using the CASESPECIFIC (CS) Command in Teradata Mode
Using NOT CASESPECIFIC (CS) in ANSI Mode
On-Line Analytical Processing (OLAP) or Ordered Analytics
Cumulative Sum (CSUM) Command and how OLAP Works
After the Sort the CSUM is Calculated
The OLAP Major Sort Key and the Minor Sort Key(s)
Troubleshooting OLAP – My Data isn’t coming back Correct
GROUP BY in Teradata OLAP Syntax Resets on the Group
CSUM the Number 1 to get a Sequential Number
A Single GROUP BY Resets each OLAP with Teradata Syntax
CSUM – Rows Unbounded Preceding Explained
CSUM – Making Sense of the Data
CSUM – Making Even More Sense of the Data
CSUM – The Major and Minor Sort Key(s)
The ANSI CSUM – Getting a Sequential Number
Troubleshooting The ANSI OLAP on a GROUP BY
Reset with a PARTITION BY Statement
PARTITION BY only Resets a Single OLAP not ALL of them
The Moving SUM (MSUM) and Moving Window
How the Moving Sum is Calculated
How the Sort works for Moving SUM (MSUM)
GROUP BY in the Moving SUM does a Reset
Quiz – Can you make the Advanced Calculation in your mind?
Answer to Quiz for the Advanced Calculation in your mind?
Quiz – Write that Teradata Moving SUM in ANSI Syntax
Both the Teradata Moving SUM and ANSI Version
ANSI Moving Window is Current Row and Preceding n Rows
How ANSI Moving SUM Handles the Sort
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Moving SUM every 3-rows Vs a Continuous Average
Partition By Resets an ANSI OLAP
The Moving Average (MAVG) and Moving Window
How the Moving Average is Calculated
How the Sort works for Moving Average (MAVG)
GROUP BY in the Moving Average does a Reset
Quiz – Can you make the Advanced Calculation in your mind?
Answer to Quiz for the Advanced Calculation in your mind?
Quiz – Write that Teradata Moving Average in ANSI Syntax
Both the Teradata Moving Average and ANSI Version
The Moving Window is Current Row and Preceding
How Moving Average Handles the Sort
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
Partition By Resets an ANSI OLAP
Moving Difference using ANSI Syntax
Moving Difference using ANSI Syntax with Partition By
Trouble Shooting the Moving Difference (MDIFF)
Using the RESET WHEN Option in Teradata V13
How Many Months Per Product_ID Has Revenue Increased?
How to get Rank to Sort in Ascending Order
Two ways to get Rank to Sort in Ascending Order
RANK Defaults to Ascending Order
Getting RANK to Sort in DESC Order
RANK() OVER and PARTITION BY with a QUALIFY
Quiz – How can you simplify the QUALIFY Statement?
Answer to Quiz –Can you simplify the QUALIFY Statement
The QUALIFY Statement without Ties
The QUALIFY Statement with Ties
The QUALIFY Statement with Ties Brings back Extra Rows
Mixing Sort Order for QUALIFY Statement
Quiz – What Caused the RANK to Reset?
Answer to Quiz – What Caused the RANK to Reset?
Answer to Quiz – Name those Sort Orders
PERCENT_RANK() OVER with 14 rows in Calculation
PERCENT_RANK() OVER with 21 rows in Calculation
Quiz – What Causes the Product_ID to Reset?
Answer to Quiz – What Cause the Product_ID to Reset?
COUNT OVER for a Sequential Number
Quiz – What caused the COUNT OVER to Reset?
Answer to Quiz – What caused the COUNT OVER to Reset?
MAX OVER with PARTITION BY Reset
Finding a Value of a Column in the Next Row with MIN
The CSUM For Each Product_Id For The First 3 Days
Quiz – How did the Row_Number Reset?
Quiz – How did the Row_Number Reset?
Row_Number With Qualify to get the Typical Rows Per Value
A Second Typical Rows Per Value Query on Sale_Date
Chapter 9 – The Quantile Function
The Quantile Function and Syntax
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 Tiebreaker
Using Tertiles (Partitions of Four)
There are Three types of Temporary Tables
Aliasing the Column Names in The Derived Table
Multiple Ways to Alias the Columns in a Derived Table
CREATING A Derived Table using the WITH Command
Most Derived Tables Are Used To Join To Other Tables
The Three Components of a Derived Table
Our Join Example With A Different Column Aliasing Style
Column Aliasing Can Default For Normal Columns
Our Join Example With The WITH Syntax
Answer to Quiz - Answer the Questions
Clever Tricks on Aliasing Columns in a Derived Table
A Derived Table lives only for the lifetime of a single query
An Example of Two Derived Tables in a Single Query
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 Definition
WITH RECURSIVE Derived Table Definition
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?
The HELP Volatile Table Command Shows your Volatiles
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 V14 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 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 and Compress
Chapter 11 - Sub-query Functions
An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
The Three Steps of How a Basic Subquery Works
The Final Answer Set from the Subquery
Quiz- Answer the Difficult Question
Answer to Quiz- Answer the Difficult Question
Should you use a Subquery of a Join?
Answer to Quiz- Write the Subquery
Quiz- Write the More Difficult Subquery
Answer to Quiz- Write the More Difficult 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
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 to Quiz- Write the Subquery with Two Parameters
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
Quiz – How many rows return on a NOT IN with a NULL?
Answer – How many rows return on a NOT IN with a NULL?
How to handle a NOT IN with Potential NULL Values
How a Correlated Exists matches up
The Correlated NOT Exists Answer Set
Quiz – How many rows come back from this NOT Exists?
Answer – How many rows come back from this NOT Exists?
Chapter 12 – Substring and Positioning Functions
The CHARACTERS Command Counts Characters
The CHARACTERS Command – Spaces can Count too
The CHARACTERS Command and Char(20) Data
Troubleshooting the CHARACTERS Command
TRIM for Troubleshooting the CHARACTERS Command
CHARACTERS and CHARACTER_LENGTH equivalent
The TRIM Command trims both Leading and Trailing Spaces
Trim Combined with the CHARACTERS Command
How to TRIM only the Trailing Spaces
A Visual of the TRIM Command Using Concatenation
Trim and Trailing is Case Sensitive
SUBSTRING and SUBSTR are equal, but use different syntax
How SUBSTRING Works with NO ENDING POSITION
Using SUBSTRING to move Backwards
How SUBSTRING Works with a Starting Position of -1
How SUBSTRING Works with an Ending Position of 0
An Example using SUBSTRING, TRIM and CHAR Together
The POSITION Command finds a Letters Position
The POSITION Command is brilliant with SUBSTRING
Quiz – Find that SUBSTRING Starting Position
Answer to Quiz – Find that SUBSTRING Starting Position
Using the SUBSTRING to Find the Second Word On
Quiz – Why Did only one Row Return
Answer to Quiz – Why Did only one Row Return
Imbedded Services Functions and Their Descriptions
Imbedded Services Functions Example With To_Date
Chapter 13 – Interrogating the Data
Quiz – What would the Answer be?
Answer to Quiz – What would the Answer be?
Quiz – Fill in the Blank Values in the Answer Set
Answer to Quiz – Fill in the Blank Values in the Answer Set
Quiz – Fill in the Answers for the NULLIF Command
Quiz – Fill in the Answers for the NULLIF Command
Answer to the ZEROIFNULL Question
The Basics of CAST (Convert And STore)
Some Great CAST (Convert And STore) Examples
Some Great CAST (Convert And STore) Examples
Some Great CAST (Convert And STore) Examples
A Teradata Extension – The Implied Cast
The Basics of the CASE Statements
The Basics of the CASE Statements
Valued Case Vs. A Searched Case
Answer - Valued Case Statement
Quiz - Searched Case Statement
Answer - Searched Case Statement
Quiz - When NO ELSE is present in CASE Statement
Answer - When NO ELSE is present in CASE Statement
When an ELSE is present in CASE Statement
Answer - When an ELSE is present in CASE Statement
When an Alias is NOT used in a CASE Statement
Answer - When an Alias is NOT used in a CASE Statement
Combining Searched Case and Valued Case
A Trick for getting a Horizontal Case
Creating a Simple View to Restrict Sensitive Columns
Creating a Simple View to Restrict Rows
Two Exceptions to the ORDER BY Rule inside a View
Views sometimes CREATED for Formatting or Row Security
Creating a View to Join Tables Together
Another Way to Alias Columns in a View CREATE
The Standard Way Most Aliasing is Done
What Happens When Both Aliasing Options Are Present
Resolving Aliasing Problems in a View CREATE
Answer to Resolving Aliasing Problems in a View CREATE
Altering A Table After a View has been Created
A View that Errors After An ALTER
Updating Data in a Table through a View
Maintenance Restrictions on a Table through a View
CREATING and EXECUTING a Simple Macro
Multiple SQL Statements 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
More Troubleshooting of a Macro
Chapter 16 – Set Operators Functions
Answer - Testing Your Knowledge
Answer - Testing Your Knowledge
An Equal Amount of Columns in both SELECT List
Columns in the SELECT list should be from the same Domain
The Top Query handles all Aliases
The Bottom Query does the ORDER BY (a Number)
Great Trick: Place your Set Operator in a Derived Table
A Great Example of how EXCEPT works
USING Multiple SET Operators in a Single Request
Changing the Order of Precedence with Parentheses
Using UNION ALL for speed in Merging Data Sets
Using UNION to be same as GROUP BY GROUPING SETS
Chapter 17 - Table Create and Data Types
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 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
Creating a PPI Table with CASE_N
The No Case and Unknown Partition Options
Partitioning of Older and Newer Data Separately
Almost All PPI Tables have a Non-Unique Primary Index
PPI Table With a Unique Primary Index (UPI)
Another Clever Trick for PPI Tables
Character Based PPI for RANGE_N
Character-Based PPI for CASE_N
Dates and Character-Based Multi-Level PPI
TIMESTAMP Partitioning That is Deterministic
Altering a PPI Table the Hard Way
Altering a PPI Table the Easy Way With TO CURRENT
Altering a PPI Table and Saving the Deleted Data
Using the PARTITION Keyword in your SQL
Creating a Columnar Table With Multi-Column Containers
Columnar Row Hybrid CREATE Statement
CREATE Statement for both Row and Column Partition
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
Table Customization of the Data Block Size
Table Customization with FREESPACE Percent
You Can Select From a Queue Table
Exploring the Real Purpose of a Queue Table
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
Creating Partitioned Primary Index (PPI) Volatile Tables
A Volatile Table That Only Populates Some of the Rows
A Volatile Table With Some of the Columns
A Volatile Table With No Data and Zeroed Statistics
A Multiset Volatile Table With Statistics Example
Major Data Types and the number of Bytes they take up
Chapter 18 - Data Manipulation Language (DML)
INSERT/SELECT Example using All Columns (*)
INSERT/SELECT Example with Less Columns
INSERT/SELECT to Build a Data Mart
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
Subquery UPDATE Command Syntax
Example of Subquery UPDATE Command
Example of an UPDATE Join Command
The DELETE Command Basic Syntax
Two DELETE Examples to DELETE ALL Rows in a Table
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
MERGE INTO Example that Matches
MERGE INTO Example that does NOT Match
Formatting for Internationalizations
Chapter 19 – Stored Procedure Functions
How you CALL a Stored Procedure
Label all BEGIN and END statements except the first ones
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
Example of two Stored Procedures with different techniques
Using Loops in Stored Procedures
Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT
Stored Procedure Workshop Answer
Stored Procedure Workshop Alternative Answer
Chapter 20 – Trigger Functions
Checking To See If the Trigger Works
FOR EACH STATEMENT vs. FOR EACH ROW
Checking To See If Our New Trigger Worked
Using ORDER when Similar Triggers Exist
Chapter 21 – The SAMPLE Function
The SAMPLE Function and Syntax
A SAMPLE Example that asks for Multiple Samples
A SAMPLE Example with the SAMPLEID
A SAMPLE Example WITH REPLACEMENT
A SAMPLE Example with Four 10% Samples
A SAMPLE with Conditional Logic
Aggregates and A SAMPLE using a Derived Table
Using Random to SELECT a Percentage of Rows
Chapter 22 – Statistical Aggregate Function
Another CORR Example so you can Compare
Another COVAR_POP Example so you can Compare
Another REGR_INTERCEPT Example so you can Compare
Another REGR_SLOPE Example so you can Compare
No Having Clause Vs Use of HAVING
Determining the Release of your Teradata System
SHOW Table command for Table DDL
SHOW View command for View Create Statement
SHOW Macro command for Macro Create Statement
SHOW Trigger command for Trigger Create Statement
Chapter 24 – Teradata (BTET) vs. ANSI Mode
Teradata Mode (BTET) vs. ANSI Mode
Both Modes Can Have Control Over The Commit Time
Multi-statement Transactions Using BTEQ Semi-colons
What Happens If A Transaction Fails?
Chapter 25 – BTEQ – Batch Teradata Query
BTEQ – Batch TEradata Query Tool
How to Logon to BTEQ in Interactive Mode
Running Queries in BTEQ in Interactive Mode
BTEQ Commands Vs BTEQ SQL Statements
WITH Command for a Grand Total
WITH and WITH BY Together for Subtotals and Grand Totals
How to Logon to BTEQ in a SCRIPT
Multi-statement Transactions Using BTEQ Semi-colons
The Four Major Locks of Teradata
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, Table or Database
All Views should consider the Locking for Access Statement
What is a Dead Lock or a Deadly Embrace?
Pseudo Tables are designed to minimize Dead Locks
Pseudo Tables are referenced in the Explain Plan
Incompatible Locks Wait on each Other
The Automatic Locking for Access Button inside Nexus
Viewpoint Lock Viewer Lets You Configure Your View
What is a Host Utility (HUT) Lock?
Chapter 27 – Lessons with Tera-Tom Video Guide
Video 1 - Teradata Basics - The Architecture
Video 2 - Hashing the Primary Index
Video 3 - The Cold Hard Teradata Facts
Video 4 -Inside the Amps’ Disc
Video 5 - PPI (Partitioned Primary Index Tables)
Video 8 - How Teradata Joins Tables Together
Video 9- Protection Features (1/3)
Video 10- Protection Features (2/3)
Video 11- Protection Features (3/3)
Video 12- Collect Statistics (1 of 2)
Video 13- Collect Statistics (2 of 2)
Teradata SQL Video 1 - The Basics of SQL
Teradata SQL Video 2 – Building Your SQL Knowledge
Teradata SQL Video 3 - Aggregates
Teradata SQL Video 6 – Temporary Tables (Derived)
Teradata SQL Video 7 – Volatile and Global Temporary Tables
Teradata SQL Video 8 – Ordered Analytic Functions
Teradata SQL Video 9 – Advanced Ordered Analytics
Teradata SQL Video 10 - Subqueries
Teradata SQL Video 11 – Substring and Positioning
Teradata SQL Video 12 – Data Interrogation
Teradata SQL Video 14 - Macros
Teradata SQL Video 15 – Stored Procedures