Teradata SQL Training

Training Provided by LearnQuest Course Number: CFLQ-105 Duration: 3 days Format: Group On-Site Training, Group Virtual Training, Public Virtual Training Course Description This course is designed to comprehensively cover SQL from basic syntax to stored procedures to SQL programming considerations. It is also fully customizable, and can range from 2-5 days, depending upon your needs. Course Objective Upon completion of the course, students will be able to: a Describe the Teradata architecture a Perform basic and advanced SQL functions Course Audience a IT Professionals Course Prerequisites None

Teradata SQL Training Course Topics

1. The Rules of Data Warehousing
   • Teradata certification
   • A logical view of the Teradata architecture
   • The Parsing Engine (PE)
   • The Parsing Engine in detail
   • The Parsing Engine knows all
   • The Access Module Processors (AMPs)
   • The BYNET
   • A visual for data layout
   • How Teradata handles data access
   • The PE uses statistics to come up with the plan
   • When there are NO statistics collected on a table
   • Teradata understands SQL
   • Teradata maximums
   • Teradata maximums per release
2. SQL Basics
   • Rows and columns
   • The SELECT command
   • The SELECT command with *
   • The WHERE clause
   • The order BY clause
   • Sorting by multiple columns
   • Sorting in descending order
3. Teradata SQL Punctuation
   • Valid Teradata names
   • Punctuation (period .)
   • How to SET your default database
   • Punctuation (comma ,)
   • Punctuation (single quotes )
   • Punctuation (double quotes )
   • Punctuation - placing comments inside the SQL
4. Help, Show, and Explain
   • The HELP command
   • The HELP command continued
   • The SHOW command
   • The EXPLAIN command
5. SELECTING Rows
   • Checking for NULL values with IS NULL
   • Checking for NULL values with IS NOT NULL
   • How will NULL values sort?
   • The DISTINCT command
   • The DISTINCT command with multiple columns
   • Multiple DISTINCT statements in the same SQL
   • The AND operator
   • The OR operator
   • The NOT operator
   • Order of precedence for (), NOT, AND, or OR
   • USING an IN List instead of OR
   • The BETWEEN operator
   • The LIKE operator and percent wildcard
   • The LIKE operator - underscore wildcard _
   • SQL that causes a full table scan (FTS)
6. Aggregates
   • The five aggregates
   • Aggregate example with NULL values
   • Aggregate example answers
   • Aggregates and the GROUP BY statement
   • Non-Aggregates must be grouped
   • Aggregates and the HAVING statement
   • WHERE, GROUP BY and HAVING together
   • Aggregates
   • Query results when a table is empty
7. Subqueries
   • The IN statement (for review)
   • Normal subqueries
   • Normal subqueries using multiple tables
   • Subqueries using multiple tables continued
   • Subqueries use values from the same domain
   • Using subqueries with aggregates
   • Correlated subquery
   • How a correlated subquery runs
   • EXISTS
   • Estimate the number of rows for each query
   • Number of rows returned quiz answers
   • NOT IN returns nothing when NULLS are present
   • NOT EXISTS vs. NOT IN
8. Joins
   • Primary key/ foreign key relationships
   • A join using Teradata syntax
   • A join using ANSI syntax
   • A LEFT OUTER JOIN
   • A LEFT OUTER JOIN (continued)
   • A series of joins (inner)
   • A series of joins (LEFT OUTER)
   • A series of joins (RIGHT OUTER)
   • A series of joins (FULL OUTER)
   • Join types vs. join strategies
   • The key things about Teradata and joins
   • Joins need the joined rows to be on the same AMP
   • Another great join picture
   • Joining tables with matching rows on different AMPs
   • Redistributing a table for join purposes
   • Big table small table join strategy
   • Big table small table duplication
   • Nested join
   • Hash join
   • Exclusion join
   • Product joins
   • Cartesian product join
   • Cross join
   • Self join
   • Adding residual conditions to a join
   • Adding residual conditions to a join (AND)
9. Aliasing, Title, Cast, and Format
   • Title function
   • Title function in BTEQ adds functionality
   • Title function used with distinct
   • Teradata data types
   • CAST function
   • CAST examples
   • CAST examples that FAIL
   • Derived columns
   • Using and ALIAS on a column
   • Formatting a column
   • Trick to make ODBC use the FORMAT command
   • FORMAT options for dates
   • FORMAT separators
   • TIME FORMAT options
   • Date, time, and timestamp FORMAT examples
10. Interrogating Data
    • SUBSTRING
    • SUBSTR
    • Concatenation of character strings
    • Using SUBSTRING and concatenation together
    • CHARACTER vs. VARCHAR
    • The TRIM function
    • CHARACTERS command
    • Output results for multiple commands mixed
    • The POSITION function
    • The INDEX function
    • SUBSTRING and POSITION together
    • COALESCE
    • COALESCE with literals
    • ZEROIFNULL
    • NULLIFZERO
    • NULLIF command
    • The CASE command (valued CASE)
    • The CASE command (searched CASE)
    • Nested CASE statement
