Snowflake SQL Training

Chapter 1 - Getting Started with a Snowflake Trial Account

• How to Create a Starter Account on Snowflake
• Click on the START FOR FREE Menu Item
• Start Your 30-Day Free Trial
• Choose Your Edition of Snowflake and Cloud Provider
• You're Now Signed Up – An Email Will Arrive Soon
• Take the Snowflake Email and CLICK TO ACTIVATE
• Click the Snowflake Link to Enter a Username and Password
• Welcome to Snowflake Message Comes Up
• Snowflake Browser and Query Tool
• Snowflake Worksheet to Create and Run Queries
• Script to Create the Database, Schema, Tables, and Views
• Choose the COURSE OUTLINE Button for Snowflake
• Press on Download Snowflake Class Database Script Button
• Copy All of the Text From Your SnowflakeClass.Txt Script
• Run Your Script on the Snowflake Website – First Way
• Copy Your Script to the Snowflake Website – Alternative Way
• Run Your Script on the Snowflake Website – Alternative Way
• Your Systems Tree will Contain the Nexus Database
• The SQL_CLASS Schema Contains Your Tables
• Set Your Database to NEXUS and Schema to SQL_CLASS
• Download Nexus at CoffingDW.com
• Nexus Server Migrates All Data Warehouses to Snowflake
• Nexus Joins Data Across Platforms with the Super Join Builder
• YouTube Video of Nexus on Snowflake Look-and-Feel

Chapter 2 – What is Snowflake?

• Snowflake IPO Largest in History
• Advertising in the Computer Industry Pays off Each Year
• Scaling Compute and Storage on Public Clouds
• Customer Retention is Gold in the Computer Industry
• High-powered Investors are Critical
• Who are the Biggest Losers in the IPO?
• Sharing Data Between Customers and Providers is Amazing
• Standard SQL Makes Application Migration Easy
• Hiring a Proven CEO can make the Difference
• Snowflake Architecture
• Snowflake Architecture – Cloud Services
• Snowflake Architecture – Virtual Warehouse
• Snowflake Architecture – Data Storage
• Snowflake Tables are Immutable
• Elasticity
• Worker Nodes and Worker Processes
• Min-Max Based Pruning
• Snowflake Execution Engine
• Fault Tolerance
• Semi-Structured Data
• Snowflake Editions
• Snowflake Pricing
• Snowflake Virtual Warehouse Sizes
• Snowflake Storage Pricing

Chapter 3 – Configuring and Managing the Snowflake Warehouse

• Five System Defined Snowflake Roles
• Create a Snowflake Virtual Data Warehouse
• Create a Role
• Create a User and Grant a Role
• Scaling Up or Down the Virtual Data Warehouse
• Scaling Up the Virtual Warehouse
• Scaling Out the Virtual Data Warehouse
• Configuring a Warehouse
• The Life of a Query
• Resource Monitor
• Create Resource Monitor Screen
• Creating a Resource Monitor with SQL

Chapter 4 – Brilliant Features of Snowflake

• Snowflake Caching
• The Life of a Query With Caching (Option 1)
• The Life of a Query With Caching (Option 2)
• Time Travel Using OFFSET
• Time Travel Using TIMESTAMP Part 1
• Time Travel Using TIMESTAMP Part 2 - Troubleshooting
• Time Travel Using TIMESTAMP Timezone CST
• Time Travel Using TIMESTAMP With UTC Time
• Time Travel Using QUERY_ID Part 1
• Time Travel Using Query_ID Part 2
• Restoring Data Using Time Travel Feature (Best Option)
• Restoring Data Using Time Travel Feature (Bad Option)
• CREATE TABLE with Time Travel Days
• Account Usage
• Drop and Undrop a Table
• Drop and Undrop a Schema
• Drop and Undrop a Database
• Rename a Table
• SHOW TABLES With LIKE
• Table Types
• TABLE STORAGE METRICS QUERY
• Creating a Transient Schema
• Zero-Copy Cloning Fundamentals
• Zero-Copy Cloning
• Zero-Copy Cloning with Time Travel
• Zero-Copy Cloning Rules
• Zero-Copy Cloning a Database and Schema
• Cloning a Temporary Table
• Creating Temporary and Transient Tables
• Fail Safe
• Fail Safe Begins when Time Travel Ends
• Two Fail Safe Storage Metrics Query

