Download

REQUEST PRICING

Course Number: SQL-702
Duration: 3 days (19.5 hours)
Format: Live, hands-on

Google BigQuery SQL Training Overview

This live, instructor-led Google BigQuery SQL training course teaches attendees basic and advanced SQL for querying the Google BigQuery cloud data warehouse.

Location and Pricing

Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.

In addition, some courses are available as live, instructor-led training from one of our partners.

Objectives

Basic SQL functions
The WHERE clause
Distinct and Group By
Aggregation
Joins
Date and time functions
Format functions
Analytics and window functions
Temporary tables
Subqueries
Strings
Data interrogating
Views
Set operators
Table creation
Data Manipulation Language (DML)
Math functions
Statistical aggregate functions

Prerequisites

No prior experience is presumed.

Outline

Expand All | Collapse All

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

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.

Download

REQUEST PRICING

Google BigQuery SQL Training Overview

Location and Pricing

Objectives

Prerequisites

Outline

Training Materials

Software Requirements

Related Topics

Learn faster

Satisfaction guarantee

Learn online from anywhere

Multiple Payment Options

Agile

Business Analysis

DEI

ITIL

IT Leadership

Six Sigma

Introduction to Cloud Computing for Managers

Cloudflare

Google Cloud

Beginning OpenStack

Terraform

VMware

Amazon Web Services (AWS)

Azure

Remote Conferencing Tools

Writing and Communication

Adobe, Articulate, and e-Learning

AWS Data Science

Machine Learning

Data Engineering

Generative AI

NVIDIA

Data Literacy

Data Science for Healthcare Overview

Data Science Programming

Data Science Management and DataOps

Robotic Process Automation (RPA)

Data Analytics Tools

Data Visualization

Reporting

Amazon RedShift

MongoDB

NoSQL

PostgreSQL

Introduction to SQL Using MySQL

Big Data

SQL Server

Oracle

Ansible

Apache Maven

DevOps

DevOps CI/CD Pipeline

Docker and Kubernetes

Git

Jenkins

Jira & Confluence

Linux

Microservices

Terraform

OpenShift Administration

SaltStack and Salt Open Source Administration

Microsoft Official Curriculum (MOC)

.NET Development

SharePoint

Microsoft Server Platforms

Microsoft 365

Microsoft 365 Administration and Security

Salesforce End User

Salesforce Administration

Salesforce Developer

Salesforce Cloud

Salesforce Einstein and Salesforce Platform

MuleSoft

Fundamentals of DevSecOps

Secure Coding

Microsoft Security

Web Application Security

AWS Security

Introduction to ArgoCD

Introduction to Bazel

Programming in C++

Introduction to Lua Programming

API Management Fundamentals for Architects

RESTful API Design and Development

RESTful API Design, Development, and Testing using Insomnia

Scala Programming for Java Developers

Introduction to the Zig Programming Language

Erlang

Go Programming

Java