Azure SQL Training

Azure SQL Training

Course Length:
Delivery Methods: Available for private class only
Course Topics
  • Learn to gain a deeper knowledge and understanding of the Azure SQL Data Warehouse and how to write it.
Course Overview

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.

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

Request a Private Class
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Pricing