Chapter 5 – Performance Tuning with Cluster Keys

• What is Parallel Processing?
• The Basics of a Single Computer
• Data in Memory is Fast as Lightning
• Parallel Processing Of Data
• Snowflake has Linear Scalability
• Snowflake Clustering For Performance Tuning
• Creating a Table with a Cluster Key
• Creating a Table with a Cluster Key Function
• Creating a Table with a Multi-Cluster Key
• Altering a Table to Create or Drop a Cluster Key
• Joining Tables Can Have the Same Cluster Keys for Speed
• The Emp_Tbl CREATE Statement with Eight Rows
• Snowflake Quiz –Put the Rows on the Proper Micro-Partition
• The First Row is Now on the Proper Micro-Partition
• Place the Remaining Rows on the Proper Micro-Partition
• The Rows Are Now on the Proper Micro-Partition
• Snowflake Slices will use Columnar Storage
• Snowflake Micro-Partitions 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 Cluster Key of DeptNo
• Snowflake Quiz –Place the Rows on the Micro-Partition
• The First Row is on the Proper Micro-Partition
• Place the Remaining Rows on the Proper Micro-Partitions
• The Rows are on the Proper Micro-Partitions
• Snowflake will use Columnar Storage
• Quiz – Can you Place the Metadata Correctly?
• Answer – Can you Place the Metadata Correctly?
• 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?
• Each Block Comes With Metadata
• Snowflake Architecture – Data Storage
• Min-Max Based Pruning

Chapter 6 – Data Sharing

• The Nexus Cache Tree Shows Objects Across All Systems
• Data Sharing
• Implementing Data Sharing
• Investigating Data Sharing – SHOW SHARES
• SEE SHARE DETAILS with the DESC Command
• CREATE a Database for the SHARE
• Sharing All Tables in a Database and Schema
• Investigating Data Sharing – SHOW SHARES
• SEE SHARE DETAILS with the DESC Command
• Data Sharing for Non-Snowflake Account Users
• Steps We Need to Take to Share with Non-Snowflake Users
• Creating a Reader Account
• Viewing Managed Accounts
• Sharing Data with the Managed Account
• Troubleshooting Sharing Data with the Managed Account
• Gathering the URL for Logging Into a Reader Account
• Using the URL to Login to your Snowflake Reader Account
• SHOW SHARES for Reader Account
• SEE SHARE DETAILS with the DESC Command
• CREATE a Database and Warehouse for the SHARE
• Create a User and Grant a Role for Reader Account Users

Chapter 7 – Snowflake Tasks

• Creating a Task
• Creating a Task Using CRON
• Creating a Task Using CRON Using Minute and Hour
• Tasks Using CRON Using Minute, Hour, and Day of Week
• CRON Using Minute, Hour Range, and Day of Week Range
• CRON with Minute, Multiple Hours, and Day of Week Range
• CRON with Minute, Multiple Hours, and Last Day of Month
• CRON with Minute, Multiple Hours, and Month
• CRON Last Friday of the Month
• CRON With America/Los Angeles Time
• Creating a Tree of Tasks

Chapter 8 – Roles and Access Rights

• Access Control Framework
• Access Control Objects Hierarchy
• Roles
• Five System Defined Snowflake Roles
• Roles Can Inherit Privileges
• Current Role
• Primary and Secondary Roles
• Using the ACCOUNTADMIN Role
• Precautions for ACCOUNTADMIN Role
• Accessing Database Objects
• Role Examples
• CREATE ROLE Examples
• Create Database and Schema Examples
• Granting Rights to Roles Examples
• Creating Users
• Granting Rights to Roles Examples
• Simplifying Grant Management Using Future Grants

