Google BigQuery:Hands-On SQL Training for Data Analysis

Learn how to use upper, lower, substring, and trim in SQL to transform and extract parts of strings, including examples of starting positions and lengths.

Explore sql string functions by using concatenate to merge columns, replace to swap substrings, and split to divide a string by a delimiter, with practical BigQuery examples.

Explore the main sql operators: arithmetic operators for numeric calculations, comparison operators for value comparisons, and logical operators for combining conditions. Learn the like operator for pattern matching.

Learn how the and operator enforces multiple true conditions, such as robbery and residence, in the Chicago crime dataset; build and run queries to see only matching results.

Learn how the or operator filters rows in sql queries on a crime table, selecting columns and combining conditions (location description, case number, incident type, primary type) to return incidents.

Learn how to use the in operator to filter by multiple values in Google BigQuery SQL queries, with examples on location description such as residence, street, and apartment.

Explore how the like operator filters BigQuery data by matching patterns with percent signs and underscores, finding state names that start, end, or appear in any position.

Learn to use the between operator in Google BigQuery to filter text, numeric, and date fields with inclusive bounds and start-with patterns.

Explore aggregate functions in SQL, such as count, sum, average, min, max, and standard deviation, and apply them to tables like salary to compute totals and durations.

Learn how the group by statement works with aggregate functions to group repeating values in one or more columns, using a salary table to sum by designation.

Learn to use aggregate functions in Google BigQuery with hands-on SQL, applying count, sum, and average alongside group by and having to analyze bike trips, station durations, and trip averages.

Group and summarize data in Google BigQuery by day, week, and month using the extract function and group by to count records and analyze trends.

Explore inner join by combining records from the imply table and the department table to retrieve matching department names, illustrating that inner joins return only matched rows.

Explore left, right, and full outer joins, learning how each returns rows from left, right, or both tables and when unmatched rows appear as null values.

Learn inner joins to match rows across cycle and cycle_stations, then left outer joins to include all left-table rows and show nulls for unmatched right-table entries.

Explore self joins by joining a table to itself, including inner self join, left outer self join, and right outer self join, plus cross join and Cartesian product.

Explore the cartesian product, or cross join, where every row from one table pairs with all rows from another, illustrated by 3x3 examples and warnings about large-table performance.

Explore subqueries in sql by showing how the inner query runs before the outer query, with operators, and solve examples like the second maximum duration using select, from, and joins.

Master row_number, or rule number, which assigns a unique value per partition using order by; apply partition by and inline syntax to retrieve top names per state.

Learn how rank and dense_rank functions work in sql queries, using over and order by salary desc; rank skips ranks on duplicates while dense_rank does not, with optional partition by.

Explore how rank and dense_rank work in BigQuery, including how duplicates affect ranking, with a practical example using a table to show descending order and skipped vs non-skipped rankings.

Demonstrates cumulative sum and moving average using unbounded preceding and following with partition by and order by, in sql queries, with practical examples and result interpretation.

Explore the case statement by applying case when conditions to the count column, classify counts into small, medium, and large quantities, and order results descending.

Use the if function in Google BigQuery to return 'large' when a count exceeds 15 and null otherwise, creating derived columns. Then test name patterns to yield true or false.