Amazon Redshift SQL Training

Course Number: RDS103-W

Duration: 2 days

Format: Live, online

Overview

In this training class, students will learn the Amazon Redshift SQL starting at the most basic level and going to the most advanced level with many examples.

Objectives

  • Learn to gain a deeper knowledge and understanding of the Amazon Redshift SQL and how to write it.

Attendee Setup Instructions

For details about what is required when attending this class, please refer to the these instructions (will open in a seperate browser window).

Outline

  • 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
    • 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
    • ORDER BY Defaults to Ascending
    • Use the Name or the Number in your 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 Sorts
    • Multiple Sort Keys using Names vs. Numbers
    • Sorts are Alphabetical, NOT Logical
    • Using A CASE Statement to Sort Logically
    • How to ALIAS a Column Name
    • A Missing Comma can by Mistake become an Alias
    • Comments using Double Dashes are Single Line Comments
    • Comments for Multi-Lines
    • Comments for Multi-Lines As Double Dashes Per Line
    • A Great Technique for Comments to Look for SQL Errors
  • The WHERE Clause
    • Using Limit to bring back a Sample
    • Using Limit With an Order By Statement
    • The WHERE Clause limits Returning Rows
    • Using a Column ALIAS throughout the SQL
    • Double Quoted Aliases are for Reserved Words and Spaces
    • Character Data needs Single Quotes in the WHERE Clause
    • Character Data needs Single Quotes, but Numbers Don’t
    • NULL means UNKNOWN DATA so Equal (=) won’t Work
    • Use IS NULL or IS NOT NULL when dealing with NULLs
    • NULL is UNKNOWN DATA so NOT Equal won’t Work
    • 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
    • Troubleshooting Character Data
    • Using Different Columns in an AND Statement
    • 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
    • Using a NOT IN List
    • Null Values in a NOT IN List Bring Back No Rows
    • Another Technique for Handling Nulls with a NOT IN List
    • BETWEEN is Inclusive
    • NOT BETWEEN is Also Inclusive
    • LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
    • LIKE command Underscore is Wildcard for one Character
    • LIKE Command Works Differently on Char Vs Varchar
    • The Ilike Command Is NOT Case Sensitive
    • Troubleshooting LIKE Command on Character Data
    • Introducing the TRIM Command
    • Numbers are Right Justified and Character Data is Left
    • An Example of Data with Left and Right Justification
    • A Visual of CHARACTER Data vs. VARCHAR Data
    • Use the TRIM command to remove spaces on CHAR Data
    • Like and Your Escape Character of Choice
    • Like and the Default Escape Character
    • Similar To Operators
    • Similar To Operators
    • 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
  • Distinct Vs Group By AND TOP
    • The Distinct Command
    • Distinct vs. GROUP BY
    • TOP Command
    • TOP Command is brilliant when ORDER BY is Used!
    • What is the Difference Between TOP and LIMIT?
  • Aggregation
    • The Rules of Aggregation
    • There are Five Aggregates
    • Troubleshooting Aggregates
    • GROUP BY when Aggregates and Normal Columns Mix
    • GROUP BY Delivers one row per Group
    • GROUP BY Dept_No or GROUP BY 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
  • Join Functions
    • A Two-Table Join Using Traditional Syntax
    • A two-table join using Non-ANSI Syntax with Table Alias
    • You Can Fully Qualify All Columns
    • A two-table join using ANSI Syntax
    • Both Queries have the same Results and Performance
    • 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 the 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
    • OUTER JOIN with Additional AND Clause Results
    • The DREADED Product Join
    • The DREADED Product Join Results
    • The Horrifying Cartesian Product Join
    • The ANSI Cartesian Join will ERROR
    • The CROSS JOIN
    • The CROSS JOIN Answer Set
    • The Self Join
    • The Self Join with ANSI Syntax
    • How would you Join these two tables?
    • An Associative Table is a Bridge that Joins Two Tables
    • The 5-Table Join – Logical Insurance Model
  • Date Functions
    • Current_Date
    • TIMEOFDAY()
    • SYSDATE Returns a Timestamp With Microseconds
    • GETDATE Returns a Timestamp Without Microseconds
    • 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 -Year or -Years
    • Dateadd Function And Add_Months Function are Different
    • The EXTRACT Command
    • EXTRACT from DATES and TIME
    • EXTRACT with DATE and TIME Literals
    • EXTRACT of the Month on Aggregate Queries
    • The Datediff command
    • The Datediff Function on Column Data
    • The Date_Part Function Using a Date
    • The Date_Part Function Using a Time
    • Date_Part Abbreviations
    • The to_char command
    • Conversion Functions
    • Conversion Function Templates
    • Conversion Function Templates Continued
    • Formatting A Date
    • A Summary of Math Operations on Dates
    • Using a Math Operation to find your Age in Years
    • Date Related Functions
    • A Side Title example with Reserved Words as an Alias
    • Implied Extract of Day, Month and Year
    • DATE_PART Function
    • DATE_PART Function using an ALIAS
    • DATE_TRUNC Function
    • DATE_TRUNC Function using TIME
    • MONTHS_BETWEEN Function
    • MONTHS_BETWEEN Function in Action
    • ANSI TIME
    • ANSI TIMESTAMP
    • Redshift TIMESTAMP Function
    • Redshift TO_TIMESTAMP Function
    • Redshift NOW() Function
    • Redshift TIMEOFDAY Function
    • Redshift AGE Function
    • Time Zones
    • Setting Time Zones
    • Using Time Zones
    • Intervals for Date, Time and Timestamp
    • Using Intervals
    • Troubleshooting The Basics of a Simple Interval
    • Interval Arithmetic Results
    • A Date Interval Example
    • A Time Interval Example
    • A DATE Interval Example
    • A Complex Time Interval Example using CAST
    • A Complex Time Interval Example using CAST
    • The OVERLAPS Command
    • An OVERLAPS Example that Returns No Rows
    • The OVERLAPS Command using TIME
    • The OVERLAPS Command using a NULL Value
  • Window Functions
    • Cumulative Sum (CSUM)
    • CSUM – The Sort Explained
    • CSUM – Rows Unbounded Preceding Explained
    • CSUM – Making Sense of the Data
    • CSUM – Making Even More Sense of the Data
    • CSUM – The Major and Minor Sort Key(s)
    • Reset with a PARTITION BY Statement
    • PARTITION BY only Resets a Single OLAP not ALL of them
    • ANSI Moving Window is Current Row and Preceding n Rows
    • How ANSI Moving SUM Handles the Sort
    • Moving SUM every -rows Vs a Continuous Average
    • Partition By Resets an ANSI OLAP
    • Moving Average
    • The Moving Window is Current Row and Preceding
    • How Moving Average Handles the Sort
    • Moving Average every -rows Vs a Continuous Average
    • Partition By Resets an ANSI OLAP
    • RANK Defaults to Ascending Order
    • Getting RANK to Sort in DESC Order
    • RANK() OVER and PARTITION BY
    • RANK() OVER And LIMIT
    • PERCENT RANK() OVER
    • PERCENT_RANK() OVER with rows in Calculation
    • PERCENT_RANK() OVER with rows in Calculation
    • COUNT OVER for a Sequential Number
    • The MAX OVER Command
    • MAX OVER with PARTITION BY Reset
    • The MIN OVER Command
    • The Row_Number Command
    • 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
    • Using FIRST_VALUE and LAST_VALUE
    • Using FIRST_VALUE
    • FIRST_VALUE
    • FIRST_VALUE After Sorting by the Highest Value
    • FIRST_VALUE with Partitioning
    • FIRST_VALUE Combined with Row_Number
    • FIRST_ FIRST_VALUE And Row_Number with Different Sort
    • Using LAG and LEAD
    • Using LEAD
    • Using LEAD with a PARTITION Statement
    • Using LEAD With an Offset of
    • Using LEAD With an Offset of and a PARTITION
    • Using LAG
    • Using LAG with a PARTITION Statement
    • Using LAG With an Offset of
    • Using LAG With an Offset of and a PARTITION
    • CUME_DIST
    • CUME_DIST With a Partition
    • RANK and DENSE RANK
    • 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
    • NTILE Function
    • How Ntile Works
    • Ntile
    • Ntile Continued
    • Ntile Percentile
    • Another Ntile Example
    • Using Tertiles (Partitions of Four)
    • NTILE
    • NTILE Using a Value of
    • NTILE With a Partition
    • NTH_VALUE Function and Syntax
    • NTH_VALUE Arguments
    • NTH_VALUE
    • NTH_VALUE With Partition
    • NTH_VALUE With Partition
    • 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 ( . )
    • 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 ( . )
    • RATIO_TO_REPORT Function
    • RATIO_TO_REPORT Example
    • RATIO_TO_REPORT Example with Partitioning
  • Temporary Tables
    • CREATING A Derived Table
    • The Three Components of a Derived Table
    • Naming the Derived Table
    • Aliasing the Column Names in The Derived Table
    • Visualize This Derived Table
    • Most Derived Tables Are Used To Join To Other Tables
    • Multiple Ways to Alias the Columns in a Derived Table
    • Our Join Example With A Different Column Aliasing Style
    • Column Aliasing Can Default For Normal Columns
    • CREATING A Derived Table using the WITH Command
    • A Join Using the WITH Syntax
    • WITH
    • A WITH Clause That Produces Two Tables
    • Finding the First Occurrence of a Row using WITH
    • Finding the First Occurrence of a Row using a Derived Table
    • Finding the Last Occurrence Using a WITH Derived Table
    • Finding the Last Occurrence Using a Derived Table
    • The Same Derived Query shown Three Different Ways
    • Clever Tricks on Aliasing Columns in a Derived Table
    • A Derived Table lives only for the lifetime of a single query
    • An Example of Two Derived Tables in a Single Query
    • Create Table Syntax
    • Basic Temporary Table Examples
    • More Advanced Temporary Table Examples
    • Advanced Temporary Table Examples
    • 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
  • 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 Final Answer Set from the 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
    • How to handle a NOT IN with Potential NULL Values
    • Using a Correlated Exists
    • How a Correlated Exists matches up
    • The Correlated NOT Exists
    • Substrings and Positioning Functions
    • The TRIM Command trims both Leading and Trailing Spaces
    • A Visual of the TRIM Command Using Concatenation
    • Trim and Trailing is Case Sensitive
    • How to TRIM Trailing Letters
    • The SUBSTRING Command
    • How SUBSTRING Works with NO ENDING POSITION
    • Using SUBSTRING to move Backwards
    • How SUBSTRING Works with a Starting Position of -
    • How SUBSTRING Works with an Ending Position of
    • The POSITION Command finds a Letters Position
    • Using the SUBSTRING to Find the Second Word On
    • Concatenation
    • Concatenation and SUBSTRING
    • Four Concatenations Together
    • Troubleshooting Concatenation
    • Declaring a Cursor
  • Interrogating the Data
    • The NULLIFZERO Command
    • The ZEROIFNULL Command
    • The COALESCE Command
    • The COALESCE Answer Set
    • The Coalesce Quiz
    • The Basics of CAST (Convert And STore)
    • Some Great CAST (Convert And STore) Examples
    • Some Great CAST (Convert And STore) Examples
    • Some Great CAST (Convert And STore) Examples
    • The Basics of the CASE Statements
    • The Basics of the CASE Statement
    • Valued Case Vs. A Searched Case
    • Combining Searched Case and Valued Case
    • Nested Case
    • Put a CASE in the ORDER BY
  • View Functions
    • Creating a Simple View to Restrict Sensitive Columns
    • Creating a Simple View to Restrict Sensitive Columns
    • Creating a Simple View to Restrict Rows
    • Creating a View to Join Tables Together
    • You Select From a View
    • Basic Rules for Views
    • An ORDER BY Example Inside of a View
    • An ORDER BY Inside of a View That is Queried Differently
    • Creating a View With Ordered Analytics
    • Creating a View With The TOP Command
    • Creating a View With The LIMIT Command
    • Altering A Table
    • Altering A Table After a View has been Created
    • A View that Errors After An ALTER
    • Troubleshooting a View
    • Updating Data in a Table through a View
  • Set Operators Functions
    • Rules of Set Operators
    • INTERSECT Explained Logically
    • INTERSECT Explained Logically
    • UNION Explained Logically
    • UNION Explained Logically
    • UNION ALL Explained Logically
    • UNION Explained Logically
    • EXCEPT Explained Logically
    • EXCEPT Explained Logically
    • Minus Explained Logically
    • Minus Explained Logically
    • Testing Your Knowledge
    • Testing Your Knowledge
    • An Equal amount of Columns in both SELECT List
    • Columns in the SELECT list should be from the same Domain
    • The Top Query handles all Aliases
    • The Bottom Query does the ORDER BY (a Number)
    • Great Trick: Place your Set Operator in a Derived Table
    • UNION vs. UNION ALL
    • A Great Example of how EXCEPT works
  • Statistical Aggregate Functions
    • The Stats Table
    • STDDEV
    • Casting STDDEV_SAMP and SQRT (VAR_SAMP)
    • The STDDEV_POP Function
    • A 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

