Teradata SQL Training

Chapter 1 - Basic SQL Functions    

• Introduction    
• SELECT * (All Columns) in a Table    
• SELECT Specific Columns in a Table    
• Using the Best Form for Writing SQL    
• Commas in the Front or in the Back?    
• 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)    
• Major Sort vs. Minor Sorts    
• Multiple Sort Keys using Names vs. Numbers    
• Sorts are Alphabetical, NOT Logical    
• Using A CASE Statement to Sort Logically    
• How to ALIAS a Column Name    
• 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    
• Comments for Multi-Lines as Double Dashes per Line    
• A Great Technique for Comments to Look for SQL Errors    

 

Chapter 2 - The WHERE Clause    

• 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    
• 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 (>=)    
• AND in the WHERE Clause    
• Troubleshooting AND    
• OR in the WHERE Clause    
• Troubleshooting OR    
• 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 Results    
• Using a NOT IN List    
• 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    
• BETWEEN is Inclusive    
• BETWEEN Works for Character Data    
• LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’    
• LIKE command Underscore is Wildcard for one Character    
• LIKE ALL means ALL conditions must be Met    
• LIKE ANY means ANY of the Conditions can be Met    
• IN ANSI Transaction Mode Case Matters    
• In Teradata Transaction Mode Case Doesn’t Matter    
• LIKE Command Works Differently on Char Vs. Varchar    
• Troubleshooting LIKE Command on Character Data    
• Introducing the TRIM Command    
• Quiz – Which Data is Left Justified and Which is Right?    
• Numbers are Right Justified and Character Data is Left    
• Answer – Which Data is Left Justified and Which 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    
• TRIM Eliminates Leading and Trailing Spaces    
• Escape Character in the LIKE Command changes Wildcards    
• Escape Characters Turn off Wildcards in the LIKE Command    
• Quiz – Turn off that Wildcard    
• ANSWER – To Find that Wildcard    

 

Chapter 3 - Distinct Vs. Group By    

• The Distinct Command    
• Distinct vs. GROUP BY    
• Rules of Thumb for DISTINCT vs. GROUP BY    
• GROUP BY Vs. DISTINCT – Good Advice    
• Quiz – How many rows come back from the Distinct?    
• Answer – How many rows come back from the Distinct?    

 

Chapter 4 - The TOP Command    

• TOP Command    
• TOP Command is brilliant when ORDER BY is used!    
• The TOP Command WITH TIES    
• How the TOP Command WITH TIES Decides    
• The TOP Command will NOT work with Certain Commands    

 

Chapter 5 – Review    

• Testing Your Knowledge 1    
• Testing Your Knowledge 2    
• Testing Your Knowledge 3    
• Testing Your Knowledge 4    
• Testing Your Knowledge 5    
• Testing Your Knowledge 6    
• Testing Your Knowledge 7    

 

Chapter 6 - HELP and SHOW    

• Determining the Release of your Teradata System    
• Basic HELP Commands    
• Other HELP Commands    
• HELP DATABASE    
• HELP USER    
• HELP TABLE    
• Adding a Comment to a Table    
• Adding a Comment to a View    
• SELECT SESSION    
• USER Information Functions    
• HELP SESSION    
• HELP SQL    
• A HELP SQL Example    
• Show Commands    
• 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 7 - Aggregation Function    

• Quiz – You calculate the Answer Set in your own Mind    
• Answer – You calculate the Answer Set in your own Mind    
• The 3 Rules of Aggregation    
• There are Five Aggregates    
• Quiz – How many rows come back?    
• Troubleshooting Aggregates    
• 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    
• 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    
• GROUP BY Grouping Sets    
• GROUP BY Rollup    
• GROUP BY Rollup Result Set    
• GROUP BY Cube    
• GROUP BY CUBE Result Set    
• Use the Nexus for all Groupings    
• Testing Your Knowledge – Basic Aggregation    
• Testing Your Knowledge – Multiple Aggregates    
• Testing Your Knowledge- Group By    
• Testing Your Knowledge – Using a Where Clause    
• Testing Your Knowledge- Using Having    
• Final Answer to Test Your Knowledge on Aggregates    

 

