MS 20761 : Querying Data with Transact SQL Training Course

Introduction to Microsoft SQL Server 2016

• Foundational Architecture of SQL Server
• Different SQL Server Editions and Versions
• Getting Started with SQL Server Management Studio
• Practical Exercise: Utilising SQL Server 2016 Tools

Introduction to T-SQL Querying

• Overview of T-SQL
• Concepts of Sets
• Principles of Predicate Logic
• Logical Sequence of Operations in SELECT Statements
• Practical Exercise: Basics of T-SQL Querying

Composing SELECT Queries

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

Querying Multiple Tables

• Concepts of Joins
• Executing Queries with Inner Joins
• Executing Queries with Outer Joins
• Executing Queries with Cross and Self Joins
• Practical Exercise: Querying Across Multiple Tables

Sorting and Filtering Data

• Techniques for Sorting Data
• Filtering Data Using Predicates
• Filtering Data with TOP and OFFSET-FETCH
• Handling Unknown Values
• Practical Exercise: 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
• Practical Exercise: Manipulating SQL Server 2016 Data Types

Modifying Data with DML

• Inserting Data into Tables
• Updating and Deleting Data
• Generating Automatic Column Values
• Practical Exercise: Modifying Data via DML

Leveraging Built-In Functions

• Writing Queries with Built-In Functions
• Applying Conversion Functions
• Utilising Logical Functions
• Using Functions to Handle NULLs
• Practical Exercise: Applying Built-In Functions

Grouping and Aggregating Data

• Applying Aggregate Functions
• Utilising the GROUP BY Clause
• Filtering Groups with HAVING
• Practical Exercise: Grouping and Aggregation

Utilising Subqueries

• Creating Self-Contained Subqueries
• Constructing Correlated Subqueries
• Using the EXISTS Predicate with Subqueries
• Practical Exercise: Implementing Subqueries

Using Table Expressions

• Utilising Views
• Applying Inline TVFs
• Using Derived Tables
• Employing CTEs
• Practical Exercise: Implementing Table Expressions

Applying Set Operators

• Writing Queries with the UNION Operator
• Using EXCEPT and INTERSECT
• Applying APPLY
• Practical Exercise: Using Set Operators

Window Ranking, Offset, and Aggregate Functions

• Creating Windows with OVER
• Exploring Window Functions
• Practical Exercise: Window Ranking, Offset, and Aggregate Functions

Pivoting and Grouping Sets

• Writing Queries with PIVOT and UNPIVOT
• Managing Grouping Sets
• Practical Exercise: Pivoting and Grouping Sets

Executing Stored Procedures

• Querying Data via Stored Procedures
• Passing Parameters to Stored Procedures
• Creating Simple Stored Procedures
•  Working with Dynamic SQL
• Practical Exercise: Running Stored Procedures

Programming with T-SQL

• T-SQL Programming Components
• Managing Program Flow
• Practical Exercise: T-SQL Programming

Implementing Error Handling

• Setting Up T-SQL Error Handling
• Implementing Structured Exception Handling
• Practical Exercise: Error Handling Implementation

Implementing Transactions

• Transactions and the Database Engine
• Managing Transactions
• Practical Exercise: Transaction Implementation