Chapter 9 – Data Ingestion

• Migrate Data to Snowflake at High Speeds with Nexus
• Loading Data
• What is a Staging Area?
• Snowflake has Four Types of Stages
• Snowflake Internal Stages
• Data File Details
• User Stage
• Loading User Stage Data with the Snowflake Web Interface
• Go to the Databases Tab to See Your Databases
• Click on the Table You Want To Load
• The Table Columns Appear – Choose Load Table
• Load Data Screen Appears
• Choose Your Source Files
• Choose Your File Format
• Create the File Format
• Create the File Format – Press Load
• Load Results
• Table Stage
• Download a Flat File From Our Website for Load Exercise
• Choose the COURSE OUTLINE Button for Snowflake
• Press on Download Snowflake Class Database Script Button
• Copying Data Into a Table Using the Table Stage
• The PUT Command will not work on Snowflake Browser
• Query a Table Residing in the Table Stage
• Using the Copy Command with File Format Options
• Named Stages
• Create Three Internal Named Stages Using SQL
• Creating Three Internal Named Stages with Nexus
• Using an Internal Named Stage to Load Data with PUT
• Create Named Stage Using the Snowflake Browser Tool - 1
• Create Named Stage Using the Snowflake Browser Tool - 2
• Create Named Stage Using the Snowflake Browser Tool - 3
• Create Named Stage Using the Snowflake Browser Tool - 4
• Create Named Stage Using the Snowflake Browser Tool - 5
• Create Named Stage Using the Snowflake Browser Tool - 6
• Importing Data From External Stages
• Example of Copy Command from an External Stage
• Example of Copy Command Using Pattern
• Pattern Results from Loading Multiple Files
• Copying Only Some Columns Into a Table
• Functions for Transforming Data During a Load
• Transformation Example Using CASE and Implicit CAST
• Transformation Example To Populate Only Two Columns
• Using the ON_ERROR Options
• ON_ERROR Defaults to ABORT_STATEMENT
• ON_ERROR SKIP_FILE Option
• ON_ERROR_<NUMBER> SKIP_FILE Option
• ON_ERROR_<PERCENTAGE> SKIP_FILE Option
• Creating and Describing a File Format (1 of 3)
• Creating and Describing a File Format (2 of 3)
• Creating and Describing a File Format (3 of 3)
• Altering or Changing Attributes in a File Format
• DESCRIBING our File Format to Confirm Attribute Changes
• Important Copy Options – Validation Mode
• Validation Mode RETURN_ERRORS Example
• Validation Mode RETURN_n_ROWS Example of an Error
• Validation Mode RETURN_n_ROWS Example of Success
• Saving Load Error Rows in a Table
• Another Technique to Save Load Error Rows in a Table
• SPLIT_PART Function for Easy Reading of Errors
• Important Copy Options – Size Limit
• Example of Using the Size_Limit Option (1 of 3)
• Example of Using the Size_Limit Option (2 of 3)
• Example of Using the Size_Limit Option (3 of 3)
• Important Copy Options – RETURNED_FAILED_ONLY
• RETURNED_FAILED_ONLY Example
• Important Copy Options – TRUNCATECOLUMNS
• TRUNCATECOLUMNS Example (1 of 3)
• TRUNCATECOLUMNS Example (2 of 3)
• TRUNCATECOLUMNS Example (3 of 3)
• Important Copy Options – FORCE
• FORCE Example
• An Example of a JSON File
• Creating a JSON Stage and File Format
• Querying the JSON Table
• Tricks and Tips to Query the JSON Table
• Tricks and Tips to Query Nested Data in a JSON Table
• Tricks and Tips to Query Arrays in a JSON Table
• Tricks and Tips to Flatten Arrays in a JSON Table
• Creating a Table with Flattened Data
• LOAD_HISTORY
• LOAD_HISTORY Results
• LOAD_HISTORY For Loads Happening Before Today
• STAGES View in Information_Schema
• Nexus Server Migrates All Data Warehouses to Snowflake

