Teaching a Snowflake SQL Class Remotely this Week.
Over the next two days, I am teaching Snowflake SQL remotely to Ivy League students. I have taught close to 1,000 SQL classes over the past 30 years, and I have written over 75 books covering SQL on every database platform. I have never seen SQL so robust as I have with Snowflake. Almost every SQL command from every database works on Snowflake. What a brilliant idea to make migrating to Snowflake so easy. Check out the outline below and be prepared to be amazed.
I have also been working with customers to move and migrate data to the cloud. A major insurance provider is moving thousands of tables from Teradata, Oracle, and Netezza to Snowflake, Redshift, and MySQL Aurora. We have put 15 years of development into allowing users and ETL teams to move data using our Nexus Server, which sits on the cloud, and our Nexus Pro desktops, which allow users to create and schedule data movement and migration on the Nexus Server.
The majority of companies are currently moving from Netezza. The Nexus Pro and Nexus Server allow automation of migration from Netezza to Snowflake, Yellowbrick, Teradata, Oracle, SQL Server, DB2, Redshift, Hadoop, SAP Hana, MySQL, PostgreSQL, and Greenplum.
Below is the Snowflake SQL Table of Contents
Snowflake SQL Table of Contents
Chapter 1 – Basic SQL Functions 1
Setting Your Default Database and Schema. 3
SELECT * (All Columns) in a Table. 4
SELECT Specific Columns in a Table. 5
Commas in the Front or Back?. 6
Place your Commas in front for better Debugging Capabilities 7
Sort the Data with the ORDER BY Keyword. 8
Use a Column Name or Number in an ORDER BY Statement 9
Two Examples of ORDER BY using Different Techniques. 10
Changing the ORDER BY to Descending Order 11
NULL Values sort Last in Ascending Mode (Default) 12
Using the Nulls First Command. 13
NULL Values sort First in Descending Mode (DESC) 14
Using the Nulls Last Command. 15
Multiple Sort Keys using Names vs. Numbers. 17
Sorts are Alphabetical, NOT Logical 18
Using A Valued CASE Statement to Sort Logically. 19
Using A Searched CASE Statement to Sort Logically. 20
Quiz – Can you Add a Minor Sort?. 21
Answer – Can you Add a Minor Sort?. 22
Using Decode to Sort Logically. 23
Quiz – Can you Add Two Minor Sorts Using Decode?. 24
Answer – Can you Add Two Minor Sorts Using Decode?. 25
How to ALIAS a Column Name. 26
A Missing Comma can by Mistake become an Alias. 27
Comments using Double Dashes are Single Line Comments 28
Comments for Multi-Lines as Double Dashes per Line. 30
Comments are a Great Technique for Finding SQL Errors 31
Popular Snowflake Functions 32
Move Data to the Snowflake Effortlessly. 33
Move Data to the Cloud Effortlessly. 34
Chapter 2 – The WHERE Clause. 36
The WHERE Clause limits Returning Rows 37
The WHERE Clause Needs Single-Quotes for Character Data. 38
Using a Column ALIAS in the WHERE Clause. 39
Numbers Don’t Need Single or Double Quotes 40
Searching for NULL Values Using Equality Returns Nothing. 41
Use IS NULL or IS NOT NULL when dealing with NULLs. 42
Using Greater Than OR Equal To (>=) 43
WHY OR must utilize the Column Name Each Time. 48
Troubleshooting Character Data. 49
Using Different Columns in an AND Statement 50
Quiz – How many rows will return?. 51
Answer to Quiz – How many rows will return?. 52
What is the Order of Precedence?. 53
Using Parentheses to change the Order of Precedence. 54
Using an IN List in place of OR.. 55
The IN List is an Excellent Technique. 56
IN List vs. OR brings the same Results 57
The IN List Can Use Character Data. 58
Null Values in a NOT IN List Return No Rows. 60
A Technique for Handling Nulls with a NOT IN List 61
The BETWEEN Statement is Inclusive. 62
The NOT BETWEEN Statement is also Inclusive. 63
The BETWEEN Statement Works for Character Data. 64
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’ 65
LIKE command Underscore is Wildcard for one Character 66
Using Upper and Lower to Handle Case Issues. 67
Using ILIKE Handle Case Issues. 68
Finding Anyone Who Name End in ‘Y’ 69
Escape Character in the LIKE Command changes Wildcards 70
Escape Characters Turn off Wildcards in the LIKE Command. 71
Chapter 3 – Distinct, Group By, Top, and Pivot 73
Quiz – How many rows come back from the Distinct?. 76
Answer – How many rows come back from the Distinct?. 77
TOP Command is brilliant when ORDER BY is Used! 80
Quiz – You calculate the Answer Set in your Mind. 87
Answer – You calculate the Answer Set in your Mind. 88
Quiz 2 – You calculate the Answer Set in your Mind. 89
Answer Quiz 2 – You calculate the Answer Set in your Mind. 90
Quiz – How many rows come back?. 92
Answer – How many rows come back?. 93
Troubleshooting Aggregates. 94
GROUP BY when Aggregates and Normal Columns Mix. 95
GROUP BY Delivers one row per Group. 96
GROUP BY Dept_No or GROUP BY 1 the same thing. 97
Limiting Rows and Improving Performance with WHERE. 98
WHERE Clause in Aggregation limits unneeded Calculations 99
Keyword HAVING tests Aggregates after they are Totaled. 100
Keyword HAVING is like an Extra WHERE Clause for Totals. 101
Three types of Advanced Grouping. 102
GROUP BY Grouping Sets Answer Set 104
Quiz – GROUP BY GROUPING SETS Challenge. 109
Answer To Quiz – GROUP BY GROUPING SETS Challenge. 110
Chapter 5 – Join Functions 113
A two-table join using Non-ANSI Syntax. 114
A two-table join using Non-ANSI Syntax with Table Alias. 115
You Can Fully Qualify All Columns 116
A two-table join using ANSI Syntax. 117
Both Queries have the same Results and Performance. 118
Quiz – Can You Finish the Join Syntax?. 119
Answer to Quiz – Can You Finish the Join Syntax?. 120
Quiz – Can You Find the Error?. 121
Answer to Quiz – Can You Find the Error?. 122
Super Quiz – Can You Find the Difficult Error?. 123
Answer to Super Quiz – Can You Find the Difficult Error?. 124
Quiz – Which rows from both tables Won’t Return?. 125
Answer to Quiz – Which rows from both tables Won’t Return?. 126
Right Outer Join Example and Results. 131
RIGHT OUTER JOIN Using (+) 132
Which Tables are the Left, and which are the Right?. 135
Answer – Which Tables are the Left and which are the Right?. 136
INNER JOIN with Additional AND Clause. 137
ANSI INNER JOIN with Additional AND Clause. 138
ANSI INNER JOIN with Additional WHERE Clause. 139
OUTER JOIN with Additional WHERE Clause. 140
OUTER JOIN with Additional AND Clause. 141
The DREADED Product Join Results 143
Cartesian Product Join with Traditional Syntax. 144
Cartesian Product Join with ANSI Syntax. 145
An Associative Table is a Bridge that Joins Two Tables. 149
Quiz – Can you Write the 3-Table Join with Traditional Join Syntax?. 150
Answer to Quiz – Can you Write the 3-Table Join with Traditional Join Syntax?. 151
Quiz – Can you Write the 3-Table Join Using ANSI Syntax?. 152
Answer – Can you Write the 3-Table Join to ANSI Syntax?. 153
Quiz – Can you Place the ON Clauses at the End?. 154
Answer – Can you Place the ON Clauses at the End?. 155
The 5-Table Join – Logical Insurance Model 156
Quiz – Write a Five Table Join Using ANSI Syntax. 157
Answer – Write a Five Table Join Using ANSI Syntax. 158
Quiz – Write a Five Table Join Using Traditional Syntax. 159
Answer – Write a Five Table Join Using ANSI Syntax. 160
Quiz –Re-Write this putting the ON clauses at the END.. 161
Answer –Re-Write this putting the ON clauses at the END.. 162
Chapter 6 – Date Functions 165
Current_Date, Current_Time, and Current_Timestamp. 167
Current_Time vs. LocalTime With Precision. 168
Local_Time and Local_Timestamp With Precision. 169
Add or Subtract Days from a date. 170
Using the ADD_MONTHS Command to Add 1 Year 172
Using the ADD_MONTHS Command to Add 5 Years. 173
Formatting a Date Using the To_Char Command. 174
Formatting Date and Time With To_Char 175
The To_Char command to format Dollar Signs. 176
The To_Char Command for Formatting Numbers. 177
EXTRACT from DATES and TIME. 179
EXTRACT from DATES and TIME Optional Syntax. 181
Another Option for Extracting Portions of Dates and Times. 182
Using Date_Part to Extract 183
Implied Extract of Day, Month and Year using to_char 184
The Date_Part Function Using a Date. 185
Great Date Functions to Know.. 186
DAYOFWEEK and a CASE Statement 187
Year and Days for the First/Last Weeks of the Year 188
First Day and Last Day Functions 189
Incrementing Date Values Using the Dateadd Function. 190
Incrementing Time Values Using Dateadd. 191
The Datediff Function on Column Data. 193
Calculating Days Between using the DATEDIFF Function. 194
Changing the Date to a Timestamp. 195
Find the First Day of the Current Month. 196
Using Day, Month, and Year Intervals. 198
Quiz – How did the Row_Number Reset?. 204
Answer – How did the Row_Number Reset?. 205
Find the Top Two Students Per Class_Code using Qualify. 206
Find the Top Two Students using a Derived Table. 207
Getting RANK to Sort in DESC Order 209
RANK () OVER and PARTITION BY.. 210
RANK() OVER and a Qualify Statement 211
RANK() OVER and a WITH Derived Table. 212
DENSE_RANK() OVER and PARTITION BY.. 214
DENSE_RANK() OVER and QUALIFY.. 215
PERCENT_RANK () OVER with 14 rows in Calculation. 216
PERCENT_RANK () OVER with 21 rows in Calculation. 217
PERCENT_RANK() OVER and PARTITION BY.. 218
CSUM – The Sort Explained. 220
CSUM – Rows Unbounded Preceding Explained. 221
The CSUM – Making Sense of the Data. 222
CSUM – The Major and Minor Sort Key(s) 223
The ANSI OLAP – Reset with a PARTITION BY Statement 224
Totals and Subtotals through Partition BY.. 225
Moving SUM every 3-rows Vs. a Continuous Average. 227
Partition By Resets the Calculations. 228
How the Moving Average Calculates. 230
How the Sort works for Moving Average (MAVG) 231
Quiz – How is that Total Calculated?. 232
Answer to Quiz – How is that Total Calculated?. 233
Quiz – How is that 4th Row Calculated?. 234
Answer to Quiz – How is that 4th Row Calculated?. 235
Moving Average every 3-rows Vs. a Continuous Average. 236
Partition BY Resets an ANSI OLAP. 237
Moving Difference using ANSI Syntax. 238
Moving Difference using ANSI Syntax with Partition By. 239
Finding a Value of a Column in the Next Row with MIN.. 240
Finding a Value of a Column in the Next Row with PARTITION BY.. 241
Finding Multiple Values of a Column in Upcoming Rows 242
Finding The Next Date using MAX.. 243
COUNT OVER for a Sequential Number 244
COUNT OVER using ROWS UNBOUNDED PRECEDING.. 245
MAX OVER with PARTITION BY Reset 247
The MIN OVER Command with PARTITION BY.. 249
Finding Gaps Between Dates 250
The CSUM For Each Product_Id For The First 3 Days. 251
FIRST_VALUE with Partitioning. 254
FIRST_VALUE Combined with Row_Number and Qualify. 255
FIRST_VALUE and Row_Number with a Derived Table. 256
Using LEAD with a PARTITION Statement 258
Using LEAD With an Offset of 2. 259
Using LEAD With an Offset of 2 and a PARTITION.. 260
Using LAG with a PARTITION Statement 262
Using LAG With an Offset of 2. 263
Using LAG With an Offset of 2 and a PARTITION.. 264
CUME_DIST With a Partition. 266
CURRENT ROW AND UNBOUNDED FOLLOWING.. 267
Different Windowing Options 268
Another Example of LISTAGG.. 270
LISTAGG With a Pipe-Separated List 271
LISTAGG With a Comma-Separated List in Groups 272
MEDIAN with Partitioning and a WHERE Clause. 275
Using Quantiles (Partitions of Four) 283
NTILE Using a Value of 10. 284
NTH_VALUE Function and Syntax. 286
NTH_VALUE With Partition and Ignore Nulls. 290
PERCENTILE_CONT Function Description and Syntax. 291
Final Result Information About PERCENTILE_CONT. 292
PERCENTILE_CONT Function Arguments 293
PERCENTILE_CONT Example with Percentage Change. 295
PERCENTILE_CONT With PARTITION Example. 296
PERCENTILE_CONT With PARTITION and (0.4) 297
PERCENTILE_DISC Function Description and Syntax. 298
PERCENTILE_DISC Function Arguments. 299
PERCENTILE_DISC Example with Percentage Change. 301
PERCENTILE_DISC With PARTITION Example. 302
PERCENTILE_DISC With PARTITION and (0.4) 303
RATIO_TO_REPORT Example with Partitioning. 306
Chapter 8 – Temporary Tables. 309
Aliasing the Column Names in the Derived Table. 312
CREATING A Derived Table using the WITH Command. 313
Derived Query Examples with Three Different Techniques 314
Most Derived Tables Are Used To Join To Other Tables 315
The Three Components of a Derived Table. 316
Visualize This Derived Table. 317
Our Join Example With the WITH Syntax. 318
An Example of Two Derived Tables in a Single Query. 319
MULTIPLE Derived Tables using the WITH Command. 320
WITH RECURSIVE Derived Table Hierarchy. 321
WITH RECURSIVE Derived Table Query. 322
WITH RECURSIVE Derived Table Definition. 323
WITH RECURSIVE Derived Table Seeding. 324
WITH RECURSIVE Derived Table Looping. 325
WITH RECURSIVE Derived Table Looping in Slow Motion. 326
WITH RECURSIVE Derived Table Looping Continued. 327
WITH RECURSIVE Derived Table Ends the Looping. 328
WITH RECURSIVE Derived Table Final Report 329
Creating a Temporary Table. 330
Creating a Temporary Table using a CTAS. 331
Dropping a Temporary Table. 332
Chapter 9 – Sub-query Functions. 335
An IN List is much like a Subquery. 336
An IN List Never has Duplicates – Just like a Subquery. 337
An IN List Ignores Duplicates. 338
How a Basic Subquery Works 340
These are Equivalent Queries. 341
The Final Answer Set from the Subquery. 342
Quiz- Answer the Difficult Question. 343
Answer to Quiz- Answer the Difficult Question. 344
Should you use a Subquery of a Join?. 345
Quiz – Write the Subquery. 346
Answer to Quiz – Write the Subquery. 347
Quiz – Write the More Difficult Subquery. 348
Answer to Quiz – Write the More Difficult Subquery. 349
Quiz – Write the Extreme Subquery. 350
Answer To Quiz – Write the Extreme Subquery. 351
Quiz – Write the Subquery with an Aggregate. 352
Answer to Quiz – Write the Subquery with an Aggregate. 353
Quiz – Write the Correlated Subquery. 354
Answer to Quiz – Write the Correlated Subquery. 355
The Basics of a Correlated Subquery. 356
The Top Query always runs first in a Correlated Subquery. 357
Correlated Subquery Example vs. a Join with a Derived Table. 358
Quiz- A Second Chance to Write a Correlated Subquery. 359
Answer – A Second Chance to Write a Correlated Subquery. 360
Quiz- A Third Chance to Write a Correlated Subquery. 361
Answer – A Third Chance to Write a Correlated Subquery. 362
Quiz- Last Chance to Write a Correlated Subquery. 363
Answer – Last Chance to Write a Correlated Subquery. 364
Quiz – Write the Extreme Correlated Subquery. 365
Answer – Write the Extreme Correlated Subquery. 366
NOT IN Subquery Returns Nothing when NULLs are Present 367
Fixing a NOT IN Subquery with Null Values. 368
Quiz- Write the NOT Subquery. 369
Answer to Quiz- Write the NOT Subquery. 370
Quiz – Write the Subquery using a WHERE Clause. 371
Answer – Write the Subquery using a WHERE Clause. 372
Quiz- Write the Subquery with Two Parameters. 373
Answer to Quiz- Write the Subquery with Two Parameters 374
How the Double Parameter Subquery Works 375
More on how the Double Parameter Subquery Works 376
Quiz – Write the Triple Subquery. 377
Answer to Quiz – Write the Triple Subquery. 378
IN is equivalent to =ANY.. 379
How a Correlated Exists matches up. 381
The Correlated NOT Exists. 382
The Length Command Counts Characters 386
LENGTH Works on Fixed Length Columns 387
The TRIM Command trims both Leading and Trailing Spaces 389
The RTRIM and LTRIM Command trims Spaces. 390
Concatenation and SUBSTRING.. 392
Four Concatenations Together 393
The SUBSTR and SUBSTRING Command. 395
How SUBSTR Works with NO ENDING POSITION.. 396
Using SUBSTR and LENGTH Together 397
The LEFT and RIGHT Functions 398
The POSITION Command finds a Letters Position. 399
The POSITION Command is brilliant with SUBSTR. 400
CHARINDEX Finds a Letter(s) Position in a String. 401
The CHARINDEX Command is brilliant with SUBSTRING.. 402
The CHARINDEX Command Using a Literal 403
SOUNDEX Function to Find a Sound. 407
The Reverse String Function. 411
Chapter 11 – Interrogating the Data. 414
Numeric Manipulation Functions 415
Ceiling Gets the Smallest Integer Not Smaller Than X.. 417
Floor Finds the Largest Integer Not Greater Than X.. 418
The Round Function and Precision. 419
Quiz – Fill in the Answers for the NULLIF Command. 420
Answer – Fill in the Answers for the NULLIF Command. 421
COALESCE is Equivalent to this CASE Statement 423
A Rounding Example Using CAST. 424
CAST will Round Values up or Down. 425
Valued Case vs. Searched Case. 426
Combining Searched Case and Valued Case. 427
A Trick for getting a Horizontal Case. 432
Put a CASE in the ORDER BY.. 433
Using Decode to Sort Logically. 434
Chapter 12 – View Functions. 436
The Fundamentals of Views. 437
Creating a Simple View to Restrict Sensitive Columns 438
Creating a Simple View to Restrict Rows 439
Creating a View to Join Tables Together 440
Join Views Allow Users to Merely Select Columns 441
Sometimes we Create Views for Formatting. 442
The Exception to the ORDER BY Rule inside a View.. 445
Derived Columns in a View Should Contain a Column Alias. 446
The Standard Way Most Aliasing is Done. 447
Another Way to Alias Columns in a View CREATE. 448
What Happens When a View Column gets Aliased Twice?. 449
Aggregates on View Aggregates. 450
Chapter 13 – UNION Set Operator 452
INTERSECT Explained Logically. 454
INTERSECT Explained Logically. 455
UNION Explained Logically. 456
UNION Explained Logically. 457
UNION ALL Explained Logically. 458
UNION ALL Explained Logically. 459
EXCEPT Explained Logically. 460
EXCEPT Explained Logically. 461
Minus Explained Logically. 462
Minus Explained Logically. 463
Answer Testing Your Knowledge. 465
Answer Testing Your Knowledge. 467
An Equal Number of Columns in both SELECT Lists. 468
Columns in the SELECT list should be from the same Domain. 469
The Top Query handles all Aliases. 470
The Bottom Query does the ORDER BY.. 471
Great Trick: Place your Set Operator in a Derived Table. 472
Using UNION ALL and Literals. 474
A Great Example of how EXCEPT works 475
USING Multiple SET Operators in a Single Request 476
Changing the Order of Precedence with Parentheses. 477
Using UNION ALL for speed in Merging Data Sets. 478
Using UNION to be same as GROUP BY GROUPING SETS. 479
Chapter 14 – Creating Tables. 482
Show Databases and Table DDL Commands 483
Creating A Table in Snowflake. 486
Creating Temporary and Transient Tables. 487
Data Types for Numeric, String, and Binary. 489
Data Types for Date, Time, and Unstructured. 490
Creating Tables with a Clustering Key. 491
Joining Tables Can Have the Same Clustering Keys for Speed. 492
Creating Tables with a Primary Key/Foreign Key Relationship. 493
A Table with a NOT NULL Constraint 494
CREATE a Temporary TABLE using LIKE. 496
CREATE TABLE AS (CTAS) Populates the Table With Data. 497
CREATE TABLE AS (CTAS) Can Choose Certain Columns 498
CREATE a Temporary Table AS (CTAS) 499
CREATE a Temporary Table AS (CTAS) Using a Join. 500
Chapter 15 – Data Manipulation Language (DML) 503
INSERT Example with Multiple Rows 506
Inserting Null Values into a Table. 507
INSERT/SELECT to Build a Data Mart 509
Subquery UPDATE Command Syntax. 511
Example of Subquery UPDATE Command. 512
Chapter 16 – Statistical Aggregate Functions 515
Another CORR Example so you can Compare. 531
Another COVAR_POP Example so you can Compare. 534
The REGR_INTERCEPT Function. 535
Another REGR_INTERCEPT Example so you can Compare. 537
Another REGR_SLOPE Example so you can Compare. 540
Another REGR_AVGX Example so you can Compare. 543
Another REGR_AVGY Example so you can Compare. 546
Tom Coffing, better known as Tera-Tom, is the founder of Coffing Data Warehousing, where he has been CEO for the past 20 years. Tom has written over 50 books on all aspects of Teradata, Netezza, Kognitio, Redshift, ParAccel, Vertica, SQL Server, and Greenplum. Tom has taught over 1,000 Teradata classes in places such as India, Africa, Europe, China, Malaysia, and throughout North America.
Tom is also the owner and designer of the Nexus Query Chameleon, the most sophisticated enterprise query tool in the industry. The Nexus works on all platforms, including Hadoop, converts table structures between all systems, and allows companies to load their Erwin logical model inside Nexus. The Nexus guides users like GPS. Users point and click on any table or view from any system, and the Nexus guides them to what joins to what. As users choose the columns they want on their report, the SQL builds automatically.
In High School, Tom was the first athlete from his school to ever place at state. He was selected by his school to represent them at Buckeye Boys State, and Tom’s induction into the first class of the Lakota High School Hall of Fame gives him great pride.
At the University of Arizona and University of Nevada Las Vegas, Tom was a two-time All-American wrestler, Sophomore Athlete of the year, and a two-time winner of the 1980 Olympic wrestling trials. Tom graduated with a bachelor’s degree in Speech Communications.
After college, Tom became a state and national champion speech winner for Toastmasters and won two orchid awards as an actor. Tom is the proud father of three wonderful children and married his wife, Leona, 38 years ago.
You can contact Tom at 513 300-0341 or Tom.Coffing@CoffingDW.com.
If you are interested in an SQL class on Snowflake or for any other database, please contact me.
Thanks,
Tom
Tom Coffing, better known as Tera-Tom, is the founder of Coffing Data Warehousing where he has been CEO for the past 25 years. Tom has written over 75 books on all aspects of Teradata, Netezza, Yellowbrick, Snowflake, Redshift, Aurora, Vertica, SQL Server, and Greenplum. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus Product Line.