Amazon Redshift Details

For pricing and availability

Contact: Tom Coffing

Email: Tom.Coffing@CoffingDW.com

Phone: 513 300-0341

Amazon Redshift Architecture and SQL

Description:  In this course, students will learn the Amazon Redshift architecture with an excellent level of detail.  Students will also learn the clever tricks and tips that make Redshift unique, including table creates with auto, automatic compression, and min-max metadata pruning. In addition, students will create all types of Redshift tables and views and learn how to performance tune Redshift tables and queries. Students will also gain significant knowledge of SQL, starting at the most basic level and building to intermediate and advanced levels.  Advanced topics include date functions, advanced analytics, User Defined Functions (UDF), and Stored Procedures.  There are over 800 examples of managing Redshift and 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 Redshift 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 Redshift architecture, creating, and managing tables and objects, utilizing stored procedures and user-defined functions, plus performance tuning expertise, and basic, intermediate, and advanced SQL hands-on training. 

 

Audience:  This course is for anyone who desires to learn Redshift architecture and SQL, from beginners to experienced users.

 

Prerequisites: None

 

Duration:  3-Days

 

Topics:

Why Columnar?
Table Distribution Types
Redshift Quiz –Place the Rows on the Proper Slice
Best practices for Performance Tuning
Tuning Joins
Vacuum
Compression Styles and Their Supported Data Types
Workload Management (WLM)
System Tables
Aggregation
Joins
Similar To Operators
Date and Time Functions
Using Time Zones
Format Functions
Analytics and Window Functions
ListAgg Function
Temporary Tables
Subqueries
Strings
REGEX Examples
SOUNDEX Function
Interrogating the Data
Views
Set Operators
Creating Tables
Data Manipulation Language (DML)
User-Defined Functions (UDF)
Stored Procedures
Math Functions
Statistical Aggregate Functions
 

Redshift Architecture and SQL Course Outline

 

Chapter 1 - The Redshift Architecture

 

What is Parallel Processing?

The Basics of a Single Computer

Data in Memory is Fast as Lightning

Parallel Processing Of Data

Redshift has Linear Scalability

The Architecture of Redshift

Disturbing Facts

Why Columnar?

Row Based Blocks vs. Columnar Based Blocks

As Blocks Reach Maximum Size, New Blocks Append

Data Blocks Are Processed One at a Time Per Slice

Columnar Tables Store Each Column in Separate Blocks

Visualize the Data – Rows vs. Columns

Table Distribution Styles

Distribution Key Where the Data is Unique

Another Way to Create A Table

Distribution Key Where the Data is Non-Unique

Distribution Key is ALL

Even Distribution Key

The Emp_Tbl CREATE Statement with Eight Rows

Redshift Quiz –Place the Rows on the Proper Slice

The First Row is Now on the Proper Slice

Place the Remaining Rows on the Proper Slice

The Rows Are Now on the Proper Slice

Amazon Redshift Slices will use Columnar Storage

Redshift Slices Create Metadata for each Block

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

Redshift Quiz –Place the Rows on the Proper Slice

The First Row is on the Proper Slice

Place the Remaining Rows on the Proper Slice

The Rows are all on the Proper Slice

Amazon Redshift Slices will use Columnar Storage

Quiz – Can you Place the Metadata Correctly?

Answer – Can you Place the Metadata Correctly?

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?

Each Block Comes With Metadata

How Data Might Look On A Slice

Question – How Many Blocks Move Into Memory?

Answer – How Many Blocks Move Into Memory?

Quiz – Master that Query With the Metadata

Answer to Quiz – Master that Query With the Metadata

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 Slice

Table Joins – A Bad Example - What Must Redshift Do?

Emp_Tbl Redistributes by Hashing DeptNo Temporarily

Distribution Key is ALL

Table Joins – Creating a Table with a DISTSTYLE ALL

Even Distribution Key

Table Joins –DISTSTYLE ALL and EVEN

Matching Distribution Keys for Co-Location of Joins

Big Table / Small Table Joins

Quiz - Fact and Dimension Table Distribution Key Designs

Answer - Fact and Dimension Table Distribution Key Designs

