MS 20761 : Querying Data with Transact SQL Training Course

Introduction to Microsoft SQL Server 2016

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

Introduction to T-SQL Querying

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

Writing SELECT Queries

• Constructing Basic SELECT Statements
• Removing Duplicates Using DISTINCT
• Applying Column and Table Aliases
• Creating Simple CASE Expressions
• Lab: Writing Basic SELECT Statements

Querying Multiple Tables

• Understanding Join Types
• Executing Queries with Inner Joins
• Executing Queries with Outer Joins
• Executing Queries with Cross and Self Joins
• Lab: Querying Multiple Tables

Sorting and Filtering Data

• Techniques for Sorting Data
• Applying Filters Using Predicates
• Limits and Pagination with TOP and OFFSET-FETCH
• Handling Unknown Values
• Lab: Sorting and Filtering Data

Working with SQL Server 2016 Data Types

• Overview of SQL Server 2016 Data Types
• Handling Character Data
• Handling Date and Time Data
• Lab: Working with SQL Server 2016 Data Types

Using DML to Modify Data

• Inserting Data into Tables
• Updating and Deleting Data
• Generating Automatic Column Values
• Lab: Using DML to Modify Data

Using Built-In Functions

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

Grouping and Aggregating Data

• Applying Aggregate Functions
• Using the GROUP BY Clause
• Filtering Aggregated Groups with HAVING
• Lab: Grouping and Aggregating

Using Subqueries

• Writing Independent Subqueries
• Writing Correlated Subqueries
• Using the EXISTS Predicate in Subqueries
• Lab: Using Subqueries

Using Table Expressions

• Utilizing Views
• Using Inline Table-Valued Functions (TVFs)
• Creating Derived Tables
• Using Common Table Expressions (CTEs)
• Lab: Using Table Expressions

Using Set Operators

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

Using Window Ranking, Offset, and Aggregate Functions

• Defining Windows with OVER
• Exploring Various Window Functions
• Lab: Using Window Ranking, Offset, and Aggregate Functions

Pivoting and Grouping Sets

• Transforming Data with PIVOT and UNPIVOT
• Utilizing Grouping Sets
• Lab: Pivoting and Grouping Sets

Executing Stored Procedures

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

Programming with T-SQL

• Key Elements of T-SQL Programming
• Managing Program Flow
• Lab: Programming with T-SQL

Implementing Error Handling

• Basic T-SQL Error Handling Techniques
• Structured Exception Handling Implementation
• Lab: Implementing Error Handling

Implementing Transactions

• The Role of Transactions in the Database Engine
• Managing Transaction States
• Lab: Implementing Transactions