Visão Geral

Este Curso Teradata SQL foi desenvolvido para cobrir de forma abrangente o SQL, desde a sintaxe básica até os procedimentos armazenados e as considerações de programação SQL.

Este curso pode ser ministrado usando a versão 12, 13 ou 14. Informe seu representante de vendas sobre a versão que você está usando no momento. Também é totalmente personalizável e pode variar de 2 a 5 dias, dependendo de suas necessidades.

Objetivo

Após a conclusão do curso Teradata SQL, os alunos serão capazes de:

• Descreva a arquitetura Teradata
• Executar funções SQL básicas e avançadas

Publico Alvo

• Profissionais de TI
• Gerente de projetos
• Analista de sistema
• DBA Junior,
• Analista de Banco de dados
• Analista de estrutura de TI

Pre-Requisitos

Informações Gerais

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

Formato de Entrega:

• On-line ao vivo em tempo real via Microsoft Teams, na presença de um instrutor,

Materiais

Conteúdo Programatico

Teradata Parallel Architecture

1. Teradata Introduction
2. Teradata Architecture
3. Teradata Components
4. Parsing Engine Processor (PEP or PE)
5. Access Module Processor (AMP)
6. Message Passing Layer (BYNET)
7. A Teradata Database
8. CREATE / MODIFY DATABASE Parameters
9. Teradata Users
10. {CREATE | MODIFY} DATABASE or USER (in common)
11. {CREATE | MODIFY} USER (only)
12. Symbols Used in this Book
13. DATABASE Command
14. Use of an Index
15. Primary Index
16. Secondary Index
17. Determining the Release of Your Teradata System:
18. Teradata Limits

Fundamental SQL Using SELECT

1. Fundamental Structured Query Language (SQL)
2. Basic SELECT Command
3. WHERE Clause
4. Compound Comparisons (AND / OR)
5. Impact of NULL on Compound Comparisons
6. Using NOT in SQL Comparisons
7. Multiple Value Search (IN)
8. Using NOT IN
9. Using Quantifiers Versus IN
10. Multiple Value Range Search (BETWEEN)
11. Character String Search (LIKE)
12. Derived Columns
13. Creating a Column Alias Name
14. AS
15. NAMED
16. Naming conventions
17. Breaking Conventions
18. ORDER BY
19. TOP Rows Option
20. DISTINCT Function

On-line HELP and SHOW Commands

1. HELP commands
2. SET SESSION command
3. SHOW commands
4. EXPLAIN
5. Adding Comments
6. ANSI Comment
7. Teradata Comment
8. User Information Functions
9. ACCOUNT Function
10. DATABASE Function
11. SESSION Function

Data Conversions

1. Data Conversions
2. Data Types
3. CAST
4. Implied CAST
5. Formatted Data
6. Formatted Data for Day-Month-Year
7. Tricking the ODBC to Allow Formatted Data
8. TITLE Attribute for Data Columns
9. Transaction Modes
10. Case Sensitivity of Data
11. CASESPECIFIC
12. LOWER Function
13. UPPER Function

Aggregation

1. Aggregate Processing
2. Math Aggregates
3. The SUM Function
4. The AVG Function
5. The MIN Function
6. The MAX Function
7. The COUNT Function
8. Aggregates and Derived Data
9. GROUP BY
10. Limiting Output Values Using HAVING
11. V12 GROUP BY Options
12. GROUP BY GROUPING SETS
13. GROUP BY ROLLUP
14. GROUP BY CUBE
15. Statistical Aggregates
16. The KURTOSIS Function
17. The SKEW Function
18. The STDDEV_POP Function
19. The STDDEV_SAMP Function
20. The VAR_POP Function
21. The VAR_SAMP Function
22. The CORR Function
23. The COVAR Function
24. The REGR_INTERCEPT Function
25. The REGR_SLOPE Function
26. Using GROUP BY
27. Use of HAVING
28. Using the DISTINCT Function with Aggregates
29. Aggregates and Very Large Data Bases (VLDB)
30. Potential of Execution Error
31. GROUP BY versus DISTINCT
32. Performance Opportunities

Subquery Processing

1. Subquery
2. Using NOT IN
3. Using Quantifiers
4. Qualifying Table Names and Creating a Table Alias
5. Qualifying Column Names
6. Creating an Alias for a Table
7. Correlated Subquery Processing
8. Correlated Subquery To Find Duplicate Values
9. EXISTS

Join Processing

1. Join Processing
2. Original Join Syntax
3. Product Join
4. Newer ANSI Join Syntax
5. INNER JOIN
6. OUTER JOIN
7. LEFT OUTER JOIN
8. RIGHT OUTER JOIN
9. FULL OUTER JOIN
10. CROSS JOIN
11. Self Join
12. Alternative JOIN / ON Coding
13. Adding Residual Conditions to a Join
14. INNER JOIN
15. OUTER JOIN
16. OUTER JOIN Hints
17. Parallel Join Processing
18. Join Index Processing

