Google BiqQuery Details
For pricing and availability
Contact: Tom Coffing
Email: Tom.Coffing@CoffingDW.com
Phone: 513 3000341
Google BigQuery SQL
Description: Students will learn SQL starting at the most basic level and building to intermediate and advanced levels in this course. There are over 500 examples of SQL, and the class is 90% handson training. The students will use the SQL_Class database with 18 tables representing the tables in the book.
Reference Guide: The book is an excellent book for teaching and makes for an excellent reference guide to help on the job.
Objective: After this course, students will have deep knowledge and understanding of Google BigQuery SQL and how to write it.
Description: In this course, students will learn SQL starting at the most basic level and going to the most advanced level.
Audience: This course is for anyone who desires to learn BigQuery SQL from beginners to an advanced audience.
Prerequisites: None
Duration: 3Days
Topics:

Basic SQL Functions

The WHERE Clause

Distinct Vs. Group By

Aggregation

Joins

Date and Time Functions

Format Functions

Analytics and Window Functions

Temporary Tables

Subqueries

Strings

Interrogating the Data

Views

Set Operators

Creating Tables

Data Manipulation Language (DML)

Math Functions

Statistical Aggregate Functions
Google BigQuery SQL Course Outline
Chapter 1  The Fundamentals of SQL
SELECT * Returns All Columns from the Table
SELECT Specific Columns in a Table
Commas in the Front or Back?
Place your Commas in front for better Debugging Capabilities
Sort the ResultSet with the ORDER BY Keyword
A Use a Column Name or Number in an ORDER BY Statement
Two Examples of ORDER BY using Different Techniques
Changing the ORDER BY to Descending Order
Null Values Sort Last in Ascending Mode (Default)
Null Values sort First in Descending Mode (DESC)
Order By with Nulls Last
Order By with Nulls First
Major Sort vs. Minor Sort
Multiple Sort Keys using Names vs. Numbers
You can ORDER BY using a Mix of names and Numbers
Sorts are Alphabetical, NOT Logical
Using A Valued CASE Statement to Sort Logically
Using A Searched CASE Statement to Sort Logically
Quiz – Can you Add a Minor Sort?
Answer – Can you Add a Minor Sort?
How to ALIAS a Column Name
How to Get Capital Letters in a Report Header
Using Spaces in an ALIAS Clauses Errors
Using an Alias in the WHERE and ORDER BY Clause
A Missing Comma can become an Alias by Mistake
Limit
Limit and Offset
Comments using Double Dashes are Single Line Comments
Comments on Multiple Lines
Multiple Line Comments using Double Dashes Per Line
Comments are a Great Technique for Finding SQL Errors
Move Data to the Google BigQuery Effortlessly
Chapter 2 – The WHERE Clause
The WHERE Clause limits Returning Rows
SingleQuotes or DoubleQuotes Are Used for Character Data
Reminder: Using Spaces in an ALIAS Clauses Errors
Using a Column ALIAS in the WHERE Clause
Numbers Do Not Need Single Quotes
Searching for null Values Using Equality Returns Nothing
Use IS NULL to Check for Null Values
Use IS NOT NULL for Interrogating NULL Values
Using Greater Than Or Equal To (>=)
AND in the WHERE Clause
Troubleshooting AND
OR in the WHERE Clause
Troubleshooting OR
Why OR Must Utilize the Column Name Each Time
Troubleshooting Character Data
Troubleshooting Character Data Continued
Quiz – How many rows will return?
Answer to Quiz – How many rows will return?
What is the Order of Precedence?
Using Parentheses to change the Order of Precedence
Using an IN List in Place of OR
The IN List is an Excellent Technique
IN List vs. OR Brings the Same Results
The IN List Can Use Character Data
Using a NOT IN List
Null Values in a NOT IN List Return No Rows
A Technique for Handling Nulls with a NOT IN List
Technique 2 for Handling Nulls with a NOT IN List
The BETWEEN Statement is Inclusive
The NOT BETWEEN Statement is also Inclusive
The BETWEEN Statement Works for Character Data
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
Using LIKE for all Cases with Lower and Upper
LIKE command Underscore is Wildcard for one Character
Finding anyone Whose name End in 'Y'
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command
Chapter 3  Distinct Vs. Group By
The Distinct Command
Distinct vs. GROUP BY
Quiz – How many rows come back from the Distinct?
Answer – How many rows come back from the Distinct?
Chapter 4  Aggregation
Quiz – Calculate the Answer Set in your Mind
Answer – Calculate the Answer Set in your Mind
Quiz 2 – Calculate the Answer Set in your Mind
Answer  Calculate the Answer Set in your Mind
There are Five Aggregates
Quiz – How many rows come back?
Answer – How many rows come back?
Casting a Data Type
Troubleshooting Aggregates
GROUP BY when Aggregates and Normal Columns Mix
GROUP BY Delivers One Row Per Group
GROUP BY dept_no or GROUP BY 1 the same thing
Limiting Rows and Improving Performance with WHERE
WHERE Clause in Aggregation limits unneeded Calculations
Keyword HAVING tests Aggregates after they are Totaled
Chapter 5 – Joining Tables
NexusCore Servers  Control Network and Data Movement
A TwoTable Join Using Traditional Syntax
A twotable join using NonANSI Syntax with Table Alias
You Can Fully Qualify All Columns
A twotable join using ANSI Syntax
Both Queries have the same Results and Performance
Quiz – Can You Finish the Join Syntax?
Answer– Can You Finish the Join Syntax?
Quiz – Can You Find the Error?
Answer– Can You Find the Error?
Super Quiz – Can You Find the Difficult Error?
Answer– Can You Find the Difficult Error?
Quiz – Which Rows from Both Tables Will Not Return?
Answer– Which Rows from Both Tables Will Not Return?
Left Outer Join
Left Outer Join Results
Right Outer Join
Right Outer Join Example and Results
Full Outer Join
Full Outer Join Results
Which Tables are Left, and Which are the Right?
Answer  Which Tables are the Left and Which are the Right?
INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional WHERE Clause
OUTER JOIN with Additional WHERE Clause
OUTER JOIN with Additional AND Clause
The DREADED Product Join
The DREADED Product Join Results
Cartesian Product Join with Traditional Syntax
Cartesian Product Join with ANSI Syntax
The CROSS JOIN
The CROSSJOIN Answer Set
The Self Join
The Self Join with ANSI Syntax
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the ThreeTable Join?
Answer – Can you Write the ThreeTable Join?
Quiz –Write the ThreeTable Join with ANSI Syntax?
Answer – Write the ThreeTable Join with ANSI Syntax?
Quiz – Can you Place the ON Clauses at the End?
Answer – Can you Place the ON Clauses at the End?
The FiveTable Join – Logical Insurance Model
Quiz  Write a Five Table Join Using ANSI Syntax
Answer  Write a Five Table Join Using ANSI Syntax
Quiz  Write a Five Table Join Using Traditional Syntax
Answer  Write a Five Table Join Using NonANSI Syntax
Quiz – ReWrite this putting the ON clauses at the END
Answer – ReWrite this putting the ON clauses at the END
Chapter 6  Date and Time
The Google BigQuery Tree of Nexus
Current_Date
Current_Date and Current_Timestamp
Add or Subtract From a Date
Add or Subtract Days From a Date
DATE
TIME
Support Elements for DATE Formatting
The EXTRACT Command
EXTRACT from DATES and TIME
Extract Options
Extract Time and Timestamp
STRING Timestamp
Another Datediff Example
DATE_TRUNC
TIME_TRUNC
TIMESTAMP_TRUNC
LAST_DAY
DATE_ADD
TIME_ADD
TIMESTAMP_ADD
DATE_SUB
TIME_SUB
TIMESTAMP_SUB
Clever Tricks for Month
Determining if the Current_Date is a Leap Year
Determining if the Current_Timestamp is a Leap Year
Chapter 7  Analytics
The Nexus Super Join Builder builds SQL Automatically
Row_Number
Quiz – How did the Row_Number Reset?
Answer – How did the Row_Number Reset?
Find the Top Two Students Per class_code
RANK
Dense_Rank
Getting RANK to Sort in DESC Order
RANK() OVER and PARTITION BY
RANK() OVER and a Derived Table
RANK() OVER and a WITH Derived Table
RANK vs. DENSE_RANK
DENSE_RANK() OVER and PARTITION BY
PERCENT_RANK() OVER with 14 rows in Calculation
PERCENT_RANK() OVER with 21 rows in Calculation
PERCENT_RANK() OVER and PARTITION BY
Cumulative Sum
Cumulative Sum – The Sort Explained
Cumulative Sum – Rows Unbounded Preceding Explained
Cumulative Sum – Making Sense of the Data
Cumulative Sum – Major and Minor Sort Keys
Reset with a PARTITION BY Statement
Totals and Subtotals through Partition By
Moving Sum
Moving SUM every 3rows Vs. a Continuous Average
Partition By Resets the Calculation
Moving Average
The Moving Window is Current Row and Preceding
How Moving Average Handles the Order By
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Quiz – How is that 4th Row Calculated?
Answer to Quiz – How is that 4th Row Calculated?
Moving Average every 3rows Vs. a Continuous Average
The Partition By Statement
Partition By Resets an ANSI OLAP
Moving Difference
Moving Difference with Partition By
Finding a Value of a Column in the Next Row with MIN
Finding a Next Row Value with MIN and PARTITION BY
Finding The Next Date using MAX
Finding Multiple Values of a Column in Upcoming Rows
COUNT OVER for a Sequential Number
COUNT OVER using ROWS UNBOUNDED PRECEDING
The MAX OVER Command
MAX OVER with PARTITION BY Reset
The MIN OVER Command
The MIN OVER Command with PARTITION B
Different Windowing Options
How Ntile Works
Ntile in DESC Mode
Ntile
Ntile with a Derived Table
Ntile Percentile
Another Ntile Example
Using Quantiles (Partitions of Four)
NTILE With a Partition
NTILE With a Partition and a Derived Table
Using FIRST_VALUE
FIRST_VALUE
Daily_Sales Minus FIRST_VALUE With Partitioning
FIRST_VALUE to Find the Delta
FIRST_VALUE After Sorting by the Highest Value
FIRST_VALUE with Partitioning
Using LAST_VALUE
First_Value Review
Last_Value Can Be Confusing
Last_Value Now Makes Sense
Last_Value With Partitioning
Using LEAD With an Offset of 2 and a PARTITION
Using LAG
Using LAG with a PARTITION Statement
Using Two LAG Statements
Using LAG With an Offset of 2
Using LAG With an Offset of 2 and a PARTITION
CUME_DIST
CUME_DIST With a Partition
CURRENT ROW AND UNBOUNDED FOLLOWING
Different Windowing Options
ANY_VALUE
Chapter 8 – Temporary Tables
Move an Entire Database to Google BigQuery
CREATING A Derived Table
Naming the Derived Table
CREATING A Derived Table using the WITH Command
Derived Query Examples with Two Different Techniques
Most Derived Tables Are Used To Join To Other Tables
The Three Components of a Derived Table
Visualize This Derived Table
Our Join Example Using The WITH Syntax
An Example of Two Derived Tables in a Single Query
An Example of Two Derived Tables Using WITH
Select Expressions
Select Expression Using UNION ALL
Another Select Expression Using UNION ALL
Chapter 9 – Subqueries
The Nexus Migrates Data To and From Every System
An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
An IN List Ignores Duplicates
The Subquery
The Three Steps of How a Basic Subquery Works
These are Equivalent Queries
The Final Answer Set from the Subquery
Quiz Answer the Difficult Question
Answer to Quiz Answer the Difficult Question
Should you use a Subquery or a Join?
Quiz  Write the Subquery
Answer to Quiz Write the Subquery
Quiz  Write the More Difficult Subquery
Answer to Quiz  Write the More Difficult Subquery
Quiz – Write the Extreme Subquery
Answer To Quiz – Write the Extreme Subquery
Quiz  Write the Subquery with an Aggregate
Answer to Quiz Write the Subquery with an Aggregate
Quiz Write the Correlated Subquery
Answer to Quiz Write the Correlated Subquery
The Basics of a Correlated Subquery
The Top Query always runs first in a Correlated Subquery
Correlated Subquery Example vs. a Join with a Derived Table
Quiz A Second Chance To Write a Correlated Subquery
Answer  A Second Chance to Write a Correlated Subquery
Quiz A Third Chance To Write a Correlated Subquery
Answer  A Third Chance to Write a Correlated Subquery
Quiz Last Chance To Write a Correlated Subquery
Quiz – Write the Extreme Correlated Subquery
Answer To Quiz – Write the Extreme Correlated Subquery
NOT IN Subquery Returns Nothing when Nulls are Present
Fixing a NOT IN Subquery with Null Values
Quiz Write the NOT Subquery
Answer to Quiz Write the NOT Subquery
Quiz  Write the Subquery using a WHERE Clause
Answer  Write the Subquery using a WHERE Clause
Quiz – Write the Triple Subquery
Answer to Quiz – Write the Triple Subquery
Using a Correlated Exists
How a Correlated Exists Matches Up
The Correlated NOT Exists
Chapter 10  Strings
Nexus Pivots Your Answer Sets
UPPER and lower Functions
The Length Command Counts Characters
LENGTH Works on Fixed Length Columns
The Char_Length Command Counts Characters
CHAR_LENGTH Works on Fixed Length Columns
CHAR_LENGTH and OCTET_LENGTH
The TRIM Command trims both Leading and Trailing Spaces
The RTRIM and LTRIM Command Trims Spaces
Concatenation
Concat or Pipe Symbols for Concatenation
The SUBSTR and SUBSTRING Commands
How SUBSTR Works with NO ENDING POSITION
How SUBSTR can Retrieve the Right Most Characters
Using SUBSTR and CHAR_LENGTH Together
The STRPOS Command finds a Letters Position
The STRPOS Command is Brilliant with SUBSTR
The STRPOS Command is brilliant with SUBSTRING
The STRPOS Command Using a Literal
LPAD and RPAD
The REPLACE Function
The STARTS_WITH Function
The ENDS_WITH Function
Initcap Function
Repeat Function
SPLIT Function
TRANSLATE Function
The ASCII Function
The UNICODE Function
The Reverse String Function
The RIGHT Function
The LEFT and RIGHT Functions
SOUNDEX Function to Find a Sound
Java Script Object Notation (JSON)
JSON – Getting the First and Second Entry in the List
JSON – Extract vs. Extract_Scalar
Regex
Regexp_Contains Examples +
Regexp_Contains Examples {n}
Regexp_Contains Examples {} and []
Regexp_Contains Example
Regexp_Contains Examples Dot and Anchors ^ $
Regexp_Contains Alternation
Regexp_Contains Repetition +
Regexp_Contains Repetition Amount Using Curly Braces
Regexp_Extract
Regexp_Replace
The REPLACE Function
Chapter 11  Interrogating the Data
Drag an Answer Set to Any System to Create a Table
Quiz – Fill in the Answers for the NULLIF Command
Answer – Fill in the Answers for the NULLIF Command
IFNULL
The COALESCE Command
COALESCE is Equivalent to this CASE Statement
IF
Valued Case vs. Searched Case
Combining Searched Case and Valued Case
A Trick for getting a Horizontal Case
Put a Valued CASE in the ORDER BY
Put a Searched CASE in the ORDER BY
Chapter 12  Views
Join Excel with Production Tables
The Fundamentals of Views
Creating a Simple View to Restrict Sensitive Columns
Creating a Simple View to Restrict Rows
Creating a View to Join Tables Together
Basic Rules for Views
How to Modify a View
The Exception to the ORDER BY Rule inside a View
Derived Columns in a View Should Contain a Column Alias
The Standard Way Most Aliasing is Done
Chapter 13 – Set Operators
When the Desktop and the Server Work as One
Set Operators
Rule 1: Equal Number of Columns in both SELECT Lists
Rule 2: Top Query is Responsible for all Aliasing
Rule 3: Bottom Query does the ORDER BY Statement
Intersect Challenge
Answer  Intersect Challenge
Using UNION ALL and Literals
Great Trick: Place your Set Operator in a Derived Table
A Great Example of how EXCEPT works
Changing the Order of Precedence with Parentheses
Chapter 14 – Creating Tables
Google BigQuery Data Types (1 of 3)
Google BigQuery Data Types (2 of 3)
Google BigQuery Data Types (3 of 3)
Creating a Basic Table
IF NOT EXISTS
CREATE OR REPLACE
Float64 vs. Numeric
Partitioned Table Options
Date Partitioned Table
Timestamp Partitioned Table by Hour
Timestamp Partitioned Table by Day
Timestamp Partitioned Table by Month
Timestamp Partitioned Table by Year
Timestamp Partitioned Table by Integer
Table Clustering
Chapter 15  Data Manipulation Language (DML)
INSERT Syntax # 1
INSERT Syntax # 2
INSERT Example with Multiple Rows
Inserting Null Values into a Table
INSERT/SELECT Command
INSERT/SELECT to Build a Data Mart
UPDATE Examples
Deleting Rows in a Table
Chapter 16  Statistical Aggregate Functions
The Stats Table
The STDDEV_POP Function
STDDEV
The STDDEV_SAMP Function
The VAR_POP Function
The VAR_SAMP Function
Variance
The CORR Function
The COVAR_POP Function
The COVAR_SAMP Function
ARRAY_AGG
ARRAY_AGG Examples
More ARRAY_AGG Examples
APPROX_COUNT_DISTINCT
Chapter 17  Mathematical Functions
Example Mathematical Functions
Numeric Manipulation Functions
ABS
ACOS
ASIN
Ceiling
Floor
COS
DIV
EXP
LN
LOG
MOD
POWER
ROUND
SIGN
SIN
SQRT
TRUNC