CERTIFICATIONS

PROGRAMS

LEARN > Find a Class > Class Summary

TSQL05 - Writing T-SQL Queries In SQL Server 2005

If you have any questions about registering for this class, please call (877) 832-0688 Ext. 1493 or email us at getsmart@LRS.com

In this course, you’ll learn how to build basic queries using Transact-SQL, the language of SQL Server. Then, you'll learn how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL. You'll learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and programmers can now use .NET languages like C# and Visual Basic to build database objects.

Click here for a printer-friendly version of this page

Prerequisites

A solid understanding of relational databases is recommended.

Detailed Class Syllabus

A Tour of SQL Server 2005

SQL Server 2005 Editions, Components, and Tools

Editions

Server Components

Management Tools

Documentation and Samples

Using SQL Server Management Studio (SSMS)

Connecting Management Studio

The Object Explorer

Database Objects

Working with Tables and Queries

Creating Tables

Creating a View

Generating Scripts

New Transact-SQL Features

Common Table Expressions (CTEs)

Ranking Functions

TOP(n) and APPLY

PIVOT

FOR XML PATH

Try/Catch Error Handling

Using EXECUTE AS

Using Snapshot Isolation

Running CLR Code in SQL Server 2005

Business Intelligence Services

SQL Server Integration Services (SSIS)

SQL Server Business Intelligence Development Studio (BIDS)

Reporting Services

Analysis Services (SSAS)

Designing and Creating a Database

Relational Database Design Principles

The Origins of Relational Design

Data Normalization

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

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 Expressions

The GROUP BY Clause

Aggregate Functions

Counting Rows

Counting Columns

Counting with a WHERE Clause

Using GROUP BY

Using ORDER BY with GROUP BY

Using HAVING with GROUP BY

TOP Value 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

Working with SQL Server Management Studio

Getting Started with SSMS

Connecting to Management Studio

Configuring SSMS Options

SSMS Toolbars

Overview of SSMS Menu Options

Configuring SSMS Windows

Exploring the Object Explorer

Right-Click Menu Options

Server and Database Objects

Displaying and Filtering Objects

Finding Objects

Editing Database Objects in the SSMS Designers

Working with the Query Editor

Displaying Multiple Code Windows

Formatting and Editing Code

Scripting with SSMS

Executing Queries

Creating Projects and Solutions

Working Offline

Using SQL Server Books Online

Getting Help in SSMS

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 Stored Procedure

Handling Uncommittable Transactions in XACT_STATE

Creating Views

What Is a View?

Advantages of Views

Views and Security

Creating Views

View Rules

View Syntax and Options

Tools for Creating Views

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

Creating Stored Procedures and Triggers

Creating Stored Procedures

Stored Procedure Features

Stored Procedures Performance Benefits

Reasons to Use Stored Procedures

Stored Procedure Syntax

Creating Stored Procedures Using SSMS

Creating and Executing Stored Procedures

Working with Parameters

Testing and Debugging Stored Procedures

Creating Triggers

Uses for Triggers

How Triggers Work

Creating an AFTER Trigger

Creating an INSTEAD OF Trigger on a View

Creating a DDL Trigger to Restrict Table Creation

Creating User-Defined Functions

User-Defined Function Overview

Scalar Functions

Scalar Function Syntax

Inline Table-Valued Functions

Inline Syntax

Executing Inline Table-Valued Functions

Inline Syntax

Executing Inline Table-Valued Functions

Inline Functions with Parameters

Updating Data with Inline Functions

Multi-Statement Table-Valued Functions

Multi-Statement Function Syntax

Executing Multi-Statement Functions

Joining to Functions

Using Functions, Views, and Stored Procedures

Using Scalar Functions

Using Table-Valued Functions

Choosing Between Functions, Views, and Stored Procedures

Using .NET Code in SQL Server 2005

Writing SQLCLR Code

Creating SQLCLR Code Manually

Creating SQLCLR Code Using Visual Studio 2005

SQL Data Types

Accessing Local Data

Returning Results

SQLCLR Code Modules

Stored Procedures and Triggers

User-Defined Functions

Aggregates

Managing Code Modules

System Catalogs

Troubleshooting

SQLCLR Security

Code Access Security (CAS)

SQLCLR Permission Sets

T-SQL vs. .NET Code

Advanced Query Techniques

Full-Text Search

SQL Server 2005 Enhancements

Configuring Full-Text Search

Writing Full-Text Queries

The CONTAINS Predicate

The FREETEXT Predicate

Using CONTAINSTABLE and FREETEXTTABLE

Using Full-Text DDL

Generating XML with FOR XML

Using AUTO Mode

Using RAW Mode

Using EXPLICIT Mode

Using PATH Mode

Using APPLY

Comparing APPLY to Using Joins

Using APPLY with Table-Valued Functions

Combining APPLY with FOR XML PATH

Creating Recursive Queries

Using a Common Table Expression

Creating Pivot Queries

Rotating Column Data in Column Headings

Executing Dynamic SQL

Overcoming PIVOT Limitations

Using Parameters with sp_executesql

Using QUOTENAME()

Using sp_executesql with Output Parameters

Signing Stored Procedures with Certificates