Call : (+91) 968636 4243
Mail : info@EncartaLabs.com

Snowflake SQL

( Duration: 3 Days )

Program Overview
Program Objectives

In Snowflake SQL training course, you will learn Snowflake SQL, which is the most comprehensive SQL of any database. You can improve their SQL enormously through the hundreds of examples using Snowflake SQL commands.

By attending Snowflake SQL workshop, delegates will learn to:

Use the Nexus Chameleon to migrate and move data to Snowflake
Use Nexus to work with the Snowflake database to run SQL statements, retrieve advanced analytics, and create graphs and charts
Understand a wide variety of Snowflake analytics
Fully understand and use Joins and Subqueries
Interrogate the data using Case, Coalesce, and Decode
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
Write SQL for advanced Statistical Aggregate Functions

COURSE AGENDA

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
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
The FETCH Clause
Sample and Tablesample
TOP vs. Sample
The Pivot Command

Aggregation

The 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 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
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 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 With a Pipe-Separated List
LISTAGG With a Comma-Separated List in Groups
MEDIAN Function
MEDIAN with Partitioning and a WHERE Clause
MEDIAN with Partitioning
NTILE Function
How Ntile Works
Ntile
Ntile Continued
Ntile Percentile
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 With PARTITION and (0.4)
PERCENTILE_DISC Function Description and Syntax
PERCENTILE_DISC Function Arguments
PERCENTILE_DISC Example with Percentage Change
PERCENTILE_DISC With PARTITION and (0.4)
RATIO_TO_REPORT Function
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
Equivalent Queries
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 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
UNION Explained Logically
UNION ALL Explained Logically
EXCEPT 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
Deleting Rows in a Table

Statistical Aggregate Functions

The Stats Table
The KURTOSIS Function
The SKEW Function
The STDDEV_POP Function
The STDDEV_SAMP Function
The VAR_POP Function
The VAR_SAMP Function
The CORR Function
The COVAR_POP Function
The REGR_INTERCEPT Function
The REGR_SLOPE Function
The REGR_AVGX Function
The REGR_AVGY Function
The REGR_COUNT Function
The REGR_R2 Function
The REGR_SXX Function
The REGR_SXY Function
The REGR_SYY Function
Using GROUP BY

Encarta Labs Advantage

One Stop Corporate Training Solution Providers for over 6,000 various courses on a variety of subjects
All courses are delivered by Industry Veterans
Get jumpstarted from newbie to production ready in a matter of few days

Trained more than 50,000 Corporate executives across the Globe
All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting https://www.encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top

Notice