Quiz – Which Tables Would Redshift Join First?

Answer – Which Tables Would Redshift Join First?

Improving Performance By Defining a Sort Key

Sort Keys Help Group By, Order By, and Window Functions

How Redshift Loads Data With a Sort Key

Quiz – Place the New Batch of Rows Inside the Table

Answer – Place the New Batch of Rows Inside the Table

The Same is True If There Was Only One Data Block

Quiz - What Happens When Redshift Updates a Row?

Answer - What Happens When Redshift Updates a Row?

Quiz - What Happens When Redshift Deletes a Row?

Answer - What Happens When Redshift Deletes a Row?

Quiz - What Happens When a Table Gets a Vacuum?

Answer - What Happens When a Table Gets a Vacuum?

What is a Vacuum?

When is a Good Time to Vacuum?

The VACUUM Command Grooms a Table

Creating Three Tables with Different Sort Key Strategies

A Table with a Distribution Key and a Single-Sortkey

Quiz – How Many Blocks Move Into Memory?

Answer – How Many Blocks Move Into Memory?

A Table with a Distribution Key and a Compound-Sortkey

Quiz – How Many Blocks Move Into Memory?

Answer – How Many Blocks Move Into Memory?

A Table with a Distribution Key and an Interleaved Sortkey

Quiz – How Many Blocks Move Into Memory?

Answer – How Many Blocks Move Into Memory?

Another Table Example with a Distkey and a Single-Sortkey

A Normal Sort Key Example

Creating a Table with an Interleaved Sort Key

Interleaved Vs. a Normal Sort Key

The ANALYZE Command Collects Statistics

Redshift Automatically ANALYZES Some Create Statements

Database Limits

 

Chapter 2 - Best Practices For Table Design

 

Best Practices for Designing Tables

Choose the Best Sort Key

Choose a Great Distribution Key

Distribution Key Where the Data is Unique

Matching Distribution Keys for Co-Location of Joins

Big Table / Small Table Joins

Define Primary Key and Foreign Key Constraints

Primary Key and Foreign Key Examples

Use The Smallest Column Size When Creating Tables

Use Date/Time Data Types for Date Columns

Specify Redundant Predicates on the Sort Column

Setting the Statement_Timeout to Abort Long Queries

 

Chapter 3 - System Tables

 

Redshift Has System Tables that Log to Disk (Prefix STL)

Redshift Has Virtual System Tables (STV Prefix)

Redshift Has System Catalog Tables Visible to Users

Amazon Redshift System Tables

Trouble Shooting Catalog Table pg_table_def

Catalog Table pg_table_def

Checking Tables for Skew (Poor Distribution)

Checking All Statements That Used the Analyze Command

Checking Specific Tables for Skew (Poor Distribution)

Checking For Details About the Last Copy Operation

Checking When a Table Has Last Been Analyzed

Checking For Column Information on a Table

System Tables for troubleshooting Data Loads

Determining Whether a Query is Writing to Disk

Showing Alert Events

Showing the Last 10 Queries Run on the System

Showing Queries that Last More than One Second

Listing Queries From Longest to Shortest for a Particular Day

Reporting Queries with High CPU Time

Reporting Queries of Nested Loops Returning Many Rows

Finding Queries Aborted Because of a Monitoring Rule

The Number of 1 MB blocks used by each column in a Table

Checking if a Table Distributes Over All Slices

List Schemas and Tables in a Database from the PG Catalog

A View to See the State of the system Queues for Workloads

SELECT From the WLM_QUEUE_STATE_VW View

WLM_QUEUE_STATE_VW View Definitions

A View Showing the State of Current Queries and Queues

WLM_QUERY_STATE_VW View Definitions

 

Chapter 4 - Compression

 

Compression Types Supported

Compression Styles and Their Supported Data Types

Creating a Table with Encoding Compression

Byte Dictionary Compression

Delta Encoding

LZO Encoding

Mostly Encoding

Runlength encoding

Text255 and Text32k Encodings

ANALYZE COMPRESSION

Copy

 

Chapter 5 – Workload Management (WLM)

 

