Curso Google BigQuery SQL Foundation

  • Tableau Data Visualization

Curso Google BigQuery SQL Foundation

24 Horas
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
Português/Inglês
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
TENHO INTERESSE

Cursos Relacionados

Curso Análise de Dados Com o Power BI - 20778B

24 horas

Curso Análise de dados Excel Com Power BI - 20779B

16 horas

Curso Talend Data Integration Foundation

16 horas

Curso Talend Data Integration Advanced

16 horas

Curso Advanced Data Analysis and Dashboard Reporting

28 horas