20761 - Querying Data with Transact-SQL

• Writing Simple SELECT Statements
• Eliminating Duplicates with DISTINCT
• Using Culumn and Table Aliases
• Writing Simple CASE Expressions
• Lab: Writing Basic SELECT Statements
• Writing Simple SELECT Statements
• Eliminating Duplicates Using DISTINCT
• Using Culumn and Table Aliases
• Using a Simple CASE Expression

Querying Multiple Tables

• Understanding 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

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

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
• Using DML to Modify Data
• Inserting Data
• Modifying and Deleting Data
• Lab: Using DML to Modify Data
• Inserting Data
• Updating and Deleting Data

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

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

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

Using Table Expressions

• Using Views
• Using Inline Table-Valued Functions
• Using Derived Tables
• Using Common Table Expressions
• 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

Using Set Operators

• Writing Queries with the UNION operator
• Using EXCEPT and INTERSECT
• Using APPLY
• 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

Using Windows Ranking, Offset, and Aggregate 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

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

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

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

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

Implementing Transactions

• Transactions and the database engines
• Controling transactions
• Lab: Implementing Transactions
• Controlling transactions with BEGIN, COMMIT, and ROLLBACK
• Adding error handling to a CATCH block