Pré-requis

This course requires that participants meet the following prerequisites:

Core Microsoft Windows Server™ skills:

• Navigation
• Configuration
• Monitoring tools
• Control panel – Services
• Windows Explorer

Programming skills:

• Familiarity and experience with a programming or scripting language.

Relational database knowledge:

• Relational theory
• Set theory
• Database vs. spreadsheet

Database design skills:

• 3NF
• Normalization

Objectifs

The purpose of this course is to provide participants with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2005.

Contenu

Module 1: Getting Started with Databases and
Transact-SQL

Module 2: Performing Basic Queries

Module 3: Grouping and Summarizing Data

Module 4: Joining Data from Multiple Tables

Module 5: Working with Subqueries

Module 6: Modifying Data

Module 7: Querying Full-Text Indexes and XML Data

Module 8: Using Programming Objects for Data Retrieval

Module 9 Advanced Querying Techniques Labs

Module 1 Lab: Getting Started with Databases and Transact-SQL

• Exercise 1: Exploring SQL Server
• Management Studio
• Exercise 2: Executing Queries in SQL Server
• Management Studio
• Exercise 3: Examining a Database Diagram in SQL Server
• Management Studio
• Exercise 4: Using Microsoft Excel® to Generate a Report Against SQL Server 2005

Module 2 Lab: Performing Basic Queries

• Exercise 1: Retrieving Data by Using the Select Statement
• Exercise 2: Filtering Data by Using Different Search Conditions
• Exercise 3: Using Functions to Work with Null Values
• Exercise 4: Formatting Result Sets
• Exercise 5: Rewriting Queries for Performance

Module 3 Lab: Grouping and Summarizing Data

• Exercise 1: Summarizing Data by Using Aggregate Functions
• Exercise 2: Summarizing Grouped Data
• Exercise 3: Ranking Grouped Data
• Exercise 4: Creating Crosstab Queries by Using the PIVOT and UNPIVOT Clauses

Module 4 Lab: Joining Data from Multiple Tables

• Exercise 1: Querying Multiple Tables by Using Joins
• Exercise 2: Applying Joins for Typical Reporting Needs
• Exercise 3: Combining and Limiting Result Sets

Module 5 – 9 Labs:

• to be determined (design in process)