top of page

Google BiqQuery Details

For pricing and availability

Contact: Tom Coffing

Email: Tom.Coffing@CoffingDW.com

Phone: 513 300-0341

​

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% hands-on 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:  3-Days

 

 

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

Single-Quotes or Double-Quotes 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 Two-Table Join Using Traditional Syntax

A two-table join using Non-ANSI Syntax with Table Alias

You Can Fully Qualify All Columns

A two-table 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 CROSS-JOIN 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 Three-Table Join?

Answer – Can you Write the Three-Table Join?

Quiz –Write the Three-Table Join with ANSI Syntax?

Answer – Write the Three-Table 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 Five-Table 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 Non-ANSI Syntax

Quiz – Re-Write this putting the ON clauses at the END

Answer – Re-Write 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 3-rows 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 3-rows 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

bottom of page