Workload Management Queues

Automatic Workload Management (WLM)

Automatic Workload Management Real Life Example

Query Types

A Default Queue Running Only Data Loads

We Will Now Add the Transforms and Ad-Hoc Queries

Wasted Capacity

WLM Configuration – Step-By-Step

Set-Up Users With Individual Logins

Define Workloads and Group Your Users

Configure Workload Manager (WLM)

Concurrency and Memory are the Most Important

Final Step: Appy & Monitor

What's New in Workload Management

What is a Parameter Group?

Select A Parameter Group on the AWS Console

Switch WLM Mode

Choose Edit and Add Queue Twice

You Now Have Two New Queues

Enter Your Values in Queue 1 and Queue2

Associate the Parameter Group with a Cluster

Find Your Cluster

Modify Your Cluster

Modify Your Cluster

Modify the Cluster

Reboot the Cluster

Queues in the Manual Workload Management Configuration

Queue Configuration - Priority

Queue Configuration - Concurrency Scaling Mode Part 1

Queue Configuration - Concurrency Scaling Mode Part 2

Queue Configuration - Concurrency Scaling Mode Part 3

Queue Configuration - Concurrency Scaling Mode Part 4

Queue Configuration - Concurrency Scaling Mode Part 5

Queue Configuration – User Groups

Assigning queries to queues based on user groups

Queue Configuration – Query Groups

Assigning a Query to a Query Group

Assigning Queries to the Superuser Queue

Queue Configuration – Query Monitoring Rules

Queue Configuration – Query Monitoring Metrics Part 1

Queue Configuration – Query Monitoring Metrics Part 2

Queue Configuration – Query Monitoring Metrics Part 3

Best Practices for Queues and Workloads

Create the WLM_QUEUE_STATE_VW View

SELECT From the WLM_QUEUE_STATE_VW View

WLM_QUEUE_STATE_VW View Definitions

Create the WLM_QUERY_STATE_VW View

WLM_QUERY_STATE_VW View Definitions

Open Up Two Sessions in your Nexus

SELECT From our WLM_QUERY_STATE_VW View

Run a Long-Running Query in Tab 2

In Tab 1 Run These Two Queries

After Set Up of Four Queues

How to use the SET command to Place a Query in a Queue

Checking which Queue the Query is Executing

How to Reset the Query Group

Creating and Altering a Group

Admin User Can Still SET to a Different Queue if they Want

Overriding the Concurrency Level

 

Chapter 6 – Basic SQL Functions

 

Finding the Current Schema on the Leader Node

Getting Things Setup in Your Search Path

Five Details You Need To Know About The Search_Path

Introduction

Fully Qualifying a Database, Schema, and Table

SELECT * (All Columns) in a Table

SELECT Specific Columns From 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 Last in Ascending Mode (Default)

Using the Nulls First Command

NULL Values Sort First in Descending Mode (DESC)

Major Sort vs. Minor Sort

Multiple Sort Keys using Names vs. Numbers

An Order By That Uses an Expression

Sorts are Alphabetic, 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?

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?

How to ALIAS a Column Name

Using an Alias in the WHERE or ORDER BY Clause

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

Using Limit With an ORDER BY Statement

 

Chapter 7 - The WHERE Clause

 

The WHERE Clause limits Returning Rows

Using a Column ALIAS in the WHERE Clause

Numbers Don't Need Single Quotes

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

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 Handle Case Issues

Finding Anyone Whose First Name Ends in 'Y'

Finding Anyone Whose Last Name Ends in 'y'

Using Trim on Character Data with LIKE

Find First_Name Containing and 'a' or an 'e'

Find First_Name Containing and 'a' and an 'e'

Escape Character in the LIKE Command changes Wildcards

Escape Characters Turn off Wildcards in the LIKE Command

Similar To Operator Example

Similar To Example With Lower Case Letters

Similar To Example With Lower and Upper Case Letters

Similar To Example With Multiple Occurrences

Multiple Occurrences Must Be Consecutive

 

 

Chapter 8 - Distinct Vs. Group By AND TOP

 

The Distinct Command

DISTINCT vs. GROUP BY

