Learning Snowflake SQL and Scripting - Helion

ebook

Autor: Alan Beaulieu
ISBN: 9781098140281
stron: 400, Format: ebook
Data wydania: 2023-10-03
Księgarnia: Helion

Cena książki: 271,15 zł (poprzednio: 319,00 zł)
Oszczędzasz: 15% (-47,85 zł)

Osoby, które kupiły tę książkę, wybierały także »

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

Osoby które kupowały "Learning Snowflake SQL and Scripting", wybierały także:

Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
DevOps w praktyce. Kurs video. Jenkins, Ansible, Terraform i Docker 190,00 zł, (39,90 zł -79%)
Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)

Spis treści

Learning Snowflake SQL and Scripting eBook -- spis treści

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
- OReilly 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