Date and Time Processing

1. ANSI Standard DATE Reference
2. INTEGERDATE
3. ANSIDATE
4. DATEFORM
5. System Level Definition
6. User Level Definition
7. Session Level Declaration
8. DATE Processing
9. ADD_MONTHS
10. ANSI TIME
11. EXTRACT
12. Implied Extract of Day, Month and Year
13. ANSI TIMESTAMP
14. TIME ZONES
15. Setting TIME ZONES
16. Using TIME ZONES
17. Normalizing TIME ZONES
18. DATE and TIME Intervals
19. Using Intervals
20. INTERVAL Arithmetic with DATE and TIME
21. CAST Using Intervals
22. OVERLAPS
23. System Calendar

Character String Processing

1. Transforming Character Data
2. CHARACTERS Function
3. CHARACTER_LENGTH Function
4. OCTET_LENGTH Function
5. TRIM
6. SUBSTRING
7. SUBSTR
8. SUBSTRING and Numeric Data
9. POSITION
10. INDEX
11. SUBSTRING and POSITION or INDEX Used Together
12. Concatenation of Character Strings

OLAP Functions

1. On-Line Analytical Processing (OLAP) Functions
2. OLAP Functions
3. Cumulative Sum Using the CSUM Function
4. Cumulative Sum with Reset Capabilities
5. Using CSUM and GROUP BY
6. Generating Sequential Numbers with CSUM
7. Moving Sum Using the MSUM Function
8. Moving Sum with Reset Capabilities
9. Using MSUM and GROUP BY
10. Moving Average Using the MAVG Function
11. Moving Average with Reset Capabilities
12. Using MAVG and GROUP BY
13. Moving Difference Using the MDIFF Function
14. Moving Difference with Reset Capabilities
15. Using MDIFF and GROUP BY
16. Cumulative and Moving SUM Using SUM / OVER
17. Cumulative Sum with Reset Capabilities
18. SUM Using SUM / OVER and PARTITION BY
19. Moving Average Using AVG / OVER
20. Moving Average with Reset Capabilities
21. Using AVG and OVER / ROWS and PARTITION BY
22. Moving Linear Regression Using the MLINREG Function
23. Categorizing Data Using the QUANTILE Function
24. QUALIFY to Find Products in the top Partitions
25. Ranking Data using RANK
26. QUALIFY to Find Top Best or Bottom Worse
27. RANK with Reset Capabilities
28. Using RANK with GROUP BY
29. Ranking Data using RANK / OVER
30. QUALIFY to Find Top Best or Bottom Worse
31. RANK with Reset Capabilities
32. Using RANK/OVER with PARTITION BY
33. Internal RANK operations
34. Percentage of Total Rows Using PERCENT_RANK / OVER
35. Percent Rank with Reset Capabilities
36. Using PERCENT_RANK OVER and PARTITION BY
37. Counting of the Total Rows Using COUNT / OVER
38. COUNT OVER with Reset Capabilities
39. Using COUNT OVER and PARTITION BY
40. Finding the Largest Value Using MAX / OVER
41. Finding the Smallest Value Using MIN / OVER
42. Numbering of the Rows Using ROW_NUMBER / OVER
43. Sampling Rows using the SAMPLE Function
44. RANDOM Number Generator Function

SET Operators

1. Set Operators
2. Considerations for Using Set Operators
3. INTERSECT
4. UNION
5. EXCEPT
6. MINUS
7. Using Multiple Set Operators in a Single Request

Data Manipulation

1. Data Maintenance
2. Considerations for Data Maintenance
3. Safeguards
4. INSERT Command
5. Using Null for DEFAULT VALUES
6. INSERT / SELECT Command
7. Fast Path INSERT / SELECT
8. UPDATE Command
9. Fast Path UPDATE
10. DELETE Command
11. Fast Path DELETE
12. UPSERT
13. MERGE
14. ANSI Vs Teradata Transactions
15. Performance Issues with Data Maintenance
16. Impact of FALLBACK on Row Modification
17. Impact of PERMANENT JOURNAL Logging on Row Modification
18. Impact of Primary Index on Row Modification
19. Impact of Secondary Indices on Row Modification

Data Interrogation

1. Data Interrogation
2. NULLIFZERO
3. NULLIF
4. ZEROIFNULL
5. COALESCE
6. CASE
7. Flexible Comparisons within CASE
8. Comparison Operators within CASE
9. CASE for Horizontal Reporting
10. Nested CASE Expressions
11. CASE used with the other DML
12. Using CASE to avoid a join

View Processing

1. Views
2. Reasons to Use Views
3. Considerations for Creating Views
4. Creating and Using VIEWS
5. TOP Command allows ORDER BY in the View
6. Deleting Views
7. Modifying Views
8. Modifying Rows Using Views
9. DML Restrictions when using Views
10. INSERT using Views
11. UPDATE or DELETE using Views
12. WITH CHECK OPTION
13. Locking and Views

