Vertica Details
For pricing and availability
Contact: Tom Coffing
Email: Tom.Coffing@CoffingDW.com
Phone: 513 300-0341
Vertica Architecture and SQL
​
Description: In this course, students will learn the Vertica architecture with an excellent level of detail. Students will also learn the clever tricks and tips that make Vertica unique. In addition, students will create all types of Vertica tables, views, user-defined functions, and stored procedures, and learn how to performance tune Vertica tables and queries—over 600 examples of managing, creating tables, and Vertica SQL. The class is 90% hands-on training. The students will use the SQL_Class database with 18 tables representing the tables in the book.
Reference Guide: The book is an excellent book for teaching and makes for an excellent reference guide to help on the job.
Description: Students will learn the Vertica architecture and SQL starting at the most basic level and going to the most advanced level in this course.
Objectives: After this course, students will have detailed knowledge and understanding of the Vertica architecture, creating and managing tables and objects, utilizing projections performance tuning, and essential, intermediate, and advanced SQL hands-on training.
Audience: This course is for anyone who desires to learn the Vertica architecture and SQL, from beginners to experienced users.
Prerequisites: None
Duration: 3-5 Days
​
Topics:
-
What is Parallel Processing?
-
The Vertica Architecture
-
Table Distribution Options
-
How Hashing works
-
Partitioned Tables
-
Columnar Tables
-
Projections
-
Compression
-
System Tables
-
Unstructured Data
-
Collecting Statistics
-
Explain Plans
-
Major Sort vs. Minor Sorts
-
The Like Command Wildcards are Percent and Underscore
-
Distinct and Group By
-
There are Five Aggregates
-
Inner and Outer Joins
-
Date and Time Functions
-
Format Functions
-
Analytics and Window Functions
-
Temporary Tables
-
Subqueries
-
Strings
-
Interrogating the Data
-
Views
-
Set Operators
-
Creating Tables
-
Data Manipulation Language (DML)
-
User-Defined Functions (UDF)
-
Stored Procedures
-
Statistical Aggregate Functions
Vertica Architecture and SQL Course Outline
Chapter 1 – Intro to Vertica
What is Parallel Processing?
Nothing Happens on Disk
Data in Memory is fast as Lightning
Parallel Processing Of Data
Vertica has Linear Scalability
The Architecture of Vertica
Vertica Architecture Terms
The Problem With Row-Based Data
Columnar Data Can Store Each Column in A Separate Block
Why Columnar?
Row Based Blocks vs. Columnar Based Blocks
Visualize the Data – Rows vs. Columns
Vertica Deployment Options
Chapter 2 – Vertica Data Distribution
Distribution Strategy 1 - Segmented By Hash
Distribution Strategy 2 - Unsegmented
Sorting the Data in a Table CREATE Statement
Even Distribution When Hash Column is Unique
Uneven Distribution Where the Hash Key is Non-Unique
A Table With Matching Distribution and Join Keys
Range Partitioning Per Day
Visual of Range Partitioning Per Day
Range Partitioning Per Month
Range Partitioning Per Month Visual
Range Partitioning a Timestamp Per Year
Range Partitioning Per Year
Hierarchical Partitioning Using Calendar_Hierarchy_Day
Hierarchical Partitioning Year, Month, and Day
A Visual For Hierarchical Partitioning
Hierarchical Partitioning Month, Day, and Hours
Hash Partitioning
Partitioning Per Hash Visual
The Importance of Collecting Statistics
Fact and Dimension Table Distribution Key Designs
Why a Sort Key Improves Performance
Sort Keys Help Group By, Order By, and Window Functions
Chapter 3 – Performance
The Emp_Tbl CREATE Statement with Eight Rows
Vertica Quiz –Place the Rows on the Proper Segment
The First Row is Now on the Proper Segment
Place the Remaining Rows on the Proper Segment
The Rows Are Now on the Proper Segment
Vertica Segments will use Columnar Storage
Quiz – How Many Blocks Move into Memory?
Answer –How Many Blocks Move into Memory?
Quiz – How Many Blocks Move into Memory?
Answer – How Many Blocks Move into Memory?
Quiz – How Many Blocks Move into Memory?
Answer – How Many Blocks Move into Memory?
Emp_Tbl CREATE Statement with a DISTKEY of DeptNo
Vertica Quiz –Place the Rows on the Proper Segment
The First Row is on the Proper Segment
Place the Remaining Rows on the Proper Segment
The Rows are all on the Proper Segment
Vertica Segments will use Columnar Storage
Quiz – How Many Blocks Move Into Memory?
Answer – How Many Blocks Move into Memory?
Quiz – How Many Blocks Move into Memory?
Answer – How Many Blocks Move into Memory?
Quiz – How Many Blocks Move into Memory?
Answer – How Many Blocks Move into Memory?
Which Join is More Efficient and Why? This Page or Next?
Which Join is More Efficient? This Page or Previous?
Two Rows Joining Must Physically be on the Same Segment
Table Joins – A Bad Example - What Must Vertica Do?
Emp_Tbl Redistributes by Hashing DeptNo Temporarily
Unsegmented All Nodes Duplicate the Table on Segments
Table Joins – Creating a Table with a DISTSTYLE ALL
Quiz – Which Tables Would Vertica Join First?
Answer – Which Tables Would Vertica Join First?
Chapter 4 - Clever Features Of Vertica
Super Projections
Vertica Projections
The Components of a Projection
The Five Advantages of Projections
Creating a Projection
The Copy Command
Vertica Hybrid Storage Model
Read-Optimized Store (ROS)/Write-Optimized Store (WOS)
Write-Optimized Store (WOS) is Memory Resident
Updates Collect in Time-Based Buckets called Epochs
Vertica Does Not Support In-Place Updates
Using the COPY – Auto, Trickle, or Direct
Using the COPY – Moveout and Mergeout
Using the COPY Command
Using the COPY Command on the Subscribers Table
COPY Command on the Claims in Comma Delimited
Using COPY DIRECT on the Services Table
Using COPY TRICKLE on the Providers Table
Using COPY Without Commit
Seeing Storage Containers
Using a Unique Constraint
K-Safety
K-Safety of 2
The Five Data Isolation Modes
Import/Export Between Multiple Vertica Systems
Roles
Chapter 5 – Compression
Compression
Compression Types Supported
A Table With Compression Encodings
Auto Compression
BLOCK_DICT Compression
BLOCKDICT_COMP Compression
BZIP_COMP Compression
COMMONDELTA_COMP Compression
DELTARANGE_COMP Compression
DELTAVAL Compression
GZIP_COMP Compression
LZO Compression
RLE Compression
Zstandard Compression
Chapter 6 – System Tables
System Tables
audit_managing_users_privileges
ca_bundles
catalog_subscription_changes
catalog_sync_state
catalog_truncation_status
certificates
client_auth
cluster_layout
columns
comments
complex_types
constraint_columns
cryptographic_keys
databases
directed_queries
dual
elastic_cluster
fault_groups
foreign_keys
grants
hcatalog_columns
hcatalog_schemata
hcatalog_table_list
hcatalog_tables
inherited_privileges
inheriting_objects
jdbc_columns
keywords
large_cluster_configuration_status
license_audits
licenses
load_balance_groups
log_params
log_queries
log_tables
materialize_flextable_columns_results
models
network_addresses
node_subscription_change_phases
node_subscriptions
"nodes"
odbc_columns
password_auditor
passwords
primary_keys
profile_parameters
profiles
projection_checkpoint_epochs
projection_columns
projection_delete_concerns
projections
resource_pool_defaults
resource_pools
"roles"
routing_rules
schemata
sequences
session_subscriptions
shards
standard_keywords
storage_locations
subcluster_resource_pool_overrides
subclusters
system_columns
system_tables
table_constraints
tables
text_indices
types
user_audits
user_client_auth
user_configuration_parameters
user_function_parameters
user_functions
user_procedures
user_transforms
users
view_columns
view_tables
views
Chapter 7 – Collect Statistics and EXPLAIN
Running an EXPLAIN
Running an EXPLAIN On Tables Without Statistics
Collecting Statistics
Running an EXPLAIN LOCAL VERBOSE
Running an EXPLAIN on a Query with a WHERE Clause
Performance Tuning and Collecting Statistics for Joins
EXPLAIN on a Join (Segmented Vs. Unsegmented)
EXPLAIN on a Join With Performance Tuning
EXPLAIN for a Subquery
EXPLAIN for a Correlated Subquery
EXPLAIN using a BETWEEN Statement With Statistics
EXPLAIN on Aggregates and WHERE and HAVING Clauses
EXPLAIN GROUP BY GROUPING SETS
EXPLAIN of an Ordered Analytic
Download Nexus at CoffingDW.com
Nexus Joins Data Across Platforms In the Super Join Builder
Chapter 8 – Introduction to SQL
Introduction
Setting Your Session Search_Path
SELECTING Current Information
SELECTING Current Information
SELECT * (All Columns) in a Table
SELECT Specific Columns in a Table
Commas in the Front or Back?
Place your Commas in front for better Debugging Capabilities
Sort the Data with the ORDER BY Keyword
Use a Column Name or Number in an 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)
Major Sort vs. Minor Sort
Multiple Sort Keys using Names vs. Numbers
Sorts are Alphabetical, NOT Logical
Using A Valued CASE Statement to Sort Logically
Using A Searched CASE Statement to Sort Logically
Quiz – Can you Add a Minor Sort?
Order By Decode
Quiz – Can you Add Two Minor Sorts Using Decode?
Answer – Can you Add Two Minor Sorts Using Decode?
How to ALIAS a Column Name
Using an Alias in the WHERE and ORDER BY Clause
Using an Alias in the ORDER BY Clause with Decode
A Missing Comma Can Become an Alias by Mistake
Comments using Double Dashes are Single Line Comments
Comments for Multi-Lines
Comments for Multi-Lines As Double Dashes Per Line
Comments are a Great Technique for Finding SQL Errors
Chapter 9 – The WHERE Clause
NexusCore Servers - Control Network and Data Movement
The WHERE Clause limits Returning Rows
Using a Column ALIAS in the WHERE Clause
Numbers Don't Need Single Quotes
Three Techniques for Not Equal
Comparisons Against a Null Value
Searching for NULL Values Using Equality Returns Nothing
Use IS NULL or IS NOT NULL when dealing with NULLs
Use IS NULL or IS NOT NULL when dealing with NULLs
Using Greater Than Or Equal To (>=)
AND in the WHERE Clause
Troubleshooting AND
OR in the WHERE Clause
Troubleshooting OR
WHY OR must utilize the Column Name Each Time
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
Using a NOT IN List
Null Values in a NOT IN List Return No Rows
A Technique for Handling Nulls with a NOT IN List
Technique 2 for Handling Nulls with a NOT IN List
The BETWEEN Statement is Inclusive
The NOT BETWEEN Statement is also Inclusive
The BETWEEN Statement Works for Character Data
The Like Command Wildcards are Percent and Underscore
LIKE command Underscore is Wildcard for one Character
CASE Matters with the LIKE Command
Using LIKE for all Cases with Lower and Upper
Another Example of UPPER and LOWER
LIKE Command to Find Multiple Characters
LIKE Command to Find Either Character
Using ILIKE To Handle Case Issues
Finding Anyone Whose Name Ends in 'Y'
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command
Chapter 10 – Distinct, Group By, and Limit
The Distinct Command
DISTINCT vs. GROUP BY
Answer – How many rows come back from the Distinct?
Limit Command
Limit Command and Order By
Chapter 11 – Aggregation
Answer – Calculate the Answer Set in your Mind
Quiz 2 – Calculate the Answer Set in your Mind
Answer Quiz 2 – Calculate the Answer Set in your Mind
There are Five Aggregates
Quiz – How many Rows Return?
Answer – How many Rows Return?
Casting a Data Type
Troubleshooting Aggregates
GROUP BY Delivers One Row Per Group
GROUP BY DEPT_NO or GROUP BY 1 are Equivalent
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
MIN For Character Data
GROUP BY GROUPING SETS
GROUP BY GROUPING SETS Super Query
GROUP BY ROLLUP
GROUP BY ROLLUP Answer Set
GROUP BY ROLLUP Super Query
GROUP BY CUBE
GROUP BY CUBE Answer Set
GROUP BY CUBE Super Query
Chapter 12 – Joining Tables
A Two-Table Join Using Traditional Syntax
A Join using Traditional Syntax with Table Alias
You Can Fully Qualify All Columns
A 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 Will NOT Return?
Answer– Which Rows from Both Tables Will NOT Return?
LEFT OUTER JOIN
LEFT OUTER Join Results
RIGHT OUTER JOIN
RIGHT OUTER Join Example and Results
FULL OUTER JOIN
FULL OUTER Join Results
Which Tables are the Left and Which are Right?
Answer - Which Tables are Left and Which are 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
The DREADED Product Join
The DREADED Product Join Results
Cartesian Product Join with Traditional Syntax
The CROSS JOIN
The CROSS JOIN Answer Set
Create a New Table With the Name EMPLOYEE_TABLE2
The SELF JOIN
The SELF JOIN with ANSI Syntax
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the Three-Table Join?
Answer to Quiz – Can you Write the Three-Table Join?
Quiz –Write the Three-Table Join Using ANSI Syntax?
Answer –Write the Three-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 Five-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 Traditional Syntax
Answer - Write a Five Table Join Using Traditional Syntax
Quiz – Re-Write this putting the ON clauses at the END
Answer – Re-Write this putting the ON clauses at the END
Chapter 13 – Date Functions
CURRENT_DATE
CURRENT_DATE and CURRENT_TIMESTAMP
CLOCK_TIMESTAMP
TIMEOFDAY
Current_Timestamp and Local_Timestamp With Precision
CURRENT_TIME vs. LOCALTIME With Precision
Getdate Function
Other Date Keywords
Using CAST in Literal Values
Add or Subtract Days from a Date
The ADD_MONTHS Command
ADD_MONTHS to Add Five Years to a Date
DayofMonth, DayofYear, DayofWeek, and DayofWeek_ISO
Formatting Dates
Formatting Date Example
The TO_CHAR Command to Format Dollar Signs
Formatting Numbers
Formatting Number Examples with TO_CHAR
TO_CHAR Command for Formatting Numbers Continued
The EXTRACT Command
EXTRACT from DATES and TIME
Another Option for Extracting Portions of Dates and Times
The DATE_PART Function
Implied Extract of Day, Month, and Year using TO_CHAR
The DATE_PART Function Using Day of Week (DOW)
Day of Week and a CASE Statement
Great Date Functions to Know
DAYOFWEEK
Week
DAYS
AGE_IN_MONTHS
AGE_IN_MONTHS And AGE_IN_YEARS
DATEDIFF
DATEDIFF Continued
Using CASE and Extract to Reformat Dates
Using CAST and SUBSTRING to Reformat Dates
Date_Trunc Year and Quarter
The Date_Trunc Function
DATE_TRUNC Command With Time
DATE_TRUNC Command With Dates
LAST_DAY
MONTHS_BETWEEN
DATE
NEXT_DAY
NEW_TIME
ROUND
TIMESTAMP_ROUND
TIMESTAMP_TRUNC
TRANSACTION_TIMESTAMP
TIME_SLICE
MONTH
DAY
HOUR
MINUTE
SECOND
MICROSECOND
MIDNIGHT_SECONDS
JULIAN_DAY
ISFINITE
Intervals for Date, Time, and Timestamp
Interval Data Types and the Bytes to Store Them
Using Day, Month, and Year intervals
Cast with Intervals
Leap Year can be Inconsistent
Interval Arithmetic Results
Time and Date Interval Examples
The OVERLAPS Command
An OVERLAPS Example that Returns No Rows
The OVERLAPS Command using TIME
Chapter 14 – Temporary Tables
There are Three types of Temporary Tables
CREATING A Derived Table
Derived Query Examples with Three Different Techniques
Most Derived Tables Are Used To Join To Other Tables
The Three Components of a Derived Table
Visualize This Derived Table
Quiz - Derived Table Challenge
Answer to Quiz - Derived Table Challenge
An Example of Two Derived Tables Using WITH
WITH RECURSIVE Derived Table Hierarchy
WITH RECURSIVE Derived Table Query
WITH RECURSIVE Derived Table Definition
WITH RECURSIVE Derived Table Seeding
WITH RECURSIVE Derived Table Looping
WITH RECURSIVE Derived Table Looping in Slow Motion
WITH RECURSIVE Derived Table Looping Continued
WITH RECURSIVE Derived Table Looping Continued
WITH RECURSIVE Derived Table Ends the Looping
WITH RECURSIVE Derived Table Final SELECT
WITH RECURSIVE Results
Syntax for Temporary Tables
Temporary Tables Explained
Key Temporary Table Terms
Creating and Populating a Local Temporary Table
Using a Local Temporary Table
Creating and Populating a Global Temporary Table
Creating and Populating a Global Temporary Table
Some Great Examples of Creating a Temporary Table Quickly
Creating a Temporary Table That is Sorted
A Temp Table That Populates Some of the Rows
A Temporary Table With Some of the Columns
Example of Creating and Querying an External Tables
Chapter 15 – Sub-query Functions
An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
An IN List Ignores Duplicates
The Subquery
The Three Steps of How a Basic Subquery Works
These are Equivalent Queries
The 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?
Quiz - Write the Subquery
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
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
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
NOT IN Subquery Returns Nothing when NULLs are Present
Fixing a NOT IN Subquery with Null Values
Quiz- Write the NOT IN 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
IN is equivalent to =ANY
Using a Correlated Exists
How a Correlated Exists Matches Up
The Correlated NOT Exists
Chapter 16 – Analytic and Window Functions
ROW_NUMBER
Quiz – How did the Row_Number Reset?
Answer – How did the Row_Number Reset?
Using a Derived Table
Quiz – Return Two Students Per Class_Code with Highest Grades
Answer – Return Two Students Per Class_Code with Highest Grades
RANK
Dense_Rank
RANK vs. DENSE_RANK
Getting RANK to Sort in DESC Order
RANK() OVER and PARTITION BY
Derived Table For the Top Three Sales Per Product_ID
DENSE_RANK() OVER and PARTITION BY
PERCENT_RANK() OVER with 14 rows in Calculation
PERCENT_RANK() OVER with 21 rows in Calculation
PERCENT_RANK and PARTITION BY
Cumulative Sum
Cumulative Sum – The Sort Explained
Cumulative Sum – Rows Unbounded Preceding Explained
Cumulative Sum – Making Sense of the Data
Cumulative Sum – Major and Minor Sort Keys
Reset with a PARTITION BY Statement
Totals and Subtotals through Partition By
Moving Sum
Moving SUM every 3-rows vs. a Continuous Average
Partition By Resets the Calculations
Moving Average
The Moving Window is Current Row and Preceding n
How Moving Average Handles the Order By
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
Moving Average and Partition By
Partition By Resets an Ordered Analytic
Exponential Moving Average Vs. Moving Average
Two Exponential Moving Averages With Different Smoothing
Moving Difference
Moving Difference With Lag
Moving Difference with Partition By
Finding a Value of a Column in the Next Row with MIN
Finding a Next Row Value with MIN and PARTITION BY
Finding Multiple Values of a Column in Upcoming Rows
Finding The Next Date using MAX
COUNT OVER for a Sequential Number
COUNT OVER using ROWS UNBOUNDED PRECEDING
The MAX OVER Command
MAX OVER with PARTITION BY Reset
The MIN OVER Command
The MIN OVER Command with PARTITION BY
Using FIRST_VALUE
FIRST_VALUE With Partitioning
Daily_Sales Minus FIRST_VALUE With Partitioning
FIRST_VALUE After Sorting by the Highest Value
FIRST_VALUE and a Derived Table
Last_Value Can Be Confusing
Last_Value Working Properly
Last_Value With Partitioning
Last_Value And First_Value with Partitioning
First and Last Value Difference
ARGMIN
ARGMAX
ARGMIN and ARGMAX Together
ARGMIN and ARGMAX For Trending with a Derived Table
Using LEAD
Using LEAD with a PARTITION Statement
Using LEAD With an Offset of 2
Using LEAD With an Offset of 2 and a PARTITION
Using LAG
Using LAG with a PARTITION Statement
Using LAG With an Offset of 2
Using Two LAG Statements
Using LAG With an Offset of 2 and a PARTITION
CUME_DIST
CUME_DIST With a Percentage
CUME_DIST With a Tie Value
CUME_DIST and a Derived Table
CUME_DIST and a Partition By Statement
CUME_DIST with 14 Rows
CUME_DIST With a Partition on 7 Rows
Different Windowing Options
How Ntile Works
Ntile Example
Ntile Continued
Ntile Percentile
Using Quantiles (Partitions of Four)
Using Deciles (Partitions of Ten)
NTILE With a Partition
MEDIAN Example
MEDIAN with Partitioning and a WHERE Clause
Another Example of MEDIAN with Partitioning
PERCENTILE_CONT Function Description and Syntax
PERCENTILE_CONT Example
PERCENTILE_CONT Example with Percentage Change
PERCENTILE_CONT With PARTITION and (0.4)
PERCENTILE_DISC Function Description and Syntax
PERCENTILE_DISC Function Arguments
PERCENTILE_DISC Example
PERCENTILE_DISC Example with Percentage Change
PERCENTILE_DISC With PARTITION Example
PERCENTILE_DISC With PARTITION and (0.4)
LISTAGG Basic Example
LISTAGG Using Dates
LISTAGG With a Comma-Separated List in Groups
LISTAGG To See Website Page Visits in Descending Order
NTH_VALUE Function and Syntax
NTH_VALUE Arguments
NTH_VALUE with Offset of Two
NTH_VALUE With Partition on Product_ID
NTH_VALUE With Partition on Class_Code
Width_Bucket
Chapter 17 – Strings
UPPER and lower Functions
The Length Command Counts Characters
LENGTH Works on Fixed Length Columns
OCTET_LENGTH
The TRIM Command Trims Leading and Trailing Spaces
The RTRIM and LTRIM Command Trims Spaces
Trim and Trailing is Case Sensitive
How to TRIM Trailing Letters
Concatenation
CONCAT
The SUBSTR and SUBSTRING Commands
How SUBSTR Works with NO ENDING POSITION
Using SUBSTR and CHAR_LENGTH Together
The POSITION Command finds a Letters Position
The POSITION Command is Brilliant with SUBSTR
LPAD and RPAD
The REPLACE Function
REGEXP_INSTR
REGEXP_SUBSTR (1 of 4)
REGEXP_SUBSTR Options (2 of 4)
REGEXP_SUBSTR Options (3 of 4)
REGEXP_SUBSTR Options (4 of 4)
REGEXP_SUBSTR Example
REGEXP_INSTR Example 2
REGEXP_REPLACE Example
Another REGEXP_REPLACE Example
REGEXP_INSTR
REGEXP_LIKE
REGEXP_LIKE Example
REGEXP_NOT_LIKE
REGEXP_SUBSTR
REGEXP_COUNT Syntax
SOUNDEX Function to Find a Sound
SOUNDEX_MATCHES
The RIGHT Function
The LEFT and RIGHT Functions
The ASCII Function
BIT_LENGTH
BITCOUNT
BITSTRING_TO_BINARY
BTRIM
CHR
COLLATION
EDIT_DISTANCE
GREATEST
GREATESTB
LEAST
LEASTB
LOWERB
UPPERB
OVERLAY
OVERLAYB
INITCAP
INITCAPB
Chapter 18 – Interrogating the Data
NULLIFZERO
Quiz – Fill in the Answers for the NULLIF Command
Answer – Fill in the Answers for the NULLIF Command
The COALESCE Command
COALESCE is Equivalent to this CASE Statement
COALESCE in a Real-World Example
Some Great CAST (Convert And Store) Examples
A Rounding Example Using CAST
CAST will Round Values Up or Down
The Round Function and Precision
Valued Case vs. Searched Case
Combining Searched Case and Valued Case
The DECODE Command
DECODE Vs. CASE
A Trick for getting a Horizontal Case
Put a CASE in the ORDER BY
Using A Searched CASE Statement to Sort Logically
Quiz – Can you Add a Minor Sort?
Answer – Can you Add a Minor Sort?
Order By DECODE
Quiz – Can you Add Two Minor Sorts Using Decode?
Answer – Can you Add Two Minor Sorts Using Decode?
CASE Challenge
Answer - CASE Challenge
Chapter 19 – Views
The Fundamentals of Views
Creating a Simple View to Restrict Sensitive Columns
Creating a Simple View to Restrict Rows
Sometimes We Create Views for Formatting
Basic Rules for Views
How to Modify a View
The Exception to the ORDER BY Rule inside a View
Derived Columns in a View Should Have a Column Alias
The Standard Way Most Aliasing is Done
Another Way to Alias Columns in a View CREATE
What Happens When a View Column gets Aliased Twice?
​
Chapter 20 – Set Operators
Rules of Set Operators
Quiz - Intersect Explained Logically
Answer - Intersect Explained Logically
Quiz - Union Explained Logically
Answer - Union Explained Logically
Quiz - Union ALL Explained Logically
Answer - Union ALL Explained Logically
Quiz - Except Explained Logically
Answer - Except Explained Logically
Quiz - Minus Explained Logically
Answer - MINUS Explained Logically
Quiz - Testing Your Knowledge
Answer - Testing Your Knowledge
Rule 1 - Equal Number of Columns in Both SELECT Lists
Rule 2 - Top Query Handles all Aliases
Rule 3 - The Bottom Query does the ORDER BY
Intersect Challenge
Answer - Intersect Challenge
UNION Vs. UNION ALL
Using UNION ALL and Literals
Using UNION ALL for Speed in Merging Data Sets
Great Trick: Place your Set Operator in a Derived Table
A Great Example of how EXCEPT and MINUS work
Using UNION to be same as GROUP BY GROUPING SETS
USING Multiple SET Operators in a Single Request
Changing the Order of Precedence with Parentheses
Chapter 21 – Creating Tables
Distribution Strategy 1 - Segmented By Hash
Distribution Strategy 2 - Unsegmented
Sorting the Data in a Table CREATE Statement
Uneven Distribution Where the Hash Key is Non-Unique
Matching Distribution Keys for Co-Location of Joins
Big Table / Small Table Joins
Fact and Dimension Table Distribution Key Designs
Why a Sort Key Improves Performance
Sort Keys Help Group By, Order By, and Window Functions
Creating a Table With a NOT NULL Constraint
Creating Partitioned Tables
Creating a Table With a Unique Constraint
Creating a Table With UUID_GENERATE
Creating a Table With a Primary Key
Creating a Table With a Foreign Key
Creating An Automatic Data Mart
Syntax For CREATE SEQUENCE
CREATE SEQUENCE Example
Creating a table with Auto-Increment
Creating a table with an Identity Column
Syntax for Creating External Tables
Example of Creating and Querying an External Table
Syntax for Temporary Tables
Temporary Tables Explained
Key Temporary Table Terms
Creating and Populating a Local Temporary Table
Using a Local Temporary Table
Creating and Populating a Global Temporary Table
Creating a Temporary Table That is Sorted
A Temp Table That Populates Some of the Rows
Numeric Data Types
Date and Time Data Types
String, Binary, Boolean, Spatial, and UUID Data Types
Chapter 22 – Inserts, Updates, and Deletes
INSERT Syntax # 1
INSERT Syntax # 2
Inserting Null Values into a Table
INSERT/SELECT Command
INSERT/SELECT to Build a Data Mart
UPDATE Examples
Subquery UPDATE Command Syntax
Example of Subquery UPDATE Command
Another Example of a Subquery UPDATE Command
Example of an UPDATE Join Command
Fast UPDATE
Deleting Rows in a Table
Example of Subquery DELETE Command
Rename a Table
​
Chapter 23 – Unstructured Data
JSON and the Internet of Things
Why Use JSON?
JSON Types
JSON Syntax Explanation
Building a Complex Object
Continuing to Build a Complex Object
Creating a Flex Table and Inserting JSON Data
Flex Table and Insert of JSON Data Using Nexus
Querying the Flex Table Using the View
Inserting Two Objects Into a Flex Table
How to Query a JSON View
Creating a More Complex JSON Table
Flattening a JSON Table
Creating a Table with ROW Data Type
Creating a Table with An Array Data Type
Selecting from A Table with An Array Data Type
Index Number Technique to Select Portions of Array Types
Quiz - Find People with Four Claims In An Array
Answer - Find People with Four Claims In An Array
Creating Tables For Flat, Array, and Set
Querying Tables For Flat, Array, and Set
Functions that Support Arrays and Sets
Explode Arrays
ARRAY_FIND
Spatial Data Types - Geometry and Geography
Best Practices for Geospatial Analytics
Pros and Cons of GEOGRAPHY Data Types
What Is A Polygon?
Query For Distance Between Two Locations
Chapter 24 – User Defined Functions (UDFs)
User Defined Functions
Difference Between UDFs and Stored Procedures
Function Volatility
Creating Your First Vertica User-Defined Function
Creating A User-Defined Function With an Input Parameter
A Function with Hard-Coded Values
You can use a Function in a WHERE or ORDER BY Clause
Multiple Functions with an Input Parameter
Creating a Function that Sings
Creating a Function to Convert to Timezone PST
An EXAMPLE of a CASE Statement Using Two Functions
Functions With Duplicate Names But Different Arguments
DROP Function
Chapter 25 – Stored Procedures
Creating Your First Stored Procedure
Creating A Stored Procedure with an Input Parameter
Creating A Stored Procedure Using Perform
Stored Procedure Doing an Insert With Input Parameters
Stored Procedure Doing an Update Using Input Parameters
Creating A Stored Procedure That Executes a Command
Stored Procedure Using Input Parameters To Create a User
Stored Procedure Using IF and ENDIF
Stored Procedure With IF, ELSEIF, and ELSE Logic
Stored Procedure With A WHILE
Stored Procedure With A Loop
Stored Procedure With A FOR Loop
Stored Procedure Using The Keyword FOUND
Stored Procedure With A FOR Loop
Stored Procedure With An IN QUERY SELECT
Results of Stored Procedure With An IN QUERY SELECT
Stored Procedure Workshop
Stored Procedure Workshop Answer Using Loop
Stored Procedure Workshop Using Loop And One Counter
Stored Procedure Workshop Answer Using WHILE
Stored Procedure Workshop Answer Using FOR Loop
Procedure Workshop Answer Using Reverse FOR Loop
Creating A Stored Procedure with Exceptions
Exception Variable Levels
Stored Procedure with ASSERT
SQLSTATE and SQLERRM
Creating A Stored Procedure With a Cursor
Chapter 26 – Statistical Aggregate Functions
The Stats Table
The STDDEV_POP Function
STDDEV_POP Example
The STDDEV_SAMP Function
The STDDEV_SAMP Function
A STDDEV_SAMP Example
The VAR_POP Function
A VAR_POP Example
The VAR_SAMP Function
A VAR_SAMP Example
The CORR Function
A CORR Example
Another CORR Example so you can Compare
The VARIANCE Function
A VARIANCE Example
The COVAR_POP Function
A COVAR_POP Example
Another COVAR_POP Example so you can Compare
The COVAR_SAMP Function
A COVAR_SAMP Example
Another COVAR_SAMP Example so you can Compare
The REGR_INTERCEPT Function
A REGR_INTERCEPT Example
Another REGR_INTERCEPT Example so you can Compare
The REGR_SLOPE Function
A REGR_SLOPE Example
Another REGR_SLOPE Example so you can Compare
The REGR_AVGX Function
A REGR_AVGX Example
The REGR_AVGY Function
A REGR_AVGY Example
Another REGR_AVGY Example so you can Compare
The REGR_COUNT Function
A REGR_COUNT Example
The REGR_R2 Function
A REGR_R2 Example
The REGR_SXX Function
A REGR_SXX Example
The REGR_SXY Function
A REGR_SXY Example
The REGR_SYY Function
A REGR_SYY Example
Using GROUP BY
Chapter 27 – Mathematical Functions
Numeric Manipulation Functions
Numeric Manipulation Functions
ABS
ASIN
ATAN
ATAN2
CBRT
Ceil
Ceil
COS
COSH
DEGREES
DISTANCE
DISTANCEV
FACTORIAL
Floor
HASH
LN
LOG
LOG10
MOD
PI
POW or POWER
RADIANS
RANDOM
RANDOMINT_CRYPTO
ROUND
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC
WIDTH_BUCKET
WIDTH_BUCKET Example