Customized Onsite Training

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

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

Goals
  1. Learn to gain a deeper knowledge and understanding of the Greenplum Architecture and SQL and how to write it.
Outline
  1. Introduction to the Greenplum Architecture
    1. What is Parallel Processing?
    2. The Basics of a Single Computer
    3. Data in Memory is Fast as Lightning
    4. Parallel Processing Of Data
    5. Symmetric Multi-Processing (SMP) Server
    6. Commodity Hardware Servers are Configured for Greenplum
    7. Commodity Hardware Allows For One Segment Per CPU
    8. The Master Host
    9. The Segment's Responsibilities
    10. The Host's Plan is Either All Segments or a Single Segment
    11. A Table has Columns and Rows
    12. Greenplum has Linear Scalability
    13. The Architecture of A Greenplum Data Warehouse
    14. Nexus is Now Available For Greenplum
  2. Greenplum Table Structures
    1. The Concepts of Greenplum Tables
    2. Tables are Either Distributed by Hash or Random
    3. A Hash Distributed Table has A Distribution Key
    4. Picking A Distribution Key That Is Not Very Unique
    5. Random Distribution Uses a Round Robin Technique
    6. Tables Will Be Distributed Among All Segments
    7. The Default For Distribution Chooses the First Column
    8. Table are Either a Heap or Append-Only
    9. Tables are Stored in Either Row or Columnar Format
    10. Creating a Column Oriented Table
    11. Comparing Normal Table Vs. Columnar Tables
    12. Columnar can move just One Column Block Into Memory
    13. Segments on Distributions are Aligned to Rebuild a Row
    14. Columnar Tables Store Each Column in Separate Blocks
    15. Visualize the Data - Rows vs. Columns
    16. Table Rows are Either Sorted or Unsorted
    17. Creating a Clustered Index in Order to Physically Sort Rows
    18. Physically Ordered Tables Are Faster on Certain Queries
    19. Another Way to Create a Clustered Table
    20. Creating a B-Tree Index and then Running Analyze
    21. Creating a Bitmap Index
    22. Why Create a Bitmap Index?
    23. Tables Can Be Partitioned
    24. A Table Partitioned By Range (Per Month)
    25. A Visual of a Partitioned Table by Range (Month)
    26. Tables Can Be Partitioned by Day
    27. Visualize a Partitioned Table by Day
    28. Creating a Partitioned Table Using a List
    29. Creating a Multi-Level Partitioned Table
    30. Changing a Table to a Partitioned Table
    31. Not Null Constraints
    32. Unique Constraints
    33. Unique Constraints That Fail
    34. Primary Key Constraints
    35. A Primary Key Automatically Creates a Unique Index
    36. Check Constraints
    37. Creating an Automatic Number Called a Sequence
    38. Multiple INSERT example Using a Sequence
  3. Hashing and Data Distribution
    1. Distribution Keys Hashed on Unique Values Spread Evenly
    2. Distribution Keys With Non-Unique Values Spread Unevenly
    3. Best Practices for Choosing a Distribution Key
    4. The Hash Map Determines which Segment owns the Row
    5. The Hash Map Determines which Node will Own the Row
    6. The Hash Map Determines which Node will Own the Row
    7. The Hash Map Determines which Node will Own the Row
    8. Hash Map Determines which Node will Own the Row
    9. A Review of the Hashing Process
    10. Non-Unique Distribution Keys have Skewed Data
    11. Non-Unique Distribution Keys have Skewed Data
  4. The Technical Details
    1. Greenplum Limitations
    2. Every Segment has the Exact Same Tables
    3. Tables are Distributed Across All Segments
    4. The Table Header and the Data Rows are Stored Separately
    5. Segments Store Rows inside a Data Block Called a Page
    6. To Read a Data Block a Node Moves the Block into Memory
    7. A Full Table Scan Means All Nodes Must Read All Rows
    8. Rows are Organized inside a Page
    9. Moving Data Blocks is Like Checking In Luggage
    10. As Row-Based Tables Get Bigger, the Page Splits
    11. Data Pages are Processed One at a Time Per Unit
    12. Creating a Table that is a Heap
    13. Heap Page
    14. Creating a Table that has a Clustered Index
    15. Clustered Index Page
    16. The Row Offset Array is the Guidance System for Every Row
    17. The Row Offset Array Provides Two Search Options (1 of 2)
    18. The Row Offset Array Provides Two Search Options (2 of 2)
    19. The Row Offset Array Helps With Inserts
    20. B-Trees
    21. The Building of a B-Tree for a Clustered Index (1 of 3)
    22. The Building of a B-Tree for a Clustered Index (2 of 3)
    23. The Building of a B-Tree for a Clustered Index (3 of 3)
    24. When Do I Create a Clustered Index?
    25. When Do I Create a Non Clustered Index?
    26. B-Tree for Non Clustered Index on a Clustered Table (1 of 2)
    27. B-Tree for Non Clustered Index on a Clustered Table (2 of 2)
    28. Adding a Non Clustered Index To A
    29. B-Tree for Non Clustered Index on a Heap Table (1 of 2)
    30. B-Tree for Non Clustered Index on a Heap Table (2 of 2)
  5. Physical Database Design
    1. The Four Stages of Modeling for Greenplum- Check out #4
    2. The Logical Model
    3. The Logical Model can be loaded inside Nexus
    4. First, Second and Third Normal Form
    5. Quiz - Choose that Normalization Technique
    6. Answer to Quiz - Choose that Normalization Technique
    7. Quiz - What Normalization is it Now?
    8. Answer to Quiz - What Normalization is it Now?
    9. The Employee_Table and Department_Table can be Joined
    10. The Employee_Table and Department_Table Join SQL
    11. The Extended Logical Model Template
    12. User Access is of Great Importance
    13. User Access in Layman’s Terms
    14. User Access for Joins in Layman’s Terms
    15. The Nexus Shows Users the Table’s Distribution Key
    16. Data Demographics Tell Us if the Column is Worthy
    17. Data Demographics - Distinct Rows
    18. Data Demographics - Distinct Rows Query
    19. Data Demographics - Max Rows Null
    20. Data Demographics - Max Rows Null Query
    21. Data Demographics - Max Rows Per Value
    22. Data Demographics - Max Rows Per Value
    23. Data Demographics - Typical Rows Per Value
    24. Typical Rows Per Value Query For Greenplum Systems
    25. SQL to Get the Average Rows Per Value for a Column (Mean)
    26. Data Demographics - Change Rating
    27. Factors When Choosing Greenplum Indexes
    28. Distribution Key Data Demographics Candidate Guidelines
    29. Distribution key Access Considerations
    30. Answer -Three Important distribution key Considerations
    31. Step 1 is to Pick All Potential Distribution Key Columns
    32. Step 1 is to Pick All Potential Distribution Key Columns
    33. Step 2 is to Pick All Potential Secondary Indexes
    34. Answer to 2nd Step to Picking Potential Secondary Indexes
    35. Choose the Distribution Key and Secondary Indexes
    36. 3rd Step is to Picking your Indexes
    37. Our Index Picks
  6. Denormalization
    1. Denormalization
    2. Derived Data
    3. Repeating Groups
    4. Pre-Joining Tables
    5. Storing Summary Data with a Trigger
    6. Summary Tables or Data Marts the Old Way
    7. Horizontal Partitioning the Old Way
    8. Horizontal Partitioning the New Way
    9. Vertical Partitioning the Old Way
    10. Columnar Tables Are the New Vertical Partitioning
  7. Nexus for Greenplum
    1. Nexus is Available on the Cloud
    2. Nexus Queries Every Major System
    3. Setup of Nexus is as Easy as Pie
    4. Setup of Nexus is a Easy as 1, 2, 3
    5. Nexus Data Visualization
    6. Nexus Data Visualization
    7. Nexus Data Visualization Shows What Tables Can Be Joined
    8. Nexus is Doing a Five-Table Join
    9. Nexus Generates the SQL Automatically
    10. Nexus Delivers the Report
    11. Cross-System Joins From Teradata, Oracle and SQL Server
    12. The Tabs of the Super Join Builder
    13. The 9 Tabs of the Super Join Builder - Objects Tab 1
    14. Selecting Columns in the Objects Tab
    15. The 9 Tabs of the Super Join Builder - Columns Tab 2
    16. Removing Columns From the Report in the Columns Tab
    17. The 9 Tabs of the Super Join Builder - Sorting Tab 3
    18. The 9 Tabs of the Super Join Builder - Joins Tab 4
    19. The 9 Tabs of the Super Join Builder - Where Tab 5
    20. Using the WHERE Tab For Additional WHERE or AND
    21. The 9 Tabs of the Super Join Builder - SQL Tab 6 - check paragraph below
    22. The 9 Tabs of the Super Join Builder - Answer Set Tab 7
    23. The 9 Tabs of the Super Join Builder - Analytics Tab 9
    24. Analytics Tab
    25. Analytics Tab - OLAP Example
    26. Analytics Tab - OLAP Example of SQL Generated
    27. Analytics Tab - Grouping Sets Example
    28. Analytics Tab - Grouping Sets Answer Set
    29. Nexus Data Movement
    30. Moving a Single Table To a Different System
    31. The Single Table Data Movement Screen
    32. Moving an Entire Database To a Different System
    33. The Database Mover Screen
    34. The Database Mover Options Tab
    35. Converting DDL Table Structures
    36. Converting DDL Table Structures
    37. Converting DDL Table Structures
    38. Compare and Synchronize
    39. Compare Two Different Databases From Different Systems
    40. Comparisons Down to the Column Level
    41. The Results Tab
    42. View Differences
    43. Synchronizing Differences In the Results Tab
    44. Synchronizing Differences In the Results Tab
    45. Hound Dog Compression
    46. Hound Dog Compression On Greenplum
  8. The Basics of SQL
    1. Introduction
    2. SELECT * (All Columns) in a Table
    3. Fully Qualifying a Database, Schema and 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. Comments using Double Dashes are Single Line Comments
    21. Comments for Multi-Lines
    22. Comments for Multi-Lines As Double Dashes Per Line
    23. A Great Technique for Comments to Look for SQL Errors
  9. The WHERE Clause
    1. The WHERE Clause limits Returning Rows
    2. Double Quoted Aliases are for Reserved Words and Spaces
    3. Character Data needs Single Quotes in the WHERE Clause
    4. Character Data needs Single Quotes, but Numbers Don’t
    5. Comparisons against a Null Value
    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. AND in the WHERE Clause
    12. Troubleshooting AND
    13. OR in the WHERE Clause
    14. Troubleshooting Or
    15. Troubleshooting Character Data
    16. Using Different Columns in an AND Statement
    17. Quiz - How many rows will return?
    18. Answer to Quiz - How many rows will return?
    19. What is the Order of Precedence?
    20. Using Parentheses to change the Order of Precedence
    21. Using an IN List in place of OR
    22. The IN List is an Excellent Technique
    23. IN List vs. OR brings the same Results
    24. The IN List Can Use Character Data
    25. Using a NOT IN List
    26. Null Values in a NOT IN List Bring Back No Rows
    27. A Technique for Handling Nulls with a NOT IN List
    28. BETWEEN is Inclusive
    29. NOT BETWEEN is Also Inclusive
    30. LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
    31. LIKE command Underscore is Wildcard for one Character
    32. ilike
    33. LIKE Command Works Differently on Char Vs Varchar
    34. Troubleshooting LIKE Command on Character Data
    35. Introducing the TRIM Command
    36. Introducing the RTRIM Command
    37. Quiz - What Data is Left Justified and What is Right?
    38. Numbers are Right Justified and Character Data is Left
    39. Answer - What Data is Left Justified and What is Right?
    40. An example of Data with Left and Right Justification
    41. A Visual of CHARACTER Data vs. VARCHAR Data
    42. Use the TRIM command to remove spaces on CHAR Data
    43. Escape Character in the LIKE Command changes Wildcards
    44. Escape Characters Turn off Wildcards in the LIKE Command
    45. Quiz - Turn off that Wildcard
    46. ANSWER - To Find that Wildcard
    47. Introducing the RTRIM Command
    48. Quiz - What Data is Left Justified and What is Right?
    49. Answer - What Data is Left Justified and What is Right?
    50. An example of Data with Left and Right Justification
    51. A Visual of CHARACTER Data vs. VARCHAR Data
    52. RTRIM command Removes Trailing spaces on CHAR Data
    53. Using Like with an AND Clause to Find Multiple Letters
    54. Using Like with an OR Clause to Find Either Letters
  10. Distinct vs. Group By
    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?
  11. Aggregation
    1. Quiz - You calculate the Answer Set in your own Mind
    2. Answer - You calculate the Answer Set in your own Mind
    3. Answer - You calculate the Answer Set in your own Mind
    4. The 3 Rules of Aggregation
    5. There are Five Aggregates
    6. Quiz - How many rows come back?
    7. Answer - How many rows come back?
    8. Troubleshooting Aggregates
    9. GROUP BY when Aggregates and Normal Columns Mix
    10. GROUP BY delivers one row per Group
    11. GROUP BY Dept_No or GROUP BY 1 the same thing
    12. Limiting Rows and Improving Performance with WHERE
    13. WHERE Clause in Aggregation limits unneeded Calculations
    14. Keyword HAVING tests Aggregates after they are Totaled
    15. Aggregates Return Null on Empty Tables
    16. Keyword HAVING is like an Extra WHERE Clause for Totals
    17. Keyword HAVING tests Aggregates after they are Totaled
    18. Getting the Average Values Per Column
    19. Average Values Per Column For all Columns in a Table
    20. Three types of Advanced Grouping
    21. Group By Grouping Sets
    22. Group By Rollup
    23. GROUP BY Rollup Result Set
    24. GROUP BY Cube
    25. GROUP BY CUBE Result Set
    26. GROUP BY CUBE Result Set - check paragraph
    27. Quiz - GROUP BY GROUPING SETS Challenge
    28. Answer To Quiz - GROUP BY GROUPING SETS Challenge
  12. Join Functions
    1. Greenplum Join Quiz Answer
    2. Redistribution
    3. Big Table Small Table Join Strategy
    4. Duplication of the Smaller Table across All-Distributions
    5. If the Join Condition is the Distribution Key no Movement
    6. Matching Rows That Are On The Same Node Naturally
    7. Strategy 1 of 4 - The Merge Join
    8. Quiz - Redistribute the Employees by their Dept_No
    9. Quiz - Employees' Dept_No landed on segment with Matches
    10. Quiz - Redistribute the Orders to the Proper segment
    11. Answer to Redistribute the Employees by their Dept_No Quiz
    12. Strategy 2 of 4 - The Hash Join
    13. Strategy 3 of 4 - The Nested Join
    14. Strategy 4 of 4 - The Product Join
    15. A Two-Table Join Using Traditional Syntax
    16. A two-table join using Non-ANSI Syntax with Table Alias
    17. You Can Fully Qualify All Columns
    18. A two-table join using ANSI Syntax
    19. Both Queries have the same Results and Performance
    20. Quiz - Can You Finish the Join Syntax?
    21. Answer to Quiz - Can You Finish the Join Syntax?
    22. Quiz - Can You Find the Error?
    23. Answer to Quiz - Can You Find the Error?
    24. Super Quiz - Can You Find the Difficult Error?
    25. Answer to Super Quiz - Can You Find the Difficult Error?
    26. Quiz - Which rows from both tables won’t Return?
    27. Answer to Quiz - Which rows from both tables Won’t Return?
    28. LEFT OUTER JOIN
    29. LEFT OUTER JOIN Results
    30. RIGHT OUTER JOIN
    31. RIGHT OUTER JOIN Example and Results
    32. FULL OUTER JOIN
    33. FULL OUTER JOIN Results
    34. Which Tables are the Left and which Tables are Right?
    35. Answer - Which Tables are the Left and Which are the Right?
    36. INNER JOIN with Additional AND Clause
    37. ANSI INNER JOIN with Additional AND Clause
    38. ANSI INNER JOIN with Additional WHERE Clause
    39. OUTER JOIN with Additional WHERE Clause
    40. OUTER JOIN with Additional AND Clause
    41. OUTER JOIN with Additional AND Clause Results
    42. Quiz - Why is this considered an INNER JOIN?
    43. Evaluation Order for Outer Queries
    44. The DREADED Product Join
    45. The DREADED Product Join Results
    46. The Horrifying Cartesian Product Join
    47. The ANSI Cartesian Join will ERROR
    48. Quiz - Do these Joins Return the Same Answer Set?
    49. Answer - Do these Joins Return the Same Answer Set?
    50. The CROSS JOIN
    51. The CROSS JOIN Answer Set
    52. The SelfJoin
    53. The SelfJoin with ANSI Syntax
    54. Quiz - Will both queries bring back the same Answer Set?
    55. Answer - Will both queries bring back the same Answer Set?
    56. Quiz - Will both queries bring back the same Answer Set?
    57. Answer - Will both queries bring back the same Answer Set?
    58. How would you Join these two tables?
    59. An Associative Table is a Bridge that Joins Two Tables
    60. Quiz - Can you write the 3-Table Join?
    61. Answer to Quiz - Can you Write the 3-Table Join?
    62. Quiz - Can you write the 3-Table Join to ANSI Syntax?
    63. Answer - Can you Write the 3-Table Join to ANSI Syntax?
    64. Quiz - Can you Place the ON Clauses at the End?
    65. Answer - Can you Place the ON Clauses at the End?
    66. The 5-Table Join - Logical Insurance Model
    67. Quiz - Write a Five Table Join Using ANSI Syntax
    68. Answer - Write a Five Table Join Using ANSI Syntax
    69. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    70. Answer - Write a Five Table Join Using Non-ANSI Syntax
    71. Quiz -Re-Write this putting the ON clauses at the END
    72. Answer -Re-Write this putting the ON clauses at the END
    73. The Nexus Query Chameleon Writes the SQL for Users.
  13. Date Function
    1. Current_Date
    2. Current_Date, Current_Time, and Current_Timestamp
    3. Current_Time vs. LocalTime With Precision
    4. Local_Time and Local_Timestamp With Precision
    5. Now() and Timeofday() Functions
    6. Adding A Week to a Date
    7. Add or Subtract Days from a date
    8. Formatting Dates and Dollar Amounts
    9. The EXTRACT Command
    10. EXTRACT from DATES and TIME
    11. EXTRACT Command on the Century
    12. EXTRACT Command for the Decade, DOW and DOY
    13. EXTRACT Microseconds, Milliseconds and Millennium
    14. EXTRACT of the Month on Aggregate Queries
    15. Date_part Command
    16. Date_Trunc Command With Time
    17. Date_Trunc Command With Dates
    18. The AGE Command
    19. AGE Challenge
    20. AGE Challenge Results
    21. Epoch
    22. Using Intervals
    23. More Interval Examples
    24. Interval Arithmetic Results
    25. A Complex Time Interval example using CAST
    26. The OVERLAPS Command
    27. An OVERLAPS example that Returns No Rows
    28. The OVERLAPS Command using TIME
    29. Using Both CAST and CONVERT in Literal Values
    30. A Better Technique for YEAR, MONTH, and DAY Functions
  14. Conversions and Formatting
    1. Postgres Conversion Functions
    2. Postgres Conversion Function Templates
    3. Postgres Conversion Function Templates Continued
    4. To_Char command Examples
    5. Formatting A Date with To_Char
    6. Formatting A Date With To_Char Continued
    7. To_Number
    8. The to_number syntax, and postgres parameters and their explanation are listed above.Not all above work on Greenplum
    9. To_Number Examples
    10. To_Date
    11. To_Timestamp
  15. 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 Extreme Subquery
    16. Answer to Quiz - Write the Extreme Subquery
    17. Quiz- Write the Subquery with an Aggregate
    18. Answer to Quiz- Write the Subquery with an Aggregate
    19. Quiz- Write the Correlated Subquery
    20. Answer to Quiz- Write the Correlated Subquery
    21. The Basics of a Correlated Subquery
    22. The Top Query always runs first in a Correlated Subquery
    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. Quiz - Write the Extreme Correlated Subquery
    31. Answer To Quiz - Write the Extreme Correlated Subquery
    32. Quiz- Write the NOT Subquery
    33. Answer to Quiz- Write the NOT Subquery
    34. Quiz- Write the Subquery using a WHERE Clause
    35. Answer - Write the Subquery using a WHERE Clause
    36. Quiz- Write the Subquery with Two Parameters
    37. Answer to Quiz- Write the Subquery with Two Parameters
    38. More on how the Double Parameter Subquery Works
    39. Quiz - Write the Triple Subquery
    40. Answer to Quiz - Write the Triple Subquery
    41. Quiz - How many rows return on a NOT IN with a NULL?
    42. Answer - How many rows return on a NOT IN with a NULL?
    43. How to handle a NOT IN with PotentialNULL Values
    44. IN is equivalent to =ANY
    45. Using a Correlated Exists
    46. How a Correlated Exists matches up
    47. The Correlated NOT Exists
    48. The Correlated NOT Exists Answer Set
    49. Quiz - How many rows come back from this NOT Exists?
    50. Answer - How many rows come back from this NOT Exists?
  16. OLAP Functions
    1. CSUM
    2. CSUM - The Sort Explained
    3. CSUM - Rows Unbounded Preceding Explained
    4. CSUM - Making Sense of the Data
    5. CSUM - Making Even More Sense of the Data
    6. CSUM - The Major and Minor Sort Key(s)
    7. The ANSI CSUM - Getting a Sequential Number
    8. Troubleshooting The ANSI OLAP on a GROUP BY
    9. Reset with a PARTITION BY Statement
    10. PARTITION BY only Resets a Single OLAP not ALL of them
    11. Moving SUM
    12. ANSI Moving Window is Current Row and Preceding n Rows
    13. How ANSI Moving SUM Handles the Sort
    14. Quiz - How is that Total Calculated?
    15. Answer to Quiz - How is that Total Calculated?
    16. Moving SUM every 3-rows Vs a Continuous Average
    17. Partition By Resets an ANSI OLAP
    18. Both the Greenplum Moving Average andANSI Version
    19. Moving Average
    20. The Moving Window is Current Row and Preceding
    21. How Moving Average Handles the Sort
    22. Quiz - How is that Total Calculated?
    23. Answer to Quiz - How is that Total Calculated?
    24. Quiz - How is that 4th Row Calculated?
    25. Answer to Quiz - How is that 4th Row Calculated?
    26. Moving Average every 3-rows Vs a Continuous Average
    27. Partition By Resets an ANSI OLAP
    28. Moving Difference using ANSI Syntax with Partition By
    29. RANK Defaults to Ascending Order
    30. Getting RANK to Sort in DESC Order
    31. RANK() OVER and PARTITION BY
    32. RANK and DENSE RANK
    33. PERCENT_RANK() OVER
    34. PERCENT_RANK() OVER with 14 rows in Calculation
    35. PERCENT_RANK() OVER with 21 rows in Calculation
    36. Quiz - What Causes the Product_ID to Reset?
    37. Answer to Quiz - What Cause the Product_ID to Reset?
    38. COUNT OVER for a Sequential Number
    39. Troubleshooting COUNT OVER
    40. Quiz - What caused the COUNT OVER to Reset?
    41. Answer to Quiz - What caused the COUNT OVER to Reset?
    42. The MAX OVER Command
    43. MAX OVERwith PARTITION BY Reset
    44. Troubleshooting MAX OVER
    45. The MIN OVER Command
    46. Troubleshooting MIN OVER
    47. Finding a Value of a Column in the Next Row with MIN
    48. Quiz - Fill in the Blank
    49. Answer - Fill in the Blank
    50. The Row_Number Command
    51. Using a Derived Table and Row_Number
    52. Quiz - How did the Row_Number Reset?
    53. Answer - How did the Row_Number Reset?
    54. Ordered Analytics OVER
    55. CURRENT ROW AND UNBOUNDED FOLLOWING
    56. Different Windowing Options
    57. The CSUM For Each Product_Id and the Next Start Date
    58. How Ntile Works
    59. Ntile
    60. Ntile Continued
    61. Ntile Percentile
    62. Another Ntile example
    63. Using Tertiles (Partitions of Four)
    64. NTILE
    65. NTILE Using a Value of 10
    66. NTILE With a Partition
    67. Using FIRST_VALUE
    68. FIRST_VALUE
    69. FIRST_VALUE After Sorting by the Highest Value
    70. FIRST_VALUE with Partitioning
    71. Using LAST_VALUE
    72. LAST_VALUE
    73. Using LEAD
    74. Using LEAD With and Offset of 2
    75. LEAD
    76. LEAD With Partitioning
    77. Using LAG
    78. Using LAG With an Offset of 2
    79. LAG
    80. LAG with Partitioning
    81. CUME_DIST
    82. CUME_DIST With a Partition
    83. SUM(SUM(n))
  17. Temporary Tables
    1. There are Two 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. The Same Derived Query shown Three Different Ways
    8. Most Derived Tables Are Used To Join To Other Tables
    9. The Three Components of a Derived Table
    10. Visualize This Derived Table
    11. A Derived Table and CAST Statements
    12. A Derived example Using 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. An example of Two Derived Tables in a Single Query
    17. MULTIPLE Derived Tables using the WITH Command
    18. Three Steps to Creating a Temporary Table
    19. Three Versions of Creating a Temporary Table
    20. ON COMMIT PRESERVE ROWS is the Greenplum Default
    21. ON COMMIT DELETE ROWS
    22. How to Use the ON COMMIT DELETE ROWS Option
    23. ON COMMIT DROP
    24. How to Use the ON COMMIT DROP Option
    25. Create Table AS
    26. Create Table LIKE
    27. Creating a Clustered Index on a Temporary Table
  18. Substrings and Positioning Functions
    1. The CHARACTERS Command Counts Characters
    2. The CHARACTERS Command and Char(20) Data
    3. CHARACTER_LENGTH and OCTET_LENGTH
    4. The TRIM Command trims both Leading and Trailing Spaces
    5. Trim Combined with the CHARACTERS Command
    6. How to TRIM only the Trailing Spaces
    7. A Visual of the TRIM Command Using Concatenation
    8. Trim and Trailing is Case Sensitive
    9. How to TRIM Trailing Letters
    10. The SUBSTRING Command
    11. SUBSTRING and SUBSTR are equal, but use different syntax
    12. How SUBSTRING Works with NO ENDING POSITION
    13. Using SUBSTRING to move Backwards
    14. How SUBSTRING Works with a Starting Position of -1
    15. How SUBSTRING Works with an Ending Position of 0
    16. An example using SUBSTRING, TRIM andCHAR Together
    17. The POSITION Command finds a Letters Position
    18. Concatenation
    19. Concatenation and SUBSTRING
    20. Four Concatenations Together
    21. Troubleshooting Concatenation
  19. Interrogating the Data
    1. Quiz - What would the Answer be?
    2. Answer to Quiz - What would the Answer be?
    3. The NULLIF Command
    4. Quiz - Fill in the Answers for the NULLIF Command
    5. Answer- Fill in the Answers for the NULLIF Command
    6. The COALESCE Command - Fill In the Answers
    7. The COALESCE Answer Set
    8. COALESCE is Equivalent to This CASE Statement
    9. The COALESCE Command
    10. The COALESCE Answer Set
    11. The COALESCE Quiz
    12. Answer - The COALESCE Quiz
    13. The Basics of CAST (Convert and Store)
    14. Some Great CAST (Convert and Store) Examples
    15. Some Great CAST (Convert and Store) Examples
    16. A Rounding Example
    17. Some Great CAST (Convert And STore) example
    18. Quiz - The Basics of the CASE Statements
    19. Answer to Quiz - The Basics of the CASE Statements
    20. Using an ELSE in the Case Statement
    21. Using an ELSE as a Safety Net
    22. Rules For a Valued Case Statement
    23. Rules for a Searched Case Statement
    24. Valued Case Vs. A Searched Case
    25. Quiz - Valued Case Statement
    26. Answer - Valued Case Statement
    27. Quiz - Searched Case Statement
    28. Answer - Searched Case Statement
    29. The CASE Challenge
    30. The CASE Challenge Answer
    31. Combining Searched Case and Valued Case
    32. A Trick for getting a Horizontal Case
    33. Nested Case
  20. Set Op
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,346

Students who have taken Live Online Training

15,155

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,232 reviews

Contact Us or call 1-877-932-8228