Contents
The Basics of a Single Computer
Data in Memory is fast as Lightning
Rows are Placed Inside a Data Block
Moving Data Blocks is Like Checking In Luggage
Row Based Blocks vs. Columnar Based Blocks
As Row-Based Tables Get Bigger, the Blocks Split
Data Blocks Are Processed One at a Time Per Unit
Columnar Tables Store Each Column in Separate Blocks
Visualize the Data – Rows vs. Columns
The Architecture of Actian Matrix
Distribution Key Where the Data is Unique
Distribution Key Where the Data is Non-Unique
Matching Distribution Keys for Co-Location of Joins
Fact and Dimension Table Distribution Key Designs
Improving Performance By Defining a Sort Key
Sort Keys Help Group By, Order By and Window Functions
Each Block Comes With Metadata
How Data Might Look On A Slice
Question – How Many Blocks Move Into Memory?
Answer – How Many Blocks Move Into Memory?
Quiz – Master that Query With the Metadata
Answer to Quiz – Master that Query With the Metadata
The ANALYZE Command Collects Statistics
Matrix Automatically ANALYZES Some Create Statements
When is a Good Time to Vacuum?
The VACUUM Command Grooms a Table
The Matrix database catalog also needs periodic vacuuming and indexing
Adding and Dropping a Column to a Table
Chapter 2 – Best Practices for Table Design
Converting Table Structures to Actian Matrix
Converting Table Structures to Actian Matrix Finale
Best Practices for Designing Tables
Each Block Comes With Metadata
Sort Keys Help Group By, Order By and Window Functions
Choose a Great Distribution Key
Distribution Key Where the Data is Unique
Matching Distribution Keys for Co-Location of Joins
Define Primary Key and Foreign Key Constraints
Primary Key and Foreign Key Examples
Use the Smallest Column Size When Creating Tables
Use Date/Time Data Types for Date Columns
Specify Redundant Predicates on the Sort Column
Setting the statement_timeout to Abort Long Queries
Trouble Shooting Catalog Table pg_table_def
Seeing the System Tables in your Nexus Tree
Checking Tables for Skew (Poor Distribution)
Checking All Statements That Used the Analyze Command
Checking Tables for Skew (Poor Distribution)
Checking for Details about the Last Copy Operation
Checking When a Table Has Last Been Analyzed
Checking For Column Information on a Table
System tables for troubleshooting data loads
Determining Whether a Query is Writing to Disk
Deflate Encoding - Lempel–Ziv–Oberhumer (LZO)
Analyze Compression using xpx ‘complyze’
Analyze Results from xpx ‘complyze’
Basic Temporary Table Examples
More Advanced Temporary Table Examples
Advanced Temporary Table Examples
Deep Copy Using the Original DDL
Deep Copy Using A Create Table LIKE
Deep Copy by Creating a Temp Table and Truncating Original
The Three Components of a Derived Table
Aliasing the Column Names in the Derived Table
Most Derived Tables Are Used To Join To Other Tables
Multiple Ways to Alias the Columns in a Derived Table
Our Join Example with a Different Column Aliasing Style
Column Aliasing Can Default for Normal Columns
CREATING a Derived Table using the WITH Command
Our Join Example With the WITH Syntax
WITH Statement That Uses a SELECT *
A WITH Clause That Produces Two Tables
The Same Derived Query shown Three Different Ways
Answer to Quiz - Answer the Questions
Clever Tricks on Aliasing Columns in a Derived Table
A Derived Table lives only for the lifetime of a single query
An Example of Two Derived Tables in a Single Query
Connecting To Matrix Via Nexus
Connecting To Matrix Via Nexus
Connecting To Matrix Via Nexus
Connecting To Matrix Via Nexus
EXPLAIN – Steps, Segments and Streams
EXPLAIN Terms for Scans and Joins
EXPLAIN Terms for Aggregation and Sorts
EXPLAIN Terms for Set Operators and Miscellaneous Terms
EXPLAIN Terms for Set Operators and Miscellaneous Terms
Simple EXPLAIN Example and the Costs
EXPLAIN Join Example Using DS_BCAST_INNER
EXPLAIN Join Example Using DS_DIST_NONE
EXPLAIN Showing DS_DIST_NONE Visually
EXPLAIN for Ordered Analytics Such as CSUM
EXPLAIN for Scalar Aggregate Functions
EXPLAIN for HashAggregate Functions
EXPLAIN Using Limit, Merge and Sort
EXPLAIN Using a WHERE Clause Filter
EXPLAIN Using the Keyword Distinct
Chapter 7 - Basic SQL Functions
Finding the Current Schema on the Leader Node
Getting Things Setup in Your Search Path
Five Details You Need To Know About the Search_Path
SELECT * (All Columns) in a Table
SELECT Specific Columns in a Table
Place your Commas in front for better Debugging Capabilities
Sort the Data with the ORDER BY Keyword
ORDER BY Defaults to Ascending
Use the Name or the Number in your ORDER BY Statement
Two Examples of ORDER BY using Different Techniques
Changing the ORDER BY to Descending Order
NULL Values sort First in Ascending Mode (Default)
NULL Values sort Last in Descending Mode (DESC)
Multiple Sort Keys using Names vs. Numbers
Sorts are Alphabetical, NOT Logical
Using A CASE Statement to Sort Logically
A Missing Comma can by Mistake become an Alias
Comments using Double Dashes are Single Line Comments
Comments for Multi-Lines as Double Dashes Per Line
A Great Technique for Comments to Look for SQL Errors
Using Limit to bring back a Sample
Using Limit with an Order By Statement
The WHERE Clause limits Returning Rows
Using a Column ALIAS throughout the SQL
Double Quoted Aliases are for Reserved Words and Spaces
Character Data needs Single Quotes in the WHERE Clause
Character Data needs Single Quotes, but Numbers Don’t
NULL means UNKNOWN DATA so Equal (=) won’t Work
Use IS NULL or IS NOT NULL when dealing with NULLs
NULL is UNKNOWN DATA so NOT Equal won’t Work
Use IS NULL or IS NOT NULL when dealing with NULLs
Using Greater Than or Equal To (>=)
Troubleshooting Character Data
Using Different Columns in an AND Statement
Quiz – How many rows will return?
Answer to Quiz – How many rows will return?
What is the Order of Precedence?
Using Parentheses to change the Order of Precedence
Using an IN List in place of OR
The IN List is an Excellent Technique
IN List vs. OR brings the same Results
A Technique for Handling Nulls with a NOT IN List
Another Technique for Handling Nulls with a NOT IN List
LIKE command Underscore is Wildcard for one Character
LIKE Command Works Differently on Char Vs Varchar
The Ilike Command Is NOT Case Sensitive
Troubleshooting LIKE Command on Character Data
Quiz – What Data is Left Justified and what is Right?
Numbers are Right Justified and Character Data is Left
Answer – What Data is Left Justified and what is Right?
An Example of Data with Left and Right Justification
A Visual of CHARACTER Data vs. VARCHAR Data
Use the TRIM command to remove spaces on CHAR Data
Like and Your Escape Character of Choice
Like and the Default Escape Character
Similar To Example with Lower Case Letters
Similar To Example with Lower and Upper Case Letters
Similar To Example with Multiple Occurrences
Multiple Occurrences Must Be Consecutive
Chapter 9 – Distinct Vs Group By AND TOP
Quiz – How many rows come back from the Distinct?
Answer – How many rows come back from the Distinct?
TOP Command is brilliant when ORDER BY is Used!
What is the Difference between TOP and LIMIT?
Quiz – You calculate the Answer Set in your own Mind
Answer – You calculate the Answer Set in your own Mind
Quiz – How many rows come back?
Answer – How many rows come back?
GROUP BY when Aggregates and Normal Columns Mix
GROUP BY delivers one row per Group
GROUP BY Dept_No or GROUP BY 1 the same thing
Limiting Rows and Improving Performance with WHERE
WHERE Clause in Aggregation limits unneeded Calculations
Keyword HAVING tests Aggregates after they are Totaled
Keyword HAVING is like an Extra WHERE Clause for Totals
A Two-Table Join Using Traditional Syntax
A two-table join using Non-ANSI Syntax with Table Alias
You Can Fully Qualify All Columns
A two-table join using ANSI Syntax
Both Queries have the same Results and Performance
Quiz – Can You Finish the Join Syntax?
Answer to Quiz – Can You Finish the Join Syntax?
Quiz – Can You Find the Error?
Answer to Quiz – Can You Find the Error?
Super Quiz – Can You Find the Difficult Error?
Answer to Super Quiz – Can You Find the Difficult Error?
Quiz – Which rows from both tables won’t Return?
Answer to Quiz – Which rows from both tables Won’t Return?
Left Outer Joins Compatible with Oracle
RIGHT OUTER JOIN Example and Results
Right Outer Joins Compatible with Oracle
Which Tables are the Left and which are the Right?
Answer - Which Tables are the Left and which are the Right?
INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional WHERE Clause
OUTER JOIN with Additional WHERE Clause
OUTER JOIN with Additional AND Clause
OUTER JOIN with Additional AND Clause Results
Quiz – Why is this considered an INNER JOIN?
The DREADED Product Join Results
The Horrifying Cartesian Product Join
The ANSI Cartesian Join will ERROR
Quiz – Do these Joins Return the Same Answer Set?
Answer – Do these Joins Return the Same Answer Set?
The Self Join with ANSI Syntax
Quiz – Will both queries bring back the same Answer Set?
Answer – Will both queries bring back the same Answer Set?
Quiz – Will both queries bring back the same Answer Set?
Answer – Will both queries bring back the same Answer Set?
How would you Join these two tables?
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the 3-Table Join?
Answer to Quiz – Can you Write the 3-Table Join?
Quiz – Can you Write the 3-Table Join to ANSI Syntax?
Answer – Can you Write the 3-Table Join to ANSI Syntax?
Quiz – Can you Place the ON Clauses at the End?
Answer – Can you Place the ON Clauses at the End?
The 5-Table Join – Logical Insurance Model
Quiz - Write a Five Table Join Using ANSI Syntax
Answer - Write a Five Table Join Using ANSI Syntax
Quiz - Write a Five Table Join Using Non-ANSI Syntax
Answer - Write a Five Table Join Using Non-ANSI Syntax
Quiz –Re-Write this putting the ON clauses at the END
Answer –Re-Write this putting the ON clauses at the END
SYSDATE Returns a Timestamp with Microseconds
GETDATE Returns a Timestamp without Microseconds
Add or Subtract Days from a date
The ADD_MONTHS Command Returns a Timestamp
The ADD_MONTHS Command with Trunc Removes Time
ADD_MONTHS Command to Add 1-Year or 5-Years
Dateadd Function and Add_Months Function are Different
EXTRACT with DATE and TIME Literals
EXTRACT of the Month on Aggregate Queries
The Datediff Function on Column Data
The Date_Part Function Using a Date
The Date_Part Function Using a Time
Conversion Function Templates Continued
A Summary of Math Operations on Dates
Using a Math Operation to find your Age in Years
A Side Title example with Reserved Words as an Alias
Implied Extract of Day, Month and Year
DATE_PART Function using an ALIAS
DATE_TRUNC Function using TIME
MONTHS_BETWEEN Function in Action
Intervals for Date, Time and Timestamp
Troubleshooting the Basics of a Simple Interval
A Complex Time Interval Example using CAST
A Complex Time Interval Example using CAST
An OVERLAPS Example that Returns No Rows
The OVERLAPS Command using TIME
The OVERLAPS Command using a NULL Value
CSUM – Rows Unbounded Preceding Explained
CSUM – Making Sense of the Data
CSUM – Making Even More Sense of the Data
CSUM – The Major and Minor Sort Key(s)
Reset with a PARTITION BY Statement
PARTITION BY only Resets a Single OLAP not ALL of them
ANSI Moving Window is Current Row and Preceding n Rows
How ANSI Moving SUM Handles the Sort
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Moving SUM every 3-rows Vs a Continuous Average
Partition by Resets an ANSI OLAP
The Moving Window is Current Row and Preceding
How Moving Average Handles the Sort
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Quiz – How is that 4th Row Calculated?
Answer to Quiz – How is that 4th Row Calculated?
Moving Average every 3-rows Vs a Continuous Average
Partition By Resets an ANSI OLAP
RANK Defaults to Ascending Order
Getting RANK to Sort in DESC Order
PERCENT_RANK() OVER with 14 rows in Calculation
PERCENT_RANK() OVER with 21 rows in Calculation
Quiz – What Causes the Product_ID to Reset?
Answer to Quiz – What Cause the Product_ID to Reset?
COUNT OVER for a Sequential Number
Quiz – What caused the COUNT OVER to Reset?
Answer to Quiz – What caused the COUNT OVER to Reset?
MAX OVER with PARTITION BY Reset
Quiz – How did the Row_Number Reset?
Quiz – How did the Row_Number Reset?
Standard Deviation Functions Using STDDEV / OVER
Standard Deviation Functions and STDDEV / OVER Syntax
Variance Functions Using VARIANCE / OVER
Using VARIANCE with PARTITION BY Example
Using FIRST_VALUE and LAST_VALUE
Using LEAD With and Offset of 2
The Three Components of a Derived Table
Aliasing the Column Names in The Derived Table
Most Derived Tables Are Used To Join To Other Tables
Multiple Ways to Alias the Columns in a Derived Table
Our Join Example with a Different Column Aliasing Style
Column Aliasing Can Default for Normal Columns
CREATING a Derived Table using the WITH Command
Our Join Example With the WITH Syntax
A WITH Clause That Produces Two Tables
The Same Derived Query shown Three Different Ways
Answer to Quiz - Answer the Questions
Clever Tricks on Aliasing Columns in a Derived Table
A Derived Table lives only for the lifetime of a single query
An Example of Two Derived Tables in a Single Query
Basic Temporary Table Examples
More Advanced Temporary Table Examples
Advanced Temporary Table Examples
Deep Copy Using the Original DDL
Deep Copy Using A Create Table LIKE
Deep Copy by Creating a Temp Table and Truncating Original
Chapter 15 – Sub-query Functions
An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
The Three Steps of How a Basic Subquery Works
The Final Answer Set from the Subquery
Quiz- Answer the Difficult Question
Answer to Quiz- Answer the Difficult Question
Should you use a Subquery or a Join?
Answer to Quiz- Write the Subquery
Quiz- Write the More Difficult Subquery
Answer to Quiz- Write the More Difficult Subquery
Quiz- Write the Subquery with an Aggregate
Answer to Quiz- Write the Subquery with an Aggregate
Quiz- Write the Correlated Subquery
Answer to Quiz- Write the Correlated Subquery
The Basics of a Correlated Subquery
The Top Query always runs first in a Correlated Subquery
Correlated Subquery Example vs. a Join with a Derived Table
Quiz- A Second Chance to Write a Correlated Subquery
Answer - A Second Chance to Write a Correlated Subquery
Quiz- A Third Chance to Write a Correlated Subquery
Answer - A Third Chance to Write a Correlated Subquery
Quiz- Last Chance to Write a Correlated Subquery
Answer – Last Chance to Write a Correlated Subquery
Answer to Quiz- Write the NOT Subquery
Quiz- Write the Subquery using a WHERE Clause
Answer - Write the Subquery using a WHERE Clause
Quiz- Write the Subquery with Two Parameters
Answer to Quiz- Write the Subquery with Two Parameters
How the Double Parameter Subquery Works
More on how the Double Parameter Subquery Works
Quiz – Write the Triple Subquery
Answer to Quiz – Write the Triple Subquery
Quiz – How many rows return on a NOT IN with a NULL?
Answer – How many rows return on a NOT IN with a NULL?
How to handle a NOT IN with Potential NULL Values
How a Correlated Exists matches up
The Correlated NOT Exists Answer Set
Quiz – How many rows come back from this NOT Exists?
Answer – How many rows come back from this NOT Exists?
Chapter 16 – Substrings and Positioning Functions
The TRIM Command trims both Leading and Trailing Spaces
A Visual of the TRIM Command Using Concatenation
Trim and Trailing is Case Sensitive
How SUBSTRING Works with NO ENDING POSITION
Using SUBSTRING to move Backwards
How SUBSTRING Works with a Starting Position of -1
How SUBSTRING Works with an Ending Position of 0
The POSITION Command finds a Letters Position
Quiz – Find that SUBSTRING Starting Position
Answer to Quiz – Find that SUBSTRING Starting Position
Using the SUBSTRING to Find the Second Word On
Quiz – Why did only one Row Return?
Answer to Quiz – Why Did only one Row Return?
Chapter 17 – Interrogating the Data
Quiz – What would the Answer be?
Answer to Quiz – What would the Answer be?
Quiz – Fill in the Blank Values in the Answer Set
Answer to Quiz – Fill in the Blank Values in the Answer Set
Quiz – Fill in the Answers for the NULLIF Command
Quiz – Fill in the Answers for the NULLIF Command
Quiz – Fill in the Answers for the NULLIF Command
Quiz – Fill in the Answers for the NULLIF Command
The ISNULL, NVL and COALESCE Commands
The ISNULL, NVL and COALESCE Commands
The ISNULL, NVL and COALESCE more examples
The Basics of CAST (Convert And STore)
Some Great CAST (Convert And STore) Examples
Some Great CAST (Convert And STore) Examples
Some Great CAST (Convert And STore) Examples
The Basics of the CASE Statements
The Basics of the CASE Statement
Valued Case Vs. A Searched Case
Answer - Valued Case Statement
Quiz - Searched CASE Statement
Answer - Searched CASE Statement
Quiz - When NO ELSE is present in CASE Statement
Answer - When NO ELSE is present in CASE Statement
When an ELSE is present in CASE Statement
Answer - When an ELSE is present in CASE Statement
When an Alias is NOT used in a CASE Statement
Answer - When an Alias is NOT used in a CASE Statement
Combining Searched Case and Valued Case
Creating a Simple View to Restrict Sensitive Columns
Creating a Simple View to Restrict Rows
Creating a View to Join Tables Together
An ORDER BY Example Inside of a View
An ORDER BY Inside of a View that is Queried Differently
Creating a View with Ordered Analytics
Creating a View with the TOP Command
Creating a View with the LIMIT Command
Altering a Table after a View has been Created
A View that Errors after An ALTER
Updating Data in a Table through a View
Chapter 19 – Set Operators Functions
Answer - Testing Your Knowledge
Answer - Testing Your Knowledge
An Equal Amount of Columns in both SELECT List
Columns in the SELECT list should be from the same Domain
The Top Query handles all Aliases
The Bottom Query does the ORDER BY (a Number)
Great Trick: Place your Set Operator in a Derived Table
A Great Example of how EXCEPT works
Chapter 20 – Statistical Aggregate Functions
Casting STDDEV_SAMP and SQRT (VAR_SAMP)
Nexus is Now Available on the Microsoft Azure Cloud
Nexus Queries Every Major System
Setup of Nexus is as easy as pie
Setup of Nexus is a Easy as 1, 2, 3
Nexus Data Visualization Shows What Tables Can Be Joined
Nexus is doing a Five-Table Join
Nexus Generates the SQL Automatically
Cross-System Joins from Teradata, Oracle and SQL Server
The Tab of the Super Join Builder
The 9 Tabs of the Super Join Builder – Objects Tab 1
Selecting Columns in the Objects Tab
The 9 Tabs of the Super Join Builder – Columns Tab 2
Removing Columns from the Report in the Columns Tab
The 9 Tabs of the Super Join Builder – Sorting Tab 3
The 9 Tabs of the Super Join Builder – Joins Tab 4
The 9 Tabs of the Super Join Builder – Where Tab 5
Using the WHERE Tab For Additional WHERE or AND
The 9 Tabs of the Super Join Builder – SQL Tab 6
The 9 Tabs of the Super Join Builder – Answer Set Tab 7
The 9 Tabs of the Super Join Builder – Analytics Tab 9
Analytics Tab – OLAP Example of SQL Generated
Analytics Tab – Grouping Sets Example
Analytics Tab – Grouping Sets Answer Set
Moving a Single Table To a Different System
The Single Table Data Movement Screen
Moving an Entire Database To a Different System
The Database Mover Options Tab
Converting DDL Table Structures
Converting DDL Table Structures
Converting DDL Table Structures