Azure SQL Training (AZU103)
Course Length: 1 day
Delivery Methods:
Available as private class only
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 Benefits
- Learn to gain a deeper knowledge and understanding of the Azure SQL Data Warehouse and how to write it.
Course Outline
- The Basics of Azure SQL
- Introduction
- Naming of Objects
- Setting Your Default Database
- SELECT * (All Columns) in a Table
- Fully Qualifying a Database, Schema and Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- 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
- How to ALIAS a Column Name
- Aliasing a Column Name with Spaces or Reserved Words
- A Missing Comma can by Mistake become an Alias
- 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
- Character Data needs Single Quotes, but 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
- NULL is UNKNOWN DATA so NOT 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
- GROUP BY delivers one row per Group
- 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
- Group by Rollup
- Answer Set for Group by Rollup Query
- Creating a Cube
- Answer Set for Cube Query
- An Easy Example of 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
- LEFT OUTER JOIN
- LEFT OUTER JOIN Results
- RIGHT OUTER JOIN
- RIGHT OUTER JOIN Example and Results
- FULL OUTER JOIN
- FULL OUTER JOIN Results
- Which Tables are the Left and which Tables are Right?
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional AND Clause
- OUTER JOIN with Additional AND Clause Results
- Evaluation Order for Outer Queries
- The DREADED Product Join
- The DREADED Product Join Results
- The Horrifying Cartesian product Join
- The ANSI Cartesian Join will ERROR
- The CROSS JOIN
- The CROSS JOIN Answer Set
- 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
- SYSDATETIMEOFFSET Provides the Timezone Offset
- Using both CAST and CONVERT in Literal Values
- Using Both CAST and CONVERT in Literal Values
- Using both CAST and CONVERT in Literal Values
- The DATEADD Function
- The DATEDIFF Function
- DATEADD Function
- A Real World Example for DateAdd Using the Order Table
- DATEPART Function
- DATEPART Function Examples
- YEAR, MONTH, and DAY Functions
- A Better Technique for 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
- Multiple Ways to Alias the Columns in a 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
- A Join Example Showing Different Column Alias Styles
- 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
- An Example of Two Derived Tables in a Single Query
- RECURSIVE Derived Table Hierarchy
- RECURSIVE Derived Table Query
- RECURSIVE Derived Table Definition
- WITH RECURSIVE Derived Table Seeding
- WITH RECURSIVE Derived Table Looping
- RECURSIVE Derived Table Looping in Slow Motion
- RECURSIVE Derived Table Looping Continued
- RECURSIVE Derived Table Looping Continued
- Six rows are added in the third loop. RECURSIVE Derived Table Ends the Looping
- RECURSIVE Derived Table Ends the Looping
- RECURSIVE Derived Table Definition
- RECURSIVE Derived Table Answer Set
- 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 Never has Duplicates – Just 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
- The Correlated NOT Exists Answer Set
- Window Functions OLAP
- The Row_Number Command
- Using a Derived Table and Row_Number
- Ordered Analytics OVER
- RANK and DENSE RANK
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK OVER and PARTITION BY
- 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
- MAX OVER with PARTITION BY Reset
- MAX OVER Without Rows Unbounded Preceding
- The MIN OVER Command
- How Ntile Works
- Ntile
- Ntile Continued
- Ntile Percentile
- Another Ntile Example
- Using Quartiles (Partitions of Four)
- NTILE Buckets
- NTILE Using a Value of 10
- NTILE with a Partition
- Using LAG and LEAD
- Using LEAD
- Using LEAD With and Offset of 2
- LEAD
- LEAD With Partitioning
- Using LAG
- Using LAG with an Offset of 2
- LAG
- LAG with Partitioning
- 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
- Using SUBSTRING to move Backwards
- How SUBSTRING Works with a Starting Position of -1
- How SUBSTRING Works with an Ending Position of 0
- 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
- PATINDEX Function to Find a Character Pattern
- 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
- STUFF without Deleting Function
- UPPER and lower Functions
- Interrogating the Data
- The NULLIF Command
- The COALESCE Command – Fill In the Answers
- The COALESCE Answer Set
- COALESCE is Equivalent to This CASE Statement
- The Basics of CAST (Convert and Store)
- Some Great CAST (Convert and Store) Examples
- 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 (1 of 3)
- The Building of a B-Tree for a Clustered Index (2 of 3)
- The Building of a B-Tree for a Clustered Index (3 of 3)
- The Row Offset Array is the Guidance System for Every Row
- The Row Offset Array Provides Two Search Options (1 of 2)
- The Row Offset Array Provides Two Search Options (2 of 2)
- 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 (1 of 2)
- B-Tree for Non Clustered Index on a Clustered Table (2 of 2)
- Adding a Non Clustered Index to A Heap
- B-Tree for Non Clustered Index on a Heap Table (1 of 2)
- B-Tree for a Non Clustered Index on a Heap Table (2 of 2)
- Default Values
- View Functions
- The Fundamentals of Views
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict 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
- Aggregates on View Aggregates
- Altering a Table
- Altering a Table after a View has been created
- A View that Errors after an ALTER
- Troubleshooting a View
- Loading Data through a View
- Data Manipulation Language (DML)
- INSERT Syntax # 1
- INSERT Example with Syntax 1
- INSERT Syntax #2
- INSERT Example with Syntax 2
- INSERT/SELECT Command
- INSERT/SELECT Example using All Columns (*)
- INSERT/SELECT Example with Less Columns
- The UPDATE Command Basic Syntax
- Two UPDATE Examples
- Subquery UPDATE Command Syntax
- Example of Subquery UPDATE Command
- Join UPDATE Command Syntax
- Example of an UPDATE Join Command
- The DELETE Command Basic Syntax
- Two DELETE Examples to DELETE ALL Rows in a Table
- To DELETE or to TRUNCATE
- A DELETE Example Deleting only Some of the Rows
- Subquery and Join DELETE Command Syntax
- Example of Subquery DELETE Command
- MERGE INTO
- MERGE INTO
- Set Operators Functions
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION ALL Explained Logically
- EXCEPT Explained Logically
- EXCEPT Explained Logically
- Another EXCEPT Example
- 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
- UNION Vs UNION ALL
- 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
- Executing a Stored Procedure
- There are Three Ways to Execute 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
- A VAR Example
- A VARP Example
- The STDEV and STDEVP Functions
- A STDEV Example
- A STDEVP Example
- 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
Class Materials
Each student will receive a comprehensive set of materials, including course notes and all the class examples.