Learning Snowflake SQL and Scripting

Preface
Relational Database Primer
Snowflake
What Is SQL?
What Is SQL Scripting?
Setting Up a Sample Database
Sample Database Setup
Sample Database Option #1: Copy from TPCH_SF1
Sample Database Option #2: Load Data from GitHub Files
Conventions Used in This Book
Using Code Examples
O’Reilly Online Learning
How to Contact Us
Acknowledgments

1. Query Primer
Query Basics
Query Clauses
The select Clause
The from Clause
The where Clause
The group by Clause
The having Clause
The qualify Clause
The order by Clause
The limit Clause
Wrap-Up
Test Your Knowledge

2. Filtering
Condition Evaluation
Using Parentheses
Using the not Operator
Condition Components
Equality Conditions
Inequality Conditions
Range Conditions
Membership Conditions
Matching Conditions
Null Values
Filtering Using Snowsight
Wrap-Up
Test Your Knowledge

3. Joins
What Is a Join?
Table Aliases
Inner Joins
Outer Joins
Cross Joins
Joining Three or More Tables
Joining a Table to Itself
Joining the Same Table Twice
Wrap-Up
Test Your Knowledge

4. Working with Sets
Set Theory Primer
The union Operator
The intersect Operator
The except Operator
Set Operation Rules
Sorting Compound Query Results
Set Operation Precedence
Wrap-Up
Test Your Knowledge

5. Creating and Modifying Data
Data Types
Character Data
Numeric Data
Temporal Data
Other Data Types
Creating Tables
Populating and Modifying Tables
Deleting Data
Modifying Data
Merging Data
Wrap-Up
Test Your Knowledge

6. Data Generation, Conversion, and Manipulation
Working with Character Data
String Generation and Manipulation
String Searching and Extracting
Working with Numeric Data
Numeric Functions
Numeric Conversion
Number Generation
Working with Temporal Data
Date and Timestamp Generation
Manipulating Dates and Timestamps
Date Conversion
Wrap-Up
Test Your Knowledge

7. Grouping and Aggregates
Grouping Concepts
Aggregate Functions
count() Function
min(), max(), avg(), and sum() Functions
listagg() Function
Generating Groups
Multicolumn Grouping
Grouping Using Expressions
Generating Rollups
Filtering on Grouped Data
Filtering with Snowsight
Wrap-Up
Test Your Knowledge

8. Subqueries
Subqueries Defined
Subquery Types
Uncorrelated Subqueries
Correlated Subqueries
Subqueries as Data Sources
Subqueries in the from Clause
Common Table Expressions
Wrap-Up
Test Your Knowledge

9. From Clause Revisited
Hierarchical Queries
Time Travel
Pivot Queries
Random Sampling
Full Outer Joins
Lateral Joins
Table Literals
Wrap-Up
Test Your Knowledge

10. Conditional Logic
What Is Conditional Logic?
Types of Case Expressions
Searched Case Expressions
Simple Case Expressions
Uses for Case Expressions
Pivot Operations
Checking for Existence
Conditional Updates
Functions for Conditional Logic
iff() Function
ifnull() and nvl() Functions
decode() Function
Wrap-Up
Test Your Knowledge

11. Transactions
What Is a Transaction?
Explicit and Implicit Transactions
Related Topics
Finding Open Transactions
Isolation Levels
Locking
Transactions and Stored Procedures
Wrap-Up
Test Your Knowledge

12. Views
What Is a View?
Creating Views
Using Views
Why Use Views?
Data Security
Data Aggregation
Hiding Complexity
Considerations When Using Views
Wrap-Up
Test Your Knowledge

13. Metadata
information_schema
Working with Metadata
Schema Discovery
Deployment Verification
Generating Administration Scripts
get_ddl() Function
account_usage
Wrap-Up
Test Your Knowledge

14. Window Functions
Windowing Concepts
Data Windows
Partitioning and Sorting
Ranking
Ranking Functions
Top/Bottom/Nth Ranking
Qualify Clause
Reporting Functions
Positional Windows
Other Window Functions
Wrap-Up
Test Your Knowledge

15. Snowflake Scripting Language
A Little Background
Scripting Blocks
Scripting Statements
Value Assignment
if
case
Cursors
Loops
Exceptions
Wrap-Up
Test Your Knowledge

16. Building Stored Procedures
Why Use Stored Procedures?
Turning a Script into a Stored Procedure
Stored Procedure Execution
Stored Procedures in Action
Returning Result Sets
Dynamic SQL
Wrap-Up
Test Your Knowledge

17. Table Functions
User-Defined Functions
What Is a Table Function?
Writing Your Own Table Functions
Using Built-In Table Functions
Data Generation
Flattening Rows
Finding and Retrieving Query Results
Wrap-Up
Test Your Knowledge

18. Semistructured Data
Generating JSON from Relational Data
Storing JSON Documents
Querying JSON Documents
Wrap-Up
Test Your Knowledge

A. Sample Database
B. Solutions to Exercises

Index
About the Author