Call : (+91) 99 8080 3767
Mail : info@EncartaLabs.com
EncartaLabs

Teradata SQL

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

Teradata SQL - Advanced training course is designed to provide attendees with 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.

By attending Teradata SQL - Essentials workshop, attendees will learn to:

  • Describe the Teradata architecture
  • Perform basic and advanced SQL functions

By attending Teradata SQL - Advanced workshop, attendees 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

  • Strong experience in using SQL

  • IT Professionals

COURSE AGENDA

Teradata SQL - Essentials
(Duration : 3 Days)

1

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
2

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
3

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
4

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
5

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
6

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
7

Join Processing

  • Join Processing
  • Original Join Syntax
  • Product Join
  • Newer ANSI Join Syntax
  • INNER JOIN
  • OUTER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • Alternative JOIN / ON Coding
  • Adding Residual Conditions to a Join
  • INNER JOIN
  • OUTER JOIN
  • OUTER JOIN Hints
  • Parallel Join Processing
  • Join Index Processing
8

Date and Time Processing

  • ANSI Standard DATE Reference
  • INTEGERDATE
  • ANSIDATE
  • DATEFORM
  • System Level Definition
  • User Level Definition
  • Session Level Declaration
  • DATE Processing
  • ADD_MONTHS
  • ANSI TIME
  • EXTRACT
  • Implied Extract of Day, Month and Year
  • ANSI TIMESTAMP
  • TIME ZONES
  • Setting TIME ZONES
  • Using TIME ZONES
  • Normalizing TIME ZONES
  • DATE and TIME Intervals
  • Using Intervals
  • INTERVAL Arithmetic with DATE and TIME
  • CAST Using Intervals
  • OVERLAPS
  • System Calendar
9

OLAP Functions

  • On-Line Analytical Processing (OLAP) Functions
  • OLAP Functions
  • Cumulative Sum Using the CSUM Function
  • Cumulative Sum with Reset Capabilities
  • Using CSUM and GROUP BY
  • Generating Sequential Numbers with CSUM
  • Moving Sum Using the MSUM Function
  • Moving Sum with Reset Capabilities
  • Using MSUM and GROUP BY
  • Moving Average Using the MAVG Function
  • Moving Average with Reset Capabilities
  • Using MAVG and GROUP BY
  • Moving Difference Using the MDIFF Function
  • Moving Difference with Reset Capabilities
  • Using MDIFF and GROUP BY
  • Cumulative and Moving SUM Using SUM / OVER
  • Cumulative Sum with Reset Capabilities
  • SUM Using SUM / OVER and PARTITION BY
  • Moving Average Using AVG / OVER
  • Moving Average with Reset Capabilities
  • Using AVG and OVER / ROWS and PARTITION BY
  • Moving Linear Regression Using the MLINREG Function
  • Categorizing Data Using the QUANTILE Function
  • QUALIFY to Find Products in the top Partitions
  • Ranking Data using RANK
  • QUALIFY to Find Top Best or Bottom Worse
  • RANK with Reset Capabilities
  • Using RANK with GROUP BY
  • Ranking Data using RANK / OVER
  • QUALIFY to Find Top Best or Bottom Worse
  • RANK with Reset Capabilities
  • Using RANK/OVER with PARTITION BY
  • Internal RANK operations
  • Percentage of Total Rows Using PERCENT_RANK / OVER
  • Percent Rank with Reset Capabilities
  • Using PERCENT_RANK OVER and PARTITION BY
  • Counting of the Total Rows Using COUNT / OVER
  • COUNT OVER with Reset Capabilities
  • Using COUNT OVER and PARTITION BY
  • Finding the Largest Value Using MAX / OVER
  • Finding the Smallest Value Using MIN / OVER
  • Numbering of the Rows Using ROW_NUMBER / OVER
  • Sampling Rows using the SAMPLE Function
  • RANDOM Number Generator Function
10

SET Operators

  • Set Operators
  • Considerations for Using Set Operators
  • INTERSECT
  • UNION
  • EXCEPT
  • MINUS
  • Using Multiple Set Operators in a Single Request
11

Data Manipulation

  • Data Maintenance
  • Considerations for Data Maintenance
  • Safeguards
  • INSERT Command
  • Using Null for DEFAULT VALUES
  • INSERT / SELECT Command
  • Fast Path INSERT / SELECT
  • UPDATE Command
  • Fast Path UPDATE
  • DELETE Command
  • Fast Path DELETE
  • UPSERT
  • MERGE
  • ANSI Vs Teradata Transactions
  • Performance Issues with Data Maintenance
  • Impact of FALLBACK on Row Modification
  • Impact of PERMANENT JOURNAL Logging on Row Modification
  • Impact of Primary Index on Row Modification
  • Impact of Secondary Indices on Row Modification
12

View Processing

  • Views
  • Reasons to Use Views
  • Considerations for Creating Views
  • Creating and Using VIEWS
  • TOP Command allows ORDER BY in the View
  • Deleting Views
  • Modifying Views
  • Modifying Rows Using Views
  • DML Restrictions when using Views
  • INSERT using Views
  • UPDATE or DELETE using Views
  • WITH CHECK OPTION
  • Locking and Views
13

Macro Processing

  • Macros
  • CREATE MACRO
  • REPLACE MACRO
  • EXECUTE Macro
  • DROP MACRO
  • Generating SQL from a Macro
14

