Yellowbrick Details

For pricing and availability

Contact: Tom Coffing

Email: Tom.Coffing@CoffingDW.com

Phone: 513 300-0341

Yellowbrick Architecture and SQL

Description:  In this course, students will learn the Yellowbrick architecture with an excellent level of detail.  Students will also learn the clever tricks and tips that make Yellowbrick unique, including min-max metadata pruning, memory management, and columnar and rowstore strategies. In addition, students will create all types of Yellowbrick tables and views and learn how to performance tune Yellowbrick tables and queries. Students will also gain significant knowledge of SQL, starting at the most basic level and building to intermediate and advanced levels.  Advanced topics include date functions, advanced analytics, and Stored Procedures.  There are over 800 examples of managing Yellowbrick and 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 Yellowbick 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 Yellowbrick architecture, creating, and managing tables and objects, utilizing stored procedures and user-defined functions, plus performance tuning expertise, and basic, intermediate, and advanced SQL hands-on training. 

 

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

 

Prerequisites: None

 

Duration:  3-Days

 

Topics:

Yellowbrick Architecture

Why Columnar?

Why Rowstore for Data Loading
Yellowbrick Table Distribution Style Options
Best practices for Performance Tuning
Tuning Joins
Best Practices for Partitioning
Improving Performance By Defining a Sort Key
Each Block Comes With Metadata
System Tables
Aggregation
Inner and Outer Joins
SQL Regex Operators

Similar To Operators
Date and Time Functions
Using Time Zones
Format Functions
Analytics and Window Functions
Temporary Tables
Subqueries
Strings
Interrogating the Data
Views
Set Operators
Creating Tables
Data Manipulation Language (DML)
Stored Procedures
Math Functions
Statistical Aggregate Functions

 

Yellowbrick Architecture and SQL Course Outline

 

Chapter 1 - The Yellowbrick Architecture

 

Nexus Desktop - Combining all tools to all databases

What is Parallel Processing?

Yellowbrick has Linear Scalability

Yellowbrick Can Scale

The Basics of a Single Computer

Data in Memory is Fast as Lightning

Parallel Processing Of Data

A Table has Rows and Columns

Each Worker Organizes the Rows inside a Data Block

Moving Data Blocks is Like Checking In Luggage

Disturbing facts

Why Columnar?

Row Based Blocks vs. Columnar Based Blocks

As Row-Based Tables Get Bigger, the Blocks Append

Data Blocks Are Processed One at a Time Per Unit

Columnar Tables Store Each Column in Separate Blocks

Visualize the Data – Rows vs. Columns

Yellowbrick Table Distribution Style Options

Distribution Key Where the Data is Unique

A Table with a Sort Key

Distribution Key Where the Data is Non-Unique

Distribution Key is Replicate

Random Distribution Key

 

 

 

Chapter 2 – Yellowbrick Performance Tuning

 

Nexus Chameleon

The emp_tbl CREATE Statement with Eight Rows

Yellowbrick Quiz –Place the Rows on the Proper Worker

Place the Remaining Rows on the Proper Worker

The Rows have been Placed on the Proper Worker

Yellowbrick Workers will use Columnar Storage

Yellowbrick Workers Create Metadata for each Block

Quiz – How Many Blocks are Read into Memory?

Answer – How Many are Read into Memory?

Quiz – How Many Blocks are Read into Memory?

Answer – How Many Blocks are Read into Memory?

Quiz – How Many Blocks are Read into Memory?

Answer – How Many Blocks Read into Memory?

emp_tbl CREATE Statement with a DISTKEY of deptno

Yellowbrick Quiz –Place the Rows on the Proper Worker

Place the Remaining Rows on the Proper Worker

The Rows have been Placed on the Proper Worker

Yellowbrick Workers will use Columnar Storage

Quiz – Can you Place the Metadata in Correctly?

Answer – Can you Place the Metadata in Correctly?

Quiz – How Many Blocks are Read into Memory?

Answer – How Many Blocks are Read into Memory?

Quiz – How Many Blocks are Read into Memory?

Answer – How Many Blocks are Read into Memory?

Quiz – How Many Blocks are Read into Memory?

Answer – How Many Blocks are Read into Memory?

Which Join is More Efficient and Why?  This Page or Next?

Which Join is More Efficient? This Page or Previous?

Two Rows Joining Must Physically be on the Same Worker

