Teradata Details

For pricing and availability

Contact: Tom Coffing

Phone: 513 300-0341

Teradata Architecture and SQL

Description: In this course, students will learn the Teradata architecture with an excellent level of detail. Students will also learn the clever tricks and tips that make Teradata unique. In addition, students will create all types of Teradata tables, views, macros, user-defined functions, and stored procedures, and learn how to performance tune Teradata tables and queries—over 600 examples of managing, creating tables, and Teradata SQL. The class is 90% hands-on training. The students will use the SQL_Class database with 18 tables representing the tables in the book.

Reference Guide: The book is an excellent book for teaching and makes for an excellent reference guide to help on the job.

Description: Students will learn the Teradata architecture and SQL starting at the most basic level and going to the most advanced level in this course.

Objectives: After this course, students will have detailed knowledge and understanding of the Teradata architecture, creating and managing tables and objects, utilizing indexes, performance tuning, and essential, intermediate, and advanced SQL hands-on training.

Audience: This course is for anyone who desires to learn the Teradata architecture and SQL, from beginners to experienced users.

Prerequisites: None

Duration: 4-Days

Topics:

What is Parallel Processing?
The Teradata Architecture
The Primary Index
How Hashing works
Partitioned Tables
Columnar Tables
Secondary Indexes
Perm, Spool, and Temp Space
Blocks, Cylinders, and the Master Index
Join Indexes
Temporal Tables
Collecting Statistics
Native Object Stores
Explain Plans
Major Sort vs. Minor Sorts
The Like Command Wildcards are Percent and Underscore
Distinct, Group By, and TOP
There are Five Aggregates
Inner and Outer Joins
Date and Time Functions
Format Functions
Analytics and Window Functions
Temporary Tables
Subqueries
Strings
Interrogating the Data
Views
Set Operators
Creating Tables
Data Manipulation Language (DML)
User-Defined Functions (UDF)
Stored Procedures
Statistical Aggregate Functions

Teradata Architecture and SQL Course Outline

Chapter 1 - The Teradata Architecture

What is Parallel Processing?

The Teradata Architecture

The Basics of a Single Computer

Teradata Parallel Processes Data

Parallel Architecture

Teradata Architecture Components in Action

All Teradata Tables Distribute 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 attached via a Network to a Disk Farm

There are Many Nodes in a Teradata Cabinet

Two SMP Nodes Connected Become One MPP System

Inside a Teradata Node

The Parsing Engine

The AMPs Responsibilities

Teradata Architecture Visual

Chapter 2 – The Primary Index

You Define the Primary Index when you CREATE the table

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?

Why create a table with No Primary Index (NoPI)?

Chapter 3 – Hashing of the Primary Index

The Hashing Formula Facts

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

Quiz

Answer to Quiz

Chapter 4 - Secondary Indexes

Creating a Unique Secondary Index (USI)

What is in a Unique Secondary Index (USI) Subtable?

A Unique Secondary Index (USI) Subtable is hashed

How the Parsing Engine uses the USI Subtable

A USI is a Two-AMP Operation

Creating a Non-Unique Secondary Index (NUSI)

What is in a Unique Secondary Index (USI) Subtable?

Non-Unique Secondary Index (NUSI) Subtable is AMP Local

How the Parsing Engine uses the NUSI Subtable

Creating a Value-Ordered NUSI

The Hash Map Determines which AMP will own the Row

A Unique Primary Index Spreads the Data Evenly

Quiz – Answer the Tough USI Questions

Answer to Quiz – Answer the Tough USI Questions

A Picture with a Base Table, USI, and NUSI Subtable

Quiz – Tough Questions on the USI and NUSI Subtables

Answer – Tough Questions on the USI and NUSI Subtables

A Query Using an USI Only Moves Two Blocks

A Query Using A NUSI Always Uses All AMPs

Two Non-Unique Secondary Indexes (NUSI) on a Table

A NUSI BITMAP Query (1 of 3)

A NUSI BITMAP Theory (2 of 3)