Quiz – How many rows come back from the Distinct?

Answer – How many rows come back from the Distinct

Limit Command

LIMIT Command with ORDER BY

TOP Command is brilliant when ORDER BY is Used!

What is the Difference Between TOP and LIMIT?

 

Chapter 9 - Aggregation

 

Quiz – You calculate the Answer Set in your Mind

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

The answers are above.

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 the same thing

Limiting Rows and Improving Performance with WHERE

WHERE Clause in Aggregation limits unneeded Calculations

Keyword HAVING tests Aggregates after they are Totaled

Keyword HAVING is like an Extra WHERE Clause for Totals

Using Max or Min on Columns, You Do Not want to Group

 

Chapter 10 - Join Functions

 

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 Won’t Return?

Answer– Which Rows from Both Tables Won’t Return?

LEFT OUTER JOIN

LEFT OUTER Join Results

Left Outer Joins Compatible with Oracle

RIGHT OUTER JOIN

RIGHT OUTER Join Example and Results

Right Outer Joins Compatible with Oracle

FULL OUTER JOIN

FULL OUTER Join Results

Which Tables are the Left and Which are Right?

Answer - Which Tables are the 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

Cartesian Product Join with ANSI Syntax

The CROSS JOIN

The CROSS JOIN Answer Set

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?

Please re-write the above query 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 Non-ANSI Syntax

Quiz – Re-Write this putting the ON clauses at the END

Answer – Re-Write this putting the ON clauses at the END

 

Chapter 11 - Date Functions

 

CURRENT_DATE

Now() and Timeofday() Functions

CURRENT_DATE and CURRENT_TIMESTAMP

Current_Timestamp and Local_Timestamp With Precision

CURRENT_TIME vs. LOCALTIME With Precision

SYSDATE Returns a Timestamp With Microseconds

GETDATE Returns a Timestamp Without Microseconds

Adding A Week to a Date

Add or Subtract Days from a Date

The ADD_MONTHS Command Returns a Timestamp

The ADD_MONTHS Command With Trunc Removes Time

ADD_MONTHS Command to Add 1-Year or 5-Years

Dateadd Function And Add_Months Function are Different

Formatting a Date

The TO_CHAR Command to Format Dollar Signs

The TO_CHAR Command for Formatting Numbers

The EXTRACT Command

EXTRACT from DATES and TIME

EXTRACT with DATE and TIME Literals

EXTRACT of the Month on Aggregate Queries

EXTRACT Command on the Century

EXTRACT Command for the Decade, DOW and DOY

EXTRACT Microseconds, Milliseconds, and Millennium

Implied Extract of Day, Month, and Year using TO_CHAR

Date_Part Abbreviations

The Date_Part Function Using a Time

The DATE_PART Function Using Day of Week (DOW)

The DATEDIFF command

DATEDIFF

Day of Week and a CASE Statement

Day of Week and DECODE

Using CASE and Extract to Reformat Dates

Using CAST and SUBSTRING to Reformat Dates

The AGE Command

MONTHS_BETWEEN

Epoch

The Date_Trunc Function

DATE_TRUNC Command With Time

DATE_TRUNC Command With Dates

The OVERLAPS Command

An OVERLAPS example that Returns No Rows

The OVERLAPS Command using TIME

Using Both CAST and CONVERT in Literal Values

Postgres Conversion Functions

Postgres Conversion Function Templates

Postgres Conversion Function Templates Continued

To_Char Command Examples

Formatting A Date With To_Char Continued

To_Number

To_Number Examples

To_Date

First Day and Last Day Functions

First Day and Last Day Functions

NEXT_DAY

Interval Arithmetic Results

A Summary of Math Operations on Dates

Using a Math Operation to find your Age in Years

Time Zones

Setting Time Zones

Using Time Zones

More Time Zone Examples

Using Day, Month, and Year intervals

Leap Year Intervals

Interesting Intervals

More Interval Examples

Chapter 12 – Analytics

 

ROW_NUMBER

Quiz – How did the Row_Number Reset?

Answer – How did the Row_Number Reset?

Using an ORDER BY at the End of the SQL