Table Joins – A Bad Example - What Must Yellowbrick Do?

emp_tbl Redistributes by Hashing deptno Temporarily

Replicate is for Joins

Table Joins – Create Table with a Distribute Replicate

Random Distribution Key is for Even Distribution and Joins

Creating a Table With No Distribution Method

Create Tables with a DISTSTYLE ALL and EVEN

Quiz - Fact and Dimension Table Distribution Key Design

Answer - Fact and Dimension Table Distribution Key Design

Quiz – Which Tables Would Yellowbrick Join First?

Answer – Which Tables Would Yellowbrick Join First?

Improving Performance by Defining a Sort Key

Restrictions on Partitioned Tables

Best Practices for Partitioning

Range Partitioning Per Day

Range Partitioning Per Day

Describe Only DDL

Range Partitioning Per Week

Range Partitioning Per Month

Range Partitioning Per Month Visual

Range Partitioning a Timestamp Per Hour

Range Partitioning Per Hour Visual

Range Partitioning with a Number

Hash Partitioning

Range Partitioning Per Hash Visual

Hash Partitioning on Multiple Columns

Supported Columns for Partitioning

Creating a Table with a Sort Key

Creating a Table with a Cluster Key

Sequence

Fact and Dimension Table Distribution Key Designs

Improving Performance By Defining a Sort Key

Sort Keys Help Group By, Order By, and Window Functions

Each Block Comes With Metadata

How Data Might Look On A Worker

Question – How Many Blocks Move Into Memory?

Answer – How Many Blocks Move Into Memory?

Quiz – Master that Query With the Metadata

Answer to Quiz – Master that Query With the Metadata

The ANALYZE Command Collects Statistics

Yellowbrick ANALYZES Some Create Statements

Yellowbrick Limits

 

Chapter 3 - System Tables

 

Describe and Describe With DDL

Describe Only DDL

Sys.User View

Sys.Database View

Sys.Schema View

Sys.Table View

Sys.Column View

Sys.Query View

Sys.Procedure View

 

Chapter 4 - The Basics of SQL

 

Nexus Desktop - Combining all tools to all databases

Find Your Current Schema

Seeing Your Current Search Path

Adding a Schema to Your Search Path

Setting the Default Schema

Introduction

SELECT * (All Columns) in a Table

SELECT Specific Columns in a Table

Commas in the Front or Back?

Place your Commas in front for better Debugging Capabilities

Sort the Data with the ORDER BY Keyword

Use a Column name or Number in an ORDER BY Statement

Two Examples of ORDER BY using Different Techniques

Changing the ORDER BY to Descending Order

Null Values Sort Last in Ascending Mode (Default)

Null Values sort First in Descending Mode (DESC)

Order By with Nulls First

Major Sort vs. Minor Sort

Multiple Sort Keys using names vs. Numbers

Sorts are Alphabetical, NOT Logical

Using A Valued 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

Comments using Double Dashes are Single Line Comments

Comments for Multi-Lines

Comments for Multi-Lines As Double Dashes Per Line

Yellowbrick System Commands

Comments are a Great Technique for Finding SQL Errors

Move Data to Yellowbrick Effortlessly

 

Chapter 5 - The WHERE Clause

 

NexusCore Servers - Control Network and Data Movement

The WHERE Clause limits Returning Rows

Using a Column ALIAS in the WHERE Clause

Numbers Don't Need Single Quotes

Searching for null Values Using Equality Returns Nothing

Is NULL

IS Not Null

Using 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

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

A Technique for Handling Nulls with a NOT IN List

Technique 2 for Handling Nulls with a NOT IN List

The BETWEEN Statement is Inclusive

The NOT BETWEEN Statement is also Inclusive

The BETWEEN Statement Works for Character Data

LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’

Using LIKE for all Cases with Lower and Upper

Using ILIKE Handle Case Issues

LIKE command Underscore is Wildcard for one Character

Finding Anyone Whose name End in 'Y'

Escape Character in the LIKE Command changes Wildcards

Escape Characters Turn off Wildcards in the LIKE Command

SQL Regex Operators

Quiz - SQL Regex Operators

Answer - SQL Regex Operators

SQL Regex Operator that Finds Matches Case-Insensitive

Quiz - SQL Regex Operators

Answer - SQL Regex Operators

Quiz – Find a Name that Does Not Match – Case Sensitive

Answer – Find a Name that Does Not Match – Case Sensitive

