OneSource Professional Training Solutions, Inc. presents
 

Writing Queries Using Microsoft SQL Server 2008 Transact-SQL On-Site Training

Writing Queries Using Microsoft SQL Server 2008 Transact-SQL

Course Description/Agenda

Gain the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2008 in this course that incorporates material from the Official Microsoft Learning Product (OMLP) 2778: Writing Queries Using Microsoft SQL Server 2008 Transact-SQL.

What You'll Learn

• Uses of and ways to execute the Transact-SQL language
• Use querying tools
• Write SELECT queries to retrieve data
• Group and summarize data by using Transact-SQL
• Join data from multiple tables
• Write queries that retrieve and modify data by using subqueries
• Modify data in tables
• Query text fields with full-text search
• Create programming objects
• Use various techniques when working with complex queries

Who Should Attend

SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries

Course Prerequisites

• Knowledge of data integrity concepts
• Core Windows Server skills
• Relational database design skills
• Programming skills

Course Outline

1. Getting Started with Databases and Transact-SQL in SQL Server 2008

• Overview of SQL Server 2008
• Overview of SQL Server Databases
• Overview and Syntax Elements of T-SQL
• Working with T-SQL Scripts
• Using T-SQL Querying Tools

2. Querying and Filtering Data

• Using the SELECT Statement
• Filtering Data
• Working with NULL Values
• Formatting Result Sets
• Performance Considerations for Writing Queries

3. Grouping and Summarizing Data

• Summarizing Data by Using Aggregate Functions
• Summarizing Grouped Data
• Ranking Grouped Data
• Creating Crosstab Queries

4. Joining Data from Multiple Tables

• Querying Multiple Tables by Using Joins
• Applying Joins for Typical Reporting Needs
• Combining and Limiting Result Set

5. Working with Subqueries

• Writing Basic Subqueries
• Writing Correlated Subqueries
• Comparing Subqueries with Joins and Temporary Tables
• Using Common Table Expressions

6. Modifying Data in Tables

• Inserting Data into Tables
• Deleting Data from Tables
• Updating Data in Tables
• Overview of Transactions

7. Querying Metadata, XML, and Full-Text Indexes

• Querying Metadata
• Overview of XML
• Querying XML Data
• Overview of Full-Text Indexes
• Querying Full-Text Indexes

8. Using Programming Objects for Data Retrieval

• Overview of Views
• Overview of User-Defined Functions
• Overview of Stored Procedures
• Overview of Triggers
• Writing Distributed Queries

9. Using Advanced Querying Techniques

• Considerations for Querying Complex Data
• Working with Data Types
• Cursors and Set-Based Queries
• Dynamic SQL
• Maintaining Query Files

Labs

Lab 1: Getting Started with Databases and Transact-SQL in SQL Server 2008

• Exploring the Components and Executing Queries in SQL Server Management Studio
• Starting and Using SQLCMD
• Generating a Report from a SQL Server Database Using Microsoft Office Excel

Lab 2: Querying and Filtering Data

• Retrieving Data by Using the SELECT Statement
• Filtering Data by Using Search Conditions
• Using Functions to Work with NULL Values
• Formatting Result Sets

Lab 3: Grouping and Summarizing Data

• Summarizing Data by Using Aggregate Functions
• Summarizing Grouped Data
• Ranking Grouped Data
• Creating Crosstab Queries

Lab 4: Joining Data from Multiple Tables

• Querying Multiple Tables by Using Joins
• Applying Joins for Typical Reporting Needs
• Combining and Limiting Result Sets

Lab 5: Working with Subqueries

• Writing Basic Subqueries
• Writing Correlated Subqueries
• Comparing Subqueries with Joins and Temporary Tables
• Using Common Table Expressions

Lab 6: Modifying Data in Tables

• Inserting Data into Tables
• Deleting Data from Tables
• Updating Data in Tables
• Working with Transactions

Lab 7: Querying Metadata, XML, and Full-Text Indexes

• Querying Metadata
• Querying XML Data
• Creating and Querying Full-Text Indexes

Lab 8: Using Programming Objects for Data Retrieval

• Creating Views
• Creating User-Defined Functions
• Creating Stored Procedures
• Writing Distributed Queries

Lab 9: Using Advanced Querying Techniques

• Using Execution Plans
• Converting Data Types
• Implementing a Hierarchy
• Using Cursors and Set-Based Queries

More Seminar Information

OneSource Professional Training Solutions, Inc. Delivery Method  On-Site Training Also Available As  Seminar