Curso Teradata SQL

Database

Curso Teradata SQL

24 Horas
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
Não se aplica!
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
Português | Inglês
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
TENHO INTERESSE

Cursos Relacionados

Curso Oracle Database 12c Admin Install Upgrade

horas

Curso Oracle Database 12c Administração I

32 horas

Curso Oracle 12c Banco de Dados Introdução ao SQL

28 Horas

Curso Oracle Fundamentos de Big Data

32 horas

Curso Oracle Database 12c Performance Tuning

24 Horas

Curso Oracle 12c 10 Programa com PL/SQL

32 horas

Curso Oclacle 12c RAC – Administração de Banco de Dados

32 horas

Curso Oracle 12c Banco de Dados PL/SQL Advanced

24 horas

Curso Oracle 18c Database Backup and Recovery Workshop

32 horas