Visão Geral

Este Curso Google BigQuery SQL  Foundation ministrado por instrutor ensina aos participantes SQL básico e avançado para consultar o data warehouse em nuvem do Google BigQuery.

Objetivo

Após realizar este Curso Google BigQuery SQL  Foundation você será capaz de:

• Funções SQL básicas
• A cláusula WHERE
• Distinto e agrupado por
• Agregação
• Junta-se
• Funções de data e hora
• Funções de formatação
• Funções analíticas e de janela
• Tabelas temporárias
• Subconsultas
• Cordas
• Interrogatório de dados 
• Visualizações
• Operadores de conjunto
• Criação de mesa
• Linguagem de manipulação de dados (DML)
• Funções matemáticas
• Funções de agregação estatística

Informações Gerais

• Carga horaria, 24h
• Se noturno este curso e ministrado de segunda-feira a sexta-feira das 19h às 23h, total de 6 noites,
• Se aos sábado este curso e ministrado das 09h às 18h, total de 3 sábados,

Formato de entrega:

• On-line ao vivo na presença de um instrutor via plataforma Microsoft Teams

Materiais

Conteúdo Programatico

Introduction

1. The Fundamentals of SQL
2. SELECT * Returns All Columns from the Table
3. SELECT Specific Columns in a Table
4. Commas in the Front or Back?
5. ORDER BY
6. Nulls
7. Major Sort vs. Minor Sort
8. Multiple Sort Keys using Names vs. Numbers
9. You can ORDER BY using a Mix of names and Numbers
10. Sorts are Alphabetical, NOT Logical
11. Using A Valued CASE Statement to Sort Logically
12. Using A Searched CASE Statement to Sort Logically
13. How to ALIAS a Column Name
14. How to Get Capital Letters in a Report Header
15. Using Spaces in an ALIAS Clauses Errors
16. Using an Alias in the WHERE and ORDER BY Clause
17. A Missing Comma can become an Alias by Mistake
18. Limit and Offset
19. Comments
20. Move Data to the Google BigQuery Effortlessly

The WHERE Clause

1. The WHERE Clause limits Returning Rows
2. Single-Quotes or Double-Quotes Are Used for Character Data
3. Reminder: Using Spaces in an ALIAS Clauses Errors
4. Using a Column ALIAS in the WHERE Clause
5. Numbers Do Not Need Single Quotes
6. Searching for null Values Using Equality Returns Nothing
7. Use IS NULL to Check for Null Values
8. Use IS NOT NULL for Interrogating NULL Values
9. Using Greater Than Or Equal To (>=)
10. AND in the WHERE Clause
11. Troubleshooting
12. Troubleshooting AND
13. OR in the WHERE Clause
14. Troubleshooting OR
15. Why OR Must Utilize the Column Name Each Time
16. Troubleshooting Character Data
17. Troubleshooting Character Data Continued
18. What is the Order of Precedence?
19. Using Parentheses to change the Order of Precedence
20. Using an IN List in Place of OR
21. The BETWEEN STatement
22. LIKE

Distinct Vs. Group By

1. The Distinct Command
2. Distinct vs. GROUP BY

Aggregation

1. There are Five Aggregates
2. Casting a Data Type
3. Troubleshooting Aggregates
4. GROUP BY when Aggregates and Normal Columns Mix
5. GROUP BY Delivers One Row Per Group
6. GROUP BY dept_no or GROUP BY 1 the same thing
7. Limiting Rows and Improving Performance with WHERE
8. WHERE Clause in Aggregation limits unneeded Calculations
9. Keyword HAVING tests Aggregates after they are Totaled

Joining Tables

