Retired: Querying Data With Transact-SQL M20761 - Australia

Retired: Querying Data with Transact-SQL Outline

Module 1: Introduction to Microsoft SQL Server 2016

• The Basic Architecture of SQL Server

• SQL Server Editions and Versions

• Getting Started with SQL Server Management Studio

• Lab: Working with SQL Server 2016 Tools

  • Working with SQL Server Management Studio

  • Creating and Organizing T-SQL Scripts

  • Using Books Online

Module 2: Introduction to T-SQL Querying

• Introducing T-SQL

• Understanding Sets

• Writing Queries

• Understanding Predicate Logic

• Understanding the Logical Order of Operations in SELECT statements

• Lab: Introduction to T-SQL Querying

  • Executing Basic SELECT Statements

  • Executing Queries that Filter Data using Predicates

  • Executing Queries That Sort Data Using ORDER BY

Module 3: Writing SELECT Queries

• Writing Simple SELECT Statements

• Eliminating Duplicates with DISTINCT

• Using Column and Table Aliases

• Writing Simple CASE Expressions

• Lab: Writing Basic SELECT Statements

  • Writing Simple SELECT Statements

  • Eliminating Duplicates Using DISTINCT

  • Using Column and Table Aliases

  • Using a Simple CASE Expression

Module 4: Querying Multiple Tables

• Understanding the Fundamentals of Joins

• Querying with Inner Joins

• Querying with Outer Joins

• Querying with Cross Joins and Self Joins

• Lab: Querying Multiple Tables

  • Writing Queries that use Inner Joins

  • Writing Queries that use Multiple-Table Inner Joins

  • Writing Queries that use Self-Joins

  • Writing Queries that use Outer Joins

  • Writing Queries that use Cross Joins

Module 5: Sorting and Filtering Data

• Sorting Data

• Filtering Data with Predicates

• Filtering Data with TOP and OFFSET-FETCH

• Working with Unknown Values

• Lab: Sorting and Filtering Data

  • Writing Queries that Filter Data using a WHERE Clause

  • Writing Queries that Sort Data Using an ORDER BY Clause

  • Writing Queries that Filter Data Using the TOP Option

Module 6: Working with SQL Server 2016 Data Types

• Introducing SQL Server 2016 Data Types

• Working with Character Data

• Working with Date and Time Data

• Lab: Working with SQL Server 2016 Data Types

  • Writing Queries that Return Date and Time Data

  • Writing Queries that use Date and Time Functions

  • Writing Queries That Return Character Data

  • Writing Queries That Return Character Functions

Module 7: Using DML to Modify Data

• Inserting Data

• Creating DML Queries

• Modifying and Deleting Data

• Lab: Using DML to Modify Data

  • Inserting Data

  • Updating, Merging, and Deleting Data

Module 8: Using Built-In Functions

• Writing Queries with Built-In Functions

• Using Conversion Functions

• Using Logical Functions

• Using Functions to Work with NULL

• Lab: Using Built-In Functions

  • Writing Queries That Use Conversion Functions

  • Writing Queries that use Logical Functions

  • Writing Queries that Test for Nullability

Module 9: Grouping and Aggregating Data

• Using Aggregate Functions

• Using the GROUP BY Clause

• Filtering Groups with HAVING

• Lab: Grouping and Aggregating Data

  • Writing Queries That Use the GROUP BY Clause

  • Writing Queries that Use Aggregate Functions

  • Writing Queries that Use Distinct Aggregate Functions

  • Writing Queries that Filter Groups with the HAVING Clause

Module 10: Using Subqueries

• Writing Self-Contained Subqueries

• Writing Correlated Subqueries

• Using the EXISTS Predicate with Subqueries

• Lab: Using Subqueries

  • Writing Queries That Use Self-Contained Subqueries

  • Writing Queries That Use Scalar and Multi-Result Subqueries

  • Writing Queries That Use Correlated Subqueries and an EXISTS Clause

Module 11: Using Table Expressions

• Using Views

• Using Inline Table-Valued Functions (TVFs)

• Using Derived Tables

• Using Common Table Expressions (CTEs)

• Lab: Using Table Expressions

  • Writing Queries That Use Views

  • Writing Queries That Use Derived Tables

  • Writing Queries That Use Common Table Expressions (CTEs)

  • Writing Queries That Sue Inline Table-Valued Expressions

Module 12: Using Set Operators

• Writing Queries with the UNION operator

• Using EXCEPT and INTERSECT

• Using APPLY

• Comparing Rows Between Two Input Sets

• Lab: Using Set Operators

  • Writing Queries That Use UNION Set Operators and UNION ALL

  • Writing Queries That Use CROSS APPLY and OUTER APPLY Operators

  • Writing Queries That Use the EXCEPT and INTERSECT Operators

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

• Benefits of Window Functions

• Restricting Window Functions

• Creating Windows with OVER

• Exploring Window Functions

• Lab: Using Windows Ranking, Offset, and Aggregate Functions

  • Writing Queries that use Ranking Functions

  • Writing Queries that use Offset Functions

  • Writing Queries that use Window Aggregate Functions

Module 14: Pivoting and Grouping Sets

• Writing Queries with PIVOT and UNPIVOT

• Working with Grouping Sets

• Lab: Pivoting and Grouping Sets

  • Writing Queries that use the PIVOT Operator

  • Writing Queries that use the UNPIVOT Operator

  • Writing Queries that use the GROUPING SETS CUBE and ROLLUP Subclauses

Module 15: Executing Stored Procedures

• Querying Data with Stored Procedures

• Passing Parameters to Stored procedures

• Creating Simple Stored Procedures

• Working with Dynamic SQL

• Lab: Executing Stored Procedures

  • Using the EXECUTE statement to Invoke Stored Procedures

  • Passing Parameters to Stored procedures

  • Executing System Stored Procedures

Module 16: Programming with T-SQL

• T-SQL Programming Elements

• Controlling Program Flow

• Lab: Programming with T-SQL

  • Declaring Variables and Delimiting Batches

  • Using Control-Of-Flow Elements

  • Using Variables in a Dynamic SQL Statement

  • Using Synonyms

Module 17: Implementing Error Handling

• Implementing T-SQL error handling

• Implementing structured exception handling

• Lab: Implementing Error Handling

  • Redirecting errors with TRY/CATCH

  • Using THROW to pass an error message back to a client

Module 18: Implementing Transactions

• Transactions and the Database Engines

• Controlling Transactions

• Lab: Implementing Transactions

  • Controlling transactions with BEGIN, COMMIT, and ROLLBACK

  • Adding error handling to a CATCH block