Chapter 10 – Information_Schema System Catalog

• APPLICABLE_ROLES
• COLUMNS
• COLUMNS EXAMPLE
• DATABASES
• ENABLED_ROLES
• EXTERNAL_TABLES
• FILE_FORMATS
• FUNCTIONS
• INFORMATION_SCHEMA_CATALOG_NAME
• LOAD_HISTORY
• OBJECT_PRIVILEGES
• PACKAGES
• PIPES
• PROCEDURES
• REFERENTIAL_CONSTRAINTS
• REPLICATION_DATABASES
• SCHEMATA
• SEQUENCES
• STAGES
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLE_STORAGE_METRICS
• TABLES
• USAGE_PRIVILEGES
• VIEWS

Chapter 11 – Systems Functions

• ALL_USER_NAMES and CURRENT_IP_ADDRESS
• CURRENT_ACCOUNT, CURRENT_AVAILABLE_ROLES
• CURRENT_CLIENT and CURRENT_DATE
• CURRENT_DATABASE and CURRENT_SCHEMA
• CURRENT_SESSION and CURRENT_STATEMENT
• CURRENT_TIME and CURRENT_TIMESTAMP
• CURRENT_VERSION and CURRENT_WAREHOUSE
• CURRENT_TRANSACTION and CURRENT_USER
• CURRENT_REGION and CURRENT_ROLE

Chapter 12 – Introduction to SQL

• Introduction
• SELECTING Current Information
• 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
• An Order By That Uses an Expression
• 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?
• 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 and ORDER BY Clause
• Using an Alias in the ORDER BY Clause with Decode
• 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

Chapter 13 – The WHERE Clause

• The WHERE Clause limits Returning Rows
• Numbers Don't Need Single Quotes
• Not Equal
• 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 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
• 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
• 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
• 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 Name Ends in 'Y'
• Escape Character in the LIKE Command changes Wildcards
• Escape Characters Turn off Wildcards in the LIKE Command
• Time Travel Using OFFSET
• Time Travel Using TIMESTAMP Part 1
• Time Travel Using TIMESTAMP Part 2
• Time Travel Using TIMESTAMP Timezone CST
• Time Travel Using TIMESTAMP With UTC Time
• Time Travel Using QUERY_ID Part 1
• Time Travel Using Query_ID Part 2

Chapter 14 – Distinct, Group By, Top, and Pivot

• 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?
• Top Command
• Top Command and Order By
• Top Command and Order By Plus Nulls Last
• The FETCH Clause
• Sample and Tablesample
• TOP vs. SAMPLE
• Two Forms of Data Sampling
• The Pivot Command
• Extreme Pivot Challenge
• Answer - Extreme Pivot Challenge
• Alternate Answer - Extreme Pivot Challenge

Chapter 15 – Aggregation

• Quiz – Calculate the Answer Set in your Mind
• Answer – Calculate the Answer Set in your Mind
• Quiz 2  – Calculate the Answer Set in your Mind
• 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 are Equivalent
• 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
• ANY_VALUE
• COUNT_IF
• GROUP BY GROUPING SETS
• GROUP BY GROUPING SETS Super Query
• GROUP BY ROLLUP
• GROUP BY ROLLUP Super Query
• GROUP BY CUBE
• GROUP BY CUBE Answer Set
• GROUP BY CUBE Super Query

Chapter 16 – Joining Tables

• Nexus Builds Your Join SQL Automatically
• 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
• RIGHT OUTER JOIN
• RIGHT OUTER Join Example and Results
• 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?
• 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 17 – Date Functions