1. NexusCore Servers - Control Network and Data Movement
2. A Two-Table Join Using Traditional Syntax
3. A two-table join using Non-ANSI Syntax with Table Alias
4. You Can Fully Qualify All Columns
5. A two-table join using ANSI Syntax
6. Both Queries have the same Results and Performance
7. Left Outer Join
8. Left Outer Join Results
9. Right Outer Join
10. Right Outer Join Example and Results
11. Full Outer Join
12. Full Outer Join Results
13. Which Tables are Left, and Which are the Right?
14. INNER JOIN with Additional AND Clause
15. ANSI INNER JOIN with Additional AND Clause
16. ANSI INNER JOIN with Additional WHERE Clause
17. OUTER JOIN with Additional WHERE Clause
18. OUTER JOIN with Additional AND Clause
19. The DREADED Product Join
20. The DREADED Product Join Results
21. Cartesian Product Join with Traditional Syntax
22. Cartesian Product Join with ANSI Syntax
23. The CROSS JOIN
24. The Self  Join
25. The Self  Join with ANSI Syntax
26. An Associative Table is a Bridge that Joins Two Tables
27. The Five-Table Join – Logical Insurance Model

Date and Time

1. The Google BigQuery Tree of Nexus
2. Current_Date
3. Current_Date and Current_Timestamp
4. Add or Subtract From a Date
5. Add or Subtract Days From a Date
6. DATE
7. TIME
8. Support Elements for DATE Formatting
9. The EXTRACT Command
10. EXTRACT from DATES and TIME
11. Extract Options
12. Extract Time and Timestamp
13. STRING Timestamp
14. Another Datediff Example
15. DATE_TRUNC
16. TIME_TRUNC
17. TIMESTAMP_TRUNC
18. LAST_DAY
19. DATE_ADD
20. TIME_ADD
21. TIMESTAMP_ADD
22. DATE_SUB
23. TIME_SUB
24. TIMESTAMP_SUB
25. Clever Tricks for Month
26. Determining if the Current_Date is a Leap Year
27. Determining if the Current_Timestamp is a Leap Year

Analytics

1. The Nexus Super Join Builder builds SQL Automatically
2. Row_Number
3. Find the Top Two Students Per class_code
4. RANK
5. Cumulative Sum
6. Reset with a PARTITION BY Statement
7. Totals and Subtotals through Partition By
8. Moving Sum
9. Partition By Resets the Calculation
10. Moving Average
11. The Partition By Statement
12. Partition By Resets an ANSI OLAP
13. Moving Difference
14. Finding a Value of a Column in the Next Row with MIN
15. Finding a Next Row Value with MIN and PARTITION BY
16. Finding The Next Date using MAX
17. Finding Multiple Values of a Column in Upcoming Rows
18. COUNT OVER
19. MAX OVER and MIN OVER
20. Different Windowing Options
21. How Ntile Works
22. Using Quantiles (Partitions of Four)
23. NTILE With a Partition
24. NTILE With a Partition and a Derived Table
25. Using FIRST_VALUE
26. Last_Value
27. Using LEAD With an Offset of 2 and a PARTITION
28. Using LAG
29. CUME_DIST
30. CURRENT ROW AND UNBOUNDED FOLLOWING
31. Different Windowing Options
32. ANY_VALUE

Temporary Tables

1. Move an Entire Database to Google BigQuery
2. CREATING A Derived Table
3. Naming the Derived Table
4. CREATING A Derived Table using the WITH Command
5. Derived Query Examples with Two Different Techniques
6. Most Derived Tables Are Used To Join To Other Tables
7. The Three Components of a Derived Table
8. Visualize This Derived Table
9. Our Join Example Using The WITH Syntax
10. An Example of Two Derived Tables in a Single Query
11. An Example of Two Derived Tables Using WITH
12. Select Expressions
13. Select Expression Using UNION ALL
14. Another Select Expression Using UNION ALL

Subqueries

1. The Nexus Migrates Data To and From Every System
2. An IN List is much like a Subquery
3. An IN List Never has Duplicates – Just like a Subquery
4. An IN List Ignores Duplicates
5. The Subquery
6. The Three Steps of How a Basic Subquery Works
7. These are Equivalent Queries
8. The Final Answer Set from the Subquery
9. Should you use a Subquery or a Join?
10. The Basics of a Correlated Subquery
11. The Top Query always runs first in a Correlated Subquery
12. Correlated Subquery Example vs. a Join with a Derived Table
13. NOT IN Subquery Returns Nothing when Nulls are Present
14. Fixing a NOT IN Subquery with Null Values
15. Using a Correlated Exists
16. How a Correlated Exists Matches Up
17. The Correlated NOT Exists