A NUSI Bitmap in Action (3 of 3)

A Brilliant Technique for a Unique Secondary Index

The USI for Partitioned Tables Points to the Row Key

A Brilliant Technique for a Non-Unique Secondary Index

The NUSI for Partitioned Tables Points to the Row Key

How the PE Decides on the NUSI or the Full Table Scan

The Bigger Quiz

The Bigger Quiz Answers

Multiple Choice DBA

What are the Big Three Tactical Queries?

Chapter 5 - Partition Primary Index (PPI) Tables

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

A Clever Range_N Option

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 RANGE_N

SQL for CASE_N

Primary Time Index (Teradata 16.20 release)

Primary Time Index Table Created on Time Bucket Parameter

PTI Table Created Jointly on Time Bucket and Column

Primary Time Index Table Created Only on the Column

Distribution Strategies Across AMPs

Sorting Strategies Within AMPs

Primary Time Index Auto-Generated Columns

SHOW Table Example of Auto-Generated Columns

Auto-generated Columns

Chapter 6 – Columnar Tables

Columnar Tables

Show Table

Auto-Compression

No Auto Compress

DDL of Previous Example

What does a Columnar Table look like?

Comparing Normal Table vs. Columnar Tables

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)

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

Another Form of Creating a Table with NO AUTO COMPRESS

Auto Compress in Columnar Tables

When and When NOT to use Columnar Tables

Did you know Tom Coffing was a Professional Golf Caddie?

Chapter 7 – Space

When your System Arrives, there is only User named DBC

USER 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 workspace 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

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

Space Transfer Quiz

Answer to Space Transfer Quiz

Drop Space Quiz

Answers to Drop Space Quiz

Chapter 8 – The User Environment

DBC is the only user when the system first arrives

DBC will Create Databases and Give them Space

DBC will create some initial Users

A Typical Teradata Environment

What are Similarities between a DATABASE and a USER?

Roles

Create a Role and then Assign that Role Its Access Rights

Create a User and Assign them a Default Role

Granting Access Rights

There are Three Types of Access Rights

Description of the Three Types of Access Rights

Profiles

Creating a Profile and a User

ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights

Accounts and their Associated Priorities

Creating a User with Multiple Account Priorities

Account String Expansion (ASE)

The DBC.AMPUsage View

Teradata TASM provides a User Traffic System

Teradata Viewpoint

Chapter 9 - How Teradata Works Internally

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 Happens on Disk because Everything Happens 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 Intelligent Memory Gives Data a Temperature

Data deemed VeryHot stays in each AMP's Intelligent Memory

Intelligent Memory Stays in Memory

What is the Goal of a Teradata Physical Database Design?

Chapter 10 – Inside the AMPs Disk

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

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 a 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

Chapter 11 - Temporal Tables Create Functions

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

Chapter 12 - How Joins Work Internally

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?

Join Feature PRPD

Chapter 13 - Join Indexes

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

Sparse Join Index

A Global Multi-Table Join Index

Creating a Hash Index

Join Index Details

Chapter 14 - Collect Statistics

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 Way to Collect Statistics

Where Does Teradata Keep the Collected Statistics?

The Official Syntax for COLLECT STATISTICS

How to Recollect STATISTICS on a Table

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

How to Collect Statistics on a PPI Table on the Partition

Teradata Statistics Enhancements

Teradata Statistics Enhancements Continued

Summary Statistics

MaxValueLength

MaxIntervals

Sample N Percent

Statistics Collection Improvements

AutoStats feature

Teradata Statistics Wizard

Chapter 15 – Native Object Store

Native Object Store (NOS)

Native Object Store (NOS) Capabilities

The Two Ways to Read Data From an External Object Store

Native Object Store (NOS) Security

Security Example

Finding all Objects in an Object Store

Chapter 16 - Basic SQL Functions

Nexus – Your query and migration tool

Introduction

SELECT * (All Columns) in a Table

SELECT Specific Columns in a Table

Using the Best Form for Writing SQL

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

