Contact Us info@learnquest.com

??WelcomeName??
??WelcomeName??
photo

Thank you for your interest in LearnQuest.

Your request is being processed and LearnQuest or a LearnQuest-Authorized Training Provider will be in touch with you shortly.

photo

Thank you for your interest in Private Training.

We look forward to helping you develop the perfect training solution to help you meet your company's goals.

For immediate assistance, speak with one of our representatives using the chat module below. Otherwise, LearnQuest or a LearnQuest-Authorized Training Provider will be in touch with you shortly.

photo

Thank you for your interest in LearnQuest!

Now, you will be able to stay up-to-date on our latest course offerings, promotions, and training discounts. Watch your inbox for upcoming special offers.

title

Date: xxx

Location: xxx

Time: xxx

Price: xxx

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

Course content updated by LearnQuest
Price
2,295 USD
3 Days
ARAM-100
Classroom Training, Online Training
Prices reflect a 22.5% discount for IBM employees.
Prices shown are the special AWS Partner Prices.
Prices reflect the Capgemini employee discount.
Prices reflect the UPS employee discount.
Prices reflect the ??democompanyname?? employee discount.
GSA Private/Onsite Price: ??gsa-private-price??
For GSA pricing, please go to GSA Advantage.
This course is eligible for the IBM Full Access Training Pass
Enroll today and save 10% on this course. Use promo code CLOUD10 when registering.
Working on a laptop
Gain access to IBM’s library of digital, on-demand courses for one low annual subscription fee
IBM Full Access Training Pass
$500 off any IBM Full Access Training Pass Option
See Offer
Get a 30% Discount on IBM Self-Paced Courses
See Offer

Class Schedule

Delivery Formats

Sort results

Filter Classes

Guaranteed to Run

Modality

Location

Language

Date

    Sorry, there are no public classes currently scheduled in your country.

    Please complete this form, and a Training Advisor will be in touch with you shortly to address your training needs.

View Global Schedule

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 Columnar?
    • 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
  • Best Practices for Table Design
    • 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
  • Systems Tables
    • 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
    • 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
  • Temporary Tables
    • 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
  • Explain
    • 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
  • Basic SQL Functions
    • 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
  • The WHERE Clause
    • 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
  • Distinct Vs Group By AND TOP
    • 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?
  • Aggregation
    • 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
  • Join Functions
    • 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
  • Date Functions
    • 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
  • OLAP Functions
    • 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
  • Temporary Tables
    • 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
  • Sub-query Functions
    • 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
  • Substrings and Positioning Functions
    • 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
  • Interrogating the Data
    • 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
  • View Functions
    • 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
  • Set Operators Functions
    • 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
  • Statistical Aggregate Functions
    • 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
    • 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
    • Microsoft SQL Server T-SQL

      MSSQ-200
      • Duration: 3 Days
      • Delivery Format: Classroom Training, Online Training
      • Price: 2,295.00 USD
    • SQL Server T-SQL

      MSSQ-215
      • Duration: 3 Days
      • Delivery Format: Classroom Training, Online Training
      • Price: 2,295.00 USD
    2020 Top 20 Training Industry Company - IT Training

    Need Help?

    Call us toll free at 877-206-0106 or e-mail us at info@learnquest.com

    Personalized Solutions

    Need a personalized solution for your training? Contact us, and one of our advisors will help you find the best solution to your training needs.

    Contact us

    Need Help?

    Do you have a question about the courses, instruction, or materials covered? Do you need help finding which course is best for you?

    Talk to us

    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??
    ??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.
    Nothing yet
    here's the message from the cart

    To view the cart, you can click "View Cart" on the right side of the heading on each page
    Add to cart clicker.

    Purchase Information

    ??elearning-coursenumber?? ??coursename??
    View Cart

    Need more Information?

    Speak with our training specialists to continue your learning journey.

     

    Delivery Formats

    Close

    By submitting this form, I agree to LearnQuest's Terms and Conditions

    heres the new schedule
    This website uses third-party profiling cookies to provide services in line with the preferences you reveal while browsing the Website. By continuing to browse this Website, you consent to the use of these cookies. If you wish to object such processing, please read the instructions described in our Privacy Policy.
    Your use of this LearnQuest site affirms your consent to our use of session and persistent cookies to track how you use our website.