• CURRENT_DATE
• CURRENT_DATE and CURRENT_TIMESTAMP
• Current_Timestamp and Local_Timestamp With Precision
• CURRENT_TIME vs. LOCALTIME With Precision
• Add or Subtract Days from a Date
• The ADD_MONTHS Command
• ADD_MONTHS to Add a Year to a Date
• ADD_MONTHS to Add Five Years to a Date
• Incrementing Date Values Using the Dateadd Function
• Incrementing Time Values Using the Dateadd Function
• 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
• MONTHNAME
• EXTRACT from DATES and TIME
• EXTRACT from DATES and TIME Optional Syntax
• Another Option for Extracting Portions of Dates and Times
• The DATE_PART Function
• Using DATE_PART to Extract
• Implied Extract of Day, Month, and Year using TO_CHAR
• The DATE_PART Function Using Day of Week (DOW)
• Day of Week and a CASE Statement
• Day of Week and DECODE
• Great Date Functions to Know
• Week of Year and Year of Week
• First Day and Last Day Functions
• DATEDIFF
• Using CASE and Extract to Reformat Dates
• Using CAST and SUBSTRING to Reformat Dates
• The Date_Trunc Function
• DATE_TRUNC Command With Time
• DATE_TRUNC Command With Dates
• LAST_DAY
• LAST_DAY
• NEXT_DAY
• PREVIOUS_DAY
• MONTHS_BETWEEN
• TIME_SLICE
• TO_TIMESTAMP
• Using Day, Month, and Year intervals
• The Basics of a Simple Interval

Chapter 18 – Temporary Tables

• Nexus Joins Snowflake Tables with Excel Worksheets
• 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
• Quiz - Derived Table Challenge
• Answer to Quiz - Derived Table Challenge
• An Example of Two Derived Tables Using WITH
• 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 Looping Continued
• WITH RECURSIVE Derived Table Ends the Looping
• WITH RECURSIVE Derived Table Final SELECT
• WITH RECURSIVE Results
• Creating a Temporary Table
• CREATE a Temporary TABLE using LIKE
• Creating a Temporary Table using a CTAS
• CREATE Temporary Table AS (CTAS) with Specific Columns
• CREATE a Temporary Table AS (CTAS) Using a Join
• Cloning a Temporary Table
• Dropping a Temporary Table

Chapter 19 – 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
• Quiz- Write the Correlated Subquery
• 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 - Write the Subquery with Two Parameters
• Quiz - Write the Two Parameter Subquery With an Aggregate
• Answer - Two Parameter Subquery and an Aggregate
• 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

Chapter 20  – Analytic and Window Functions

• Nexus Gives You Snowflake Analytics for Free
• ROW_NUMBER
• Quiz – How did the Row_Number Reset?
• Quiz – Return Only the last Two Days Per Product_ID
• Answer – Return Only the last Two Days Per Product_ID
• Quiz – Return Two Students Per Class_Code with Highest Grades
• Answer – Return Two Students Per Class_Code with Highest Grades
• Using a Derived Table
• RANK
• Dense_Rank
• RANK vs. DENSE_RANK
• Getting RANK to Sort in DESC Order
• RANK() OVER, PARTITION BY, and QUALIFY
• Using 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 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?
• 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 OLAP
• 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 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
• CSUM For Each Product_ID For the First Three Days
• 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 Combined with Row_Number and Qualify
• FIRST_VALUE and Qualify with a Row_Number
• 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 Qualify
• CUME_DIST and a Derived Table
• CUME_DIST and a Partition By Statement
• CUME_DIST with 14 Rows
• CUME_DIST With a Partition on 7 Rows
• CURRENT ROW AND UNBOUNDED FOLLOWING
• 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
• MEDIAN Example
• MEDIAN with Partitioning and a WHERE Clause
• MEDIAN with Partitioning
• PERCENTILE_CONT Function Description and Syntax
• 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)
• LISTAGG Basic Example
• LISTAGG With a Pipe-Separated List
• LISTAGG With a Comma-Separated List in Groups
• NTH_VALUE Function and Syntax
• NTH_VALUE Arguments
• NTH_VALUE Function and Syntax
• NTH_VALUE With Partition
• NTH_VALUE With Partition and Ignore Nulls
• RATIO_TO_REPORT Function
• RATIO_TO_REPORT Example
• ANY_VALUE
• MODE
• Width_Bucket
• COUNT_IF