Order By with Nulls Last

Changing the ORDER BY to Descending Order

NULL Values sort First in Ascending Mode (Default)

NULL Values sort Last in Descending Mode (DESC)

Order By DESC With Nulls First

Major Sort vs. Minor Sorts

Multiple Sort Keys using Names vs. Numbers

Sorts are Alphabetical, NOT Logical

Using A CASE Statement to Sort Logically

Using A Searched CASE Statement to Sort Logically

Quiz – Can you Add a Minor Sort?

Answer – Can you Add a Minor Sort?

Order By Decode

Quiz – Can you Add Two Minor Sorts Using Decode?

Answer – Can you Add Two Minor Sorts Using Decode?

How to ALIAS a Column Name

Using an Alias in the WHERE and ORDER BY Clause

Using an Alias in the ORDER BY Clause with Decode

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

Chapter 17 - The WHERE Clause

The WHERE Clause limits Returning Rows

Using a Column ALIAS throughout the SQL

Double Quoted Aliases are for Reserved Words and Spaces

Character Data needs Single Quotes in the WHERE Clause

Character Data needs Single Quotes, but Numbers Don’t

NULL means UNKNOWN DATA so Equal (=) won’t Work

Use IS NULL or IS NOT NULL when dealing with NULLs

NULL is UNKNOWN DATA so NOT Equal won’t Work

Use IS NULL or IS NOT NULL when dealing with NULLs

Using Greater Than OR Equal To (>=)

Using GE as Greater Than or Equal To (>=)

AND in the WHERE Clause

Troubleshooting AND

OR in the WHERE Clause

Troubleshooting OR

WHY OR Must Utilize the Column Name Each Time

Troubleshooting Character Data

Troubleshooting Character Data Continued

Using Different Columns in an AND Statement

Quiz – How many rows will return?

Answer to Quiz – How many rows will return?

What is the Order of Precedence?

Using Parentheses to change the Order of Precedence

Using an IN List in place of OR

The IN List is an Excellent Technique

IN List vs. OR brings the same Results

The IN List Can Use Character Data

Using a NOT IN List

Null Values in a NOT IN List Return No Rows

An IN List with the Keyword ANY

A NOT IN List with the Keywords NOT = ALL

The NOT BETWEEN Statement is also Inclusive

BETWEEN Works for Character Data

IN ANSI Transaction Mode Case Matters

Escape Character in the LIKE Command changes Wildcards

Escape Characters Turn off Wildcards in the LIKE Command

Chapter 18 - Distinct Vs. Group By

The Distinct Command

Distinct vs. GROUP BY

Quiz – How many rows come back from the Distinct?

Answer – How many rows come back from the Distinct?

Chapter 19 - The TOP Command

TOP Command

TOP Command is brilliant with an ORDER BY Statement

The TOP Command WITH TIES

How the TOP Command WITH TIES Decides

The TOP Command will NOT work with Certain Commands

Chapter 20 - HELP and SHOW

Determining the Release of your Teradata System

Basic HELP Commands

Other HELP Commands

HELP DATABASE

HELP USER

HELP TABLE

Adding a Comment to a Table

Adding a Comment to a View

SELECT SESSION

USER Information Functions

HELP SESSION

HELP SQL

A HELP SQL Example

Show Commands

SHOW Table command for Table DDL

SHOW View command for View Create Statement

SHOW Macro command for Macro Create Statement

SHOW Trigger command for Trigger Create Statement

Chapter 21 - Aggregation Function

Quiz – You calculate the Answer Set in your Mind

Answer – You calculate the Answer Set in your Mind

The Three Rules of Aggregation

The Five Aggregates

Casting a Data Type

Troubleshooting Aggregates

GROUP BY when Aggregates and Normal Columns Mix

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 ResultSet

Quiz - GROUP BY GROUPING SETS Advanced Challenge

Answer To Quiz - GROUP BY GROUPING SETS Advanced Challenge

Chapter 22 - Join Functions

Two-Table join using Traditional Join Syntax

Two-table join using Traditional Join Syntax with Table Alias

You Can Fully Qualify All Columns for Clarity

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?

Super Quiz – Can You Find the Difficult 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 WHERE Clause

ANSI INNER JOIN with Additional AND Clause

OUTER JOIN with Additional WHERE Clause

OUTER JOIN with Additional AND Clause

The DREADED Product Join

The DREADED Product Join Results

Cartesian Product Join with Traditional Syntaxn

Cartesian Product Join with ANSI Syntax

The CROSS JOIN

The CROSS JOIN Answer Set

The Self Join

The Self Join with ANSI Syntax

An Associative Table is a Bridge that Joins Two Tables

Quiz – Can you Write the 3-Table Join?

Answer to Quiz – Can you Write the 3-Table Join?

Quiz – Can you Write the 3-Table Join to ANSI Syntax?

Answer – Can you Write the 3-Table Join to ANSI Syntax?

Quiz – Can you Place the ON Clauses at the End?

Answer – Can you Place the ON Clauses at the End?

The 5-Table Join – Logical Insurance Model

Quiz - Write a Five Table Join Using ANSI Syntax

Answer - Write a Five Table Join Using ANSI Syntax

Quiz - Write a Five Table Join Using Traditional Syntax

Answer - Write a Five Table Join Using Traditional Syntax

Quiz - Write a Five Table Join Using ANSI Syntax

Answer - Write a Five Table Join Using ANSI Syntax

Quiz –Re-Write this putting the ON clauses at the END

Answer –Re-Write this putting the ON clauses at the END

Chapter 23 - Date Functions

Date, Time, and Current_Timestamp Keywords

Add or Subtract From a Date

Subtract Two Dates for a Difference in Days

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

ResultSet to 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

Another Technique for Extracting Portions of a Timestamp or Date

EXTRACT from DATES and TIME

Why EXTRACT is a Better Form

CURRENT_DATE and EXTRACT or Current_Date and Math

CAST the Date of January 1, 2011, and the Year 1800

EXTRACT of the Month on Aggregate Queries

The System Calendar

Using the System Calendar in Its Simplest Form

How to use the Sys_Calendar.Calendar

Another Method For Advanced Dates

Date Tricks

Changing the Date to a Timestamp

Selecting the First and Last Day of the Current Month

Finding the Last Day of the Previous Month

Finding the Previous Friday Date From Today

Advanced Tricks for Month

Converting a String to a Date that has a Different Format

Combining Date and Time to Make a Timestamp

Converting a Timestamp into Time

Determining if the Current_Date is a Leap Year

Determining if the Current_Timestamp is a Leap Year

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

Time Zones

Setting Time Zones at the System Level

Setting Time Zones at the User Level

Setting Time Zones at the Session Level

Seeing your Time Zone

Creating a Sample Table for Time Zone Examples

Inserting Rows in the Sample Table for Time Zone Examples

Selecting the Data from our Time Zone Table

Normalizing our Time Zone Table with a CAST

Intervals for Date, Time and Timestamp

Interval Data Types and the Bytes to Store Them

The Basics of a Simple Interval

Troubleshooting the Basics of a Simple Interval

Interval Arithmetic Results

A Date Interval Example

A Time Interval Example

A DATE Interval Example

A Complex Time Interval Example using CAST

The OVERLAPS Command

An OVERLAPS Example that Returns No Rows

The OVERLAPS Command using TIME

The OVERLAPS Command using a NULL Value

Chapter 24 - Format Functions

The FORMAT Command

The Basics of the FORMAT Command

Quiz – How will the Date Appear after Formatting

Answer to Quiz – How will the Date Appear after Formatting

Quiz – How will the Date Appear after Formatting

Answer to Quiz – How will the Date Appear after Formatting

Formatting with MMM for the Abbreviated Month

Answer to Quiz – How will the Date Appear after Formatting

Formatting with MMMM for the Full Month Name

Formatting with MMMM for the Full Month

Formatting with DDD for the Julian Day

