Cover image for Tera-Tom Genius Series - Kognitio Architecture and SQL

Tera-Tom Genius Series - Kognitio Architecture and SQL

Author Leona Coffing , Tom Coffing
Release Date: 2016/01/01
ISBN: 9781940540382
Topic:
0%
31
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 database inventions is in-memory technology. Kognitio has been the pioneer of the in-memory database and it is brilliant for both on-premises and cloud technology. This book details the architecture of the Kognitio and the SQL commands available. This book is perfect for anyone who works with a Kognitio system. This book educates readers on how to create tables, how the data is distributed, and how the system process the data. Plus, it is followed up with over 500 pages of SQL examples and explanations. This book is a must have for anyone designing, implementing or querying a Kognitio system. Authors Tera-Tom Coffing, who has written over 75 successful books, and Leona Coffing, Chief Financial Officer (CFO) of Coffing Data Warehousing, bring a combined 40 years 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 Kognitio 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. Kognitio is an In-Memory System
    6. Kognitio has Three Table Distribution Options
    7. Kognitio has Linear Scalability
    8. Nexus is Now Available for Kognitio
  13. Chapter 2 – Kognitio Table Structures
    1. Kognitio has Three Table Distribution Options
    2. A Table that is distributed via a Round Robin Technique
    3. Round Robin Technique is the Default
    4. Random Distribution
    5. A Table that is distributed by Hash
    6. Tables that join are excellent candidates for Hashed Tables
    7. Hash Distribution
    8. A Table that is distributed by Hash by Multiple Columns
    9. The Reasons for a Multi-Column HASHED Distribution Key
    10. Creating a Table that is replicated across all Nodes
    11. Replicated Distribution
    12. The Concept is all about the Joins
    13. Kognitio allows you to create Images
    14. Creating a Table Image to place a Table in Memory
    15. Partitioning an Image
    16. Partitioning an Image View
    17. CREATE OR REPLACE TABLE IMAGE
    18. DEFRAG TABLE IMAGE
  14. Chapter 3 - 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 Kognitio, Oracle and SQL Server Tables
    74. The Kognitio 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 Kognitio System
    87. Saving the Answer Set to a Teradata System
  15. Chapter 4 – The Basics of SQL
    1. Introduction
    2. Setting the Default Schema
    3. SELECT * (All Columns) in a 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 Last in Ascending Mode (Default)
    13. NULL Values sort First 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
  16. Chapter 5 – 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 return rows
    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. Escape Character in the LIKE Command changes Wildcards
    43. Escape Characters Turn off Wildcards in the LIKE Command
    44. Quiz – Turn off that Wildcard
    45. ANSWER – To Find that Wildcard
    46. Using ILIKE with an AND Clause to Find Multiple Letters
    47. Using ILIKE with an OR Clause to Find Either Letters
    48. Keywords
  17. Chapter 6 – Distinct, 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 with an ORDER BY Statement
    7. Just Place the TOP n in front of any Query
  18. Chapter 7 – Aggregation
    1. Quiz – You calculate the Answer Set in your own Mind
    2. Answer – You calculate the Answer Set in your own Mind
    3. Quiz – You calculate the Answer Set in your own Mind
    4. Answer – You calculate the Answer Set in your own Mind
    5. The 3 Rules of Aggregation
    6. There are Five Aggregates
    7. Quiz – How many rows come back?
    8. Answer – How many rows come back?
    9. Troubleshooting Aggregates
    10. GROUP BY when Aggregates and Normal Columns Mix
    11. GROUP BY delivers one row per Group
    12. GROUP BY Dept_No or GROUP BY 1 the same thing
    13. Limiting Rows and Improving Performance with WHERE
    14. WHERE Clause in Aggregation limits unneeded Calculations
    15. Keyword HAVING tests Aggregates after they are totaled
    16. Keyword HAVING is like an Extra WHERE Clause for Totals
    17. Keyword HAVING tests Aggregates after they are totaled
    18. Getting the Average Values per Column
  19. Chapter 8 – Join Functions
    1. A Two-Table Join Using Traditional Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. You Can Fully Qualify All Columns
    4. A two-table join using ANSI Syntax
    5. Both Queries have the same Results and Performance
    6. Quiz – Can You Finish the Join Syntax?
    7. Answer to Quiz – Can You Finish the Join Syntax?
    8. Quiz – Can You Find the Error?
    9. Answer to Quiz – Can You Find the Error?
    10. Super Quiz – Can You Find the Difficult Error?
    11. Answer to Super Quiz – Can You Find the Difficult Error?
    12. Quiz – Which rows from both tables won’t return?
    13. Answer to Quiz – Which rows from both tables won’t return?
    14. LEFT OUTER JOIN
    15. LEFT OUTER JOIN Results
    16. RIGHT OUTER JOIN
    17. RIGHT OUTER JOIN Example and Results
    18. FULL OUTER JOIN
    19. FULL OUTER JOIN Results
    20. Which Tables are the Left and which Tables are Right?
    21. Answer - Which Tables are the Left and Which are the Right?
    22. INNER JOIN with Additional AND Clause
    23. ANSI INNER JOIN with Additional AND Clause
    24. ANSI INNER JOIN with Additional WHERE Clause
    25. OUTER JOIN with Additional WHERE Clause
    26. OUTER JOIN with Additional AND Clause
    27. OUTER JOIN with Additional AND Clause Results
    28. Quiz – Why is this considered an INNER JOIN?
    29. Evaluation Order for Outer Queries
    30. The DREADED Product Join
    31. The DREADED Product Join Results
    32. The Horrifying Cartesian Product Join
    33. The ANSI Cartesian Join will ERROR
    34. Quiz – Do these Joins Return the Same Answer Set?
    35. Answer – Do these Joins Return the Same Answer Set?
    36. The CROSS JOIN
    37. The CROSS JOIN Answer Set
    38. The Self Join
    39. The Self Join with ANSI Syntax
    40. Quiz – Will both queries bring back the same Answer Set?
    41. Answer – Will both queries bring back the same Answer Set?
    42. Quiz – Will both queries bring back the same Answer Set?
    43. Answer – Will both queries bring back the same Answer Set?
    44. How would you Join these two tables?
    45. An Associative Table is a Bridge that Joins Two Tables
    46. Quiz – Can you write the 3-Table Join?
    47. Answer to Quiz – Can you Write the 3-Table Join?
    48. Quiz – Can you write the 3-Table Join to ANSI Syntax?
    49. Answer – Can you Write the 3-Table Join to ANSI Syntax?
    50. Quiz – Can you Place the ON Clauses at the End?
    51. Answer – Can you Place the ON Clauses at the End?
    52. The 5-Table Join – Logical Insurance Model
    53. Quiz - Write a Five Table Join Using ANSI Syntax
    54. Answer - Write a Five Table Join Using ANSI Syntax
    55. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    56. Answer - Write a Five Table Join Using Non-ANSI Syntax
    57. Quiz –Re-Write this putting the ON clauses at the END
    58. Answer –Re-Write this putting the ON clauses at the END
  20. Chapter 9 – Date Functions
    1. Current_Date
    2. Current_Date and Current_Time
    3. Current_Date and Current_Timestamp
    4. Current_Timestamp with Milliseconds
    5. Current_Timestamp with Microseconds
    6. Current_Timestamp and SYSDATE are Synonyms
    7. The Now Function
    8. Adding Days, Weeks and Months to a Date
    9. Add or Subtract Days from a date
    10. The EXTRACT Command
    11. EXTRACT from DATES and TIME
    12. EXTRACT of the Month on Aggregate Queries
    13. Deriving a Timestamp from a Date and Time
    14. Formatting Dates and Dollar Amounts
    15. TO_CHAR Example that is Impressive
    16. TO_CHAR Example that is Amazing
    17. TO_CHAR Example to get Seconds since Midnight
    18. TO_CHAR Example that is ahead of its Time
    19. TO_DATE
    20. TO_TIME
    21. TO_TIMESTAMP
    22. Using CASE and Extract to reformat Dates
    23. Using CAST and SUBSTRING to reformat Dates
    24. Using the DAYOFWEEK and the DECODE Function
    25. Intervals
    26. More Interval Examples
    27. TO_CHAR Details
    28. TO_CHAR Details Continued
    29. TO_CHAR, TO_DATE, TO_TIME and TO_TIMESTAMP
    30. TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
    31. TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
    32. TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
  21. Chapter 10 – 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?
  22. Chapter 11 – OLAP Functions
    1. The Row_Number Command
    2. Using a Derived Table and Row_Number
    3. Finding the First Occurrence
    4. Finding the Last Occurrence
    5. Quiz – How did the Row_Number Reset?
    6. Answer – How did the Row_Number Reset?
    7. RANK Defaults to Ascending Order
    8. Getting RANK to Sort in DESC Order
    9. RANK OVER and PARTITION BY
    10. RANK and DENSE RANK
    11. CSUM
    12. CSUM – The Sort Explained
    13. CSUM – Rows Unbounded Preceding Explained
    14. CSUM – Making Sense of the Data
    15. CSUM – Making Even More Sense of the Data
    16. CSUM – The Major and Minor Sort Key(s)
    17. The ANSI CSUM – Getting a Sequential Number
    18. Troubleshooting the ANSI OLAP on a GROUP BY
    19. Reset with a PARTITION BY Statement
    20. PARTITION BY only Resets a Single OLAP not ALL of them
    21. Moving SUM
    22. ANSI Moving Window is Current Row and Preceding n Rows
    23. How ANSI Moving SUM Handles the Sort
    24. Quiz – How is that Total Calculated?
    25. Answer to Quiz – How is that Total Calculated?
    26. Moving SUM every 3-rows Vs a Continuous Average
    27. PARTITION BY Resets an ANSI OLAP
    28. Moving Average
    29. Moving Average with a Moving Window of 3
    30. The Moving Window is Current Row and Preceding
    31. How Moving Average Handles the Sort
    32. Quiz – How is that Total Calculated?
    33. Answer to Quiz – How is that Total Calculated?
    34. Quiz – How is that 4th Row Calculated?
    35. Answer to Quiz – How is that 4th Row Calculated?
    36. Moving Average every 3-rows Vs a Continuous Average
    37. PARTITION BY Resets an ANSI OLAP
    38. Moving Difference
    39. Moving Difference using ANSI Syntax with Partition By
    40. PERCENT_RANK OVER
    41. PERCENT_RANK OVER with 14 rows in Calculation
    42. PERCENT_RANK OVER with 21 rows in Calculation
    43. COUNT OVER for a Sequential Number
    44. Troubleshooting COUNT OVER
    45. Quiz – What caused the COUNT OVER to Reset?
    46. Answer to Quiz – What caused the COUNT OVER to Reset?
    47. The MAX OVER Command
    48. MAX OVER with PARTITION BY Reset
    49. Troubleshooting MAX OVER
    50. The MIN OVER Command
    51. Troubleshooting MIN OVER
    52. Finding a Value of a Column in the Next Row with MIN
    53. Quiz – Fill in the Blank
    54. Answer – Fill in the Blank
    55. Ordered Analytics OVER
    56. CURRENT ROW AND UNBOUNDED FOLLOWING
    57. Different Windowing Options
    58. The CSUM for Each Product_Id and the Next Start Date
    59. How Ntile Works
    60. Ntile
    61. Ntile Continued
    62. Ntile Percentile
    63. Another Ntile example
    64. Using Quantiles (Partitions of Four)
    65. NTILE Using a Value of 10
    66. NTILE - Tertiles with a PARTITION BY
    67. FIRST_VALUE
    68. FIRST_VALUE after Sorting by the Highest Value
    69. FIRST_VALUE with Partitioning
    70. LAST_VALUE
    71. Using LEAD
    72. Using LEAD With and Offset of 2
    73. LEAD
    74. LEAD With Partitioning
    75. Using LAG
    76. Using LAG with an Offset of 2
    77. LAG
    78. LAG with Partitioning
    79. SUM (SUM(n))
  23. Chapter 12 – 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. Two Versions of Creating a Temporary Table
    22. ON COMMIT DELETE ROWS is the Kognitio Default
    23. ON COMMIT DELETE ROWS
    24. Important Temporary Table Information
    25. How to Use the ON COMMIT DELETE ROWS Option
    26. Create Table AS
    27. Creating a Temporary Table Using a CTAS that Joins Multiple Tables
    28. Create Table LIKE
  24. Chapter 13 –Strings
    1. The LENGTH Command Counts Characters
    2. The LENGTH Command – Spaces can Count too
    3. The LENGTH Command Counts Trailing Spaces
    4. The LENGTH Command and TRIM
    5. UPPER and LOWER Commands
    6. Using the LOWER Command
    7. Using the UPPER Command
    8. Non-Letters are Unaffected by UPPER and LOWER
    9. The CHARACTERS Command Counts Characters
    10. The CHARACTERS Command and Character Data
    11. The CHARACTERS and TRIM Commands
    12. LENGTH, CHARACTER_LENGTH and OCTET_LENGTH
    13. The TRIM Command trims both Leading and Trailing Spaces
    14. How to TRIM only the Trailing Spaces
    15. Concatenation
    16. A Visual of the TRIM Command Using Concatenation
    17. Trim and Trailing is Case Sensitive
    18. How to TRIM Trailing Letters
    19. The SUBSTRING Command
    20. SUBSTRING and SUBSTR are equal, but use different syntax
    21. How SUBSTRING Works with NO ENDING POSITION
    22. Using SUBSTRING to move backwards
    23. How SUBSTRING Works with a Starting Position of -1
    24. How SUBSTRING Works with an Ending Position of 0
    25. An example using SUBSTRING, TRIM and CHAR Together
    26. The POSITION Command finds a Letters Position
    27. Concatenation
    28. Concatenation and SUBSTRING
    29. Four Concatenations Together
    30. Troubleshooting Concatenation
  25. Chapter 14 – 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. The COALESCE Command
    9. The COALESCE Answer Set
    10. The COALESCE Quiz
    11. Answer - The COALESCE Quiz
    12. The Basics of CAST (Convert and Store)
    13. A CAST (Convert and Store) example
    14. Quiz - The Basics of the CASE Statements
    15. Answer to Quiz - The Basics of the CASE Statements
    16. Using an ELSE in the Case Statement
    17. Using an ELSE as a Safety Net
    18. Rules for a Valued Case Statement
    19. Rules for a Searched Case Statement
    20. Valued Case Vs. A Searched Case
    21. Quiz - Valued Case Statement
    22. Answer - Valued Case Statement
    23. Quiz - Searched Case Statement
    24. Answer - Searched Case Statement
    25. Answer - When an ELSE is present in CASE Statement
    26. The CASE Challenge
    27. The CASE Challenge Answer
    28. Combining Searched Case and Valued Case
    29. A Trick for getting a Horizontal Case
    30. Put a CASE in the ORDER BY
    31. Nested Case
  26. Chapter 15 – Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION ALL Explained Logically
    8. EXCEPT/MINUS Explained Logically
    9. EXCEPT Explained Logically
    10. An Equal Amount of Columns in both SELECT List
    11. Columns in the SELECT list should be from the same Domain
    12. The Top Query handles all Aliases
    13. The Bottom Query does the ORDER BY
    14. Great Trick: Place your Set Operator in a Derived Table
    15. UNION Vs UNION ALL
    16. Using UNION ALL and Literals
    17. A Great example of how EXCEPT works
    18. Quiz – Build that Query
    19. Answer To Quiz – Build that Query
    20. USING Multiple SET Operators in a Single Request
    21. Changing the Order of Precedence with Parentheses
    22. Using UNION ALL for speed in Merging Data Sets
  27. Chapter 16 – 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
  28. Chapter 17 – Table Create and Data Types
    1. Kognitio Has Three Table Distribution Options
    2. A Table that is distributed via a Round Robin Technique
    3. Round Robin Technique is the Default
    4. A Table that is distributed by Hash
    5. Tables that join are excellent candidates for Hashed Tables
    6. A Table that is distributed by Hash by Multiple Columns
    7. The Reasons for a Multi-Column HASHED Distribution Key
    8. Creating a Table that is replicated across all Nodes
    9. The Concept is all about the Joins
    10. Creating a Table with Primary Key
    11. Creating a Table with a UNIQUE constraint
    12. How to create tables with Referential Integrity
    13. Not Null Constraints
    14. Creating a Table with Default Values
    15. Creating a Table with a CHECK Constraint
    16. Creating a Global Temporary Table
    17. Important Temporary Table Information
    18. Creating a Table Image to place a Table in Memory
    19. CREATE OR REPLACE TABLE IMAGE
    20. DEFRAG TABLE IMAGE
    21. Not Null Constraints
    22. Unique Constraints
    23. Primary Key Constraints
    24. Check Constraints
    25. Create Table AS WITH DATA or WITH NO DATA
    26. Another Version of Create Table AS
    27. CREATE Table FOR and FROM
    28. Create Table LIKE
    29. String Data Types
    30. Numeric Data Types
    31. Date, Time and Timestamp Data Types
  29. Chapter 18 – Data Manipulation Language (DML)
    1. INSERT Syntax # 1
    2. INSERT example with Syntax 1
    3. INSERT Syntax # 2
    4. INSERT example with Syntax 2
    5. INSERT example with Syntax 3
    6. INSERT/SELECT Command
    7. INSERT/SELECT example using All Columns (*)
    8. INSERT/SELECT example with Less Columns
    9. Two UPDATE Examples
    10. Subquery UPDATE Command Syntax
    11. Example of Subquery UPDATE Command
    12. Join UPDATE Command Syntax
    13. Example of an UPDATE Join Command
    14. DELETE and TRUNCATE Examples
    15. To DELETE or to TRUNCATE
    16. Subquery and Join DELETE Command Syntax
    17. Example of Subquery DELETE Command
  30. Chapter 19 – Kognitio Explain
    1. How to See an EXPLAIN Plan
    2. Seeing an EXPLAIN Plan with Nexus
    3. The Eight Rules to Reading an EXPLAIN Plan
    4. Interpreting Keywords in an EXPLAIN Plan
    5. Interpreting an EXPLAIN Plan
    6. A Single Segment Retrieve – The Fastest Query
    7. EXPLAIN With an ORDER BY Statement
    8. EXPLAIN ANALYZE
    9. EXPLAIN With a Range Query on a Table Partitioned By Day
    10. EXPLAIN That Uses a B-Tree Index Scan
    11. EXPLAIN That Uses a Bitmap Scan
    12. EXPLAIN With a Simple Subquery
    13. EXPLAIN With a Columnar Query
    14. EXPLAIN With a Clustered Index
    15. The Most Important Concept for Joins is the Distribution Key
    16. EXPLAIN With Join that has to Move Data
    17. EXPLAIN With Join that has to Move Data
    18. Changing the Join Query Changes the EXPLAIN Plan
    19. Analyzing the Tables Structures For a 3-Table Join
    20. An EXPLAIN For a 3-Table Join
    21. Explain of a Derived Table vs. a Correlated Subquery
    22. Explain of the Correlated Subquery
    23. Explain of the Derived Table
  31. Chapter 20 – Statistical Aggregate Functions
    1. The Stats Table
    2. Numeric Manipulation Functions
    3. Ceiling Gets the Smallest Integer Not Smaller Than X
    4. Floor Finds the Largest Integer Not Greater Than X
    5. The Round Function and Precision
    6. The STDDEV_POP Function
    7. A STDDEV_POP Example
    8. The STDDEV_SAMP Function
    9. A STDDEV_SAMP Example
    10. The VAR_POP Function
    11. A VAR_POP Example
    12. The VAR_SAMP Function
    13. A VAR_SAMP Example
    14. The CORR Function
    15. A CORR Example
    16. Another CORR Example so you can compare
    17. The COVAR_POP Function
    18. A COVAR_POP Example
    19. Another COVAR_POP Example so you can compare
    20. The COVAR_SAMP Function
    21. A COVAR_SAMP Example
    22. Another COVAR_SAMP Example so you can compare
    23. The REGR_INTERCEPT Function
    24. A REGR_INTERCEPT Example
    25. Another REGR_INTERCEPT Example so you can compare
    26. The REGR_SLOPE Function
    27. A REGR_SLOPE Example
    28. Another REGR_SLOPE Example so you can compare
    29. The REGR_AVGX Function
    30. A REGR_AVGX Example
    31. Another REGR_AVGX Example so you can compare
    32. The REGR_AVGY Function
    33. A REGR_AVGY Example
    34. Another COVAR_POP Example so you can compare
    35. The REGR_COUNT Function
    36. A REGR_COUNT Example
    37. The REGR_R2 Function
    38. A REGR_R2 Example
    39. The REGR_SXX Function
    40. A REGR_SXX Example
    41. The REGR_SXY Function
    42. A REGR_SXY Example
    43. The REGR_SYY Function
    44. A REGR_SYY Example
    45. Using GROUP BY