Chapter 21 – Strings

• UPPER and lower  Functions
• The Length Command Counts Characters
• LENGTH Does Not Work on Fixed Length Columns
• OCTET_LENGTH
• The TRIM Command Trims Leading and Trailing Spaces
• The RTRIM and LTRIM Command Trims Spaces
• Concatenation
• Concat and Concat_WS for Concatenation
• The SUBSTR and SUBSTRING Commands
• How SUBSTR Works with NO ENDING POSITION
• Using SUBSTR and CHAR_LENGTH Together
• The POSITION Command finds a Letters Position
• The POSITION Command is brilliant with SUBSTR
• CHARINDEX Finds a Letter's Position in a String
• CHARINDEX Command is Brilliant with SUBSTRING
• The CHARINDEX Command Using a Literal
• LPAD and RPAD
• The REPLACE Function
• REGEXP Example for Whitespace Character
• REGEXP Example for Non-Whitespace
• REGEXP Example for [xyz]
• REGEXP Example Start of a String
• REGEXP Example End of a String
• REGEXP Example Matching Within a Range
• REGEXP_REPLACE
• REGEXP_REPLACE Example
• Another REGEXP_REPLACE Example
• REGEXP_INSTR
• REGEXP_LIKE
• RLIKE
• REGEXP_SUBSTR
• REGEXP_SUBSTR Example
• SOUNDEX Function to Find a Sound
• The REVERSE String Function
• The RIGHT Function
• The LEFT and RIGHT Functions
• The ASCII Function

Chapter 22 – Interrogating the Data

• 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
• COALESCE in a Real-World Example
• 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
• The DECODE Command
• DECODE
• A Trick for getting a Horizontal Case
• Extreme CASE Challenge
• Answer - Extreme CASE Challenge
• 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?
• CASE Challenge
• Answer - CASE Challenge
• Quiz – Advanced Case Challenge
• Answer – Advanced Case Challenge

Chapter 23 – 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
• How to Modify a View
• Creating a Secure View to Restrict DDL Viewing
• The Exception to the ORDER BY Rule inside a View
• Another Exception to the ORDER BY Rule is TOP
• Derived Columns in a View Must 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?

Chapter 24 – Set Operators

• Rules of Set Operators
• 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
• Quiz - Minus Explained Logically
• Answer - MINUS Explained Logically
• Quiz - Testing Your Knowledge
• 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
• Answer - Intersect Challenge
• Quiz - Set Operator Challenge
• Answer – Set Operator Challenge
• Quiz - Advanced Set Operator Challenge
• Answer - Advanced Set Operator 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 and MINUS work
• Using UNION to be same as GROUP BY GROUPING SETS
• USING Multiple SET Operators in a Single Request
• Changing the Order of Precedence with Parentheses

Chapter 25 – Creating Tables