Formatting with EEE or EEEE for the Day of the Week

EEEE for the Abbreviated or Full Day of the Week

Placing Spaces inside your Formatting Commands with a B

Formatting Spaces with B or b

Formatting with 9

Formatting with 9 Results

Troubleshooting when Formatted Data Overflows

Formatting with X or x

Formatting with Z

Formatting with Z Visual

Formatting with 9

Formatting with 9 Visual

Formatting with $

Formatting with $ Visual

Formatting with $ and Commas

Formatting with $ and Commas Visual

Formatting with $ and Commas and 9

Formatting with $ and Commas and 9 with Zero Dollars

A Great Formatting Example

A Great Formatting Example for Day, Month, and Year

A Trick to get SQL Assistant to Format Data

Using the CASESPECIFIC (CS) Command in Teradata Mode

Using NOT CASESPECIFIC (CS) in ANSI Mode

Using the LOWER Command

Using the UPPER Command

Chapter 25 – Ordered Analytic Window Functions

On-Line Analytical Processing (OLAP) or Ordered Analytics

Cumulative Sum (CSUM) Command and how OLAP Works

Row_Number

Quiz – How did the Row_Number Reset?

What Keyword(s) caused the column alias startover to reset?

Answer – How did the Row_Number Reset?

Qualify

Quiz – Return Only the last Two Days Per Product_ID

Answer – Return Only the last Two Days Per Product_ID

Quiz – Return Two Students Per Class_Code with Highest Grades

Answer – Return Two Students Per Class_Code with Highest Grades

Using a Derived Table

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

Trouble Shooting the Moving Difference (MDIFF)

Using the RESET WHEN Option in Teradata (V13)

How Many Months per Product_ID has Revenue Increased?

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

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?

Row_Number with Qualify to get the Typical Rows per Value

A Second Typical Rows per Value Query on Sale_Date

Testing Your Knowledge

Chapter 26 - The Quantile Function

The Quantile Function and Syntax

A Quantile Example

A Quantile Example using DESC Mode

QUALIFY to find Products in the top Partitions

QUALIFY to find Products in the top Partitions Sorted DESC

QUALIFY to find Products in the top Partitions Sorted ASC

QUALIFY to find Products in top Partitions with Tiebreaker

Using Tertiles (Partitions of Four)

How Quantile Works

Chapter 27 - Temporary Tables

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

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

Chapter 28 - Sub-query Functions

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?

How to handle a NOT IN with Potential NULL Values

IN is equivalent to =ANY

Using a Correlated Exists

How a Correlated Exists matches up

The Correlated NOT Exists

The Correlated NOT Exists Answer Set

Quiz – How many rows come back from this NOT Exists?

Answer – How many rows come back from this NOT Exists?

Chapter 29 - Strings

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 differe,nt 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

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

Chapter 30 - Interrogating the Data

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

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

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 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

Chapter 31 - View Functions

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

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

Chapter 32 - Macro Functions

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

An UPDATE Macro with Two Input Parameters

Executing a Macro with Named (Not Positional) Parameters

Troubleshooting a Macro

Chapter 33 - Set Operators Functions

Rules of Set Operators

INTERSECT Explained Logically

UNION Explained Logically

UNION ALL Explained Logically

UNION Explained Logically

EXCEPT Explained Logically

Minus Explained Logically

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

Using UNION to be same as GROUP BY GROUPING SETS

Using UNION to be same as GROUP BY ROLLUP

Using UNION to be the same as GROUP BY Cube

Using UNION to be same as GROUP BY Cube

Chapter 34 – Creating Tables, Secondary Indexes, and Join Indexes

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

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?

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

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

Combining Older Data and Newer Data in PPI

A Visual for Combining Older Data and Newer Data in PPI

Multi-Level Partitioning Combining Range_N and Case_N

A Visual of Multi-Level Partitioning

NON-Unique Primary Indexes (NUPI) in PPI

PPI Table with a Unique Primary Index (UPI)

Tricks for Non-Unique Primary Indexes (NUPI)

