Learning Snowflake SQL and Scripting

Learning Snowflake SQL and Scripting

by Alan Beaulieu
Released October 2023
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098140328

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

To help you on the path to becoming a Snowflake pro, this concise yet comprehensive guide reviews fundamentals and best practices for Snowflake's SQL and Scripting languages. Developers and data professionals will learn how to generate, modify, and query data in the Snowflake relational database management system as well as how to apply analytic functions for reporting.

Author Alan Beaulieu also shows you how to create scripts, stored functions, and stored procedures to return data sets using Snowflake Scripting. This book is ideal whether you're new to databases and need to run queries or reports against a Snowflake database, or transitioning from databases such as Oracle, SQL Server, or MySQL to cloud-based platforms.

With this book, you will:

  • Generate and modify Snowflake data using INSERT, UPDATE, DELETE
  • Query data in Snowflake using SELECT, including joining multiple tables, using subqueries, and grouping
  • Apply analytic functions for performing subtotals, grand totals, row comparisons, and other reporting functionality
  • Build scripts combining SQL statements with looping, if-then-else, and exception handling
  • Learn how to build stored procedures and functions
  • Use stored procedures to return data sets

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Relational Database Primer
    2. Snowflake
    3. What Is SQL?
    4. What Is SQL Scripting?
    5. Setting Up a Sample Database
      1. Sample Database Setup
      2. Sample Database Option #1: Copy from TPCH_SF1
      3. Sample Database Option #2: Load Data from GitHub Files
    6. Conventions Used in This Book
    7. Using Code Examples
    8. O’Reilly Online Learning
    9. How to Contact Us
    10. Acknowledgments
  2. 1. Query Primer
    1. Query Basics
    2. Query Clauses
      1. The select Clause
      2. The from Clause
      3. The where Clause
      4. The group by Clause
      5. The having Clause
      6. The qualify Clause
      7. The order by Clause
      8. The limit Clause
      9. Wrap-Up
    3. Test Your Knowledge
  3. 2. Filtering
    1. Condition Evaluation
      1. Using Parentheses
      2. Using the not Operator
    2. Condition Components
      1. Equality Conditions
      2. Inequality Conditions
      3. Range Conditions
      4. Membership Conditions
      5. Matching Conditions
    3. Null Values
    4. Filtering Using Snowsight
    5. Wrap-Up
    6. Test Your Knowledge
  4. 3. Joins
    1. What Is a Join?
    2. Table Aliases
    3. Inner Joins
    4. Outer Joins
    5. Cross Joins
    6. Joining Three or More Tables
      1. Joining a Table to Itself
      2. Joining the Same Table Twice
    7. Wrap-Up
    8. Test Your Knowledge
  5. 4. Working with Sets
    1. Set Theory Primer
    2. The union Operator
    3. The intersect Operator
    4. The except Operator
    5. Set Operation Rules
      1. Sorting Compound Query Results
      2. Set Operation Precedence
    6. Wrap-Up
    7. Test Your Knowledge
  6. 5. Creating and Modifying Data
    1. Data Types
      1. Character Data
      2. Numeric Data
      3. Temporal Data
      4. Other Data Types
    2. Creating Tables
    3. Populating and Modifying Tables
      1. Deleting Data
      2. Modifying Data
      3. Merging Data
    4. Wrap-Up
    5. Test Your Knowledge
  7. 6. Data Generation, Conversion, and Manipulation
    1. Working with Character Data
      1. String Generation and Manipulation
      2. String Searching and Extracting
    2. Working with Numeric Data
      1. Numeric Functions
      2. Numeric Conversion
      3. Number Generation
    3. Working with Temporal Data
      1. Date and Timestamp Generation
      2. Manipulating Dates and Timestamps
      3. Date Conversion
    4. Wrap-Up
    5. Test Your Knowledge
  8. 7. Grouping and Aggregates
    1. Grouping Concepts
    2. Aggregate Functions
      1. count() Function
      2. min(), max(), avg(), and sum() Functions
      3. listagg() Function
    3. Generating Groups
      1. Multicolumn Grouping
      2. Grouping Using Expressions
      3. Generating Rollups
    4. Filtering on Grouped Data
    5. Filtering with Snowsight
    6. Wrap-Up
    7. Test Your Knowledge
  9. 8. Subqueries
    1. Subqueries Defined
    2. Subquery Types
      1. Uncorrelated Subqueries
      2. Correlated Subqueries
    3. Subqueries as Data Sources
      1. Subqueries in the from Clause
      2. Common Table Expressions
    4. Wrap-Up
    5. Test Your Knowledge
  10. 9. From Clause Revisited
    1. Hierarchical Queries
    2. Time Travel
    3. Pivot Queries
    4. Random Sampling
    5. Full Outer Joins
    6. Lateral Joins
    7. Table Literals
    8. Wrap-Up
    9. Test Your Knowledge
  11. 10. Conditional Logic
    1. What Is Conditional Logic?
    2. Types of Case Expressions
      1. Searched Case Expressions
      2. Simple Case Expressions
    3. Uses for Case Expressions
      1. Pivot Operations
      2. Checking for Existence
      3. Conditional Updates
    4. Functions for Conditional Logic
      1. iff() Function
      2. ifnull() and nvl() Functions
      3. decode() Function
    5. Wrap-Up
    6. Test Your Knowledge
  12. 11. Transactions
    1. What Is a Transaction?
    2. Explicit and Implicit Transactions
    3. Related Topics
      1. Finding Open Transactions
      2. Isolation Levels
      3. Locking
      4. Transactions and Stored Procedures
    4. Wrap-Up
    5. Test Your Knowledge
  13. 12. Views
    1. What Is a View?
      1. Creating Views
      2. Using Views
    2. Why Use Views?
      1. Data Security
      2. Data Aggregation
      3. Hiding Complexity
    3. Considerations When Using Views
    4. Wrap-Up
    5. Test Your Knowledge
  14. 13. Metadata
    1. information_schema
    2. Working with Metadata
      1. Schema Discovery
      2. Deployment Verification
      3. Generating Administration Scripts
    3. get_ddl() Function
    4. account_usage
    5. Wrap-Up
    6. Test Your Knowledge
  15. 14. Window Functions
    1. Windowing Concepts
      1. Data Windows
      2. Partitioning and Sorting
    2. Ranking
      1. Ranking Functions
      2. Top/Bottom/Nth Ranking
      3. Qualify Clause
    3. Reporting Functions
    4. Positional Windows
    5. Other Window Functions
    6. Wrap-Up
    7. Test Your Knowledge
  16. 15. Snowflake Scripting Language
    1. A Little Background
    2. Scripting Blocks
    3. Scripting Statements
      1. Value Assignment
      2. if
      3. case
      4. Cursors
      5. Loops
      6. Exceptions
    4. Wrap-Up
    5. Test Your Knowledge
  17. 16. Building Stored Procedures
    1. Why Use Stored Procedures?
    2. Turning a Script into a Stored Procedure
    3. Stored Procedure Execution
    4. Stored Procedures in Action
    5. Returning Result Sets
    6. Dynamic SQL
    7. Wrap-Up
    8. Test Your Knowledge
  18. 17. Table Functions
    1. User-Defined Functions
    2. What Is a Table Function?
    3. Writing Your Own Table Functions
    4. Using Built-In Table Functions
      1. Data Generation
      2. Flattening Rows
      3. Finding and Retrieving Query Results
    5. Wrap-Up
    6. Test Your Knowledge
  19. 18. Semistructured Data
    1. Generating JSON from Relational Data
    2. Storing JSON Documents
    3. Querying JSON Documents
    4. Wrap-Up
    5. Test Your Knowledge
  20. A. Sample Database
  21. B. Solutions to Exercises
    1. Chapter 1
      1. Solution to Exercise 1-1
      2. Solution to Exercise 1-2
      3. Solution to Exercise 1-3
      4. Solution to Exercise 1-4
    2. Chapter 2
      1. Solution to Exercise 2-1
      2. Solution to Exercise 2-2
      3. Solution to Exercise 2-3
      4. Solution to Exercise 2-4
    3. Chapter 3
      1. Solution to Exercise 3-1
      2. Solution to Exercise 3-2
      3. Solution to Exercise 3-3
      4. Solution to Exercise 3-4
    4. Chapter 4
      1. Solution to Exercise 4-1
      2. Solution to Exercise 4-2
      3. Solution to Exercise 4-3
      4. Solution to Exercise 4-4
    5. Chapter 5
      1. Solution to Exercise 5-1
      2. Solution to Exercise 5-2
      3. Solution to Exercise 5-3
      4. Solution to Exercise 5-4
    6. Chapter 6
      1. Solution to Exercise 6-1
      2. Solution to Exercise 6-2
      3. Solution to Exercise 6-3
      4. Solution to Exercise 6-4
    7. Chapter 7
      1. Solution to Exercise 7-1
      2. Solution to Exercise 7-2
      3. Solution to Exercise 7-3
      4. Solution to Exercise 7-4
    8. Chapter 8
      1. Solution to Exercise 8-1
      2. Solution to Exercise 8-2
      3. Solution to Exercise 8-3
      4. Solution to Exercise 8-4
    9. Chapter 9
      1. Solution to Exercise 9-1
      2. Solution to Exercise 9-2
      3. Solution to Exercise 9-3
    10. Chapter 10
      1. Solution to Exercise 10-1
      2. Solution to Exercise 10-2
      3. Solution to Exercise 10-3
    11. Chapter 11
      1. Solution to Exercise 11-1
    12. Chapter 12
      1. Solution to Exercise 12-1
      2. Solution to Exercise 12-2
    13. Chapter 13
      1. Solution to Exercise 13-1
      2. Solution to Exercise 13-2
    14. Chapter 14
      1. Solution to Exercise 14-1
      2. Solution to Exercise 14-2
      3. Solution to Exercise 14-3
      4. Solution to Exercise 14-4
    15. Chapter 15
      1. Solution to Exercise 15-1
      2. Solution to Exercise 15-2
      3. Solution to Exercise 15-3
      4. Solution to Exercise 15-4
    16. Chapter 16
      1. Solution to Exercise 16-1
      2. Solution to Exercise 16-2
    17. Chapter 17
      1. Solution to Exercise 17-1
      2. Solution to Exercise 17-2
      3. Solution to Exercise 17-3
      4. Solution to Exercise 17-4
    18. Chapter 18
      1. Solution to Exercise 18-1
      2. Solution to Exercise 18-2
      3. Solution to Exercise 18-3
  22. Index
  23. About the Author

Product information

  • Title: Learning Snowflake SQL and Scripting
  • Author(s): Alan Beaulieu
  • Release date: October 2023
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098140328