Vertica Architecture and SQL Training

Customized Onsite Training

3
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors
Overview

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

Goals
  1. Gain a deeper knowledge and understanding of the Vertica Architecture and SQL and how to write it.
Outline
  1. Chapter 1 - What is Columnar?
    1. What is Parallel Processing?
    2. Nothing Happens on Disk
    3. Data in Memory is Fast as Lightning
    4. Parallel Processing Of Data
    5. The Problem With Row-Based Data
    6. Columnar Data Can Store Each Column in Their Own Block
    7. Why Columnar?
    8. Row Based Blocks vs. Columnar Based Blocks
    9. Visualize the Data - Rows vs. Columns
    10. The Architecture of Vertica
    11. Vertica Architecture Terms
    12. Vertica has Linear Scalability
  2. Chapter 2 - Vertica Data Distribution
    1. Distribution Strategy 1 - Segmented By Hash
    2. Distribution Strategy 2 - Unsegmented
    3. Sorting the Data in a Table CREATE Statement
    4. Even Distribution
    5. Uneven Distribution Where the Data is Non-Unique
    6. Matching Distribution Keys for Co-Location of Joins
    7. Big Table / Small Table Joins
    8. Fact and Dimension Table Distribution Key Designs
    9. Why a Sort Key Improves Performance
    10. Sort Keys Help Group By, Order By and Window Functions
  3. Chapter 3 - Clever Features of Vertica
    1. Super Projections
    2. Vertica Projections
    3. The Five Advantages of Projections
    4. Creating a Projection
    5. Read-Optimized Store (ROS)/Write-Optimized Store (WOS)
    6. Write-Optimized Store (WOS) is Memory Resident
    7. Updates are Collected in Time-Based Buckets called Epochs
    8. Vertica Does Not Support In-Place Updates
    9. K-Safety
    10. K-Safety of 2
    11. The Five Data Isolation Modes
    12. Import/Export Between Multiple Vertica Systems
    13. Roles
    14. Compression
    15. Runlength encoding
    16. LZO Encoding
    17. Delta Encoding
    18. Block Based Dictionary Encoding for Character Data
  4. Chapter 4 - Nexus on Vertica
    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
  5. Chapter 5 - The Basics of SQL
    1. Introduction
    2. Setting your Path
    3. Setting Your Default Database
    4. SELECT * (All Columns) in a Table
    5. Fully Qualifying a Database, Schema and Table
    6. SELECT Specific Columns in a Table
    7. Commas in the Front or Back?
    8. Place your Commas in front for better Debugging Capabilities
    9. Sort the Data with the ORDER BY Keyword
    10. ORDER BY Defaults to Ascending
    11. Use the Name or the Number in your ORDER BY Statement
    12. Two Examples of ORDER BY using Different Techniques
    13. Changing the ORDER BY to Descending Order
    14. NULL Values sort First in Ascending Mode (Default)
    15. NULL Values sort Last in Descending Mode (DESC)
    16. Major Sort vs. Minor Sorts
    17. Multiple Sort Keys using Names vs. Numbers
    18. Sorts are Alphabetical, NOT Logical
    19. Using A CASE Statement to Sort Logically
    20. How to ALIAS a Column Name
    21. A Missing Comma can by Mistake become an Alias
    22. Aliasing a Column Name With Spaces or Reserved Words
    23. Comments using Double Dashes are Single Line Comments
    24. Comments for Multi-Lines
    25. Comments for Multi-Lines As Double Dashes Per Line
    26. Formatting Number
    27. Formatting Number Examples
    28. Formatting Dates
    29. Formatting Date Example
  6. Chapter 6- 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. LIKE Command Works Differently on Char Vs Varchar
    33. LIKE Command on Character Data Auto Trims
    34. Quiz - What Data is Left Justified and What is Right?
    35. Numbers are Right Justified and Character Data is Left
    36. Answer - What Data is Left Justified and What is Right?
    37. An Example of Data with Left and Right Justification
    38. A Visual of CHARACTER Data vs. VARCHAR Data
    39. Use the TRIM command to remove spaces on CHAR Data
    40. Escape Character in the LIKE Command changes Wildcards
    41. Escape Characters Turn off Wildcards in the LIKE Command
    42. Quiz - Turn off that Wildcard
    43. ANSWER - To Find that Wildcard
    44. The Distinct Command
    45. Distinct vs. GROUP BY
    46. Answer - How many rows come back from the Distinct?
  7. Chapter 7 - Aggregation
    1. Quiz - You calculate the Answer Set in your own Mind
    2. Answer - You calculate the Answer Set in your own Mind
    3. Quiz - You calculate the Answer Set in your own Mind
    4. Answer - You calculate the Answer Set in your own Mind
    5. The 3 Rules of Aggregation
    6. There are Five Aggregates
    7. Quiz - How many rows come back?
    8. Answer - How many rows come back?
    9. Troubleshooting Aggregates
    10. GROUP BY when Aggregates and Normal Columns Mix
    11. GROUP BY delivers one row per Group
    12. GROUP BY Dept_No or GROUP BY 1 the same thing
    13. Limiting Rows and Improving Performance with WHERE
    14. WHERE Clause in Aggregation limits unneeded Calculations
    15. Keyword HAVING tests Aggregates after they are Totaled
    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. Group By Rollup
    21. GROUP BY Rollup Result Set
  8. Chapter 8 - Join Functions
    1. A Two-Table Join Using Traditional Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. You Can Fully Qualify All 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. Super Quiz - Can You Find the Difficult Error?
    11. Answer to Super Quiz - Can You Find the Difficult Error?
    12. Quiz - Which rows from both tables won’t Return?
    13. Answer to Quiz - Which rows from both tables Won’t Return?
    14. LEFT OUTER JOIN
    15. LEFT OUTER JOIN Results
    16. RIGHT OUTER JOIN
    17. RIGHT OUTER JOIN Example and Results
    18. FULL OUTER JOIN
    19. FULL OUTER JOIN Results
    20. Which Tables are the Left and which Tables are Right?
    21. Answer - Which Tables are the Left and Which are the Right?
    22. INNER JOIN with Additional AND Clause
    23. ANSI INNER JOIN with Additional AND Clause
    24. ANSI INNER JOIN with Additional WHERE Clause
    25. OUTER JOIN with Additional WHERE Clause
    26. OUTER JOIN with Additional AND Clause
    27. OUTER JOIN with Additional AND Clause Results
    28. Quiz - Why is this considered an INNER JOIN?
    29. Evaluation Order for Outer Queries
    30. The DREADED Product Join
    31. The DREADED Product Join Results
    32. The Horrifying Cartesian Product Join
    33. The ANSI Cartesian Join will ERROR
    34. Quiz - Do these Joins Return the Same Answer Set?
    35. Answer - Do these Joins Return the Same Answer Set?
    36. The CROSS JOIN
    37. The CROSS JOIN Answer Set
    38. The SelfJoin
    39. The SelfJoin with ANSI Syntax
    40. Quiz - Will both queries bring back the same Answer Set?
    41. Answer - Will both queries bring back the same Answer Set?
    42. Quiz - Will both queries bring back the same Answer Set?
    43. Answer - Will both queries bring back the same Answer Set?
    44. How would you Join these two tables?
    45. An Associative Table is a Bridge that Joins Two Tables
    46. Quiz - Can you write the 3-Table Join?
    47. Answer to Quiz - Can you Write the 3-Table Join?
    48. Quiz - Can you write the 3-Table Join to ANSI Syntax?
    49. Answer - Can you Write the 3-Table Join to ANSI Syntax?
    50. Quiz - Can you Place the ON Clauses at the End?
    51. Answer - Can you Place the ON Clauses at the End?
    52. The 5-Table Join - Logical Insurance Model
    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. Chapter 9 - Date Functions
    1. Current_Date
    2. Current_Date, Current_Time And Current_Timestamp
    3. Timestamp Differences
    4. Getdate
    5. Date and Time Keywords
    6. Using CAST in Literal Values
    7. Add or Subtract Days from a date
    8. Formatting Dates
    9. Formatting Date Example
    10. A Summary of Math Operations on Dates
    11. The ADD_MONTHS Command
    12. Using the ADD_MONTHS Command to Add 1 Year
    13. Using the ADD_MONTHS Command to Add 1 Year
    14. Using the ADD_MONTHS Command to Add 5 Years
    15. Using the ADD_MONTHS Command to Add 5 Years
    16. The EXTRACT Command
    17. YEAR, MONTH, and DAY Functions
    18. A Better Technique for YEAR, MONTH, and DAY Functions
    19. Another Version of the EXTRACT Command
    20. EXTRACT from DATES and TIME
    21. Why EXTRACT is a Better Form
    22. EXTRACT with DATE and TIME Literals
    23. EXTRACT of the Month on Aggregate Queries
    24. AGE_IN_MONTHS
    25. AGE_IN_YEARS
    26. DATE_TRUNC
    27. DATEDIFF
    28. DAYOFWEEK
    29. Intervals for Date, Time and Timestamp
    30. Interval Data Types and the Bytes to Store Them
    31. Using Intervals
    32. How a Simple Interval Handles Leap Year
    33. Interval Arithmetic Results
    34. A Time Interval Example
    35. A DATE Interval Example Going Back in Time
    36. A Complex Time Interval Example using CAST
    37. A Complex Time Interval Example using CAST
    38. The OVERLAPS Command
    39. An OVERLAPS Example that Returns No Rows
    40. The OVERLAPS Command using TIME
  10. Chapter 10- OLAP Functions
    1. The Row_Number Command
    2. Quiz - How did the Row_Number Reset?
    3. Quiz - How did the Row_Number Reset?
    4. Using a Derived Table and Row_Number
    5. Ordered Analytics OVER
    6. RANK and DENSE RANK
    7. RANK Defaults to Ascending Order
    8. Getting RANK to Sort in DESC Order
    9. RANK() OVER and PARTITION BY
    10. PERCENT_RANK() OVER
    11. PERCENT_RANK() OVER with 14 rows in Calculation
    12. PERCENT_RANK() OVER with 21 rows in Calculation
    13. Quiz - What Causes the Product_ID to Reset?
    14. Answer to Quiz - What Cause the Product_ID to Reset?
    15. Finding Gaps Between Dates
    16. CSUM - Rows Unbounded Preceding Explained
    17. CSUM - Making Sense of the Data
    18. CSUM - Making Even More Sense of the Data
    19. CSUM - The Major and Minor Sort Key(s)
    20. The ANSI CSUM - Getting a Sequential Number
    21. Troubleshooting The ANSI OLAP on a GROUP BY
    22. Reset with a PARTITION BY Statement
    23. PARTITION BY only Resets a Single OLAP not ALL of them
    24. PARTITION BY only Resets a Single OLAP not ALL of them
    25. CURRENT ROW AND UNBOUNDED FOLLOWING
    26. Different Windowing Options
    27. Moving Sum has a Moving Window
    28. How ANSI Moving SUM Handles the Sort
    29. Quiz - How is that Total Calculated?
    30. Answer to Quiz - How is that Total Calculated?
    31. Moving SUM every 3-rows Vs a Continuous Average
    32. Partition By Resets an ANSI OLAP
    33. The Moving Window is Current Row and Preceding
    34. How Moving Average Handles the Sort
    35. Moving Average
    36. Moving Average
    37. Quiz - How is that Total Calculated?
    38. Answer to Quiz - How is that Total Calculated?
    39. Quiz - How is that 4th Row Calculated?
    40. Answer to Quiz - How is that 4th Row Calculated?
    41. Moving Average every 3-rows Vs a Continuous Average
    42. Partition By Resets an ANSI OLAP
    43. Moving Difference using ANSI Syntax
    44. Moving Difference using ANSI Syntax with Partition By
    45. COUNT OVER for a Sequential Number
    46. COUNT OVER Without Rows Unbounded Preceding
    47. Quiz - What caused the COUNT OVER to Reset?
    48. Answer to Quiz - What caused the COUNT OVER to Reset?
    49. The MAX OVER Command
    50. MAX OVERwith PARTITION BY Reset
    51. MAX OVER Without Rows Unbounded Preceding
    52. The MIN OVER Command
    53. MIN OVER Without Rows Unbounded Preceding
    54. Finding a Value of a Column in the Next Row with MIN
    55. The CSUM For Each Product_Id and the Next Start Date
    56. Quiz - Fill in the Blank
    57. Answer - Fill in the Blank
    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 LAG and LEAD
    74. Using LEAD
    75. Using LEAD With and Offset of 2
    76. LEAD
    77. LEAD With Partitioning
    78. Using LAG
    79. Using LAG With an Offset of 2
    80. LAG
    81. LAG with Partitioning
    82. MEDIAN with Partitioning
    83. CUME_DIST
    84. CUME_DIST With a Partition
    85. SUM(SUM(n))
  11. Chapter 11 - 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. 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. Our Join Example With A Different Column Aliasing Style
    12. Column Aliasing Can Default For Normal Columns
    13. Our Join Example With The WITH Syntax
    14. Quiz - Answer the Questions
    15. Answer to Quiz - Answer the Questions
    16. Clever Tricks on Aliasing Columns in a Derived Table
    17. A Derived Table lives only for the lifetime of a single query
    18. An Example of Two Derived Tables in a Single Query
    19. Example of Two Derived Tables in a Single WITH Statement
    20. Syntax for Temporary Tables
    21. Temporary Tables Explained
    22. Key Temporary Table Terms
    23. Creating and Populating a Local Temporary Table
    24. Using a Local Temporary Table
    25. Creating and Populating a Global Temporary Table
    26. Creating and Populating a Global Temporary Table
    27. Some Great Examples of Creating a Temporary Table Quickly
    28. Creating a Temporary Table That is Sorted
    29. A Temp Table That Populates Some of the Rows
    30. A Temporary Table With Some of the Columns
  12. Chapter 12 - Sub-query Functions
    1. An IN List is much like a Subquery
    2. An IN List Never has Duplicates - Just like a Subquery
    3. The Subquery
    4. The Three Steps of How a Basic Subquery Works
    5. These are Equivalent Queries
    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 or 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 Extreme Subquery
    15. Answer to Quiz- Write the Extreme Subquery
    16. Quiz- Write the Subquery with an Aggregate
    17. Answer to Quiz- Write the Subquery with an Aggregate
    18. Quiz- Write the Correlated Subquery
    19. Answer to Quiz- Write the Correlated Subquery
    20. The Basics of a Correlated Subquery
    21. The Top Query always runs first in a Correlated Subquery
    22. Correlated Subquery Example vs. a Join with a Derived Table
    23. Quiz- A Second Chance To Write a Correlated Subquery
    24. Answer - A Second Chance to Write a Correlated Subquery
    25. Quiz- A Third Chance To Write a Correlated Subquery
    26. Answer - A Third Chance to Write a Correlated Subquery
    27. Quiz- Last Chance To Write a Correlated Subquery
    28. Answer - Last Chance to Write a Correlated Subquery
    29. Quiz - Write the Extreme Correlated Subquery
    30. Answer To Quiz - Write the Extreme Correlated Subquery
    31. Quiz- Write the NOT Subquery
    32. Answer to Quiz- Write the NOT Subquery
    33. Quiz- Write the Subquery using a WHERE Clause
    34. Answer - Write the Subquery using a WHERE Clause
    35. Quiz- Write the Subquery with Two Parameters
    36. Answer to Quiz- Write the Subquery with Two Parameters
    37. How the Double Parameter Subquery Works
    38. More on how the Double Parameter Subquery Works
    39. Quiz - Write the Triple Subquery
    40. Answer to Quiz - Write the Triple Subquery
    41. Quiz - How many rows return on a NOT IN with a NULL?
    42. Answer - How many rows return on a NOT IN with a NULL?
    43. How to handle a NOT IN with 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?
  13. Chapter 13 - Substrings and Positioning Functions
    1. The LENGTH Command Counts Characters
    2. The LENGTH Command - Spaces can Count too
    3. The LENGTH Command and Char(20) Data
    4. LENGTH and CHARACTER_LENGTH Are Equivalent
    5. OCTET_LENGTH
    6. The TRIM Command trims both Leading and Trailing Spaces
    7. Trim Combined with the CHARACTERS Command
    8. How to TRIM only the Trailing Spaces
    9. A Visual of the TRIM Command Using Concatenation
    10. Trim and Trailing is Case Sensitive
    11. How to TRIM Trailing Letters
    12. The SUBSTRING Command
    13. SUBSTRING and SUBSTR are equal, but use different syntax
    14. How SUBSTRING Works with NO ENDING POSITION
    15. Using SUBSTRING to move Backwards
    16. How SUBSTRING Works with a Starting Position of -1
    17. How SUBSTRING Works with an Ending Position of 0
    18. An Example using SUBSTRING, TRIM andCHAR Together
    19. The POSITION Command finds a Letters Position
    20. Quiz - Find that SUBSTRING Starting Position
    21. Answer to Quiz - Find that SUBSTRING Starting Position
    22. Using the SUBSTRING to Find the Second Word On
    23. Quiz - Why Did only one Row Return
    24. Answer to Quiz - Why Did only one Row Return
    25. Concatenation
    26. Concatenation and SUBSTRING
    27. Four Concatenations Together
    28. Troubleshooting Concatenation
  14. Chapter 14 - Interrogating the Data
    1. Quiz - What would the Answer be?
    2. Answer to Quiz - What would the Answer be?
    3. The NULLIFZERO Command
    4. The NULLIFZERO vs. Zeroes
    5. 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. Answer - 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. Answer - The Coalesce Quiz
    15. The COALESCE Command - Fill In the Answers
    16. The COALESCE Answer Set
    17. COALESCE is Equivalent to This CASE Statement
    18. Some Great CAST (Convert And STore) Examples
    19. Some Great CAST (Convert And Store) Examples
    20. A Rounding Example
    21. Some Great CAST (Convert And Store) Examples
    22. Quiz - The Basics of the CASE Statements
    23. Answer to Quiz - The Basics of the CASE Statements
    24. Using an ELSE in the Case Statement
    25. Using an ELSE as a Safety Net
    26. Rules For a Valued Case Statement
    27. Rules For a Searched Case Statement
    28. The Basics of the CASE Statements
    29. The Basics of the CASE Statement
    30. Valued Case Vs. A Searched Case
    31. Quiz - Valued Case Statement
    32. Answer - Valued Case Statement
    33. Quiz - Searched Case Statement
    34. Answer - Searched Case Statement
    35. Quiz - When NO ELSE is present in CASE Statement
    36. Answer - When NO ELSE is present in CASE Statement
    37. When an ELSE is present in CASE Statement
    38. Answer - When an ELSE is present in CASE Statement
    39. The CASE Challenge
    40. The CASE Challenge Answer
    41. Combining Searched Case and Valued Case
    42. A Trick for getting a Horizontal Case
    43. Nested Case
    44. Put a CASE in the ORDER BY
  15. Chapter 15 - View Functions
    1. The Fundamentals of Views
    2. Creating a Simple View to Restrict Sensitive Columns
    3. You SELECT From a View
    4. Creating a Simple View to Restrict Rows
    5. A View Provides Security for Columns and Rows
    6. Basic Rules for Views
    7. How to Modify a View
    8. An Exception to the ORDER BY Rule inside a View
    9. Views Are Sometimes CREATED for Formatting
    10. Creating a View to Join Tables Together
    11. How 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. Altering A Table After a View Has Been Created
    18. A View that Errors After An ALTER
    19. Chapter 16 - Set Operators Functions
    20. Rules of Set Operators
    21. INTERSECT Explained Logically
    22. INTERSECT Explained Logically
    23. UNION Explained Logically
    24. UNION Explained Logically
    25. UNION ALL Explained Logically
    26. UNION ALL Explained Logically
    27. EXCEPT Explained Logically
    28. EXCEPT Explained Logically
    29. Minus Explained Logically
    30. Minus Explained Logically
    31. Testing Your Knowledge
    32. Answer - Testing Your Knowledge
    33. Testing Your Knowledge
    34. Answer - Testing Your Knowledge
    35. An Equal Amount of Columns in both SELECT List
    36. Columns in the SELECT list should be from the same Domain
    37. The Top Query handles all Aliases
    38. The Bottom Query does the ORDER BY (a Number)
    39. Great Trick:Place your Set Operator in a Derived Table
    40. UNION Vs UNION ALL
    41. Using UNION ALL and Literals
    42. A Great Example of how EXCEPT works
    43. USING Multiple SET Operators in a Single Request
    44. Changing the Order of Precedence with Parentheses
    45. Using UNION ALL for speed in Merging Data Sets
  16. Chapter 17 - Table Create and Data Types
    1. Distribution Strategy 1 - Segmented By Hash
    2. Distribution Strategy 2 - Unsegmented
    3. Sorting the Data in a Table CREATE Statement
    4. Even Distribution
    5. Uneven Distribution Where the Data is Non-Unique
    6. Matching Distribution Keys for Co-Location of Joins
    7. Big Table / Small Table Joins
    8. Fact and Dimension Table Distribution Key Designs
    9. Why a Sort Key Improves Performance
    10. Sort Keys Help Group By, Order By and Window Functions
    11. Syntax for Temporary Tables
    12. Temporary Tables Explained
    13. Key Temporary Table Terms
    14. Creating and Populating a Local Temporary Table
    15. Using a Local Temporary Table
    16. Creating and Populating a Global Temporary Table
    17. Creating and Populating a Global Temporary Table
    18. Some Great Examples of Creating a Temporary Table Quickly
    19. Creating a Temporary Table That is Sorted
    20. A Temp Table That Populates Some of the Rows
    21. A Temporary Table With Some of the Columns
  17. 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/SELECT Command
    6. INSERT/SELECT example using All Columns (*)
    7. INSERT/SELECT example with Less Columns
    8. Two UPDATE Examples
    9. Subquery UPDATE Command Syntax
    10. example of Subquery UPDATE Command
    11. Join UPDATE Command Syntax
    12. example of an UPDATE Join Command
    13. Fast UPDATE
    14. Example of Subquery DELETE Command
  18. Chapter 19 - Statistical Aggregate Functions
    1. The Stats Table
    2. The STDDEV_POP Function
    3. A STDDEV_POP Example
    4. The STDDEV_SAMP Function
    5. A STDDEV_SAMP Example
    6. The VAR_POP Function
    7. A VAR_POP Example
    8. The VAR_SAMP Function
    9. A VAR_SAMP Example
    10. The VARIANCE Function
    11. A VARIANCE Example
    12. The CORR Function
    13. A CORR Example
    14. Another CORR Example so you can Compare
    15. The COVAR_POP Function
    16. A COVAR_POP Example
    17. Another COVAR_POP Example so you can Compare
    18. The COVAR_SAMP Function
    19. A COVAR_SAMP Example
    20. Another COVAR_SAMP Example so you can Compare
    21. The REGR_INTERCEPTFunction
    22. A REGR_INTERCEPTExample
    23. Another REGR_INTERCEPT Example so you can Compare
    24. The REGR_SLOPE Function
    25. A REGR_SLOPEExample
    26. Another REGR_SLOPEExample so you can Compare
    27. The REGR_AVGXFunction
    28. A REGR_AVGXExample
    29. Another REGR_AVGXExample so you can Compare
    30. The REGR_AVGYFunction
    31. A REGR_AVGYExample
    32. Another REGR_AVGYExample so you can Compare
    33. The REGR_COUNTFunction
    34. A REGR_COUNTExample
    35. The REGR_R2Function
    36. A REGR_R2Example
    37. The REGR_SXXFunction
    38. A REGR_SXXExample
    39. The REGR_SXYFunction
    40. A REGR_SXYExample
    41. The REGR_SYYFunction
    42. A REGR_SYYExample
    43. Using GROUP BY
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

74,839

Students who have taken Live Online Training

15,233

Organizations who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.41

Students rated our trainers 9.41 out of 10 based on 5,189 reviews

Contact Us or call 1-877-932-8228