11. Temporary Tables
    • Derived tables
    • Derived tables
    • Derived tables continued
    • Multiple columns in a derived table
    • Derived table using with a different format
    • Volatile table
    • Volatile table restrictions
    • Global temporary tables
12. SET Operators
    • INTERSECT
    • INTERSECT example
    • UNION
    • UNION with INSERT SELECT to eliminate transient journal
    • EXCEPT or MINUS
13. Views
    • View basics
    • How to CREATE a view
    • You SELECT from a view
    • Change a view with the keyword REPLACE
    • Drop view
    • Placing aggregates inside a view
    • Using locking for access in views
    • You can UPDATE tables through views
    • Restricting UPDATE rows with check option
14. Macros
    • Macro basics
    • How to create a macro
    • How to execute a macro
    • How to create a macro with input parameters
    • How to change a macro
    • Drop macro
    • Macros that will not work
15. Dates and Times
    • RESERVED words such as DATE and TIME
    • How dates are stored on disk
    • How Teradata displays the date
    • How to change the DATEFORM
    • Teradata dates stored as integers for a reason
    • ADD_MONTHS command
    • EXTRACT command with dates
    • EXTRACT command with TIME
    • The system calendar
    • Using the system calendar for date comparison
    • INTERVAL processing for arithmetic and conversion
    • INTERVAL processing that fails
    • INTERVAL arithmetic with date and time
    • TIMESTAMP
    • CURRENT_TIMESTAMP
16. Creating Tables
    • A simple CREATE statement
    • A simple INSERT statement
    • Creating set tables
    • Creating multiset tables
    • Unique primary index
    • A quick way to copy tables
    • CREATE table options
    • Defining constraints at the column level
    • Defining constraints at the table level
    • Partitioned primary index tables
    • Partitions can eliminate full table scans
    • Partitioning with CASE_N
    • Partitioning with RANGE_N
    • NO CASE, NO RANGE, or UNKNOWN
17. WITH and WITH BY for Totals and Subtotals
    • The WITH statement
    • The WITH BY statement
    • Combining WITH BY and WITH
    • Combining multiple WITH BY statements and WITH
    • Combining multiple WITH BY statements and WITH (continued)
18. Sampling
    • Random sampling number of rows sample
    • Random sampling percentage of the table sample
    • Multiple samples
    • SAMPLE WITH REPLACEMENT
    • SAMPLE
    • SAMPLE WITH REPLACEMENT and RANDOMIZED ALLOCATION together
    • SAMPLE with conditional test using WHEN
    • SAMPLE example that errors
19. Rank and Quantile
    • RANK
    • RANK in ASC order
    • QUALIFY RANK is like a HAVING statement
    • QUALIFY RANK with a GROUP BY
    • QUANTILE function
    • QUANTILE function example using 5
    • QUANTILE function using 100 (percentile)
    • QUANTILE function sorted ASC
    • QUANTILE function with percentile (100)
20. OLAP
    • Cumulative Sum (CSUM)
    • Cumulative Sum (CSUM) with multiple sort keys
    • Cumulative Sum (CSUM) with GROUP BY
    • CSUM to generate sequential numbers
    • CSUM using ANSI SUM OVER
    • ANSI SUM OVER with PARTITION BY for grouping
    • Moving Sum (MSUM)
    • Moving Sum (MSUM) with multiple sort keys
    • Moving Sum (MSUM) with GROUP BY
    • Moving Sum (MSUM) with ANSI SUM OVER
    • MSUM with ANSI SUM OVER and PARTITION BY for grouping
    • Moving Average (MAVG)
    • Moving Average (MAVG) with multiple sort keys
    • Moving Average (MAVG) with GROUP BY
    • MAVG with ANSI AVG OVER
    • MAVG with ANSI AVG OVER and PARTITION BY for Grouping
    • Moving Difference (MDIFF)
    • Moving Difference (MDIFF) with multiple sort keys
    • Moving Difference (MDIFF) with GROUP BY
21. New V2R5. 1 and V2R6 Features
    • New GROUP BY specifications
    • Original GROUP BY Example
    • GROUPING SETS
    • CUBE
    • ROLLUP
    • TOP ROWS OPTION
    • QUEUE Tables
    • FROM TABLE UDF TAbles
22. Miscellaneous
    • Single row MERGE INTO command
    • Compression
    • Implementing compression
    • How compression works
    • Teradata and ANSI mode
    • Teradata mode transactions (Called BTET)
    • ANSI mode transactions
    • SQRT function
    • INSERT/ SELECT on two exact tables
    • INSERT/ SELECT on tables that don t match
    • Triggers
    • Row triggers or statement triggers
    • Trigger examples
    • ORDERING multiple triggers in a sequence
    • Trigger enable or disable with ALTER trigger
23. Stored Procedures
    • Stored procedures
    • CREATE procedure
    • Nesting BEGIN and END statements
    • Passing a stored procedure parameters
    • An example of all three parameters
    • DECLARE and SET
    • ELSEIF for speed
    • The scoop is the LOOP and it LEAVES like a tree
    • The WHILE and END WHILE

About The Training Provider: LearnQuest