Macro Processing

1. Macros
2. CREATE MACRO
3. REPLACE MACRO
4. EXECUTE Macro
5. DROP MACRO
6. Generating SQL from a Macro

Transaction Processing

1. What is a Transaction?
2. Locking
3. Transaction Modes
4. Comparison Chart
5. Setting the Transaction Mode
6. Teradata Mode Transactions
7. ANSI Mode Transactions
8. Aborting Teradata Transactions
9. Aborting ANSI Transactions

Reporting Totals and Subtotals

1. Totals and Subtotals
2. Totals (WITH)
3. Subtotals (WITH BY)
4. Multiple Subtotals on a Single Break
5. Multiple Subtotal Breaks

Data Definition Language

1. Creating Tables
2. Table Considerations
3. Maximum Columns per Table
4. Table Naming Conventions
5. CREATE TABLE
6. Column Data Types
7. Specifying the Database in a CREATE TABLE Statement
8. PRIMARY INDEX considerations
9. CREATE TABLE AS
10. Table Type Specifications of SET VS MULTISET
11. SET and MULTISET Tables
12. Protection Features
13. FALLBACK
14. Permanent Journal
15. BEFORE Journal
16. AFTER Journal
17. Internal Storage Options
18. DATABLOCKSIZE
19. FREESPACE PERCENTAGE
20. QUEUE Tables
21. Partitioned Primary Index (PPI)
22. SQL for Partitioned Primary Index (PPI)
23. Adding and Dropping Partitions
24. Column Attributes
25. Constraints
26. UNIQUE Constraint
27. CHECK Constraint
28. Referential Integrity (RI) Constraint
29. Defining Constraints at the Column level
30. Defining Constraints at the Table Level
31. Utilizing Default Values for a Table
32. Secondary Indices
33. CREATE TABLE to Copy an existing table
34. Altering a Table
35. Modifying the Primary Index Partitioning
36. Revalidating the Primary Index
37. Dropping a Table
38. Dropping a Table versus Deleting Rows
39. Renaming a Table
40. Creating Secondary via CREATE INDEX
41. Join Index
42. Collecting Statistics
43. Hashing Functions
44. HASHROW
45. HASHBUCKET
46. HASHAMP
47. HASHBAKAMP

Temporary Tables

1. Temporary Tables
2. Temporary Table Choices
3. Derived Tables
4. Derived Tables Using a Non-Recursive WITH
5. Derived Tables Using a Recursive WITH
6. FROM TABLE UDF Tables
7. Volatile Temporary Tables
8. Global Temporary Tables
9. GLOBAL Temporary Table Examples
10. General Practices for Temporary use Tables

Trigger Processing

1. Triggers
2. Terminology
3. Logic Flow
4. CREATE TRIGGER Syntax
5. Row Trigger
6. Statement Trigger
7. BEFORE Trigger
8. AFTER Trigger
9. INSTEAD OF Trigger
10. Cascading Triggers
11. Sequencing Triggers

Stored Procedures

1. Teradata Stored Procedures
2. CREATE PROCEDURE
3. Stored Procedural Language (SPL) Statements
4. BEGIN / END Statements
5. Establishing Variables and Data Values
6. DECLARE Statement to Define Variables
7. SET to Assign a Data Value as a Variable
8. Status Variables
9. Assigning a Data Value as a Parameter
10. Additional SPL Statements
11. CALL Statement
12. CASE / END CASE Statement
13. IF / END IF Statement
14. LOOP / END LOOP Statements
15. LEAVE Statement
16. REPEAT / END REPEAT Statement
17. WHILE / END WHILE Statement
18. FOR / END FOR Statements
19. ITERATE Statement
20. Using a Cursor
21. DECLARE CURSOR Statement
22. OPEN CURSOR Statement
23. CLOSE CURSOR Statement
24. FETCH Statement
25. Exception Handling
26. DECLARE HANDLER Statement
27. PRINT Statement
28. DML Statements
29. Using Column and Alias Names
30. Comments and Stored Procedures
31. Commenting in a Stored Procedure
32. Commenting on a Stored Procedure
33. On-line HELP for Stored Procedures
34. HELP on a Stored Procedure
35. HELP on Stored Procedure Language (SPL)
36. REPLACE PROCEDURE
37. ALTER PROCEDURE
38. DROP PROCEDURE
39. RENAME PROCEDURE
40. SHOW PROCEDURE
41. Dynamic SQL
42. Considerations When Using Stored Procedures
43. Compiling a Procedure
44. Temporary Directory Usage

Temporal Tables

1. Temporal Tables
2. There are three types of Temporal Tables:
3. Valid Time Temporal Table
4. Temporal Table Quiz
5. Transaction Time Temporal Table
6. Bi-Temporal Tables
7. SQL for Temporal Tables
8. Here are your Temporal Table SQL Keywords designed to help query Temporal Tables.
9. CURRENT AS OF
10. SEQUENCED
11. NONSEQUENCED