MS 20761 : Querying Data with Transact SQL Training Course

Introduction to Microsoft SQL Server 2016

• Overview of the Basic SQL Server Architecture
• Differences Between SQL Server Editions and Versions
• Getting Started with SQL Server Management Studio
• Lab: Practical Work with SQL Server 2016 Tools

Introduction to T-SQL Querying

• An Overview of T-SQL
• Concepts of Sets
• Understanding Predicate Logic
• The Logical Order of Operations within SELECT Statements
• Lab: Basics of T-SQL Querying

Writing SELECT Queries

• Constructing Simple SELECT Statements
• Removing Duplicate Records with DISTINCT
• Applying Column and Table Aliases
• Creating Simple CASE Expressions
• Lab: Drafting Basic SELECT Statements

Querying Multiple Tables

• Concepts Behind Table Joins
• Performing Queries with Inner Joins
• Performing Queries with Outer Joins
• Executing Cross Joins and Self Joins
• Lab: Retrieving Data from Multiple Tables

Sorting and Filtering Data

• Techniques for Sorting Data
• Filtering Records Using Predicates
• Limits and Pagination with TOP and OFFSET-FETCH
• Handling Unknown Values (NULLs)
• Lab: Exercises in 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 Information
• Lab: Practical Application of Data Types

Modifying Data Using DML

• Inserting New Records into Tables
• Updating and Deleting Existing Data
• Generating Automatic Column Values
• Lab: Modifying Data with DML Commands

Leveraging Built-In Functions

• Incorporating Built-In Functions into Queries
• Applying Conversion Functions
• Using Logical Functions
• Managing NULL Values with Functions
• Lab: Utilizing Built-in Functions

Grouping and Aggregating Data

• Applying Aggregate Functions
• Structuring Queries with the GROUP BY Clause
• Filtering Aggregated Results with HAVING
• Lab: Grouping and Aggregation Exercises

Utilizing Subqueries

• Creating Standalone Subqueries
• Constructing Correlated Subqueries
• Using the EXISTS Predicate in Subqueries
• Lab: Advanced Subquery Techniques

Applying Table Expressions

• Working with Views
• Implementing Inline Table-Valued Functions (TVFs)
• Using Derived Tables
• Employing Common Table Expressions (CTEs)
• Lab: Practical Table Expressions

Using Set Operators

• Combining Results with the UNION Operator
• Differencing and Intersecting with EXCEPT and INTERSECT
• Implementing the APPLY Operator
• Lab: Set Operator Applications

Window Ranking, Offset, and Aggregate Functions

• Defining Windows using the OVER Clause
• Deep Dive into Window Functions
• Lab: Window Function Practicalities

Pivoting and Grouping Sets

• Reshaping Data with PIVOT and UNPIVOT
• Advanced Grouping with Grouping Sets
• Lab: Pivoting and Grouping Set Exercises

Executing Stored Procedures

• Retrieving Data via Stored Procedures
• Handling Parameters in Stored Procedures
• Developing Basic Stored Procedures
• Managing Dynamic SQL
• Lab: Stored Procedure Implementation

Programming with T-SQL

• Core Elements of T-SQL Programming
• Managing Program Flow Control
• Lab: T-SQL Programming Tasks

Implementing Error Handling

• Basic T-SQL Error Management
• Advanced Structured Exception Handling
• Lab: Error Handling Scenarios

Implementing Transactions

• The Role of Transactions in the Database Engine
• Controlling Transaction Behaviors
• Lab: Transaction Implementation