You are previewing V14 Certification: Teradata SQL.
O'Reilly logo
V14 Certification:  Teradata SQL

Book Description

You are going to love this book because your confidence and knowledge will soar. V14 Certification: Teradata SQL has been designed to help you pass the test, but it is so much more. It is an incredible reference guide, learning tool, and the only book you will need for learning, writing Teradata SQL and passing the certification test.

Table of Contents

  1. Cover
  2. The Tera-Tom Video Series
  3. The Tera-Tom Genius Series
  4. Tera-Tom- Author of over 50 Books
  5. The Best Query Tool Works on all Systems
  6. Trademarks and Copyrights
  7. About Tom Coffing
  8. About Leona Coffing
  9. Contents
  10. Chapter 1 – The Basics of SQL
    1. Passing Your Teradata Certification Tests
    2. Introduction
    3. SELECT * (All Columns) in a Table
    4. SELECT Specific Columns in a Table
    5. Commas in the Front or Back?
    6. Place your Commas in front for better Debugging Capabilities
    7. Sort the Data with the ORDER BY Keyword
    8. ORDER BY Defaults to Ascending
    9. Use the Name or the Number in your ORDER BY Statement
    10. Two Examples of ORDER BY using Different Techniques
    11. Changing the ORDER BY to Descending Order
    12. NULL Values sort First in Ascending Mode (Default)
    13. NULL Values sort Last in Descending Mode (DESC)
    14. Major Sort vs. Minor Sorts
    15. Multiple Sort Keys using Names vs. Numbers
    16. Sorts are Alphabetical, NOT Logical
    17. Using A CASE Statement to Sort Logically
    18. How to ALIAS a Column Name
    19. A Missing Comma can by Mistake become an Alias
    20. The Title Command and Literal Data
    21. Comments using Double Dashes are Single Line Comments
    22. Comments for Multi-Lines
    23. Comments for Multi-Lines As Double Dashes Per Line
    24. A Great Technique for Comments to Look for SQL Errors
  11. Chapter 2 – The Where Clause
    1. The WHERE Clause limits Returning Rows
    2. Using a Column ALIAS throughout the SQL
    3. Double Quoted Aliases are for Reserved Words and Spaces
    4. Character Data needs Single Quotes in the WHERE Clause
    5. Character Data needs Single Quotes, but Numbers Don’t
    6. Comparisons Against a Null Value
    7. NULL means UNKNOWN DATA so Equal (=) won’t Work
    8. Use IS NULL or IS NOT NULL when dealing with NULLs
    9. NULL is UNKNOWN DATA so NOT Equal won’t Work
    10. Use IS NULL or IS NOT NULL when dealing with NULLs
    11. Using Greater Than Or Equal To (>=)
    12. Using GE as Greater Than or Equal To (>=)
    13. AND in the WHERE Clause
    14. Troubleshooting AND
    15. OR in the WHERE Clause
    16. Troubleshooting Or
    17. Troubleshooting Character Data
    18. Using Different Columns in an AND Statement
    19. Quiz – How many rows will return?
    20. Answer to Quiz – How many rows will return?
    21. What is the Order of Precedence?
    22. Using Parentheses to change the Order of Precedence
    23. Using an IN List in place of OR
    24. The IN List is an Excellent Technique
    25. IN List vs. OR brings the same Results
    26. The IN List Can Use Character Data
    27. Using a NOT IN List
    28. Null Values in a NOT IN List Bring Back No Rows
    29. A Technique for Handling Nulls with a NOT IN List
    30. An IN List with the Keyword ANY
    31. A NOT IN List with the Keywords NOT = ALL
    32. A NOT IN List with the Keywords NOT = ALL and NULL
    33. BETWEEN is Inclusive
    34. NOT BETWEEN is Also Inclusive
    35. LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
    36. LIKE command Underscore is Wildcard for one Character
    37. LIKE Command Works Differently on Char Vs Varchar
    38. Troubleshooting LIKE Command on Character Data
    39. Introducing the TRIM Command
    40. Quiz – What Data is Left Justified and What is Right?
    41. Numbers are Right Justified and Character Data is Left
    42. Answer – What Data is Left Justified and What is Right?
    43. An Example of Data with Left and Right Justification
    44. A Visual of CHARACTER Data vs. VARCHAR Data
    45. Use the TRIM command to remove spaces on CHAR Data
    46. LIKE ALL means ALL conditions must be Met
    47. These Two Queries Do Exactly The Same Thing
    48. LIKE ANY means ANY of the Conditions can be Met
    49. These Two Queries Do Exactly The Same Thing
    50. IN ANSI Transaction Mode Case Matters
    51. In Teradata Transaction Mode Case Doesn’t Matter
    52. Escape Character in the LIKE Command changes Wildcards
    53. Escape Characters Turn off Wildcards in the LIKE Command
    54. Quiz – Turn off that Wildcard
    55. ANSWER – To Find that Wildcard
    56. Built-In Functions
  12. Chapter 3 – Distinct, Group By and TOP
    1. The Distinct Command
    2. Distinct vs. GROUP BY
    3. Quiz – How many rows come back from the Distinct?
    4. Answer – How many rows come back from the Distinct?
    5. TOP Command
    6. TOP Command is brilliant when ORDER BY is Used!
    7. What is the Difference Between TOP and Sample?
    8. SAMPLE Does NOT Use the ORDER BY Correctly
    9. The TOP Command WITH TIES
    10. The TOP Command will NOT work with Certain Commands
  13. Chapter 4 - Aggregation
    1. Quiz – You calculate the Answer Set in your own Mind
    2. Answer – You calculate the Answer Set in your own Mind
    3. The 3 Rules of Aggregation
    4. There are Five Aggregates
    5. Quiz – How many rows come back?
    6. Answer – How many rows come back?
    7. Troubleshooting Aggregates
    8. GROUP BY when Aggregates and Normal Columns Mix
    9. GROUP BY Delivers one row per Group
    10. GROUP BY Dept_No or GROUP BY 1 the same thing
    11. Limiting Rows and Improving Performance with WHERE
    12. WHERE Clause in Aggregation limits unneeded Calculations
    13. Keyword HAVING tests Aggregates after they are Totaled
    14. Aggregates Return Null on Empty Tables
    15. Keyword HAVING is like an Extra WHERE Clause for Totals
    16. Getting the Average Values Per Column
    17. Average Values Per Column For all Columns in a Table
    18. Three types of Advanced Grouping
    19. GROUP BY Grouping Sets
    20. GROUP BY Rollup
    21. GROUP BY Rollup Result Set
    22. GROUP BY Cube
    23. GROUP BY CUBE Result Set
    24. GROUP BY CUBE Result Set
  14. Chapter 5 – Join Functions
    1. Teradata Join Quiz
    2. Teradata Join Quiz Answer
    3. Redistribution
    4. Big Table Small Table Join Strategy
    5. Duplication of the Smaller Table across All-AMPs
    6. If the Join Condition is the Distribution Key no Movement
    7. Matching Rows That Are On The Same AMP Naturally
    8. What if the Join Condition Columns are Not Primary Indexes
    9. Strategy 1 of 4 – The Merge Join
    10. Quiz – Redistribute the Employees by their Dept_No
    11. Quiz – Employees' Dept_No landed on AMP with Matches
    12. Quiz – Redistribute the Orders to the Proper AMP
    13. Answer to Redistribute the Employees by their Dept_No Quiz
    14. Strategy 2 of 4 – The Hash Join
    15. Strategy 3 of 4 – The Nested Join
    16. Strategy 4 of 4 – The Product Join
    17. A Two-Table Join Using Traditional Syntax
    18. A two-table join using Non-ANSI Syntax with Table Alias
    19. You Can Fully Qualify All Columns
    20. A two-table join using ANSI Syntax
    21. Both Queries have the same Results and Performance
    22. Quiz – Can You Finish the Join Syntax?
    23. Answer to Quiz – Can You Finish the Join Syntax?
    24. Quiz – Can You Find the Error?
    25. Answer to Quiz – Can You Find the Error?
    26. Super Quiz – Can You Find the Difficult Error?
    27. Answer to Super Quiz – Can You Find the Difficult Error?
    28. Quiz – Which rows from both tables Won’t Return?
    29. Answer to Quiz – Which rows from both tables Won’t Return?
    30. LEFT OUTER JOIN
    31. LEFT OUTER JOIN Results
    32. RIGHT OUTER JOIN
    33. RIGHT OUTER JOIN Example and Results
    34. FULL OUTER JOIN
    35. FULL OUTER JOIN Results
    36. Which Tables are the Left and Which are the Right?
    37. Answer - Which Tables are the Left and Which are the Right?
    38. INNER JOIN with Additional AND Clause
    39. ANSI INNER JOIN with Additional AND Clause
    40. ANSI INNER JOIN with Additional WHERE Clause
    41. OUTER JOIN with Additional WHERE Clause
    42. OUTER JOIN with Additional AND Clause
    43. OUTER JOIN with Additional AND Clause Results
    44. Quiz – Why is this Considered an INNER JOIN?
    45. Evaluation Order For Outer Queries
    46. The DREADED Product Join
    47. The DREADED Product Join Results
    48. The Horrifying Cartesian Product Join
    49. The ANSI Cartesian Join will ERROR
    50. Quiz – Do these Joins Return the Same Answer Set?
    51. Answer – Do these Joins Return the Same Answer Set?
    52. The CROSS JOIN
    53. The CROSS JOIN Answer Set
    54. The Self Join
    55. The Self Join with ANSI Syntax
    56. Quiz – Will both queries bring back the same Answer Set?
    57. Answer – Will both queries bring back the same Answer Set?
    58. Quiz – Will both queries bring back the same Answer Set?
    59. Answer – Will both queries bring back the same Answer Set?
    60. How would you Join these two tables?
    61. An Associative Table is a Bridge that Joins Two Tables
    62. Quiz – Can you Write the 3-Table Join?
    63. Answer to Quiz – Can you Write the 3-Table Join?
    64. Quiz – Can you Write the 3-Table Join to ANSI Syntax?
    65. Answer – Can you Write the 3-Table Join to ANSI Syntax?
    66. Quiz – Can you Place the ON Clauses at the End?
    67. Answer – Can you Place the ON Clauses at the End?
    68. The 5-Table Join – Logical Insurance Model
    69. Quiz - Write a Five Table Join Using ANSI Syntax
    70. Answer - Write a Five Table Join Using ANSI Syntax
    71. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    72. Answer - Write a Five Table Join Using Non-ANSI Syntax
    73. Quiz –Re-Write this putting the ON clauses at the END
    74. Answer –Re-Write this putting the ON clauses at the END
    75. The Nexus Query Chameleon Writes the SQL for Users
  15. Chapter 6 – Date Functions
    1. Current_Date
    2. Date, Time, and Current_Timestamp Keywords
    3. Dates are stored Internally as INTEGERS from a Formula
    4. Displaying Dates for INTEGERDATE and ANSIDATE
    5. DATEFORM
    6. Changing the DATEFORM in Client Utilities such as BTEQ
    7. Date, Time, and Timestamp Recap
    8. Timestamp Differences
    9. Troubleshooting Timestamp
    10. Add or Subtract Days from a date
    11. A Summary of Math Operations on Dates
    12. Age in Days vs. Age in Years
    13. Find What Day of the week you were Born
    14. The ADD_MONTHS Command
    15. Using the ADD_MONTHS Command to Add 1 Year
    16. Using the ADD_MONTHS Command to Add 1 Year
    17. Using the ADD_MONTHS Command to Add 5 Years
    18. The EXTRACT Command
    19. Another Version of the EXTRACT Command
    20. EXTRACT from DATES and TIME
    21. Why EXTRACT is a Better Form
    22. EXTRACT or Math Can Accomplish the Same Thing
    23. EXTRACT with DATE and TIME Literals
    24. CAST the Date of January 1, 2011 and the Year 1800
    25. EXTRACT of the Month on Aggregate Queries
    26. The System Calendar
    27. Using the System Calendar In Its Simplest Form
    28. How to really use the Sys_Calendar.Calendar
    29. Storing Dates Internally
    30. Storing Time Internally
    31. Storing TIME With TIME ZONE Internally
    32. Storing Timestamp Internally
    33. Storing Timestamp with TIME ZONE Internally
    34. Storing Date, Time, and Timestamp with Zone Internally
    35. Time Zones
    36. Setting Time Zones at the System Level
    37. Setting Time Zones at the User Level
    38. Setting Time Zones at the Session Level
    39. Seeing your Time Zone
    40. Creating a Sample Table for Time Zone Examples
    41. Inserting Rows in the Sample Table for Time Zone Examples
    42. Inserting Rows in the Sample Table for Time Zone Examples
    43. Normalizing our Time Zone Table with a CAST
    44. Intervals for Date, Time and Timestamp
    45. Interval Data Types and the Bytes to Store Them
    46. Using Intervals
    47. Troubleshooting The Basics of a Simple Interval
    48. Interval Arithmetic Results
    49. A Date Interval Example
    50. A Time Interval Example
    51. A DATE Interval Example
    52. A Complex Time Interval Example using CAST
    53. A Complex Time Interval Example using CAST
    54. The OVERLAPS Command
    55. An OVERLAPS Example that Returns No Rows
    56. The OVERLAPS Command using TIME
    57. The OVERLAPS Command using a NULL Value
  16. Chapter 7 – Format Functions
    1. The FORMAT Command
    2. The Basics of the FORMAT Command
    3. Quiz – How will the Date Appear after Formatting
    4. Answer to Quiz – How will the Date Appear after Formatting
    5. Quiz – How will the Date Appear after Formatting
    6. Answer to Quiz – How will the Date Appear after Formatting
    7. Formatting with MMM for the Abbreviated Month
    8. Answer to Quiz – How will the Date Appear after Formatting
    9. Formatting with MMMM for the Full Month Name
    10. Formatting with MMMM for the Full Month
    11. Formatting with DDD for the Julian Day
    12. Formatting with DDD for the Julian Day
    13. Formatting with EEE or EEEE for the Day of the Week
    14. EEEE for the Abbreviated or Full Day of the Week
    15. Placing Spaces inside your Formatting Commands with a B
    16. Formatting Spaces with B or b
    17. Formatting with 9
    18. Formatting with 9 Results
    19. Troubleshooting when Formatted Data Overflows
    20. Troubleshooting when Formatted Data Overflows
    21. Formatting with X or x
    22. Formatting with X or x Results
    23. Formatting with Z
    24. Formatting with Z Visual
    25. Formatting with 9
    26. Formatting with 9 Visual
    27. Formatting with $
    28. Formatting with $ Visual
    29. Formatting with $ and Commas
    30. Formatting with $ and Commas Visual
    31. Formatting with $ and Commas and 9
    32. Formatting with $ and Commas and 9 with Zero Dollars
    33. A Great Formatting Example
    34. A Great Formatting Example for Day, Month and Year
    35. A Trick to get SQL Assistant to Format Data
    36. Using the CASESPECIFIC (CS) Command in Teradata Mode
    37. Using NOT CASESPECIFIC (CS) in ANSI Mode
    38. Using the LOWER Command
    39. Using the UPPER Command
  17. Chapter 8 - OLAP Functions
    1. On-Line Analytical Processing (OLAP) or Ordered Analytics
    2. Cumulative Sum (CSUM) Command and how OLAP Works
    3. After the Sort the CSUM is Calculated
    4. The OLAP Major Sort Key
    5. The OLAP Major Sort Key and the Minor Sort Key(s)
    6. Troubleshooting OLAP – My Data isn’t coming back Correct
    7. GROUP BY in Teradata OLAP Syntax Resets on the Group
    8. CSUM the Number 1 to get a Sequential Number
    9. A Single GROUP BY Resets each OLAP with Teradata Syntax
    10. CSUM
    11. CSUM – The Sort Explained
    12. CSUM – Rows Unbounded Preceding Explained
    13. CSUM – Making Sense of the Data
    14. CSUM – Making Even More Sense of the Data
    15. CSUM – The Major and Minor Sort Key(s)
    16. The ANSI CSUM – Getting a Sequential Number
    17. Troubleshooting The ANSI OLAP on a GROUP BY
    18. Reset with a PARTITION BY Statement
    19. PARTITION BY only Resets a Single OLAP not ALL of them
    20. The Moving SUM (MSUM) and Moving Window
    21. How the Moving Sum is Calculated
    22. How the Sort works for Moving SUM (MSUM)
    23. GROUP BY in the Moving SUM does a Reset
    24. Quiz – Can you make the Advanced Calculation in your mind?
    25. Answer to Quiz for the Advanced Calculation in your mind?
    26. Quiz – Write that Teradata Moving SUM in ANSI Syntax
    27. Both the Teradata Moving SUM and ANSI Version
    28. ANSI Moving Window is Current Row and Preceding n Rows
    29. How ANSI Moving SUM Handles the Sort
    30. Quiz – How is that Total Calculated?
    31. Answer to Quiz – How is that Total Calculated?
    32. Moving SUM every 3-rows Vs a Continuous Average
    33. Partition By Resets an ANSI OLAP
    34. The Moving Average (MAVG) and Moving Window
    35. How the Moving Average is Calculated
    36. How the Sort works for Moving Average (MAVG)
    37. GROUP BY in the Moving Average does a Reset
    38. Quiz – Can you make the Advanced Calculation in your mind?
    39. Answer to Quiz for the Advanced Calculation in your mind?
    40. Quiz – Write that Teradata Moving Average in ANSI Syntax
    41. Both the Teradata Moving Average and ANSI Version
    42. Moving Average
    43. The Moving Window is Current Row and Preceding
    44. How Moving Average Handles the Sort
    45. Quiz – How is that Total Calculated?
    46. Answer to Quiz – How is that Total Calculated?
    47. Quiz – How is that 4th Row Calculated?
    48. Answer to Quiz – How is that 4th Row Calculated?
    49. Moving Average every 3-rows Vs a Continuous Average
    50. Partition By Resets an ANSI OLAP
    51. The Moving Difference (MDIFF)
    52. Moving Difference using ANSI Syntax
    53. Moving Difference using ANSI Syntax with Partition By
    54. Trouble Shooting the Moving Difference (MDIFF)
    55. Using the RESET WHEN Option in Teradata V13
    56. How Many Months Per Product_ID Has Revenue Increased?
    57. The RANK Command
    58. How to get Rank to Sort in Ascending Order
    59. Two ways to get Rank to Sort in Ascending Order
    60. RANK Defaults to Ascending Order
    61. Getting RANK to Sort in DESC Order
    62. RANK() OVER and PARTITION BY
    63. RANK() OVER And QUALIFY
    64. RANK() OVER and PARTITION BY with a QUALIFY
    65. QUALIFY and WHERE
    66. Quiz – How can you simplify the QUALIFY Statement?
    67. Answer to Quiz –Can you simplify the QUALIFY Statement
    68. The QUALIFY Statement without Ties
    69. The QUALIFY Statement with Ties
    70. The QUALIFY Statement with Ties Brings back Extra Rows
    71. Mixing Sort Order for QUALIFY Statement
    72. Quiz – What Caused the RANK to Reset?
    73. Answer to Quiz – What Caused the RANK to Reset?
    74. Quiz – Name those Sort Orders
    75. Answer to Quiz – Name those Sort Orders
    76. PERCENT_RANK() OVER
    77. PERCENT_RANK() OVER with 14 rows in Calculation
    78. PERCENT_RANK() OVER with 21 rows in Calculation
    79. Quiz – What Causes the Product_ID to Reset?
    80. Answer to Quiz – What Cause the Product_ID to Reset?
    81. COUNT OVER for a Sequential Number
    82. Troubleshooting COUNT OVER
    83. Quiz – What caused the COUNT OVER to Reset?
    84. Answer to Quiz – What caused the COUNT OVER to Reset?
    85. The MAX OVER Command
    86. MAX OVER with PARTITION BY Reset
    87. Troubleshooting MAX OVER
    88. The MIN OVER Command
    89. Troubleshooting MIN OVER
    90. Finding a Value of a Column in the Next Row with MIN
    91. Finding Gaps Between Dates
    92. The CSUM For Each Product_Id For The First 3 Days
    93. Quiz – Fill in the Blank
    94. Answer – Fill in the Blank
    95. The Row_Number Command
    96. Quiz – How did the Row_Number Reset?
    97. Quiz – How did the Row_Number Reset?
    98. Row_Number With Qualify to get the Typical Rows Per Value
    99. A Second Typical Rows Per Value Query on Sale_Date
  18. Chapter 9 – The Quantile Function
    1. The Quantile Function and Syntax
    2. A Quantile Example
    3. A Quantile Example using DESC Mode
    4. QUALIFY to find Products in the top Partitions
    5. QUALIFY to find Products in the top Partitions Sorted DESC
    6. QUALIFY to find Products in the top Partitions Sorted ASC
    7. QUALIFY to find Products in top Partitions with Tiebreaker
    8. Using Tertiles (Partitions of Four)
    9. How Quantile Works
  19. Chapter 10 - Temporary Tables
    1. There are Three types of Temporary Tables
    2. CREATING A Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in The Derived Table
    5. Multiple Ways to Alias the Columns in a Derived Table
    6. CREATING A Derived Table using the WITH Command
    7. Most Derived Tables Are Used To Join To Other Tables
    8. The Three Components of a Derived Table
    9. Visualize This Derived Table
    10. Our Join Example With A Different Column Aliasing Style
    11. Column Aliasing Can Default For Normal Columns
    12. Our Join Example With The WITH Syntax
    13. Quiz - Answer the Questions
    14. Answer to Quiz - Answer the Questions
    15. Clever Tricks on Aliasing Columns in a Derived Table
    16. A Derived Table lives only for the lifetime of a single query
    17. An Example of Two Derived Tables in a Single Query
    18. WITH RECURSIVE Derived Table Hierarchy
    19. WITH RECURSIVE Derived Table Query
    20. WITH RECURSIVE Derived Table Definition
    21. WITH RECURSIVE Derived Table Seeding
    22. WITH RECURSIVE Derived Table Looping
    23. WITH RECURSIVE Derived Table Looping in Slow Motion
    24. WITH RECURSIVE Derived Table Looping Continued
    25. WITH RECURSIVE Derived Table Looping Continued
    26. WITH RECURSIVE Derived Table Ends the Looping
    27. WITH RECURSIVE Derived Table Definition
    28. WITH RECURSIVE Derived Table Definition
    29. Creating a Volatile Table
    30. You Populate a Volatile Table with an INSERT/SELECT
    31. The Three Steps to Use a Volatile Table
    32. Why Would You Use the ON COMMIT DELETE ROWS?
    33. The HELP Volatile Table Command Shows your Volatiles
    34. A Volatile Table with a Primary Index
    35. The Joining of Two Tables Using a Volatile Table
    36. You Can Collect Statistics on Volatile Tables
    37. The New Teradata V14 Way to Collect Statistics
    38. Some Great Examples of Creating a Volatile Table Quickly
    39. Creating Partitioned Primary Index (PPI) Volatile Tables
    40. A Volatile Table That Only Populates Some of the Rows
    41. A Volatile Table With No Data
    42. A Volatile Table With Some of the Columns
    43. A Volatile Table With No Data and Zeroed Statistics
    44. A Multiset Volatile Table With Statistics Example
    45. Using a Volatile Table to Get Rid of Duplicate Rows
    46. CREATING A Global Temporary Table
    47. Many Users Can Populate the Same Global Temporary Table
    48. Global Temporary Table with a Primary Index and Compress
  20. Chapter 11 - Sub-query Functions
    1. An IN List is much like a Subquery
    2. An IN List Never has Duplicates – Just like a Subquery
    3. An IN List Ignores Duplicates
    4. The Subquery
    5. The Three Steps of How a Basic Subquery Works
    6. These are Equivalent Queries
    7. The Final Answer Set from the Subquery
    8. Quiz- Answer the Difficult Question
    9. Answer to Quiz- Answer the Difficult Question
    10. Should you use a Subquery of a Join?
    11. Quiz- Write the Subquery
    12. Answer to Quiz- Write the Subquery
    13. Quiz- Write the More Difficult Subquery
    14. Answer to Quiz- Write the More Difficult Subquery
    15. Quiz- Write the Subquery with an Aggregate
    16. Answer to Quiz- Write the Subquery with an Aggregate
    17. Quiz- Write the Correlated Subquery
    18. Answer to Quiz- Write the Correlated Subquery
    19. The Basics of a Correlated Subquery
    20. The Top Query always runs first in a Correlated Subquery
    21. Correlated Subquery Example vs. a Join with a Derived Table
    22. Quiz- A Second Chance To Write a Correlated Subquery
    23. Answer - A Second Chance to Write a Correlated Subquery
    24. Quiz- A Third Chance To Write a Correlated Subquery
    25. Answer - A Third Chance to Write a Correlated Subquery
    26. Quiz- Last Chance To Write a Correlated Subquery
    27. Answer – Last Chance to Write a Correlated Subquery
    28. Quiz- Write the NOT Subquery
    29. Answer to Quiz- Write the NOT Subquery
    30. Quiz- Write the Subquery using a WHERE Clause
    31. Answer - Write the Subquery using a WHERE Clause
    32. Quiz- Write the Subquery with Two Parameters
    33. Answer to Quiz- Write the Subquery with Two Parameters
    34. How the Double Parameter Subquery Works
    35. More on how the Double Parameter Subquery Works
    36. Quiz – Write the Triple Subquery
    37. Answer to Quiz – Write the Triple Subquery
    38. Quiz – How many rows return on a NOT IN with a NULL?
    39. Answer – How many rows return on a NOT IN with a NULL?
    40. How to handle a NOT IN with Potential NULL Values
    41. IN is equivalent to =ANY
    42. Using a Correlated Exists
    43. How a Correlated Exists matches up
    44. The Correlated NOT Exists
    45. The Correlated NOT Exists Answer Set
    46. Quiz – How many rows come back from this NOT Exists?
    47. Answer – How many rows come back from this NOT Exists?
  21. Chapter 12 – Substring and Positioning Functions
    1. The CHARACTERS Command Counts Characters
    2. The CHARACTERS Command – Spaces can Count too
    3. The CHARACTERS Command and Char(20) Data
    4. Troubleshooting the CHARACTERS Command
    5. TRIM for Troubleshooting the CHARACTERS Command
    6. CHARACTERS and CHARACTER_LENGTH equivalent
    7. OCTET_LENGTH
    8. The TRIM Command trims both Leading and Trailing Spaces
    9. Trim Combined with the CHARACTERS Command
    10. How to TRIM only the Trailing Spaces
    11. A Visual of the TRIM Command Using Concatenation
    12. Trim and Trailing is Case Sensitive
    13. How to TRIM Trailing Letters
    14. The SUBSTRING Command
    15. SUBSTRING and SUBSTR are equal, but use different syntax
    16. How SUBSTRING Works with NO ENDING POSITION
    17. Using SUBSTRING to move Backwards
    18. How SUBSTRING Works with a Starting Position of -1
    19. How SUBSTRING Works with an Ending Position of 0
    20. An Example using SUBSTRING, TRIM and CHAR Together
    21. The POSITION Command finds a Letters Position
    22. The POSITION Command is brilliant with SUBSTRING
    23. Quiz – Find that SUBSTRING Starting Position
    24. Answer to Quiz – Find that SUBSTRING Starting Position
    25. Using the SUBSTRING to Find the Second Word On
    26. Quiz – Why Did only one Row Return
    27. Answer to Quiz – Why Did only one Row Return
    28. Concatenation
    29. Concatenation and SUBSTRING
    30. Four Concatenations Together
    31. Troubleshooting Concatenation
    32. Imbedded Services Functions
    33. Imbedded Services Functions and Their Descriptions
    34. Imbedded Services Functions Example With To_Date
  22. Chapter 13 – Interrogating the Data
    1. Quiz – What would the Answer be?
    2. Answer to Quiz – What would the Answer be?
    3. The NULLIFZERO Command
    4. Quiz – Fill in the Blank Values in the Answer Set
    5. Answer to Quiz – Fill in the Blank Values in the Answer Set
    6. Quiz – Fill in the Answers for the NULLIF Command
    7. Quiz – Fill in the Answers for the NULLIF Command
    8. The ZEROIFNULL Command
    9. Answer to the ZEROIFNULL Question
    10. The COALESCE Command
    11. The COALESCE Answer Set
    12. The Coalesce Quiz
    13. Answer – The Coalesce Quiz
    14. The Basics of CAST (Convert And STore)
    15. Some Great CAST (Convert And STore) Examples
    16. Some Great CAST (Convert And STore) Examples
    17. Teradata Rules About Rounding
    18. A Rounding Example
    19. Some Great CAST (Convert And STore) Examples
    20. A Teradata Extension – The Implied Cast
    21. The Basics of the CASE Statements
    22. The Basics of the CASE Statements
    23. Valued Case Vs. A Searched Case
    24. Quiz - Valued Case Statement
    25. Answer - Valued Case Statement
    26. Quiz - Searched Case Statement
    27. Answer - Searched Case Statement
    28. Quiz - When NO ELSE is present in CASE Statement
    29. Answer - When NO ELSE is present in CASE Statement
    30. When an ELSE is present in CASE Statement
    31. Answer - When an ELSE is present in CASE Statement
    32. When an Alias is NOT used in a CASE Statement
    33. Answer - When an Alias is NOT used in a CASE Statement
    34. Combining Searched Case and Valued Case
    35. A Trick for getting a Horizontal Case
    36. Nested Case
    37. Put a CASE in the ORDER BY
  23. Chapter 14 – View Functions
    1. The Fundamentals of Views
    2. Creating a Simple View to Restrict Sensitive Columns
    3. Creating a Simple View to Restrict Rows
    4. Basic Rules for Views
    5. How to Modify a View
    6. Two Exceptions to the ORDER BY Rule inside a View
    7. How to Get HELP with a View
    8. Views sometimes CREATED for Formatting or Row Security
    9. Creating a View to Join Tables Together
    10. You Select From a View
    11. Another Way to Alias Columns in a View CREATE
    12. The Standard Way Most Aliasing is Done
    13. What Happens When Both Aliasing Options Are Present
    14. Resolving Aliasing Problems in a View CREATE
    15. Answer to Resolving Aliasing Problems in a View CREATE
    16. Aggregates on View Aggregates
    17. Locking Row for Access
    18. Altering A Table
    19. Altering A Table After a View has been Created
    20. A View that Errors After An ALTER
    21. Troubleshooting a View
    22. Updating Data in a Table through a View
    23. Loading Data through a View
    24. Maintenance Restrictions on a Table through a View
  24. Chapter 15 – Macro Functions
    1. The Rules of Macros
    2. The Rules of Macros
    3. CREATING and EXECUTING a Simple Macro
    4. Multiple SQL Statements inside a Macro
    5. Complex Joins inside a Macro
    6. Passing an INPUT Parameter to a Macro
    7. Troubleshooting a Macro with INPUT Parameters
    8. An UPDATE Macro with Two Input Parameters
    9. Executing a Macro with Named (Not Positional) Parameters
    10. Macro Parameter Restrictions
    11. Troubleshooting a Macro
    12. More Troubleshooting of a Macro
    13. Clever Tricks
  25. Chapter 16 – Set Operators Functions
    1. Rules of Set Operators
    2. Rules of Set Operators
    3. INTERSECT Explained Logically
    4. INTERSECT Explained Logically
    5. UNION Explained Logically
    6. UNION Explained Logically
    7. UNION ALL Explained Logically
    8. UNION Explained Logically
    9. EXCEPT Explained Logically
    10. EXCEPT Explained Logically
    11. Minus Explained Logically
    12. Minus Explained Logically
    13. Testing Your Knowledge
    14. Answer - Testing Your Knowledge
    15. Testing Your Knowledge
    16. Answer - Testing Your Knowledge
    17. An Equal Amount of Columns in both SELECT List
    18. Columns in the SELECT list should be from the same Domain
    19. The Top Query handles all Aliases
    20. The Bottom Query does the ORDER BY (a Number)
    21. Great Trick: Place your Set Operator in a Derived Table
    22. UNION Vs UNION ALL
    23. Using UNION ALL and Literals
    24. A Great Example of how EXCEPT works
    25. USING Multiple SET Operators in a Single Request
    26. Changing the Order of Precedence with Parentheses
    27. Using UNION ALL for speed in Merging Data Sets
    28. Using UNION to be same as GROUP BY GROUPING SETS
  26. Chapter 17 - Table Create and Data Types
    1. Creating a Table With A Unique Primary Index (UPI)
    2. Creating a Table With A Non-Unique Primary Index (NUPI)
    3. Creating a Table With A Multi-Column Primary Index
    4. Creating a Table With No Primary Index
    5. Creating a Table Without Entering a Primary Index Definition
    6. Creating a SET Table
    7. Creating a MULTISET Table
    8. Creating a SET Table With a Unique Primary Index
    9. Creating a Table With Multiple Secondary Indexes
    10. Creating a PPI Table with Simple Partitioning
    11. Creating a PPI Table with RANGE_N Partitioning per Month
    12. Creating a PPI Table with RANGE_N Partitioning per Day
    13. Creating a PPI Table with RANGE_N Partitioning per Week
    14. A Clever Range_N Option
    15. Creating a PPI Table with CASE_N
    16. The No Case and Unknown Partition Options
    17. Partitioning of Older and Newer Data Separately
    18. Multi-Level Partitioning
    19. Almost All PPI Tables have a Non-Unique Primary Index
    20. PPI Table With a Unique Primary Index (UPI)
    21. Clever Trick for PPI Tables
    22. Another Clever Trick for PPI Tables
    23. Character Based PPI for RANGE_N
    24. Character-Based PPI for CASE_N
    25. Dates and Character-Based Multi-Level PPI
    26. TIMESTAMP Partitioning That is Deterministic
    27. Altering a PPI Table the Hard Way
    28. Altering a PPI Table the Easy Way With TO CURRENT
    29. Altering a PPI Table and Saving the Deleted Data
    30. Using the PARTITION Keyword in your SQL
    31. SQL for RANGE_N
    32. SQL for CASE_N
    33. Creating a Columnar Table
    34. Creating a Columnar Table With Multi-Column Containers
    35. Columnar Row Hybrid CREATE Statement
    36. CREATE Statement for both Row and Column Partition
    37. CREATING a Bi-Temporal Table
    38. Creating a Table With Fallback
    39. Creating a Table With No Fallback
    40. Creating a Table With a Before Journal
    41. Creating a Table With a Dual Before Journal
    42. Creating a Table With an After Journal
    43. Creating a Table With a Dual After Journal
    44. Creating a Table With a Journal
    45. Why Use Journaling?
    46. Table Customization of the Data Block Size
    47. Table Customization with FREESPACE Percent
    48. Creating a QUEUE Table
    49. You Can Select From a Queue Table
    50. Exploring the Real Purpose of a Queue Table
    51. Column Attributes
    52. An Example of a Table With Column Attributes
    53. An Example of a Table With Column Level Constraints
    54. An Example of a Table With Table Level Constraints
    55. Create Table AS
    56. Creating Partitioned Primary Index (PPI) Volatile Tables
    57. A Volatile Table That Only Populates Some of the Rows
    58. A Volatile Table With No Data
    59. A Volatile Table With Some of the Columns
    60. A Volatile Table With No Data and Zeroed Statistics
    61. A Multiset Volatile Table With Statistics Example
    62. Data Types
    63. Data Types Continued
    64. Data Types Continued
    65. Major Data Types and the number of Bytes they take up
  27. Chapter 18 - Data Manipulation Language (DML)
    1. INSERT Syntax # 1
    2. INSERT Example with Syntax 1
    3. INSERT Syntax # 2
    4. INSERT Example with Syntax 2
    5. INSERT Example with Syntax 3
    6. Using NULL for Default Values
    7. INSERT/SELECT Command
    8. INSERT/SELECT Example using All Columns (*)
    9. INSERT/SELECT Example with Less Columns
    10. INSERT/SELECT to Build a Data Mart
    11. Fast Path INSERT/SELECT
    12. NOT quite the Fast Path INSERT/SELECT
    13. UNION for the Fast Path INSERT/SELECT
    14. BTEQ for the Fast Path INSERT/SELECT
    15. The UPDATE Command Basic Syntax
    16. Two UPDATE Examples
    17. Subquery UPDATE Command Syntax
    18. Example of Subquery UPDATE Command
    19. Join UPDATE Command Syntax
    20. Example of an UPDATE Join Command
    21. Fast Path UPDATE
    22. The DELETE Command Basic Syntax
    23. Two DELETE Examples to DELETE ALL Rows in a Table
    24. A DELETE Example Deleting only Some of the Rows
    25. Subquery and Join DELETE Command Syntax
    26. Example of Subquery DELETE Command
    27. Example of Join DELETE Command
    28. Fast Path DELETE
    29. Fast Path DELETE Example # 2
    30. Fast Path DELETE Example # 3
    31. MERGE INTO
    32. MERGE INTO Example that Matches
    33. MERGE INTO Example that does NOT Match
    34. OReplace
    35. User Defined Functions (UDFs)
    36. User Defined Function Example
    37. User Defined Types (UDTs)
    38. User Defined Types Example
    39. Formatting for Internationalizations
  28. Chapter 19 – Stored Procedure Functions
    1. Stored Procedures Vs. Macros
    2. Creating a Stored Procedure
    3. How you CALL a Stored Procedure
    4. Label all BEGIN and END statements except the first ones
    5. How to Declare a Variable
    6. How to Declare a Variable and then SET the Variable
    7. An IN Variable is passed to the Procedure during the CALL
    8. The IN, OUT and INOUT Parameters
    9. Using IF inside a Stored Procedure
    10. Example of two Stored Procedures with different techniques
    11. Using Loops in Stored Procedures
    12. Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT
    13. Stored Procedure Workshop
    14. Stored Procedure Workshop Answer
    15. Stored Procedure Workshop Alternative Answer
  29. Chapter 20 – Trigger Functions
    1. The Fundamentals of Triggers
    2. CREATING A Trigger
    3. Checking To See If the Trigger Works
    4. FOR EACH STATEMENT vs. FOR EACH ROW
    5. Checking To See If Our New Trigger Worked
    6. Using ORDER when Similar Triggers Exist
  30. Chapter 21 – The SAMPLE Function
    1. The SAMPLE Function and Syntax
    2. SAMPLE Function Examples
    3. A SAMPLE Example that asks for Multiple Samples
    4. A SAMPLE Example with the SAMPLEID
    5. A SAMPLE Example WITH REPLACEMENT
    6. A SAMPLE Example with Four 10% Samples
    7. A Randomized SAMPLE
    8. A SAMPLE with Conditional Logic
    9. Aggregates and A SAMPLE using a Derived Table
    10. Random Number Generator
    11. Using Random to SELECT a Percentage of Rows
    12. Using Random and Aggregations
  31. Chapter 22 – Statistical Aggregate Function
    1. The Stats Table
    2. The KURTOSIS Function
    3. A Kurtosis Example
    4. The SKEW Function
    5. A SKEW Example
    6. The STDDEV_POP Function
    7. A STDDEV_POP Example
    8. The STDDEV_SAMP Function
    9. A STDDEV_SAMP Example
    10. The VAR_POP Function
    11. A VAR_POP Example
    12. The VAR_SAMP Function
    13. A VAR_SAMP Example
    14. The CORR Function
    15. A CORR Example
    16. Another CORR Example so you can Compare
    17. The COVAR_POP Function
    18. A COVAR_POP Example
    19. Another COVAR_POP Example so you can Compare
    20. The REGR_INTERCEPT Function
    21. A REGR_INTERCEPT Example
    22. Another REGR_INTERCEPT Example so you can Compare
    23. The REGR_SLOPE Function
    24. A REGR_SLOPE Example
    25. Another REGR_SLOPE Example so you can Compare
    26. Using GROUP BY
    27. No Having Clause Vs Use of HAVING
  32. Chapter 23 – HELP and SHOW
    1. Determining the Release of your Teradata System
    2. Basic HELP Commands
    3. Other HELP Commands
    4. HELP DATABASE
    5. HELP USER
    6. HELP TABLE
    7. Adding a Comment to a Table
    8. Adding a Comment to a View
    9. SELECT SESSION
    10. USER Information Functions
    11. HELP SESSION
    12. HELP SQL
    13. A HELP SQL Example
    14. Show Commands
    15. SHOW Table command for Table DDL
    16. SHOW View command for View Create Statement
    17. SHOW Macro command for Macro Create Statement
    18. SHOW Trigger command for Trigger Create Statement
  33. Chapter 24 – Teradata (BTET) vs. ANSI Mode
    1. Teradata Mode (BTET) vs. ANSI Mode
    2. Both Modes Can Have Control Over The Commit Time
    3. Multi-Statement Requests
    4. Rolling Things Back
    5. Multi-statement Transactions Using BTEQ Semi-colons
    6. Case Sensitivity
    7. What Happens If A Transaction Fails?
  34. Chapter 25 – BTEQ – Batch Teradata Query
    1. BTEQ – Batch TEradata Query Tool
    2. How to Logon to BTEQ in Interactive Mode
    3. Running Queries in BTEQ in Interactive Mode
    4. BTEQ Commands Vs BTEQ SQL Statements
    5. WITH BY Command for Subtotals
    6. WITH Command for a Grand Total
    7. WITH and WITH BY Together for Subtotals and Grand Totals
    8. How to Logon to BTEQ in a SCRIPT
    9. Multi-statement Transactions Using BTEQ Semi-colons
  35. Chapter 26 – Locking
    1. The Four Major Locks of Teradata
    2. The Read Lock
    3. The Read Lock and Joins
    4. The Write Lock
    5. The Exclusive Lock
    6. The Three Levels of Locking
    7. Locking at the Row Hash Level
    8. Locking at the Table Level
    9. Locking at the Database Level
    10. The Ongoing Battle between Read and Write Locks
    11. Compatibility between Read Locks
    12. Why Read Locks Wait on Write Locks
    13. Why Write Locks Wait on Read Locks
    14. The Access Lock is Different from the Other Locks
    15. What is the Purpose of an Access Lock?
    16. Locking Modifiers - Locking Row, Table or Database
    17. All Views should consider the Locking for Access Statement
    18. What is a Dead Lock or a Deadly Embrace?
    19. Pseudo Tables are designed to minimize Dead Locks
    20. Pseudo Tables are referenced in the Explain Plan
    21. Incompatible Locks Wait on each Other
    22. The Checksum Lock of Teradata
    23. The Nowait Option for Locking
    24. The Automatic Locking for Access Button inside Nexus
    25. Viewpoint Lock Viewer
    26. Viewpoint Lock Viewer Lets You Configure Your View
    27. What is a Host Utility (HUT) Lock?
  36. Chapter 27 – Lessons with Tera-Tom Video Guide
    1. Video 1 - Teradata Basics - The Architecture
    2. Video 2 - Hashing the Primary Index
    3. Video 3 - The Cold Hard Teradata Facts
    4. Video 4 -Inside the Amps’ Disc
    5. Video 5 - PPI (Partitioned Primary Index Tables)
    6. Video 6 Teradata Columnar
    7. Video 7- Space
    8. Video 8 - How Teradata Joins Tables Together
    9. Video 9- Protection Features (1/3)
    10. Video 10- Protection Features (2/3)
    11. Video 11- Protection Features (3/3)
    12. Video 12- Collect Statistics (1 of 2)
    13. Video 13- Collect Statistics (2 of 2)
    14. Video 14- Locking
    15. Video 15- Temporal Tables
    16. Video 16- Join Indexes
    17. Teradata SQL Video 1 - The Basics of SQL
    18. Teradata SQL Video 2 – Building Your SQL Knowledge
    19. Teradata SQL Video 3 - Aggregates
    20. Teradata SQL Video 4 - Joins
    21. Teradata SQL Video 5 - Dates
    22. Teradata SQL Video 6 – Temporary Tables (Derived)
    23. Teradata SQL Video 7 – Volatile and Global Temporary Tables
    24. Teradata SQL Video 8 – Ordered Analytic Functions
    25. Teradata SQL Video 9 – Advanced Ordered Analytics
    26. Teradata SQL Video 10 - Subqueries
    27. Teradata SQL Video 11 – Substring and Positioning
    28. Teradata SQL Video 12 – Data Interrogation
    29. Teradata SQL Video 13 - Views
    30. Teradata SQL Video 14 - Macros
    31. Teradata SQL Video 15 – Stored Procedures
    32. Teradata SQL Video 16 – Set Operators
    33. Teradata SQL Video 17 – Creating Tables