Chapter 8 - Join Functions    

• A two-table join using Non-ANSI Syntax    
• A two-table join using Non-ANSI Syntax with Table Alias    
• Aliases and Fully Qualifying 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?    
• Quiz – Which rows from both tables Won’t Return?    
• Answer to Quiz – Which rows from both tables Won’t Return?    
• LEFT OUTER JOIN    
• LEFT OUTER JOIN Brings Back All Rows in the Left Table    
• RIGHT OUTER JOIN    
• RIGHT OUTER JOIN Brings Back All Rows in the RIGHT Table    
• FULL OUTER JOIN    
• FULL OUTER JOIN Brings Back All Rows in All Tables    
• 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    
• Results from OUTER JOIN with Additional AND Clause    
• Quiz – Why is this considered an INNER JOIN?    
• The DREADED Product Join    
• Result Set of the DREADED Product Join    
• 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 CROSS JOIN    
• The CROSS JOIN Answer Set    
• The Self Join    
• 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?    
• How would you Join these two tables? You Can’t Yet!    
• 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 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.    

 

Chapter 9 - Date Functions    

• Date, Time, and Current_Timestamp Keywords    
• Dates are stored internally as INTEGERS from a Formula    
• Displaying Dates for INTEGERDATE and ANSIDATE    
• DATEFORM    
• Changing the DATEFORM in Client Utilities such as BTEQ    
• Date, Time, and Timestamp Recap    
• Timestamp Differences    
• Finding the Number of Hours between Timestamps    
• Troubleshooting Timestamp    
• Add or Subtract Days from a date    
• A Summary of Math Operations on Dates    
• Using a Math Operation to find your Age in Years    
• Find What Day of the week you were Born    
• The ADD_MONTHS Command    
• Using the ADD_MONTHS Command to Add 1 Year    
• Using the ADD_MONTHS Command to Add 5 Years    
• The EXTRACT Command    
• EXTRACT from DATES and TIME    
• CURRENT_DATE and EXTRACT or Current_Date and Math    
• CAST the Date of January 1, 2011 and the Year 1800    
• The System Calendar    
• Using the System Calendar in Its Simplest Form    
• How to really use the Sys_Calendar.Calendar    
• Storing Dates Internally    
• 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    
• Seeing your Time Zone    
• Creating a Sample Table for Time Zone Examples    
• Inserting Rows in the Sample Table for Time Zone Examples    
• Selecting the Data from our Time Zone Table    
• Normalizing our Time Zone Table with a CAST    
• Intervals for Date, Time and Timestamp    
• Interval Data Types and the Bytes to Store Them    
• The Basics of a Simple Interval    
• 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 10 - Format Functions    

• The FORMAT Command    
• 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    
• Formatting Spaces with B or b    
• Formatting with 9    
• Formatting with 9 Results    
• Troubleshooting when Formatted Data Overflows    
• Troubleshooting when Formatted Data Overflows    
• Formatting with X or x    
• Formatting with Z    
• Formatting with Z Visual    
• Formatting with 9    
• Formatting with 9 Visual    
• Formatting with $    
• Formatting with $ Visual    
• Formatting with $ and Commas    
• Formatting with $ and Commas Visual    
• Formatting with $ and Commas and 9    
• Formatting with $ and Commas and 9 with Zero Dollars    
• A Great Formatting Example    
• 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    
• Using the LOWER Command    
• Using the UPPER Command    

 

Chapter 11 - OLAP Functions    

