title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Actian Matrix Architecture and SQL
Duration
3 Days
Course
ARAM-100
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Actian Matrix Architecture and SQL course is designed to provide students with a deeper knowledge and understanding of the Actian Matrix Architecture and SQL and how to write it. In this course, 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.Objectives
Upon completion of the Actian Matrix Architecture and SQL course, students will be able to:
- Identify and apply basic SQL functions
- Understand the WHERE clause
- Contrast Distinct vs. Group by
- Recognize and construct functions, such as:
- Aggregation, Join, Date, OLAP, View, Sub-query and Statistical Aggregate
- Recognize and produce Temporary Tables
- Define and distinguish Strings
- Interrogate the Data
- Set Operators
- Demonstrate knowledge of the Data Manipulation Language (DML)
Audience
- Anyone who has a desire to learn the Actian Matrix Architecture and SQL from beginners to an advanced audience.
Prerequisites
- None
Topics
- What is Parallel Processing?
- The Basics of a Single Computer
- Parallel Processing Of Data
- Why Columnar?
- Row Based Blocks vs. Columnar Based Blocks
- Visualize the Data – Rows vs. Columns
- The Architecture of Actian Matrix
- Matrix has Linear Scalability
- Distribution Styles
- Distribution Keys
- Big Table / Small Table Joins
- Fact and Dimension Table Distribution Key Designs
- Improving Performance by Defining a Sort Key
- The ANALYZE Command Collects Statistics
- What is a Vacuum?
- Database Limits
- Creating a Database
- Creating/Dropping a User
- Inserting Into, Renaming, Adding and Dropping a Column to a Table
- Converting Table Structures to Actian Matrix
- Best Practices for Designing Tables
- Choose the Best Sort Key
- Choose a Great Distribution Key
- Big Table / Small Table Joins
- Define Primary Key and Foreign Key Constraints
- Use Date/Time Data Types for Date Columns
- Specify Redundant Predicates on the Sort Column
- Setting the statement_timeout to Abort Long Queries
- Actian Matrix System Tables
- Trouble Shooting Catalog Table pg_table_def
- Seeing the System Tables in your Nexus Tree
- Catalog Table pg_table_def
- Checking Tables for Skew (Poor Distribution)
- Checking All Statements That Used the Analyze Command
- Checking for Details about the Last Copy Operation
- Checking When a Table Has Last Been Analyzed
- Checking For Column Information on a Table
- System tables for troubleshooting data loads
- Determining Whether a Query is writing to Disk
- Compression Types
- Byte Dictionary Compression
- Delta, Deflate, Mostly, and Runlength Encoding
- Text255 and Text32k Encodings
- Analyze Compression using xpx ‘complyze’
- Analyze Results from xpx ‘complyze’
- Copy
- Create Table Syntax
- Temporary Table Examples
- Table Limits and CTAS
- Performing a Deep Copy
- Deep Copy
- CREATING A Derived Table
- The Three Components of a Derived Table
- CREATING a Derived Table using the WITH Command
- Our Join Example With the WITH Syntax
- WITH Statement That Uses a SELECT *
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Connecting To Matrix via Nexus
- Three Ways to Run an EXPLAIN
- EXPLAIN – Steps, Segments and Streams
- EXPLAIN Terms
- EXPLAIN Examples
- EXPLAIN Showing DS_DIST_NONE Visually
- EXPLAIN With a Warning
- EXPLAIN for Ordered Analytics Such as CSUM
- EXPLAIN for Scalar Aggregate Functions
- EXPLAIN for HashAggregate Functions
- EXPLAIN Using Limit, Merge and Sort
- EXPLAIN Using a WHERE Clause Filter
- EXPLAIN Using the Keyword Distinct
- EXPLAIN for Subqueries
- Finding the Current Schema on the Leader Node
- Getting Things Setup in Your Search Path
- Five Details You Need To Know About the Search_Path
- Introduction
- SELECT * (All Columns) and Specific Columns in a Table
- Sort the Data with the ORDER BY Keyword
- NULL Values sort First in Ascending Mode (Default)
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A CASE Statement to Sort Logically
- How to ALIAS a Column Name
- 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
- Using Limit
- The WHERE Clause limits Returning Rows
- NULL means UNKNOWN DATA so Equal (=) won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Using Greater Than or Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting Or, and Character Data
- Using Different Columns in an AND Statement
- What is the Order of Precedence?
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- Using a NOT IN List
- BETWEEN and NOT BETWEEN are Inclusive
- LIKE command Underscore is Wildcard for one Character
- Introducing the TRIM Command
- An Example of Data with Left and Right Justification
- A Visual of CHARACTER Data vs. VARCHAR Data
- Use the TRIM command to remove spaces on CHAR Data
- Like and the Default Escape Character
- The Distinct Command
- Distinct vs. GROUP BY
- TOP Command
- TOP Command is brilliant when ORDER BY is used!
- What is the Difference between TOP and LIMIT?
- The 3 Rules of Aggregation
- There are Five Aggregates
- GROUP BY when Aggregates and Normal Columns Mix
- Limiting Rows and Improving Performance with WHERE
- Keyword HAVING tests Aggregates after they are Totaled
- Keyword HAVING is like an Extra WHERE Clause for Totals
- A Two-Table Join Using Traditional Syntax
- A two-table join using Non-ANSI Syntax with Table Alias
- A two-table join using ANSI Syntax
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND Clause and WHERE Clause
- OUTER JOIN with Additional WHERE Clause and AND Clause
- The DREADED Product Join
- The Horrifying Cartesian product Join
- The ANSI Cartesian Join will ERROR
- The CROSS JOIN
- The CROSS JOIN Answer Set
- The Self Join
- How would you join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- The 5-Table Join – Logical Insurance Model
- Current_Date
- TIMEOFDAY ()
- SYSDATE Returns a Timestamp with Microseconds
- GETDATE Returns a Timestamp without Microseconds
- The ADD_MONTHS Command
- Dateadd Function and Add_Months Function are Different
- The EXTRACT Command
- The Datediff command
- The to_char command
- Conversion Functions
- Formatting a Date
- A Summary of Math Operations on Dates
- Date Related Functions
- Implied Extract of Day, Month and Year
- DATE_PART Function
- DATE_TRUNC Function
- MONTHS_BETWEEN Function
- ANSI TIME
- ANSI TIMESTAMP
- Matrix TIMESTAMP Function
- Matrix TO_TIMESTAMP Function
- Matrix NOW () Function
- Matrix TIMEOFDAY Function
- Matrix AGE Function
- Time Zones
- Intervals for Date, Time and Timestamp
- Interval Arithmetic Results
- The OVERLAPS Command
- CSUM
- CSUM – The Sort Explained
- CSUM – Rows Unbounded Preceding Explained
- CSUM – Making Sense of the Data
- CSUM – Making Even More Sense of the Data
- CSUM – The Major and Minor Sort Key(s)
- Reset with a PARTITION BY Statement
- ANSI Moving Window is Current Row and Preceding n Rows
- How ANSI Moving SUM Handles the Sort
- Moving SUM every 3-rows Vs a Continuous Average
- Partition by Resets an ANSI OLAP
- Moving Average
- The Moving Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Moving Average every 3-rows Vs a Continuous Average
- Partition by Resets an ANSI OLAP
- RANK Order
- RANK () OVER and PARTITION BY
- RANK () OVER and LIMIT
- PERCENT_RANK () OVER
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- The MIN OVER Command
- The Row_Number Command
- Standard Deviation Functions Using STDDEV / OVER and Syntax
- STDDEV / OVER Example
- VARIANCE / OVER Syntax
- Variance Functions Using VARIANCE / OVER
- Using VARIANCE with PARTITION BY Example
- Using FIRST_VALUE and LAST_VALUE
- Using LAG and LEAD
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Visualize This 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
- WITH
- The Same Derived Query shown Three Different Ways
- 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
- Create Table Syntax
- Temporary Table Examples
- Performing a Deep Copy
- An IN List
- The Subquery: Three Steps of How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Should you use a Subquery or a Join?
- Quiz- Write the Subquery
- Answer to Quiz- Write the 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
- How the Double Parameter Subquery Works
- More on how the Double Parameter Subquery Works
- How to handle a NOT IN with Potential NULL Values
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- The Correlated NOT Exists Answer Set
- The TRIM Command trims both Leading and Trailing Spaces
- A Visual of the TRIM Command Using Concatenation
- Trim and Trailing is Case Sensitive
- How to TRIM Trailing Letters
- 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
- The POSITION Command finds a Letters Position
- Using the SUBSTRING to Find the Second Word On
- Concatenation and SUBSTRING
- Four Concatenations Together
- Troubleshooting Concatenation
- Declaring a Cursor
- The NULLIF Command
- The ISNULL, NVL and COALESCE Commands
- The Basics of CAST (Convert and Store)
- Some Great CAST (Convert and Store) Examples
- The Basics of the CASE Statements
- Valued Case Vs. A Searched Case
- When an ELSE is present in CASE Statement
- When an Alias is NOT used in a CASE Statement
- Combining Searched Case and Valued Case
- Nested Case
- Put a CASE in the ORDER BY
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Creating a View to Join Tables Together
- You Select From a View
- Basic Rules for Views
- An ORDER BY Example Inside of a View
- An ORDER BY Inside of a View that is Queried Differently
- Creating a View with Ordered Analytics
- Creating a View with the TOP Command
- Creating a View with the LIMIT Command
- Altering a Table
- A View that Errors after an ALTER
- Troubleshooting a View
- Updating Data in a Table through a View
- Rules of Set Operators
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- EXCEPT Explained Logically
- Minus Explained Logically
- 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
- A Great Example of how EXCEPT works
- The Stats Table
- STDDEV
- Casting STDDEV_SAMP and SQRT (VAR_SAMP)
- The STDDEV_POP Function and Example
- The STDDEV_SAMP Function and Example
- The VAR_POP Function and Example
- The VAR_SAMP Function
- A VAR_SAMP Function
- Nexus is Now Available on the Microsoft Azure Cloud
- Nexus Queries Every Major System
- Setup of Nexus
- Nexus Data Visualization
- Nexus is doing a Five-Table Join
- Nexus Generates the SQL Automatically
- Nexus Delivers the Report
- Cross-System Joins from Teradata, Oracle and SQL Server
- The Tab of the Super Join Builder
- The 9 Tabs of the Super Join Builder
- Selecting Columns in the Objects Tab
- Removing Columns from the Report in the Columns Tab
- Analytics Tab
- Nexus Data Movement
- Moving a Single Table to a Different System
- The Single Table Data Movement Screen
- Moving an Entire Database to a Different System
- The Database Mover Screen
- The Database Mover Options Tab
- Converting DDL Table Structures
- Hound Dog Compression
Related Courses
-
Querying Data with Microsoft Transact-SQL
MOC-DP-080T00- Duration: 2 Days
- Delivery Format: Classroom Training, Online Training
- Price: 1,190.00 USD
-
Getting Started with Cosmos DB NoSQL Development
MOC-DP-3015- Duration: 1
- Delivery Format: Classroom Training, Online Training
- Price: 595.00 USD
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
??spvc-wbt-warning??
Exam Terms & Conditions
??exam-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Nothing yet
Purchase Information
??elearning-coursenumber?? ??coursename??
View Cart
title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.