V14 Certification: Teradata SQL

Author Tom Coffing , Leona Coffing

Release Date: 2015/02/01

ISBN: 9781940540252

Topic:

Data

36
Chapters

0-1
Hours read

0k
Total Words

Start Reading Now
Add to Wishlist
View table of contents

Book Description

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

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

V14 Certification: Teradata SQL

Book Description

Table of Contents