## BigQuery for Data Analysts

### What you’ll learn

Learn how to work with SQL functions for math, strings, datetimes, and more

Use structured and repeated fields for efficient data modeling

Apply access controls to columns, rows, tables, and datasets

Cost controls and performance optimizations

### Requirements

Some basic experience with BigQuery

### Description

Analyze data in BigQuery using both basic SQL statements as well as specialized functions to help you gain insight into your data. If you need to analyze data in BigQuery and have some familiarity with using BigQuery, then this course will help you expand your skills with lectures, quizzes, and assignments. (If you are new to BigQuery, consider taking the Introduction to BigQuery course first.)The course begins with data definition language statements for creating and altering tables and views followed by a review of data manipulation statements, including SELECT, INSERT, UPDATE, DELETE, and MERGE. Learn how to use common table expressions (CTEs) effectively to modularize your SQL and make even complex logic easy to understand.Note: There are several basic videos reviewing SELECT statements which you can skip if you have taken Introduction to BigQuery or are comfortable writing SELECT statements.Working with BigQuery has some differences from working with relational databases like Postgres and SQL Server. We often use arrays and structures in BigQuery and this course will teach you how to work with arrays, querying and unnesting arrays, using array functions as well as working with structures and arrays of structures.SQL functions are foundational building blocks for data analytics work and it is important to know the functions available in BigQuery to rapidly and effectively analyze data. In this course, we will learn about a range of SQL functions for:Math and statisticsAggregate functionsData type castingDates and timesString manipulationRegular expressionsApproximate functions for working with large datasetsAnalytical and window functionsData analysts need to be familiar with BigQuery operations so the course also covers BigQuery security, including access controls, column security, and row-level security, as well as performance and cost management.Test your understanding of BigQuery concepts with 8 assignments and 10 quizzes.In this course, you will learn a wide range of topics that data analysts need to understand and you will know how to apply that knowledge effectively in BigQuery while working cost-effectively. After completing this course you will know a wide range of functions and techniques for analyzing and transforming data in BigQuery, understand how access to data is controlled using IAM, row-level, and column security, and how to control the cost of your BigQuery operations.

### Overview

Section 1: Introduction

Lecture 1 Introduction

Section 2: Creating Tables and Views in BigQuery

Lecture 2 Creating Tables using the BigQuery Console

Lecture 3 Creating Tables using the bq Command

Lecture 4 Creating Tables using SQL Statements

Lecture 5 Creating Views using the BigQuery Console

Lecture 6 Creating Views using SQL Statements

Lecture 7 Altering a Table

Section 3: Review of Querying with SELECT Statements

Lecture 8 Working with a 1 Table Public Data Set

Lecture 9 Working with a 2 Table Public Data Set

Lecture 10 Basic Select Statements

Lecture 11 WHERE Clauses

Lecture 12 ORDER BY Clauses

Lecture 13 GROUP BY Clauses

Lecture 14 WHERE, GROUP BY, and ORDER BY Clauses

Lecture 15 HAVING Clauses

Lecture 16 Aggregate Functions

Lecture 17 Simple Math Expressions

Lecture 18 CASE Statements and Error Checking

Lecture 19 Joining Two Tables

Lecture 20 Common Table Expressions

Section 4: Data Manipulation Language Statements

Lecture 21 Inserting Data

Lecture 22 Updating Data

Lecture 23 Deleting and Truncating Data

Lecture 24 Merging and Upserting Data

Section 5: Arrays and Structs

Lecture 25 Querying and Unnesting Arrays

Lecture 26 Array Functions

Lecture 27 Querying Structs

Lecture 28 Querying Arrays of Structs

Section 6: SQL Math and Cast Functions

Lecture 29 Basic Math Functions

Lecture 30 Sample Data for Numeric Functions

Lecture 31 ABS and SIGN Functions

Lecture 32 SQRT and POWER Functions

Lecture 33 RAND and MOD Functions

Lecture 34 Safe Operations

Lecture 35 Casting Data Types

Section 7: Date and Time Functions

Lecture 36 Date Functions

Lecture 37 Datetime Functions

Lecture 38 Time Functions

Lecture 39 Timestamp Functions

Section 8: Statistical Aggregate Functions

Lecture 40 Pearson Correlation with the CORR Function

Lecture 41 Calculating Covariance with COVAR Function

Lecture 42 Calculating Standard Deviation with STDDEV Functions

Section 9: String Functions

Lecture 43 Basic String Functions

Lecture 44 Trimming and Padding

Lecture 45 Splitting Delimited Strings

Lecture 46 Working with Substrings

Lecture 47 Matching with Regular Expressions

Section 10: Window Functions

Lecture 48 Introduction to Window Functions

Lecture 49 Lead and Lag Functions

Lecture 50 First Values Functions

Lecture 51 Numbering Functions

Lecture 52 Moving Average Functions

Section 11: Access Controls in BigQuery

Lecture 53 BigQuery and Identity and Access Management (IAM)

Lecture 54 Column Level Access Contols

Lecture 55 Row Level Security

Section 12: Cost Control and Query Performance Tips

Lecture 56 Query Performance Tips

Lecture 57 Cost Control Options

Section 13: Conclusion

Lecture 58 Next Steps

Data analysts,Data engineers,SQL developers,Database administrators,Analytics engineers

#### Course Information:

Udemy | English | 4h 56m | 1.55 GB

Created by: Dan Sullivan

You Can See More Courses in the IT & Software >> Greetings from CourseDown.com