Querying Data with Transact-SQL (M20761)

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 Set Operators

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

• 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

Lab 1: Working with SQL Server 2016 Tools
Lab 2: Introduction to Transact-SQL Querying
Lab 3: Writing Basic SELECT Statements
Lab 4: Querying Multiple Tables
Lab 5: Sorting and Filtering Data
Lab 6: Working with SQL Server 2016 Data Types
Lab 7: Using DML to Modify Data
Lab 8: Using Built-In Functions
Lab 9: Grouping and Aggregating Data
Lab 10: Using Subqueries
Lab 11: Using Table Expressions
Lab 12: Using SET Operators
Lab 13: Using Windows Ranking, Offset, and Aggregate 
Lab 14: Pivoting and Grouping Sets
Lab 15: Executing Stored 
Lab 16: Programming with T-SQL
Lab 17: Implementing Error Handling
Lab 18: Implementing Transactions

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

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.