GC Partner no outline H
8293  Reviews star_rate star_rate star_rate star_rate star_half

Google BigQuery SQL

Skills Gained 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...

Read More
$2,000 USD
Duration 3 days
Course Code SQL-702
Available Formats Classroom

Overview

Skills Gained

  • 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.

Course Details

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

Schedule

FAQ

Does the course schedule include a Lunchbreak?

Classes typically include a 1-hour lunch break around midday. However, the exact break times and duration can vary depending on the specific class. Your instructor will provide detailed information at the start of the course.

What languages are used to deliver training?

Most courses are conducted in English, unless otherwise specified. Some courses will have the word "FRENCH" marked in red beside the scheduled date(s) indicating the language of instruction.

What does GTR stand for?

GTR stands for Guaranteed to Run; if you see a course with this status, it means this event is confirmed to run. View our GTR page to see our full list of Guaranteed to Run courses.

Does Ascendient Learning deliver group training?

Yes, we provide training for groups, individuals and private on sites. View our group training page for more information.

What does vendor-authorized training mean?

As a vendor-authorized training partner, we offer a curriculum that our partners have vetted. We use the same course materials and facilitate the same labs as our vendor-delivered training. These courses are considered the gold standard and, as such, are priced accordingly.

Is the training too basic, or will you go deep into technology?

It depends on your requirements, your role in your company, and your depth of knowledge. The good news about many of our learning paths, you can start from the fundamentals to highly specialized training.

How up-to-date are your courses and support materials?

We continuously work with our vendors to evaluate and refresh course material to reflect the latest training courses and best practices.

Are your instructors seasoned trainers who have deep knowledge of the training topic?

Ascendient Learning instructors have an average of 27 years of practical IT experience and have also served as consultants for an average of 15 years. To stay current, instructors spend at least 25 percent of their time learning new, emerging technologies and courses.

Do you provide hands-on training and exercises in an actual lab environment?

Lab access is dependent on the vendor and the type of training you sign up for. However, many of our top vendors will provide lab access to students to test and practice. The course description will specify lab access.

Will you customize the training for our company’s specific needs and goals?

We will work with you to identify training needs and areas of growth.  We offer a variety of training methods, such as private group training, on-site of your choice, and virtually. We provide courses and certifications that are aligned with your business goals.

How do I get started with certification?

Getting started on a certification pathway depends on your goals and the vendor you choose to get certified in. Many vendors offer entry-level IT certification to advanced IT certification that can boost your career. To get access to certification vouchers and discounts, please contact info@ascendientlearning.com.

Will I get access to content after I complete a course?

You will get access to the PDF of course books and guides, but access to the recording and slides will depend on the vendor and type of training you receive.

How do I request a W9 for Ascendient Learning?

View our filing status and how to request a W9.

Reviews

Exit certified was great as it is very in depth and hands on learning which made it very easy to learn this type of work.

Good Course. We covered a lot of material in a short amount of time. This course had useful labs that built upon each other.

Easy to work with. Learning material pdfs were able to be printed out in color which was very nice to write on.

Instructor knew her stuff. Long time in the industry. Course was easy to follow and very informative.

Great company -- easy to sign up and very organized. Loved my teacher and class overall.