Schedule

No public classes currenly scheduled. Please contact us about the possibility of a scheduling a private class.

No public classes currenly scheduled. Please contact us about the possibility of a scheduling a private class.

Learn faster

Our live, instructor-led lectures are far more effective than pre-recorded classes

Satisfaction guarantee

If your team is not 100% satisfied with your training, we do what's necessary to make it right

Learn online from anywhere

Whether you are at home or in the office, we make learning interactive and engaging

Multiple Payment Options

We accept check, ACH/EFT, major credit cards, and most purchase orders



Recent Training Locations

Alabama

Birmingham

Huntsville

Montgomery

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

Los Angeles

Oakland

Orange County

Sacramento

San Diego

San Francisco

San Jose

Colorado

Boulder

Colorado Springs

Denver

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Jacksonville

Miami

Orlando

Tampa

Georgia

Atlanta

Augusta

Savannah

Hawaii

Honolulu

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Cedar Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Frederick

Hagerstown

Massachusetts

Boston

Cambridge

Springfield

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Minneapolis

Saint Paul

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Las Vegas

Reno

New Jersey

Princeton

New Mexico

Albuquerque

New York

Albany

Buffalo

New York City

White Plains

North Carolina

Charlotte

Durham

Raleigh

Ohio

Akron

Canton

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Oklahoma City

Tulsa

Oregon

Portland

Pennsylvania

Philadelphia

Pittsburgh

Rhode Island

Providence

South Carolina

Charleston

Columbia

Greenville

Tennessee

Knoxville

Memphis

Nashville

Texas

Austin

Dallas

El Paso

Houston

San Antonio

Utah

Salt Lake City

Virginia

Alexandria

Arlington

Norfolk

Richmond

Washington

Seattle

Tacoma

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Calgary

Edmonton

British Columbia

Vancouver

Manitoba

Winnipeg

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan