Querying Data with Transact-SQL

Introduction to Microsoft SQL Server

• The Basic Architecture of SQL Server

• SQL Server Editions and Versions

• Getting Started with SQL Server Management Studio

• Lab: Working with SQL Server Tools

• Lab: Working with SQL Server Management Studio

• Lab: Creating and Organizing T-SQL Scripts

• Lab: Using Books Online

Introduction to T-SQL Querying

• Introducing T-SQL

• Understanding Sets

• Understanding Predicate Logic

• Understanding the Logical Order of Operations in SELECT statements

• Lab: Introduction to T-SQL Querying

• Lab: Executing Basic SELECT Statements

• Lab: Executing Queries that Filter Data using Predicates

• Lab: Executing Queries That Sort Data Using ORDER BY

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

• Lab: Writing Simple SELECT Statements

• Lab: Eliminating Duplicates Using DISTINCT

• Lab: Using Column and Table Aliases

• Lab: 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

• Lab: Writing Queries that use Inner Joins

• Lab: Writing Queries that use Multiple-Table Inner Joins

• Lab: Writing Queries that use Self-Joins

• Lab: Writing Queries that use Outer Joins

• Lab: 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

• Lab: Writing Queries that Filter Data using a WHERE Clause

• Lab: Writing Queries that Sort Data Using an ORDER BY Clause

• Lab: Writing Queries that Filter Data Using the TOP Option

• Lab: Write Queries that filter data using the OFFSET-FETCH clause

Working with SQL Server Data Types

• Introducing SQL Server Data Types

• Working with Character Data

• Working with Date and Time Data

• Lab: Working with SQL Server Data Types

• Lab: Writing Queries that Return Date and Time Data

• Lab: Writing Queries that use Date and Time Functions

• Lab: Writing Queries That Return Character Data

• Lab: Writing Queries That Return Character Functions

Using DML to Modify Data

• Adding Data to Tables

• Modifying and Removing Data

• Generating automatic column values

• Lab: Using DML to Modify Data

• Lab: Inserting Records with DML

• Lab: Updating and Deleting Records Using DML

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

• Lab: Writing Queries That Use Conversion Functions

• Lab: Writing Queries that use Logical Functions

• Lab: 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

• Lab: Writing Queries That Use the GROUP BY Clause

• Lab: Writing Queries that Use Aggregate Functions

• Lab: Writing Queries that Use Distinct Aggregate Functions

• Lab: 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

• Lab: Writing Queries That Use Self-Contained Subqueries

• Lab: Writing Queries That Use Scalar and Multi-Result Subqueries

• Lab: Writing Queries That Use Correlated Subqueries and an EXISTSClause

Using Table Expressions

• Using Views

• Using Inline Table-Valued Functions

• Using Derived Tables

• Using Common Table Expressions

• Lab: Using Table Expressions

• Lab: Writing Queries That Use Views

• Lab: Writing Queries That Use Derived Tables

• Lab: Writing Queries That Use Common Table Expressions (CTEs)

• Lab: Writing Queries That Use Inline Table-Valued Expressions (TVFs)

Using Set Operators

• Writing Queries with the UNION operator

• Using EXCEPT and INTERSECT

• Using APPLY

• Lab: Using Set Operators

• Lab: Writing Queries That Use UNION Set Operators and UNION ALL

• Lab: Writing Queries That Use CROSS APPLY and OUTER APPLYOperators

• Lab: 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

• Lab: Writing Queries that use Ranking Functions

• Lab: Writing Queries that use Offset Functions

• Lab: 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

• Lab: Writing Queries that use the PIVOT Operator

• Lab: Writing Queries that use the UNPIVOT Operator

• Lab: Writing Queries that use the GROUPING SETS CUBE and ROLLUPSubclauses

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

• Lab: Using the EXECUTE statement to Invoke Stored Procedures

• Lab: Passing Parameters to Stored procedures

• Lab: Executing System Stored Procedures

Programming with T-SQL

• T-SQL Programming Elements

• Controlling Program Flow

• Lab: Programming with T-SQL

• Lab: Declaring Variables and Delimiting Batches

• Lab: Using Control-Of-Flow Elements

• Lab: Using Variables in a Dynamic SQL Statement

• Lab: Using Synonyms

Implementing Error Handling

• Implementing T-SQL error handling

• Implementing structured exception handling

• Lab: Implementing Error Handling

• Lab: Redirecting errors with TRY/CATCH

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

Implementing Transactions

• Transactions and the database engines

• Controlling transactions

• Lab: Implementing Transactions

• Lab: Controlling transactions with BEGIN, COMMIT, and ROLLBACK

• Lab: Adding error handling to a CATCH block