title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Teradata SQL
Duration
3 Days
Rating
Course
CFLQ-105
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Teradata SQL course is designed to comprehensively cover SQL from basic syntax to stored procedures to SQL programming considerations.This course can be taught using Version 12, 13, or 14. Please inform your sales representative of which version you are currently using. It is also fully customizable, and can range from 2-5 days, depending upon your needs.
Objectives
Upon completion of the Teradata SQL course, students will be able to:
- Describe the Teradata architecture
- Perform basic and advanced SQL functions
Audience
- IT Professionals
Prerequisites
- None
Topics
- 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 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
- 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 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
- CASESPECIFIC
- LOWER Function
- UPPER Function
- 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
- 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
- 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
- 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
- Transforming Character Data
- CHARACTERS Function
- CHARACTER_LENGTH Function
- OCTET_LENGTH Function
- TRIM
- SUBSTRING
- SUBSTR
- SUBSTRING and Numeric Data
- POSITION
- INDEX
- SUBSTRING and POSITION or INDEX Used Together
- Concatenation of Character Strings
- 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
- Set Operators
- Considerations for Using Set Operators
- INTERSECT
- UNION
- EXCEPT
- MINUS
- Using Multiple Set Operators in a Single Request
- 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
- Data Interrogation
- NULLIFZERO
- NULLIF
- ZEROIFNULL
- COALESCE
- CASE
- Flexible Comparisons within CASE
- Comparison Operators within CASE
- CASE for Horizontal Reporting
- Nested CASE Expressions
- CASE used with the other DML
- Using CASE to avoid a join
- 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
- Macros
- CREATE MACRO
- REPLACE MACRO
- EXECUTE Macro
- DROP MACRO
- Generating SQL from a Macro
- 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
- Totals and Subtotals
- Totals (WITH)
- Subtotals (WITH BY)
- Multiple Subtotals on a Single Break
- Multiple Subtotal Breaks
- 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
- 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
- GLOBAL Temporary Table Examples
- General Practices for Temporary use Tables
- Triggers
- Terminology
- Logic Flow
- CREATE TRIGGER Syntax
- Row Trigger
- Statement Trigger
- BEFORE Trigger
- AFTER Trigger
- INSTEAD OF Trigger
- Cascading Triggers
- Sequencing Triggers
- 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 in a Stored Procedure
- 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
- Temporal Tables
- There are three types of Temporal Tables:
- Valid Time Temporal Table
- Temporal Table Quiz
- 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
Related Courses
-
Teradata Basics
CFLQ-100- Duration: 1 Day
- Delivery Format: Classroom Training, Online Training
- Price: 765.00 USD
-
Teradata Application Development
CFLQ-130- Duration: 3 Days
- Delivery Format: Classroom Training, Online Training
- Price: 2,295.00 USD
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
??spvc-wbt-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Nothing yet
Purchase Information
??elearning-coursenumber?? ??coursename??
View Cart