Chapter 27 – Lessons with Tera-Tom Video Guide
Lessons with Tera-Tom
Teradata Architecture and SQL Video Series
These exciting videos make learning and certification much easier
Three ways to view them:
4. Safari (look up Coffing Studios)
5. CoffingDW.com (sign-up on our website)
6. Your company can buy them all for everyone to see (contact [email protected])
Video 1 - Teradata Basics - The Architecture
"Teradata Basics 1- Teradata Architecture" introduces the basic foundations so users can understand the roles of the Parsing Engine, Bynet, and the AMPs. This video will also make sure that users know how the hardware is configured in an SMP and MPP configuration. Finally, users will understand the fundamentals behind the primary index and its importance in distributing and retrieving data. This video sets the stage as the foundation for learning Teradata. Viewers who watch all the videos will have extensive knowledge on Teradata.
Objectives
After viewing this video, students will be able to:
•Explain the 3 major components of the Teradata Architecture
•Recognize the fastest and slowest queries on Teradata
•Discuss linear scalability
•Know the difference between an SMP and MPP system
Table of Contents
•What is Parallel Processing?
•The Basics of a Single Computer
•Teradata Parallel Processes Data
•Parallel Architecture
•The Teradata Architecture
•All Teradata Tables are spread across ALL AMPS
•Teradata Systems can Add AMPs for Linear Scalability
•Understand that Teradata can scale to incredible size
•AMPs and Parsing Engines (PEs) live inside SMP Nodes
•Each Node is attached via a Network to a Disk Farm
•Two SMP Nodes Connected Become One MPP System
•There are Many Nodes in a Teradata Cabinet
•Inside a Teradata Node
•The Boardless BYNET and the Physical BYNET
•This is the Visual You Want to Know in order to Understand Teradata
•The Primary Index is defined when the table is CREATED
•A Unique Primary Index (UPI)
•Primary Index in the WHERE Clause - Single-AMP Retrieve
•Using EXPLAIN
•A Non-Unique Primary Index (NUPI)
•Primary Index in the WHERE Clause - Single-AMP Retrieve
•Using EXPLAIN in a NUPI Query
•A conceptual example of a Multi-Column Primary Index
•Primary Index in the WHERE Clause - Single-AMP Retrieve
•A conceptual example of a Table with NO PRIMARY INDEX
•A Full Table Scan is likely on a table with NO Primary Index
•An EXPLAIN that shows a Full Table Scan
•Table CREATE Examples with four different Primary Indexes
•What happens when you forget the Primary Index?
Video 2 - Hashing the Primary Index
This video shows how Teradata hashes the primary index to distribute the data for all of the primary index options. Understanding how Teradata distributes the data gives viewers and opportunity to see the data and understand how to performance tune queries when speed is the need.
Objectives
After viewing this video, students will be able to:
•Describe how Teradata lays out data among the AMPs
•Describe how each row has a row ID
•Explain how Teradata’s sorting by Row ID helps with a binary search
•List how UPI, NUPI, Multi-Column, and NOPI are used when defining tables
Table of Contents
•The Hashing Formula Facts
•The Hash Map Determines which AMP will own the Row
•The Hash Map Determines which AMP will own the Row
•Placing rows on the AMP
•Placing rows on the AMP Continued
•A Review of the Hashing Process
•Non-Unique Primary Indexes have Skewed Data
•The Uniqueness Value
•The Row Hash and Uniqueness Value make up the Row-ID
•A Row-ID Example for a Unique Primary Index
•A Row-ID Example for a Non-Unique Primary Index (NUPI)
•Two Reasons why each AMP Sorts their rows by the Row-ID
•AMPs sort their rows by Row-ID to Group like Data
•AMPs sort their rows by Row-ID to do a Binary Search
•Table CREATE Examples with four different Primary Indexes
•Null Values all Hash to the Same AMP
•A Unique Primary Index (UPI) Example
•A Non-Unique Primary Index (NUPI) Example
•A Multi-Column Primary Index Example
•A No Primary Index (NoPI) Example
Video 3 - The Cold Hard Teradata Facts
This chapter shows how data is stored in data blocks and how that data is processed. It also introduces synchronized scan and intelligent memory for better performance. This brilliant video shows viewers the realities of how Teradata processes data and additional Teradata options for maximum performance.
Objectives
After viewing this video, students will be able to:
•Describe how data is stored in blocks
•Understand how moving blocks from disk to memory is a burden
•Understand how the master index shows the cylinder information
•Understand how the cylinder index shows the data block information
•Describe a synchronized scan
•Be able to explain in detail Intelligent Memory with V14.10
Table of Contents
•All Teradata Tables are spread across All AMPs
•The Table Header and the Data Rows are Stored Separately
•An AMP Stores the Rows of a Table inside a Data Block
•To Read a Data Block, an AMP Moves the Block into Memory
•Nothing is done on disk and everything is done in Memory
•Most Taxing thing for an AMP is Moving Blocks into Memory
•A Full Table Scan Means All AMPs must Read All Rows
•The “Achilles Heel” and slowest process is Block Transfer
•Each Table has a Primary Index
•A Query Using the Primary Index is a Single AMP Retrieve.
•As Rows are added a Data Block will Eventually Split
•A Full Table Scan Means All AMPs must Read All Blocks
•A Primary Index Query uses a Single AMP and Single Block
•Each AMP Can Have Many Blocks for a Single Table
•A Full Table Scan Means All AMPs must Read All Blocks
•Quiz – How Many Blocks Move into FSG Cache?
•Answer – How Many Blocks Move into FSG Cache?
•Quiz – How Many Blocks Move Using the Primary Index?
•Answer – How Many Blocks Move Using the Primary Index?
•Synchronized Scan (Sync Scan)
•EXPLAIN Using a Synchronized Scan
•Intelligent Memory (Teradata V14.10)
•Teradata V14.10 Intelligent Memory Gives Data a Temperature
•Data deemed Very Hot stays in each AMP's Intelligent Memory
•Intelligent Memory Stays in Memory
Video 4 -Inside the Amps’ Disc
This video shows how data is stored in blocks and how the blocks are stored in cylinders. Each AMP has a master index and a cylinder index to quickly find their data, and the cylinders are given temperatures so the most popular data is in the hottest cylinders.
Objectives
After viewing this video, students will be able to:
•Understand how blocks are stored in cylinders
•Describe how the row reference array performs a binary search
•Explain how blocks split amongst an AMP
•Understand the concept of hot, warm, and cold cylinders
•Describe the difference between a full table scan and an index lookup
Table of Contents
•Rows are Stored in Data Blocks which are stored in Cylinders
•An AMP's rows are stored inside a Data Block in a Cylinder
•An AMP’s Master Index is used to find the Right Cylinder
•The Row Reference Array (RRA) Does the Binary Search?
•A Block Splits into Two Blocks at Maximum Block Size
•Data Blocks Maximum Block Size has Changed (V14.10)
•The New Block Split with Teradata V14.10
•The Block Split with Even More Detail in Teradata V14.10
•Teradata V14.10 Block Split Defaults
•There is One Master Index and Thousands of Cylinder Indexes
•Blocks Continue to Split as Tables Grow Larger
•FYI – Some Advanced Information about Data Block Headers
•A top down view of Cylinders
•There are Hot, Warm, and Cold Cylinders
•Cylinders are used for Perm, Spool, Temp, and Journals
•Each AMP has Their Own Master Index
•Each Cylinder on an AMP has a Cylinder Index
•Quiz – What Two Things Does and AMP Read?
•Answer – What Two Things Does and AMP Read?
•Quiz – How Many Row Reference Arrays do you see?
•Answer – How Many Row Reference Arrays do you see?
•Quiz – How Many Row Reference Arrays are there Now?
•Answer – How Many Row Reference Arrays do you see?
•Quiz – How Many Row Reference Arrays in Total?
•Answer – How Many Row Reference Arrays in Total?
•Quiz – How Many Cylinder Indexes are here?
•Answer – How Many Cylinder Indexes are here?
•A More Detailed Illustration of the Master Index
•A Real-World View of the Master Index
•An Even More Realistic View of an AMP’s Master Index
•The Cylinder Index
•An Even More Realistic View of a Cylinder Index
•How a Query using the Primary Index works
•How the AMPs Do a Full Table Scan
•How an AMP Reads Using a Primary Index
Video 5 - PPI (Partitioned Primary Index Tables)
Partitioned primary index tables (PPI TABLES) are designed to horizontally partition tables in order to prevent full table scans. Viewers will understand all aspects of creating a partitioned table, how the data is laid out, and how this speeds up retrieval.
Objectives
After viewing this video, students will be able to:
•Explain the 3 different options for partitioning a table
•Learn how to utilize multi-level partitioning
•Learn how to create PPI tables
•Be able to alter a PPI table
•Utilize reports to learn more about a partitioned table
Table of Contents
•The Concept behind Partitioning a Table
•Creating a PPI Table with Simple Partitioning
•A Visual Display of Simple Partitioning
•An SQL Example that explains Simple Partitioning
•Creating a PPI Table with RANGE_N Partitioning per Month
•A Visual of One Year of Data with Range_N per Month
•An SQL Example explaining Range_N Partitioning per Month
•A Partition # and Row-ID = Row Key
•An AMP Stores its Rows Sorted in only Two Different Ways
•Creating a PPI Table with RANGE_N Partitioning per Day
•A Visual of Range_N Partitioning Per Day
•An SQL Example that explains Range_N Partitioning per Day
•Creating a PPI Table with RANGE_N Partitioning per Week
•A Visual of Range_N Partitioning Per Week
•SQL Example that explains Range_N Partitioning per Week
•Creating a PPI Table with CASE_N
•A Visual of Case_N Partitioning
•An SQL Example that explains CASE_N Partitioning
•How many partitions do you see?
•Number of PPI Partitions Allowed
•How many partitions do you see?
•NO CASE and UNKNOWN Partitions Together
•A Visual of Case_N Partitioning
•Combining Older Data and Newer Data in PPI
•A Visual for Combining Older Data and Newer Data in PPI
•The SQL on Combining Older Data and Newer Data in PPI
•Multi-Level Partitioning Combining Range_N and Case_N
•A Visual of Multi-Level Partitioning
•The SQL on a Multi-Level Partitioned Primary Index
•NON-Unique Primary Indexes (NUPI) in PPI
•PPI Table with a Unique Primary Index (UPI)
•Tricks for Non-Unique Primary Indexes (NUPI)
•Character Based PPI for RANGE_N
•A Visual for Character-Based PPI for RANGE_N
•The SQL on Character-Based PPI for RANGE_N
•Character-Based PPI for CASE_N
•Dates and Character-Based Multi-Level PPI
•TIMESTAMP Partitioning
•Using CURRENT_DATE to define a PPI
•ALTER to CURRENT_DATE the next year
•ALTER to CURRENT_DATE with Save
•Altering a PPI Table to Add or Drop Partitions
•Deleting a Partition
•Deleting a Partition and saving its contents
•Using the PARTITION Keyword in your SQL
•SQL for CASE_N
Video 6 Teradata Columnar
Columnar Tables are Teradata’s way of vertically partitioning tables. Viewers will learn how to create a columnar table and how to populate it. Finally, viewers will learn when they should choose to lay out tables in a columnar design.
Objectives
After viewing this video, students will be able to:
•Describe NOPI Tables and their functions
•Create a columnar table
•Discuss the differences between a columnar table and a row based table
•Understand compression options
•Gain knowledge of when and when not to use columnar tables
Table of Contents
•Columnar Tables have NO Primary Index
•This is NOT a NoPI Table
•NoPI Tables Spread rows across all-AMPs Evenly
•NoPI Tables used as Staging Tables for Data Loads
•NoPI Table Capabilities
•NoPI Table Restrictions
•What does a Columnar Table look like?
•Comparing Normal Table vs. Columnar Tables
•Columnar Table Fundamentals
•Example of Columnar CREATE Statement
•Columnar can move just One Container to Memory
•Containers on AMPs match up perfectly to rebuild a Row
•Indexes can be used on Columns (Containers)
•Indexes can be used on Columns (Containers)
•Visualize a Columnar Table
•Single-Column vs. Multi-Column Containers
•Comparing Normal Table vs. Columnar Tables
•Columnar Row Hybrid CREATE Statement
•Columnar Row Hybrid Example
•Columnar Row Hybrid Query Example
•Review of Row-Based Partition Primary Index (PPI)
•Visual of Row Partitioning (PPI Tables) by Month
•CREATE Statement for both Row and Column Partition
•Visual of Row Partitioning (PPI Tables) and Columnar
•How to Load into a Columnar Table
•Columnar NO AUTO COMPRESS
•Auto Compress in Columnar Tables
•Auto Compress Techniques in Columnar Tables
•When and When NOT to use Columnar Tables
Video 7- Space
This chapter will explain perm space, spool space, and temp space and how the DBC will begin to create the user and database environment.
Objectives
After viewing this video, students will be able to:
•Understand the hierarchical nature of Teradata
•Describe the 3 types of space
•Gain a thorough knowledge of spool space
•Know the difference between a database and a user
•Know the objects that take up PERM space
Table of Contents
•When your System Arrives, there is only User named DBC
•First Assignment is to create another User just under DBC
•USER DBC
•Perm and Spool Space
•Perm Space is for Permanent Tables
•Spool Space is work space that builds a User’s Answer Sets
•Spool Space is in an AMP’s Memory and on its Disk
•Users are Assigned Spool Space Limits
•What is the Purpose of Spool Limits?
•Why did my query Abort and say “Out of Spool”?
•How can Skewed Data cause me to run “Out of Spool”?
•Why did my Join cause me to run “Out of Spool”?
•Finding out how much Space you have
•Space per AMP on all tables in a Database shows Skew
•What does my system look like when it first arrives?
•DBC owns all the PERM Space in the system on day one
•DBC’s First Assignment is Spool Space
•DBC’s 2nd Assignment is to CREATE Users and Databases
•The Teradata Hierarchy Begins
•The Teradata Hierarchy Continues
•Differences between PERM and SPOOL
•Databases, Users, and Views
•What are Similarities between a DATABASE and a USER?
•What is the Difference between a DATABASE and a USER?
•Objects that take up PERM Space
•A Series of Quizzes on Adding and Subtracting Space
•Answer 1 to Quiz on
•Space Transfer Quiz
•Answer to Space Transfer Quiz
Video 8 - How Teradata Joins Tables Together
This video will show how Teradata performs joins inside the Teradata engine. Viewers will learn how to tune their joins for maximum performance. This video provides insight into the keys to make joining tables a much faster process. The information in this video is absolutely critical for any Teradata user.
Objectives
After viewing this video, students will be able to:
•Identify key concepts to know how Teradata performs joins
•Understand key words Redistribution and Duplication in a join explain
•Understand how to tune their joins for better performance
•Create volatile and global temporary tables
•Understand the physical operations needed to perform a join
Table of Contents
•Teradata Join Quiz
•Teradata Join Quiz Answer
•The Joining of Two Tables
•Teradata Moves Joining Rows to the Same AMP
•Imagine Joining Two NoPI Tables that have No Primary Index
•Both Tables are redistributed to Join Rows on the Same AMP
•How do you join if One Table is Big and One Table is Small?
•Duplicate the Small Table on Every AMP (like a mirror)
•What Could You Do If Two Tables Joined 1000 Times a Day?
•Joining Two Tables with the same PK/FK Primary Index
•A Join with No Redistribution or Duplication
•A Performance Tuning Technique for Large Joins
•The Joining of Two Tables with an Additional WHERE Clause
•An Example of the Fastest Join Possible
•Using a Simple Volatile Table
•A Volatile Table with a Primary Index
•Using a Simple Global Temporary Table
•Two Brilliant Techniques for Global Temporary Tables
•The Joining of Two Tables Using a Global Temporary Table
•Quiz – How Much Data Moves Across the BYNET?
•Answer – How Much Data Moves Across the BYNET?
•Teradata V14.10 Join Feature PRPD
Video 9- Protection Features (1/3)
This video will cover the protection features of the transient journal, fallback, and RAID.
Objectives
After viewing this video, students will be able to:
•Describe the transient journal process
•Create tables with fallback
•Understand the positives and negatives of fallback
•Have a strong understanding of RAID mirroring
Table of Contents
•A List of the Protection Features
•Transient Journal Protects the Transaction Integrity
•The Transient Journal in Action
•A Single Transaction could Involve All AMPs
•The Secret to turning off the Transient Journal
•The Transient Journal’s Write Ahead Logging (WAL)
•A Node with 40 AMPs and 40 Dedicated FSG Caches
•The Transient Journal’s Write Ahead Logging (WAL)
•Working Example of the Write Ahead Log (WAL)
•The First Step in our Example of the Write Ahead Log (WAL)
•The Second Step in our Example of the Write Ahead Log
•The Third Step in our Example of the Write Ahead Log
•The Fourth Step in our Example of the Write Ahead Log
•The Last Step in our Example of the Write Ahead Log
•Fallback to Protect against an AMP Failure
•Fallback Clusters
•AMPs in a Cluster are Physically Separated
•The Reason AMPs in a Cluster are Physically Separated
•The Price you pay for Fallback
•How to Create a Table with Fallback
•How to Create a Table with No Fallback
•How to Alter a Table to Add or Drop Fallback
•What is a Virtual Disk?
•Why do AMPs each have Four Physical Disks?
•Is a Mirror just like Looking into a Mirror?
•RAID 1 Mirroring – Redundant Array of Independent Disks
•What does RAID Protect?
•How Does RAID Fail?
•Do RAID and Fallback have a Connection?
Video 10- Protection Features (2/3)
This video introduces cliques and then provides a series of 16 quizzes to review transient journals, fallback, RAID, and cliques.
Objectives
After viewing this video, students will be able to:
•Define a clique
•Understand the difference between 2 node and 4 node cliques
•Describe the difference between clusters and cliques
•Be challenged with quizzes on transient journals, fallback, clusters, and cliques
Table of Contents
•What is a Clique?
•If a Node goes down the AMPs migrate within the Clique?
•Does Teradata Reset during a Node Failure?
•Four Node Cliques
•Migrating AMPs in Four Node Cliques
•The Hot Spare Node
•The Hot Spare Node in Action
•With a Hot Spare a Second Teradata Reset isn’t Needed
•A Node, It’s AMPs and their Disks
•How Cliques are Physically Defined
•Cliques are cabled so Migrating AMPs can access their Disks
•A Review of Fallback and Clusters
•An Example of Fallback and Clusters
•Quiz 1 – How Many Clusters do you see?
•Quiz 1 Answer – How Many Clusters do you see?
•Quiz 2 – How Many Cliques do you see?
•Quiz 2 Answer – How Many Cliques do you see?
•Quiz 3 – What have we lost? Multiple Choice Answer
•Quiz 3 Answer – What have we lost? Multiple Choice Answer
•Quiz 4 – What have we lost? Multiple Choice Answer
•Quiz 4 Answer – What have we lost? Multiple Choice Answer
•Quiz 5 – What have we lost? Which Answer is False?
•Quiz 5 Answer – What have we lost? Which Answer is False?
•Quiz 6 – What have we lost? Pick Two True Answers
•Quiz 6 Answer – What have we lost? Pick Two True Answers
•Summary of the facts for Fallback, Clusters, and Cliques
•Quiz 7 –How Many Virtual Disks (Vdisks) are in this System?
•Quiz 7 Answer –How Many Virtual Disks are in this System?
•Quiz 8 –How Many Physical Disks are in this System?
•Quiz 8 Answer–How Many Physical Disks are in this System?
•Quiz 9 – How Many Transient Journals in this System?
•Quiz 9 Answer –How Many Transient Journals in this System?
•Quiz 10 – How Many Transient Journals are Open?
•Quiz 10 Answer – How Many Transient Journals are Open?
•Quiz 11 – How Much Space?
•Quiz 11 Answers – How Much Space?
•Quiz 12 – How Much Space with Fallback?
•Quiz 12 Answers – How Much Space with Fallback?
•Quiz 13 – How Many Disks could we lose with RAID 1?
•Quiz 13 Answer – How Many Disks could we lose?
•Quiz 14 – How Many Disk losses could Kill Us?
•Quiz 14 Answer – How Many Disk losses could Kill Us?
•Quiz 15 – How Many AMPs could we lose if Lucky?
•Quiz 15 Answer – How Many AMPs could we lose if Lucky?
•Quiz 16 – How Many AMPs could we lose if Unlucky?
•Quiz 16 Answer – How Many AMPs could we lose Unlucky?
Video 11- Protection Features (3/3)
This video covers the permanent journal features of Teradata as well as the archive recovery.
Objectives
After viewing this video, students will be able to:
•Describe the permanent journal
•List the differences between the transient and permanent journals
•Understand the concept of full systems backups in conjunction with after journals
•Look up information about journals in the data dictionary
Table of Contents
•The Permanent Journal
•Difference between the Transient and the Permanent Journal
•Difference Between the Before and After Permanent Journal
•Full System Backup compared to an After Journal
•How Full System Backups work with the After Journal
•The Many Different Permanent Journal Options
•Where is the Permanent Journal Stored?
•Using Common Sense about Journal Locations
•After Journals are Never stored in the Same Node or Clique
•What is a Dual After Journal?
•What is a Dual Before Journal?
•What is a Journal?
•Creating a Table with Fallback and a Before and After Journal
•Does Fallback Affect a Permanent Journal?
•Permanent Journal Rules
•Example 1: Permanent Journal Scenarios to Test the Rules
•Example 2: Permanent Journal Scenarios to Test the Rules
•Example 3: Permanent Journal Scenarios to Test the Rules
•How to Create Database with a Permanent Journal
•Creating Tables under different Journal Circumstances
•Permanent Journal’s Three Main Areas
•The Current Journal consists of the Active and Saved Areas
•Permanent Journal Commands
•Deleting a Permanent Journal
•Some Great Advice for Maintaining the Permanent Journals
•Recovery Using the Permanent Journals
•The Journals View in DBC (DBC.Journals)
•Archive Recovery Console (ARC)
•Reasons You Might Utilize ARC
•ARC raising the BAR (Backup Archive Restore)
•ARC Commands in Alphabetical Order
Video 12- Collect Statistics (1 of 2)
This video is designed to show the fundamentals of the collect statistics process, what to collect on, and the new v14 way to collect statistics.
Objectives
After viewing this video, students will be able to:
•Collect statistics
•Understand the purpose of collecting statistics
•Describe a statistics histogram
•List what columns or indexes statistics should be collected on
•Collect and recollect statistics a newer way in Teradata V14
Table of Contents
•The Teradata Parsing Engine (Optimizer) is Cost Based
•The Purpose of Collect Statistics
•When Teradata Collects Statistics it creates a Histogram
•The Interval of the Collect Statistics Histogram
•Histogram Quiz
•Answers to Histogram Quiz
•What to COLLECT STATISTICS On?
•Why Collect Statistics?
•How do you know if Statistics were collected on a Table?
•A Huge Hint that No Statistics Have Been Collected
•The Basic Syntax for COLLECT STATISTICS
•COLLECT STATISTICS Examples for a better Understanding
•The New Teradata V14 Way to Collect Statistics
•Where Does Teradata Keep the Collected Statistics?
•The Official Syntax for COLLECT STATISTICS
•How to Recollect STATISTICS on a Table
Video 13- Collect Statistics (2 of 2)
This chapter will show some of the more advanced features of collecting statistics such as sample and extrapolation. This video will also show how random AMP sampling works.
Objectives
After viewing this video, students will be able to:
•Describe a random AMP sample
•Copy a table and statistics
•Collect statistics using a sample
•Understand Teradata V14 statistics enhancements
Table of Contents
•Teradata Always Does a Random AMP Sample
•Random Sample is kept in the Table Header in FSG Cache
•Multiple Random AMP Samplings
•How a Random AMP gets a Table Row count
•Random AMP Estimates for NUSI Secondary Indexes
•USI Random AMP Samples are Not Considered
•There’s No Random AMP Estimate for Non-Indexed Columns
•The PE’s Plan if No Statistics Were Collected?
•Stale Statistics Detection and Extrapolation
•Extrapolation for Future Dates
•How to Copy a Table with Data and the Statistics?
•How to Copy a Table with NO Data and the Statistics?
•COLLECT STATISTICS Directly From another Table
•When to COLLECT STATISTICS Using only a SAMPLE
•Examples of COLLECT STATISTICS Using only a SAMPLE
•Examples of COLLECT STATISTICS For V14
•How to Collect Statistics on a PPI Table on the Partition
•Teradata V12 and V13 Statistics Enhancements
•Teradata V14 Statistics Enhancements
•Teradata V14 Summary Statistics
•Teradata V14 MaxValueLength
•Teradata V14 MaxIntervals
•Teradata V14 Sample N Percent
•Teradata V14.10 Statistics Collection Improvements
Video 14- Locking
This video is designed to show you all aspects of how Teradata locks tables and rows. It will also provide insight into the explain command.
Objectives
After viewing this video, students will be able to:
•List the 4 major locks of Teradata
•Describe the 3 levels of locking
•Utilize an access lock for speed
•Gain knowledge on pseudo tables
•Describe in detail how Teradata handles locks
Table of Contents
•The Four Major Locks of Teradata
•The Read Lock
•The Read Lock and Joins
•The Write Lock
•The Exclusive Lock
•The Three Levels of Locking
•Locking at the Row Hash Level
•Locking at the Table Level
•Locking at the Database Level
•The Ongoing Battle between Read and Write Locks
•Compatibility between Read Locks
•Why Read Locks Wait on Write Locks
•Why Write Locks Wait on Read Locks
•The Access Lock is Different from the Other Locks
•What is the Purpose of an Access Lock?
•Locking Modifiers - Locking Row, Table or Database
•All Views should consider the Locking for Access Statement
•What is a Dead Lock or a Deadly Embrace?
•Pseudo Tables are designed to minimize Dead Locks
•Pseudo Tables are referenced in the Explain Plan
•Incompatible Locks Wait on each Other
•The Checksum Lock of Teradata
•The Nowait Option for Locking
•The Automatic Locking for Access Button inside Nexus
•Viewpoint Lock Viewer
•Viewpoint Lock Viewer Lets You Configure Your View
•What is a Host Utility (HUT) Lock?
Video 15- Temporal Tables
This chapter details temporal tables, how to create temporal tables, and additional SQL to query them. It also describes the inner workings of Teradata for temporal tables.
Objectives
After viewing this video, students will be able to:
•Describe the 3 types of temporal tables
•Create a bi-temporal table
•Utilize special SQL to see closed rows
•Create views for temporal tables
•Describe a period data type
Table of Contents
•Three types of Temporal Tables
•CREATING a Bi-Temporal Table
•PERIOD Data Types
•Bi-Temporal Data Type Standards
•Bi-Temporal Example – Tera-Tom buys!
•A Look at the Temporal Results
•Bi-Temporal Example – Tera-Tom Sells!
•Bi-Temporal Example – How the data looks!
•Normal SQL for Bi-Temporal Tables
•NONSEQUENCED SQL for Temporal Tables
•AS OF SQL for Temporal Tables
•NONSEQUENCED for Both
•Creating Views for Temporal Tables
•Bi-Temporal Example – Socrates is DELETED!
•Bi-Temporal Results – Socrates is DELETED
Video 16- Join Indexes
This video describes how to create single table, multi-table, and aggregate join indexes as well as more advanced join index concepts.
Objectives
After viewing this video, students will be able to:
•Create a single table join index, multi-table join index, and an aggregate join index
•Understand additional indexing options for join indexes
•Compress a join index
Table of Contents
•Creating a Multi-Table Join Index
•Visual of a Join Index
•Outer Join Multi-Table Join Index
•Visual of a Left Outer Join Index
•Compressed Multi-Table Join Index
•A Visual of a Compressed Multi-Table Join Index
•Creating a Single-Table Join Index
•Conceptual of a Single Table Join Index on an AMP
•Single Table Join Index Great For LIKE Clause
•Single Table Join Index with Value Ordered NUSI
•Aggregate Join Indexes
•Compressed Single-Table Join Index
•Aggregate Join Index
•New Aggregate Join Index (Teradata V14.10)
•Sparse Join Index
•A Global Multi-Table Join Index
•Creating a Hash Index
•Join Index Details
Teradata SQL Video 1 - The Basics of SQL
"Teradata SQL 1- The Basics of SQL" introduces the fundamentals of SQL.
Objectives
After viewing this video, students will be able to:
•Write a basic Select statement
•Sort an answer set
•Describe a Major and Minor Sort
•Understand how to alias a column
•Comment out single or multiple lines
Table of Contents
•Introduction
•SELECT * (All Columns) in a Table
•SELECT Specific Columns in a Table
•Using the Best Form for Writing SQL
•Commas in the Front or in the Back?
•Place your Commas in front for better Debugging Capabilities
•Sort the Data with the ORDER BY Keyword
•ORDER BY Defaults to Ascending
•Use the Name or the Number in your ORDER BY Statement
•Two Examples of ORDER BY using Different Techniques
•Changing the ORDER BY to Descending Order
•NULL Values sort First in Ascending Mode (Default)
•NULL Values sort Last in Descending Mode (DESC)
•Major Sort vs. Minor Sorts
•Multiple Sort Keys using Names vs. Numbers
•Sorts are Alphabetical, NOT Logical
•Using A CASE Statement to Sort Logically
•How to ALIAS a Column Name
•A Missing Comma can by Mistake become an Alias
•The Title Command and Literal Data
•Comments using Double Dashes are Single Line Comments
•Comments for Multi-Lines
•Comments for Multi-Lines as Double Dashes per Line
•A Great Technique for Comments to Look for SQL Errors
Teradata SQL Video 2 – Building Your SQL Knowledge
This video teaches viewers about some key commands including: Distinct vs. Group By and Top command vs. Sample.
Objectives
After viewing this video, students will be able to:
•Fully utilize Between and Like statements
•Know the difference between Character and Varchar data
•Utilize the Trim command
•Utilize the Top command
•Understand all options for using Sample
Table of Contents
•BETWEEN is Inclusive
•BETWEEN Works for Character Data
•LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
•LIKE command Underscore is Wildcard for one Character
•LIKE ALL means ALL conditions must be Met
•LIKE ANY means ANY of the Conditions can be Met
•IN ANSI Transaction Mode Case Matters
•In Teradata Transaction Mode Case Doesn’t Matter
•LIKE Command Works Differently on Char Vs. Varchar
•Troubleshooting LIKE Command on Character Data
•Introducing the TRIM Command
•Quiz – Which Data is Left Justified and which is Right?
•Numbers are Right Justified and Character Data is Left
•Answer – Which Data is Left Justified and which is Right?
•An Example of Data with Left and Right Justification
•A Visual of CHARACTER Data vs. VARCHAR Data
•Use the TRIM command to remove spaces on CHAR Data
•TRIM Eliminates Leading and Trailing Spaces
•Escape Character in the LIKE Command changes Wildcards
•Escape Characters Turn off Wildcards in the LIKE Command
•Quiz – Turn off that Wildcard
•ANSWER – To Find that Wildcard
•The Distinct Command
•Distinct vs. GROUP BY
•Rules of Thumb for DISTINCT vs. GROUP BY
•GROUP BY Vs. DISTINCT – Good Advice
•Quiz – How many rows come back from the Distinct?
•Answer – How many rows come back from the Distinct?
•TOP Command
•TOP Command is brilliant when ORDER BY is used!
•The TOP Command WITH TIES
•How the TOP Command WITH TIES Decides
•The TOP Command will NOT work with Certain Commands
•The SAMPLE Function and Syntax
•A SAMPLE Example that asks for Multiple Samples
•A SAMPLE Example with the SAMPLEID
•A SAMPLE Example WITH REPLACEMENT
•A SAMPLE Example with Four 10% Samples
•A Randomized SAMPLE
•A SAMPLE with Conditional Logic
•Aggregates and A SAMPLE using a Derived Table
•Random Number Generator
•Using Random to SELECT a Percentage of Rows
•Using Random and Aggregations
Teradata SQL Video 3 - Aggregates
This chapter shows viewers everything they need to know about Aggregates.
Objectives
After viewing this video, students will be able to:
•Know the 3 rules of Aggregation
•Know the 5 different aggregates
•Utilize Group By statements
•Understand the difference between a Where and Having clause
•Utilize Group by Grouping Sets, Rollup, and Cube
Table of Contents
•Quiz – You calculate the Answer Set in your own Mind
•Answer – You calculate the Answer Set in your own Mind
•The 3 Rules of Aggregation
•Quiz – How many rows come back?
•Troubleshooting Aggregates
•GROUP BY when Aggregates and Normal Columns Mix
•GROUP BY Delivers one row per Group
•GROUP BY Dept_No or GROUP BY 1 the same thing
•Limiting Rows and Improving Performance with WHERE
•WHERE Clause in Aggregation limits unneeded Calculations
•Keyword HAVING tests Aggregates after they are Totaled
•Keyword HAVING is like an Extra WHERE Clause for Totals
•Getting the Average Values per Column
•Average Values per Column for All Columns in a Table
•Three types of Advanced Grouping
•GROUP BY Grouping Sets
•GROUP BY Rollup
•GROUP BY Rollup Result Set
•GROUP BY Cube
•GROUP BY CUBE Result Set
•Use the Nexus for all Groupings
Teradata SQL Video 4 - Joins
This video shows how to create joins between tables.
Objectives
After viewing this video, students will be able to:
•Perform a join using traditional syntax
•Perform a join using ANSI syntax
•Understand the differences between inner joins and outer joins
•Describe how Teradata performs joins inside the Teradata engine
Table of Contents
•A two-table join using Non-ANSI Syntax
•A two-table join using Non-ANSI Syntax with Table Alias
•Aliases and Fully Qualifying Columns
•A two-table join using ANSI Syntax
•Both Queries have the same Results and Performance
•Quiz – Can You Finish the Join Syntax?
•Answer to Quiz – Can You Finish the Join Syntax?
•Quiz – Can You Find the Error?
•Answer to Quiz – Can You Find the Error?
•Quiz – Which rows from both tables won’t return?
•Answer to Quiz – Which rows from both tables Won’t Return?
•LEFT OUTER JOIN
•LEFT OUTER JOIN Brings Back All Rows in the Left Table
•RIGHT OUTER JOIN
•RIGHT OUTER JOIN Brings Back All Rows in the RIGHT Table
•FULL OUTER JOIN
•FULL OUTER JOIN Brings Back All Rows in All Tables
•Which Tables are the Left and which are the Right?
•Answer - Which Tables are the Left and which are the Right?
•INNER JOIN with Additional AND Clause
•ANSI INNER JOIN with Additional AND Clause
•ANSI INNER JOIN with Additional WHERE Clause
•OUTER JOIN with Additional WHERE Clause
•OUTER JOIN with Additional AND Clause
•Results from OUTER JOIN with Additional AND Clause
•Quiz – Why is this considered an INNER JOIN?
•The DREADED Product Join
•Result Set of the DREADED Product Join
•The Horrifying Cartesian Product Join
•The ANSI Cartesian Join will ERROR
Teradata SQL Video 5 - Dates
The video shows how Teradata stores dates and viewers will learn how to master all SQL involving dates, time, and Timestamp.
Objectives
After viewing this video, students will be able to:
•Utilize Date, Time, and Timestamp key words
•Understand how dates are stored internally
•Manipulate dates with the Add Months and Extract commands
•Utilize the system calendar for advanced dates
Table of Contents
•Date, Time, and Current_Timestamp Keywords
•Dates are stored internally as INTEGERS from a Formula
•Displaying Dates for INTEGERDATE and ANSIDATE
•DATEFORM
•Changing the DATEFORM in Client Utilities such as BTEQ
•Date, Time, and Timestamp Recap
•Timestamp Differences
•Finding the Number of Hours between Timestamps
•Troubleshooting Timestamp
•Add or Subtract Days from a date
•A Summary of Math Operations on Dates
•Using a Math Operation to find your Age in Years
•Find What Day of the week you were Born
•The ADD_MONTHS Command
•Using the ADD_MONTHS Command to Add 1 Year
•Using the ADD_MONTHS Command to Add 5 Years
•The EXTRACT Command
•EXTRACT from DATES and TIME
•CURRENT_DATE and EXTRACT or Current_Date and Math
•CAST the Date of January 1, 2011 and the Year 1800
•The System Calendar
•Using the System Calendar in Its Simplest Form
•How to really use the Sys_Calendar.Calendar
•Storing Dates Internally
•Storing Time Internally
•Storing TIME with TIME ZONE Internally
•Storing Timestamp Internally
•Storing Timestamp with TIME ZONE Internally
•Storing Date, Time, and Timestamp with Zone Internally
Teradata SQL Video 6 – Temporary Tables (Derived)
This video teaches about Derived Tables.
Objectives
After viewing this video, students will be able to:
•Know 3 different ways to create derived tables
•Understand the different options when creating a derived table
•Utilize tricks for performance tuning
•Create a recursive derived table
Table of Contents
•There are three types of Temporary Tables
•CREATING A Derived Table
•Naming the Derived Table
•Aliasing the Column Names in the Derived Table
•Most Derived Tables Are Used To Join To Other Tables
•Multiple Ways to Alias the Columns in a Derived Table
•Our Join Example with a Different Column Aliasing Style
•Column Aliasing Can Default for Normal Columns
•CREATING a Derived Table using the WITH Command
•Our Join Example With the WITH Syntax
•The Same Derived Query shown Three Different Ways
•Quiz - Answer the Questions
•Answer to Quiz - Answer the Questions
•Clever Tricks on Aliasing Columns in a Derived Table
•A Derived Table lives only for the lifetime of a single query
•An Example of Two Derived Tables in a Single Query
•WITH RECURSIVE Derived Table
•Defining the WITH Recursive Derived Table
•Looping Through the WITH Recursive Derived Table
Teradata SQL Video 7 – Volatile and Global Temporary Tables
This chapter will explain both Volatile and Global Temporary Tables.
Objectives
After viewing this video, students will be able to:
•Create Volatile and Global Temporary tables
•Describe the differences between a Volatile and Global Temporary table
•Create volatile tables quickly
•Utilize performance tuning techniques on temporary tables
Table of Contents
•Creating a Volatile Table
•You Populate a Volatile Table with an INSERT/SELECT
•The Three Steps to Use a Volatile Table
•Why Would You Use the ON COMMIT DELETE ROWS?
•The HELP Volatile Table Command Shows your Volatiles
•A Volatile Table with a Primary Index
•The Joining of Two Tables Using a Volatile Table
•You Can Collect Statistics on Volatile Tables
•The New Teradata V14 Way to Collect Statistics
•Four Examples of Creating a Volatile Table Quickly
•Four Advanced Examples of Creating a Volatile Table Quickly
•Creating Partitioned Primary Index (PPI) Volatile Tables
•Using a Volatile Table to Get Rid of Duplicate Rows
•Using a Simple Global Temporary Table
•Two Brilliant Techniques for Global Temporary Tables
•The Joining of Two Tables Using a Global Temporary Table
•CREATING A Global Temporary Table
Teradata SQL Video 8 – Ordered Analytic Functions
This video will show the fundamentals of Ordered Analytics.
Objectives
After viewing this video, students will be able to:
•Understand the difference between Teradata OLAP and ANSI OLAP
•Utilize CSUM, MSUM, MAVG, MDIFF
•Utilize Partition statement to see subtotals
Table of Contents
•On-Line Analytical Processing (OLAP) or Ordered Analytics
•Cumulative Sum (CSUM) Command and how OLAP Works
•OLAP Commands always Sort (ORDER BY) in the Command
•Calculate the Cumulative Sum (CSUM) after Sorting the Data
•The OLAP Major Sort Key
•The OLAP Major Sort Key and the Minor Sort Key(s)
•Troubleshooting OLAP – My Data isn’t coming back correct
•GROUP BY in Teradata OLAP Syntax Resets on the Group
•CSUM the Number 1 to get a Sequential Number
•A Single GROUP BY Resets each OLAP with Teradata Syntax
•A Better Choice – The ANSI Version of CSUM
•The ANSI Version of CSUM – The Sort Explained
•The ANSI CSUM – Rows Unbounded Preceding Explained
•The ANSI CSUM – Making Sense of the Data
•The ANSI CSUM – Making Even More Sense of the Data
•The ANSI CSUM – The Major and Minor Sort Key(s)
•The ANSI CSUM – Getting a Sequential Number
•Troubleshooting the ANSI OLAP on a GROUP BY
•The ANSI OLAP – Reset with a PARTITION BY Statement
•PARTITION BY only Resets a Single OLAP not ALL of them
•The Moving SUM (MSUM) and Moving Window
•How the Moving Sum is calculated
•How the Sort works for Moving SUM (MSUM)
•GROUP BY in the Moving SUM does a Reset
•Quiz – Can you make the Advanced Calculation in your mind?
•Answer to Quiz for the Advanced Calculation in your mind?
•Quiz – Write that Teradata Moving Average in ANSI Syntax
•Both the Teradata Moving SUM and ANSI Version
•The ANSI Moving Window is Current Row and Preceding
•How ANSI Moving Average Handles the Sort
•Quiz – How is that Total Calculated?
•Answer to Quiz – How is that Total Calculated?
•Moving SUM every 3-rows Vs. a Continuous Average
•Partition BY Resets an ANSI OLAP
•The Moving Average (MAVG) and Moving Window
•How the Moving Average is calculated
•How the Sort works for Moving Average (MAVG)
•GROUP BY in the Moving Average does a Reset
•Quiz – Can you make the Advanced Calculation in your mind?
•Answer to Quiz for the Advanced Calculation in your mind?
•Quiz – Write that Teradata Moving Average in ANSI Syntax
•Both the Teradata Moving Average and ANSI Version
•The ANSI Moving Window is Current Row and Preceding
•How ANSI Moving Average Handles the Sort
•Quiz – How is that Total Calculated?
•Answer to Quiz – How is that Total Calculated?
•Quiz – How is that 4th Row Calculated?
•Answer to Quiz – How is that 4th Row Calculated?
•Moving Average every 3-rows Vs. a Continuous Average
•Partition BY Resets an ANSI OLAP
•The Moving Difference (MDIFF)
•Moving Difference (MDIFF) Visual
•Moving Difference using ANSI Syntax
•Moving Difference using ANSI Syntax with Partition By
Teradata SQL Video 9 – Advanced Ordered Analytics
This video covers Ordered Analytics such as RANK, Min, Max and Row_Number.
Objectives
After viewing this video, students will be able to:
•Utilize the Rank command
•Utilize Rank with both Partition By and Qualify
•Understand what a Percent Rank is
•Utilize the Count Over, Max Over, and Row_Number
Table of Contents
•The RANK Command
•How to get Rank to Sort in Ascending Order
•Two ways to get Rank to Sort in Ascending Order
•RANK using ANSI Syntax Defaults to Ascending Order
•Getting RANK using ANSI Syntax to Sort in DESC Order
•RANK () OVER and PARTITION BY
•RANK () OVER and QUALIFY
•RANK () OVER and PARTITION BY with a QUALIFY
•QUALIFY and WHERE
•Quiz – How can you simplify the QUALIFY Statement
•Answer to Quiz –Can you simplify the QUALIFY Statement
•The QUALIFY Statement without Ties
•The QUALIFY Statement with Ties
•The QUALIFY Statement with Ties Brings back Extra Rows
•Mixing Sort Order for QUALIFY Statement
•Quiz – What Caused the RANK to Reset?
•Answer to Quiz – What Caused the RANK to Reset?
•Quiz – Name those Sort Orders
•Answer to Quiz – Name those Sort Orders
•PERCENT_RANK () OVER
•PERCENT_RANK () OVER with 14 rows in Calculation
•PERCENT_RANK () OVER with 21 rows in Calculation
•Quiz – What Cause the Product_ID to Reset?
•Answer to Quiz – What Causes the Product_ID to Reset
•Answer to Quiz – What Causes the Product_ID to Reset
•COUNT OVER for a Sequential Number
•Troubleshooting COUNT OVER
•Quiz – What caused the COUNT OVER to Reset?
•Answer to Quiz – What caused the COUNT OVER to Reset?
•The MAX OVER Command
•MAX OVER with PARTITION BY Reset
•Troubleshooting MAX OVER
•The MIN OVER Command
•Troubleshooting MIN OVER
•Finding a Value of a Column in the Next Row with MIN
•Finding a Value of a Date in the Next Row with MIN
•Finding Gaps between Dates
•The CSUM for Each Product_ID for the First 3 Days
•Quiz – Fill in the Blank
•Answer to Quiz – Fill in the Blank
•The Row_Number Command
•Quiz – How did the Row_Number Reset?
•Quiz – How did the Row_Number Reset?
Teradata SQL Video 10 - Subqueries
This video covers Subqueries, Correlated Subqueries, and Exists.
Objectives
After viewing this video, students will be able to:
•Write a series of subqueries
•Understand the difference between a Subquery and Correlated Subquery
•Write Subqueries with multiple parameters
•Write an Exists statement
•Handle a Not In with potential Null values
Table of Contents
•An IN List is much like a Subquery
•An IN List Never has Duplicates – Just like a Subquery
•An IN List Ignores Duplicates
•The Subquery
•How a Basic Subquery Works
•The Final Answer Set from the Subquery
•Quiz- Answer the Difficult Question
•Answer to Quiz- Answer the Difficult Question
•Should you use a Subquery of a Join?
•Quiz- Write the Subquery
•Answer to Quiz- Write the Subquery
•Quiz- Write the More Difficult Subquery
•Answer to Quiz- Write the More Difficult Subquery
•Quiz- Write the Subquery with an Aggregate
•Answer to Quiz- Write the Subquery with an Aggregate
•Quiz- Write the Correlated Subquery
•Answer to Quiz- Write the Correlated Subquery
•The Basics of a Correlated Subquery
•The Top Query always runs first in a Correlated Subquery
•The Bottom Query runs last in a Correlated Subquery
•Quiz- Who is coming back in the Final Answer Set?
•Answer- Who is coming back in the Final Answer Set?
•Correlated Subquery Example vs. a Join with a Derived Table
•Quiz- A Second Chance to Write a Correlated Subquery
•Answer - A Second Chance to Write a Correlated Subquery
•Quiz- A Third Chance to Write a Correlated Subquery
•Answer - A Third Chance to Write a Correlated Subquery
•Quiz- Last Chance to Write a Correlated Subquery
•Answer – Last Chance to Write a Correlated Subquery
•Correlated Subquery that Finds Duplicates
•Quiz- Write the NOT Subquery
•Answer to Quiz- Write the NOT Subquery
•Quiz- Write the Subquery using a WHERE Clause
•Answer - Write the Subquery using a WHERE Clause
•Quiz- Write the Subquery with Two Parameters
•Answer to Quiz- Write the Subquery with Two Parameters
•How the Double Parameter Subquery Works
•More on how the Double Parameter Subquery Works
•Quiz – Write the Triple Subquery
•Answer to Quiz – Write the Triple Subquery
•Quiz – How many rows return on a NOT IN with a NULL?
•Answer – How many rows return on a NOT IN with a NULL?
•How to handle a NOT IN with Potential NULL Values
•IN is equivalent to =ANY
•How a Correlated Exists matches up
•The Correlated NOT Exists
•The Correlated NOT Exists Answer Set
Teradata SQL Video 11 – Substring and Positioning
This video is designed to show Substring, Position, Trim, Concatenate, and Characters commands in order to deal with Character Strings.
Objectives
After viewing this video, students will be able to:
•Understand the Character_Length and Trim commands
•Completely describe the Substring command and how it works
•Utilize Substring and Position together for advanced substringing
•Concatenate columns together
Table of Contents
•The CHARACTERS Command Counts Characters
•The CHARACTERS Command – Spaces can Count too
•The CHARACTERS Command and Char (20) Data
•Troubleshooting the CHARACTERS Command
•TRIM for Troubleshooting the CHARACTERS Command
•CHARACTERS and CHARACTER_LENGTH equivalent
•OCTET_LENGTH
•The TRIM Command trims both Leading and Trailing Spaces
•Trim and Trailing is Case Sensitive
•Trim and Trailing works if Case right
•Trim Combined with the CHARACTERS Command
•How to TRIM only the Trailing Spaces
•How to TRIM Trailing Letters
•How to TRIM Trailing Letters and use CHARACTER_Length
•The SUBSTRING Command
•How SUBSTRING Works with NO ENDING POSITION
•Using SUBSTRING to move Backwards
•How SUBSTRING Works with a Starting Position of -1
•How SUBSTRING Works with an Ending Position of 0
•An Example using SUBSTRING, TRIM and CHAR Together
•SUBSTRING and SUBSTR are equal, but use different syntax
•The POSITION Command finds a Letters Position
•The POSITION Command is brilliant with SUBSTRING
•Quiz – Name that SUBSTRING Starting and For Length
•The POSITION Command is brilliant with SUBSTRING
•Quiz – Name that SUBSTRING Starting and For Length
•Answer to Quiz – Name that Starting and For Length
•Answer to Quiz – Name that Starting and For Length
•Using the SUBSTRING to Find the Second Word On
•Quiz – Why did only one Row Return?
•Answer to Quiz – Why Did only one Row Return?
•Concatenation
•Concatenation and SUBSTRING
•Four Concatenations Together
•Troubleshooting Concatenation
Teradata SQL Video 12 – Data Interrogation
This videos shows how to interrogate data using key commands such as CASE.
After viewing this video, students will be able to:
•Null If Zero and Zero If Null commands
•Describe how Coalesce Statement works
•Write multiple Cast statements
•Know the difference between a Searched Case and Valued Case statement
•Understand Advanced features such as Horizontal Case and Nested Case
•Sort using a Case statement
Table of Contents
•Quiz – What would the Answer be?
•Answer to Quiz – What would the Answer be?
•The NULLIFZERO Command
•Quiz – Fill in the Blank Values in the Answer Set
•Answer to Quiz – Fill in the Blank Values in the Answer Set
•Answer to Quiz – Fill in the Blank Values in the Answer Set
•Quiz – Fill in the Answers for the NULLIF Command
•Quiz – Fill in the Answers for the NULLIF Command
•The ZEROIFNULL Command
•Answer to the ZEROIFNULL Question
•The COALESCE Command
•The COALESCE Answer Set
•The Coalesce Quiz
•Answers to the Coalesce Quiz
•The Basics of CAST (Convert and Store)
•Some Great CAST (Convert and Store) Examples
•Some Great CAST (Convert and Store) Examples
•Some Great CAST (Convert and Store) Examples
•A Teradata Extension – The Implied Cast
•The Basics of the CASE Statements
•The Basics of the CASE Statement shown visually
•Valued Case vs. Searched Case
•Quiz - Valued Case Statement
•Answer - Valued Case Statement
•Quiz - Searched Case Statement
•Answer - Searched Case Statement
•Quiz - When NO ELSE is present in CASE Statement
•Answer - When NO ELSE is present in CASE Statement
•When an ELSE is present in CASE Statement
•When NO ELSE is present in CASE Statement
•When an Alias is NOT used in a CASE Statement
•When an Alias is NOT used in a CASE Statement
•When NO ELSE is present in CASE Statement
•Combining Searched Case and Valued Case
•A Trick for getting a Horizontal Case
•Nested Case
•Put a CASE in the ORDER BY
Teradata SQL Video 13 - Views
This video is designed to show how views are created and utilized.
Objectives
After viewing this video, students will be able to:
•Create views
•Modify views
•Understand when aliasing is mandatory
•Utilize Locking Row for access in views
•Update tables through a view
Table of Contents
•Creating a Simple View
•Basic Rules for Views
•How to Modify a View
•Exceptions to the ORDER BY Rule inside a View
•How to Get HELP with a View
•Views sometimes CREATED for Formatting or Row Security
•Another Way to Alias Columns in a View CREATE
•Resolving Aliasing Problems in a View CREATE
•Resolving Aliasing Problems in a View CREATE
•Resolving Aliasing Problems in a View CREATE
•CREATING Views for Complex SQL such as Joins
•WHY certain columns need Aliasing in a View
•Aggregates on View Aggregates
•Locking Row for Access
•Creating Views for Temporal Tables
•Altering a Table
•Altering a Table after a View has been created
•A View that errors After an ALTER
•Troubleshooting a View
•Updating Data in a Table through a View
•Maintenance Restrictions on a Table through a View
Teradata SQL Video 14 - Macros
This chapter teachers viewers everything about Teradata Macros.
Objectives
After viewing this video, students will be able to:
•Understand the 14 rules of Macros
•Create and execute a Macro
•Create advanced Macros
•Pass input parameters to a Macro
Table of Contents
•The 14 rules of Macros
•CREATING and EXECUTING a Simple Macro
•Multiple SQL Statements inside a Macro
•Complex Joins inside a Macro
•Passing an INPUT Parameter to a Macro
•Troubleshooting a Macro with INPUT Parameters
•Troubleshooting a Macro with INPUT Parameters
•An UPDATE Macro with Two Input Parameters
•Executing a Macro with Named (Not Positional) Parameters
•Troubleshooting a Macro
Teradata SQL Video 15 – Stored Procedures
This video teaches the concepts of Stored Procedures.
Objectives
After viewing this video, students will be able to:
•Create a Stored Procedure
•Call a Stored Procedure
•Understand how to Create and Declare a variable
•Utilize Looping in stored procedures
•Understand how to create an If Statement inside a stored procedure
Table of Contents
•Stored Procedures vs. Macros
•Creating a Stored Procedure
•How you CALL a Stored Procedure
•Label all BEGIN and END statements except the first ones
•How to Declare a Variable
•How to Declare a Variable and then SET the Variable
•An IN Variable is passed to the Procedure during the CALL
•The IN, OUT and INOUT Parameters
•Using IF inside a Stored Procedure
•Example of two Stored Procedures with different techniques
•Using Loops in Stored Procedures
•You can Name the First Begin and End if you choose
•Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT
•Stored Procedure Basic Assignment
•Answer - Stored Procedure Basic Assignment
Teradata SQL Video 16 – Set Operators
This video teaches about the Set Operators Union, Intersect and Except/Minus.
Objectives
After viewing this video, students will be able to:
•Know the difference between Union Intersect and Minus
•Understand the rules for Set Operators
•Sort a Set Operator
•Describe the performance differences between Union and Union All
•Change the order of Precedence when using multiple set operators
Table of Contents
•Rules of Set Operators
•INTERSECT Explained Logically
•INTERSECT Explained Logically
•UNION Explained Logically
•UNION Explained Logically
•UNION ALL Explained Logically
•UNION Explained Logically
•EXCEPT Explained Logically
•EXCEPT Explained Logically
•Minus Explained Logically
•Minus Explained Logically
•Testing Your Knowledge
•Testing Your Knowledge
•An Equal Amount of Columns in both SELECT List
•Columns in the SELECT list should be from the same Domain
•The Top Query handles all Aliases
•The Bottom Query does the ORDER BY (a Number)
•Great Trick: Place your Set Operator in a Derived Table
•UNION vs. UNION ALL
•UNION vs. UNION ALL Example
•Using UNION ALL and Literals
•A Great Example of how EXCEPT works
•USING Multiple SET Operators in a Single Request
•Changing the Order of Precedence with Parentheses
•Using UNION ALL for speed in Merging Data Sets
Teradata SQL Video 17 – Creating Tables
This video teaches the many options for Creating Teradata Tables.
Objectives
After viewing this video, students will be able to:
•Create a table with an UPI or NUPI
•Understand the difference between Set and Multiset tables
•Create tables with secondary indexes
•Create a copy of a table quickly
•Describe how a Queue Table works
•Understand PPI Tables vs Columnar Tables vs Bi-Temporal Tables vs Normal Tables
Table of Contents
•Creating a Table with a Unique Primary Index
•Creating a Table with a Non-Unique Primary Index
•Creating a Table without entering a Primary Index
•Creating a Table with NO Primary Index
•Creating a Set Table
•Creating a Multiset Table
•Creating a Set Table with a Unique Primary Index
•Creating a Set Table with a Unique Secondary Index
•Creating a Table with an UPI and USI
•Creating a Table with a Multicolumn Primary Index
•Creating a Unique Secondary Index (USI) after a table is created
•Creating a Non-Unique Secondary Index (NUSI) after a table is created
•Creating a Value-Ordered NUSI
•Data Types
•Data Types Continued
•Data Types Continued
•Major Data Types and the number of Bytes they take up
•Making an exact copy a Table
•Making a NOT-So-Exact Copy a Table
•Copying a Table
•Troubleshooting Copying and Changing the Primary Index
•Copying only specific columns of a table
•Copying a Table and Keeping the Statistics
•Copying a Table with Statistics
•Copying a table Structure with NO Data but Statistics
•Creating a Table with Fallback
•Creating a Table with No Fallback
•Creating a Table with a Before Journal
•Creating a Table with a Dual Before Journal
•Creating a Table with an After Journal
•Creating a Table with a Dual After Journal
•Creating a Table with a Journal Keyword Alone
•Why Use Journaling?
•Why Use Journaling?
•Creating a Table with Customization of the Data Block Size
•Creating a Table with Customization with FREESPACE Percent
•Creating a QUEUE Table
•Example of how a Queue Table Works
•Example of how a Queue Table Works
•Creating a Columnar Table
•Creating a Columnar Table with Multi-Column Containers
•Creating a Columnar Table with a Row Hybrid
•Creating a Columnar Table with both Row and Column Partitions
•How to Load into a Columnar Table
•Creating a Columnar Table with NO AUTO COMPRESS
•CREATING a Bi-Temporal Table
•Explaining Bi-Temporal PERIOD Data Types
•Creating a PPI Table with Simple Partitioning
•Creating a PPI Table with RANGE_N Partitioning per Day
•Creating a PPI Table with RANGE_N Partitioning per Month
•A Visual of One Year of Data with Range_N per Month
•Creating a PPI Table with RANGE_N Partitioning per Week
•A Clever Range_N Option
•Creating a PPI Table with CASE_N
•A Visual of Case_N Partitioning
•Number of PPI Partitions Allowed
•NO CASE and UNKNOWN partitions together