Learning Snowflake SQL and Scripting: Generate, Retrieve, and Automate Snowflake Data

Preface
Relational Database Primer
Snowflake
What Is SQL?
What Is SQL Scripting?
Setting Up a Sample Database
Sample Database Setup
Create a worksheet
Create your database
Sample Database Option #1: Copy from TPCH_SF1
Create and populate the tables
Sample Database Option #2: Load Data from GitHub Files
Creating sample database tables
Load files into tables
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
Column aliases
Removing duplicates
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
Boolean
Variant
Array
Object
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
Temporal functions that return dates
Temporal functions that return strings
Temporal functions that return numbers
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
Multiple-row, single-column subqueries
Multicolumn subqueries
Correlated Subqueries
Exists operator
Correlated subqueries in update and delete statements
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
Lock wait time
Deadlocks
Transactions and Stored Procedures
Wrap-Up
Test Your Knowledge
12. Views
What Is a View?
Creating Views
Using Views
Why Use Views?
Data Security
Restricting column access
Restricting row access
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
loop
repeat
while
for
Exceptions
Catching exceptions
Declaring and raising 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
Chapter 1
Solution to Exercise 1-1
Solution to Exercise 1-2
Solution to Exercise 1-3
Solution to Exercise 1-4
Chapter 2
Solution to Exercise 2-1
Solution to Exercise 2-2
Solution to Exercise 2-3
Solution to Exercise 2-4
Chapter 3
Solution to Exercise 3-1
Solution to Exercise 3-2
Solution to Exercise 3-3
Solution to Exercise 3-4
Chapter 4
Solution to Exercise 4-1
Solution to Exercise 4-2
Solution to Exercise 4-3
Solution to Exercise 4-4
Chapter 5
Solution to Exercise 5-1
Solution to Exercise 5-2
Solution to Exercise 5-3
Solution to Exercise 5-4
Chapter 6
Solution to Exercise 6-1
Solution to Exercise 6-2
Solution to Exercise 6-3
Solution to Exercise 6-4
Chapter 7
Solution to Exercise 7-1
Solution to Exercise 7-2
Solution to Exercise 7-3
Solution to Exercise 7-4
Chapter 8
Solution to Exercise 8-1
Solution to Exercise 8-2
Solution to Exercise 8-3
Solution to Exercise 8-4
Chapter 9
Solution to Exercise 9-1
Solution to Exercise 9-2
Solution to Exercise 9-3
Chapter 10
Solution to Exercise 10-1
Solution to Exercise 10-2
Solution to Exercise 10-3
Chapter 11
Solution to Exercise 11-1
Chapter 12
Solution to Exercise 12-1
Solution to Exercise 12-2
Chapter 13
Solution to Exercise 13-1
Solution to Exercise 13-2
Chapter 14
Solution to Exercise 14-1
Solution to Exercise 14-2
Solution to Exercise 14-3
Solution to Exercise 14-4
Chapter 15
Solution to Exercise 15-1
Solution to Exercise 15-2
Solution to Exercise 15-3
Solution to Exercise 15-4
Chapter 16
Solution to Exercise 16-1
Solution to Exercise 16-2
Chapter 17
Solution to Exercise 17-1
Solution to Exercise 17-2
Solution to Exercise 17-3
Solution to Exercise 17-4
Chapter 18
Solution to Exercise 18-1
Solution to Exercise 18-2
Solution to Exercise 18-3
Index