Transaction Processing

  • What is a Transaction?
  • Locking
  • Transaction Modes
  • Comparison Chart
  • Setting the Transaction Mode
  • Teradata Mode Transactions
  • ANSI Mode Transactions
  • Aborting Teradata Transactions
  • Aborting ANSI Transactions
15

Reporting Totals and Subtotals

  • Totals and Subtotals
  • Totals (WITH)
  • Subtotals (WITH BY)
  • Multiple Subtotals on a Single Break
  • Multiple Subtotal Breaks
16

Data Definition Language

  • Creating Tables
  • Table Considerations
  • Maximum Columns per Table
  • Table Naming Conventions
  • CREATE TABLE
  • Column Data Types
  • Specifying the Database in a CREATE TABLE Statement
  • PRIMARY INDEX considerations
  • CREATE TABLE AS
  • Table Type Specifications of SET VS MULTISET
  • SET and MULTISET Tables
  • Protection Features
  • FALLBACK
  • Permanent Journal
  • BEFORE Journal
  • AFTER Journal
  • Internal Storage Options
  • DATABLOCKSIZE
  • FREESPACE PERCENTAGE
  • QUEUE Tables
  • Partitioned Primary Index (PPI)
  • SQL for Partitioned Primary Index (PPI)
  • Adding and Dropping Partitions
  • Column Attributes
  • Constraints
  • UNIQUE Constraint
  • CHECK Constraint
  • Referential Integrity (RI) Constraint
  • Defining Constraints at the Column level
  • Defining Constraints at the Table Level
  • Utilizing Default Values for a Table
  • Secondary Indices
  • CREATE TABLE to Copy an existing table
  • Altering a Table
  • Modifying the Primary Index Partitioning
  • Revalidating the Primary Index
  • Dropping a Table
  • Dropping a Table versus Deleting Rows
  • Renaming a Table
  • Creating Secondary via CREATE INDEX
  • Join Index
  • Collecting Statistics
  • Hashing Functions
  • HASHROW
  • HASHBUCKET
  • HASHAMP
  • HASHBAKAMP
17

Temporary Tables

  • Temporary Tables
  • Temporary Table Choices
  • Derived Tables
  • Derived Tables Using a Non-Recursive WITH
  • Derived Tables Using a Recursive WITH
  • FROM TABLE UDF Tables
  • Volatile Temporary Tables
  • Global Temporary Tables
  • General Practices for Temporary use Tables
18

Trigger Processing

  • Triggers
  • Terminology
  • Logic Flow
  • CREATE TRIGGER Syntax
  • Row Trigger
  • Statement Trigger
  • BEFORE Trigger
  • AFTER Trigger
  • INSTEAD OF Trigger
  • Cascading Triggers
  • Sequencing Triggers
19

Stored Procedures

  • Teradata Stored Procedures
  • CREATE PROCEDURE
  • Stored Procedural Language (SPL) Statements
  • BEGIN / END Statements
  • Establishing Variables and Data Values
  • DECLARE Statement to Define Variables
  • SET to Assign a Data Value as a Variable
  • Status Variables
  • Assigning a Data Value as a Parameter
  • Additional SPL Statements
  • CALL Statement
  • CASE / END CASE Statement
  • IF / END IF Statement
  • LOOP / END LOOP Statements
  • LEAVE Statement
  • REPEAT / END REPEAT Statement
  • WHILE / END WHILE Statement
  • FOR / END FOR Statements
  • ITERATE Statement
  • Using a Cursor
  • DECLARE CURSOR Statement
  • OPEN CURSOR Statement
  • CLOSE CURSOR Statement
  • FETCH Statement
  • Exception Handling
  • DECLARE HANDLER Statement
  • PRINT Statement
  • DML Statements
  • Using Column and Alias Names
  • Comments and Stored Procedures
  • Commenting on a Stored Procedure
  • On-line HELP for Stored Procedures
  • HELP on a Stored Procedure
  • HELP on Stored Procedure Language (SPL)
  • REPLACE PROCEDURE
  • ALTER PROCEDURE
  • DROP PROCEDURE
  • RENAME PROCEDURE
  • SHOW PROCEDURE
  • Dynamic SQL
  • Considerations When Using Stored Procedures
  • Compiling a Procedure
  • Temporary Directory Usage
20

Temporal Tables

  • Temporal Tables
  • The three types of Temporal Tables
  • Valid Time Temporal Table
  • Transaction Time Temporal Table
  • Bi-Temporal Tables
  • SQL for Temporal Tables
  • Here are your Temporal Table SQL Keywords designed to help query Temporal Tables
  • CURRENT AS OF
  • SEQUENCED
  • NONSEQUENCED
Teradata SQL - Advanced
(Duration : 2 Days)

1

Subquery Processing

  • Correlated subqueries only
2

Joins

  • Joins with correlated subquery (inner and outer joins)
3

Set Operators

  • INTERSECT
  • UNION
  • EXCEPT
4

DML Statements

  • INSERT
  • INSERT / SELECT
  • UPDATE
  • DELETE
5

Data Interrogation

  • CASE only
6

Transaction Processing

  • Transactions defined
  • Locking
  • Multi-statement operations
7

CREATE TABLE

  • Data types
  • Attributes
  • Constraints
  • PPI and Identity columns
  • Secondary index definitions
  • COLLECT STATISTICS
  • Hash functions
8

Temporary Tables

  • Derived tables
  • Volatile tables
  • Global tables as needed
9

Stored Procedures

  • SPL
  • Variables and parameters
  • Single row selection vs multi-row via cursor
  • New SQL version of SELECT
  • Dynamic SQL

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top