• On-Line Analytical Processing (OLAP) or Ordered Analytics    
• Cumulative Sum (CSUM) Command and how OLAP Works    
• OLAP Commands always Sort (ORDER BY) in the Command    
• Calculate the Cumulative Sum (CSUM) after Sorting the Data    
• The OLAP Major Sort Key    
• 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    
• A Better Choice – The ANSI Version of CSUM    
• The ANSI Version of CSUM – The Sort Explained    
• The ANSI CSUM – Rows Unbounded Preceding Explained    
• The ANSI CSUM – Making Sense of the Data    
• The ANSI CSUM – Making Even More Sense of the Data    
• The ANSI CSUM – The Major and Minor Sort Key(s)    
• The ANSI CSUM – Getting a Sequential Number    
• Troubleshooting the ANSI OLAP on a GROUP BY    
• The ANSI OLAP – 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 Average in ANSI Syntax    
• Both the Teradata Moving SUM and ANSI Version    
• The ANSI Moving Window is Current Row and Preceding    
• How ANSI Moving Average 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 ANSI Moving Window is Current Row and Preceding    
• How ANSI 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    
• The Moving Difference (MDIFF)    
• Moving Difference (MDIFF) Visual    
• 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?    
• The RANK Command    
• How to get Rank to Sort in Ascending Order    
• Two ways to get Rank to Sort in Ascending Order    
• RANK using ANSI Syntax Defaults to Ascending Order    
• Getting RANK using ANSI Syntax to Sort in DESC Order    
• RANK () OVER and PARTITION BY    
• RANK () OVER and QUALIFY    
• RANK () OVER and PARTITION BY with a QUALIFY    
• QUALIFY and WHERE    
• 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?    
• Quiz – Name those Sort Orders    
• Answer to Quiz – Name those Sort Orders    
• PERCENT_RANK () OVER    
• PERCENT_RANK () OVER with 14 rows in Calculation    
• PERCENT_RANK () OVER with 21 rows in Calculation    
• Quiz – What Cause the Product_ID to Reset    
• Answer to Quiz – What Causes the Product_ID to Reset    
• Answer to Quiz – What Causes the Product_ID to Reset    
• COUNT OVER for a Sequential Number    
• Troubleshooting COUNT OVER    
• Quiz – What caused the COUNT OVER to Reset?    
• Answer to Quiz – What caused the COUNT OVER to Reset?    
• The MAX OVER Command    
• MAX OVER with PARTITION BY Reset    
• Troubleshooting MAX OVER    
• The MIN OVER Command    
• Troubleshooting MIN OVER    
• Finding a Value of a Column in the Next Row with MIN    
• Finding a Value of a Date in the Next Row with MIN    
• Finding Gaps between Dates    
• The CSUM for Each Product_ID for the First 3 Days    
• Quiz – Fill in the Blank    
• Answer to Quiz – Fill in the Blank    
• The Row_Number Command    
• 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    
• Testing Your Knowledge    
• Testing Your Knowledge    
• Testing Your Knowledge    
• Testing Your Knowledge    
• Testing Your Knowledge    
• Testing Your Knowledge    

 

Chapter 12 - The Quantile Function    

• 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 Tiebreaker    
• Using Tertiles (Partitions of Four)    
• How Quantile Works    

 

Chapter 13 - Temporary Tables    

• There are three types of Temporary Tables    
• CREATING A Derived Table    
• Naming the Derived Table    
• Aliasing the Column Names in the Derived Table    
• Most Derived Tables Are Used To Join To Other Tables    
• Multiple Ways to Alias the Columns in a Derived Table    
• Our Join Example with a Different Column Aliasing Style    
• Column Aliasing Can Default for Normal Columns    
• CREATING a Derived Table using the WITH Command    
• Our Join Example With the WITH Syntax    
• The Same Derived Query shown Three Different Ways    
• Quiz - Answer the Questions    
• 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    
• Defining the WITH Recursive Derived Table    
• Looping Through the WITH Recursive Derived Table    
• Looping Through the WITH Recursive Derived Table    
• Looping Through the WITH Recursive Derived Table    
• Looping Through the WITH Recursive Derived Table    
• Looping Through the WITH Recursive Derived Table    
• 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?    
• 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    
• Four Examples of Creating a Volatile Table Quickly    
• Four Advanced Examples of Creating a Volatile Table Quickly    
• Creating Partitioned Primary Index (PPI) Volatile Tables    
• Using a Volatile Table to Get Rid of Duplicate Rows    
• Using a Simple Global Temporary Table    
• Two Brilliant Techniques for Global Temporary Tables    
• The Joining of Two Tables Using a Global Temporary Table    
• CREATING A Global Temporary Table    

 

