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