Course duration
- 2 days
Course Benefits
- Learn to gain a deeper knowledge and understanding of the Amazon Redshift SQL and how to write it.
Course 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
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Instructor-led courses are offered via a live Web connection, at client sites throughout Europe, and at our Geneva Training Center.