Chapter 14 - Sub-query Functions    

• 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    
• 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?    
• 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 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    
• The Bottom Query runs last in a Correlated Subquery    
• Quiz- Who is coming back in the Final Answer Set?    
• Answer- Who is coming back in the Final Answer Set?    
• 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    
• Correlated Subquery that Finds Duplicates    
• Quiz- Write the NOT 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?    
• How to handle a NOT IN with Potential NULL Values    
• IN is equivalent to =ANY    
• Using a Correlated Exists    
• How a Correlated Exists matches up    
• The Correlated NOT Exists    
• 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 15 - Substrings 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    
• OCTET_LENGTH    
• The TRIM Command trims both Leading and Trailing Spaces    
• Trim and Trailing is Case Sensitive    
• Trim and Trailing works if Case right    
• Trim Combined with the CHARACTERS Command    
• How to TRIM only the Trailing Spaces    
• How to TRIM Trailing Letters    
• How to TRIM Trailing Letters and use CHARACTER_Length    
• The SUBSTRING Command    
• 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    
• SUBSTRING and SUBSTR are equal, but use differe,nt syntax    
• The POSITION Command finds a Letters Position    
• The POSITION Command is brilliant with SUBSTRING    
• Quiz – Name that SUBSTRING Starting and For Length    
• The POSITION Command is brilliant with SUBSTRING    
• Quiz – Name that SUBSTRING Starting and For Length    
• Answer to Quiz – Name that Starting and For Length    
• Answer to Quiz – Name that Starting and For Length    
• 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    
• Concatenation    
• Concatenation and SUBSTRING    
• Four Concatenations Together    
• Troubleshooting Concatenation    

 

Chapter 16 - Interrogating the Data    

• Quiz – What would the Answer be?    
• Answer to Quiz – What would the Answer be?    
• The NULLIFZERO Command    
• Quiz – Fill in the Blank Values in the Answer Set    
• Answer to 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    
• The ZEROIFNULL Command    
• Answer to the ZEROIFNULL Question    
• The COALESCE Command    
• The COALESCE Answer Set    
• The Coalesce Quiz    
• Answers to the Coalesce Quiz    
• 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 Statement shown visually    
• Valued Case vs. Searched Case    
• Quiz - Valued Case Statement    
• 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    
• When NO ELSE is present in CASE Statement    
• When an Alias is NOT used in a CASE Statement    
• When an Alias is NOT used in a CASE Statement    
• When NO ELSE is present in CASE Statement    
• Combining Searched Case and Valued Case    
• A Trick for getting a Horizontal Case    
• Nested Case    
• Put a CASE in the ORDER BY    

 

Chapter 17 - View Functions    

• Creating a Simple View    
• Basic Rules for Views    
• How to Modify a View    
• Exceptions to the ORDER BY Rule inside a View    
• How to Get HELP with a View    
• Views sometimes CREATED for Formatting or Row Security    
• Another Way to Alias Columns in a View CREATE    
• Resolving Aliasing Problems in a View CREATE    
• Resolving Aliasing Problems in a View CREATE    
• Resolving Aliasing Problems in a View CREATE    
• CREATING Views for Complex SQL such as Joins    
• WHY certain columns need Aliasing in a View    
• Aggregates on View Aggregates    
• Locking Row for Access    
• Creating Views for Temporal Tables    
• Altering a Table    
• Altering a Table after a View has been created    
• A View that errors After an ALTER    
• Troubleshooting a View    
• Updating Data in a Table through a View    
• Maintenance Restrictions on a Table through a View    

 

