Cover image for HP Vertica - Architecture and SQL

HP Vertica - Architecture and SQL

Author Leslie Nolander , Tom Coffing
Release Date: 2015/12/01
ISBN: 9781940540344
Topic:
0%
28
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

One of the most exciting new database inventions is Columnar technology. HP has built one of the best columnar databases in the world. HP Vertica is designed for both on-premises and cloud technology. This book details the architecture of the HP Vertica database and the SQL commands available. The book educates readers on how to create tables and indexes, how the data is distributed, and how the system process the data. Plus, it is followed up with over 500 pages of SQL examples and explanations. This book is a must have for anyone designing, implementing or querying the HP Vertica system. The Authors Tera-Tom Coffing, who has written over 75 successful books on Data Warehousing and Leslie Nolander, Chief Operations Officer (COO) of Coffing Data Warehousing bring a combined 40 years of experience of data warehouse knowledge to create this must have book.

Table of Contents

  1. Cover
  2. The Tera-Tom Video Series
  3. The Tera-Tom Genius Series
  4. Tera-Tom- Author of over 50 Books
  5. The Best Query Tool Works on all Systems
  6. Trademarks and Copyrights
  7. About Tom Coffing
  8. About Leslie Nolander
  9. Contents
  10. 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
  11. 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
  12. 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
  13. Chapter 4 - Nexus
    1. Nexus is Available on the Cloud
    2. Nexus Queries Every Major System
    3. How to Use Nexus
    4. Why is Nexus Special? Visualization and Automatic SQL
    5. Why is Nexus Special? Cross-System Joins
    6. Why is Nexus Special? The Amazing Hub System
    7. Why is Nexus Special? Save Answer Sets as Tables
    8. Why is Nexus Special? Automated Data Movement
    9. Why is Nexus Special? Nexus makes the Servers Talk Directly
    10. What Makes Nexus Special? The Garden of Analysis
    11. The Garden of Analysis Grouping Sets Tab
    12. The Garden of Analysis - Grouping Sets Answer Sets
    13. The Garden of Analysis – Join Tab (1 of 4)
    14. The Garden of Analysis – Join Tab (2 of 4)
    15. The Garden of Analysis – Join Tab (3 of 4)
    16. The Garden of Analysis – Join Tab (4 of 4)
    17. The Garden of Analysis – Charts/Graphs Tab (1 of 4)
    18. The Garden of Analysis – Charts/Graphs Tab (2 of 4)
    19. The Garden of Analysis – Charts/Graphs Tab (3 of 4)
    20. The Garden of Analysis – Charts/Graphs Tab (4 of 4)
    21. The Garden of Analysis – Dynamic Charts Tab (1 of 4)
    22. The Garden of Analysis – Dynamic Charts Tab (2 of 4)
    23. The Garden of Analysis – Dynamic Charts Tab (3 of 4)
    24. The Garden of Analysis – Dynamic Charts Tab (4 of 4)
    25. The Garden of Analysis – Dashboard Tab (1 of 5)
    26. The Garden of Analysis – Dynamic Charts Tab (2 of 5)
    27. The Garden of Analysis – Dynamic Charts Tab (3 of 5)
    28. The Garden of Analysis – Dynamic Charts Tab (4 of 5)
    29. The Garden of Analysis – Dynamic Charts Tab (5 of 5)
    30. Getting to the Super Join Builder
    31. The Super Join Builder is the First Entry in the Menu
    32. The Super Join Builder Shows Tables Visually
    33. Using the Add Join Button
    34. What to Do When No Tables are Joinable?
    35. Drag a Joinable Object into the Super Join Builder
    36. You will see the Add Custom Join Window
    37. Defining the Join Columns
    38. Your Tables Will Appear Together
    39. Select the Columns You Want on the Report
    40. Check out the SQL Tab to See the SQL that has been built
    41. SQL Tab
    42. Hit Execute to get the Report inside the Super Join Builder
    43. The Report is delivered inside the Super Join Builder
    44. Let's Join Two Tables Again (1 of 6)
    45. Let's Join Two Tables Again (2 of 6)
    46. Let's Join Two Tables Again (3 of 6)
    47. Let's Join Two Tables Again (4 of 6)
    48. Let's Join Two Tables Again (5 of 6)
    49. Let's Join Two Tables Again (6 of 6)
    50. The Tabs of the Super Join Builder Philosophy – One Query
    51. The Tabs of the Super Join Builder – Objects Tab
    52. The Tabs of the Super Join Builder – Columns Tab)
    53. The Tabs of the Super Join Builder – Sorting Tab
    54. The Tabs of the Super Join Builder – Joins Tab
    55. The Tabs of the Super Join Builder – SQL Tab
    56. The Tabs of the Super Join Builder – Metadata Tab
    57. The Tabs of the Super Join Builder – Analytics Tab
    58. The Tabs of the SJB – Analytics Tab – OLAP Screen
    59. Getting a Simple CSUM in the Analytics Tab – OLAP
    60. Getting a Simple CSUM – The SQL Automatically Generated
    61. The Answer Set of the CSUM
    62. Getting all of the OLAP functions in the Analytics Tab
    63. A Five Table Join Using the Menu
    64. The First Table is placed in the Super Join Builder
    65. Using the Add Join Cascading Menu
    66. All Five Tables Are In the Super Join Builder
    67. A Five Table Join Two Steps (Cube)
    68. Choose Cube with Columns from the Left Top of the Table
    69. All Tables are Cubed (Joined Together Instantly)
    70. Choose Cube and then Choose Your Columns
    71. Create Cube - Tables Are Joined Without Columns Selected
    72. Create Cube – Select the Columns You Want on the Report
    73. How to join Vertica, Oracle and SQL Server Tables
    74. The Vertica Table is now in the Super Join Builder
    75. Drag the Joining Oracle Table to the Super Join Builder
    76. Defining the Join Columns
    77. Choose the Columns You Want on Your Report
    78. Let's Add a SQL Server Table to our Vertica and Oracle Join
    79. Defining the Join Columns
    80. All Three Tables are now in the Super Join Builder
    81. Change the Hub and Run the Join on Oracle
    82. Change the Hub and Run the Join on SQL Server
    83. Simply Amazing - Change the Hub to the Garden of Analysis
    84. Have the Answer Set Saved Automatically to Any System
    85. Saving the Answer Set to an Oracle or SQL Server System
    86. Saving the Answer Set to a Vertica System
    87. Saving the Answer Set to a Teradata System
  14. 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
  15. 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. Quiz – How many rows come back from the Distinct?
    47. Answer – How many rows come back from the Distinct?
  16. 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. GROUP BY Rollup
    20. GROUP BY Rollup Result Set
  17. 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 Self Join
    39. The Self Join 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
  18. 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. The EXTRACT Command
    16. YEAR, MONTH, and DAY Functions
    17. A Better Technique for YEAR, MONTH, and DAY Functions
    18. Another Version of the EXTRACT Command
    19. EXTRACT from DATES and TIME
    20. Why EXTRACT is a Better Form
    21. EXTRACT with DATE and TIME Literals
    22. EXTRACT of the Month on Aggregate Queries
    23. AGE_IN_MONTHS
    24. AGE_IN_YEARS
    25. DATE_TRUNC
    26. DATEDIFF
    27. DAYOFWEEK
    28. Intervals for Date, Time and Timestamp
    29. Interval Data Types and the Bytes to Store Them
    30. Using Intervals
    31. How a Simple Interval Handles Leap Year
    32. Interval Arithmetic Results
    33. A Time Interval Example
    34. A DATE Interval Example Going Back in Time
    35. A Complex Time Interval Example using CAST
    36. A Complex Time Interval Example using CAST
    37. The OVERLAPS Command
    38. An OVERLAPS Example that Returns No Rows
    39. The OVERLAPS Command using TIME
  19. 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. Finding the First Occurrence using a WITH Derived Table
    6. Finding the Last Occurrence using a WITH Derived Table
    7. Ordered Analytics OVER
    8. RANK and DENSE RANK
    9. RANK Defaults to Ascending Order
    10. Getting RANK to Sort in DESC Order
    11. RANK OVER and PARTITION BY
    12. PERCENT_RANK OVER
    13. PERCENT_RANK OVER with 14 rows in Calculation
    14. PERCENT_RANK OVER with 21 rows in Calculation
    15. Quiz – What Causes the Product_ID to Reset?
    16. Answer to Quiz – What Cause the Product_ID to Reset?
    17. Finding Gaps between Dates
    18. CSUM – Rows Unbounded Preceding Explained
    19. CSUM – Making Sense of the Data
    20. CSUM – Making Even More Sense of the Data
    21. CSUM – The Major and Minor Sort Key(s)
    22. The ANSI CSUM – Getting a Sequential Number
    23. Troubleshooting the ANSI OLAP on a GROUP BY
    24. Reset with a PARTITION BY Statement
    25. PARTITION BY only Resets a Single OLAP not ALL of them
    26. CURRENT ROW AND UNBOUNDED FOLLOWING
    27. Different Windowing Options
    28. Moving Sum has a Moving Window
    29. How ANSI Moving SUM Handles the Sort
    30. Quiz – How is that Total Calculated?
    31. Answer to Quiz – How is that Total Calculated?
    32. Moving SUM every 3-rows Vs a Continuous Average
    33. PARTITION BY Resets an ANSI OLAP
    34. The Moving Window is Current Row and Preceding
    35. How Moving Average Handles the Sort
    36. Moving Average
    37. Moving Average
    38. Quiz – How is that Total Calculated?
    39. Answer to Quiz – How is that Total Calculated?
    40. Quiz – How is that 4th Row Calculated?
    41. Answer to Quiz – How is that 4th Row Calculated?
    42. Moving Average every 3-rows vs a Continuous Average
    43. PARTITION BY Resets an ANSI OLAP
    44. Moving Difference using ANSI Syntax
    45. Moving Difference using ANSI Syntax with Partition By
    46. COUNT OVER for a Sequential Number
    47. COUNT OVER without Rows Unbounded Preceding
    48. Quiz – What caused the COUNT OVER to Reset?
    49. Answer to Quiz – What caused the COUNT OVER to Reset?
    50. The MAX OVER Command
    51. MAX OVER with PARTITION BY Reset
    52. MAX OVER without Rows Unbounded Preceding
    53. The MIN OVER Command
    54. MIN OVER without Rows Unbounded Preceding
    55. Finding a Value of a Column in the Next Row with MIN
    56. The CSUM for Each Product_Id and the Next Start Date
    57. Quiz – Fill in the Blank
    58. Answer – Fill in the Blank
    59. How Ntile Works
    60. Ntile
    61. Ntile Continued
    62. Ntile Percentile
    63. Another Ntile Example
    64. Using Tertiles (Partitions of Four)
    65. NTILE
    66. NTILE Using a Value of 10
    67. NTILE with a Partition
    68. Using FIRST_VALUE
    69. FIRST_VALUE
    70. FIRST_VALUE after Sorting by the Highest Value
    71. FIRST_VALUE with Partitioning
    72. Using LAST_VALUE
    73. LAST_VALUE
    74. Using LAG and LEAD
    75. Using LEAD
    76. Using LEAD With and Offset of 2
    77. LEAD
    78. LEAD With Partitioning
    79. Using LAG
    80. Using LAG with an Offset of 2
    81. LAG
    82. LAG with Partitioning
    83. MEDIAN with Partitioning
    84. CUME_DIST
    85. CUME_DIST with a Partition
    86. SUM (SUM (n))
  20. 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. A Derived example Using 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. Finding the First Occurrence of a Row using WITH
    21. Finding the Last Occurrence of a Row using WITH
    22. Syntax for Temporary Tables
    23. Temporary Tables Explained
    24. Key Temporary Table Terms
    25. Creating and Populating a Local Temporary Table
    26. Using a Local Temporary Table
    27. Creating and Populating a Global Temporary Table
    28. Creating and Populating a Global Temporary Table
    29. Some Great Examples of Creating a Temporary Table Quickly
    30. Creating a Temporary Table That is sorted
    31. A Temp Table That Populates some of the Rows
    32. A Temporary Table with Some of the Columns
  21. 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 Potential NULL 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?
  22. Chapter 13 – Strings
    1. The LENGTH Command Counts Characters
    2. The LENGTH Command – Spaces can Count too
    3. The LENGTH Command and Character Data
    4. LENGTH and CHARACTER_LENGTH Are Equivalent
    5. OCTET_LENGTH
    6. UPPER and LOWER Commands
    7. Using the LOWER Command
    8. A LOWER Command Example
    9. Using the UPPER Command
    10. An UPPER Command Example
    11. Non-Letters are Unaffected by UPPER and LOWER
    12. The TRIM Command trims both Leading and Trailing Spaces
    13. Trim Combined with the CHARACTERS Command
    14. How to TRIM only the Trailing Spaces
    15. A Visual of the TRIM Command Using Concatenation
    16. Trim and Trailing is Case Sensitive
    17. How to TRIM Trailing Letters
    18. The SUBSTRING Command
    19. SUBSTRING and SUBSTR are equal, but use different syntax
    20. How SUBSTRING Works with NO ENDING POSITION
    21. Using SUBSTRING to move backwards
    22. How SUBSTRING Works with a Starting Position of -1
    23. How SUBSTRING Works with an Ending Position of 0
    24. An Example using SUBSTRING, TRIM and CHAR Together
    25. The POSITION Command finds a Letters Position
    26. Quiz – Find that SUBSTRING Starting Position
    27. Answer to Quiz – Find that SUBSTRING Starting Position
    28. Using the SUBSTRING to Find the Second Word On
    29. Quiz – Why did only one Row Return
    30. Answer to Quiz – Why Did only one Row Return
    31. Concatenation
    32. Concatenation and SUBSTRING
    33. Four Concatenations Together
    34. Troubleshooting Concatenation
  23. Chapter 14 – Interrogating the Data
    1. Numeric Manipulation Functions
    2. Finding the Cube Root
    3. Ceiling Gets the Smallest Integer Not Smaller Than X
    4. Floor Finds the Largest Integer Not Greater Than X
    5. The Round Function and Precision
    6. Quiz – What would the Answer be?
    7. Answer to Quiz – What would the Answer be?
    8. The NULLIFZERO Command
    9. The NULLIFZERO vs. Zeroes
    10. Quiz – Fill in the Blank Values in the Answer Set
    11. Answer to Quiz – Fill in the Blank Values in the Answer Set
    12. Quiz – Fill in the Answers for the NULLIF Command
    13. Answer – Fill in the Answers for the NULLIF Command
    14. The ZEROIFNULL Command
    15. Answer to the ZEROIFNULL Question
    16. The COALESCE Command
    17. The COALESCE Answer Set
    18. The Coalesce Quiz
    19. Answer – The Coalesce Quiz
    20. The COALESCE Command – Fill In the Answers
    21. The COALESCE Answer Set
    22. COALESCE is Equivalent to This CASE Statement
    23. Some Great CAST (Convert and Store) Examples
    24. Some Great CAST (Convert and Store) Examples
    25. A Rounding Example
    26. Some Great CAST (Convert and Store) Examples
    27. Quiz - The Basics of the CASE Statements
    28. Answer to Quiz - The Basics of the CASE Statements
    29. Using an ELSE in the Case Statement
    30. Using an ELSE as a Safety Net
    31. Rules for a Valued Case Statement
    32. Rules for a Searched Case Statement
    33. The Basics of the CASE Statements
    34. The Basics of the CASE Statement
    35. Valued Case vs. a Searched Case
    36. Quiz - Valued Case Statement
    37. Answer - Valued Case Statement
    38. Quiz - Searched Case Statement
    39. Answer - Searched Case Statement
    40. Quiz - When NO ELSE is present in CASE Statement
    41. Answer - When NO ELSE is present in CASE Statement
    42. When an ELSE is present in CASE Statement
    43. Answer - When an ELSE is present in CASE Statement
    44. The CASE Challenge
    45. The CASE Challenge Answer
    46. Combining Searched Case and Valued Case
    47. A Trick for getting a Horizontal Case
    48. Nested Case
    49. Put a CASE in the ORDER BY
  24. 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
  25. Chapter 16 – Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION ALL Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Minus Explained Logically
    11. Minus Explained Logically
    12. Testing Your Knowledge
    13. Answer - Testing Your Knowledge
    14. Testing Your Knowledge
    15. Answer - Testing Your Knowledge
    16. An Equal Amount of Columns in both SELECT List
    17. Columns in the SELECT list should be from the same Domain
    18. The Top Query handles all Aliases
    19. The Bottom Query does the ORDER BY (a Number)
    20. Great Trick: Place your Set Operator in a Derived Table
    21. UNION Vs UNION ALL
    22. Using UNION ALL and Literals
    23. A Great Example of how EXCEPT works
    24. USING Multiple SET Operators in a Single Request
    25. Changing the Order of Precedence with Parentheses
    26. Using UNION ALL for speed in Merging Data Sets
  26. 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
  27. 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
  28. 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_INTERCEPT Function
    22. A REGR_INTERCEPT Example
    23. Another REGR_INTERCEPT Example so you can compare
    24. The REGR_SLOPE Function
    25. REGR_SLOPE Example
    26. Another REGR_SLOPE Example so you can compare
    27. The REGR_AVGX Function
    28. A REGR_AVGX Example
    29. Another REGR_AVGX Example so you can compare
    30. The REGR_AVGY Function
    31. A REGR_AVGY Example
    32. Another REGR_AVGY Example so you can compare
    33. The REGR_COUNT Function
    34. A REGR_COUNT Example
    35. The REGR_R2 Function
    36. A REGR_R2 Example
    37. The REGR_SXX Function
    38. A REGR_SXX Example
    39. The REGR_SXY Function
    40. A REGR_SXY Example
    41. The REGR_SYY Function
    42. A REGR_SYY Example
    43. Using GROUP BY
18.218.127.141