Google BigQuery SQL

Training Materials

All Google BigQuery SQL training students receive comprehensive courseware.

Software Requirements

Internet access via Chrome is required to access the remote environment used for this training.

Outline

• Introduction
• The Fundamentals of SQL
  • SELECT * Returns All Columns from the Table
  • SELECT Specific Columns in a Table
  • Commas in the Front or Back?
  • ORDER BY
  • Nulls
  • 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
  • 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 and Offset
  • Comments
  • Move Data to the Google BigQuery Effortlessly
• 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
    • 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
  • What is the Order of Precedence?
  • Using Parentheses to change the Order of Precedence
  • Using an IN List in Place of OR
  • The BETWEEN STatement
  • LIKE
• Distinct Vs. Group By
  • The Distinct Command
  • Distinct vs. GROUP BY
• Aggregation
  • There are Five Aggregates
  • 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
• 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
  • 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?
  • 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 Self  Join
  • The Self  Join with ANSI Syntax
  • An Associative Table is a Bridge that Joins Two Tables
  • The Five-Table Join – Logical Insurance Model
• 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
• Analytics
  • The Nexus Super Join Builder builds SQL Automatically
  • Row_Number
  • Find the Top Two Students Per class_code
  • RANK
  • Cumulative Sum
  • Reset with a PARTITION BY Statement
  • Totals and Subtotals through Partition By
  • Moving Sum
  • Partition By Resets the Calculation
  • Moving Average
  • The Partition By Statement
  • Partition By Resets an ANSI OLAP
  • Moving Difference
  • 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
  • MAX OVER and MIN OVER
  • Different Windowing Options
  • How Ntile Works
  • Using Quantiles (Partitions of Four)
  • NTILE With a Partition
  • NTILE With a Partition and a Derived Table
  • Using FIRST_VALUE
  • Last_Value
  • Using LEAD With an Offset of 2 and a PARTITION
  • Using LAG
  • CUME_DIST
  • CURRENT ROW AND UNBOUNDED FOLLOWING
  • Different Windowing Options
  • ANY_VALUE
• 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
• 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
  • Should you use a Subquery or a Join?
  • 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
  • NOT IN Subquery Returns Nothing when Nulls are Present
  • Fixing a NOT IN Subquery with Null Values
  • Using a Correlated Exists
  • How a Correlated Exists Matches Up
  • The Correlated NOT Exists
• Strings
  • Nexus Pivots Your Answer Sets
  • UPPER and lower  Functions
  • The Length Command Counts Characters
  • The Char_Length Command Counts Characters
  • The TRIM Command trims both Leading and Trailing Spaces
  • The RTRIM and LTRIM Command Trims Spaces
  • Concatenation
  • The SUBSTR and SUBSTRING Commands
  • The STRPOS Command finds a Letters Position
  • 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)
  • Regex
  • The REPLACE Function
• Interrogating the Data
  • Drag an Answer Set to Any System to Create a Table
  • 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
• 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
• 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
  • 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
• 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
• 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
• 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
• 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
• Conclusion