Chapter 18 - Macro Functions

• The 14 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    
• Troubleshooting a Macro with INPUT Parameters    
• An UPDATE Macro with Two Input Parameters    
• Executing a Macro with Named (Not Positional) Parameters    
• Troubleshooting a Macro    

Chapter 19 - Set Operators Functions    

• Rules of Set Operators    
• INTERSECT Explained Logically    
• INTERSECT Explained Logically    
• UNION Explained Logically    
• UNION Explained Logically    
• UNION ALL Explained Logically    
• UNION Explained Logically    
• EXCEPT Explained Logically    
• EXCEPT Explained Logically    
• Minus Explained Logically    
• Minus Explained Logically    
• Testing Your Knowledge    
• 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    
• UNION vs. UNION ALL    
• UNION vs. UNION ALL Example    
• Using UNION ALL and Literals    
• 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    
• Using UNION to be same as GROUP BY ROLLUP    
• Using UNION to be the same as GROUP BY Cube    
• Using UNION to be same as GROUP BY Cube    
• Using UNION to be same as GROUP BY Cube    

 

Chapter 20 – Creating Tables, Secondary Indexes, and Join Indexes 

• Creating a Table with a Unique Primary Index    
• Creating a Table with a Non-Unique Primary Index    
• Creating a Table without entering a Primary Index    
• Creating a Table with NO Primary Index    
• Creating a Set Table    
• Creating a Multiset Table    
• Creating a Set Table with a Unique Primary Index    
• Creating a Set Table with a Unique Secondary Index    
• Creating a Table with an UPI and USI    
• Creating a Table with a Multicolumn Primary Index    
• Creating a Unique Secondary Index (USI) after a table is created    
• Creating a Non-Unique Secondary Index (NUSI) after a table is created    
• Creating a Value-Ordered NUSI    
• Data Types    
• Data Types Continued    
• Data Types Continued    
• Major Data Types and the number of Bytes they take up    
• Making an exact copy a Table    
• Making a NOT-So-Exact Copy a Table    
• Copying a Table    
• Troubleshooting Copying and Changing the Primary Index    
• Copying only specific columns of a table    
• Copying a Table and Keeping the Statistics    
• Copying a Table with Statistics    
• Copying a table Structure with NO Data but Statistics    
• 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 Keyword Alone    
• Why Use Journaling?    
• Why Use Journaling?    
• Creating a Table with Customization of the Data Block Size    
• Creating a Table with Customization with FREESPACE Percent    
• Creating a QUEUE Table    
• Example of how a Queue Table Works    
• Example of how a Queue Table Works    
• Creating a Columnar Table    
• Creating a Columnar Table with Multi-Column Containers    
• Creating a Columnar Table with a Row Hybrid    
• Creating a Columnar Table with both Row and Column Partitions    
• How to Load into a Columnar Table    
• Creating a Columnar Table with NO AUTO COMPRESS    
• CREATING a Bi-Temporal Table    
• Explaining Bi-Temporal PERIOD Data Types    
• Creating a PPI Table with Simple Partitioning    
• Creating a PPI Table with RANGE_N Partitioning per Day    
• Creating a PPI Table with RANGE_N Partitioning per Month    
• A Visual of One Year of Data with Range_N per Month    
• Creating a PPI Table with RANGE_N Partitioning per Week    
• A Clever Range_N Option    
• Creating a PPI Table with CASE_N    
• A Visual of Case_N Partitioning    
• Number of PPI Partitions Allowed    
• NO CASE and UNKNOWN Partitions Together    
• Combining Older Data and Newer Data in PPI    
• A Visual for Combining Older Data and Newer Data in PPI    
• Multi-Level Partitioning Combining Range_N and Case_N    
• A Visual of Multi-Level Partitioning    
• NON-Unique Primary Indexes (NUPI) in PPI    
• PPI Table with a Unique Primary Index (UPI)    
• Tricks for Non-Unique Primary Indexes (NUPI)    
• A Brilliant Technique for a Unique Secondary Index    
• A Brilliant Technique for a Non-Unique Secondary Index    
• Character Based PPI for RANGE_N    
• Character-Based PPI for CASE_N    
• Dates and Character-Based Multi-Level PPI    
• TIMESTAMP Partitioning    
• Using CURRENT_DATE to define a PPI    
• ALTER to CURRENT_DATE the next year    
• 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    
• SQL – User Defined Functions (UDF)    
• User Defined Functions    
• 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    
• Creating a Single-Table Join Index    
• Compressed Single-Table Join Index    
• Aggregate Join Index    
• Sparse Join Index    
• A Global Multi-Table Join Index   
• Creating a Hash Index    

 

