Teradata SQL

This Teradata SQL - Essentials training course is designed to comprehensively cover SQL from basic syntax to stored procedures to SQL programming considerations.

The Teradata SQL - Advanced training course is designed to provide more sophisticated usages of the Teradata database. While it does incorporate more functionality associated with the SELECT, it goes beyond data mining to include data maintenance.

Additionally, it focuses on techniques that provide a high degree of flexibility as well as performance opportunities. This course uses methodologies to enhance data integrity as well as looking for opportunities to enhance performance of queries. This course is designed for experienced SQL users.

Objectives:

• By attending Teradata SQL - Essentials workshop, delegates will learn to:
• Describe the Teradata architecture
• Perform basic and advanced SQL functions
• By attending Teradata SQL - Advanced workshop, delegates will learn to:
• Increase overall knowledge and experience with Teradata
• Describe the EXPLAIN
• Increase SQL proficiency
• Apply performance advantages related to transactions
• List and apply alternatives, tricks and techniques for data mining

Content:

• Teradata Parallel Architecture
• Teradata Introduction
• Teradata Architecture
• Teradata Components
• Parsing Engine Processor (PEP or PE)
• Access Module Processor (AMP)
• Message Passing Layer (BYNET)
• A Teradata Database
• CREATE  MODIFY DATABASE Parameters
• Teradata Users
• {CREATE | MODIFY} DATABASE or USER (in common)
• {CREATE | MODIFY} USER (only)
• Symbols Used in this Book
• DATABASE Command
• Use of an Index
• Primary Index
• Secondary Index
• Determining the Release of Your Teradata System:
• Teradata Limits
• Fundamental SQL Using SELECT
• Fundamental Structured Query Language (SQL)
• Basic SELECT Command
• WHERE Clause
• Compound Comparisons (AND / OR)
• Impact of NULL on Compound Comparisons
• Using NOT in SQL Comparisons
• Multiple Value Search (IN)
• Using NOT IN
• Using Quantifiers Versus IN
• Multiple Value Range Search (BETWEEN)
• Character String Search (LIKE)
• Derived Columns
• Creating a Column Alias Name
• AS
• NAMED
• Naming conventions
• Breaking Conventions
• ORDER BY
• TOP Rows Option
• DISTINCT Function
• On-line HELP and SHOW Commands
• HELP commands
• SET SESSION command
• SHOW commands
• EXPLAIN
• Adding Comments
• ANSI Comment
• Teradata Comment
• User Information Functions
• ACCOUNT Function
• DATABASE Function
• SESSION Function
• Data Conversions
• Data Conversions
• Data Types
• CAST
• Implied CAST
• Formatted Data
• Formatted Data for Day-Month-Year
• Tricking the ODBC to Allow Formatted Data
• TITLE Attribute for Data Columns
• Transaction Modes
• Case Sensitivity of Data
• CASE SPECIFIC
• LOWER Function
• UPPER Function
• Aggregation
• Aggregate Processing
• Math Aggregates
• The SUM Function
• The AVG Function
• The MIN Function
• The MAX Function
• The COUNT Function
• Aggregates and Derived Data
• GROUP BY
• Limiting Output Values Using HAVING
• V12 GROUP BY Options
• GROUP BY GROUPING SETS
• GROUP BY ROLLUP
• GROUP BY CUBE
• Statistical Aggregates
• The KURTOSIS Function
• The SKEW Function
• The STDDEV_POP Function
• The STDDEV_SAMP Function
• The VAR_POP Function
• The VAR_SAMP Function
• The CORR Function
• The COVAR Function
• The REGR_INTERCEPT Function
• The REGR_SLOPE Function
• Using GROUP BY
• Use of HAVING
• Using the DISTINCT Function with Aggregates
• Aggregates and Very Large Data Bases (VLDB)
• Potential of Execution Error
• GROUP BY versus DISTINCT
• Performance Opportunities
• Subquery Processing
• Subquery
• Using NOT IN
• Using Quantifiers
• Qualifying Table Names and Creating a Table Alias
• Qualifying Column Names
• Creating an Alias for a Table
• Correlated Subquery Processing
• Correlated Subquery To Find Duplicate Values
• EXISTS