You are previewing Tera-Tom's 1000 Page e-Book on Teradata Architecture and SQL, 2nd Edition.
O'Reilly logo
Tera-Tom's 1000 Page e-Book on Teradata Architecture and SQL, 2nd Edition

Book Description

This single book teaches the reader everything they need to know about Teradata, the architecture, and how Teradata works. It then gives over 1000 SQL examples that teach them everything. If you went to a desert island and could only take one book, this is the one you want.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. About Tom Coffing
  5. About Warehouse William Coffing
  6. Contents
  7. Chapter 1 - Introduction and Good Advice
    1. What is Parallel Processing?
    2. Start Small and Think Big
    3. Give your Enterprise the Tools they Need
    4. Model the Business with ERwin
    5. Educate the Business on the Business by Sharing the Model
    6. Load Your Models and have the SQL Built Automatically
    7. Five Brilliant Pieces of Teradata (1 of 5) is MPP
    8. Five Brilliant Pieces (2 of 5) are Tactical Queries
    9. Five Brilliant Pieces (3 of 5) is a Traffic System
    10. Five Brilliant Pieces (4 of 5) is Viewpoint
    11. Five Brilliant Pieces (5 of 5) Are Data Processing Options
  8. Chapter 2 - The Teradata Architecture
    1. The Basics of a Single Computer
    2. Teradata Parallel Processes Data
    3. Parallel Architecture
    4. The Teradata Architecture
    5. All Teradata Tables are spread across ALL AMPS
    6. Teradata Systems can Add AMPs for Linear Scalability
    7. Understand that Teradata can scale to incredible size
    8. AMPs and Parsing Engines (PEs) live inside SMP Nodes
    9. Each Node is attached via a Network to a Disk Farm
    10. Two SMP Nodes Connected Become One MPP System
    11. There are Many Nodes in a Teradata Cabinet
    12. Inside a Teradata Node
    13. The Boardless BYNET and the Physical BYNET
    14. The Parsing Engine
    15. The AMPs Responsibilities
    16. This is the Visual You Want to Know in order to Understand Teradata
  9. Chapter 3 - The Primary Index
    1. The Primary Index is defined when the table is CREATED
    2. A Unique Primary Index (UPI)
    3. Primary Index in the WHERE Clause - Single-AMP Retrieve
    4. Using EXPLAIN
    5. A Non-Unique Primary Index (NUPI)
    6. Primary Index in the WHERE Clause - Single-AMP Retrieve
    7. Using EXPLAIN in a NUPI Query
    8. A conceptual example of a Multi-Column Primary Index
    9. Primary Index in the WHERE Clause - Single-AMP Retrieve
    10. A conceptual example of a Table with NO PRIMARY INDEX
    11. A Full Table Scan is likely on a table with NO Primary Index
    12. An EXPLAIN that shows a Full Table Scan
    13. Table CREATE Examples with four different Primary Indexes
    14. What happens when you forget the Primary Index?
    15. Why create a table with No Primary Index (NoPI)?
  10. Chapter 4 - Hashing of the Primary Index
    1. The Hashing Formula Facts
    2. The Hash Map Determines which AMP will own the Row
    3. The Hash Map Determines which AMP will own the Row
    4. Placing rows on the AMP
    5. Placing rows on the AMP Continued
    6. A Review of the Hashing Process
    7. Non-Unique Primary Indexes have Skewed Data
    8. The Uniqueness Value
    9. The Row Hash and Uniqueness Value make up the Row-ID
    10. A Row-ID Example for a Unique Primary Index
    11. A Row-ID Example for a Non-Unique Primary Index (NUPI)
    12. Two Reasons why each AMP Sorts their rows by the Row-ID
    13. AMPs sort their rows by Row-ID to Group like Data
    14. AMPs sort their rows by Row-ID to do a Binary Search
    15. Table CREATE Examples with four different Primary Indexes
    16. Null Values all Hash to the Same AMP
    17. A Unique Primary Index (UPI) Example
    18. A Non-Unique Primary Index (NUPI) Example
    19. A Multi-Column Primary Index Example
    20. A No Primary Index (NoPI) Example
  11. Chapter 5 - Teradata - The Cold Hard Facts
    1. All Teradata Tables are spread across All AMPs
    2. The Table Header and the Data Rows are Stored Separately
    3. An AMP Stores the Rows of a Table inside a Data Block
    4. To Read a Data Block, an AMP Moves the Block into Memory
    5. Nothing is done on disk and everything is done in Memory
    6. Most Taxing thing for an AMP is Moving Blocks into Memory
    7. A Full Table Scan Means All AMPs must Read All Rows
    8. The “Achilles Heel and slowest process is Block Transfer
    9. Each Table has a Primary Index
    10. A Query Using the Primary Index is a Single AMP Retrieve
    11. As Rows are added a Data Block will Eventually Split
    12. A Full Table Scan Means All AMPs must Read All Blocks
    13. A Primary Index Query uses a Single AMP and Single Block
    14. Each AMP Can Have Many Blocks for a Single Table
    15. A Full Table Scan Means All AMPs must Read All Blocks
    16. Quiz – How Many Blocks Move into FSG Cache?
    17. Answer – How Many Blocks Move into FSG Cache?
    18. Quiz – How Many Blocks Move Using the Primary Index?
    19. Answer – How Many Blocks Move Using the Primary Index?
    20. Synchronized Scan (Sync Scan)
    21. EXPLAIN Using a Synchronized Scan
    22. Intelligent Memory (Teradata V14.10)
    23. Teradata V14.10 Intelligent Memory Gives Data a Temperature
    24. Data deemed VeryHot stays in each AMP's Intelligent Memory
    25. Intelligent Memory Stays in Memory
    26. What is the Goal of a Teradata Physical Database Design?
  12. Chapter 6 - Inside the AMPs Disk
    1. Rows are Stored in Data Blocks which are stored in Cylinders
    2. An AMP's rows are stored inside a Data Block in a Cylinder
    3. An AMP's Master Index is used to find the Right Cylinder
    4. The Row Reference Array (RRA) Does the Binary Search?
    5. A Block Splits into Two Blocks at Maximum Block Size
    6. Data Blocks Maximum Block Size has Changed (V14.10)
    7. The New Block Split with Teradata V14.10
    8. The Block Split with Even More Detail in Teradata V14.10
    9. Teradata V14.10 Block Split Defaults
    10. There is One Master Index and Thousands of Cylinder Indexes
    11. Blocks Continue to Split as Tables Grow Larger
    12. FYI – Some Advanced Information about Data Block Headers
    13. A top down view of Cylinders
    14. There are Hot, Warm, and Cold Cylinders
    15. Cylinders are used for Perm, Spool, Temp, and Journals
    16. Each AMP has Their Own Master Index
    17. Each Cylinder on an AMP has a Cylinder Index
    18. Quiz – What Two Things Does and AMP Read?
    19. Answer – What Two Things Does and AMP Read?
    20. Quiz – How Many Row Reference Arrays do you see?
    21. Answer – How Many Row Reference Arrays do you see?
    22. Quiz – How Many Row Reference Arrays are there Now?
    23. Answer – How Many Row Reference Arrays do you see?
    24. Quiz – How Many Row Reference Arrays in Total?
    25. Answer – How Many Row Reference Arrays in Total?
    26. Quiz – How Many Cylinder Indexes are here?
    27. Answer – How Many Cylinder Indexes are here?
    28. A More Detailed Illustration of the Master Index
    29. A Real-World View of the Master Index
    30. An Even More Realistic View of an AMP's Master Index
    31. The Cylinder Index
    32. An Even More Realistic View of a Cylinder Index
    33. How a Query using the Primary Index works
    34. How the AMPs Do a Full Table Scan
    35. How an AMP Reads Using a Primary Index
  13. Chapter 7 - Partition Primary Index (PPI) Tables
    1. The Concept behind Partitioning a Table
    2. Creating a PPI Table with Simple Partitioning
    3. A Visual Display of Simple Partitioning
    4. An SQL Example that explains Simple Partitioning
    5. Creating a PPI Table with RANGE_N Partitioning per Month
    6. A Visual of One Year of Data with Range_N per Month
    7. An SQL Example explaining Range_N Partitioning per Month
    8. A Partition # and Row-ID = Row Key
    9. An AMP Stores its Rows Sorted in only Two Different Ways
    10. Creating a PPI Table with RANGE_N Partitioning per Day
    11. A Visual of Range_N Partitioning Per Day
    12. An SQL Example that explains Range_N Partitioning per Day
    13. Creating a PPI Table with RANGE_N Partitioning per Week
    14. A Visual of Range_N Partitioning Per Week
    15. SQL Example that explains Range_N Partitioning per Week
    16. A Clever Range_N Option
    17. Creating a PPI Table with CASE_N
    18. A Visual of Case_N Partitioning
    19. An SQL Example that explains CASE_N Partitioning
    20. How many partitions do you see?
    21. Number of PPI Partitions Allowed
    22. How many partitions do you see?
    23. NO CASE and UNKNOWN Partitions Together
    24. A Visual of Case_N Partitioning
    25. Combining Older Data and Newer Data in PPI
    26. A Visual for Combining Older Data and Newer Data in PPI
    27. The SQL on Combining Older Data and Newer Data in PPI
    28. Multi-Level Partitioning Combining Range_N and Case_N
    29. A Visual of Multi-Level Partitioning
    30. The SQL on a Multi-Level Partitioned Primary Index
    31. NON-Unique Primary Indexes (NUPI) in PPI
    32. PPI Table with a Unique Primary Index (UPI)
    33. Tricks for Non-Unique Primary Indexes (NUPI)
    34. Character Based PPI for RANGE_N
    35. A Visual for Character-Based PPI for RANGE_N
    36. The SQL on Character-Based PPI for RANGE_N
    37. Character-Based PPI for CASE_N
    38. Dates and Character-Based Multi-Level PPI
    39. TIMESTAMP Partitioning
    40. Using CURRENT_DATE to define a PPI
    41. ALTER to CURRENT_DATE the next year
    42. ALTER to CURRENT_DATE with Save
    43. Altering a PPI Table to Add or Drop Partitions
    44. Deleting a Partition
    45. Deleting a Partition and saving its contents
    46. Using the PARTITION Keyword in your SQL
    47. SQL for RANGE_N
    48. SQL for CASE_N
  14. Chapter 8 - Columnar Tables
    1. Columnar Tables have NO Primary Index
    2. This is NOT a NoPI Table
    3. NoPI Tables Spread rows across all-AMPs Evenly
    4. NoPI Tables used as Staging Tables for Data Loads
    5. NoPI Table Capabilities
    6. NoPI Table Restrictions
    7. What does a Columnar Table look like?
    8. Comparing Normal Table vs. Columnar Tables
    9. Columnar Table Fundamentals
    10. Example of Columnar CREATE Statement
    11. Columnar can move just One Container to Memory
    12. Containers on AMPs match up perfectly to rebuild a Row
    13. Indexes can be used on Columns (Containers)
    14. Indexes can be used on Columns (Containers)
    15. Visualize a Columnar Table
    16. Single-Column vs. Multi-Column Containers
    17. Comparing Normal Table vs. Columnar Tables
    18. Columnar Row Hybrid CREATE Statement
    19. Columnar Row Hybrid Example
    20. Columnar Row Hybrid Query Example
    21. Review of Row-Based Partition Primary Index (PPI)
    22. Visual of Row Partitioning (PPI Tables) by Month
    23. CREATE Statement for both Row and Column Partition
    24. Visual of Row Partitioning (PPI Tables) and Columnar
    25. How to Load into a Columnar Table
    26. Columnar NO AUTO COMPRESS
    27. Auto Compress in Columnar Tables
    28. Auto Compress Techniques in Columnar Tables
    29. When and When NOT to use Columnar Tables
    30. Did you know?
  15. Chapter 9 - Space
    1. When your System Arrives, there is only User named DBC
    2. USER DBC
    3. First Assignment is to create another User just under DBC
    4. USER DBC
    5. Perm and Spool Space
    6. Perm Space is for Permanent Tables
    7. Spool Space is work space that builds a User's Answer Sets
    8. Spool Space is in an AMP's Memory and on its Disk
    9. Users are Assigned Spool Space Limits
    10. What is the Purpose of Spool Limits?
    11. Why did my query Abort and say “Out of Spool”?
    12. How can Skewed Data cause me to run “Out of Spool”?
    13. Why did my Join cause me to run “Out of Spool”?
    14. Finding out how much Space you have
    15. Space per AMP on all tables in a Database shows Skew
    16. What does my system look like when it first arrives?
    17. DBC owns all the PERM Space in the system on day one
    18. DBC's First Assignment is Spool Space
    19. DBC's 2nd Assignment is to CREATE Users and Databases
    20. The Teradata Hierarchy Begins
    21. The Teradata Hierarchy Continues
    22. Differences between PERM and SPOOL
    23. Databases, Users, and Views
    24. What are Similarities between a DATABASE and a USER?
    25. What is the Difference between a DATABASE and a USER?
    26. Objects that take up PERM Space
    27. A Series of Quizzes on Adding and Subtracting Space
    28. Answer 1 to Quiz on Space
    29. Space Transfer Quiz
    30. Answer to Space Transfer Quiz
    31. Drop Space Quiz
    32. Answers to Drop Space Quiz
  16. Chapter 10 - The User Environment
    1. DBC is the only user when the system first arrives
    2. DBC will Create Databases and Give them Space
    3. DBC will create some initial Users
    4. A Typical Teradata Environment
    5. What are Similarities between a DATABASE and a USER?
    6. Roles
    7. Create a Role and then Assign that Role Its Access Rights
    8. Create a User and Assign them a Default Role
    9. Granting Access Rights
    10. There are Three Types of Access Rights
    11. Description of the Three Types of Access Rights
    12. Profiles
    13. Creating a Profile and a User
    14. ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
    15. Accounts and their Associated Priorities
    16. Creating a User with Multiple Account Priorities
    17. Account String Expansion (ASE)
    18. The DBC.AMPUsage View
    19. Teradata TASM provides a User Traffic System
    20. Teradata Viewpoint
  17. Chapter 11 - Secondary Indexes
    1. Creating a Unique Secondary Index (USI)
    2. What is in a Unique Secondary Index (USI) Subtable?
    3. A Unique Secondary Index (USI) Subtable is hashed
    4. How the Parsing Engine uses the USI Subtable
    5. A USI is a Two-AMP Operation
    6. Creating a Non-Unique Secondary Index (NUSI)
    7. What is in a Unique Secondary Index (USI) Subtable?
    8. Non-Unique Secondary Index (NUSI) Subtable is AMP Local
    9. How the Parsing Engine uses the NUSI Subtable
    10. Creating a Value-Ordered NUSI
    11. The Hash Map Determines which AMP will own the Row
    12. A Unique Primary Index Spreads the Data Evenly
    13. Quiz – Answer the Tough USI Questions
    14. Answer to Quiz – Answer the Tough USI Questions
    15. A Picture with a Base Table, USI, and NUSI Subtable
    16. Quiz – Tough Questions on the USI and NUSI Subtables
    17. Answer – Tough Questions on the USI and NUSI Subtables
    18. A Query Using an USI Only Moves Two Blocks
    19. A Query Using A NUSI Always Uses All AMPs
    20. Two Non-Unique Secondary Indexes (NUSI) on a Table
    21. A NUSI BITMAP Query (1 of 3)
    22. A NUSI BITMAP Theory (2 of 3)
    23. A NUSI Bitmap in Action (3 of 3)
    24. A Brilliant Technique for a Unique Secondary Index
    25. The USI for Partitioned Tables Points to the Row Key
    26. A Brilliant Technique for a Non-Unique Secondary Index
    27. The NUSI for Partitioned Tables Points to the Row Key
    28. How the PE Decides on the NUSI or the Full Table Scan
    29. The Bigger Quiz
    30. The Bigger Quiz Answers
    31. Multiple Choice DBA
    32. Multiple Choice DBA
    33. What are the Big Four Tactical Queries?
    34. What are the Big Four Tactical Queries?
  18. Chapter 12 - Temporal Tables Create Functions
    1. Three types of Temporal Tables
    2. CREATING a Bi-Temporal Table
    3. PERIOD Data Types
    4. Bi-Temporal Data Type Standards
    5. Bi-Temporal Example – Tera-Tom buys!
    6. A Look at the Temporal Results
    7. Bi-Temporal Example – Tera-Tom Sells!
    8. Bi-Temporal Example – How the data looks!
    9. Normal SQL for Bi-Temporal Tables
    10. NONSEQUENCED SQL for Temporal Tables
    11. AS OF SQL for Temporal Tables
    12. NONSEQUENCED for Both
    13. Creating Views for Temporal Tables
    14. Bi-Temporal Example – Socrates is DELETED!
    15. Bi-Temporal Results – Socrates is DELETED
  19. Chapter 13 - How Joins Work Internally
    1. Teradata Join Quiz
    2. Teradata Join Quiz Answer
    3. The Joining of Two Tables
    4. Teradata Moves Joining Rows to the Same AMP
    5. Imagine Joining Two NoPI Tables that have No Primary Index
    6. Both Tables are redistributed to Join Rows on the Same AMP
    7. How do you join if One Table is Big and One Table is Small?
    8. Duplicate the Small Table on Every AMP (like a mirror)
    9. What Could You Do If Two Tables Joined 1000 Times a Day?
    10. Joining Two Tables with the same PK/FK Primary Index
    11. A Join with No Redistribution or Duplication
    12. A Performance Tuning Technique for Large Joins
    13. The Joining of Two Tables with an Additional WHERE Clause
    14. An Example of the Fastest Join Possible
    15. Using a Simple Volatile Table
    16. A Volatile Table with a Primary Index
    17. Using a Simple Global Temporary Table
    18. Two Brilliant Techniques for Global Temporary Tables
    19. The Joining of Two Tables Using a Global Temporary Table
    20. Quiz – How Much Data Moves Across the BYNET?
    21. Answer – How Much Data Moves Across the BYNET?
    22. Teradata V14.10 Join Feature PRPD
  20. Chapter 14 - Join Indexes
    1. Creating a Multi-Table Join Index
    2. Visual of a Join Index
    3. Outer Join Multi-Table Join Index
    4. Visual of a Left Outer Join Index
    5. Compressed Multi-Table Join Index
    6. A Visual of a Compressed Multi-Table Join Index
    7. Creating a Single-Table Join Index
    8. Conceptual of a Single Table Join Index on an AMP
    9. Single Table Join Index Great For LIKE Clause
    10. Single Table Join Index with Value Ordered NUSI
    11. Aggregate Join Indexes
    12. Compressed Single-Table Join Index
    13. Aggregate Join Index
    14. New Aggregate Join Index (Teradata V14.10)
    15. Sparse Join Index
    16. A Global Multi-Table Join Index
    17. Creating a Hash Index
    18. Join Index Details
  21. Chapter 15 - Basic SQL Functions
    1. Introduction
    2. SELECT * (All Columns) in a Table
    3. SELECT Specific Columns in a Table
    4. Using the Best Form for Writing SQL
    5. Commas in the Front or in the 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
  22. Chapter 16 - 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. NULL means UNKNOWN DATA so Equal (=) won't Work
    7. Use IS NULL or IS NOT NULL when dealing with NULLs
    8. NULL is UNKNOWN DATA so NOT Equal won't Work
    9. Use IS NULL or IS NOT NULL when dealing with NULLs
    10. Using Greater Than OR Equal To (>=)
    11. Using GE as Greater Than or Equal To (>=)
    12. AND in the WHERE Clause
    13. Troubleshooting AND
    14. OR in the WHERE Clause
    15. Troubleshooting OR
    16. OR must utilize the Column Name Each Time
    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. Using a NOT IN List
    27. A Technique for Handling Nulls with a NOT IN List
    28. An IN List with the Keyword ANY
    29. A NOT IN List with the Keywords NOT = ALL
    30. BETWEEN is Inclusive
    31. BETWEEN Works for Character Data
    32. LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
    33. LIKE command Underscore is Wildcard for one Character
    34. LIKE ALL means ALL conditions must be Met
    35. LIKE ANY means ANY of the Conditions can be Met
    36. IN ANSI Transaction Mode Case Matters
    37. In Teradata Transaction Mode Case Doesn't Matter
    38. LIKE Command Works Differently on Char Vs. Varchar
    39. Troubleshooting LIKE Command on Character Data
    40. Introducing the TRIM Command
    41. Quiz – Which Data is Left Justified and Which is Right?
    42. Numbers are Right Justified and Character Data is Left
    43. Answer – Which Data is Left Justified and Which is Right?
    44. An Example of Data with Left and Right Justification
    45. A Visual of CHARACTER Data vs. VARCHAR Data
    46. Use the TRIM command to remove spaces on CHAR Data
    47. TRIM Eliminates Leading and Trailing Spaces
    48. Escape Character in the LIKE Command changes Wildcards
    49. Escape Characters Turn off Wildcards in the LIKE Command
    50. Quiz – Turn off that Wildcard
    51. ANSWER – To Find that Wildcard
  23. Chapter 17 - Distinct Vs. Group By
    1. The Distinct Command
    2. Distinct vs. GROUP BY
    3. Rules of Thumb for DISTINCT vs. GROUP BY
    4. GROUP BY Vs. DISTINCT – Good Advice
    5. Quiz – How many rows come back from the Distinct?
    6. Answer – How many rows come back from the Distinct?
  24. Chapter 18 - The TOP Command
    1. TOP Command
    2. TOP Command is brilliant when ORDER BY is used!
    3. The TOP Command WITH TIES
    4. How the TOP Command WITH TIES Decides
    5. The TOP Command will NOT work with Certain Commands
  25. Chapter 19 - Review
    1. Testing Your Knowledge 1
    2. Testing Your Knowledge 2
    3. Testing Your Knowledge 3
    4. Testing Your Knowledge 4
    5. Testing Your Knowledge 5
    6. Testing Your Knowledge 6
    7. Testing Your Knowledge 7
  26. Chapter 20 - 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
  27. Chapter 21 - Aggregation Function
    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. Troubleshooting Aggregates
    7. GROUP BY when Aggregates and Normal Columns Mix
    8. GROUP BY Delivers one row per Group
    9. GROUP BY Dept_No or GROUP BY 1 the same thing
    10. Limiting Rows and Improving Performance with WHERE
    11. WHERE Clause in Aggregation limits unneeded Calculations
    12. Keyword HAVING tests Aggregates after they are Totaled
    13. Keyword HAVING is like an Extra WHERE Clause for Totals
    14. Getting the Average Values per Column
    15. Average Values per Column for All Columns in a Table
    16. Three types of Advanced Grouping
    17. GROUP BY Grouping Sets
    18. GROUP BY Rollup
    19. GROUP BY Rollup Result Set
    20. GROUP BY Cube
    21. GROUP BY CUBE Result Set
    22. Use the Nexus for all Groupings
    23. Testing Your Knowledge – Basic Aggregation
    24. Testing Your Knowledge – Multiple Aggregates
    25. Testing Your Knowledge- Group By
    26. Testing Your Knowledge – Using a Where Clause
    27. Testing Your Knowledge- Using Having
    28. Final Answer to Test Your Knowledge on Aggregates
  28. Chapter 22 - Join Functions
    1. A two-table join using Non-ANSI Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. Aliases and Fully Qualifying Columns
    4. A two-table join using ANSI Syntax
    5. Both Queries have the same Results and Performance
    6. Quiz – Can You Finish the Join Syntax?
    7. Answer to Quiz – Can You Finish the Join Syntax?
    8. Quiz – Can You Find the Error?
    9. Answer to Quiz – Can You Find the Error?
    10. Quiz – Which rows from both tables Won't Return?
    11. Answer to Quiz – Which rows from both tables Won't Return?
    12. LEFT OUTER JOIN
    13. LEFT OUTER JOIN Brings Back All Rows in the Left Table
    14. RIGHT OUTER JOIN
    15. RIGHT OUTER JOIN Brings Back All Rows in the RIGHT Table
    16. FULL OUTER JOIN
    17. FULL OUTER JOIN Brings Back All Rows in All Tables
    18. Which Tables are the Left and which are the Right?
    19. Answer - Which Tables are the Left and which are the Right?
    20. INNER JOIN with Additional AND Clause
    21. ANSI INNER JOIN with Additional AND Clause
    22. ANSI INNER JOIN with Additional WHERE Clause
    23. OUTER JOIN with Additional WHERE Clause
    24. OUTER JOIN with Additional AND Clause
    25. Results from OUTER JOIN with Additional AND Clause
    26. Quiz – Why is this considered an INNER JOIN?
    27. The DREADED Product Join
    28. Result Set of the DREADED Product Join
    29. The Horrifying Cartesian Product Join
    30. The ANSI Cartesian Join will ERROR
    31. Quiz – Do these Joins Return the Same Answer Set?
    32. Answer – Do these Joins Return the Same Answer Set?
    33. The CROSS JOIN
    34. The CROSS JOIN Answer Set
    35. The Self Join
    36. The Self Join with ANSI Syntax
    37. Quiz – Will both queries bring back the same Answer Set?
    38. Answer – Will both queries bring back the same Answer Set?
    39. Quiz – Will both queries bring back the same Answer Set?
    40. Answer – Will both queries bring back the same Answer Set?
    41. How would you Join these two tables?
    42. How would you Join these two tables? You Can't Yet!
    43. An Associative Table is a Bridge that Joins Two Tables
    44. Quiz – Can you Write the 3-Table Join?
    45. Answer to Quiz – Can you Write the 3-Table Join?
    46. Quiz – Can you Write the 3-Table Join to ANSI Syntax?
    47. Answer – Can you Write the 3-Table Join to ANSI Syntax?
    48. Quiz – Can you Place the ON Clauses at the End?
    49. Answer – Can you Place the ON Clauses at the End?
    50. The 5-Table Join – Logical Insurance Model
    51. Quiz - Write a Five Table Join Using ANSI Syntax
    52. Answer - Write a Five Table Join Using ANSI Syntax
    53. Quiz - Write a Five Table Join Using ANSI Syntax
    54. Answer - Write a Five Table Join Using ANSI Syntax
    55. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    56. Answer - Write a Five Table Join Using Non-ANSI Syntax
    57. Quiz –Re-Write this putting the ON clauses at the END
    58. Answer –Re-Write this putting the ON clauses at the END
    59. The Nexus Query Chameleon Writes the SQL for Users.
  29. Chapter 23 - Date Functions
    1. Date, Time, and Current_Timestamp Keywords
    2. Dates are stored internally as INTEGERS from a Formula
    3. Displaying Dates for INTEGERDATE and ANSIDATE
    4. DATEFORM
    5. Changing the DATEFORM in Client Utilities such as BTEQ
    6. Date, Time, and Timestamp Recap
    7. Timestamp Differences
    8. Finding the Number of Hours between Timestamps
    9. Troubleshooting Timestamp
    10. Add or Subtract Days from a date
    11. A Summary of Math Operations on Dates
    12. Using a Math Operation to find your 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 5 Years
    17. The EXTRACT Command
    18. EXTRACT from DATES and TIME
    19. CURRENT_DATE and EXTRACT or Current_Date and Math
    20. CAST the Date of January 1, 2011 and the Year 1800
    21. The System Calendar
    22. Using the System Calendar in Its Simplest Form
    23. How to really use the Sys_Calendar.Calendar
    24. Storing Dates Internally
    25. Storing Time Internally
    26. Storing TIME with TIME ZONE Internally
    27. Storing Timestamp Internally
    28. Storing Timestamp with TIME ZONE Internally
    29. Storing Date, Time, and Timestamp with Zone Internally
    30. Time Zones
    31. Setting Time Zones
    32. Seeing your Time Zone
    33. Creating a Sample Table for Time Zone Examples
    34. Inserting Rows in the Sample Table for Time Zone Examples
    35. Selecting the Data from our Time Zone Table
    36. Normalizing our Time Zone Table with a CAST
    37. Intervals for Date, Time and Timestamp
    38. Interval Data Types and the Bytes to Store Them
    39. The Basics of a Simple Interval
    40. Troubleshooting the Basics of a Simple Interval
    41. Interval Arithmetic Results
    42. A Date Interval Example
    43. A Time Interval Example
    44. A - DATE Interval Example
    45. A Complex Time Interval Example using CAST
    46. A Complex Time Interval Example using CAST
    47. The OVERLAPS Command
    48. An OVERLAPS Example that Returns No Rows
    49. The OVERLAPS Command using TIME
    50. The OVERLAPS Command using a NULL Value
  30. Chapter 24 - 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 Z
    23. Formatting with Z Visual
    24. Formatting with 9
    25. Formatting with 9 Visual
    26. Formatting with $
    27. Formatting with $ Visual
    28. Formatting with $ and Commas
    29. Formatting with $ and Commas Visual
    30. Formatting with $ and Commas and 9
    31. Formatting with $ and Commas and 9 with Zero Dollars
    32. A Great Formatting Example
    33. A Great Formatting Example for Day, Month, and Year
    34. A Trick to get SQL Assistant to Format Data
    35. Using the CASESPECIFIC (CS) Command in Teradata Mode
    36. Using NOT CASESPECIFIC (CS) in ANSI Mode
    37. Using the LOWER Command
    38. Using the UPPER Command
  31. Chapter 25 - OLAP Functions
    1. On-Line Analytical Processing (OLAP) or Ordered Analytics
    2. Cumulative Sum (CSUM) Command and how OLAP Works
    3. OLAP Commands always Sort (ORDER BY) in the Command
    4. Calculate the Cumulative Sum (CSUM) after Sorting the Data
    5. The OLAP Major Sort Key
    6. The OLAP Major Sort Key and the Minor Sort Key(s)
    7. Troubleshooting OLAP – My Data isn't coming back correct
    8. GROUP BY in Teradata OLAP Syntax Resets on the Group
    9. CSUM the Number 1 to get a Sequential Number
    10. A Single GROUP BY Resets each OLAP with Teradata Syntax
    11. A Better Choice – The ANSI Version of CSUM
    12. The ANSI Version of CSUM – The Sort Explained
    13. The ANSI CSUM – Rows Unbounded Preceding Explained
    14. The ANSI CSUM – Making Sense of the Data
    15. The ANSI CSUM – Making Even More Sense of the Data
    16. The ANSI CSUM – The Major and Minor Sort Key(s)
    17. The ANSI CSUM – Getting a Sequential Number
    18. Troubleshooting the ANSI OLAP on a GROUP BY
    19. The ANSI OLAP – Reset with a PARTITION BY Statement
    20. PARTITION BY only Resets a Single OLAP not ALL of them
    21. The Moving SUM (MSUM) and Moving Window
    22. How the Moving Sum is calculated
    23. How the Sort works for Moving SUM (MSUM)
    24. GROUP BY in the Moving SUM does a Reset
    25. Quiz – Can you make the Advanced Calculation in your mind?
    26. Answer to Quiz for the Advanced Calculation in your mind?
    27. Quiz – Write that Teradata Moving Average in ANSI Syntax
    28. Both the Teradata Moving SUM and ANSI Version
    29. The ANSI Moving Window is Current Row and Preceding
    30. How ANSI Moving Average Handles the Sort
    31. Quiz – How is that Total Calculated?
    32. Answer to Quiz – How is that Total Calculated?
    33. Moving SUM every 3-rows Vs. a Continuous Average
    34. Partition BY Resets an ANSI OLAP
    35. The Moving Average (MAVG) and Moving Window
    36. How the Moving Average is calculated
    37. How the Sort works for Moving Average (MAVG)
    38. GROUP BY in the Moving Average does a Reset
    39. Quiz – Can you make the Advanced Calculation in your mind?
    40. Answer to Quiz for the Advanced Calculation in your mind?
    41. Quiz – Write that Teradata Moving Average in ANSI Syntax
    42. Both the Teradata Moving Average and ANSI Version
    43. The ANSI Moving Window is Current Row and Preceding
    44. How ANSI 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 (MDIFF) Visual
    53. Moving Difference using ANSI Syntax
    54. Moving Difference using ANSI Syntax with Partition By
    55. Trouble Shooting the Moving Difference (MDIFF)
    56. Using the RESET WHEN Option in Teradata (V13)
    57. How Many Months per Product_ID has Revenue Increased?
    58. The RANK Command
    59. How to get Rank to Sort in Ascending Order
    60. Two ways to get Rank to Sort in Ascending Order
    61. RANK using ANSI Syntax Defaults to Ascending Order
    62. Getting RANK using ANSI Syntax to Sort in DESC Order
    63. RANK () OVER and PARTITION BY
    64. RANK () OVER and QUALIFY
    65. RANK () OVER and PARTITION BY with a QUALIFY
    66. QUALIFY and WHERE
    67. Quiz – How can you simplify the QUALIFY Statement
    68. Answer to Quiz –Can you simplify the QUALIFY Statement
    69. The QUALIFY Statement without Ties
    70. The QUALIFY Statement with Ties
    71. The QUALIFY Statement with Ties Brings back Extra Rows
    72. Mixing Sort Order for QUALIFY Statement
    73. Quiz – What Caused the RANK to Reset?
    74. Answer to Quiz – What Caused the RANK to Reset?
    75. Quiz – Name those Sort Orders
    76. Answer to Quiz – Name those Sort Orders
    77. PERCENT_RANK () OVER
    78. PERCENT_RANK () OVER with 14 rows in Calculation
    79. PERCENT_RANK () OVER with 21 rows in Calculation
    80. Quiz – What Cause the Product_ID to Reset
    81. Answer to Quiz – What Causes the Product_ID to Reset
    82. Answer to Quiz – What Causes the Product_ID to Reset
    83. COUNT OVER for a Sequential Number
    84. Troubleshooting COUNT OVER
    85. Quiz – What caused the COUNT OVER to Reset?
    86. Answer to Quiz – What caused the COUNT OVER to Reset?
    87. The MAX OVER Command
    88. MAX OVER with PARTITION BY Reset
    89. Troubleshooting MAX OVER
    90. The MIN OVER Command
    91. Troubleshooting MIN OVER
    92. Finding a Value of a Column in the Next Row with MIN
    93. Finding a Value of a Date in the Next Row with MIN
    94. Finding Gaps between Dates
    95. The CSUM for Each Product_ID for the First 3 Days
    96. Quiz – Fill in the Blank
    97. Answer to Quiz – Fill in the Blank
    98. The Row_Number Command
    99. Quiz – How did the Row_Number Reset?
    100. Quiz – How did the Row_Number Reset?
    101. Row_Number with Qualify to get the Typical Rows per Value
    102. A Second Typical Rows per Value Query on Sale_Date
    103. Testing Your Knowledge
    104. Testing Your Knowledge
    105. Testing Your Knowledge
    106. Testing Your Knowledge
    107. Testing Your Knowledge
    108. Testing Your Knowledge
  32. Chapter 26 - 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
  33. Chapter 27 - 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. Most Derived Tables Are Used To Join To Other Tables
    6. Multiple Ways to Alias the Columns in a Derived Table
    7. Our Join Example with a Different Column Aliasing Style
    8. Column Aliasing Can Default for Normal Columns
    9. CREATING a Derived Table using the WITH Command
    10. Our Join Example With the WITH Syntax
    11. The Same Derived Query shown Three Different Ways
    12. Quiz - Answer the Questions
    13. Answer to Quiz - Answer the Questions
    14. Clever Tricks on Aliasing Columns in a Derived Table
    15. A Derived Table lives only for the lifetime of a single query
    16. An Example of Two Derived Tables in a Single Query
    17. WITH RECURSIVE Derived Table
    18. Defining the WITH Recursive Derived Table
    19. Looping Through the WITH Recursive Derived Table
    20. Looping Through the WITH Recursive Derived Table
    21. Looping Through the WITH Recursive Derived Table
    22. Looping Through the WITH Recursive Derived Table
    23. Looping Through the WITH Recursive Derived Table
    24. Creating a Volatile Table
    25. You Populate a Volatile Table with an INSERT/SELECT
    26. The Three Steps to Use a Volatile Table
    27. Why Would You Use the ON COMMIT DELETE ROWS?
    28. The HELP Volatile Table Command Shows your Volatiles
    29. A Volatile Table with a Primary Index
    30. The Joining of Two Tables Using a Volatile Table
    31. You Can Collect Statistics on Volatile Tables
    32. The New Teradata V14 Way to Collect Statistics
    33. Four Examples of Creating a Volatile Table Quickly
    34. Four Advanced Examples of Creating a Volatile Table Quickly
    35. Creating Partitioned Primary Index (PPI) Volatile Tables
    36. Using a Volatile Table to Get Rid of Duplicate Rows
    37. Using a Simple Global Temporary Table
    38. Two Brilliant Techniques for Global Temporary Tables
    39. The Joining of Two Tables Using a Global Temporary Table
    40. CREATING A Global Temporary Table
  34. Chapter 28 - 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. How a Basic Subquery Works
    6. The Final Answer Set from the Subquery
    7. Quiz- Answer the Difficult Question
    8. Answer to Quiz- Answer the Difficult Question
    9. Should you use a Subquery of a Join?
    10. Quiz- Write the Subquery
    11. Answer to Quiz- Write the Subquery
    12. Quiz- Write the More Difficult Subquery
    13. Answer to Quiz- Write the More Difficult Subquery
    14. Quiz- Write the Subquery with an Aggregate
    15. Answer to Quiz- Write the Subquery with an Aggregate
    16. Quiz- Write the Correlated Subquery
    17. Answer to Quiz- Write the Correlated Subquery
    18. The Basics of a Correlated Subquery
    19. The Top Query always runs first in a Correlated Subquery
    20. The Bottom Query runs last in a Correlated Subquery
    21. Quiz- Who is coming back in the Final Answer Set?
    22. Answer- Who is coming back in the Final Answer Set?
    23. Correlated Subquery Example vs. a Join with a Derived Table
    24. Quiz- A Second Chance to Write a Correlated Subquery
    25. Answer - A Second Chance to Write a Correlated Subquery
    26. Quiz- A Third Chance to Write a Correlated Subquery
    27. Answer - A Third Chance to Write a Correlated Subquery
    28. Quiz- Last Chance to Write a Correlated Subquery
    29. Answer – Last Chance to Write a Correlated Subquery
    30. Correlated Subquery that Finds Duplicates
    31. Quiz- Write the NOT Subquery
    32. Answer to Quiz- Write the NOT Subquery
    33. Quiz- Write the Subquery using a WHERE Clause
    34. Answer - Write the Subquery using a WHERE Clause
    35. Quiz- Write the Subquery with Two Parameters
    36. Answer to Quiz- Write the Subquery with Two Parameters
    37. How the Double Parameter Subquery Works
    38. More on how the Double Parameter Subquery Works
    39. Quiz – Write the Triple Subquery
    40. Answer to Quiz – Write the Triple Subquery
    41. Quiz – How many rows return on a NOT IN with a NULL?
    42. Answer – How many rows return on a NOT IN with a NULL?
    43. How to handle a NOT IN with Potential NULL Values
    44. IN is equivalent to =ANY
    45. Using a Correlated Exists
    46. How a Correlated Exists matches up
    47. The Correlated NOT Exists
    48. The Correlated NOT Exists Answer Set
    49. Quiz – How many rows come back from this NOT Exists?
    50. Answer – How many rows come back from this NOT Exists?
  35. Chapter 29 - Substrings 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 and Trailing is Case Sensitive
    10. Trim and Trailing works if Case right
    11. Trim Combined with the CHARACTERS Command
    12. How to TRIM only the Trailing Spaces
    13. How to TRIM Trailing Letters
    14. How to TRIM Trailing Letters and use CHARACTER_Length
    15. The SUBSTRING Command
    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. SUBSTRING and SUBSTR are equal, but use differe,nt syntax
    22. The POSITION Command finds a Letters Position
    23. The POSITION Command is brilliant with SUBSTRING
    24. Quiz – Name that SUBSTRING Starting and For Length
    25. The POSITION Command is brilliant with SUBSTRING
    26. Quiz – Name that SUBSTRING Starting and For Length
    27. Answer to Quiz – Name that Starting and For Length
    28. Answer to Quiz – Name that Starting and For Length
    29. Using the SUBSTRING to Find the Second Word On
    30. Quiz – Why did only one Row Return
    31. Answer to Quiz – Why Did only one Row Return
    32. Concatenation
    33. Concatenation and SUBSTRING
    34. Four Concatenations Together
    35. Troubleshooting Concatenation
  36. Chapter 30 - 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. Answer to Quiz – Fill in the Blank Values in the Answer Set
    7. Quiz – Fill in the Answers for the NULLIF Command
    8. Quiz – Fill in the Answers for the NULLIF Command
    9. The ZEROIFNULL Command
    10. Answer to the ZEROIFNULL Question
    11. The COALESCE Command
    12. The COALESCE Answer Set
    13. The Coalesce Quiz
    14. Answers to the Coalesce Quiz
    15. The Basics of CAST (Convert and Store)
    16. Some Great CAST (Convert and Store) Examples
    17. Some Great CAST (Convert and Store) Examples
    18. Some Great CAST (Convert and Store) Examples
    19. A Teradata Extension – The Implied Cast
    20. The Basics of the CASE Statements
    21. The Basics of the CASE Statement shown visually
    22. Valued Case vs. Searched Case
    23. Quiz - Valued Case Statement
    24. Answer - Valued Case Statement
    25. Quiz - Searched Case Statement
    26. Answer - Searched Case Statement
    27. Quiz - When NO ELSE is present in CASE Statement
    28. Answer - When NO ELSE is present in CASE Statement
    29. When an ELSE is present in CASE Statement
    30. When NO ELSE is present in CASE Statement
    31. When an Alias is NOT used in a CASE Statement
    32. When an Alias is NOT used in a CASE Statement
    33. When NO ELSE is present in 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
  37. Chapter 31 - View Functions
    1. Creating a Simple View
    2. Basic Rules for Views
    3. How to Modify a View
    4. Exceptions to the ORDER BY Rule inside a View
    5. How to Get HELP with a View
    6. Views sometimes CREATED for Formatting or Row Security
    7. Another Way to Alias Columns in a View CREATE
    8. Resolving Aliasing Problems in a View CREATE
    9. Resolving Aliasing Problems in a View CREATE
    10. Resolving Aliasing Problems in a View CREATE
    11. CREATING Views for Complex SQL such as Joins
    12. WHY certain columns need Aliasing in a View
    13. Aggregates on View Aggregates
    14. Locking Row for Access
    15. Creating Views for Temporal Tables
    16. Altering a Table
    17. Altering a Table after a View has been created
    18. A View that errors After an ALTER
    19. Troubleshooting a View
    20. Updating Data in a Table through a View
    21. Maintenance Restrictions on a Table through a View
  38. Chapter 32 - Macro Functions
    1. The 14 rules of Macros
    2. CREATING and EXECUTING a Simple Macro
    3. Multiple SQL Statements inside a Macro
    4. Complex Joins inside a Macro
    5. Passing an INPUT Parameter to a Macro
    6. Troubleshooting a Macro with INPUT Parameters
    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. Troubleshooting a Macro
  39. Chapter 33 - Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Minus Explained Logically
    11. Minus Explained Logically
    12. Testing Your Knowledge
    13. Testing Your Knowledge
    14. An Equal Amount of Columns in both SELECT List
    15. Columns in the SELECT list should be from the same Domain
    16. The Top Query handles all Aliases
    17. The Bottom Query does the ORDER BY (a Number)
    18. Great Trick: Place your Set Operator in a Derived Table
    19. UNION vs. UNION ALL
    20. UNION vs. UNION ALL Example
    21. Using UNION ALL and Literals
    22. A Great Example of how EXCEPT works
    23. USING Multiple SET Operators in a Single Request
    24. Changing the Order of Precedence with Parentheses
    25. Using UNION ALL for speed in Merging Data Sets
    26. Using UNION to be same as GROUP BY GROUPING SETS
    27. Using UNION to be same as GROUP BY ROLLUP
    28. Using UNION to be the same as GROUP BY Cube
    29. Using UNION to be same as GROUP BY Cube
    30. Using UNION to be same as GROUP BY Cube
  40. Chapter 34 - Creating Tables, Secondary Indexes, and Join Indexes
    1. Creating a Table with a Unique Primary Index
    2. Creating a Table with a Non-Unique Primary Index
    3. Creating a Table without entering a Primary Index
    4. Creating a Table with NO Primary Index
    5. Creating a Set Table
    6. Creating a Multiset Table
    7. Creating a Set Table with a Unique Primary Index
    8. Creating a Set Table with a Unique Secondary Index
    9. Creating a Table with an UPI and USI
    10. Creating a Table with a Multicolumn Primary Index
    11. Creating a Unique Secondary Index (USI) after a table is created
    12. Creating a Non-Unique Secondary Index (NUSI) after a table is created
    13. Creating a Value-Ordered NUSI
    14. Data Types
    15. Data Types Continued
    16. Data Types Continued
    17. Major Data Types and the number of Bytes they take up
    18. Making an exact copy a Table
    19. Making a NOT-So-Exact Copy a Table
    20. Copying a Table
    21. Troubleshooting Copying and Changing the Primary Index
    22. Copying only specific columns of a table
    23. Copying a Table and Keeping the Statistics
    24. Copying a Table with Statistics
    25. Copying a table Structure with NO Data but Statistics
    26. Creating a Table with Fallback
    27. Creating a Table with No Fallback
    28. Creating a Table with a Before Journal
    29. Creating a Table with a Dual Before Journal
    30. Creating a Table with an After Journal
    31. Creating a Table with a Dual After Journal
    32. Creating a Table with a Journal Keyword Alone
    33. Why Use Journaling?
    34. Why Use Journaling?
    35. Creating a Table with Customization of the Data Block Size
    36. Creating a Table with Customization with FREESPACE Percent
    37. Creating a QUEUE Table
    38. Example of how a Queue Table Works
    39. Example of how a Queue Table Works
    40. Creating a Columnar Table
    41. Creating a Columnar Table with Multi-Column Containers
    42. Creating a Columnar Table with a Row Hybrid
    43. Creating a Columnar Table with both Row and Column Partitions
    44. How to Load into a Columnar Table
    45. Creating a Columnar Table with NO AUTO COMPRESS
    46. CREATING a Bi-Temporal Table
    47. Explaining Bi-Temporal PERIOD Data Types
    48. Creating a PPI Table with Simple Partitioning
    49. Creating a PPI Table with RANGE_N Partitioning per Day
    50. Creating a PPI Table with RANGE_N Partitioning per Month
    51. A Visual of One Year of Data with Range_N per Month
    52. Creating a PPI Table with RANGE_N Partitioning per Week
    53. A Clever Range_N Option
    54. Creating a PPI Table with CASE_N
    55. A Visual of Case_N Partitioning
    56. Number of PPI Partitions Allowed
    57. NO CASE and UNKNOWN Partitions Together
    58. Combining Older Data and Newer Data in PPI
    59. A Visual for Combining Older Data and Newer Data in PPI
    60. Multi-Level Partitioning Combining Range_N and Case_N
    61. A Visual of Multi-Level Partitioning
    62. NON-Unique Primary Indexes (NUPI) in PPI
    63. PPI Table with a Unique Primary Index (UPI)
    64. Tricks for Non-Unique Primary Indexes (NUPI)
    65. A Brilliant Technique for a Unique Secondary Index
    66. A Brilliant Technique for a Non-Unique Secondary Index
    67. Character Based PPI for RANGE_N
    68. Character-Based PPI for CASE_N
    69. Dates and Character-Based Multi-Level PPI
    70. TIMESTAMP Partitioning
    71. Using CURRENT_DATE to define a PPI
    72. ALTER to CURRENT_DATE the next year
    73. ALTER to CURRENT_DATE with Save
    74. Altering a PPI Table to Add or Drop Partitions
    75. Deleting a Partition
    76. Deleting a Partition and saving its contents
    77. Using the PARTITION Keyword in your SQL
    78. SQL for RANGE_N
    79. SQL for CASE_N
    80. SQL – User Defined Functions (UDF)
    81. User Defined Functions
    82. Creating a Multi-Table Join Index
    83. Visual of a Join Index
    84. Outer Join Multi-Table Join Index
    85. Visual of a Left Outer Join Index
    86. Compressed Multi-Table Join Index
    87. Creating a Single-Table Join Index
    88. Compressed Single-Table Join Index
    89. Aggregate Join Index
    90. Sparse Join Index
    91. A Global Multi-Table Join Index
    92. Creating a Hash Index
  41. Chapter 35 - 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 # 1
    30. Fast Path DELETE Example # 2
    31. Fast Path DELETE Example # 3
    32. MERGE INTO
    33. MERGE INTO Example that Matches
    34. MERGE INTO Example that does NOT Match
    35. OReplace
  42. Chapter 36 - 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. You can Name the First Begin and End if you choose
    13. Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT
    14. Stored Procedure Basic Assignment
    15. Answer - Stored Procedure Basic Assignment
    16. Stored Procedure Advanced Assignment
    17. Answer - Stored Advanced Assignment
  43. Chapter 37 - Trigger Functions
    1. The Fundamentals of Triggers
    2. CREATING A Trigger
    3. FOR EACH STATEMENT vs. FOR EACH ROW
    4. Using ORDER when Similar Triggers Exist
  44. Chapter 38 - Math Functions
    1. What is the Order of Precedents?
    2. What is the Answer to this Math Question?
    3. What is the Answer to this Math Question?
    4. What is the Answer to this Math Question?
  45. Chapter 39 - Sample
    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
  46. Chapter 40 - Statistical Aggregate Functions
    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
  47. Chapter 41 - Explain
    1. EXPLAIN Keywords
    2. EXPLAIN Keywords Continued
    3. Explain Example – Full Table Scan
    4. Explain Example – Unique Primary Index (UPI)
    5. Explain Example – Non-Unique Primary Index (NUPI)
    6. Explain Example – Unique Secondary Index (USI)
    7. Explain Example – Redistributed to All-AMPs
    8. Explain Example – Row Hash Match Scan
    9. Explain Example – Duplicated on All-AMPs
    10. Explain Example –Low Confidence
    11. Explain Example – High Confidence
    12. Explain Example – Product Join
    13. Explain Example – BMSMS
    14. Explain Terminology for Partitioned Primary Index Tables
    15. Explain Example – From a Single Partition
    16. Explain Example – From N Partitions
    17. Explain Example – Partitions and Current_Date
  48. Chapter 42 - Collect Statistics
    1. The Teradata Parsing Engine (Optimizer) is Cost Based
    2. The Purpose of Collect Statistics
    3. When Teradata Collects Statistics, it creates a Histogram
    4. The Interval of the Collect Statistics Histogram
    5. Histogram Quiz
    6. Answers to Histogram Quiz
    7. What to COLLECT STATISTICS On?
    8. Why Collect Statistics?
    9. How do you know if Statistics were collected on a Table?
    10. A Huge Hint that No Statistics Have Been Collected
    11. The Basic Syntax for COLLECT STATISTICS
    12. COLLECT STATISTICS Examples for a better Understanding
    13. The New Teradata V14 Way to Collect Statistics
    14. Where Does Teradata Keep the Collected Statistics?
    15. The Official Syntaxes for COLLECT STATISTICS
    16. How to Recollect STATISTICS on a Table
    17. Teradata Always Does a Random AMP Sample
    18. Random Sample is kept in the Table Header in FSG Cache
    19. Multiple Random AMP Samplings
    20. How a Random AMP gets a Table Row count
    21. Random AMP Estimates for NUSI Secondary Indexes
    22. USI Random AMP Samples are Not Considered
    23. There's No Random AMP Estimate for Non-Indexed Columns
    24. Summary of the PE Plan if No Statistics Were Collected
    25. Stale Statistics Detection and Extrapolation
    26. Extrapolation for Future Dates
    27. How to Copy a Table with Data and the Statistics?
    28. COLLECT STATISTICS Directly From another Table
    29. How to Copy a Table with NO Data and the Statistics?
    30. When to COLLECT STATISTICS Using only a SAMPLE
    31. Examples of COLLECT STATISTICS Using only a SAMPLE
    32. Examples of COLLECT STATISTICS for V14
    33. How to Collect Statistics on a PPI Table on the Partition
    34. Teradata V12 and V13 Statistics Enhancements
    35. Teradata V14 Statistics Enhancements
    36. Teradata V14 Summary Statistics
    37. Teradata V14 MaxValueLength
    38. Teradata V14 MaxIntervals
    39. Teradata V14 Sample N Percent
    40. Teradata Statistics Wizard
  49. Chapter 43 - Hashing Functions
    1. Hashing Functions on Teradata
    2. The HASHROW Function
    3. The HASHROW Function in a real-world Example
    4. The HASHBUCKET Function
    5. The HASHBUCKET Function in a real-world Example
    6. The HASHAMP Function
    7. The HASHAMP Function in a real-world Example
    8. A Great HASHAMP Function for Large Tables
    9. The HASHBAKAMP Function
    10. A Real-World HASBAKHAMP Function Example
    11. A Great way to see distribution for Primary and Fallback rows
  50. Chapter 44 - 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. Running Queries in BTEQ through a Batch Script
    10. Running a BTEQ Batch Script through the Command Prompt
    11. Running a BTEQ Batch Script through the Run Command
    12. Using Nexus to Build Your BTEQ Scripts
    13. Using Nexus to Build Your BTEQ Scripts
    14. Using BTEQ Scripts to IMPORT Data
    15. What Keywords Mean in a BTEQ Script
    16. Creating a BTEQ IMPORT for a Comma Separated Value File
    17. Four Great Examples/Ways to Run a Teradata BTEQ Script
    18. BTEQ Export – Four types of Export Variations
    19. Creating a BTEQ Export Script in Record Mode
    20. Creating a BTEQ Export Script in Report Mode
    21. The Appearance of Record Mode Vs Report Mode Data
    22. Using Report Mode to Create a Comma Separated Report
    23. Creating a BTEQ IMPORT for a Comma Separated Value File
    24. Using Multiple Sessions in BTEQ
    25. BTEQ Fast Path Inserts
    26. BTEQ Can Use Conditional Logic
    27. Using a BTEQ Export and Setting a Limit In a UNIX System
  51. Chapter 45 - Top SQL Commands Cheat Sheet
    1. SELECT All Columns from a Table and Sort
    2. Select Specific Columns and Limiting the Rows
    3. Changing your Default Database
    4. Keywords that describe you
    5. Select TOP Rows in a Rank Order
    6. A Sample number of rows
    7. Getting a Sample Percentage of rows
    8. Find Information about a Database
    9. Find information about a Table
    10. Using Aggregates
    11. Performing a Join
    12. Performing a Join using ANSI Syntax
    13. Using Date, Time and Timestamp
    14. Using Date Functions
    15. Using the System Calendar
    16. Using the System Calendar in a Query
    17. Formatting Data
    18. Using Rank
    19. Using a Derived Table
    20. Using a Subquery
    21. Correlated Subquery
    22. Using Substring
    23. Basic CASE Statement
    24. Advanced CASE Statement
    25. Using an Access Lock in your SQL
    26. Collect Statistics
    27. CREATING a Volatile Table with a Primary Index
    28. CREATING a Volatile Table that is Partitioned (PPI)
    29. CREATING a Volatile Table that is deleted after the Query
    30. Finding the Typical Rows per Value for specific column
    31. Finding out how much Space you have
    32. How much Space you have Per AMP
    33. Finding your Space
    34. Finding Space Skew in Tables in a Database
    35. Finding the Number of rows per AMP for a Column
    36. Finding Account Information
    37. Ordered Analytics
  52. Back Cover