Cover image for Greenplum - Architecture and SQL

Greenplum - Architecture and SQL

Author Leona Coffing , Tom Coffing
Release Date: 2015/11/01
ISBN: 9781940540337
Topic:
0%
37
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Greenplum is the first open source data warehouse. Purchased and improved by EMC, sold to Dell, makes Greenplum one of the most powerful and widely-used systems in the world. This incredible MPP data warehouse is designed for on-premises systems and the cloud. This book details the architecture of the Greenplum Data Warehouse and the SQL commands available. This book is perfect for anyone who designs, administers or queries Greenplum. The book educates readers on how to create tables and indexes, how the data is distributed, and how the system processes the data. Plus, it is followed up with over 700 pages of SQL examples and explanations. The Authors Tera-Tom Coffing, who has written over 75 successful books on Data Warehousing and Leona Coffing, Chief Financial Officer (CFO) 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. Current Books in the Tera-Tom Genius Series
  4. Current Books in the Tera-Tom Genius Series
  5. Our Recommended Book In The Tera-Tom Genius Series
  6. Tera-Tom- Author of over 75 Books
  7. The Best Query Tool Works on all Systems
  8. Trademarks and Copyrights
  9. About Tom Coffing
  10. About Leona Coffing
  11. Contents
  12. Chapter 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
  13. Chapter 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
  14. Chapter 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
  15. Chapter 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)
  16. Chapter 5 – Physical Database Design
    1. The Four Stages of Modeling for Greenplum
    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
  17. Chapter 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
  18. Chapter 7 - 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 Greenplum, Oracle and SQL Server Tables
    74. The Greenplum 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 Teradata 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 Greenplum System
    87. Saving the Answer Set to a Teradata System
  19. Chapter 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
  20. Chapter 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. The ilike Command
    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. Numbers are Right Justified and Character Data is Left
    50. Answer – What Data is Left Justified and what is Right?
    51. An example of Data with Left and Right Justification
    52. A Visual of CHARACTER Data vs. VARCHAR Data
    53. RTRIM command Removes Trailing spaces on CHAR Data
    54. Using Like with an AND Clause to Find Multiple Letters
    55. Using Like with an OR Clause to Find Either Letters
  21. Chapter 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?
  22. Chapter 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. 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. Aggregates Return Null on Empty Tables
    17. Keyword HAVING is like an Extra WHERE Clause for Totals
    18. Keyword HAVING tests Aggregates after they are totaled
    19. Getting the Average Values per Column
    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
    27. Quiz - GROUP BY GROUPING SETS Challenge
    28. Answer To Quiz - GROUP BY GROUPING SETS Challenge
  23. Chapter 12 – Join Functions
    1. Greenplum Join Quiz
    2. Greenplum Join Quiz Answer
    3. Redistribution
    4. Big Table Small Table Join Strategy
    5. Duplication of the Smaller Table across All-Distributions
    6. If the Join Condition is the Distribution Key no Movement
    7. Matching Rows That Are On The Same Node Naturally
    8. What if the Join Condition Columns are Not distribution keyes
    9. Strategy 1 of 4 – The Merge Join
    10. Quiz – Redistribute the Employees by their Dept_No
    11. Quiz – Employees' Dept_No landed on segment with Matches
    12. Quiz – Redistribute the Orders to the Proper segment
    13. Answer to Redistribute the Employees by their Dept_No Quiz
    14. Strategy 2 of 4 – The Hash Join
    15. Strategy 3 of 4 – The Nested Join
    16. Strategy 4 of 4 – The Product Join
    17. A Two-Table Join Using Traditional Syntax
    18. A two-table join using Non-ANSI Syntax with Table Alias
    19. You Can Fully Qualify All Columns
    20. A two-table join using ANSI Syntax
    21. Both Queries have the same Results and Performance
    22. Quiz – Can You Finish the Join Syntax?
    23. Answer to Quiz – Can You Finish the Join Syntax?
    24. Quiz – Can You Find the Error?
    25. Answer to Quiz – Can You Find the Error?
    26. Super Quiz – Can You Find the Difficult Error?
    27. Answer to Super Quiz – Can You Find the Difficult Error?
    28. Quiz – Which rows from both tables won’t return?
    29. Answer to Quiz – Which rows from both tables won’t return?
    30. LEFT OUTER JOIN
    31. LEFT OUTER JOIN Results
    32. RIGHT OUTER JOIN
    33. RIGHT OUTER JOIN Example and Results
    34. FULL OUTER JOIN
    35. FULL OUTER JOIN Results
    36. Which Tables are the Left and which Tables are Right?
    37. Answer - Which Tables are the Left and Which are the Right?
    38. INNER JOIN with Additional AND Clause
    39. ANSI INNER JOIN with Additional AND Clause
    40. ANSI INNER JOIN with Additional WHERE Clause
    41. OUTER JOIN with Additional WHERE Clause
    42. OUTER JOIN with Additional AND Clause
    43. OUTER JOIN with Additional AND Clause Results
    44. Quiz – Why is this considered an INNER JOIN?
    45. Evaluation Order for Outer Queries
    46. The DREADED Product Join
    47. The DREADED Product Join Results
    48. The Horrifying Cartesian Product Join
    49. The ANSI Cartesian Join will ERROR
    50. Quiz – Do these Joins Return the Same Answer Set?
    51. Answer – Do these Joins Return the Same Answer Set?
    52. The CROSS JOIN
    53. The CROSS JOIN Answer Set
    54. The Self Join
    55. The Self Join with ANSI Syntax
    56. Quiz – Will both queries bring back the same Answer Set?
    57. Answer – Will both queries bring back the same Answer Set?
    58. Quiz – Will both queries bring back the same Answer Set?
    59. Answer – Will both queries bring back the same Answer Set?
    60. How would you Join these two tables?
    61. An Associative Table is a Bridge that Joins Two Tables
    62. Quiz – Can you write the 3-Table Join?
    63. Answer to Quiz – Can you Write the 3-Table Join?
    64. Quiz – Can you write the 3-Table Join to ANSI Syntax?
    65. Answer – Can you Write the 3-Table Join to ANSI Syntax?
    66. Quiz – Can you Place the ON Clauses at the End?
    67. Answer – Can you Place the ON Clauses at the End?
    68. The 5-Table Join – Logical Insurance Model
    69. Quiz - Write a Five Table Join Using ANSI Syntax
    70. Answer - Write a Five Table Join Using ANSI Syntax
    71. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    72. Answer - Write a Five Table Join Using Non-ANSI Syntax
    73. Quiz –Re-Write this putting the ON clauses at the END
    74. Answer –Re-Write this putting the ON clauses at the END
  24. Chapter 13 – Date Functions
    1. Current_Date
    2. Current_Date and Current_Time
    3. Current_Date and Current_Timestamp
    4. The Many Different Ways to Look at a Timestamp
    5. Current_Time vs. LocalTime with Precision
    6. Local_Time and Local_Timestamp With Precision
    7. Now () and Timeofday () Functions
    8. Adding A Week to a Date
    9. Add or Subtract Days from a date
    10. Formatting Dates and Dollar Amounts
    11. The EXTRACT Command
    12. EXTRACT from DATES and TIME
    13. EXTRACT Command on the Century
    14. EXTRACT Command for the Decade, DOW and DOY
    15. EXTRACT Microseconds, Milliseconds and Millennium
    16. EXTRACT of the Month on Aggregate Queries
    17. Date_part Command
    18. Date_Trunc Command with Time
    19. Date_Trunc Command with Dates
    20. The AGE Command
    21. AGE Challenge
    22. AGE Challenge Results
    23. Epoch
    24. Using Intervals
    25. More Interval Examples
    26. Interval Arithmetic Results
    27. A Complex Time Interval example using CAST
    28. The OVERLAPS Command
    29. An OVERLAPS example that Returns No Rows
    30. The OVERLAPS Command using TIME
    31. Using both CAST and CONVERT in Literal Values
    32. A Better Technique for YEAR, MONTH, and DAY Functions
  25. Chapter 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. To_Number Examples
    9. To_Date
    10. To_Timestamp
    11. Numeric Manipulation Functions
    12. Finding the Cube Root
    13. Ceiling Gets the Smallest Integer Not Smaller Than X
    14. Floor Finds the Largest Integer Not Greater Than X
    15. The Round Function and Precision
  26. Chapter 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. How the Double Parameter Subquery Works
    39. More on how the Double Parameter Subquery Works
    40. Quiz – Write the Triple Subquery
    41. Answer to Quiz – Write the Triple Subquery
    42. Quiz – How many rows return on a NOT IN with a NULL?
    43. Answer – How many rows return on a NOT IN with a NULL?
    44. How to handle a NOT IN with Potential NULL Values
    45. IN is equivalent to =ANY
    46. Using a Correlated Exists
    47. How a Correlated Exists matches up
    48. The Correlated NOT Exists
    49. Quiz – How many rows come back from this NOT Exists?
    50. Answer – How many rows come back from this NOT Exists?
  27. Chapter 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 and ANSI 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 OVER with 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))
  28. Chapter 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. Finding the First Occurrence
    19. Finding the Last Occurrence
    20. Three Steps to Creating a Temporary Table
    21. Three Versions of Creating a Temporary Table
    22. ON COMMIT PRESERVE ROWS is the Greenplum Default
    23. ON COMMIT DELETE ROWS
    24. How to Use the ON COMMIT DELETE ROWS Option
    25. ON COMMIT DROP
    26. How to Use the ON COMMIT DROP Option
    27. Create Table AS
    28. Creating a Temporary Table Using a CTAS that Joins Multiple Tables
    29. Create Table LIKE
    30. Creating a Clustered Index on a Temporary Table
  29. Chapter 18 – Character Strings
    1. The LENGTH Command Counts Characters
    2. The LENGTH Command – Spaces can Count too
    3. The LENGTH Command Doesn't Count Trailing Spaces
    4. UPPER and LOWER Commands
    5. Using the LOWER Command
    6. A LOWER Command Example
    7. Using the UPPER Command
    8. An UPPER Command Example
    9. Non-Letters are Unaffected by UPPER and LOWER
    10. The CHARACTERS Command Counts Characters
    11. The CHARACTERS Command and Character Data
    12. CHARACTER_LENGTH and OCTET_LENGTH
    13. The TRIM Command trims both Leading and Trailing Spaces
    14. Trim Combined with the CHARACTERS Command
    15. How to TRIM only the Trailing Spaces
    16. REGEXP_REPLACE
    17. Concatenation
    18. A Visual of the TRIM Command Using Concatenation
    19. Trim and Trailing is Case Sensitive
    20. How to TRIM Trailing Letters
    21. The SUBSTRING Command
    22. SUBSTRING and SUBSTR are equal, but use different syntax
    23. How SUBSTRING Works with NO ENDING POSITION
    24. Using SUBSTRING to move backwards
    25. How SUBSTRING Works with a Starting Position of -1
    26. How SUBSTRING Works with an Ending Position of 0
    27. An example using SUBSTRING, TRIM and CHAR Together
    28. The POSITION Command finds a Letters Position
    29. Concatenation
    30. Concatenation and SUBSTRING
    31. Four Concatenations Together
    32. Troubleshooting Concatenation
  30. Chapter 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. Some Great CAST (Convert and Store) example
    17. Quiz - The Basics of the CASE Statements
    18. Answer to Quiz - The Basics of the CASE Statements
    19. Using an ELSE in the Case Statement
    20. Using an ELSE as a Safety Net
    21. Rules for a Valued Case Statement
    22. Rules for a Searched Case Statement
    23. Valued Case Vs. A Searched Case
    24. Quiz - Valued Case Statement
    25. Answer - Valued Case Statement
    26. Quiz - Searched Case Statement
    27. Answer - Searched Case Statement
    28. The CASE Challenge
    29. The CASE Challenge Answer
    30. Combining Searched Case and Valued Case
    31. A Trick for getting a Horizontal Case
    32. Nested Case
  31. Chapter 20 – Set Operators Functions
    1. Rules of Set Operators
    2. Rules of Set Operators
    3. INTERSECT Explained Logically
    4. INTERSECT Explained Logically
    5. UNION Explained Logically
    6. UNION Explained Logically
    7. UNION ALL Explained Logically
    8. UNION ALL Explained Logically
    9. EXCEPT Explained Logically
    10. EXCEPT Explained Logically
    11. An Equal Amount of Columns in both SELECT List
    12. Columns in the SELECT list should be from the same Domain
    13. The Top Query handles all Aliases
    14. The Bottom Query does the ORDER BY (a Number)
    15. Great Trick: Place your Set Operator in a Derived Table
    16. UNION Vs UNION ALL
    17. Using UNION ALL and Literals
    18. A Great example of how EXCEPT works
    19. Quiz – Build that Query
    20. Answer To Quiz – Build that Query
    21. USING Multiple SET Operators in a Single Request
    22. Changing the Order of Precedence with Parentheses
    23. Using UNION ALL for speed in Merging Data Sets
  32. Chapter 21 – View Functions
    1. The Fundamentals of Views
    2. Creating a Simple View to Restrict Sensitive Columns
    3. Creating a Simple View to Restrict Rows
    4. Basic Rules for Views
    5. Exception to the ORDER BY Rule inside a View
    6. Views sometimes CREATED for Formatting
    7. Creating a View to Join Tables Together
    8. Another Way to Alias Columns in a View CREATE
    9. The Standard Way Most Aliasing is done
    10. What Happens When Both Aliasing Options Are Present
    11. Resolving Aliasing Problems in a View CREATE
    12. Answer to Resolving Aliasing Problems in a View CREATE
    13. Aggregates on View Aggregates
    14. Altering A Table
    15. Altering a Table after a View has been Created
    16. A View that Errors after an ALTER
  33. Chapter 22 – Table Create and Data Types
    1. Greenplum Has Only Two Distribution Policies
    2. Creating a Table with a Single Column Distribution Key
    3. The Default Table Storage is a Heap
    4. Creating a Table With a Multi-Column Distribution Key
    5. Creating a Table with Random Distribution
    6. Creating a Table with No Distribution Key
    7. Guidelines for Partitioning a Table
    8. Creating a Partitioned Table Using a Range
    9. A Visual of One Year of Data with Range Partitioning
    10. Creating a Partitioned Table Using a Range Per Day
    11. A Visual of One Year of Data with Range per Day
    12. Creating a Partitioned Table Using a List
    13. Creating a Multi-Level Partitioned Table
    14. Changing a Table to a Partitioned Table
    15. Not Null Constraints
    16. Unique Constraints
    17. Primary Key Constraints
    18. Check Constraints
    19. Append Only Tables
    20. Storage is Either Row, Column, or a Combination of Both
    21. Column-Orientated Tables
    22. CREATE INDEX Syntax
    23. CREATE INDEX Syntax
    24. Create Table LIKE
    25. Greenplum Data Types
    26. Greenplum Data Types Continued
    27. Greenplum Data Types Continued
    28. Greenplum Data Types Continued
    29. Greenplum Data Types Continued
  34. Chapter 23 – 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 example with Syntax 3
    6. INSERT/SELECT Command
    7. INSERT/SELECT example using All Columns (*)
    8. INSERT/SELECT example with Less Columns
    9. The UPDATE Command Basic Syntax
    10. Two UPDATE Examples
    11. Subquery UPDATE Command Syntax
    12. Example of Subquery UPDATE Command
    13. Join UPDATE Command Syntax
    14. Example of an UPDATE Join Command
    15. Fast UPDATE
    16. The DELETE Command Basic Syntax
    17. DELETE and TRUNCATE Examples
    18. To DELETE or to TRUNCATE
    19. Subquery and Join DELETE Command Syntax
    20. Example of Subquery DELETE Command
  35. Chapter 24 – ANALYZE and VACUUM
    1. ANALYZE
    2. ANALYZE Options
    3. What Columns Should You Analyze?
    4. Why Analyze?
    5. VACUUM
    6. VACUUM Options
  36. Chapter 25 – Greenplum Explain
    1. How to See an EXPLAIN Plan
    2. The Eight Rules to Reading an EXPLAIN Plan
    3. Interpreting Keywords in an EXPLAIN Plan
    4. Interpreting an EXPLAIN Plan
    5. A Single Segment Retrieve – The Fastest Query
    6. EXPLAIN With an ORDER BY Statement
    7. EXPLAIN ANALYZE
    8. EXPLAIN With a Range Query on a Table Partitioned By Day
    9. EXPLAIN That Uses a B-Tree Index Scan
    10. EXPLAIN That Uses a Bitmap Scan
    11. EXPLAIN With a Simple Subquery
    12. EXPLAIN With a Columnar Query
    13. EXPLAIN With a Clustered Index
    14. The Most Important Concept for Joins is the Distribution Key
    15. EXPLAIN With Join that has to Move Data
    16. EXPLAIN With Join that has to Move Data
    17. Changing the Join Query Changes the EXPLAIN Plan
    18. Analyzing the Tables Structures For a 3-Table Join
    19. An EXPLAIN For a 3-Table Join
    20. Explain of a Derived Table vs. a Correlated Subquery
    21. Explain of the Correlated Subquery
    22. Explain of the Derived Table
  37. Chapter 26 – 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. A 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 COVAR_POP 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