Contents
Chapter 1 – Introduction to the Kognitio Architecture
The Basics of a Single Computer
Data in Memory is fast as Lightning
Kognitio is an In-Memory System
Kognitio has Three Table Distribution Options
Kognitio has Linear Scalability
Nexus is Now Available for Kognitio
Chapter 2 – Kognitio Table Structures
Kognitio has Three Table Distribution Options
A Table that is distributed via a Round Robin Technique
Round Robin Technique is the Default
A Table that is distributed by Hash
Tables that join are excellent candidates for Hashed Tables
A Table that is distributed by Hash by Multiple Columns
The Reasons for a Multi-Column HASHED Distribution Key
Creating a Table that is replicated across all Nodes
The Concept is all about the Joins
Kognitio allows you to create Images
Creating a Table Image to place a Table in Memory
Nexus is Available on the Cloud
Nexus Queries Every Major System
Why is Nexus Special? Visualization and Automatic SQL
Why is Nexus Special? Cross-System Joins
Why is Nexus Special? The Amazing Hub System
Why is Nexus Special? Save Answer Sets as Tables
Why is Nexus Special? Automated Data Movement
Why is Nexus Special? Nexus makes the Servers Talk Directly
What Makes Nexus Special? The Garden of Analysis
The Garden of Analysis Grouping Sets Tab
The Garden of Analysis - Grouping Sets Answer Sets
The Garden of Analysis – Join Tab (1 of 4)
The Garden of Analysis – Join Tab (2 of 4)
The Garden of Analysis – Join Tab (3 of 4)
The Garden of Analysis – Join Tab (4 of 4)
The Garden of Analysis – Charts/Graphs Tab (1 of 4)
The Garden of Analysis – Charts/Graphs Tab (2 of 4)
The Garden of Analysis – Charts/Graphs Tab (3 of 4)
The Garden of Analysis – Charts/Graphs Tab (4 of 4)
The Garden of Analysis – Dynamic Charts Tab (1 of 4)
The Garden of Analysis – Dynamic Charts Tab (2 of 4)
The Garden of Analysis – Dynamic Charts Tab (3 of 4)
The Garden of Analysis – Dynamic Charts Tab (4 of 4)
The Garden of Analysis – Dashboard Tab (1 of 5)
The Garden of Analysis – Dynamic Charts Tab (2 of 5)
The Garden of Analysis – Dynamic Charts Tab (3 of 5)
The Garden of Analysis – Dynamic Charts Tab (4 of 5)
The Garden of Analysis – Dynamic Charts Tab (5 of 5)
Getting to the Super Join Builder
The Super Join Builder is the First Entry in the Menu
The Super Join Builder Shows Tables Visually
What to Do When No Tables are Joinable?
Drag a Joinable Object into the Super Join Builder
You Will See the Add Custom Join Window
Your Tables Will Appear Together
Select the Columns You Want on the Report
Check out the SQL Tab to See the SQL that has been built
Hit Execute to get the Report inside the Super Join Builder
The Report is delivered inside the Super Join Builder
Let's Join Two Tables Again (1 of 6)
Let's Join Two Tables Again (2 of 6)
Let's Join Two Tables Again (3 of 6)
Let's Join Two Tables Again (4 of 6)
Let's Join Two Tables Again (5 of 6)
Let's Join Two Tables Again (6 of 6)
The Tabs of the Super Join Builder Philosophy – One Query
The Tabs of the Super Join Builder – Objects Tab
The Tabs of the Super Join Builder – Columns Tab)
The Tabs of the Super Join Builder – Sorting Tab
The Tabs of the Super Join Builder – Joins Tab
The Tabs of the Super Join Builder – SQL Tab
The Tabs of the Super Join Builder – Metadata Tab
The Tabs of the Super Join Builder – Analytics Tab
The Tabs of the SJB – Analytics Tab – OLAP Screen
Getting a Simple CSUM in the Analytics Tab – OLAP
Getting a Simple CSUM – The SQL Automatically Generated
Getting all of the OLAP functions in the Analytics Tab
A Five Table Join Using the Menu
The First Table is placed in the Super Join Builder
Using the Add Join Cascading Menu
All Five Tables Are In the Super Join Builder
A Five Table Join Two Steps (Cube)
Choose Cube with Columns from the Left Top of the Table
All Tables are Cubed (Joined Together Instantly)
Choose Cube and then Choose Your Columns
Create Cube - Tables Are Joined Without Columns Selected
Create Cube – Select the Columns You Want on the Report
How to join Kognitio, Oracle and SQL Server Tables
The Kognitio Table is now in the Super Join Builder
Drag the Joining Oracle Table to the Super Join Builder
Choose the Columns You Want on Your Report
Let's Add a SQL Server Table to our Teradata and Oracle Join
All Three Tables are now in the Super Join Builder
Change the Hub and Run the Join on Oracle
Change the Hub and Run the Join on SQL Server
Simply Amazing - Change the Hub to the Garden of Analysis
Have the Answer Set Saved Automatically to Any System
Saving the Answer Set to an Oracle or SQL Server System
Saving the Answer Set to a Kognitio System
Saving the Answer Set to a Teradata System
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 Last in Ascending Mode (Default)
NULL Values sort First 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
The WHERE Clause limits Returning Rows
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
Comparisons against a Null Value
NULL means Unknown Data so Equal (=) won’t return rows
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
The IN List Can Use Character Data
Null Values in a NOT IN List Bring Back No Rows
A Technique for Handling Nulls with a NOT IN List
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
LIKE command Underscore is Wildcard for one Character
LIKE Command Works Differently on Char Vs Varchar
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
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command
ANSWER – To Find that Wildcard
Using ILIKE with an AND Clause to Find Multiple Letters
Using ILIKE with an OR Clause to Find Either Letters
Chapter 6 – Distinct, Group By and TOP
Quiz – How many rows come back from the Distinct?
Answer – How many rows come back from the Distinct?
TOP Command with an ORDER BY Statement
Just Place the TOP n in front of any Query
Quiz – You calculate the Answer Set in your own Mind
Answer – You calculate the Answer Set in your own Mind
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
Keyword HAVING tests Aggregates after they are totaled
Getting the Average Values per Column
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?
RIGHT OUTER JOIN Example and Results
Which Tables are the Left and which Tables are 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?
Evaluation Order for Outer Queries
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
Current_Date and Current_Timestamp
Current_Timestamp with Milliseconds
Current_Timestamp with Microseconds
Current_Timestamp and SYSDATE are Synonyms
Adding Days, Weeks and Months to a Date
Add or Subtract Days from a date
EXTRACT of the Month on Aggregate Queries
Deriving a Timestamp from a Date and Time
Formatting Dates and Dollar Amounts
TO_CHAR Example that is Impressive
TO_CHAR Example that is Amazing
TO_CHAR Example to get Seconds since Midnight
TO_CHAR Example that is ahead of its Time
Using CASE and Extract to reformat Dates
Using CAST and SUBSTRING to reformat Dates
Using the DAYOFWEEK and the DECODE Function
TO_CHAR, TO_DATE, TO_TIME and TO_TIMESTAMP
TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
Chapter 10 – 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 of 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 Extreme Subquery
Answer to Quiz – Write the Extreme 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
Quiz – Write the Extreme Correlated Subquery
Answer To Quiz – Write the Extreme 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
Quiz – How many rows come back from this NOT Exists?
Answer – How many rows come back from this NOT Exists?
Using a Derived Table and Row_Number
Quiz – How did the Row_Number Reset?
Answer – How did the Row_Number Reset?
RANK Defaults to Ascending Order
Getting RANK to Sort in DESC Order
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)
The ANSI CSUM – Getting a Sequential Number
Troubleshooting the ANSI OLAP on a GROUP BY
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
Moving Average with a Moving Window of 3
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
Moving Difference using ANSI Syntax with Partition By
PERCENT_RANK OVER with 14 rows in Calculation
PERCENT_RANK OVER with 21 rows in Calculation
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
Finding a Value of a Column in the Next Row with MIN
CURRENT ROW AND UNBOUNDED FOLLOWING
The CSUM for Each Product_Id and the Next Start Date
Using Quantiles (Partitions of Four)
NTILE - Tertiles with a PARTITION BY
FIRST_VALUE after Sorting by the Highest Value
Using LEAD With and Offset of 2
There are Two Types of Temporary Tables
Aliasing the Column Names in the Derived Table
Multiple Ways to Alias the Columns in a Derived Table
CREATING a Derived Table using the WITH Command
The Same Derived Query shown Three Different Ways
Most Derived Tables Are Used To Join To Other Tables
The Three Components of a Derived Table
A Derived Table and CAST Statements
A Derived example Using the WITH Syntax
Answer to Quiz - Answer the Questions
Clever Tricks on Aliasing Columns in a Derived Table
An example of Two Derived Tables in a Single Query
MULTIPLE Derived Tables using the WITH Command
Three Steps to Creating a Temporary Table
Two Versions of Creating a Temporary Table
ON COMMIT DELETE ROWS is the Kognitio Default
Important Temporary Table Information
How to Use the ON COMMIT DELETE ROWS Option
Creating a Temporary Table Using a CTAS that Joins Multiple Tables
The LENGTH Command Counts Characters
The LENGTH Command – Spaces can Count too
The LENGTH Command Counts Trailing Spaces
Non-Letters are Unaffected by UPPER and LOWER
The CHARACTERS Command Counts Characters
The CHARACTERS Command and Character Data
The CHARACTERS and TRIM Commands
LENGTH, CHARACTER_LENGTH and OCTET_LENGTH
The TRIM Command trims both Leading and Trailing Spaces
How to TRIM only the Trailing Spaces
A Visual of the TRIM Command Using Concatenation
Trim and Trailing is Case Sensitive
SUBSTRING and SUBSTR are equal, but use different syntax
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
An example using SUBSTRING, TRIM and CHAR Together
The POSITION Command finds a Letters Position
Chapter 14 – Interrogating the Data
Quiz – What would the Answer be?
Answer to Quiz – What would the Answer be?
Quiz – Fill in the Answers for the NULLIF Command
Answer– Fill in the Answers for the NULLIF Command
The COALESCE Command – Fill In the Answers
The Basics of CAST (Convert and Store)
A CAST (Convert and Store) example
Quiz - The Basics of the CASE Statements
Answer to Quiz - The Basics of the CASE Statements
Using an ELSE in the Case Statement
Rules for a Valued Case Statement
Rules for a Searched Case Statement
Valued Case Vs. A Searched Case
Answer - Valued Case Statement
Quiz - Searched Case Statement
Answer - Searched Case Statement
Combining Searched Case and Valued Case
A Trick for getting a Horizontal Case
Chapter 15 – Set Operators Functions
EXCEPT/MINUS Explained Logically
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
Great Trick: Place your Set Operator in a Derived Table
A Great example of how EXCEPT works
Answer To Quiz – Build that Query
USING Multiple SET Operators in a Single Request
Changing the Order of Precedence with Parentheses
Using UNION ALL for speed in Merging Data Sets
Creating a Simple View to Restrict Sensitive Columns
Creating a Simple View to Restrict Rows
Exception to the ORDER BY Rule inside a View
Views sometimes CREATED for Formatting
Creating a View to Join Tables Together
Another Way to Alias Columns in a View CREATE
The Standard Way Most Aliasing is done
What Happens When Both Aliasing Options Are Present
Resolving Aliasing Problems in a View CREATE
Answer to Resolving Aliasing Problems in a View CREATE
Chapter 17 – Table Create and Data Types
Kognitio Has Three Table Distribution Options
A Table that is distributed via a Round Robin Technique
Round Robin Technique is the Default
A Table that is distributed by Hash
Tables that join are excellent candidates for Hashed Tables
A Table that is distributed by Hash by Multiple Columns
The Reasons for a Multi-Column HASHED Distribution Key
Creating a Table that is replicated across all Nodes
The Concept is all about the Joins
Creating a Table with Primary Key
Creating a Table with a UNIQUE constraint
How to create tables with Referential Integrity
Creating a Table with Default Values
Creating a Table with a CHECK Constraint
Creating a Global Temporary Table
Important Temporary Table Information
Creating a Table Image to place a Table in Memory
Create Table AS WITH DATA or WITH NO DATA
Another Version of Create Table AS
Date, Time and Timestamp Data Types
Chapter 18 – Data Manipulation Language (DML)
INSERT/SELECT example using All Columns (*)
INSERT/SELECT example with Less Columns
Subquery UPDATE Command Syntax
Example of Subquery UPDATE Command
Example of an UPDATE Join Command
Subquery and Join DELETE Command Syntax
Example of Subquery DELETE Command
Seeing an EXPLAIN Plan with Nexus
The Eight Rules to Reading an EXPLAIN Plan
Interpreting Keywords in an EXPLAIN Plan
A Single Segment Retrieve – The Fastest Query
EXPLAIN With an ORDER BY Statement
EXPLAIN With a Range Query on a Table Partitioned By Day
EXPLAIN That Uses a B-Tree Index Scan
EXPLAIN That Uses a Bitmap Scan
EXPLAIN With a Simple Subquery
EXPLAIN With a Clustered Index
The Most Important Concept for Joins is the Distribution Key
EXPLAIN With Join that has to Move Data
EXPLAIN With Join that has to Move Data
Changing the Join Query Changes the EXPLAIN Plan
Analyzing the Tables Structures For a 3-Table Join
Explain of a Derived Table vs. a Correlated Subquery
Explain of the Correlated Subquery
Chapter 20 – Statistical Aggregate Functions
Numeric Manipulation Functions
Ceiling Gets the Smallest Integer Not Smaller Than X
Floor Finds the Largest Integer Not Greater Than X
The Round Function and Precision
Another CORR Example so you can compare
Another COVAR_POP Example so you can compare
Another COVAR_SAMP Example so you can compare
Another REGR_INTERCEPT Example so you can compare
Another REGR_SLOPE Example so you can compare
Another REGR_AVGX Example so you can compare