• Table Types
• SHOW TABLES With LIKE
• TABLE STORAGE METRICS QUERY
• Snowflake Data Types
• More Snowflake Data Types
• Show Databases and Table DDL Commands
• Finding Constraints
• The Basics of Creating a Table
• Creating a Table
• Creating Temporary and Transient Tables
• Snowflake Clustering
• Creating a Table with a Cluster Key
• Creating a Table with a Cluster Key Function
• Creating a Table with a Multi-Cluster Key
• Altering a Table to Create or Drop a Cluster Key
• Joining Tables Can Have the Same Cluster Keys for Speed
• Creating Tables with a Primary Key/Foreign Key Relationship
• Primary Key Does Not Enforce Uniqueness
• A Table with a NOT NULL Constraint
• AUTOINCREMENT
• AUTOINCREMENT START = 10 INCREMENT = 20
• Restoring Data Using Time Travel Feature (Best Option)
• Restoring Data Using Time Travel Feature (Bad Option)
• Fail Safe
• Fail Safe Begins when Time Travel Ends
• CREATE TABLE with Time Travel Days
• 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 (CTAS) with Specific Columns
• CREATE a Temporary Table AS (CTAS) Using a Join
• Create a Table IF NOT EXISTS
• A Table with a NOT NULL Constraint
• Create a Table with a Column Default Value
• Creating a Transient Schema
• Zero-Copy Cloning Fundamentals
• Zero-Copy Cloning
• Zero-Copy Cloning with Time Travel
• Zero-Copy Cloning Rules
• Zero-Copy Cloning a Database and Schema
• Cloning a Temporary Table
• CREATE TABLE AS (CTAS) Populates the Table With Data
• CREATE TABLE AS (CTAS) Can Choose Specific Columns
• CREATE Temporary Table AS (CTAS) with Specific Columns
• CREATE a Temporary Table AS (CTAS) Using a Join

Chapter 26 – Inserts, Updates, and Deletes

• 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
• Example of Subquery UPDATE Command
• MERGE
• Drop and Undrop a Table
• Drop and Undrop a Schema
• Drop and Undrop a Database
• Rename a Table
• Deleting Rows in a Table

Chapter 27 – Unstructured Data – JSON and XML

• JSON and the Internet of Things
• Why Use JSON?
• JSON Types
• JSON Syntax Explanation
• Building a Complex Object
• Continuing to Build a Complex Object
• Creating a Table and Inserting JSON Data
• Querying the JSON Table
• Performing a Flatten to Display Rows Like a Typical Table
• Creating a View on a Flattened Table
• Further Building a Complex Object
• Creating a Table and Inserting JSON Data
• Querying the JSON Table
• Creating a JSON Table
• Inserting Two Objects Into a JSON Table
• How to Query a JSON Variant Data Type
• Inserting 10 Objects Into a JSON Table
• JSON Technique to CREATE a Table and INSERT Data
• How to Query JSON USING $1
• How to Format JSON Data
• Querying Portions of Semi-Structured Data
• Querying Semi-Structured Data With a WHERE Clause
• Using the FLATTEN Function to Parse Arrays
• Using the FLATTEN Function to Parse Nested Arrays
• Extracting Values by Path Using the GET_PATH Function
• Creating an XML Table
• Insert Into An XML Table
• XMLGET and FLATTEN Function For XML Data
• PARSE_JSON Semi-Structured Data Function
• Describing a Table that Contains a Variant Data Type
• Inserting a Wide Variety of Data Into a Variant Data Type
• Using the TYPEOF Function
• Using the STRIP_NULL_VALUE Function

Chapter 28 – User Defined Functions (UDFs)

• User Defined Functions
• CREATE Function Syntax
• Two Simple Functions with Hard-Coded Values
• You Can Use a Function in a Join
• You Can Use a Function in WHERE Clause
• You Can Use a Function in the ORDER BY Clause
• Creating a function with an Input Argument
• Create a Function to get Factorial Numbers
• Creating a function to Get Fibonacci Numbers
• Creating a function with the Keyword TABLE
• Joining a Function with the Keyword TABLE
• JavaScript UDF with a Try Catch Block
• Using a JavaScript UDF with a Try Catch Block for Validation
• JavaScript UDF with IF, ELSE IF, and ELSE Try Catch Block
• DDL and DML Operations are Not Supported in Functions
• An EXAMPLE of a CASE Statement Using Two Functions
• Functions With Duplicate Names But Different Arguments
• You ALTER a Function for Three Reasons
• Examples of Altering a Function
• SHOW User Functions
• Information_Schema Functions
• GET DDL Function
• Conversion Functions and TRY_CAST
• Semi-Structured Data Functions
• Functions for Array and Object Creation and Manipulation
• Functions for Semi-structured Extraction and Conversion
• Functions for Semi-structured Extraction and Type Predicates

Chapter 29 – Stored Procedures

