Course duration
- 1 day
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
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Instructor-led courses are offered via a live Web connection, at client sites throughout Europe, and at our Geneva Training Center.