Challenge – Return the Last Three Sales per Product_ID

Answer – Return the Last Three Sales per Product_ID

Answer – Another Version of the Derived Table

Quiz – Return Two Students Per Class_Code with Highest Grades

Answer – Return Two Students Per Class_Code with Highest Grades

Answer – Another Version of the Derived Table

RANK

Dense_Rank

RANK vs. DENSE_RANK

Getting RANK to Sort in DESC Order

RANK() OVER And LIMIT

RANK() OVER and PARTITION BY

RANK() OVER, PARTITION BY, and a Derived Table

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

The Partition By Statement

Partition By Resets an ANSI Analytic

Moving Difference

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

The MAX OVER Command

MAX OVER  with PARTITION BY Reset

The MIN OVER Command

The MIN OVER Command with PARTITION BY

Finding Gaps Between Dates

FIRST_VALUE

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

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

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

Standard Deviation Functions Using STDDEV / OVER

Standard Deviation Functions and STDDEV / OVER Syntax

STDDEV / OVER Example

VARIANCE / OVER Syntax

Variance Functions Using VARIANCE / OVER

Using VARIANCE with PARTITION BY Example

LISTAGG Function

LISTAGG Basic Example

Another Example of LISTAGG

LISTAGG With a Pipe-Separated List

LISTAGG With a Comma-Separated List in Groups

MEDIAN Function

MEDIAN Example

MEDIAN with Partitioning and a WHERE Clause

MEDIAN with Partitioning

NTH_VALUE Function and Syntax

NTH_VALUE Arguments

NTH_VALUE

NTH_VALUE With Partition

NTH_VALUE With Partition on Student_Table

PERCENTILE_CONT Function Description and Syntax

Final Result Information About PERCENTILE_CONT

PERCENTILE_CONT Function Arguments

PERCENTILE_CONT Example

PERCENTILE_CONT Example with Percentage Change

PERCENTILE_CONT With PARTITION Example

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)

RATIO_TO_REPORT Function

RATIO_TO_REPORT Example

RATIO_TO_REPORT Example with Partitioning

 

Chapter 13 - Temporary Tables

 

CREATING A Derived Table

Derived Query Examples with Three Different Techniques

Most Derived Tables Are Used To Join To Other Tables

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

Create Table Syntax

Simple Way to Create a Temporary Table

Creating a Temporary Table using a CTAS

CREATE Temporary Table AS (CTAS) with Specific Columns

CREATE a Temporary Table AS (CTAS) Using a Join

Dropping a Temporary Table

Basic Temporary Table Example

More Advanced Temporary Table Examples

Advanced Temporary Table Examples Continued

Performing a Deep Copy

Deep Copy Using the Original DDL

Deep Copy Using A CTAS

Deep Copy Using A Create Table LIKE

Deep Copy By Creating a Temp Table and Truncating Original

 

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

Answer to Quiz- Write the Correlated Subquery

The Basics of a Correlated Subquery

The Top Query always runs first in a Correlated Subquery

Correlated Subquery Example vs. a Join with a Derived Table

Quiz- A Second Chance To Write a Correlated Subquery

Answer - A Second Chance to Write a Correlated Subquery

Quiz- A Third Chance To Write a Correlated Subquery

Answer - A Third Chance to Write a Correlated Subquery

Quiz- Last Chance To Write a Correlated Subquery

Answer – Last Chance to Write a Correlated Subquery

Quiz – Write the Extreme Correlated Subquery

Answer To Quiz – Write the Extreme Correlated Subquery

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

Not Exists has no problem with Null Values

Quiz – How many rows come back from this NOT Exists?

 

Chapter 15 - Strings

 

UPPER and lower  Functions

The Length Command Counts Characters

LENGTH Works on Fixed Length Columns

The LEN Function

OCTET_LENGTH

The TRIM Command Trims Leading and Trailing Spaces

The RTRIM and LTRIM Command Trims Spaces

Trim Trailing is Case Sensitive

How to TRIM Trailing Letters

Concatenation

The SUBSTRING Command

How SUBSTR Works with NO ENDING POSITION