Strings

1. Nexus Pivots Your Answer Sets
2. UPPER and lower  Functions
3. The Length Command Counts Characters
4. The Char_Length Command Counts Characters
5. The TRIM Command trims both Leading and Trailing Spaces
6. The RTRIM and LTRIM Command Trims Spaces
7. Concatenation
8. The SUBSTR and SUBSTRING Commands
9. The STRPOS Command finds a Letters Position
10. LPAD and RPAD
11. The REPLACE Function
12. The STARTS_WITH Function
13. The ENDS_WITH Function
14. Initcap Function
15. Repeat Function
16. SPLIT Function
17. TRANSLATE Function
18. The ASCII Function
19. The UNICODE Function
20. The Reverse String Function
21. The RIGHT Function
22. The LEFT and RIGHT Functions
23. SOUNDEX Function to Find a Sound
24. Java Script Object Notation (JSON)
25. Regex
26. The REPLACE Function

Interrogating the Data

1. Drag an Answer Set to Any System to Create a Table
2. IFNULL
3. The COALESCE Command
4. COALESCE is Equivalent to this CASE Statement
5. IF
6. Valued Case vs. Searched Case
7. Combining Searched Case and Valued Case
8. A Trick for getting a Horizontal Case
9. Put a Valued CASE in the ORDER BY
10. Put a Searched CASE in the ORDER BY

Views

1. Join Excel with Production Tables
2. The Fundamentals of Views
3. Creating a Simple View to Restrict Sensitive Columns
4. Creating a Simple View to Restrict Rows
5. Creating a View to Join Tables Together
6. Basic Rules for Views
7. How to Modify a View
8. The Exception to the ORDER BY Rule inside a View
9. Derived Columns in a View Should Contain a Column Alias
10. The Standard Way Most Aliasing is Done

Set Operators

1. When the Desktop and the Server Work as One
2. Set Operators
3. Rule 1: Equal Number of Columns in both SELECT Lists
4. Rule 2: Top Query is Responsible for all Aliasing
5. Rule 3: Bottom Query does the ORDER BY Statement
6. Intersect Challenge
7. Using UNION ALL and Literals
8. Great Trick:  Place your Set Operator in a Derived Table
9. A Great Example of how EXCEPT works
10. Changing the Order of Precedence with Parentheses

Creating Tables

1. Google BigQuery Data Types (1 of 3)
2. Google BigQuery Data Types (2 of 3)
3. Google BigQuery Data Types (3 of 3)
4. Creating a Basic Table
5. IF NOT EXISTS
6. CREATE OR REPLACE
7. Float64 vs. Numeric
8. Partitioned Table Options
9. Date Partitioned Table
10. Timestamp Partitioned Table by Hour
11. Timestamp Partitioned Table by Day
12. Timestamp Partitioned Table by Month
13. Timestamp Partitioned Table by Year
14. Timestamp Partitioned Table by Integer
15. Table Clustering

Data Manipulation Language (DML)

1. INSERT Syntax # 1
2. INSERT Syntax # 2
3. INSERT Example with Multiple Rows
4. Inserting Null Values into a Table
5. INSERT/SELECT Command
6. INSERT/SELECT to Build a Data Mart
7. UPDATE Examples
8. Deleting Rows in a Table

Statistical Aggregate Functions

1. The Stats Table
2. The STDDEV_POP Function
3. STDDEV
4. The STDDEV_SAMP Function
5. The VAR_POP Function
6. The VAR_SAMP Function
7. Variance
8. The CORR Function
9. The COVAR_POP Function
10. The COVAR_SAMP Function
11. ARRAY_AGG
12. ARRAY_AGG Examples
13. More ARRAY_AGG Examples
14. APPROX_COUNT_DISTINCT

Mathematical Functions

1. Example Mathematical Functions
2. Numeric Manipulation Functions
3. ABS
4. ACOS
5. ASIN
6. Ceiling
7. Floor
8. COS
9. DIV
10. EXP
11. LN
12. LOG
13. MOD
14. POWER
15. ROUND
16. SIGN
17. SIN
18. SQRT
19. TRUNC