# Actian Matrix Architecture and SQL

### Customized Onsite Training

3
Days
• Customized Content
• For Groups of 5+
• Online or On-location
• Expert Instructors
Overview

In this training class, students will learn the Actian Matrix Architecture and SQL starting at the most basic level and going to the most advanced level with many examples.

Goals
1. Learn to gain a deeper knowledge and understanding of the Actian Matrix Architecture and SQL and how to write it.
Outline
1. What is Columnar?
1. What is Parallel Processing?
2. The Basics of a Single Computer
3. Data in Memory is fast as Lightning
4. Parallel Processing Of Data
5. A Table has Columns and Rows
6. Rows are placed inside a Data Block
7. Moving Data Blocks is Like Checking in Luggage
8. Facts That Are Disturbing
9. Why Columnar?
10. Row Based Blocks vs. Columnar Based Blocks
11. As Row-Based Tables Get Bigger, the Blocks Split
12. Data Blocks Are Processed One at a Time per Unit
13. Columnar Tables Store Each Column in Separate Blocks
14. Visualize the Data - Rows vs. Columns
15. The Architecture of Actian Matrix
16. Matrix has Linear Scalability
17. Distribution Styles
18. Distribution Key Where the Data is Unique
19. Another Way to Create a Table
20. Distribution Key Where the Data is Non-Unique
21. Even Distribution Key
22. Matching Distribution Keys for Co-Location of Joins
23. Big Table / Small Table Joins
24. Fact and Dimension Table Distribution Key Designs
25. Improving Performance by Defining a Sort Key
26. Sort Keys Help Group By, Order by and Window Functions
27. Each Block Comes With Metadata
28. How Data Might Look On a Slice
29. Question - How Many Blocks Move Into Memory?
30. Answer - How Many Blocks Move Into Memory?
31. Quiz - Master that Query with the Metadata
32. Answer to Quiz - Master that Query with the Metadata
33. The ANALYZE Command Collects Statistics
34. Matrix Automatically ANALYZES Some Create Statements
35. What is a Vacuum?
36. When is a Good Time to Vacuum?
37. The VACUUM Command Grooms a Table
38. The Matrix database catalog also needs periodic vacuuming and indexing
39. Database Limits
40. Creating a Database
41. Creating a User
42. Dropping a User
43. Inserting Into a Table
44. Renaming a Table or a Column
45. Adding and Dropping a Column to a Table
2. Best Practices for Table Design
1. Converting Table Structures to Actian Matrix
2. Converting Table Structures to Actian Matrix Finale
3. Best Practices for Designing Tables
4. Choose the Best Sort Key
5. Each Block Comes With Metadata
6. Creating a Sort Key
7. Sort Keys Help Group By, Order by and Window Functions
8. Choose a Great Distribution Key
9. Distribution Key Where the Data is Unique
10. Matching Distribution Keys for Co-Location of Joins
11. Big Table / Small Table Joins
12. Define Primary Key and Foreign Key Constraints
13. Primary Key and Foreign Key Examples
14. Use the Smallest Column Size When Creating Tables
15. Use Date/Time Data Types for Date Columns
16. Specify Redundant Predicates on the Sort Column
17. Setting the statement_timeout to Abort Long Queries
3. Systems Tables
1. Actian Matrix System Tables
2. Trouble Shooting Catalog Table pg_table_def
3. Seeing the System Tables in your Nexus Tree
4. Catalog Table pg_table_def
5. Checking Tables for Skew (Poor Distribution)
6. Checking All Statements That Used the Analyze Command
7. Checking Tables for Skew (Poor Distribution)
8. Checking for Details about the Last Copy Operation
9. Checking When a Table Has Last Been Analyzed
10. Checking For Column Information on a Table
11. System tables for troubleshooting data loads
12. Determining Whether a Query is writing to Disk
4. Compression
1. Compression Types
2. Byte Dictionary Compression
3. Delta Encoding
4. Deflate Encoding - Lempel-Ziv-Oberhumer (LZO)
5. Mostly Encoding
6. Runlength encoding
7. Text255 and Text32k Encodings
8. Analyze Compression using xpx 'complyze'
9. Analyze Results from xpx 'complyze'
10. Copy
5. Temporary Tables
1. Create Table Syntax
2. Basic Temporary Table Examples
3. More Advanced Temporary Table Examples
4. Advanced Temporary Table Examples
5. Table Limits and CTAS
6. Performing a Deep Copy
7. Deep Copy Using the Original DDL
8. Deep Copy Using A CTAS
9. Deep Copy Using A Create Table LIKE
10. Deep Copy by Creating a Temp Table and Truncating Original
11. CREATING A Derived Table
12. The Three Components of a Derived Table
13. Naming the Derived Table
14. Aliasing the Column Names in the Derived Table
15. Visualize This Derived Table
16. Most Derived Tables Are Used To Join To Other Tables
17. Multiple Ways to Alias the Columns in a Derived Table
18. Our Join Example with a Different Column Aliasing Style
19. Column Aliasing Can Default for Normal Columns
20. CREATING a Derived Table using the WITH Command
21. Our Join Example With the WITH Syntax
22. WITH Statement That Uses a SELECT *
23. A WITH Clause That Produces Two Tables
24. The Same Derived Query shown Three Different Ways
25. Quiz - Answer the Questions
26. Answer to Quiz - Answer the Questions
27. Clever Tricks on Aliasing Columns in a Derived Table
28. A Derived Table lives only for the lifetime of a single query
29. An Example of Two Derived Tables in a Single Query
30. Connecting To Matrix via Nexus
31. Connecting To Matrix via Nexus
32. Connecting To Matrix via Nexus
33. Connecting To Matrix via Nexus
6. Explain
1. Three Ways to Run an EXPLAIN
2. EXPLAIN - Steps, Segments and Streams
3. EXPLAIN Terms for Scans and Joins
4. EXPLAIN Terms for Aggregation and Sorts
5. EXPLAIN Terms for Set Operators and Miscellaneous Terms
6. EXPLAIN Terms for Set Operators and Miscellaneous Terms
7. EXPLAIN Example and the Cost
8. EXPLAIN Example and the Rows
9. EXPLAIN Example and the Width
10. Simple EXPLAIN Example and the Costs
11. EXPLAIN Join Example Using DS_BCAST_INNER
12. EXPLAIN Join Example Using DS_DIST_NONE
13. EXPLAIN Showing DS_DIST_NONE Visually
14. EXPLAIN With a Warning
15. EXPLAIN for Ordered Analytics Such as CSUM
16. EXPLAIN for Scalar Aggregate Functions
17. EXPLAIN for HashAggregate Functions
18. EXPLAIN Using Limit, Merge and Sort
19. EXPLAIN Using a WHERE Clause Filter
20. EXPLAIN Using the Keyword Distinct
21. EXPLAIN for Subqueries
7. Basic SQL Functions
1. Finding the Current Schema on the Leader Node
2. Getting Things Setup in Your Search Path
3. Five Details You Need To Know About the Search_Path
4. Introduction
5. SELECT * (All Columns) in a Table
6. SELECT Specific Columns in a Table
7. Commas in the Front or Back?
8. Place your Commas in front for better Debugging Capabilities
9. Sort the Data with the ORDER BY Keyword
10. ORDER BY Defaults to Ascending
11. Use the Name or the Number in your ORDER BY Statement
12. Two Examples of ORDER BY using Different Techniques
13. Changing the ORDER BY to Descending Order
14. NULL Values sort First in Ascending Mode (Default)
15. NULL Values sort Last in Descending Mode (DESC)
16. Major Sort vs. Minor Sorts
17. Multiple Sort Keys using Names vs. Numbers
18. Sorts are Alphabetical, NOT Logical
19. Using A CASE Statement to Sort Logically
20. How to ALIAS a Column Name
21. A Missing Comma can by Mistake become an Alias
22. Comments using Double Dashes are Single Line Comments
23. Comments for Multi-Lines
24. Comments for Multi-Lines as Double Dashes Per Line
25. A Great Technique for Comments to Look for SQL Errors
8. The WHERE Clause
1. Using Limit to bring back a Sample
2. Using Limit with an Order by Statement
3. The WHERE Clause limits Returning Rows
4. Using a Column ALIAS throughout the SQL
5. Double Quoted Aliases are for Reserved Words and Spaces
6. Character Data needs Single Quotes in the WHERE Clause
7. Character Data needs Single Quotes, but Numbers Don't
8. NULL means UNKNOWN DATA so Equal (=) won't Work
9. Use IS NULL or IS NOT NULL when dealing with NULLs
10. NULL is UNKNOWN DATA so NOT Equal won't Work
11. Use IS NULL or IS NOT NULL when dealing with NULLs
12. Using Greater Than or Equal To (>=)
13. AND in the WHERE Clause
14. Troubleshooting AND
15. OR in the WHERE Clause
16. Troubleshooting Or
17. Troubleshooting Character Data
18. Using Different Columns in an AND Statement
19. Quiz - How many rows will return?
20. Answer to Quiz - How many rows will return?
21. What is the Order of Precedence?
22. Using Parentheses to change the Order of Precedence
23. Using an IN List in place of OR
24. The IN List is an Excellent Technique
25. IN List vs. OR brings the same Results
26. Using a NOT IN List
27. A Technique for Handling Nulls with a NOT IN List
28. Another Technique for Handling Nulls with a NOT IN List
29. BETWEEN is Inclusive
30. NOT BETWEEN is Also Inclusive
31. LIKE command Underscore is Wildcard for one Character
32. LIKE Command Works Differently on Char Vs Varchar
33. The Ilike Command Is NOT Case Sensitive
34. Troubleshooting LIKE Command on Character Data
35. Introducing the TRIM Command
36. Quiz - What Data is Left Justified and what is Right?
37. Numbers are Right Justified and Character Data is Left
38. Answer - What Data is Left Justified and what is Right?
39. An Example of Data with Left and Right Justification
40. A Visual of CHARACTER Data vs. VARCHAR Data
41. Use the TRIM command to remove spaces on CHAR Data
42. Like and Your Escape Character of Choice
43. Like and the Default Escape Character
44. Similar To Operators
45. Similar To Operators
46. Similar To Example with Lower Case Letters
47. Similar To Example with Lower and Upper Case Letters
48. Similar To Example with Multiple Occurrences
49. Multiple Occurrences Must Be Consecutive
9. Distinct Vs Group By AND TOP
1. The Distinct Command
2. Distinct vs. GROUP BY
3. Quiz - How many rows come back from the Distinct?
4. Answer - How many rows come back from the Distinct?
5. TOP Command
6. TOP Command is brilliant when ORDER BY is used!
7. What is the Difference between TOP and LIMIT?
10. Aggregation
1. Quiz - You calculate the Answer Set in your own Mind
2. Answer - You calculate the Answer Set in your own Mind
3. The 3 Rules of Aggregation
4. There are Five Aggregates
5. Quiz - How many rows come back?
6. Answer - How many rows come back?
7. Troubleshooting Aggregates
8. GROUP BY when Aggregates and Normal Columns Mix
9. GROUP BY delivers one row per Group
10. GROUP BY Dept_No or GROUP BY 1 the same thing
11. Limiting Rows and Improving Performance with WHERE
12. WHERE Clause in Aggregation limits unneeded Calculations
13. Keyword HAVING tests Aggregates after they are Totaled
14. Keyword HAVING is like an Extra WHERE Clause for Totals
11. Join Functions
2. A two-table join using Non-ANSI Syntax with Table Alias
3. You Can Fully Qualify All Columns
4. A two-table join using ANSI Syntax
5. Both Queries have the same Results and Performance
6. Quiz - Can You Finish the Join Syntax?
7. Answer to Quiz - Can You Finish the Join Syntax?
8. Quiz - Can You Find the Error?
9. Answer to Quiz - Can You Find the Error?
10. Super Quiz - Can You Find the Difficult Error?
11. Answer to Super Quiz - Can You Find the Difficult Error?
12. Quiz - Which rows from both tables won't return?
13. Answer to Quiz - Which rows from both tables Won't Return?
14. LEFT OUTER JOIN
15. LEFT OUTER JOIN Results
16. Left Outer Joins Compatible with Oracle
17. RIGHT OUTER JOIN
18. RIGHT OUTER JOIN Example and Results
19. Right Outer Joins Compatible with Oracle
20. Right Outer Joins Compatible with Oracle
21. FULL OUTER JOIN
22. FULL OUTER JOIN Results
23. Which Tables are the Left and which are the Right?
24. Answer - Which Tables are the Left and which are the Right?
25. INNER JOIN with Additional AND Clause
26. ANSI INNER JOIN with Additional AND Clause
27. ANSI INNER JOIN with Additional WHERE Clause
28. OUTER JOIN with Additional WHERE Clause
29. OUTER JOIN with Additional AND Clause
30. OUTER JOIN with Additional AND Clause Results
31. Quiz - Why is this considered an INNER JOIN?
32. The DREADED Product Join
33. The DREADED Product Join Results
34. The Horrifying Cartesian product Join
35. The ANSI Cartesian Join will ERROR
36. Quiz - Do these Joins Return the Same Answer Set?
37. Answer - Do these Joins Return the Same Answer Set?
38. The CROSS JOIN
39. The CROSS JOIN Answer Set
40. The Self Join
41. The Self Join with ANSI Syntax
42. Quiz - Will both queries bring back the same Answer Set?
43. Answer - Will both queries bring back the same Answer Set?
44. Quiz - Will both queries bring back the same Answer Set?
45. Answer - Will both queries bring back the same Answer Set?
46. How would you join these two tables?
47. An Associative Table is a Bridge that Joins Two Tables
48. Quiz - Can you write the 3-Table Join?
49. Answer to quiz - Can you write the 3-Table Join?
50. Quiz - Can you write the 3-Table Join to ANSI Syntax?
51. Answer - Can you write the 3-Table Join to ANSI Syntax?
52. Quiz - Can you Place the ON Clauses at the End?
53. Answer - Can you Place the ON Clauses at the End?
54. The 5-Table Join - Logical Insurance Model
55. Quiz - Write a Five Table Join Using ANSI Syntax
56. Answer - Write a Five Table Join Using ANSI Syntax
57. Quiz - Write a Five Table Join Using Non-ANSI Syntax
58. Answer - Write a Five Table Join Using Non-ANSI Syntax
59. Quiz -Re-Write this putting the ON clauses at the END
60. Answer -Re-Write this putting the ON clauses at the END
12. Date Functions
1. Current_Date
2. TIMEOFDAY ()
3. SYSDATE Returns a Timestamp with Microseconds
4. GETDATE Returns a Timestamp without Microseconds
5. Add or Subtract Days from a date
6. The ADD_MONTHS Command Returns a Timestamp
7. The ADD_MONTHS Command with Trunc Removes Time
8. ADD_MONTHS Command to Add 1-Year or 5-Years
9. Dateadd Function and Add_Months Function are Different
10. The EXTRACT Command
11. EXTRACT from DATES and TIME
12. EXTRACT with DATE and TIME Literals
13. EXTRACT of the Month on Aggregate Queries
14. The Datediff command
15. The Datediff Function on Column Data
16. The Date_Part Function Using a Date
17. The Date_Part Function Using a Time
18. Date_Part Abbreviations
19. The to_char command
20. Conversion Functions
21. Conversion Function Templates
22. Conversion Function Templates Continued
23. Formatting a Date
24. A Summary of Math Operations on Dates
25. Using a Math Operation to find your Age in Years
26. Date Related Functions
27. A Side Title example with Reserved Words as an Alias
28. Implied Extract of Day, Month and Year
29. DATE_PART Function
30. DATE_PART Function using an ALIAS
31. DATE_TRUNC Function
32. DATE_TRUNC Function using TIME
33. MONTHS_BETWEEN Function
34. MONTHS_BETWEEN Function in Action
35. ANSI TIME
36. ANSI TIMESTAMP
37. Matrix TIMESTAMP Function
38. Matrix TO_TIMESTAMP Function
39. Matrix NOW () Function
40. Matrix TIMEOFDAY Function
41. Matrix AGE Function
42. Time Zones
43. Setting Time Zones
44. Using Time Zones
45. Intervals for Date, Time and Timestamp
46. Using Intervals
47. Troubleshooting the Basics of a Simple Interval
48. Interval Arithmetic Results
49. A Date Interval Example
50. A Time Interval Example
51. A DATE Interval Example
52. A Complex Time Interval Example using CAST
53. A Complex Time Interval Example using CAST
54. The OVERLAPS Command
55. An OVERLAPS Example that Returns No Rows
56. The OVERLAPS Command using TIME
57. The OVERLAPS Command using a NULL Value
13. OLAP Functions
1. CSUM
2. CSUM - The Sort Explained
3. CSUM - Rows Unbounded Preceding Explained
4. CSUM - Making Sense of the Data
5. CSUM - Making Even More Sense of the Data
6. CSUM - The Major and Minor Sort Key(s)
7. Reset with a PARTITION BY Statement
8. PARTITION BY only Resets a Single OLAP not ALL of them
9. ANSI Moving Window is Current Row and Preceding n Rows
10. How ANSI Moving SUM Handles the Sort
11. Quiz - How is that Total Calculated?
12. Answer to Quiz - How is that Total Calculated?
13. Moving SUM every 3-rows Vs a Continuous Average
14. Partition by Resets an ANSI OLAP
15. Moving Average
16. The Moving Window is Current Row and Preceding
17. How Moving Average Handles the Sort
18. Quiz - How is that Total Calculated?
19. Answer to Quiz - How is that Total Calculated?
20. Quiz - How is that 4th Row Calculated?
21. Answer to Quiz - How is that 4th Row Calculated?
22. Moving Average every 3-rows Vs a Continuous Average
23. Partition by Resets an ANSI OLAP
24. RANK Defaults to Ascending Order
25. Getting RANK to Sort in DESC Order
26. RANK () OVER and PARTITION BY
27. RANK () OVER and LIMIT
28. PERCENT_RANK () OVER
29. PERCENT_RANK () OVER with 14 rows in Calculation
30. PERCENT_RANK () OVER with 21 rows in Calculation
31. Quiz - What Causes the Product_ID to Reset?
32. Answer to Quiz - What Cause the Product_ID to Reset?
33. COUNT OVER for a Sequential Number
34. Quiz - What caused the COUNT OVER to Reset?
35. Answer to Quiz - What caused the COUNT OVER to Reset?
36. The MAX OVER Command
37. MAX OVER with PARTITION BY Reset
38. The MIN OVER Command
39. Quiz - Fill in the Blank
40. Answer - Fill in the Blank
41. The Row_Number Command
42. Quiz - How did the Row_Number Reset?
43. Quiz - How did the Row_Number Reset?
44. Standard Deviation Functions Using STDDEV / OVER
45. Standard Deviation Functions and STDDEV / OVER Syntax
46. STDDEV / OVER Example
47. VARIANCE / OVER Syntax
48. Variance Functions Using VARIANCE / OVER
49. Using VARIANCE with PARTITION BY Example
50. Using FIRST_VALUE and LAST_VALUE
51. Using FIRST_VALUE
52. Using LAST_VALUE
53. Using LAG and LEAD
55. Using LEAD With and Offset of 2
56. Using LAG
57. Using LAG with an Offset of 2
14. Temporary Tables
1. CREATING A Derived Table
2. The Three Components of a Derived Table
3. Naming the Derived Table
4. Aliasing the Column Names in the Derived Table
5. Visualize This Derived Table
6. Most Derived Tables Are Used To Join To Other Tables
7. Multiple Ways to Alias the Columns in a Derived Table
8. Our Join Example with a Different Column Aliasing Style
9. Column Aliasing Can Default for Normal Columns
10. CREATING a Derived Table using the WITH Command
11. Our Join Example With the WITH Syntax
12. WITH
13. A WITH Clause That Produces Two Tables
14. The Same Derived Query shown Three Different Ways
15. Quiz - Answer the Questions
16. Answer to Quiz - Answer the Questions
17. Clever Tricks on Aliasing Columns in a Derived Table
18. A Derived Table lives only for the lifetime of a single query
19. An Example of Two Derived Tables in a Single Query
20. Create Table Syntax
21. Basic Temporary Table Examples
22. More Advanced Temporary Table Examples
23. Advanced Temporary Table Examples
24. Performing a Deep Copy
25. Deep Copy Using the Original DDL
26. Deep Copy Using A CTAS
27. Deep Copy Using A Create Table LIKE
28. Deep Copy by Creating a Temp Table and Truncating Original
15. Sub-query Functions
1. An IN List is much like a Subquery
2. An IN List Never has Duplicates - Just like a Subquery
3. An IN List Ignores Duplicates
4. The Subquery
5. The Three Steps of How a Basic Subquery Works
6. These are Equivalent Queries
7. The Final Answer Set from the Subquery
8. Quiz- Answer the Difficult Question
9. Answer to Quiz- Answer the Difficult Question
10. Should you use a Subquery or a Join?
11. Quiz- Write the Subquery
12. Answer to Quiz- Write the Subquery
13. Quiz- Write the More Difficult Subquery
14. Answer to Quiz- Write the More Difficult Subquery
15. Quiz- Write the Subquery with an Aggregate
16. Answer to Quiz- Write the Subquery with an Aggregate
17. Quiz- Write the Correlated Subquery
18. Answer to Quiz- Write the Correlated Subquery
19. The Basics of a Correlated Subquery
20. The Top Query always runs first in a Correlated Subquery
21. Correlated Subquery Example vs. a Join with a Derived Table
22. Quiz- A Second Chance to Write a Correlated Subquery
23. Answer - A Second Chance to Write a Correlated Subquery
24. Quiz- A Third Chance to Write a Correlated Subquery
25. Answer - A Third Chance to Write a Correlated Subquery
26. Quiz- Last Chance to Write a Correlated Subquery
27. Answer - Last Chance to Write a Correlated Subquery
28. Quiz- Write the NOT Subquery
29. Answer to Quiz- Write the NOT Subquery
30. Quiz- Write the Subquery using a WHERE Clause
31. Answer - Write the Subquery using a WHERE Clause
32. Quiz- Write the Subquery with Two Parameters
33. Answer to Quiz- Write the Subquery with Two Parameters
34. How the Double Parameter Subquery Works
35. More on how the Double Parameter Subquery Works
36. Quiz - Write the Triple Subquery
37. Answer to Quiz - Write the Triple Subquery
38. Quiz - How many rows return on a NOT IN with a NULL?
39. Answer - How many rows return on a NOT IN with a NULL?
40. How to handle a NOT IN with Potential NULL Values
41. Using a Correlated Exists
42. How a Correlated Exists matches up
43. The Correlated NOT Exists
44. The Correlated NOT Exists Answer Set
45. Quiz - How many rows come back from this NOT Exists?
46. Answer - How many rows come back from this NOT Exists?
16. Substrings and Positioning Functions
1. The TRIM Command trims both Leading and Trailing Spaces
2. A Visual of the TRIM Command Using Concatenation
3. Trim and Trailing is Case Sensitive
4. How to TRIM Trailing Letters
5. The SUBSTRING Command
6. How SUBSTRING Works with NO ENDING POSITION
7. Using SUBSTRING to move backwards
8. How SUBSTRING Works with a Starting Position of -1
9. How SUBSTRING Works with an Ending Position of 0
10. The POSITION Command finds a Letters Position
11. Quiz - Find that SUBSTRING Starting Position
12. Answer to Quiz - Find that SUBSTRING Starting Position
13. Using the SUBSTRING to Find the Second Word On
14. Quiz - Why did only one Row Return?
15. Answer to Quiz - Why Did only one Row Return?
16. Concatenation
17. Concatenation and SUBSTRING
18. Four Concatenations Together
19. Troubleshooting Concatenation
20. Declaring a Cursor
17. Interrogating the Data
1. Quiz - What would the Answer be?
2. Answer to Quiz - What would the Answer be?
3. The NULLIF Command
4. Quiz - Fill in the Blank Values in the Answer Set
5. Answer to Quiz - Fill in the Blank Values in the Answer Set
6. Quiz - Fill in the Answers for the NULLIF Command
7. Quiz - Fill in the Answers for the NULLIF Command
8. Quiz - Fill in the Answers for the NULLIF Command
9. Quiz - Fill in the Answers for the NULLIF Command
10. The ISNULL, NVL and COALESCE Commands
11. The ISNULL, NVL and COALESCE Commands
12. The ISNULL, NVL and COALESCE more examples
13. The COALESCE Answer Set
14. The Coalesce Quiz
15. Answer - The Coalesce Quiz
16. The Basics of CAST (Convert and Store)
17. Some Great CAST (Convert and Store) Examples
18. The Basics of the CASE Statements
19. The Basics of the CASE Statement
20. Valued Case Vs. A Searched Case
21. Quiz - Valued Case Statement
22. Answer - Valued Case Statement
23. Quiz - Searched CASE Statement
24. Answer - Searched CASE Statement
25. Quiz - When NO ELSE is present in CASE Statement
26. Answer - When NO ELSE is present in CASE Statement
27. When an ELSE is present in CASE Statement
28. Answer - When an ELSE is present in CASE Statement
29. When an Alias is NOT used in a CASE Statement
30. Answer - When an Alias is NOT used in a CASE Statement
31. Combining Searched Case and Valued Case
32. Nested Case
33. Put a CASE in the ORDER BY
18. View Functions
1. Creating a Simple View to Restrict Sensitive Columns
2. Creating a Simple View to Restrict Rows
3. Creating a View to Join Tables Together
4. You Select From a View
5. Basic Rules for Views
6. An ORDER BY Example Inside of a View
7. An ORDER BY Inside of a View that is Queried Differently
8. Creating a View with Ordered Analytics
9. Creating a View with the TOP Command
10. Creating a View with the LIMIT Command
11. Altering a Table
12. Altering a Table after a View has been created
13. A View that Errors after an ALTER
14. Troubleshooting a View
15. Updating Data in a Table through a View
19. Set Operators Functions
1. Rules of Set Operators
2. INTERSECT Explained Logically
3. INTERSECT Explained Logically
4. UNION Explained Logically
5. UNION Explained Logically
6. UNION ALL Explained Logically
7. UNION Explained Logically
8. EXCEPT Explained Logically
9. EXCEPT Explained Logically
10. Minus Explained Logically
11. Minus Explained Logically
12. Testing Your Knowledge
14. Testing Your Knowledge
16. An Equal Amount of Columns in both SELECT List
17. Columns in the SELECT list should be from the same Domain
18. The Top Query handles all Aliases
19. The Bottom Query does the ORDER BY (a Number)
20. Great Trick: Place your Set Operator in a Derived Table
21. UNION Vs UNION ALL
22. A Great Example of how EXCEPT works
20. Statistical Aggregate Functions
1. The Stats Table
2. STDDEV
3. Casting STDDEV_SAMP and SQRT (VAR_SAMP)
4. The STDDEV_POP Function
5. A STDDEV_POP Example
6. The STDDEV_SAMP Function
7. A STDDEV_SAMP Example
8. The VAR_POP Function
9. A VAR_POP Example
10. The VAR_SAMP Function
11. A VAR_SAMP Function
21. Nexus
1. Nexus is Now Available on the Microsoft Azure Cloud
2. Nexus Queries Every Major System
3. Setup of Nexus is as easy as pie
4. Setup of Nexus is a Easy as 1, 2, 3
5. Nexus Data Visualization
6. Nexus Data Visualization
7. Nexus Data Visualization Shows What Tables Can Be Joined
8. Nexus is doing a Five-Table Join
9. Nexus Generates the SQL Automatically
10. Nexus Delivers the Report
11. Cross-System Joins from Teradata, Oracle and SQL Server
12. The Tab of the Super Join Builder
13. The 9 Tabs of the Super Join Builder - Objects Tab 1
14. Selecting Columns in the Objects Tab
15. The 9 Tabs of the Super Join Builder - Columns Tab 2
16. Removing Columns from the Report in the Columns Tab
17. The 9 Tabs of the Super Join Builder - Sorting Tab 3
18. The 9 Tabs of the Super Join Builder - Joins Tab 4
19. The 9 Tabs of the Super Join Builder - Where Tab 5
20. Using the WHERE Tab For Additional WHERE or AND
21. The 9 Tabs of the Super Join Builder - SQL Tab 6
22. The 9 Tabs of the Super Join Builder - Answer Set Tab 7
23. The 9 Tabs of the Super Join Builder - Analytics Tab 9
24. Analytics Tab
25. Analytics Tab - OLAP Example
26. Analytics Tab - OLAP Example of SQL Generated
27. Analytics Tab - Grouping Sets Example
28. Analytics Tab - Grouping Sets Answer Set
29. Nexus Data Movement
30. Moving a Single Table to a Different System
31. The Single Table Data Movement Screen
32. Moving an Entire Database to a Different System
33. The Database Mover Screen
34. The Database Mover Options Tab
35. Converting DDL Table Structures
36. Converting DDL Table Structures
37. Converting DDL Table Structures
38. Hound Dog Compression
39. Hound Dog Compression on Teradata
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

81,676

Students who have taken Live Online Training

15,640

Organizations who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.39

Students rated our trainers 9.39 out of 10 based on 4,781 reviews

The training was great, and I feel very prepared to tackle new SharePoint challenges.

Paul Prince, Public Affairs Office
Fort McPherson GA

Great learning experience for me! Excellent instructor! I'm excited about applying this technology at my company!

Casey Murrell, TTI Inc.
Fort Worth TX

This is an awesome way to learn. I enjoyed the class and my instructor was so good.

Angela Gaddy, Honeywell International
Lawrenceville GA

Great intro to WEBI application. No prerequisites needed to jump into this tool.

Eric Lum, Food and Drug Administration

or call 1-877-932-8228