Azure SQL Training

In this Azure SQL training class, students will learn the Azure SQL Data Warehouse starting at the most basic level. The course includes many examples.

Location

Public Classes: Delivered live online via WebEx and guaranteed to run . Join from anywhere!

Private Classes: Delivered at your offices , or any other location of your choice.

Goals
  1. Learn to gain a deeper knowledge and understanding of the Azure SQL Data Warehouse and how to write it.
Outline
  1. The Basics of Azure SQL
    1. Introduction
    2. Naming of Objects
    3. Setting Your Default Database
    4. SELECT * (All Columns) in a Table
    5. Fully Qualifying a Database, Schema and 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. An Order by That Uses an Expression
    21. How to ALIAS a Column Name
    22. Aliasing a Column Name with Spaces or Reserved Words
    23. A Missing Comma can by Mistake become an Alias
    24. Comments using Double Dashes are Single Line Comments
    25. Comments for Multi-Lines
    26. Comments for Multi-Lines as Double Dashes per Line
    27. A Great Technique for Comments to Look for SQL Errors
    28. sp_help at the Database Level
    29. sp_help at the Object Level
    30. Getting System Information
    31. Getting Additional System Information
  2. The Where Clause
    1. The WHERE Clause limits Returning Rows
    2. Double Quoted Aliases are for Reserved Words and Spaces
    3. Using a Column ALIAS in a WHERE Clause
    4. Using a Column ALIAS in an ORDER BY Clause
    5. In What Order Does SQL Server Process A Query?
    6. Character Data needs Single Quotes in the WHERE Clause
    7. Character Data needs Single Quotes, but Numbers Don't
    8. Declaring a Variable
    9. Comparisons against a Null Value
    10. NULL means UNKNOWN DATA so Equal (=) won't Work
    11. Use IS NULL or IS NOT NULL when dealing with NULLs
    12. NULL is UNKNOWN DATA so NOT Equal won't Work
    13. Use IS NULL or IS NOT NULL when dealing with NULLs
    14. Using Greater Than or Equal To (>=)
    15. AND in the WHERE Clause
    16. Troubleshooting AND
    17. OR in the WHERE Clause
    18. Troubleshooting Or
    19. Troubleshooting Character Data
    20. Using Different Columns in an AND Statement
    21. LIKE command Underscore is Wildcard for one Character
    22. LIKE command using a Range of Values
    23. LIKE command using a NOT Range of Values
    24. LIKE Command Works Differently on Char Vs Varchar
    25. Troubleshooting LIKE Command on Character Data
    26. Introducing the RTRIM Command
    27. Numbers are Right Justified and Character Data is Left
    28. An Example of Data with Left and Right Justification
    29. A Visual of CHARACTER Data vs. VARCHAR Data
    30. RTRIM command Removes Trailing spaces on CHAR Data
    31. Using Like with an AND Clause to Find Multiple Letters
    32. Using Like with an OR Clause to Find Either Letters
    33. Declaring a Variable and using it with the LIKE Command
    34. Escape Character in the LIKE Command changes Wildcards
    35. Escape Characters Turn off Wildcards in the LIKE Command
  3. Distinct, Group By and TOP
    1. The Distinct Command
    2. Distinct vs. GROUP BY
    3. TOP Command
    4. TOP Command is brilliant when ORDER BY is used!
    5. TOP Command with Ties
    6. TOP Command Using a Variable
  4. Aggregation
    1. The 3 Rules of Aggregation
    2. There are Five Aggregates
    3. Troubleshooting Aggregates
    4. GROUP BY when Aggregates and Normal Columns Mix
    5. GROUP BY delivers one row per Group
    6. Count_Big
    7. Limiting Rows and Improving Performance with WHERE
    8. WHERE Clause in Aggregation limits unneeded Calculations
    9. Keyword HAVING tests Aggregates after they are totaled
    10. Group by Grouping Sets
    11. Group by Rollup
    12. Answer Set for Group by Rollup Query
    13. Creating a Cube
    14. Answer Set for Cube Query
    15. An Easy Example of Creating a Cube
    16. Getting the Average Values per Column
    17. Average Values per Column for all Columns in a Table
  5. Join Functions
    1. Redistribution
    2. Big Table Small Table Join Strategy
    3. Duplication of the Smaller Table across All-Distributions
    4. If the Join Condition is the Distribution Key no Movement
    5. Matching Rows That Are On The Same Node Naturally
    6. What if the Join Condition Columns are Not Primary Indexes
    7. Strategy 1 of 4 – The Merge Join
    8. Strategy 2 of 4 – The Hash Join
    9. Strategy 4 of 4 – The Product Join
    10. A Two-Table Join Using Traditional Syntax
    11. A two-table join using Non-ANSI Syntax with Table Alias
    12. You Can Fully Qualify All Columns
    13. A two-table join using ANSI Syntax
    14. Both Queries have the same Results and Performance
    15. LEFT OUTER JOIN
    16. LEFT OUTER JOIN Results
    17. RIGHT OUTER JOIN
    18. RIGHT OUTER JOIN Example and Results
    19. FULL OUTER JOIN
    20. FULL OUTER JOIN Results
    21. Which Tables are the Left and which Tables are Right?
    22. INNER JOIN with Additional AND Clause
    23. ANSI INNER JOIN with Additional AND Clause
    24. ANSI INNER JOIN with Additional WHERE Clause
    25. OUTER JOIN with Additional WHERE Clause
    26. OUTER JOIN with Additional AND Clause
    27. OUTER JOIN with Additional AND Clause Results
    28. Evaluation Order for Outer Queries
    29. The DREADED Product Join
    30. The DREADED Product Join Results
    31. The Horrifying Cartesian product Join
    32. The ANSI Cartesian Join will ERROR
    33. The CROSS JOIN
    34. The CROSS JOIN Answer Set
    35. The Self Join
    36. The Self Join with ANSI Syntax
    37. How would you join these two tables?
    38. An Associative Table is a Bridge that Joins Two Tables
    39. The 5-Table Join – Logical Insurance Model
  6. Date Function
    1. Current_Timestamp
    2. Getdate
    3. Date and Time Keywords
    4. SYSDATETIMEOFFSET Provides the Timezone Offset
    5. SYSDATETIMEOFFSET Provides the Timezone Offset
    6. Using both CAST and CONVERT in Literal Values
    7. Using Both CAST and CONVERT in Literal Values
    8. Using both CAST and CONVERT in Literal Values
    9. The DATEADD Function
    10. The DATEDIFF Function
    11. DATEADD Function
    12. A Real World Example for DateAdd Using the Order Table
    13. DATEPART Function
    14. DATEPART Function Examples
    15. YEAR, MONTH, and DAY Functions
    16. A Better Technique for YEAR, MONTH, and DAY Functions
    17. DATENAME Function
    18. ISDATE Function
  7. Temporary Tables
    1. Temporary Tables
    2. CREATING A Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in the Derived Table
    5. Multiple Ways to Alias the Columns in a Derived Table
    6. CREATING a Derived Table using the WITH Command
    7. The Same Derived Query shown Three Different Ways
    8. MULTIPLE Derived Tables using the WITH Command
    9. Column Alias Can Default For Normal Columns
    10. Most Derived Tables Are Used To Join To Other Tables
    11. A Join Example Showing Different Column Alias Styles
    12. The Three Components of a Derived Table
    13. Visualize This Derived Table
    14. Our Join Example With the WITH Syntax
    15. Clever Tricks on Aliasing Columns in a Derived Table
    16. A Derived Table lives only for the lifetime of a single query
    17. An Example of Two Derived Tables in a Single Query
    18. RECURSIVE Derived Table Hierarchy
    19. RECURSIVE Derived Table Query
    20. RECURSIVE Derived Table Definition
    21. WITH RECURSIVE Derived Table Seeding
    22. WITH RECURSIVE Derived Table Looping
    23. RECURSIVE Derived Table Looping in Slow Motion
    24. RECURSIVE Derived Table Looping Continued
    25. RECURSIVE Derived Table Looping Continued
    26. Six rows are added in the third loop. RECURSIVE Derived Table Ends the Looping
    27. RECURSIVE Derived Table Ends the Looping
    28. RECURSIVE Derived Table Definition
    29. RECURSIVE Derived Table Answer Set
    30. What is TEMPDB?
    31. Creating a Temporary Table
    32. The Three Steps to Use a Private Temporary Table
    33. Creating a Temporary Table with a Clustered Index
    34. Creating a Columnstore Temporary Table from a CTAS
  8. 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. Should you use a Subquery or a Join?
    9. The Basics of a Correlated Subquery
    10. The Top Query always runs first in a Correlated Subquery
    11. Correlated Subquery Example vs. a Join with a Derived Table
    12. How to handle a NOT IN with Potential NULL Values
    13. Using a Correlated Exists
    14. How a Correlated Exists matches up
    15. The Correlated NOT Exists
    16. The Correlated NOT Exists Answer Set
  9. Window Functions OLAP
    1. The Row_Number Command
    2. Using a Derived Table and Row_Number
    3. Ordered Analytics OVER
    4. RANK and DENSE RANK
    5. RANK Defaults to Ascending Order
    6. Getting RANK to Sort in DESC Order
    7. RANK OVER and PARTITION BY
    8. Cumulative Sum
    9. The ANSI CSUM – Getting a Sequential Number
    10. Troubleshooting the ANSI OLAP on a GROUP BY
    11. Reset with a PARTITION BY Statement
    12. PARTITION BY only Resets a Single OLAP not ALL of them
    13. Sorting in DESC Order
    14. Moving Average
    15. Casting a Moving Average
    16. Partition by Resets an ANSI OLAP
    17. COUNT OVER for a Sequential Number
    18. The MAX OVER Command
    19. MAX OVER with PARTITION BY Reset
    20. MAX OVER Without Rows Unbounded Preceding
    21. The MIN OVER Command
    22. How Ntile Works
    23. Ntile
    24. Ntile Continued
    25. Ntile Percentile
    26. Another Ntile Example
    27. Using Quartiles (Partitions of Four)
    28. NTILE Buckets
    29. NTILE Using a Value of 10
    30. NTILE with a Partition
    31. Using LAG and LEAD
    32. Using LEAD
    33. Using LEAD With and Offset of 2
    34. LEAD
    35. LEAD With Partitioning
    36. Using LAG
    37. Using LAG with an Offset of 2
    38. LAG
    39. LAG with Partitioning
    40. SUM (SUM (n))
  10. Working with Strings
    1. The ASCII Function
    2. The CHAR Function
    3. The UNICODE Function
    4. The NCHAR Function
    5. The LEN Function
    6. The DATALENGTH Function
    7. Concatenation
    8. The RTRIM and LTRIM Command trims Spaces
    9. The SUBSTRING Command
    10. Using SUBSTRING to move Backwards
    11. How SUBSTRING Works with a Starting Position of -1
    12. How SUBSTRING Works with an Ending Position of 0
    13. Concatenation and SUBSTRING
    14. SUBSTRING and Different Aliasing
    15. The LEFT and RIGHT Functions
    16. Four Concatenations Together
    17. The DATALENGTH Function and RTRIM
    18. A Visual of the TRIM Command Using Concatenation
    19. CHARINDEX Function Finds a Letter(s) Position in a String
    20. The CHARINDEX Command is brilliant with SUBSTRING
    21. The CHARINDEX Command Using a Literal
    22. PATINDEX Function
    23. PATINDEX Function to Find a Character Pattern
    24. SOUNDEX Function to Find a Sound
    25. DIFFERENCE Function to Quantile a Sound
    26. The REPLACE Function
    27. LEN and REPLACE Functions for Number of Occurrences
    28. REPLICATE Function
    29. STUFF Function
    30. STUFF without Deleting Function
    31. UPPER and lower Functions
  11. Interrogating the Data
    1. The NULLIF Command
    2. The COALESCE Command – Fill In the Answers
    3. The COALESCE Answer Set
    4. COALESCE is Equivalent to This CASE Statement
    5. The Basics of CAST (Convert and Store)
    6. Some Great CAST (Convert and Store) Examples
    7. Some Great CAST (Convert and Store) Examples
    8. A Rounding Example
    9. Using an ELSE in the Case Statement
    10. Using an ELSE as a Safety Net
    11. Rules For a Valued Case Statement
    12. Rules for a Searched Case Statement
    13. Valued Case Vs. A Searched Case
    14. Combining Searched Case and Valued Case
    15. A Trick for getting a Horizontal Case
    16. Nested Case
    17. Put a CASE in the ORDER BY
  12. Table Create and Data Types
    1. Creating a Database
    2. Creating a Table that is a Heap
    3. Heap Page
    4. Extents
    5. Creating a Table That Has a Clustered Index
    6. Clustered Index Page
    7. When Do I Create a Clustered Index?
    8. B-Trees
    9. The Building of a B-Tree for a Clustered Index (1 of 3)
    10. The Building of a B-Tree for a Clustered Index (2 of 3)
    11. The Building of a B-Tree for a Clustered Index (3 of 3)
    12. The Row Offset Array is the Guidance System for Every Row
    13. The Row Offset Array Provides Two Search Options (1 of 2)
    14. The Row Offset Array Provides Two Search Options (2 of 2)
    15. The Row Offset Array Helps with Inserts
    16. What is a Uniquefier?
    17. Adding an Index
    18. When Do I Create a Non Clustered Index?
    19. B-Tree for Non Clustered Index on a Clustered Table (1 of 2)
    20. B-Tree for Non Clustered Index on a Clustered Table (2 of 2)
    21. Adding a Non Clustered Index to A Heap
    22. B-Tree for Non Clustered Index on a Heap Table (1 of 2)
    23. B-Tree for a Non Clustered Index on a Heap Table (2 of 2)
    24. Default Values
  13. View Functions
    1. The Fundamentals of Views
    2. Creating a Simple View to Restrict Sensitive Columns
    3. Creating a Simple View to Restrict Rows
    4. Basic Rules for Views
    5. Two Exceptions to the ORDER BY Rule inside a View
    6. Views sometimes CREATED for Row Security
    7. Creating a View to Join Tables Together
    8. You Select From a View
    9. Another Way to Alias Columns in a View CREATE
    10. The Standard Way Most Aliasing is done
    11. What Happens When Both Aliasing Options Are Present
    12. Resolving Aliasing Problems in a View CREATE
    13. Aggregates on View Aggregates
    14. Altering a Table
    15. Altering a Table after a View has been created
    16. A View that Errors after an ALTER
    17. Troubleshooting a View
    18. Loading Data through a View
  14. Data Manipulation Language (DML)
    1. INSERT Syntax # 1
    2. INSERT Example with Syntax 1
    3. INSERT Syntax #2
    4. INSERT Example with Syntax 2
    5. INSERT/SELECT Command
    6. INSERT/SELECT Example using All Columns (*)
    7. INSERT/SELECT Example with Less Columns
    8. The UPDATE Command Basic Syntax
    9. Two UPDATE Examples
    10. Subquery UPDATE Command Syntax
    11. Example of Subquery UPDATE Command
    12. Join UPDATE Command Syntax
    13. Example of an UPDATE Join Command
    14. The DELETE Command Basic Syntax
    15. Two DELETE Examples to DELETE ALL Rows in a Table
    16. To DELETE or to TRUNCATE
    17. A DELETE Example Deleting only Some of the Rows
    18. Subquery and Join DELETE Command Syntax
    19. Example of Subquery DELETE Command
    20. MERGE INTO
    21. MERGE INTO
    22. Set Operators Functions
    23. Rules of Set Operators
    24. INTERSECT Explained Logically
    25. INTERSECT Explained Logically
    26. UNION Explained Logically
    27. UNION Explained Logically
    28. UNION ALL Explained Logically
    29. UNION ALL Explained Logically
    30. EXCEPT Explained Logically
    31. EXCEPT Explained Logically
    32. Another EXCEPT Example
    33. EXCEPT Explained Logically in Reverse Order
    34. An Equal Amount of Columns in both SELECT List
    35. Columns in the SELECT list should be from the same Domain
    36. The Top Query handles all Aliases
    37. The Bottom Query does the ORDER BY
    38. Great Trick: Place your Set Operator in a Derived Table
    39. UNION Vs UNION ALL
    40. Using UNION ALL and Literals
    41. A Great Example of how EXCEPT works
    42. USING Multiple SET Operators in a Single Request
    43. Changing the Order of Precedence with Parentheses
    44. Building Grouping Sets Using UNION
    45. Three Grouping Sets Using a UNION
    46. Stored Procedure Functions
    47. Creating a Stored Procedure
    48. Executing a Stored Procedure
    49. There are Three Ways to Execute a Stored Procedure
    50. Creating a Stored Procedure with a CASE Statement
    51. Our Answer Set
    52. Dropping a Stored Procedure
    53. Passing an Input Parameter to a Stored Procedure
    54. Executing With Positional Parameter vs. Named Parameters
    55. Passing an Output Parameter to a Stored Procedure
    56. Changing a Stored Procedure with an ALTER
    57. Answer Set for the Altered Stored Procedure
    58. Using a Stored Procedure to Delete a Row
    59. A Different Method to Delete a Row
    60. Deleting a Row Using an Input Parameter
    61. Using Loops in Stored Procedures
    62. Stored Procedure Workshop
    63. Looping with a WHILE Statement
    64. Statistical Aggregate Functions
    65. The Stats Table
    66. The VAR and VARP Functions
    67. A VAR Example
    68. A VARP Example
    69. The STDEV and STDEVP Functions
    70. A STDEV Example
    71. A STDEVP Example
    72. Systems Views
    73. System Views
    74. sys.all_columns
    75. sys.all_objects
    76. sys.all_sql_modules
    77. sys.all_views
    78. sys.columns
    79. sys.data_spaces
    80. sys.database_files
    81. sys.database_principals
    82. sys.database_role_members
    83. sys.databases
    84. sys.filegroups
    85. sys.identity_columns
    86. sys.objects
    87. sys.partition_range_values
    88. sys.schemas
    89. sys.server_role_members
    90. sys.sql_logins
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.

Training for your Team

Length: 1 Day
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors

What people say about our training

The instructor was excellent, class was well paced and very informative. The good use of time kept us on track while still being able to answer individual questions, needs or concerns.
Scott Sufak
Red Energy
It is good to learn from an instructor who not only has a passion about what they are teaching, but also has extensive work experience related to what they are teaching. Great course and better teacher!
Kyle Wristbridge
InvestEdge, Inc.
Well worth the money for someone who is new to Business Objects and would like a clear, user friendly introduction to all the basic functions.
Randy Wong
Triton
Way to go! Easy to log on and easy way to learn!
Andrea Tijerina
Rady Children's Hospital - SD

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

63,932

Students who have taken Instructor-led Training

11,926

Organizations who trust Webucator for their Instructor-led training needs

100%

Satisfaction guarantee and retake option

9.29

Students rated our trainers 9.29 out of 10 based on 30,031 reviews

Contact Us or call 1-877-932-8228