Using SUBSTRING to move Backward

How SUBSTRING Works with a Starting Position of -1

How SUBSTRING Works with an Ending Position of 0

How SUBSTRING Works with an Ending Position of 0

Using SUBSTRING and CHAR_LENGTH Together

The CHARINDEX Command Using a Literal

The POSITION Command finds a Letters Position

The POSITION Command is brilliant with SUBSTR

LPAD and RPAD

The REPLACE Function

REGEX Example

Selecting Regex that Starts with Uppercase Characters

Regex that Starts with Specific Characters Case Insensitive

Regex that Starts with Specific Characters Not Matching

Selecting Regex Contain Specific Lowercase Characters

Selecting Regex that Starts with Digits

Selecting Regex that Starts with Five Digits

Regex that Contains this Character or that Character

REGEX Example For Ending Characters

REGEXP_REPLACE

REGEXP_REPLACE Example

Another REGEXP_REPLACE Example

The RIGHT Function

The LEFT and RIGHT Functions

REPLICATE  Function

The REVERSE String Function

SOUNDEX Function to Find a Sound

DIFFERENCE Function to Quantile a Sound

Declaring a Cursor

The ASCII Function

The Nexus Server Migrates and Joins Data at High Speed

 

Chapter 16 – Interrogating the Data

 

Numeric Manipulation Functions

Finding the Cube Root

Ceiling Gets the Smallest Integer Not Smaller Than X

Floor Finds the Largest Integer Not Greater Than X

Quiz – What would the Answer be?

Answer to Quiz – What would the Answer be?

The NULLIF Command

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

Quiz – Who Are You Going to Call?

Answer – Who Are You Going to Call?

Some Great CAST (Convert And Store) Examples

A Rounding Example Using CAST

CAST will Round Values Up or Down

Valued Case vs. Searched Case

Combining Searched Case and Valued Case

Nested 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 17 - View Functions

 

The Fundamentals of Views

Creating a Simple View to Restrict Sensitive Columns

Creating a Simple View to Restrict Rows

Creating a View to Join Tables Together

Sometimes We Create Views for Formatting

Basic Rules for Views

Ordered Analytics inside a View

Keyword Limit 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?

Altering A Table

Altering A Table After a View has been Created

A View that Errors After An ALTER

 

Chapter 18 - Set Operators Functions

 

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

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 Works

USING Multiple SET Operators in a Single Request

Changing the Order of Precedence with Parentheses

Group By Grouping Sets

 

Chapter 19 - Table Create and Data Types

 

Create Table Syntax

Data Types

Fully Qualifying a Database, Schema, and Table

IF NOT EXISTS Table Create

Distribution Styles

Creating a Table With A Diststyle of Auto

Creating a Table With A Distribution Key

Creating a Table with a Diststyle ALL

Creating a Table with a Diststyle Even

Creating a Table With A Sortkey

Creating a Table with a Compound Sortkey

Creating a Table with an Interleaved Sortkey

Creating a Table with NULL and NOT NULL Constraints

Creating a Table with an Identity Column

Creating a Table with Default Values

Creating a Table with a UNIQUE Constraint

Creating a Table with a Primary Key

Creating a Table with a Foreign Key

More Primary Key and Foreign Key Examples

Creating Table Like

Create Table AS

Compression Types Supported

Compression Types

Creating a Table with Encoding Compression

Creating a Database

Creating a User

Dropping a User

Renaming a Table or a Column

Adding and Dropping a Column to a Table

 

Chapter 20 - Data Manipulation Language (DML)

 

INSERT Syntax # 1

INSERT example with Syntax 1

INSERT Syntax # 2

INSERT example with Syntax 2

INSERT example with Syntax 3

INSERT/SELECT Command

INSERT/SELECT example using All Columns (*)

INSERT/SELECT example with Less Columns

The UPDATE Command Basic Syntax

Two UPDATE Examples

Subquery UPDATE Command Syntax

Example of Subquery UPDATE Command

Example of an UPDATE Join Command

Subquery and Join DELETE Command Syntax

Example of Subquery DELETE Command

The DELETE Command Basic Syntax

