Contact Us

« Important Announcement » Contact Us 877-206-0106 | USA Flag

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.


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.


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.


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.

Azure SQL Data Warehouse Architecture and SQL

2,295 USD
3 Days
Classroom Training, Online Training

AWS Training Pass

Take advantage of flexible training options with the AWS Training Pass and get Authorized AWS Training for a full year.

Learn More

Prices reflect a 22.5% discount for IBM employees (wherever applicable).
Prices reflect a 24% discount for Kyndryl employees (wherever applicable).
Prices reflect the Accenture employee discount.
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.

Class Schedule

Delivery Formats

Sort results

Filter Classes

Guaranteed to Run





    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


This Azure SQL Data Warehouse Architecture and SQL course is designed to provide students with a deeper knowledge and understanding of the Azure SQL Data Warehouse Architecture and SQL and how to write it. Students will learn the Azure SQL Data Warehouse Architecture and SQL starting at the most basic level and going to the most advanced level with many examples.


Upon completion of the Azure SQL Data Warehouse 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 apply 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)


  • Anyone who has a desire to learn the Azure SQL Data Warehouse Architecture and SQL from beginners to an advanced audience.


    • None


  • Introduction to the Azure SQL Data Warehouse
    • Introduction to the Family of SQL Server Products
    • Microsoft Azure SQL Data Warehouse
    • Symmetric Multi-Processing (SMP)
    • What is Parallel Processing?
    • The Basics of a Single Computer
    • Data in Memory is fast as Lightning
    • Parallel Processing of Data
    • The Architecture of the Azure SQL Data Warehouse
    • The MPP Engine is the Optimizer
    • The Azure SQL Data Warehouse System
    • The Control Node, Data Rack, Landing Zone, Backup Node
    • Software as a Service (SaaS) and the Elastic Database
    • Azure Data Lake
    • Azure Disaster Recovery
    • Security and Compliance
    • How to Get an EXPLAIN Plan
  • The Azure SQL Data Warehouse Table Structures
    • The 5 Concepts of Azure SQL Data Warehouse Tables
    • Tables:
      • Are Either Distributed by Hash or Replicated
      • Rows are Either Sorted or Unsorted
      • are Stored in Either Row or Columnar Format
      • can be Partitioned
      • are Permanent, Temporary or External
    • Creating a Table with a Distribution Key
    • Creating a Table that is replicated
    • Distributed by Hash vs. Replication
    • The Concept is all about the Joins
    • Creation of a Hash Distributed Table with a Clustered Index
    • A Clustered Index Sorts the Data Stored on Disk
    • Each Node Has 8 Distributions
    • How Hashed Tables are Stored among a Single Node
    • Hashed Tables Will Be Distributed Among All Distributions
    • Creation of a Replicated Table
    • Distributed by Replication
    • Tables are stored as Row-based or Column-based
    • Creation of a Columnar Table that is hashed
    • Comparing Normal Table vs. Columnar Tables
    • Segments on Distributions are aligned to rebuild a Row
    • Why Columnar?
    • Columnar Tables Store Each Column in Separate Pages
    • Visualize the Data – Rows vs. Columns
    • Creation of a Columnar Table that is replicated
    • Creating a Partitioned Table per Month
    • A Visual of One Year of Data with Range per Month
    • Another Create Example of a Partitioned Table
    • Creating a Partitioned Table per Month That is a Columnstore
    • Visual of Row Partitioning and Columnar Storage
    • CREATE TABLE AS (CTAS) Example
    • Creating a Temporary Table
    • Facts about Tables
  • Hashing and Data Distribution
    • Distribution Keys Hashed on Unique Values Spread Evenly
    • Distribution Keys with Non-Unique Values Spread Unevenly
    • Best Practices for Choosing a Distribution Key
    • The Hash Map determines which Distribution owns the Row
    • The Hash Map determines which Node will own the Row
    • A Review of the Hashing Process
    • Non-Unique Distribution Keys have Skewed Data
  • The Technical Details
    • Every Node has the Exact Same Tables
    • Hashed Tables are spread across All Distributions
    • The Table Header and the Data Rows are Stored Separately
    • A Distribution Stores the Rows of a Table inside a Data Block
    • To Read a Data Block a Node Moves the Block into Memory
    • A Full Table Scan Means All Nodes Must Read All Rows
    • As Row-Based Tables Get Bigger, the Page Splits
    • Data Pages are Processed One at a Time per Unit
    • Heap Page
    • Extents
    • Clustered Index Page
    • The Row Offset Array is the Guidance System for Every Row
    • The Row Offset Array Provides Two Search Options
    • The Row Offset Array Helps with Inserts
    • B-Trees
    • The Building of a B-Tree for a Clustered Index
    • When Do I Create a Clustered Index?
    • When Do I Create a Non Clustered Index?
    • B-Tree for Non Clustered Index on a Clustered Table
    • Adding a Non Clustered Index to A Heap
    • B-Tree for Non Clustered Index on a Heap Table
    • Max Levels on the Azure SQL Data Warehouse
    • Azure SQL Data Warehouse Data Types
    • Character Data Types for SQL Server
    • Numeric Data Types for SQL Server
    • Date and Time Data Types for SQL Server
    • Additional Data Types for SQL Server
  • CREATE Statistics
    • CREATE Statistics Syntax
    • CREATE Statistics on a Percentage of a Table
    • CREATE Statistics on a Sample by Using the System Default
    • CREATE Statistics on a Multi-Column Join Key
    • What to Column(s) to CREATE Statistics On
    • CREATE Statistics Using a WHERE Clause
    • Updating, Dropping, and Showing Statistics on a Table
  • The Basics of SQL
    • Introduction
    • Naming of Objects
    • Setting Your Default Database
    • SELECT * (All Columns) in a Table
    • SELECT Specific Columns in a Table
    • Place your Commas in front for better Debugging Capabilities
    • Sort the Data with the ORDER BY Keyword
    • ORDER BY Defaults to Ascending
    • Use the Name or the Number in your ORDER BY Statement
    • Two Examples of ORDER BY using Different Techniques
    • Changing the ORDER BY to Descending Order
    • NULL Values sort First in Ascending Mode (Default)
    • NULL Values sort Last in Descending Mode (DESC)
    • Major Sort vs. Minor Sorts
    • Multiple Sort Keys using Names vs. Numbers
    • Sorts are Alphabetical, NOT Logical
    • Using A CASE Statement to Sort Logically
    • An Order by That Uses an Expression
    • Aliasing a Column Name with Spaces or Reserved Words
    • 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
    • sp_help at the Database Level
    • sp_help at the Object Level
    • Getting System Information
    • Getting Additional System Information
  • The Where Clause
    • The WHERE Clause limits Returning Rows
    • Double Quoted Aliases are for Reserved Words and Spaces
    • Using a Column ALIAS in a WHERE Clause
    • Using a Column ALIAS in an ORDER BY Clause
    • In What Order Does SQL Server Process A Query?
    • Character Data needs Single Quotes in the WHERE Clause (Numbers Don’t)
    • Declaring a Variable
    • Comparisons against a Null Value
    • 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
    • Troubleshooting Character Data
    • Using Different Columns in an AND Statement
    • LIKE command Underscore is Wildcard for one Character
    • LIKE command using a Range of Values
    • LIKE command using a NOT Range of Values
    • LIKE Command Works Differently on Char Vs Varchar
    • Troubleshooting LIKE Command on Character Data
    • Introducing the RTRIM Command
    • Numbers are Right Justified and Character Data is Left
    • An Example of Data with Left and Right Justification
    • A Visual of CHARACTER Data vs. VARCHAR Data
    • RTRIM command Removes Trailing spaces on CHAR Data
    • Using Like with an AND Clause to Find Multiple Letters
    • Using Like with an OR Clause to Find Either Letters
    • Declaring a Variable and using it with the LIKE Command
    • Escape Character in the LIKE Command changes Wildcards
    • Escape Characters Turn off Wildcards in the LIKE Command
  • Distinct, Group By and TOP
    • The Distinct Command
    • Distinct vs. GROUP BY
    • TOP Command
    • TOP Command is brilliant when ORDER BY is used!
    • TOP Command with Ties
    • TOP Command Using a Variable
  • Aggregation
    • The 3 Rules of Aggregation
    • There are Five Aggregates
    • Troubleshooting Aggregates
    • GROUP BY when Aggregates and Normal Columns Mix
    • Count_Big
    • Limiting Rows and Improving Performance with WHERE
    • WHERE Clause in Aggregation limits unneeded Calculations
    • Keyword HAVING tests Aggregates after they are totaled
    • Group by Grouping Sets and Rollup
    • Answer Set for Group by Rollup Query
    • Creating a Cube
    • Getting the Average Values per Column
    • Average Values per Column for all Columns in a Table
  • Join Functions
    • Redistribution
    • Big Table Small Table Join Strategy
    • Duplication of the Smaller Table across All-Distributions
    • If the Join Condition is the Distribution Key no Movement
    • Matching Rows That Are On The Same Node Naturally
    • What if the Join Condition Columns are Not Primary Indexes
    • Strategy 1 of 4 – The Merge Join
    • Strategy 2 of 4 – The Hash Join
    • Strategy 4 of 4 – The Product Join
    • 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
    • Which Tables are the Left and which Tables are Right?
    • 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
    • Evaluation Order for Outer Queries
    • The DREADED Product Join
    • The Horrifying Cartesian product Join
    • The ANSI Cartesian Join will ERROR
    • The CROSS JOIN
    • The Self Join
    • The Self Join with ANSI Syntax
    • 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 Function
    • Current_Timestamp
    • Getdate
    • Date and Time Keywords
    • SYSDATETIMEOFFSET Provides the Timezone Offset
    • Using both CAST and CONVERT in Literal Values
    • The DATEADD Function
    • The DATEDIFF Function
    • A Real World Example for DateAdd Using the Order Table
    • DATEPART Function
    • YEAR, MONTH, and DAY Functions
    • DATENAME Function
    • ISDATE Function
  • Temporary Tables
    • 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
    • The Same Derived Query shown Three Different Ways
    • MULTIPLE Derived Tables using the WITH Command
    • Column Alias Can Default For Normal Columns
    • Most Derived Tables Are Used To Join To Other Tables
    • The Three Components of a Derived Table
    • Visualize This Derived Table
    • Our Join Example With the WITH Syntax
    • Clever Tricks on Aliasing Columns in a Derived Table
    • A Derived Table lives only for the lifetime of a single query
    • RECURSIVE Derived Table Hierarchy, Query, and Definition
    • WITH RECURSIVE Derived Table Seeding and Looping
    • RECURSIVE Derived Table Looping in Slow Motion
    • RECURSIVE Derived Table Ends the Looping
    • RECURSIVE Derived Table Definition
    • What is TEMPDB?
    • Creating a Temporary Table
    • The Three Steps to Use a Private Temporary Table
    • Creating a Temporary Table with a Clustered Index
    • Creating a Columnstore Temporary Table from a CTAS
  • Sub-query Functions
    • An IN List is much 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
    • Should you use a Subquery or a Join?
    • 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 to handle a NOT IN with Potential NULL Values
    • Using a Correlated Exists
    • How a Correlated Exists matches up
    • The Correlated NOT Exists
  • Window Functions OLAP
    • The Row_Number Command
    • Using a Derived Table and Row_Number
    • Ordered Analytics OVER
    • RANK Ascending and DESC Order
    • Cumulative Sum
    • The ANSI CSUM – Getting a Sequential Number
    • Troubleshooting the ANSI OLAP on a GROUP BY
    • Reset with a PARTITION BY Statement
    • PARTITION BY only Resets a Single OLAP not ALL of them
    • Sorting in DESC Order
    • Moving Average
    • Casting a Moving Average
    • Partition by Resets an ANSI OLAP
    • COUNT OVER for a Sequential Number
    • The MAX OVER Command
    • The MIN OVER Command
    • Ntile
    • LEAD
    • LAG
    • SUM (SUM (n))
  • Working with Strings
    • The ASCII Function
    • The CHAR Function
    • The UNICODE Function
    • The NCHAR Function
    • The LEN Function
    • The DATALENGTH Function
    • Concatenation
    • The RTRIM and LTRIM Command trims Spaces
    • The SUBSTRING Command
    • Concatenation and SUBSTRING
    • SUBSTRING and Different Aliasing
    • The LEFT and RIGHT Functions
    • Four Concatenations Together
    • The DATALENGTH Function and RTRIM
    • A Visual of the TRIM Command Using Concatenation
    • CHARINDEX Function Finds a Letter(s) Position in a String
    • The CHARINDEX Command is brilliant with SUBSTRING
    • The CHARINDEX Command Using a Literal
    • PATINDEX Function
    • SOUNDEX Function to Find a Sound
    • DIFFERENCE Function to Quantile a Sound
    • The REPLACE Function
    • LEN and REPLACE Functions for Number of Occurrences
    • REPLICATE Function
    • STUFF Function
    • UPPER and lower Functions
  • Interrogating the Data
    • The NULLIF Command
    • The COALESCE Command – Fill In the Answers
    • COALESCE is Equivalent to This CASE Statement
    • The Basics of CAST (Convert and Store)
    • Some Great CAST (Convert and Store) Examples
    • A Rounding Example
    • Using an ELSE in the Case Statement
    • Using an ELSE as a Safety Net
    • Rules For a Valued Case Statement
    • Rules for a Searched Case Statement
    • Valued Case Vs. A Searched Case
    • Combining Searched Case and Valued Case
    • A Trick for getting a Horizontal Case
    • Nested Case
    • Put a CASE in the ORDER BY
  • Table Create and Data Types
    • Creating a Database
    • Creating a Table that is a Heap
    • Heap Page
    • Extents
    • Creating a Table That Has a Clustered Index
    • Clustered Index Page
    • When Do I Create a Clustered Index?
    • B-Trees
    • The Building of a B-Tree for a Clustered Index
    • The Row Offset Array is the Guidance System for Every Row
    • The Row Offset Array Provides Two Search Options
    • The Row Offset Array Helps with Inserts
    • What is a Uniquefier?
    • Adding an Index
    • When Do I Create a Non Clustered Index?
    • B-Tree for Non Clustered Index on a Clustered Table
    • Adding a Non Clustered Index to A Heap
    • B-Tree for Non Clustered Index on a Heap Table
    • Default Values
  • View Functions
    • The Fundamentals of Views
    • Creating a Simple View to Restrict Sensitive Columns and Rows
    • Basic Rules for Views
    • Two Exceptions to the ORDER BY Rule inside a View
    • Views sometimes CREATED for Row Security
    • Creating a View to Join Tables Together
    • You Select From a View
    • Another Way to Alias Columns in a View CREATE
    • The Standard Way Most Aliasing is done
    • What Happens When Both Aliasing Options Are Present
    • Resolving Aliasing Problems in a View CREATE
    • Answer to Resolving Aliasing Problems in a View CREATE
    • Aggregates on View Aggregates
    • Altering a Table
    • A View that Errors after an ALTER
    • Troubleshooting a View
    • Loading Data through a View
  • Data Manipulation Language (DML)
    • INSERT Syntax #1 and #2
    • INSERT/SELECT Command
    • INSERT/SELECT Example using All Columns (*)
    • INSERT/SELECT Example with Less Columns
    • The UPDATE Command Basic Syntax
    • Subquery UPDATE Command Syntax
    • Join UPDATE Command Syntax
    • The DELETE Command Basic Syntax
    • To DELETE or to TRUNCATE
    • Subquery and Join DELETE Command Syntax
  • Set Operators Functions
    • Rules of Set Operators
    • INTERSECT Explained Logically
    • UNION Explained Logically
    • UNION ALL Explained Logically
    • EXCEPT Explained Logically
    • EXCEPT Explained Logically in Reverse Order
    • 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
    • Great Trick: Place your Set Operator in a Derived Table
    • Using UNION ALL and Literals
    • A Great Example of how EXCEPT works
    • USING Multiple SET Operators in a Single Request
    • Changing the Order of Precedence with Parentheses
    • Building Grouping Sets Using UNION
    • Three Grouping Sets Using a UNION
  • Stored Procedure Functions
    • Creating a Stored Procedure
    • 3 Ways Executing a Stored Procedure
    • Creating a Stored Procedure with a CASE Statement
    • Our Answer Set
    • Dropping a Stored Procedure
    • Passing an Input Parameter to a Stored Procedure
    • Executing With Positional Parameter vs. Named Parameters
    • Passing an Output Parameter to a Stored Procedure
    • Changing a Stored Procedure with an ALTER
    • Answer Set for the Altered Stored Procedure
    • Using a Stored Procedure to Delete a Row
    • A Different Method to Delete a Row
    • Deleting a Row Using an Input Parameter
    • Using Loops in Stored Procedures
    • Stored Procedure Workshop
    • Looping with a WHILE Statement
  • Statistical Aggregate Functions
    • The Stats Table
    • The VAR and VARP Functions
    • The STDEV and STDEVP Functions
  • Systems Views
    • System Views
    • sys.all_columns
    • sys.all_objects
    • sys.all_sql_modules
    • sys.all_views
    • sys.columns
    • sys.data_spaces
    • sys.database_files
    • sys.database_principals
    • sys.database_role_members
    • sys.databases
    • sys.filegroups
    • sys.identity_columns
    • sys.objects
    • sys.partition_range_values
    • sys.schemas
    • sys.server_role_members
    • sys.sql_logins
  • Nexus
    • Nexus is Now Available on the Microsoft Azure Cloud
    • Nexus Queries Every Major System
    • Setup of Nexus is as easy as pie
    • Setup of Nexus is a Easy as 1, 2, 3
    • Nexus Data Visualization
    • Nexus Data Visualization Shows What Tables Can Be Joined
    • 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
    • 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 and Options Tab
    • Converting DDL Table Structures
    • Compare and Synchronize
    • Compare Two Different Databases from Different Systems
    • Comparisons Down to the Column Level
    • The Results Tab
    • View Differences
    • Synchronizing Differences in the Results Tab
    • Hound Dog Compression
    • Hound Dog Compression on Teradata
  • 2023 Top 20 Training Industry Company - IT Training

    Need Help?

    Call us at 877-206-0106 or e-mail us at

    Personalized Solutions

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

    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? We are here to help!

    Talk to us

    Save Up to $500 This Summer

    Soak up new skills this summer with exclusive discounts on our popular topics and courses.


    Summer 2024 Promotion

    IBM TechXchange Conference 2024

    October 21-24 | Mandalay Bay, Las Vegas

    Save $127 with Code: TECHXCHANGE10

    Offer Expires June 30, 2024

    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







    Self-Paced Training Terms & Conditions


    Exam Terms & Conditions


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


    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.

    If you would like to request a quote for 5 or more students, please contact to be assigned an account representative.

    Need more Information?

    Speak with our training specialists to continue your learning journey.


    Delivery Formats


    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.