MS 20761 : Querying Data with Transact SQL

• Basic knowledge of relational databases.

The SQL 2016 Querying course 20761 aims to impart skills on writing queries for Transact-SQL language. This SQL 2016 Querying certification training is the basic foundation for SQL Server-related disciplines such as Database Administration, Business Intelligence and Database Development. The course teaches participants how to select, sort and filter data from multiple tables and how to use stored procedures and views.

The primary audience for this T-SQL training is database administrators, business intelligence professionals, database developers etc. This T-SQL certification course can also be attended by non-technical power users such as business analysts, report writers, and client application developers.

 Module 1: Introduction to Microsoft SQL Server 2016

• Lesson 1: The Basic Architecture of SQL Server
• Lesson 2: SQL Server Editions and Versions
• Lesson 3: Getting Started with SQL Server Management Studio
• Lab: Working with SQL Server 2016 Tools

 Module 2: Introduction to T-SQL Querying

• Lesson 1: Introducing T-SQL
• Lesson 2: Understanding Sets
• Lesson 3: Understanding Predicate Logic
• Lesson 4: Understanding the Logical Order of Operations in SELECT Statements
• Lab: Introduction to T-SQL Querying

 Module 3: Writing SELECT Queries

• Lesson 1: Writing Simple SELECT Statements
• Lesson 2: Eliminating Duplicates with DISTINCT
• Lesson 3: Using Column and Table Aliases
• Lesson 4: Writing Simple CASE Expressions
• Lab: Writing Basic SELECT Statements

 Module 4: Querying Multiple Tables

• Lesson 1: Understanding Joins
• Lesson 2: Querying with Inner Joins
• Lesson 3: Querying with Outer Joins
• Lesson 4: Querying with Cross Joins and Self Joins
• Lab: Querying Multiple Tables

 Module 5: Sorting and Filtering Data

• Lesson 1: Sorting Data
• Lesson 2: Filtering Data with Predicates
• Lesson 3: Filtering Data with TOP and OFFSET-FETCH
• Lesson 4: Working with Unknown Values
• Lab: Sorting and Filtering Data

 Module 6: Working with SQL Server 2016 Data Types

• Lesson 1: Introducing SQL Server 2016 Data Types
• Lesson 2: Working with Character Data
• Lesson 3: Working with Date and Time Data
• Lab: Working with SQL Server 2016 Data Types

 Module 7: Using DML to Modify Data

• Lesson 1: Adding Data to Tables
• Lesson 2: Modifying and Removing Data
• Lesson 3: Generating Automatic Column Values
• Lab: Using DML to Modify Data

 Module 8: Using Built-In Functions

• Lesson 1: Writing Queries with Built-In Functions
• Lesson 2: Using Conversion Functions
• Lesson 3: Using Logical Functions
• Lesson 4: Using Functions to Work with NULL
• Lab: Using Built-in Functions

 Module 9: Grouping and Aggregating Data

• Lesson 1: Using Aggregate Functions
• Lesson 2: Using the GROUP BY Clause
• Lesson 3: Filtering Groups with HAVING
• Lab: Grouping and Aggregating

 Module 10: Using Subqueries

• Lesson 1: Writing Self-Contained Subqueries
• Lesson 2: Writing Correlated Subqueries
• Lesson 3: Using the EXISTS Predicate with Subqueries
• Lab: Using Subqueries

 Module 11: Using Table Expressions

• Lesson 1: Using Views
• Lesson 2: Using Inline TVFs
• Lesson 3: Using Derived Tables
• Lesson 4: Using CTEs
• Lab: Using Table Expressions

 Module 12: Using Set Operators

• Lesson 1: Writing Queries with the UNION Operator
• Lesson 2: Using EXCEPT and INTERSECT
• Lesson 3: Using APPLY
• Lab: Using Set Operators

 Module 13: Using Window Ranking, Offset, and Aggregate Functions

• Lesson 1: Creating Windows with OVER
• Lesson 2: Exploring Window Functions
• Lab: Using Window Ranking, Offset, and Aggregate Functions

 Module 14: Pivoting and Grouping Sets

• Lesson 1: Writing Queries with PIVOT and UNPIVOT
• Lesson 2: Working with Grouping Sets
• Lab: Pivoting and Grouping Sets

 Module 15: Executing Stored Procedures

• Lesson 1: Querying Data with Stored Procedures
• Lesson 2: Passing Parameters to Stored Procedures
• Lesson 3: Creating Simple Stored Procedures
• Lesson 4: Working with Dynamic SQL
• Lab: Executing Stored Procedures

 Module 16: Programming with T-SQL

• Lesson 1: T-SQL Programming Elements
• Lesson 2: Controlling Program Flow
• Lab: Programming with T-SQL

 Module 17: Implementing Error Handling

• Lesson 1: Implementing T-SQL Error Handling
• Lesson 2: Implementing Structured Exception Handling
• Lab: Implementing Error Handling

 Module 18: Implementing Transactions

• Lesson 1: Transactions and the Database Engine
• Lesson 2: Controlling Transactions
• Lab: Implementing Transactions