DELETE and TRUNCATE Examples

To DELETE or to TRUNCATE

 

Chapter 21 - EXPLAIN

 

Three Ways to Run an EXPLAIN

EXPLAIN – Steps, Segments, and Streams

EXPLAIN Terms For Scans and Joins

EXPLAIN Terms For Aggregation and Sorts

EXPLAIN Terms For Set Operators and Miscellaneous Terms

Terms to Determine if Data Moves across the Network

EXPLAIN Example and the Cost

EXPLAIN Example and the Rows

EXPLAIN Example and the Width

Simple EXPLAIN Example and the Costs

Look for These Keywords to Track Data Movement

EXPLAIN Join Example Using DS_BCAST_INNER

EXPLAIN Join Example Using DS_DIST_NONE

EXPLAIN Showing DS_DIST_NONE Visually

EXPLAIN With a Warning

EXPLAIN For Ordered Analytics Such as CSUM

EXPLAIN For Scalar Aggregate Functions

EXPLAIN For HashAggregate Functions

EXPLAIN Using Limit, Merge, and Sort

EXPLAIN Using a WHERE Clause Filter

EXPLAIN Using the Keyword Distinct

EXPLAIN for Subqueries

 

Chapter 22 – User-Defined Functions

 

Creating a User Defined Scalar Function

Function Syntax

Creating a Simple Function as Easy as Pie

You can use a Function in a WHERE or ORDER BY Clause

You Can CAST a Function

Multiple Functions with an Input Parameter

Creating a Function that Sings

Creating a Function That Shows the Sunday Date of the Week

Create a Flight_Table that Holds Longitude and Latitude

A Function Example for Measuring Distance in Miles

A Function Example for Measuring Distance

Create a Flight_Table that Holds Longitude and Latitude

A Function Example for Comparing Two Numbers

A Function Example Using Multiple Tables

SQL that Utilizes Two User Defined Functions (UDFs)

Function Volatility

Amazon Redshift Vs. Python Data Types

Privileges

 

 

 

Chapter 23 – Stored Procedures

 

Stored Procedure Syntax

Why Use Redshift Stored Procedures?

Advantages and Limitations of Redshift Stored Procedures

A Simple Stored Procedure

Viewing the DDL of a Stored Procedure

A Stored Procedure With an Input Parameter

A Stored Procedure That Does an INSERT

Stored Procedure INSERT Using Input Parameters

Stored Procedure UPDATE Using Input Parameters

Stored Procedure UPDATE Using Subquery

Stored Procedure Delete Using an Input Parameter

Fibonacci Numbers With Variable Declarations

Amazon Redshift Stored Procedure Control Structures

A Stored Procedure With IF and END IF Logic

A Stored Procedure With IF, ELSEIF, and ELSE Logic

Using Loops in Stored Procedures

Using a WHILE Loop

Using a FOR Loop

Using a FOR Loop in Reverse

Stored Procedure Workshop

Stored Procedure Workshop Answer Using Loop

Stored Procedure Workshop Answer Using WHILE

Stored Procedure Workshop Answer Using FOR

Stored Procedure Workshop Answer Using FOR Reverse

Stored Procedure With an IN, INOUT, and OUT Parameter

A Stored Procedure With Exception Handling

A Stored Procedure With Transaction Examples (1 of 4)

A Stored Procedure With Transaction Examples (2 of 4)

A Stored Procedure With Transaction Examples (3 of 4)

A Stored Procedure With Transaction Examples (4 of 4)

What is a Cursor?

Creating a Cursor

A Cursor and an Input Parameter Used in the WHERE Clause

Creating a Temp Table that a User can Query

Using Record in a Stored Procedure

 

 

Chapter 24 – 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

The VAR_SAMP Function

A VAR_SAMP Example

The VARIANCE Function

A VARIANCE Example

Using GROUP BY

Chapter 25 – Mathematical Functions

Numeric Manipulation Functions

ABS

ACOS

ASIN

Ceiling

Floor

COS

DEGREES

EXP

LN

LOG

MOD

POWER

RADIANS

ROUND

SIGN

SIN

SQRT
TRUNC