Azure SQL Data Warehouse Architecture and SQL

Customized Onsite Training

3
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors
Overview

In this training class, 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.

Goals
  1. Learn to gain a deeper knowledge and understanding of the Azure SQL Data Warehouse Architecture and SQL and how to write it.
Outline
  1. Introduction to the Azure SQL Data Warehouse
    1. Introduction to the Family of SQL Server Products
    2. Introduction to the Family Continued
    3. Microsoft Azure SQL Data Warehouse
    4. Symmetric Multi-Processing (SMP)
    5. What is Parallel Processing?
    6. The Basics of a Single Computer
    7. Data in Memory is fast as Lightning
    8. Parallel Processing of Data
    9. A Table has Columns and Rows
    10. The Azure SQL Data Warehouse has Linear Scalability
    11. The Architecture of the Azure SQL Data Warehouse
    12. Nexus is now available on the Microsoft Azure Cloud
    13. The MPP Engine is the Optimizer
    14. The Azure SQL Data Warehouse System
    15. The Azure SQL Data Warehouse System is Scalable
    16. The Control Node
    17. The Data Rack
    18. The Landing Zone
    19. The Backup Node
    20. Software as a Service (SaaS) and the Elastic Database
    21. Azure Data Lake
    22. Azure Disaster Recovery
    23. Security and Compliance
    24. How to Get an EXPLAIN Plan
  2. The Azure SQL Data Warehouse Table Structures
    1. The 5 Concepts of Azure SQL Data Warehouse Tables
    2. Tables are Either Distributed by Hash or Replicated (1 of 5)
    3. Table Rows are Either Sorted or Unsorted (2 of 5)
    4. Tables are Stored in Either Row or Columnar Format (3 of 5)
    5. Tables can be Partitioned (4 of 5)
    6. There are Permanent, Temporary and External Tables (5 of 5)
    7. Creating a Table with a Distribution Key
    8. Creating a Table that is replicated
    9. Distributed by Hash vs. Replication
    10. The Concept is all about the Joins
    11. Creation of a Hash Distributed Table with a Clustered Index
    12. A Clustered Index Sorts the Data Stored on Disk
    13. Each Node Has 8 Distributions
    14. How Hashed Tables are Stored among a Single Node
    15. Hashed Tables Will Be Distributed Among All Distributions
    16. Creation of a Replicated Table
    17. How Replicated Tables are Stored among a Single Node
    18. Replicated Table will be duplicated among Each Node
    19. Distributed by Replication
    20. How Hashed and Replicated Tables Work Together
    21. Tables are stored as Row-based or Column-based
    22. Creation of a Columnar Table that is hashed
    23. How Hashed Columnar Tables are Stored on a Single Node
    24. How Hashed Columnar Tables are Stored on All Distributions
    25. Comparing Normal Table vs. Columnar Tables
    26. Columnar can move just One Segment to Memory
    27. Segments on Distributions are aligned to rebuild a Row
    28. Why Columnar?
    29. Columnar Tables Store Each Column in Separate Pages
    30. Visualize the Data - Rows vs. Columns
    31. Creation of a Columnar Table that is replicated
    32. Creating a Partitioned Table per Month
    33. A Visual of One Year of Data with Range per Month
    34. Another Create Example of a Partitioned Table
    35. Creating a Partitioned Table per Month That is a Columnstore
    36. Visual of Row Partitioning and Columnar Storage
    37. CREATE TABLE AS (CTAS) Example
    38. Creating a Temporary Table
    39. Facts about Tables
  3. Hashing and Data Distribution
    1. Distribution Keys Hashed on Unique Values Spread Evenly
    2. Distribution Keys with Non-Unique Values Spread Unevenly
    3. Best Practices for Choosing a Distribution Key
    4. The Hash Map determines which Distribution owns the Row
    5. The Hash Map determines which Node will own the Row
    6. A Review of the Hashing Process
    7. Non-Unique Distribution Keys have Skewed Data
  4. The Technical Details
    1. Every Node has the Exact Same Tables
    2. Hashed Tables are spread across All Distributions
    3. The Table Header and the Data Rows are Stored Separately
    4. A Distribution Stores the Rows of a Table inside a Data Block
    5. To Read a Data Block a Node Moves the Block into Memory
    6. A Full Table Scan Means All Nodes Must Read All Rows
    7. Rows are organized inside a Page
    8. Moving Data Blocks is Like Checking in Luggage
    9. As Row-Based Tables Get Bigger, the Page Splits
    10. Data Pages are Processed One at a Time per Unit
    11. Creating a Table that is a Heap
    12. Heap Page
    13. Extents
    14. Creating a Table that has a Clustered Index
    15. Clustered Index Page
    16. The Row Offset Array is the Guidance System for Every Row
    17. The Row Offset Array Provides Two Search Options (1 of 2)
    18. The Row Offset Array Provides Two Search Options (2 of 2)
    19. The Row Offset Array Helps with Inserts
    20. B-Trees
    21. The Building of a B-Tree for a Clustered Index (1 of 3)
    22. The Building of a B-Tree for a Clustered Index (2 of 3)
    23. The Building of a B-Tree for a Clustered Index (3 of 3)
    24. When Do I Create a Clustered Index?
    25. When Do I Create a Non Clustered Index?
    26. B-Tree for Non Clustered Index on a Clustered Table (1 of 2)
    27. B-Tree for Non Clustered Index on a Clustered Table (2 of 2)
    28. Adding a Non Clustered Index to A Heap
    29. B-Tree for Non Clustered Index on a Heap Table (1 of 2)
    30. B-Tree for Non Clustered Index on a Heap Table (2 of 2)
    31. Max Levels on the Azure SQL Data Warehouse
    32. Azure SQL Data Warehouse Data Types
    33. Character Data Types for SQL Server
    34. Numeric Data Types for SQL Server
    35. Date and Time Data Types for SQL Server
    36. Additional Data Types for SQL Server
  5. CREATE Statistics
    1. CREATE Statistics Syntax
    2. CREATE Statistics on a Percentage of a Table
    3. CREATE Statistics on a Sample by Using the System Default
    4. CREATE Statistics on a Multi-Column Join Key
    5. What to Column(s) to CREATE Statistics On
    6. CREATE Statistics Using a WHERE Clause
    7. Updating All Statistics on a Table
    8. Updating Only Certain Statistics on a Table
    9. Dropping Statistics on Certain Statistics on a Table
    10. Showing the Statistics
    11. DBCC SHOW_STATISTICS
    12. DBCC SHOW_STATISTICS WITH HISTOGRAM
  6. The Basics of 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
  7. 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. Quiz - How many rows will return?
    22. Answer to Quiz - How many rows will return?
    23. LIKE command Underscore is Wildcard for one Character
    24. LIKE command using a Range of Values
    25. LIKE command using a NOT Range of Values
    26. LIKE Command Works Differently on Char Vs Varchar
    27. Troubleshooting LIKE Command on Character Data
    28. Introducing the RTRIM Command
    29. Quiz - What Data is Left Justified and what is Right?
    30. Numbers are Right Justified and Character Data is Left
    31. Answer - What Data is Left Justified and what is Right?
    32. An Example of Data with Left and Right Justification
    33. A Visual of CHARACTER Data vs. VARCHAR Data
    34. RTRIM command Removes Trailing spaces on CHAR Data
    35. Using Like with an AND Clause to Find Multiple Letters
    36. Using Like with an OR Clause to Find Either Letters
    37. Declaring a Variable and using it with the LIKE Command
    38. Escape Character in the LIKE Command changes Wildcards
    39. Escape Characters Turn off Wildcards in the LIKE Command
    40. Quiz - Turn off that Wildcard
    41. ANSWER - To Find that Wildcard
  8. Distinct, Group By and TOP
    1. The Distinct Command
    2. Distinct vs. GROUP BY
    3. Quiz - How many rows come back from the Distinct?
    4. Answer - How many rows come back from the Distinct?
    5. TOP Command
    6. TOP Command is brilliant when ORDER BY is used!
    7. TOP Command with Ties
    8. TOP Command Using a Variable
  9. Aggregation
    1. Quiz - You calculate the Answer Set in your own Mind
    2. Answer - You calculate the Answer Set in your own Mind
    3. The 3 Rules of Aggregation
    4. There are Five Aggregates
    5. Quiz - How many rows come back?
    6. Answer - How many rows come back?
    7. Troubleshooting Aggregates
    8. GROUP BY when Aggregates and Normal Columns Mix
    9. GROUP BY delivers one row per Group
    10. Count_Big
    11. Limiting Rows and Improving Performance with WHERE
    12. WHERE Clause in Aggregation limits unneeded Calculations
    13. Keyword HAVING tests Aggregates after they are totaled
    14. Group by Grouping Sets
    15. Group by Rollup
    16. Answer Set for Group by Rollup Query
    17. Creating a Cube
    18. Answer Set for Cube Query
    19. An Easy Example of Creating a Cube
    20. Quiz - GROUP BY GROUPING SETS Challenge
    21. Answer To Quiz - GROUP BY GROUPING SETS Challenge
    22. Getting the Average Values per Column
    23. Average Values per Column for all Columns in a Table
  10. Join Functions
    1. The Azure SQL Data Warehouse Join Quiz
    2. The Azure SQL Data Warehouse Join Quiz Answer
    3. Redistribution
    4. Big Table Small Table Join Strategy
    5. Duplication of the Smaller Table across All-Distributions
    6. If the Join Condition is the Distribution Key no Movement
    7. Matching Rows That Are On The Same Node Naturally
    8. What if the Join Condition Columns are Not Primary Indexes
    9. Strategy 1 of 4 - The Merge Join
    10. Quiz - Redistribute the Employees by their Dept_No
    11. Quiz -Dept_No landed on Distribution with Matches
    12. Quiz - Redistribute the Orders to the Proper Distribution
    13. Answer to Redistribute the Employees by their Dept_No Quiz
    14. Strategy 2 of 4 - The Hash Join
    15. Strategy 4 of 4 - The Product Join
    16. A Two-Table Join Using Traditional Syntax
    17. A two-table join using Non-ANSI Syntax with Table Alias
    18. You Can Fully Qualify All Columns
    19. A two-table join using ANSI Syntax
    20. Both Queries have the same Results and Performance
    21. Quiz - Can You Finish the Join Syntax?
    22. Answer to Quiz - Can You Finish the Join Syntax?
    23. Quiz - Can You Find the Error?
    24. Answer to Quiz - Can You Find the Error?
    25. Super Quiz - Can You Find the Difficult Error?
    26. Answer to Super Quiz - Can You Find the Difficult Error?
    27. Quiz - Which rows from both tables won't return?
    28. Answer to Quiz - Which rows from both tables Won't Return?
    29. LEFT OUTER JOIN
    30. LEFT OUTER JOIN Results
    31. RIGHT OUTER JOIN
    32. RIGHT OUTER JOIN Example and Results
    33. FULL OUTER JOIN
    34. FULL OUTER JOIN Results
    35. Which Tables are the Left and which Tables are Right?
    36. Answer - Which Tables are the Left and which are the Right?
    37. INNER JOIN with Additional AND Clause
    38. ANSI INNER JOIN with Additional AND Clause
    39. ANSI INNER JOIN with Additional WHERE Clause
    40. OUTER JOIN with Additional WHERE Clause
    41. OUTER JOIN with Additional AND Clause
    42. OUTER JOIN with Additional AND Clause Results
    43. Quiz - Why is this considered an INNER JOIN?
    44. Evaluation Order for Outer Queries
    45. The DREADED Product Join
    46. The DREADED Product Join Results
    47. The Horrifying Cartesian product Join
    48. The ANSI Cartesian Join will ERROR
    49. Quiz - Do these Joins Return the Same Answer Set?
    50. Answer - Do these Joins Return the Same Answer Set?
    51. The CROSS JOIN
    52. The CROSS JOIN Answer Set
    53. The Self Join
    54. The Self Join with ANSI Syntax
    55. Quiz - Will both queries bring back the same Answer Set?
    56. Answer - Will both queries bring back the same Answer Set?
    57. Quiz - Will both queries bring back the same Answer Set?
    58. Answer - Will both queries bring back the same Answer Set?
    59. How would you join these two tables?
    60. An Associative Table is a Bridge that Joins Two Tables
    61. Quiz - Can you write the 3-Table Join?
    62. Answer to quiz - Can you write the 3-Table Join?
    63. Quiz - Can you write the 3-Table Join to ANSI Syntax?
    64. Answer - Can you write the 3-Table Join to ANSI Syntax?
    65. Quiz - Can you Place the ON Clauses at the End?
    66. Answer - Can you Place the ON Clauses at the End?
    67. The 5-Table Join - Logical Insurance Model
    68. Quiz - Write a Five Table Join Using ANSI Syntax
    69. Answer - Write a Five Table Join Using ANSI Syntax
    70. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    71. Answer - Write a Five Table Join Using Non-ANSI Syntax
    72. Quiz -Re-Write this putting the ON clauses at the END
    73. Answer -Re-Write this putting the ON clauses at the END
  11. CDate 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
  12. 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. Quiz - Answer the Questions
    16. Answer to Quiz - Answer the Questions
    17. Clever Tricks on Aliasing Columns in a Derived Table
    18. A Derived Table lives only for the lifetime of a single query
    19. An Example of Two Derived Tables in a Single Query
    20. RECURSIVE Derived Table Hierarchy
    21. RECURSIVE Derived Table Query
    22. RECURSIVE Derived Table Definition
    23. WITH RECURSIVE Derived Table Seeding
    24. WITH RECURSIVE Derived Table Looping
    25. RECURSIVE Derived Table Looping in Slow Motion
    26. RECURSIVE Derived Table Looping Continued
    27. RECURSIVE Derived Table Looping Continued
    28. Six rows are added in the third loop. RECURSIVE Derived Table Ends the Looping
    29. RECURSIVE Derived Table Ends the Looping
    30. RECURSIVE Derived Table Definition
    31. RECURSIVE Derived Table Answer Set
    32. What is TEMPDB?
    33. Creating a Temporary Table
    34. The Three Steps to Use a Private Temporary Table
    35. Creating a Temporary Table with a Clustered Index
    36. Creating a Columnstore Temporary Table from a CTAS
  13. 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. Quiz- Answer the Difficult Question
    9. Answer to Quiz- Answer the Difficult Question
    10. Should you use a Subquery or a Join?
    11. Quiz- Write the Subquery
    12. Answer to Quiz- Write the Subquery
    13. Quiz- Write the More Difficult Subquery
    14. Answer to Quiz- Write the More Difficult Subquery
    15. Quiz - Write the Extreme Subquery
    16. Answer to Quiz - Write the Extreme Subquery
    17. Quiz- Write the Subquery with an Aggregate
    18. Answer to Quiz- Write the Subquery with an Aggregate
    19. Quiz- Write the Correlated Subquery
    20. Answer to Quiz- Write the Correlated Subquery
    21. The Basics of a Correlated Subquery
    22. The Top Query always runs first in a Correlated Subquery
    23. Correlated Subquery Example vs. a Join with a Derived Table
    24. Quiz- A Second Chance to Write a Correlated Subquery
    25. Answer - A Second Chance to Write a Correlated Subquery
    26. Quiz- A Third Chance to Write a Correlated Subquery
    27. Answer - A Third Chance to Write a Correlated Subquery
    28. Quiz- Last Chance to Write a Correlated Subquery
    29. Answer - Last Chance to Write a Correlated Subquery
    30. Quiz - Write the Extreme Correlated Subquery
    31. Answer To Quiz - Write the Extreme Correlated Subquery
    32. Quiz- Write the NOT Subquery
    33. Answer to Quiz- Write the NOT Subquery
    34. Quiz- Write the Subquery using a WHERE Clause
    35. Answer - Write the Subquery using a WHERE Clause
    36. Quiz - Write the Triple Subquery
    37. Answer to Quiz - Write the Triple Subquery
    38. Quiz - How many rows return on a NOT IN with a NULL?
    39. Answer - How many rows return on a NOT IN with a NULL?
    40. How to handle a NOT IN with Potential NULL Values
    41. Using a Correlated Exists
    42. How a Correlated Exists matches up
    43. The Correlated NOT Exists
    44. The Correlated NOT Exists Answer Set
    45. Quiz - How many rows come back from this NOT Exists?
    46. Answer - How many rows come back from this NOT Exists?
  14. Window Functions OLAP
    1. The Row_Number Command
    2. Quiz - How did the Row_Number Reset?
    3. Quiz - How did the Row_Number Reset?
    4. Using a Derived Table and Row_Number
    5. Ordered Analytics OVER
    6. RANK and DENSE RANK
    7. RANK Defaults to Ascending Order
    8. Getting RANK to Sort in DESC Order
    9. RANK OVER and PARTITION BY
    10. Cumulative Sum
    11. The ANSI CSUM - Getting a Sequential Number
    12. Troubleshooting the ANSI OLAP on a GROUP BY
    13. Reset with a PARTITION BY Statement
    14. PARTITION BY only Resets a Single OLAP not ALL of them
    15. Sorting in DESC Order
    16. Moving Average
    17. Casting a Moving Average
    18. Partition by Resets an ANSI OLAP
    19. COUNT OVER for a Sequential Number
    20. Quiz - What caused the COUNT OVER to Reset?
    21. Answer to Quiz - What caused the COUNT OVER to Reset?
    22. The MAX OVER Command
    23. MAX OVER with PARTITION BY Reset
    24. MAX OVER Without Rows Unbounded Preceding
    25. The MIN OVER Command
    26. Quiz - Fill in the Blank
    27. Answer - Fill in the Blank
    28. How Ntile Works
    29. Ntile
    30. Ntile Continued
    31. Ntile Percentile
    32. Another Ntile Example
    33. Using Quartiles (Partitions of Four)
    34. NTILE Buckets
    35. NTILE Using a Value of 10
    36. NTILE with a Partition
    37. Using LAG and LEAD
    38. Using LEAD
    39. Using LEAD With and Offset of 2
    40. LEAD
    41. LEAD With Partitioning
    42. Using LAG
    43. Using LAG with an Offset of 2
    44. LAG
    45. LAG with Partitioning
    46. SUM (SUM (n))
  15. 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
  16. Interrogating the Data
    1. Quiz - What would the Answer be?
    2. Answer to Quiz - What would the Answer be?
    3. The NULLIF Command
    4. Quiz - Fill in the Answers for the NULLIF Command
    5. Answer- Fill in the Answers for the NULLIF Command
    6. The COALESCE Command - Fill In the Answers
    7. The COALESCE Answer Set
    8. COALESCE is Equivalent to This CASE Statement
    9. The Basics of CAST (Convert and Store)
    10. Some Great CAST (Convert and Store) Examples
    11. Some Great CAST (Convert and Store) Examples
    12. A Rounding Example
    13. Quiz - CAST Examples
    14. Answer to Quiz - CAST Examples
    15. Quiz - The Basics of the CASE Statements
    16. Answer to Quiz - The Basics of the CASE Statements
    17. Using an ELSE in the Case Statement
    18. Using an ELSE as a Safety Net
    19. Rules For a Valued Case Statement
    20. Rules for a Searched Case Statement
    21. Valued Case Vs. A Searched Case
    22. Quiz - Valued Case Statement
    23. Answer - Valued Case Statement
    24. Quiz - Searched Case Statement
    25. Answer - Searched Case Statement
    26. Quiz - When NO ELSE is present in CASE Statement
    27. Answer - When NO ELSE is present in CASE Statement
    28. Quiz -When an Alias is NOT used in a CASE Statement
    29. Answer -When an Alias is NOT used in a CASE Statement
    30. Combining Searched Case and Valued Case
    31. A Trick for getting a Horizontal Case
    32. Nested Case
    33. Put a CASE in the ORDER BY
  17. 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
  18. 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. Answer to Resolving Aliasing Problems in a View CREATE
    14. Aggregates on View Aggregates
    15. Altering a Table
    16. Altering a Table after a View has been created
    17. A View that Errors after an ALTER
    18. Troubleshooting a View
    19. Loading Data through a View
  19. 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
  20. Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION ALL Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Another EXCEPT Example
    11. EXCEPT Explained Logically in Reverse Order
    12. An Equal Amount of Columns in both SELECT List
    13. Columns in the SELECT list should be from the same Domain
    14. The Top Query handles all Aliases
    15. The Bottom Query does the ORDER BY
    16. Great Trick:Place your Set Operator in a Derived Table
    17. UNION Vs UNION ALL
    18. Using UNION ALL and Literals
    19. A Great Example of how EXCEPT works
    20. USING Multiple SET Operators in a Single Request
    21. Changing the Order of Precedence with Parentheses
    22. Building Grouping Sets Using UNION
    23. Three Grouping Sets Using a UNION
  21. Stored Procedure Functions
    1. Creating a Stored Procedure
    2. Executing a Stored Procedure
    3. There are Three Ways to Execute a Stored Procedure
    4. Creating a Stored Procedure with a CASE Statement
    5. Our Answer Set
    6. Dropping a Stored Procedure
    7. Passing an Input Parameter to a Stored Procedure
    8. Executing With Positional Parameter vs. Named Parameters
    9. Passing an Output Parameter to a Stored Procedure
    10. Changing a Stored Procedure with an ALTER
    11. Answer Set for the Altered Stored Procedure
    12. Using a Stored Procedure to Delete a Row
    13. A Different Method to Delete a Row
    14. Deleting a Row Using an Input Parameter
    15. Using Loops in Stored Procedures
    16. Stored Procedure Workshop
    17. Looping with a WHILE Statement
  22. Statistical Aggregate Functions
    1. The Stats Table
    2. The VAR and VARP Functions
    3. A VAR Example
    4. A VARP Example
    5. The STDEV and STDEVP Functions
    6. A STDEV Example
    7. A STDEVP Example
  23. Systems Views
    1. System Views
    2. sys.all_columns
    3. sys.all_objects
    4. sys.all_sql_modules
    5. sys.all_views
    6. sys.columns
    7. sys.data_spaces
    8. sys.database_files
    9. sys.database_principals
    10. sys.database_role_members
    11. sys.databases
    12. sys.filegroups
    13. sys.identity_columns
    14. sys.objects
    15. sys.partition_range_values
    16. sys.schemas
    17. sys.server_role_members
    18. sys.sql_logins
  24. Nexus
    1. Nexus is Now Available on the Microsoft Azure Cloud
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.

Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

74,839

Students who have taken Live Online Training

15,233

Organizations who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.41

Students rated our trainers 9.41 out of 10 based on 5,189 reviews

Contact Us or call 1-877-932-8228