MS 20761 : Querying Data with Transact SQL Training Course

Introduction to Microsoft SQL Server 2016

• Overview of SQL Server Architecture
• SQL Server Editions and Versioning
• Initial Setup with SQL Server Management Studio
• Lab: Utilizing SQL Server 2016 Tools

Introduction to T-SQL Querying

• Overview of T-SQL
• Concepts of Sets in Databases
• Principles of Predicate Logic
• Logical Order of Operations in SELECT Statements
• Lab: Fundamentals of T-SQL Querying

Writing SELECT Queries

• Constructing Simple SELECT Statements
• Removing Duplicates Using DISTINCT
• Implementing Column and Table Aliases
• Developing Simple CASE Expressions
• Lab: Crafting Basic SELECT Statements

Querying Multiple Tables

• Understanding Database Joins
• Executing Queries with Inner Joins
• Executing Queries with Outer Joins
• Using Cross Joins and Self Joins
• Lab: Advanced Multi-Table Querying

Sorting and Filtering Data

• Techniques for Sorting Data
• Applying Predicates to Filter Data
• Utilizing TOP and OFFSET-FETCH for Filtering
• Handling Unknown (NULL) Values
• Lab: Practical Sorting and Filtering

Working with SQL Server 2016 Data Types

• Overview of SQL Server 2016 Data Types
• Managing Character-Based Data
• Handling Date and Time Data
• Lab: Application of SQL Server 2016 Data Types

Modifying Data with DML

• Inserting New Data into Tables
• Updating and Deleting Existing Data
• Generating Automatic Column Values
• Lab: Data Modification Using DML

Using Built-In Functions

• Incorporating Built-In Functions in Queries
• Applying Conversion Functions
• Using Logical Functions
• Handling NULL Values with Specific Functions
• Lab: Practical Use of Built-In Functions

Grouping and Aggregating Data

• Application of Aggregate Functions
• Using the GROUP BY Clause
• Filtering Groups Using HAVING
• Lab: Grouping and Aggregation Exercises

Using Subqueries

• Creating Self-Contained Subqueries
• Developing Correlated Subqueries
• Utilizing the EXISTS Predicate with Subqueries
• Lab: Subquery Implementation

Using Table Expressions

• Creating and Using Views
• Utilizing Inline Table-Valued Functions (TVFs)
• Working with Derived Tables
• Implementing Common Table Expressions (CTEs)
• Lab: Table Expression Workflows

Using Set Operators

• Combining Queries with the UNION Operator
• Applying EXCEPT and INTERSECT Operators
• Using the APPLY Operator
• Lab: Set Operator Applications

Using Window Ranking, Offset, and Aggregate Functions

• Defining Windows Using OVER
• Exploring Window Function Capabilities
• Lab: Window Ranking and Aggregation

Pivoting and Grouping Sets

• Transforming Data with PIVOT and UNPIVOT
• Managing Grouping Sets
• Lab: Pivoting and Grouping Set Exercises

Executing Stored Procedures

• Retrieving Data via Stored Procedures
• Passing Parameters to Stored Procedures
• Developing Basic Stored Procedures
• Handling Dynamic SQL
• Lab: Stored Procedure Execution

Programming with T-SQL

• Core T-SQL Programming Constructs
• Managing Program Flow Control
• Lab: T-SQL Programming Practice

Implementing Error Handling

• Establishing T-SQL Error Handling Mechanisms
• Applying Structured Exception Handling
• Lab: Error Handling Implementation

Implementing Transactions

• Understanding Transactions in the Database Engine
• Controlling Transaction Behavior
• Lab: Transaction Management