Chapter 21 - Data Manipulation Language (DML) 

• 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 Example with Less 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    
• 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    
• 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    

 

Chapter 22 - Stored Procedure Functions

• 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    
• Example of two Stored Procedures with different techniques    
• Using Loops in Stored Procedures    
• You can Name the First Begin and End if you choose    
• Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT    
• Stored Procedure Basic Assignment    
• Answer - Stored Procedure Basic Assignment    
• Stored Procedure Advanced Assignment    
• Answer - Stored Advanced Assignment    

 

Chapter 23 - Trigger Functions

• The Fundamentals of Triggers    
• CREATING A Trigger    
• FOR EACH STATEMENT vs. FOR EACH ROW    
• Using ORDER when Similar Triggers Exist    

 

Chapter 24 - Math Functions 

• What is the Order of Precedents?    
• What is the Answer to this Math Question?    
• What is the Answer to this Math Question?    
• What is the Answer to this Math Question?    

 

Chapter 25 - Sample  

• The SAMPLE Function and Syntax    
• SAMPLE Function Examples    
• 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 Randomized SAMPLE    
• A SAMPLE with Conditional Logic    
• Aggregates and A SAMPLE using a Derived Table    
• Random Number Generator    
• Using Random to SELECT a Percentage of Rows    
• Using Random and Aggregations    

 

Chapter 26 - Statistical Aggregate Functions 

• The Stats Table    
• The KURTOSIS Function    
• A Kurtosis Example    
• The SKEW Function    
• A SKEW Example    
• The STDDEV_POP Function    
• A STDDEV_POP Example    
• The STDDEV_SAMP Function    
• A STDDEV_SAMP Example    
• The VAR_POP Function    
• A VAR_POP Example    
• The VAR_SAMP Function    
• A VAR_SAMP Example    
• 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 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    
• Using GROUP BY    
• No Having Clause vs. Use of HAVING    

 

Chapter 27 - Explain 

• EXPLAIN Keywords    
• EXPLAIN Keywords Continued    
• Explain Example – Full Table Scan    
• Explain Example – Unique Primary Index (UPI)    
• Explain Example – Non-Unique Primary Index (NUPI)    
• Explain Example – Unique Secondary Index (USI)    
• Explain Example – Redistributed to All-AMPs    
• Explain Example – Row Hash Match Scan    
• Explain Example – Duplicated on All-AMPs    
• Explain Example –Low Confidence    
• Explain Example – High Confidence    
• Explain Example – Product Join    
• Explain Example – BMSMS    
• Explain Terminology for Partitioned Primary Index Tables    
• Explain Example – From a Single Partition    
• Explain Example – From N Partitions    
• Explain Example – Partitions and Current_Date    

 