Quiz – Find Strings that Do Not Match – Case Insensitive

Answer – Find Strings that Do Not Match – Case Insensitive

The REPLACE Function

REGEXP_REPLACE

REGEXP_INSTR

Similar To Operators

Similar To Operators Continued

Similar To Example With Lower Case Letters

Similar To Example With Lower and Upper-Case Letters

Similar To Example Multiple Lower and Upper-Case Letters

Similar To Example With Multiple Occurrences

Multiple Occurrences Must Be Consecutive

 

Chapter 6 - 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?

The FETCH Clause

 

Chapter 7 - Aggregation

 

Quiz – You calculate the Answer Set in your Mind

Answer – You calculate the Answer Set in your Mind

Quiz 2  – You calculate the Answer Set in your Mind

There are Five Aggregates

Quiz – How many rows come back?

Answer – How many rows come back?

Casting a Data Type

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

 

Chapter 8 - Join Functions

 

A Two-Table Join Using Traditional Syntax

A two-table join using Non-ANSI Syntax with Table Alias

You Can Fully Qualify All 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?

Super Quiz – Can You Find the Difficult Error?

Answer to Super Quiz – Can You Find the Difficult 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 Results

Right Outer Join

Right Outer Join Example and Results

Full Outer Join

Full Outer Join Results

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

The DREADED Product Join

The DREADED Product Join Results

Cartesian Product Join with Traditional Syntax

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 Using 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 Non-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 9 - Date Functions

 

Current_Date

Current_Date and Current_Timestamp

Add or Subtract From a Date

Current_UTC_Timestamp

Local_Time and Local_Timestamp With Precision

The Transaction, Statement, and Clock Timestamp

Now() and TimeOfDay Function

Add or Subtract Days From a Date

Subtract Two Dates for a Difference in Days

DAYS_BETWEEN

MONTHS_BETWEEN

The ADD_MONTHS Command

Using the ADD_MONTHS Command to Add 1 Year

Using the ADD_MONTHS Command to Add 5 Years

Formatting a Date

To_Char Formatting Options (1 of 3)

To_Char Formatting Options (2 of 3)

To_Char Formatting Options (3 of 3)

TO_CHAR Example that is Impressive

Supported Dateparts

The EXTRACT Command

EXTRACT from DATES and TIME

Month and Year

Using CASE and Extract to Reformat Dates

Using CAST and SUBSTRING to Reformat Dates

Datediff

Another Datediff Example

The Date_Part Function

Dateadd

Dateadd Function and Add_Months Function are Different

The Date_Trunc Function

Date_Trunc Command With Time

Date_Trunc Command With Dates

Last_Day

Make_Date

Make_Time

Make_Timestamp

Using CAST to Convert Dates and Derived Data

The AGE Function

Using Day, Month, and Year intervals

The Basics of a Simple Interval

Year to Month Interval

Converting a Timestamp into Time

Determining if the Current_Date is a Leap Year

Determining if the Current_Timestamp is a Leap Year

Make_Interval

The To_Timestamp Function

The OVERLAPS Command

An OVERLAPS Example that Returns No Rows

The OVERLAPS Command using TIME

Epoch

Time Zones

Setting Time Zones

 

Chapter 10 - Analytics

 

Row_Number

Quiz – How did the Row_Number Reset?

Answer – How did the Row_Number Reset?

Find the Top Two Students Per class_code

RANK

Dense_Rank

Getting RANK to Sort in DESC Order

RANK() OVER and PARTITION BY

RANK() OVER and a Derived Table

RANK() OVER and a WITH Derived Table

RANK vs. DENSE_RANK

DENSE_RANK() OVER and PARTITION BY

PERCENT_RANK() OVER with 14 rows in Calculation

PERCENT_RANK() OVER with 21 rows in Calculation

PERCENT_RANK() OVER and PARTITION BY

Cumulative Sum

Cumulative Sum with CAST

Cumulative Sum – The Sort Explained

Cumulative Sum – Rows Unbounded Preceding Explained

Cumulative Sum – Making Sense of the Data

Cumulative Sum – Major and Minor Sort Keys

Reset with a PARTITION BY Statement

Totals and Subtotals through Partition By

Moving Sum

Moving SUM every 3-rows Vs. a Continuous Average

Partition By Resets the Calculations

Moving Average

The Moving Window is Current Row and Preceding

How Moving Average Handles the Order By

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

