SQL Server 2005 Writing Queries Short Course

Section A: Relational Databases

• Introduction
• About Databases and SQL Server
• Data Types and Nulls
• Table Relationships

Section B: Starting with SQL

• Client/Server Architecture
• SQL Server Management Studio
• SQL Language
• Batches
• OSQL and SQLCMD

Section C: Introduction to Transact-SQL

• Statement Types
• Language Elements

Section D: SELECT Statements

• Basic Elements
• Column Aliases
• Four-Part Name

Section E: Operators and Expressions

• Arithmetic
• Operator Precedence
• Unary and Bitwise Operators
• String Concatenation
• Table Alias

Section A: Filtering with Comparison Operators

• WHERE Clause
• Comparison Operators

Section B: Filtering with Logical Operators

• Logical Operators
• Operator Precedence

Section C: Filtering with Range of Values

• BETWEEN Operator
• BETWEEN Syntax

Section D: Filtering with Pattern Matching

• LIKE Clause
• Percent and Wildcard
• Other Wildcards

Section E: Writing Good WHERE Clauses

• What Is a SARG?
• Optimizer Plans
• Query Plans

Section F: Handling Missing Values

• Defining Null Values
• Null Basics
• Null Functions

Section G: Scalar Functions

• Function Types
• Date/Time Functions
• Metadata Functions

Section A: Result Sets

• Sorting
• Eliminating Duplicates

Section B: Using Variables

• Local Variables
• Scalar Variables
• EXEC Command
• Value in SELECT
• Variable Examples

Section C: Control of Flow

• Control of Flow Commands
• IF and CASE Statements
• WHILE Statement
• Simple CASE Expression

Section D: CASE and Ranking Functions

• Converting Codes
• Searched CASE
• Ranking Functions

Section E: Aggregate Functions

• Defining Aggregates
• Aggregate Examples
• Nulls and Aggregates
• CHECKSUM

Section F: Grouping and Summarizing Data

• GROUP BY
• HAVING

Section G: Advanced Grouping and Summarizing

• ROLLUP and CUBE
• COMPUTE and COMPUTE BY
• PIVOT and UNPIVOT

Section H: Retrieving Data with INNER JOINS

• Defining INNER JOINS
• Understanding Joins
• Why Use Joins?

Section I: Retrieving Data with OUTER/CROSS JOINS

• Defining OUTER and CROSS JOINS
• Using OUTER JOINS
• Join Tables with RANGE

Section A: Combining and Limiting Result Sets

• Unions
• UNION Operator UNION ALL
• TOPn
• INTERSECT and EXCEPT
• TABLESAMPLE

Section B: Basic Subqueries

• Subqueries
• Using a Subquery

Section C: Correlated Subqueries

• Using a Correlated Subquery
• EXISTS/NOT EXISTS Function

Section D: Common Table Expressions

• Non-Recursive CTEs
• Using Non-Recursive CTEs

Section E: Recursive (Queries) CTEs

• Traversing Hierarchies

Section F: Modifying Data with INSERT

• Defining an INSERT
• INSERT SELECT and INSERT EXEC

Section G: Modifying Data with SELECT INTO

• SELECT INTO

Section H: Modifying Data with DELETE

• Deleting Rows

Section I: Modifying Data with UPDATE

• Updating Rows

Section A: OUTPUT Clause

• OUTPUT and INSERTED Clauses
• Output to Tables
• DELETED Clause

Section B: Querying Full-Text Indexes

• Full-Text Indexes
• CONTAINS
• Table Versions and FREETEXT

Section C: Using XML

• XML-Defined
• Storing XML Data
• Querying XML Data
• XML Indexes
• Selecting

Section D: Programming Objects for Data Retrieval

• Views
• Selecting from Views
• Updatable Views
• WITH CHECK OPTION

Section E: User Defined Functions

• Scalar UDFs
• Using Scalar UDFs

Section F: Inline and Multi-Statement Functions

• Table Value Functions
• Inline Table Values
• Multi-Statement Table Values

Section G: Stored Procedures

• Stored Procedures Defined
• Creating a Stored Procedure
• Stored Procedure Parameters

Section H: Advanced Query Techniques

• Using CONVERT
• Heterogeneous Queries