Customized Onsite Training

5
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors
Request Class or call 1-877-932-8228
Overview

In this Teradata SQL training class, students will learn SQL starting at the most basic level and going to the most advanced level with many examples.

This course is designed for anyone who has a desire to learn Teradata SQL from beginners to an advanced audience.

Goals
  1. Gain a deeper knowledge and understanding of Teradata SQL and how to write it.
Outline
  1. 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
  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. 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
  3. 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?
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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?
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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 in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

72,537

Students who have taken Live Online Training

15,169

Organization who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.42

Students rated our trainers 9.42 out of 10 based on 5,209 reviews

Contact Us or call 1-877-932-8228