Moving Difference

Moving Difference with Partition By

Finding a Value of a Column in the Next Row with MIN

Finding a Next Row Value with MIN and PARTITION BY

Finding The Next Date using MAX

Finding Multiple Values of a Column in Upcoming Rows

COUNT OVER for a Sequential Number

COUNT OVER using ROWS UNBOUNDED PRECEDING

The MAX OVER Command

MAX OVER  with PARTITION BY Reset

The MIN OVER Command

The MIN OVER Command with PARTITION BY

Different Windowing Options

How Ntile Works

Ntile

Ntile Continued

Ntile Percentile

Another Ntile Example

Using Quantiles (Partitions of Four)

NTILE With a Partition

Using FIRST_VALUE

FIRST_VALUE Minus Current Row

FIRST_VALUE After Sorting by the Highest Value

FIRST_VALUE with Partitioning

Using LAST_VALUE

LAST_VALUE – Current Row

Using LEAD

Using LEAD with a PARTITION Statement

Using LEAD With an Offset of 2

Using LEAD With an Offset of 2 and a PARTITION

Using LAG

Using LAG with a PARTITION Statement

Using LAG With an Offset of 2

Using LAG With an Offset of 2 and a PARTITION

CUME_DIST

CUME_DIST With a Partition

CURRENT ROW AND UNBOUNDED FOLLOWING

Different Windowing Options

MEDIAN Example

MEDIAN with Partitioning and a WHERE Clause

MEDIAN with Partitioning

PERCENTILE_CONT Function Description and Syntax

Final Result Information About PERCENTILE_CONT

PERCENTILE_CONT Function Arguments

PERCENTILE_CONT Example

PERCENTILE_CONT Example with Percentage Change

PERCENTILE_CONT With PARTITION Example

PERCENTILE_CONT With PARTITION and (0.4)

PERCENTILE_DISC Function Description and Syntax

PERCENTILE_DISC Function Arguments

PERCENTILE_DISC Example

PERCENTILE_DISC Example with Percentage Change

PERCENTILE_DISC With PARTITION Example

PERCENTILE_DISC With PARTITION and (0.4)

 

Chapter 11 - Temporary Tables

 

CREATING A Derived Table

Naming the Derived Table

Aliasing the Column names in the Derived Table

CREATING A Derived Table using the WITH Command

Derived Query Examples with Three Different Techniques

Most Derived Tables Are Used To Join To Other Tables

The Three Components of a Derived Table

Visualize This Derived Table

Our Join Example Using The WITH Syntax

An Example of Two Derived Tables in a Single Query

Creating a Temporary Table

Creating a Temporary Table using a CTAS

CREATE a Temporary Table with Some of the Columns

CREATE a Temporary Table AS (CTAS) Using a Join

Dropping a Temporary Table

 

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

The Three Steps of How a Basic Subquery Works

These are Equivalent Queries

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

Answer To Quiz – Write the Extreme 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

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

Quiz – Write the Extreme Correlated Subquery

Answer To Quiz – Write the Extreme Correlated Subquery

NOT IN Subquery Returns Nothing when nulls are Present

Fixing a NOT IN Subquery with Null Values

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

IN is equivalent to =ANY

Using a Correlated Exists

How a Correlated Exists Matches Up

The Correlated NOT Exists

 

Chapter 13 – Strings

 

UPPER and lower  Functions

The Length Command Counts Characters

LENGTH Works on Fixed Length Columns

The Char_Length Command Counts Characters

CHAR_LENGTH Works on Fixed Length Columns

CHAR_LENGTH and OCTET_LENGTH

The TRIM Command trims both Leading and Trailing Spaces

The RTRIM and LTRIM Command Trims Spaces

TRIM can also TRIM Characters

Concatenation

Concat and Concat_WS for Concatenation

The SUBSTR and SUBSTRING Commands

How SUBSTR Works with NO ENDING POSITION

Using SUBSTR and CHAR_LENGTH Together

The POSITION Command finds a Letters Position

The POSITION Command is Brilliant with SUBSTR

CHARINDEX Finds a Letter(s) Position in a String

The CHARINDEX Command is brilliant with SUBSTRING

The CHARINDEX Command Using a Literal

LPAD and RPAD

The REPLACE Function

The ASCII Function

The Reverse String Function

The RIGHT Function

The LEFT and RIGHT Functions

 

Chapter 14 – Interrogating the Data

 

