Google BigQuery SQL


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 Big Data courses are available as live, online classes for individuals.

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.



Learn faster

Our live, instructor-led lectures are far more effective than pre-recorded classes

Satisfaction guarantee

If your team is not 100% satisfied with your training, we do what's necessary to make it right

Learn online from anywhere

Whether you are at home or in the office, we make learning interactive and engaging

Multiple Payment Options

We accept check, ACH/EFT, major credit cards, and most purchase orders



Recent Training Locations

Alabama

Birmingham

Huntsville

Montgomery

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

Los Angeles

Oakland

Orange County

Sacramento

San Diego

San Francisco

San Jose

Colorado

Boulder

Colorado Springs

Denver

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Jacksonville

Miami

Orlando

Tampa

Georgia

Atlanta

Augusta

Savannah

Hawaii

Honolulu

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Cedar Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Frederick

Hagerstown

Massachusetts

Boston

Cambridge

Springfield

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Minneapolis

Saint Paul

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Las Vegas

Reno

New Jersey

Princeton

New Mexico

Albuquerque

New York

Albany

Buffalo

New York City

White Plains

North Carolina

Charlotte

Durham

Raleigh

Ohio

Akron

Canton

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Oklahoma City

Tulsa

Oregon

Portland

Pennsylvania

Philadelphia

Pittsburgh

Rhode Island

Providence

South Carolina

Charleston

Columbia

Greenville

Tennessee

Knoxville

Memphis

Nashville

Texas

Austin

Dallas

El Paso

Houston

San Antonio

Utah

Salt Lake City

Virginia

Alexandria

Arlington

Norfolk

Richmond

Washington

Seattle

Tacoma

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Calgary

Edmonton

British Columbia

Vancouver

Manitoba

Winnipeg

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan