MS 20761 : Querying Data with Transact SQL Training Course

Introduction to Microsoft SQL Server 2016

• Foundations of SQL Server architecture
• Comparison of SQL Server editions and versions
• Initial setup and navigation in SQL Server Management Studio
• Practical lab: Utilizing SQL Server 2016 tools

Introduction to T-SQL Querying

• Overview of T-SQL capabilities
• Concepts of set-based operations
• Logic of predicates
• Logical evaluation order in SELECT statements
• Practical lab: Fundamentals of T-SQL querying

Writing SELECT Queries

• Constructing basic SELECT statements
• Removing duplicate rows using DISTINCT
• Applying column and table aliases
• Creating simple CASE expressions
• Practical lab: Building basic SELECT statements

Querying Multiple Tables

• Concepts of table joins
• Executing queries with inner joins
• Executing queries with outer joins
• Using cross joins and self-joins
• Practical lab: Joining multiple tables

Sorting and Filtering Data

• Techniques for ordering results
• Applying predicates for data filtering
• Leveraging TOP and OFFSET-FETCH for pagination
• Handling NULL and unknown values
• Practical lab: Sorting and filtering datasets

Working with SQL Server 2016 Data Types

• Overview of SQL Server 2016 data types
• Managing character-based data
• Handling date and time information
• Practical lab: Operations with SQL Server 2016 data types

Using DML to Modify Data

• Inserting new records into tables
• Updating and deleting existing data
• Automating column value generation
• Practical lab: Applying DML statements for data modification

Using Built-In Functions

• Incorporating built-in functions into queries
• Applying conversion functions
• Utilizing logical functions
• Managing NULL values with specific functions
• Practical lab: Implementing built-in functions

Grouping and Aggregating Data

• Applying aggregate functions
• Using the GROUP BY clause
• Filtering aggregated groups with HAVING
• Practical lab: Grouping and aggregation tasks

Using Subqueries

• Constructing independent subqueries
• Building correlated subqueries
• Utilizing the EXISTS predicate
• Practical lab: Working with subqueries

Using Table Expressions

• Creating and using views
• Leveraging inline TVFs
• Working with derived tables
• Using CTEs
• Practical lab: Implementing table expressions

Using Set Operators

• Combining results with the UNION operator
• Applying EXCEPT and INTERSECT operators
• Using the APPLY operator
• Practical lab: Utilizing set operators

Using Window Ranking, Offset, and Aggregate Functions

• Defining windows using OVER
• Exploring various window functions
• Practical lab: Advanced window function applications

Pivoting and Grouping Sets

• Executing PIVOT and UNPIVOT operations
• Managing complex grouping sets
• Practical lab: Pivoting and grouping set exercises

Executing Stored Procedures

• Retrieving data via stored procedures
• Passing parameters to procedures
• Developing simple stored procedures
• Handling dynamic SQL
• Practical lab: Running stored procedures

Programming with T-SQL

• Core elements of T-SQL programming
• Managing program control flow
• Practical lab: T-SQL programming tasks

Implementing Error Handling

• Basic T-SQL error management
• Structured exception handling techniques
• Practical lab: Implementing robust error handling

Implementing Transactions

• Transaction concepts within the database engine
• Controlling transaction lifecycle
• Practical lab: Managing database transactions