MS 20761 : Querying Data with Transact SQL Training Course

Introduction to Microsoft SQL Server 2016

• Overview of SQL Server Basic Architecture
• Comparison of SQL Server Editions and Versions
• Getting Started with SQL Server Management Studio
• Practical Lab: Navigating SQL Server 2016 Tools

Introduction to T-SQL Querying

• Fundamentals of T-SQL
• Concepts of Sets in Data Querying
• Application of Predicate Logic
• Logical Order of Operations in SELECT Statements
• Practical Lab: Basics of T-SQL Querying

Writing SELECT Queries

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

Querying Multiple Tables

• Understanding the Concept of Joins
• Performing Queries with Inner Joins
• Performing Queries with Outer Joins
• Advanced Joins: Cross and Self Joins
• Practical Lab: Multi-Table Querying Techniques

Sorting and Filtering Data

• Techniques for Sorting Data
• Applying Predicates for Data Filtering
• Limited Data Retrieval using TOP and OFFSET-FETCH
• Handling Unknown Values (NULLs)
• Practical Lab: Data Sorting and Filtering Exercises

Working with SQL Server 2016 Data Types

• Overview of SQL Server 2016 Data Types
• Management of Character Data
• Handling Date and Time Data
• Practical Lab: Practical Applications of Data Types

Using DML to Modify Data

• Inserting New Data into Tables
• Updating and Deleting Existing Data
• Automatic Generation of Column Values
• Practical Lab: Data Modification via DML

Using Built-In Functions

• Integrating Built-In Functions into Queries
• Application of Conversion Functions
• Use of Logical Functions
• Managing NULL Values with Functions
• Practical Lab: Leveraging Built-In Functions

Grouping and Aggregating Data

• Utilizing Aggregate Functions
• Segmenting Data with the GROUP BY Clause
• Filtering Aggregated Results with HAVING
• Practical Lab: Data Grouping and Aggregation

Using Subqueries

• Constructing Independent Subqueries
• Creating Correlated Subqueries
• Implementing the EXISTS Predicate
• Practical Lab: Advanced Subquery Usage

Using Table Expressions

• Defining and Using Views
• Application of Inline TVFs (Table-Valued Functions)
• Constructing Derived Tables
• Implementation of CTEs (Common Table Expressions)
• Practical Lab: Working with Table Expressions

Using Set Operators

• Combining Result Sets with the UNION Operator
• Differences and Intersections using EXCEPT and INTERSECT
• Utilizing the APPLY Operator
• Practical Lab: Set Operator Applications

Using Window Ranking, Offset, and Aggregate Functions

• Defining Windows with the OVER Clause
• In-depth Exploration of Window Functions
• Practical Lab: Window Function Implementations

Pivoting and Grouping Sets

• Data Transformation with PIVOT and UNPIVOT
• Complex Grouping with Grouping Sets
• Practical Lab: Pivoting and Grouping Set Exercises

Executing Stored Procedures

• Data Retrieval via Stored Procedures
• Parameter Passing Techniques
• Designing Simple Stored Procedures
• Management of Dynamic SQL
• Practical Lab: Stored Procedure Execution

Programming with T-SQL

• Core T-SQL Programming Constructs
• Flow Control Mechanisms
• Practical Lab: T-SQL Programming Basics

Implementing Error Handling

• Basics of T-SQL Error Management
• Structured Exception Handling Techniques
• Practical Lab: Error Handling Scenarios

Implementing Transactions

• Understanding Transactions in the Database Engine
• Control and Management of Transactions
• Practical Lab: Transaction Implementation