• Why Use Snowflake Stored Procedures?
• Stored Procedure Syntax Using Javascript
• Create and Execute Your First Javascript Stored Procedure
• Create and Execute with an Argument Using Backticks
• A Stored Procedure to INSERT Using Language SQL
• SQL Stored Procedure INSERT Using Input Parameters
• Creating a Snowflake Stored Procedure that Updates
• Stored Procedure UPDATE Using Subquery
• Stored Procedure Delete Using an Input Parameter
• Snowflake Stored Procedure Control Structures
• A Stored Procedure With IF and END IF Logic
• A Stored Procedure With IF, ELSEIF, and ELSE Logic
• A Stored Procedure With Simple Valued Case Logic
• A Stored Procedure With Searched Case Logic
• Using Loops in Stored Procedures
• Using a WHILE Loop
• Using a FOR Loop
• Using a FOR Reverse Loop
• Stored Procedure Workshop
• Stored Procedure Workshop Answer Using Loop
• Stored Procedure Workshop Answer Using WHILE
• Stored Procedure Workshop Answer Using FOR Loop
• Procedure Workshop Answer Using Reverse FOR Loop
• Creating a Complex Javascript Stored Procedure
• Inserting a Row Into a Table Using Javascript
• Updating a Row Into a Table Using Javascript
• Deleting a Row Into a Table Using Javascript
• DDL that Applies to Stored Procedures
• Describe Procedure
• Procedure Definition from Information_Schema
• Procedure Definition from GET_DDL
• Procedure Definition from GET_DDL using TRUE Keyword
• Alter Procedure
• Renaming a Procedure
• Dropping a Procedure
• Snowflake Data Types you must Convert to Javascript
• Stored Procedure to Get Methods from an Object
• Stored Procedure Javascript Methods Part 1
• Stored Procedure JavaScript Methods
• Stored Procedure Javascript Methods Part 2
• Stored Procedure to Get Methods from a Result Set Object
• Stored Procedure Javascript Result Set Methods
• Make Your Arguments in Upper Case
• Best Practice – Make Your Arguments in Upper Case
• How to Catch and Error using Try/Catch
• Using a WHILE Loop
• Creating a Stored Procedure to Get Fibonacci Numbers
• Create a Stored Procedure to get Factorial Numbers
• Using a WHILE Loop Example Result Set
• Line Continuation for Long SQL Statements
• Loading Rows From a Join Into a Table
• Using Stored Procedures to Produce Dynamic SQL
• Dynamic SQL Example to Truncate a Table
• Dynamic SQL Store Procedure that Calls Another Procedure
• Stored Procedures Privileges
• Information_Schema for a Database and a Schema
• A Stored Procedure to Show Privileges
• Finding Details About Procedures
• Granting and Revoking Usage on a Procedure to Another Role

Chapter 30 – Statistical Aggregate Functions

• The Stats Table
• The KURTOSIS Function
• A KURTOSIS Example
• The SKEW Function
• SKEW Example
• The STDDEV_POP Function
• 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 VARIANCE Function
• A VARIANCE Example
• The COVAR_POP Function
• A COVAR_POP Example
• Another COVAR_POP Example so you can Compare
• The COVAR_SAMP Function
• A COVAR_SAMP Example
• Another COVAR_SAMP 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
• APPROX_COUNT_DISTINCT

Chapter 31 – Mathematical Functions

• Numeric Manipulation Functions
• ABS
• ACOS
• ACOSH
• ASIN
• ASINH
• ATAN
• ATAN2
• ATANH
• CBRT
• Ceil
• COS
• COSH
• COT
• DEGREES
• Numeric Function – DIV0 (Division Operator)
• EXP
• FACTORIAL
• Floor
• HAVERSINE
• LN
• LOG
• MOD
• PI
• POW or POWER
• RADIANS
• ROUND
• SIGN
• SIN
• SINH
• SQRT
• SQUARE
• TAN
• TANH
• TRUNC or TRUNCATE