Snowflake SQL Overview
Snowflake is a popular data warehouse platform that supports SQL. In this Snowflake SQL training course, attendees will greatly expand their ability to query Snowflake using SQL.
Location and Pricing
Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.
In addition, some courses are available as live, instructor-led training from one of our partners.
Objectives
- Work with the Snowflake database to run SQL statements, retrieve advanced analytics, and create graphs and charts
- Learn a wide variety of Snowflake analytics
- Use advanced Order By methods to sort the answer sets retrieved
- Fully understand and use Joins and Subqueries
- Interrogate the data using Case, Coalesce, and Decode
- Be able to fully use all of the different Snowflake temporary options
- Create tables and views on the Snowflake system
- Utilize the many different Snowflake date functions
- Use Aggregation and advanced Aggregation techniques
- Handle and manipulate Strings
- Learn and write SQL for advanced Statistical Aggregate Functions
Prerequisites
The default 3-day length of this class presumes that attendees have prior SQL SELECT experience and that at least the first two chapters will be review. If your group is new to writing SQL queries, we recommend that we expand the class to 4 days.
Outline
Expand All | Collapse All
Introduction
Basic SQL Functions
- Introduction
- Setting Your Default Database and Schema
- SELECT * (All Columns) in a Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- Place your Commas in front for better Debugging Capabilities
- Sort the Data with the ORDER BY Keyword
- Use a Column Name or Number in an ORDER BY Statement
- Two Examples of ORDER BY using Different Techniques
- Changing the ORDER BY to Descending Order
- NULL Values sort Last in Ascending Mode (Default)
- Using the Nulls First Command
- NULL Values sort First in Descending Mode (DESC)
- Using the Nulls Last Command
- Major Sort vs. Minor Sort
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A Valued CASE Statement to Sort Logically
- Using A Searched CASE Statement to Sort Logically
- Quiz – Can you Add a Minor Sort?
- Answer – Can you Add a Minor Sort?
- Using Decode 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
- Comments are a Great Technique for Finding SQL Errors
- Popular Snowflake Functions
- Move Data to the Snowflake Effortlessly
- Move Data to the Cloud Effortlessly
The WHERE Clause
- The WHERE Clause limits Returning Rows
- The WHERE Clause Needs Single-Quotes for Character Data
- Using a Column ALIAS in the WHERE Clause
- Numbers Don't Need Single or Double Quotes
- Searching for NULL Values Using Equality Returns Nothing
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Using Greater Than OR Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting OR
- WHY OR must utilize the Column Name Each Time
- Troubleshooting Character Data
- Using Different Columns in an AND Statement
- What is the Order of Precedence?
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- The IN List is an Excellent Technique
- IN List vs. OR brings the same Results
- The IN List Can Use Character Data
- Using a NOT IN List
- Null Values in a NOT IN List Return No Rows
- A Technique for Handling Nulls with a NOT IN List
- The BETWEEN Statement is Inclusive
- The NOT BETWEEN Statement is also Inclusive
- The BETWEEN Statement Works for Character Data
- LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
- LIKE command Underscore is Wildcard for one Character
- Using Upper and Lower to Handle Case Issues
- Using ILIKE Handle Case Issues
- Finding Anyone Who Name End in 'Y'
- Escape Character in the LIKE Command changes Wildcards
- Escape Characters Turn off Wildcards in the LIKE Command
Distinct, Group By, Top, and Pivot
- The Distinct Command
- Distinct vs. GROUP BY
- TOP Command
- TOP Command is brilliant when ORDER BY is Used!
- The FETCH Clause
- Sample and Tablesample
- TOP vs. Sample
- The Pivot Command
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 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
- Three types of Advanced Grouping
- GROUP BY Grouping Sets
- GROUP BY Grouping Sets Answer Set
- GROUP BY Rollup
- GROUP BY Rollup ResultSet
- GROUP BY Cube
- GROUP BY Cube ResultSet
Join Functions
- A two-table join using Non-ANSI 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 JOIN Using (+)
- RIGHT OUTER JOIN
- Right Outer Join Example and Results
- RIGHT OUTER JOIN Using (+)
- 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
- 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
- An Associative Table is a Bridge that Joins Two Tables
- The 5-Table Join – Logical Insurance Model
Date Functions
- Current Date
- Current_Date, Current_Time, and Current_Timestamp
- Current_Time vs. LocalTime With Precision
- Local_Time and Local_Timestamp With Precision
- Add or Subtract Days from a date
- The ADD_MONTHS Command
- Using the ADD_MONTHS Command to Add 1 Year
- Using the ADD_MONTHS Command to Add 5 Years
- Formatting a Date Using the To_Char Command
- Formatting Date and Time With To_Char
- The To_Char command to format Dollar Signs
- The To_Char Command for Formatting Numbers
- The EXTRACT Command
- EXTRACT from DATES and TIME
- Using Extract
- EXTRACT from DATES and TIME Optional Syntax
- Another Option for Extracting Portions of Dates and Times
- Using Date_Part to Extract
- Implied Extract of Day, Month and Year using to_char
- The Date_Part Function Using a Date
- Great Date Functions to Know
- DAYOFWEEK and a CASE Statement
- Year and Days for the First/Last Weeks of the Year
- First Day and Last Day Functions
- Incrementing Date Values Using the Dateadd Function
- Incrementing Time Values Using Dateadd
- The Datediff command
- The Datediff Function on Column Data
- Calculating Days Between using the DATEDIFF Function
- Changing the Date to a Timestamp
- Find the First Day of the Current Month
- Using Intervals
- Using Day, Month, and Year Intervals
- Complex Interval
Analytics
- The Row_Number Command
- Find the Top Two Students Per Class_Code using Qualify
- Find the Top Two Students using a Derived Table
- The RANK Command
- Getting RANK to Sort in DESC Order
- RANK () OVER and PARTITION BY
- RANK() OVER and a Qualify Statement
- RANK() OVER and a WITH Derived Table
- RANK vs. DENSE_RANK
- DENSE_RANK() OVER and PARTITION BY
- DENSE_RANK() OVER and QUALIFY
- PERCENT_RANK () OVER with 14 rows in Calculation
- PERCENT_RANK () OVER with 21 rows in Calculation
- PERCENT_RANK() OVER and PARTITION BY
- CSUM
- CSUM – The Sort Explained
- CSUM – Rows Unbounded Preceding Explained
- The CSUM – Making Sense of the Data
- CSUM – The Major and Minor Sort Key(s)
- The ANSI OLAP – 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
- How the Moving Average Calculates
- How the Sort works for Moving Average (MAVG)
- Moving Average every 3-rows Vs. a Continuous Average
- Partition BY Resets an ANSI OLAP
- Moving Difference using ANSI Syntax
- Moving Difference using ANSI Syntax with Partition By
- Finding a Value of a Column in the Next Row with MIN
- Finding a Value of a Column in the Next Row with PARTITION BY
- Finding Multiple Values of a Column in Upcoming Rows
- Finding The Next Date using MAX
- COUNT OVER for a Sequential Number
- COUNT OVER using ROWS UNBOUNDED PRECEDING
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- The MIN OVER Command
- The MIN OVER Command with PARTITION BY
- Finding Gaps Between Dates
- The CSUM For Each Product_Id For The First 3 Days
- Using FIRST_VALUE
- FIRST_VALUE
- FIRST_VALUE with Partitioning
- FIRST_VALUE Combined with Row_Number and Qualify
- FIRST_VALUE and Row_Number with a Derived Table
- Using LEAD
- Using LEAD with a PARTITION Statement
- Using LEAD With an Offset of 2
- Using LEAD With an Offset of 2 and a PARTITION
- Using LAG
- Using LAG with a PARTITION Statement
- Using LAG With an Offset of 2
- Using LAG With an Offset of 2 and a PARTITION
- CUME_DIST
- CUME_DIST With a Partition
- CURRENT ROW AND UNBOUNDED FOLLOWING
- Different Windowing Options
- 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 Quantiles (Partitions of Four)
- NTILE Using a Value of 10
- NTILE With a Partition
- NTH_VALUE Function and Syntax
- NTH_VALUE Arguments
- NTH_VALUE
- NTH_VALUE With Partition
- NTH_VALUE With Partition and Ignore Nulls
- 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
- SUM(SUM(n))
Temporary Tables
- CREATING A Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- CREATING A Derived Table using the WITH Command
- Derived Query Examples with Three Different Techniques
- Most Derived Tables Are Used To Join To Other Tables
- The Three Components of a Derived Table
- Visualize This Derived Table
- Our Join Example With the WITH Syntax
- An Example of Two Derived Tables in a Single Query
- MULTIPLE Derived Tables using the WITH Command
- WITH RECURSIVE Derived Table Hierarchy
- WITH RECURSIVE Derived Table Query
- WITH RECURSIVE Derived Table Definition
- WITH RECURSIVE Derived Table Seeding
- WITH RECURSIVE Derived Table Looping
- WITH RECURSIVE Derived Table Looping in Slow Motion
- WITH RECURSIVE Derived Table Looping Continued
- WITH RECURSIVE Derived Table Ends the Looping
- WITH RECURSIVE Derived Table Final Report
- Creating a Temporary Table
- Creating a Temporary Table using a CTAS
- Dropping a Temporary Table
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
- How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Should you use a Subquery of a Join?
- 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
- NOT IN Subquery Returns Nothing when NULLs are Present
- Fixing a NOT IN Subquery with Null Values
- How the Double Parameter Subquery Works
- More on how the Double Parameter Subquery Works
- IN is equivalent to =ANY
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
Strings
- UPPER and lower Functions
- The Length Command Counts Characters
- LENGTH Works on Fixed Length Columns
- LENGTH and OCTET_LENGTH
- The TRIM Command trims both Leading and Trailing Spaces
- The RTRIM and LTRIM Command trims Spaces
- Concatenation
- Concatenation and SUBSTRING
- Four Concatenations Together
- LPAD and RPAD
- The SUBSTR and SUBSTRING Command
- How SUBSTR Works with NO ENDING POSITION
- Using SUBSTR and LENGTH Together
- The LEFT and RIGHT Functions
- The POSITION Command finds a Letters Position
- The POSITION Command is brilliant with SUBSTR
- CHARINDEX Finds a Letter(s) Position in a String
- The CHARINDEX Command is brilliant with SUBSTRING
- The CHARINDEX Command Using a Literal
- The REPLACE Function
- REGEXP_REPLACE
- REGEXP_INSTR
- SOUNDEX Function to Find a Sound
- The ASCII Function
- The CHAR Function
- The UNICODE Function
- The Reverse String Function
- The RIGHT Function
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
- The Round Function and Precision
- The COALESCE Command
- COALESCE is Equivalent to this CASE Statement
- 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 CASE Challenge
- The CASE Challenge Answer
- The Decode Command
- A Trick for getting a Horizontal Case
- Put a CASE in the ORDER BY
- Using Decode to Sort Logically
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
- Join Views Allow Users to Merely Select Columns
- Sometimes we Create Views for Formatting
- Basic Rules for Views
- How to Modify a View
- The Exception to the ORDER BY Rule inside a View
- Derived Columns in a View Should Contain 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?
- Aggregates on View Aggregates
UNION Set Operator
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION ALL Explained Logically
- EXCEPT Explained Logically
- EXCEPT Explained Logically
- Minus Explained Logically
- Minus Explained Logically
- An Equal Number of Columns in both SELECT Lists
- Columns in the SELECT list should be from the same Domain
- The Top Query handles all Aliases
- The Bottom Query does the ORDER BY
- Great Trick: Place your Set Operator in a Derived Table
- UNION vs. UNION ALL
- Using UNION ALL and Literals
- A Great Example of how EXCEPT works
- USING Multiple SET Operators in a Single Request
- Changing the Order of Precedence with Parentheses
- Using UNION ALL for speed in Merging Data Sets
- Using UNION to be same as GROUP BY GROUPING SETS
Creating Tables
- Show Databases and Table DDL Commands
- Finding Constraints
- Create Table Syntax
- Creating A Table in Snowflake
- Creating Temporary and Transient Tables
- Comparing Table Types
- Data Types for Numeric, String, and Binary
- Data Types for Date, Time, and Unstructured
- Creating Tables with a Clustering Key
- Joining Tables Can Have the Same Clustering Keys for Speed
- Creating Tables with a Primary Key/Foreign Key Relationship
- A Table with a NOT NULL Constraint
- CREATE TABLE LIKE
- CREATE a Temporary TABLE using LIKE
- CREATE TABLE AS (CTAS) Populates the Table With Data
- CREATE TABLE AS (CTAS) Can Choose Certain Columns
- CREATE a Temporary Table AS (CTAS)
- CREATE a Temporary Table AS (CTAS) Using a Join
Data Manipulation Language (DML)
- INSERT Syntax # 1
- INSERT Syntax # 2
- INSERT Example with Multiple Rows
- Inserting Null Values into a Table
- INSERT/SELECT Command
- INSERT/SELECT to Build a Data Mart
- UPDATE Examples
- Subquery UPDATE Command Syntax
- Example of Subquery UPDATE Command
- Deleting Rows in a Table
Statistical Aggregate Functions
- The Stats Table
- The KURTOSIS Function
- A Kurtosis Example
- The SKEW Function
- A SKEW Example
- 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
- The CORR Function
- A CORR Example
- Another CORR Example so you can Compare
- The COVAR_POP Function
- A COVAR_POP Example
- Another COVAR_POP Example so you can Compare
- The REGR_INTERCEPT Function
- A REGR_INTERCEPT Example
- Another REGR_INTERCEPT Example so you can Compare
- The REGR_SLOPE Function
- A REGR_SLOPE Example
- Another REGR_SLOPE Example so you can Compare
- The REGR_AVGX Function
- A REGR_AVGX Example
- Another REGR_AVGX Example so you can Compare
- The REGR_AVGY Function
- A REGR_AVGY Example
- Another REGR_AVGY Example so you can Compare
- The REGR_COUNT Function
- A REGR_COUNT Example
- The REGR_R2 Function
- A REGR_R2 Example
- The REGR_SXX Function
- A REGR_SXX Example
- The REGR_SXY Function
- A REGR_SXY Example
- The REGR_SYY Function
- A REGR_SYY Example
- Using GROUP BY
Conclusion
Training Materials
All students receive comprehensive courseware.
Software Requirements
All attendees will need access to a Snowflake SQL environment with Accelebrate’s sample data loaded.
