Querying with T-SQL in SQL Server

Prerequisites

None. Any previous knowledge of any version of SQL Server would be useful but not required.

Designing and Creating a Database

Relational Database Design Principles
The Origins of Relational Design
Data Normalization
Understanding the Referential Integrity
Beyond Normalization
Implementing the Design
Database Storage
Creating Databases
Modifying Database Options
Creating Tables
Creating Constraints
Triggers
Creating Indexes
Using Database Diagrams

Data Selection Queries

Understanding Transact-SQL
Schemas and Naming in SQL Server
The SELECT Statement
Selecting All Columns
Concatenating Columns
Naming Columns
Using DISTINCT to Limit Values
The WHERE Clause
Transact-SQL Comparison Operators
The LIKE Operator
The BETWEEN Operator
Using IS NULL to Test for Nulls
Multiple Conditions with AND, OR, and NOT
Operator Precedence
Using the IN Operator
Using ORDER BY to Sort Data
Sorting on a Single Column
Sorting by Multiple Columns
Sorting with Expression
The GROUP BY Clause
Aggregate Functions
Counting Rows
Counting Columns
Counting with a WHERE Clause
Using GROUP BY
Using GROUP BY with GROUP BY
Using HAVING with GROUP BY
TOP Values Queries
Joining Tables
Cross Joins (Cartesian Products)
The Use of Keys in Joining
Join Notation
Inner Joins
Outer Joins
Self Joins

Modifying Data

Modifying Data
Inserting Data
Inserting a Single Value
Inserting Multiple Values
Inserting Multiple Rows
Creating a New Table Using SELECT INTO
Temporary Tables
Using Bulk Copy to Insert Data
Updating Data
Updating a Single Row
Updating Multiple Rows and Columns
Updating from Another Table
Updating with TOP
Updating Large Value Types with UPDATE.WRITE
Deleting Data
Deleting a Single Row
Deleting Multiple Rows
Understanding Transaction Isolation
Isolation Levels Explained
Blocking and Deadlocks
Using Snapshot Isolation

Transact-SQL Programming

Overview of Transact-SQL
Transact-SQL Extensions
Batches and Scripts
Variables
Delimiters and Operators
Transact-SQL and Data Types
Using Built-In Functions
Working with Nulls
Handling Numbers
Manipulating Strings
Working with Date and Time Values
Using the @@ Functions
Controlling Flow
IF ELSE
BEGIN END
GOTO, RETURN, and Labels
CASE
WHILE
WAITFOR
Ranking Results

Transactions and Error Handling

Transaction Concepts
Passing the ACID Test
Transaction Types
Avoiding Blocked Transactions
Working around Deadlocks
Applications and Transactions
Designing Transactional Support
Understanding Compile and Runtime Errors
Creating Explicit Transactions
Explicit Transaction Syntax
Transact-SQL Error Handling in Transactions
Using RAISERROR
Using TRY/CATCH Error Handling
TRY/CATCH Overview
Using TRY/CATCH in a Stored Procedure
Handling Uncommittable Transactions with XACT_STATE

Creating Views

What is a View?
Advantage of Views
Views and Security
Creating Views
View Rules
View Syntax and Options
Tools for Creating Views
View Examples
Nesting Views, Derived Tables, and CTEs
Encrypting View Definitions
Updating Data Using a View
Updating Rules
Updating Behavior
Using Computed Columns
Creating a Computed Column
Indexed Views
How Indexed Views Work
Partitioned Views