Chapter 28 - 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 V14 Way to Collect Statistics    
• Where Does Teradata Keep the Collected Statistics?    
• The Official Syntaxes 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    
• Summary of the PE 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?    
• COLLECT STATISTICS Directly From another Table    
• How to Copy a Table with NO Data and the Statistics?    
• When to COLLECT STATISTICS Using only a SAMPLE    
• Examples of COLLECT STATISTICS Using only a SAMPLE    
• Examples of COLLECT STATISTICS for V14    
• How to Collect Statistics on a PPI Table on the Partition    
• Teradata V12 and V13 Statistics Enhancements    
• Teradata V14 Statistics Enhancements    
• Teradata V14 Summary Statistics    
• Teradata V14 MaxValueLength    
• Teradata V14 MaxIntervals    
• Teradata V14 Sample N Percent    
• Teradata Statistics Wizard    

 

Chapter 29 - Hashing Functions   

• Hashing Functions on Teradata    
• The HASHROW Function    
• The HASHROW Function in a real-world Example    
• The HASHBUCKET Function    
• The HASHBUCKET Function in a real-world Example    
• The HASHAMP Function    
• The HASHAMP Function in a real-world Example    
• A Great HASHAMP Function for Large Tables    
• The HASHBAKAMP Function    
• A Real-World HASBAKHAMP Function Example    
• A Great way to see distribution for Primary and Fallback rows    

 

Chapter 30 - 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 BY Command for Subtotals    
• WITH Command for a Grand Total    
• WITH and WITH BY Together for Subtotals and Grand Totals    
• How to Logon to BTEQ in a SCRIPT    
• Running Queries in BTEQ through a Batch Script    
• Running a BTEQ Batch Script through the Command Prompt    
• Running a BTEQ Batch Script through the Run Command    
• Using Nexus to Build Your BTEQ Scripts    
• Using Nexus to Build Your BTEQ Scripts    
• Using BTEQ Scripts to IMPORT Data    
• What Keywords Mean in a BTEQ Script    
• Creating a BTEQ IMPORT for a Comma Separated Value File    
• Four Great Examples/Ways to Run a Teradata BTEQ Script    
• BTEQ Export – Four types of Export Variations    
• Creating a BTEQ Export Script in Record Mode    
• Creating a BTEQ Export Script in Report Mode    
• The Appearance of Record Mode Vs Report Mode Data    
• Using Report Mode to Create a Comma Separated Report    
• Creating a BTEQ IMPORT for a Comma Separated Value File    
• Using Multiple Sessions in BTEQ    
• BTEQ Fast Path Inserts    
• BTEQ Can Use Conditional Logic    
• Using a BTEQ Export and Setting a Limit In a UNIX System    

 

Chapter 31 – Top SQL Commands Cheat Sheet   

• SELECT All Columns from a Table and Sort    
• Select Specific Columns and Limiting the Rows    
• Changing your Default Database    
• Keywords that describe you    
• Select TOP Rows in a Rank Order    
• A Sample number of rows    
• Getting a Sample Percentage of rows    
• Find Information about a Database    
• Find information about a Table    
• Using Aggregates    
• Performing a Join    
• Performing a Join using ANSI Syntax    
• Using Date, Time and Timestamp    
• Using Date Functions    
• Using the System Calendar    
• Using the System Calendar in a Query    
• Formatting Data    
• Using Rank    
• Using a Derived Table    
• Using a Subquery    
• Correlated Subquery    
• Using Substring    
• Basic CASE Statement    
• Advanced CASE Statement    
• Using an Access Lock in your SQL    
• Collect Statistics    
• CREATING a Volatile Table with a Primary Index    
• CREATING a Volatile Table that is Partitioned (PPI)    
• CREATING a Volatile Table that is deleted after the Query    
• Finding the Typical Rows per Value for specific column    
• Finding out how much Space you have    
• How much Space you have Per AMP    
• Finding your Space    
• Finding Space Skew in Tables in a Database    
• Finding the Number of rows per AMP for a Column    
• Finding Account Information    
• Ordered Analytics