Tera-Tom's 1000 Page e-Book on Teradata Architecture and SQL, 2nd Edition

Author Tom Coffing , William Coffing

Release Date: 2013/09/01

ISBN: 9781940540214

Topic:

Data

55
Chapters

0-1
Hours read

0k
Total Words

Start Reading Now
Add to Wishlist
View table of contents

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.

Cover Page
Title Page
Copyright Page
About Tom Coffing
About Warehouse William Coffing
Contents
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
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
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)?
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
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?
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
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
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?
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
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
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?
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
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
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
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
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
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?
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
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
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
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
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.
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
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
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
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
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
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?
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
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
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
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
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
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
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
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
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
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?
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
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
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
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
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
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
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
Back Cover

Tera-Tom's 1000 Page e-Book on Teradata Architecture and SQL, 2nd Edition

Book Description

Table of Contents