Exploring Transact-SQL

Overview of SQL Server and Transact-SQL

Programming in Transact-SQL

Querying Data

Using the SELECT Statement

Formatting the Results Set

Changing Data Through Queries

Inserting/Deleting/Updating Data

Summarizing Data

Working with Aggregate Functions

Grouping Data

Joining Tables

Querying Multiple Tables

Implementing Advanced Table Joins

Implementing Subqueries

Designing Subqueries

Working with Full-text Indexes

Implementing the Microsoft Search Service

Querying Full-text Indexes

Programming Transact-SQL

Creating and Managing Views

Triggers,User-defined Functions

An Overview of SQL Server

Exploring the Components of SQL Server

 Exploring Features of Microsoft SQL Server

 Exploring the SQL Server Services

 Identifying Administrative Utilities

 Using SQL Server Enterprise Manager

 Using SQL Query Analyzer

SQL Server Database Structure

 Discussing the Components of SQL Server

 Identify Default Databases / System Tables

 Creating/Working with a Database Diagram

 Understanding the SQL Server Architecture

 Exploring the Application Architecture

Identifying SQL Server Management Tasks

Determining SQL Management Tasks

Configuring Server’s Authentication Mode

Creating a SQL Login Account

Working With Server and Database Roles

Exploring Transact-SQL

Working with Transact-SQL

 Using SQL Query Analyzer

 Using Osql

Transact-SQL Statements

 Creating a Simple Table

 Inserting Data

 Selecting Data

 Assigning Permissions to Public DB Role

Programming in Transact-SQL

 Using Variables

 Saving a Script File

 Choosing an XML Format

Designing and Implementing Databases

Identifying Database Design Issues

 Designing Databases

 Identify Space Requirements for Databases

 Optimizing Database Performance

Creating Databases

 Setting Database Options

 Use Stored Procedures to View Information

Managing Databases

 Configuring to Auto Shrink a DatabaseFile

 Deleting a Database

Designing & Implementing Data Integrity

Understanding Data Integrity

Implementing Constraints

 Adding a Primary Key Constraint

 Adding a Foreign Key Constraint

 Adding a Default Constraint

 Adding a Check Constraint

 Disabling Constraint Checking

Implementing Indexes

Designing Indexing

 Understanding Index Architecture

Implementing Indexes

 Create Clustered Index on the Movie Table

 Viewing the Information in Sysindexes

Maintaining Indexes

 Rebuilding an Index

 Recreate PrimaryKey Index

Generating a Workload File in SQL Profiler

 Observing Index Statistics

Joining Tables

Querying Multiple Tables

 Working with Cross Joins

Implementing Advanced Table Joins

 Joining Multiple Tables

 Working with Self Joins

 Combining SELECT Statements

 Creating New Table with SELECT INTO

Designing Advanced Queries

Designing Subqueries

 Designing Single Value Subqueries

Changing Data Through Queries

 Inserting Data Based on a Query

 Deleting Rows Based on a Query

Designing Views

Creating and Managing Views

 Creating a View

 Displaying View Information

 Dropping a View

 Creating an Indexed View

 Creating a Partitioned View

Creating Stored Procedures

Designing Stored Procedures

 Research Stored Procedures Online

 Running Extended Stored Procedures

 Understanding Processes

Creating Stored Procedures

 Creating a Stored Procedure

 Executing a Stored Procedure

Using Parameters in Stored Procedures

 Create With an Input Parameter

 With Output Parameters

Managing Stored Procedures

 Recompiling a Stored Procedure

Using Functions

Working with Aggregate Functions

 Aggregate Functions to Summarize Data

 Designing GROUP BY Queries

 Using TOP in a Query

Designing/Creating User-defined Functions

 Dropping a User-defined Function