Cover image for Actian Matrix (Formely ParAccel) - Architecture and SQL

Actian Matrix (Formely ParAccel) - Architecture and SQL

Author Ed Bernier , Tom Coffing
Release Date: 2015/10/01
ISBN: 9781940540313
Topic:
0%
30
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 technologies is Columnar and one of the premier pioneers of this technology is Actian’s Matrix database. This book details the architecture of the Matrix system and shows all SQL commands with real-world examples that work. This book educates readers on how to create tables and indexes, how the data is distributed, how columnar works, and how the system processes 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 a Matrix system. The Authors, Tera-Tom Coffing and Ed Bernier, bring experience and expertise together. Tom Coffing has written over 70 books on all technologies and is considered one of the best technical authors in the world. Ed Bernier is a past employee of Actian and Netezza, and has inside knowledge because of his system engineering background on a wide variety of systems.

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 Ed Bernier
  9. Contents
  10. Chapter 1 – What is Columnar?
    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. A Table has Columns and Rows
    6. Rows are Placed Inside a Data Block
    7. Moving Data Blocks is Like Checking In Luggage
    8. Facts That Are Disturbing
    9. Why Columnar?
    10. Row Based Blocks vs. Columnar Based Blocks
    11. As Row-Based Tables Get Bigger, the Blocks Split
    12. Data Blocks Are Processed One at a Time Per Unit
    13. Columnar Tables Store Each Column in Separate Blocks
    14. Visualize the Data – Rows vs. Columns
    15. The Architecture of Actian Matrix
    16. Matrix has Linear Scalability
    17. Distribution Styles
    18. Distribution Key Where the Data is Unique
    19. Another Way to Create a Table
    20. Distribution Key Where the Data is Non-Unique
    21. Even Distribution Key
    22. Matching Distribution Keys for Co-Location of Joins
    23. Big Table / Small Table Joins
    24. Fact and Dimension Table Distribution Key Designs
    25. Improving Performance By Defining a Sort Key
    26. Sort Keys Help Group By, Order By and Window Functions
    27. Each Block Comes With Metadata
    28. How Data Might Look On A Slice
    29. Question – How Many Blocks Move Into Memory?
    30. Answer – How Many Blocks Move Into Memory?
    31. Quiz – Master that Query With the Metadata
    32. Answer to Quiz – Master that Query With the Metadata
    33. The ANALYZE Command Collects Statistics
    34. Matrix Automatically ANALYZES Some Create Statements
    35. What is a Vacuum?
    36. When is a Good Time to Vacuum?
    37. The VACUUM Command Grooms a Table
    38. The Matrix database catalog also needs periodic vacuuming and indexing
    39. Database Limits
    40. Creating a Database
    41. Creating a User
    42. Dropping a User
    43. Inserting Into a Table
    44. Renaming a Table or a Column
    45. Adding and Dropping a Column to a Table
  11. Chapter 2 – Best Practices for Table Design
    1. Converting Table Structures to Actian Matrix
    2. Converting Table Structures to Actian Matrix Finale
    3. Best Practices for Designing Tables
    4. Choose the Best Sort Key
    5. Each Block Comes With Metadata
    6. Creating a Sort Key
    7. Sort Keys Help Group By, Order By and Window Functions
    8. Choose a Great Distribution Key
    9. Distribution Key Where the Data is Unique
    10. Matching Distribution Keys for Co-Location of Joins
    11. Big Table / Small Table Joins
    12. Define Primary Key and Foreign Key Constraints
    13. Primary Key and Foreign Key Examples
    14. Use the Smallest Column Size When Creating Tables
    15. Use Date/Time Data Types for Date Columns
    16. Specify Redundant Predicates on the Sort Column
    17. Setting the statement_timeout to Abort Long Queries
  12. Chapter 3 – Systems Tables
    1. Actian Matrix System Tables
    2. Trouble Shooting Catalog Table pg_table_def
    3. Seeing the System Tables in your Nexus Tree
    4. Catalog Table pg_table_def
    5. Checking Tables for Skew (Poor Distribution)
    6. Checking All Statements That Used the Analyze Command
    7. Checking Tables for Skew (Poor Distribution)
    8. Checking for Details about the Last Copy Operation
    9. Checking When a Table Has Last Been Analyzed
    10. Checking For Column Information on a Table
    11. System tables for troubleshooting data loads
    12. Determining Whether a Query is Writing to Disk
  13. Chapter 4 – Compression
    1. Compression Types
    2. Byte Dictionary Compression
    3. Delta Encoding
    4. Deflate Encoding - Lempel–Ziv–Oberhumer (LZO)
    5. Mostly Encoding
    6. Runlength encoding
    7. Text255 and Text32k Encodings
    8. Analyze Compression using xpx ‘complyze’
    9. Analyze Results from xpx ‘complyze’
    10. Copy
  14. Chapter 5 - Temporary Tables
    1. Create Table Syntax
    2. Basic Temporary Table Examples
    3. More Advanced Temporary Table Examples
    4. Advanced Temporary Table Examples
    5. Table Limits and CTAS
    6. Performing a Deep Copy
    7. Deep Copy Using the Original DDL
    8. Deep Copy Using A CTAS
    9. Deep Copy Using A Create Table LIKE
    10. Deep Copy by Creating a Temp Table and Truncating Original
    11. CREATING A Derived Table
    12. The Three Components of a Derived Table
    13. Naming the Derived Table
    14. Aliasing the Column Names in the Derived Table
    15. Visualize This Derived Table
    16. Most Derived Tables Are Used To Join To Other Tables
    17. Multiple Ways to Alias the Columns in a Derived Table
    18. Our Join Example with a Different Column Aliasing Style
    19. Column Aliasing Can Default for Normal Columns
    20. CREATING a Derived Table using the WITH Command
    21. Our Join Example With the WITH Syntax
    22. WITH Statement That Uses a SELECT *
    23. A WITH Clause That Produces Two Tables
    24. The Same Derived Query shown Three Different Ways
    25. Quiz - Answer the Questions
    26. Answer to Quiz - Answer the Questions
    27. Clever Tricks on Aliasing Columns in a Derived Table
    28. A Derived Table lives only for the lifetime of a single query
    29. An Example of Two Derived Tables in a Single Query
    30. Connecting To Matrix Via Nexus
    31. Connecting To Matrix Via Nexus
    32. Connecting To Matrix Via Nexus
    33. Connecting To Matrix Via Nexus
  15. Chapter 6 – Explain
    1. Three Ways to Run an EXPLAIN
    2. EXPLAIN – Steps, Segments and Streams
    3. EXPLAIN Terms for Scans and Joins
    4. EXPLAIN Terms for Aggregation and Sorts
    5. EXPLAIN Terms for Set Operators and Miscellaneous Terms
    6. EXPLAIN Terms for Set Operators and Miscellaneous Terms
    7. EXPLAIN Example and the Cost
    8. EXPLAIN Example and the Rows
    9. EXPLAIN Example and the Width
    10. Simple EXPLAIN Example and the Costs
    11. EXPLAIN Join Example Using DS_BCAST_INNER
    12. EXPLAIN Join Example Using DS_DIST_NONE
    13. EXPLAIN Showing DS_DIST_NONE Visually
    14. EXPLAIN With a Warning
    15. EXPLAIN for Ordered Analytics Such as CSUM
    16. EXPLAIN for Scalar Aggregate Functions
    17. EXPLAIN for HashAggregate Functions
    18. EXPLAIN Using Limit, Merge and Sort
    19. EXPLAIN Using a WHERE Clause Filter
    20. EXPLAIN Using the Keyword Distinct
    21. EXPLAIN for Subqueries
  16. Chapter 7 - Basic SQL Functions
    1. Finding the Current Schema on the Leader Node
    2. Getting Things Setup in Your Search Path
    3. Five Details You Need To Know About the Search_Path
    4. Introduction
    5. SELECT * (All Columns) in a 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. Comments using Double Dashes are Single Line Comments
    23. Comments for Multi-Lines
    24. Comments for Multi-Lines as Double Dashes Per Line
    25. A Great Technique for Comments to Look for SQL Errors
  17. Chapter 8 – The WHERE Clause
    1. Using Limit to bring back a Sample
    2. Using Limit with an Order By Statement
    3. The WHERE Clause limits Returning Rows
    4. Using a Column ALIAS throughout the SQL
    5. Double Quoted Aliases are for Reserved Words and Spaces
    6. Character Data needs Single Quotes in the WHERE Clause
    7. Character Data needs Single Quotes, but Numbers Don’t
    8. NULL means UNKNOWN DATA so Equal (=) won’t Work
    9. Use IS NULL or IS NOT NULL when dealing with NULLs
    10. NULL is UNKNOWN DATA so NOT Equal won’t Work
    11. Use IS NULL or IS NOT NULL when dealing with NULLs
    12. Using Greater Than or Equal To (>=)
    13. AND in the WHERE Clause
    14. Troubleshooting AND
    15. OR in the WHERE Clause
    16. Troubleshooting Or
    17. Troubleshooting Character Data
    18. Using Different Columns in an AND Statement
    19. Quiz – How many rows will return?
    20. Answer to Quiz – How many rows will return?
    21. What is the Order of Precedence?
    22. Using Parentheses to change the Order of Precedence
    23. Using an IN List in place of OR
    24. The IN List is an Excellent Technique
    25. IN List vs. OR brings the same Results
    26. Using a NOT IN List
    27. A Technique for Handling Nulls with a NOT IN List
    28. Another Technique for Handling Nulls with a NOT IN List
    29. BETWEEN is Inclusive
    30. NOT BETWEEN is Also Inclusive
    31. LIKE command Underscore is Wildcard for one Character
    32. LIKE Command Works Differently on Char Vs Varchar
    33. The Ilike Command Is NOT Case Sensitive
    34. Troubleshooting LIKE Command on Character Data
    35. Introducing the TRIM Command
    36. Quiz – What Data is Left Justified and what is Right?
    37. Numbers are Right Justified and Character Data is Left
    38. Answer – What Data is Left Justified and what is Right?
    39. An Example of Data with Left and Right Justification
    40. A Visual of CHARACTER Data vs. VARCHAR Data
    41. Use the TRIM command to remove spaces on CHAR Data
    42. Like and Your Escape Character of Choice
    43. Like and the Default Escape Character
    44. Similar To Operators
    45. Similar To Operators
    46. Similar To Example with Lower Case Letters
    47. Similar To Example with Lower and Upper Case Letters
    48. Similar To Example with Multiple Occurrences
    49. Multiple Occurrences Must Be Consecutive
  18. Chapter 9 – Distinct Vs Group By AND TOP
    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?
    5. TOP Command
    6. TOP Command is brilliant when ORDER BY is Used!
    7. What is the Difference between TOP and LIMIT?
  19. Chapter 10 - Aggregation
    1. Quiz – You calculate the Answer Set in your own Mind
    2. Answer – You calculate the Answer Set in your own Mind
    3. The 3 Rules of Aggregation
    4. There are Five Aggregates
    5. Quiz – How many rows come back?
    6. Answer – How many rows come back?
    7. Troubleshooting Aggregates
    8. GROUP BY when Aggregates and Normal Columns Mix
    9. GROUP BY delivers one row per Group
    10. GROUP BY Dept_No or GROUP BY 1 the same thing
    11. Limiting Rows and Improving Performance with WHERE
    12. WHERE Clause in Aggregation limits unneeded Calculations
    13. Keyword HAVING tests Aggregates after they are Totaled
    14. Keyword HAVING is like an Extra WHERE Clause for Totals
  20. Chapter 11 – 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. Left Outer Joins Compatible with Oracle
    17. RIGHT OUTER JOIN
    18. RIGHT OUTER JOIN Example and Results
    19. Right Outer Joins Compatible with Oracle
    20. FULL OUTER JOIN
    21. FULL OUTER JOIN Results
    22. Which Tables are the Left and which are the Right?
    23. Answer - Which Tables are the Left and which are the Right?
    24. INNER JOIN with Additional AND Clause
    25. ANSI INNER JOIN with Additional AND Clause
    26. ANSI INNER JOIN with Additional WHERE Clause
    27. OUTER JOIN with Additional WHERE Clause
    28. OUTER JOIN with Additional AND Clause
    29. OUTER JOIN with Additional AND Clause Results
    30. Quiz – Why is this considered an INNER JOIN?
    31. The DREADED Product Join
    32. The DREADED Product Join Results
    33. The Horrifying Cartesian Product Join
    34. The ANSI Cartesian Join will ERROR
    35. Quiz – Do these Joins Return the Same Answer Set?
    36. Answer – Do these Joins Return the Same Answer Set?
    37. The CROSS JOIN
    38. The CROSS JOIN Answer Set
    39. The Self Join
    40. The Self Join with ANSI Syntax
    41. Quiz – Will both queries bring back the same Answer Set?
    42. Answer – Will both queries bring back the same Answer Set?
    43. Quiz – Will both queries bring back the same Answer Set?
    44. Answer – Will both queries bring back the same Answer Set?
    45. How would you Join these two tables?
    46. An Associative Table is a Bridge that Joins Two Tables
    47. Quiz – Can you Write the 3-Table Join?
    48. Answer to Quiz – Can you Write the 3-Table Join?
    49. Quiz – Can you Write the 3-Table Join to ANSI Syntax?
    50. Answer – Can you Write the 3-Table Join to ANSI Syntax?
    51. Quiz – Can you Place the ON Clauses at the End?
    52. Answer – Can you Place the ON Clauses at the End?
    53. The 5-Table Join – Logical Insurance Model
    54. Quiz - Write a Five Table Join Using ANSI Syntax
    55. Answer - Write a Five Table Join Using ANSI Syntax
    56. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    57. Answer - Write a Five Table Join Using Non-ANSI Syntax
    58. Quiz –Re-Write this putting the ON clauses at the END
    59. Answer –Re-Write this putting the ON clauses at the END
  21. Chapter 12 – Date Functions
    1. Current_Date
    2. TIMEOFDAY()
    3. SYSDATE Returns a Timestamp with Microseconds
    4. GETDATE Returns a Timestamp without Microseconds
    5. Add or Subtract Days from a date
    6. The ADD_MONTHS Command Returns a Timestamp
    7. The ADD_MONTHS Command with Trunc Removes Time
    8. ADD_MONTHS Command to Add 1-Year or 5-Years
    9. Dateadd Function and Add_Months Function are Different
    10. The EXTRACT Command
    11. EXTRACT from DATES and TIME
    12. EXTRACT with DATE and TIME Literals
    13. EXTRACT of the Month on Aggregate Queries
    14. The Datediff command
    15. The Datediff Function on Column Data
    16. The Date_Part Function Using a Date
    17. The Date_Part Function Using a Time
    18. Date_Part Abbreviations
    19. The to_char command
    20. Conversion Functions
    21. Conversion Function Templates
    22. Conversion Function Templates Continued
    23. Formatting a Date
    24. A Summary of Math Operations on Dates
    25. Using a Math Operation to find your Age in Years
    26. Date Related Functions
    27. A Side Title example with Reserved Words as an Alias
    28. Implied Extract of Day, Month and Year
    29. DATE_PART Function
    30. DATE_PART Function using an ALIAS
    31. DATE_TRUNC Function
    32. DATE_TRUNC Function using TIME
    33. MONTHS_BETWEEN Function
    34. MONTHS_BETWEEN Function in Action
    35. ANSI TIME
    36. ANSI TIMESTAMP
    37. Matrix TIMESTAMP Function
    38. Matrix TO_TIMESTAMP Function
    39. Matrix NOW() Function
    40. Matrix TIMEOFDAY Function
    41. Matrix AGE Function
    42. Time Zones
    43. Setting Time Zones
    44. Using Time Zones
    45. Intervals for Date, Time and Timestamp
    46. Using Intervals
    47. Troubleshooting the Basics of a Simple Interval
    48. Interval Arithmetic Results
    49. A Date Interval Example
    50. A Time Interval Example
    51. A DATE Interval Example
    52. A Complex Time Interval Example using CAST
    53. A Complex Time Interval Example using CAST
    54. The OVERLAPS Command
    55. An OVERLAPS Example that Returns No Rows
    56. The OVERLAPS Command using TIME
    57. The OVERLAPS Command using a NULL Value
  22. Chapter 13 – 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. Reset with a PARTITION BY Statement
    8. PARTITION BY only Resets a Single OLAP not ALL of them
    9. ANSI Moving Window is Current Row and Preceding n Rows
    10. How ANSI Moving SUM Handles the Sort
    11. Quiz – How is that Total Calculated?
    12. Answer to Quiz – How is that Total Calculated?
    13. Moving SUM every 3-rows Vs a Continuous Average
    14. Partition by Resets an ANSI OLAP
    15. Moving Average
    16. The Moving Window is Current Row and Preceding
    17. How Moving Average Handles the Sort
    18. Quiz – How is that Total Calculated?
    19. Answer to Quiz – How is that Total Calculated?
    20. Quiz – How is that 4th Row Calculated?
    21. Answer to Quiz – How is that 4th Row Calculated?
    22. Moving Average every 3-rows Vs a Continuous Average
    23. Partition By Resets an ANSI OLAP
    24. RANK Defaults to Ascending Order
    25. Getting RANK to Sort in DESC Order
    26. RANK() OVER and PARTITION BY
    27. RANK() OVER And LIMIT
    28. PERCENT_RANK() OVER
    29. PERCENT_RANK() OVER with 14 rows in Calculation
    30. PERCENT_RANK() OVER with 21 rows in Calculation
    31. Quiz – What Causes the Product_ID to Reset?
    32. Answer to Quiz – What Cause the Product_ID to Reset?
    33. COUNT OVER for a Sequential Number
    34. Quiz – What caused the COUNT OVER to Reset?
    35. Answer to Quiz – What caused the COUNT OVER to Reset?
    36. The MAX OVER Command
    37. MAX OVER with PARTITION BY Reset
    38. The MIN OVER Command
    39. Quiz – Fill in the Blank
    40. Answer – Fill in the Blank
    41. The Row_Number Command
    42. Quiz – How did the Row_Number Reset?
    43. Quiz – How did the Row_Number Reset?
    44. Standard Deviation Functions Using STDDEV / OVER
    45. Standard Deviation Functions and STDDEV / OVER Syntax
    46. STDDEV / OVER Example
    47. VARIANCE / OVER Syntax
    48. Variance Functions Using VARIANCE / OVER
    49. Using VARIANCE with PARTITION BY Example
    50. Using FIRST_VALUE and LAST_VALUE
    51. Using FIRST_VALUE
    52. Using LAST_VALUE
    53. Using LAG and LEAD
    54. Using LEAD
    55. Using LEAD With and Offset of 2
    56. Using LAG
    57. Using LAG with an Offset of 2
  23. Chapter 14 – Temporary Tables
    1. CREATING A Derived Table
    2. The Three Components of a Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in The Derived Table
    5. Visualize This Derived Table
    6. Most Derived Tables Are Used To Join To Other Tables
    7. Multiple Ways to Alias the Columns in a Derived Table
    8. Our Join Example with a Different Column Aliasing Style
    9. Column Aliasing Can Default for Normal Columns
    10. CREATING a Derived Table using the WITH Command
    11. Our Join Example With the WITH Syntax
    12. WITH
    13. A WITH Clause That Produces Two Tables
    14. The Same Derived Query shown Three Different Ways
    15. Quiz - Answer the Questions
    16. Answer to Quiz - Answer the Questions
    17. Clever Tricks on Aliasing Columns in a Derived Table
    18. A Derived Table lives only for the lifetime of a single query
    19. An Example of Two Derived Tables in a Single Query
    20. Create Table Syntax
    21. Basic Temporary Table Examples
    22. More Advanced Temporary Table Examples
    23. Advanced Temporary Table Examples
    24. Performing a Deep Copy
    25. Deep Copy Using the Original DDL
    26. Deep Copy Using A CTAS
    27. Deep Copy Using A Create Table LIKE
    28. Deep Copy by Creating a Temp Table and Truncating Original
  24. 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 or 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 Subquery with an Aggregate
    16. Answer to Quiz- Write the Subquery with an Aggregate
    17. Quiz- Write the Correlated Subquery
    18. Answer to Quiz- Write the Correlated Subquery
    19. The Basics of a Correlated Subquery
    20. The Top Query always runs first in a Correlated Subquery
    21. Correlated Subquery Example vs. a Join with a Derived Table
    22. Quiz- A Second Chance to Write a Correlated Subquery
    23. Answer - A Second Chance to Write a Correlated Subquery
    24. Quiz- A Third Chance to Write a Correlated Subquery
    25. Answer - A Third Chance to Write a Correlated Subquery
    26. Quiz- Last Chance to Write a Correlated Subquery
    27. Answer – Last Chance to Write a Correlated Subquery
    28. Quiz- Write the NOT Subquery
    29. Answer to Quiz- Write the NOT Subquery
    30. Quiz- Write the Subquery using a WHERE Clause
    31. Answer - Write the Subquery using a WHERE Clause
    32. Quiz- Write the Subquery with Two Parameters
    33. Answer to Quiz- Write the Subquery with Two Parameters
    34. How the Double Parameter Subquery Works
    35. More on how the Double Parameter Subquery Works
    36. Quiz – Write the Triple Subquery
    37. Answer to Quiz – Write the Triple Subquery
    38. Quiz – How many rows return on a NOT IN with a NULL?
    39. Answer – How many rows return on a NOT IN with a NULL?
    40. How to handle a NOT IN with Potential NULL Values
    41. Using a Correlated Exists
    42. How a Correlated Exists matches up
    43. The Correlated NOT Exists
    44. The Correlated NOT Exists Answer Set
    45. Quiz – How many rows come back from this NOT Exists?
    46. Answer – How many rows come back from this NOT Exists?
  25. Chapter 16 – Substrings and Positioning Functions
    1. The TRIM Command trims both Leading and Trailing Spaces
    2. A Visual of the TRIM Command Using Concatenation
    3. Trim and Trailing is Case Sensitive
    4. How to TRIM Trailing Letters
    5. The SUBSTRING Command
    6. How SUBSTRING Works with NO ENDING POSITION
    7. Using SUBSTRING to move Backwards
    8. How SUBSTRING Works with a Starting Position of -1
    9. How SUBSTRING Works with an Ending Position of 0
    10. The POSITION Command finds a Letters Position
    11. Quiz – Find that SUBSTRING Starting Position
    12. Answer to Quiz – Find that SUBSTRING Starting Position
    13. Using the SUBSTRING to Find the Second Word On
    14. Quiz – Why did only one Row Return?
    15. Answer to Quiz – Why Did only one Row Return?
    16. Concatenation
    17. Concatenation and SUBSTRING
    18. Four Concatenations Together
    19. Troubleshooting Concatenation
    20. Declaring a Cursor
  26. Chapter 17 – 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 Blank Values in the Answer Set
    5. Answer to Quiz – Fill in the Blank Values in the Answer Set
    6. Quiz – Fill in the Answers for the NULLIF Command
    7. Quiz – Fill in the Answers for the NULLIF Command
    8. Quiz – Fill in the Answers for the NULLIF Command
    9. Quiz – Fill in the Answers for the NULLIF Command
    10. The ISNULL, NVL and COALESCE Commands
    11. The ISNULL, NVL and COALESCE Commands
    12. The ISNULL, NVL and COALESCE more examples
    13. The COALESCE Answer Set
    14. The Coalesce Quiz
    15. Answer – The Coalesce Quiz
    16. The Basics of CAST (Convert And STore)
    17. Some Great CAST (Convert And STore) Examples
    18. Some Great CAST (Convert And STore) Examples
    19. Some Great CAST (Convert And STore) Examples
    20. The Basics of the CASE Statements
    21. The Basics of the CASE Statement
    22. Valued Case Vs. A Searched Case
    23. Quiz - Valued Case Statement
    24. Answer - Valued Case Statement
    25. Quiz - Searched CASE Statement
    26. Answer - Searched CASE Statement
    27. Quiz - When NO ELSE is present in CASE Statement
    28. Answer - When NO ELSE is present in CASE Statement
    29. When an ELSE is present in CASE Statement
    30. Answer - When an ELSE is present in CASE Statement
    31. When an Alias is NOT used in a CASE Statement
    32. Answer - When an Alias is NOT used in a CASE Statement
    33. Combining Searched Case and Valued Case
    34. Nested Case
    35. Put a CASE in the ORDER BY
  27. Chapter 18 – View Functions
    1. Creating a Simple View to Restrict Sensitive Columns
    2. Creating a Simple View to Restrict Rows
    3. Creating a View to Join Tables Together
    4. You Select From a View
    5. Basic Rules for Views
    6. An ORDER BY Example Inside of a View
    7. An ORDER BY Inside of a View that is Queried Differently
    8. Creating a View with Ordered Analytics
    9. Creating a View with the TOP Command
    10. Creating a View with the LIMIT Command
    11. Altering a Table
    12. Altering a Table after a View has been Created
    13. A View that Errors after An ALTER
    14. Troubleshooting a View
    15. Updating Data in a Table through a View
  28. Chapter 19 – 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 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. A Great Example of how EXCEPT works
  29. Chapter 20 – Statistical Aggregate Functions
    1. The Stats Table
    2. STDDEV
    3. Casting STDDEV_SAMP and SQRT (VAR_SAMP)
    4. The STDDEV_POP Function
    5. A STDDEV_POP Example
    6. The STDDEV_SAMP Function
    7. A STDDEV_SAMP Example
    8. The VAR_POP Function
    9. A VAR_POP Example
    10. The VAR_SAMP Function
    11. A VAR_SAMP Function
  30. Chapter 21 – Nexus
    1. Nexus is Now Available on the Microsoft Azure 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 Tab 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
    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. Hound Dog Compression
    39. Hound Dog Compression on Teradata
    40. Hound Dog Compression on Teradata
18.188.152.162