A Brilliant Technique for a Unique Secondary Index

A Brilliant Technique for a Non-Unique Secondary Index

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 RANGE_N

SQL for CASE_N

SQL – User Defined Functions (UDF)

User Defined Functions

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

Creating a Single-Table Join Index

Compressed Single-Table Join Index

Aggregate Join Index

Sparse Join Index

A Global Multi-Table Join Index

Creating a Hash Index

Chapter 35 - Data Manipulation Language (DML)

INSERT Syntax # 1

INSERT Example with Syntax 1

INSERT Syntax # 2

INSERT Example with Syntax 2

INSERT Example with Syntax 3

Using NULL for Default Values

INSERT/SELECT Command

INSERT/SELECT Example using All Columns (*)

INSERT/SELECT Example with Less Columns

INSERT/SELECT to Build a Data Mart

Fast Path INSERT/SELECT

NOT quite the Fast Path INSERT/SELECT

UNION for the Fast Path INSERT/SELECT

BTEQ for the Fast Path INSERT/SELECT

The UPDATE Command Basic Syntax

Two UPDATE Examples

Subquery UPDATE Command Syntax

Example of Subquery UPDATE Command

Join UPDATE Command Syntax

Example of an UPDATE Join Command

Fast Path UPDATE

The DELETE Command Basic Syntax

Two DELETE Examples to DELETE ALL Rows in a Table

A DELETE Example Deleting only Some of the Rows

Subquery and Join DELETE Command Syntax

Example of Subquery DELETE Command

Example of Join DELETE Command

Fast Path DELETE

Fast Path DELETE Example # 1

Fast Path DELETE Example # 2

Fast Path DELETE Example # 3

MERGE INTO

MERGE INTO Example that Matches

MERGE INTO Example that does NOT Match

OReplace

Chapter 36 - Stored Procedure Functions

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

Stored Procedure Advanced Assignment

Answer - Stored Advanced Assignment

Chapter 37 - Trigger Functions

The Fundamentals of Triggers

CREATING A Trigger

FOR EACH STATEMENT vs. FOR EACH ROW

Using ORDER when Similar Triggers Exist

Chapter 38 - Math Functions

What is the Order of Precedents?

What is the Answer to this Math Question?

Chapter 39 - Sample

The SAMPLE Function and Syntax

SAMPLE Function Examples

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

Chapter 40 - Statistical Aggregate Functions

The Stats Table

The KURTOSIS Function

A Kurtosis Example

The SKEW Function

A SKEW Example

The STDDEV_POP Function

A STDDEV_POP Example

The STDDEV_SAMP Function

A STDDEV_SAMP Example

The VAR_POP Function

A VAR_POP Example

The VAR_SAMP Function

A VAR_SAMP Example

The CORR Function

A CORR Example

Another CORR Example so you can compare

The COVAR_POP Function

A COVAR_POP Example

Another COVAR_POP Example so you can Compare

The REGR_INTERCEPT Function

A REGR_INTERCEPT Example

Another REGR_INTERCEPT Example so you can compare

The REGR_SLOPE Function

A REGR_SLOPE Example

Another REGR_SLOPE Example so you can compare

Using GROUP BY

No Having Clause vs. Use of HAVING

Chapter 41 - Explain Examples

EXPLAIN Keywords

EXPLAIN Keywords Continued

Explain Example – Full Table Scan

Explain Example – Unique Primary Index (UPI)

Explain Example – Non-Unique Primary Index (NUPI)

Explain Example – Unique Secondary Index (USI)

Explain Example – Redistributed to All-AMPs

Explain Example – Row Hash Match Scan

Explain Example – Duplicated on All-AMPs

Explain Example – Low Confidence

Explain Example – High Confidence

Explain Example – Product Join

Explain Example – BMSMS

Explain Terminology for Partitioned Primary Index Tables

Explain Example – From a Single Partition

Explain Example – From N Partitions

Explain Example – Partitions and Current_Date

BACK TO BIG DATA CLASSES