Vertica Details

For pricing and availability

Contact: Tom Coffing

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

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

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 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

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

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

ABS

ASIN

ATAN

ATAN2

CBRT

Ceil

COS

COSH

DEGREES

DISTANCE

DISTANCEV

FACTORIAL

Floor

HASH

LOG

LOG10

MOD

POW or POWER

RADIANS

RANDOM

RANDOMINT_CRYPTO

ROUND

SIGN

SIN

SINH

SQRT

TAN

TANH

TRUNC

WIDTH_BUCKET

WIDTH_BUCKET Example

BACK TO BIG DATA CLASSES