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