Knowledg

Querying Data With Transact-SQL

You will learn key capabilities of SQL Server and T-SQL, and prepare to pass exam 70-761 in Querying Data with Transact-SQL. Learn T-SQL sets, SELECT statements, and how to write queries and subqueries. You will also learn how to transform data, create stored procedures, and add programming constructs. This 5 day instructor led course is designed to introduce you to Transact-SQL. It is designed so that the first three days teach you the knowledge for other courses in the SQL Server curriculum, while days 4 & 5 prepare you for the skills required to take exam 70-761.

Module 1: Introduction to Microsoft SQL Server

The Basic Architecture of SQL Server SQL Server Editions and Versions Getting Started with SQL Server Management Studio

Module 2: Introduction to T-SQL Querying

Introducing T-SQL Understanding Sets Understanding Predicate Logic Understanding the Logical Order of Operations in SELECT statements

Module 3: Writing SELECT Queries

Writing Simple SELECT Statements Eliminating Duplicates with DISTINCT Using Column and Table Aliases Writing Simple CASE Expressions

Module 4: Querying Multiple Tables

Understanding Joins Querying with Inner Joins Querying with Outer Joins Querying with Cross Joins and Self Joins

Module 5: Sorting and Filtering Data

Sorting Data Filtering Data with Predicates Filtering Data with TOP and OFFSET-FETCH Working with Unknown Values

Module 6: Working with SQL Server Data Types

Introducing SQL Server Data Types Working with Character Data Working with Date and Time Data

Module 7: Using DML to Modify Data

Adding Data to Tables Modifying and Removing Data Generating automatic column values

Module 8: Using Built-In Functions

Writing Queries with Built-In Functions Using Conversion Functions Using Logical Functions Using Functions to Work with NULL

Module 9: Grouping and Aggregating Data

Using Aggregate Functions Using the GROUP BY Clause Filtering Groups with HAVING

Module 10: Using Subqueries

Writing Self-Contained Subqueries Writing Correlated Subqueries Using the EXISTS Predicate with Subqueries

Module 11: Using Table Expressions

Using Views Using Inline Table-Valued Functions Using Derived Tables Using Common Table Expressions

Module 12: Using Set Operators

Writing Queries with the UNION operator Using EXCEPT and INTERSECT Using APPLY

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

Creating Windows with OVER Exploring Window Functions

Module 14: Pivoting and Grouping Sets

Writing Queries with PIVOT and UNPIVOT Working with Grouping Sets

Module 15: Executing Stored Procedures

Querying Data with Stored Procedures Passing Parameters to Stored procedures Creating Simple Stored Procedures Working with Dynamic SQL

Module 16: Programming with T-SQL

T-SQL Programming Elements Controlling Program Flow

Module 17: Implementing Error Handling

Implementing T-SQL error handling Implementing structured exception handling

Module 18: Implementing Transactions

Transactions and the database engines Controlling transactions