Teradata SQL Training

Request an offer for delivery of this course

Course duration

2 days

Course Benefits

Gain a deeper knowledge and understanding of Teradata SQL and how to write it.

Course Outline

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
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
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?
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
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
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
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
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
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
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
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
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
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
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. How to handle a NOT IN with Potential NULL Values
43. IN is equivalent to =ANY
44. Using a Correlated Exists
45. How a Correlated Exists matches up
46. The Correlated NOT Exists
47. The Correlated NOT Exists Answer Set
48. Quiz – How many rows come back from this NOT Exists?
49. Answer – How many rows come back from this NOT Exists?
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
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
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
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
Set Operators Functions
1. Rules of Set Operators
2. INTERSECT Explained Logically
3. INTERSECT Explained Logically
4. UNION Explained Logically
5. UNION Explain

Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Since its founding in 1995, InterSource has been providing high quality and highly customized training solutions to clients worldwide. With over 500 course titles constantly updated and numerous course customization and creation possibilities, we have the capability to meet your I.T. training needs.
Instructor-led courses are offered via a live Web connection, at client sites throughout Europe, and at our Geneva Training Center.