20761: Querying Data with Transact-SQL

This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first three days can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum. Days 4 & 5 teach the remaining skills required to take exam 70-761.

Students should have a working knowledge of relational databases and a basic knowledge of the Microsoft Windows operating system and its core functionality.

• Describe key capabilities and components of SQL Server.
• Describe T-SQL, sets, and predicate logic.
• Write a single table SELECT statement.
• Write a multi-table SELECT statement.
• Write SELECT statements with filtering and sorting.
• Describe how SQL Server uses data types.
• Write DML statements.
• Write queries that use built-in functions.
• Write queries that aggregate data.
• Write subqueries.
• Create and implement views and table-valued functions.
• Use set operators to combine query results.
• Write queries that use window ranking, offset, and aggregate functions.
• Transform data by implementing pivot, unpivot, rollup and cube.
• Create and implement stored procedures.
• Add programming constructs such as variables, conditions, and loops to T-SQL code

The main purpose of the course is to give students a good understanding of the Transact-SQL language which is used by all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.

1. Introduction to Microsoft SQL

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

2. Introduction to T-SQL Querying

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

3. Writing SELECT Queries

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

4. Querying Multiple Tables

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

5. Sorting and Filtering Data

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

6. Working with SQL Server 2016 Data Types

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

7. Using DML to Modify Data

• Inserting Data
• Modifying and Deleting Data

8. Using Built-In Functions

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

9. Grouping and Aggregating Data

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

10. Using Subqueries

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

11. Using Table Expressions

• Using Views
• Using Inline TVFs
• Using Derived Tables
• Using CTEs

12. Using Set

• Writing Queries with the UNION Operator
• Using EXCEPT and INTERSECT
• Using APPLY

13. Using Windows Ranking, Offset, and Aggregate 

• Creating Windows with OVER
• Exploring Window Functions

14. Pivoting and Grouping Sets

• Writing Queries with PIVOT and UNPIVOT
• Working with Grouping Sets

15. Executing Stored 

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

16. Programming with T-SQL

• T-SQL Programming Elements
• Controlling Program Flow

17. Implementing Error Handling

• Implementing T-SQL Error Handling
• Implementing Structured Exception Handling

18. Implementing Transactions

• Transactions and the Database Engines
• Controlling Transactions