Quiz – Fill in the Answers for the NULLIF Command

Answer – Fill in the Answers for the NULLIF Command

The COALESCE Command

COALESCE is Equivalent to this CASE Statement

Some Great CAST (Convert And Store) Examples

A Rounding Example Using CAST

CAST will Round Values up or Down

Valued Case vs. Searched Case

Combining Searched Case and Valued Case

The query above uses both a Valued Case and Searched Case.

Decode

A Trick for getting a Horizontal Case

Put a Valued CASE in the ORDER BY

Put a Searched CASE in the ORDER BY

Put a Decode in the ORDER BY

 

Chapter 15 - View Functions

 

The Fundamentals of Views

Creating a Simple View to Restrict Sensitive Columns

Creating a Simple View to Restrict Rows

Creating a View to Join Tables Together

Basic Rules for Views

How to Modify a View

The Exception to the ORDER BY Rule inside a View

Derived Columns in a View Should Contain a Column Alias

The Standard Way Most Aliasing is Done

Another Way to Alias Columns in a View CREATE

What Happens When a View Column gets Aliased Twice?

 

Chapter 16 - Set Operators

 

Rules of Set Operators

Quiz - Intersect Explained Logically

Answer - Intersect Explained Logically

Quiz - Union Explained Logically

Answer - Union Explained Logically

Quiz - Union ALL Explained Logically

Answer - Union ALL Explained Logically

Quiz - Except Explained Logically

Answer - Except Explained Logically

Quiz - Testing Your Knowledge

Answer - Testing Your Knowledge

An Equal Amount of Columns in both SELECT List

The Top Query handles all Aliases

The Bottom Query does the ORDER BY

Intersect Challenge

Answer - Intersect Challenge

UNION Vs. UNION ALL

Using UNION ALL and Literals

Using UNION ALL for speed in Merging Data Sets

Great Trick:  Place your Set Operator in a Derived Table

A Great Example of how EXCEPT works

USING Multiple SET Operators in a Single Request

Changing the Order of Precedence with Parentheses

 

Chapter 17 - Creating Tables

 

Distribution Key Where the Data is Unique

A Table with a Sort Key

Distribution Key Where the Data is Non-Unique

Distribution Key is Replicate

Random Distribution Key

Creating a Table With No Distribution Method

Range Partitioning Per Day

Describe Only DDL

Range Partitioning Per Week

Range Partitioning Per Month

Range Partitioning a Timestamp Per Hour

Range Partitioning with a Number

Hash Partitioning

Hash Partitioning on Multiple Columns

Supported Columns for Partitioning

Creating a Table with a Sort Key

Creating a Table with a Cluster Key

Sequence

Creating a Schema and a Table

Create a Table IF NOT EXISTS

A Table with a Not Null Constraint

Create a Table with a Column Default Value

Create a Table with a Primary Key

Create Table AS (CTAS) Populates the Table With Data

Create Table AS (CTAS) can Choose Certain Columns

CREATE a Temporary Table AS (CTAS)

CREATE a Temporary Table AS (CTAS) Using a Join

 

Chapter 18 - Data Manipulation Language (DML)

 

INSERT Syntax # 1

INSERT Syntax # 2

INSERT Example with Multiple Rows

Inserting Null Values into a Table

INSERT/SELECT Command

INSERT/SELECT to Build a Data Mart

UPDATE Examples

Example of Subquery UPDATE Command

Deleting Rows in a Table

 

Chapter 19 - Statistical Aggregate Functions

 

The Stats Table

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

 

Chapter 20 - Stored Procedures

 

Yellowbrick Stored Procedures

Calling a Stored Procedure

Modifying a Stored Procedure

How to Define a Variable with a Default Value

How to Give a Variable a Value

An IN Variable is passed to the Procedure during the CALL

Using Loops in Stored Procedures

Using a WHILE Loop

Using a FOR Loop

Stored Procedure Workshop

Stored Procedure Workshop Answer

Stored Procedure Workshop Alternative Answer

 

Chapter 21 - Mathematical Functions

 

The Sys.Const Table

Numeric Manipulation Functions

ABS

ACOS

ASIN

Finding the Cube Root

Ceiling

Floor

COS

DEGREES

DIV

ERF

EXP

LN

LOG

MOD

POWER

PROBNORM

RADIANS

RANDOM()

ROUND

SIGN

SIN

SQRT

TRUNC