New Horizons Computer Learning Center
410-597-9722

2778A: Writing Queries Using Microsoft SQL Server 2008 Transact-SQL

Course Length: 3 Days

Overview:
This 3-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2008.

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

Course Objectives:
Upon successful completion of this course, students will be able to:

• Describe the uses of and ways to execute the Transact-SQL language
• Use querying tool
• 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 sub queries
• Modify data in tables
• Query text fields with full-text search
• Describe how to create programming objects
• Use various techniques when working with complex queries

Course Content

Lesson 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
• Lab: Using SQL Server Management Studio and SQLCMD
  • 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

Lesson 2: Querying and Filtering Data

• Using the SELECT Statement
• Filtering Data
• Working with NULL Values
• Formatting Result Sets
• Performance Considerations for Writing Queries
• Lab: Querying and Filtering Data
  • Retrieving Data by Using the SELECT Statement
  • Filtering Data by Using Different Search Conditions
  • Using Functions to Work with NULL Values
  • Formatting Result Sets

Lesson 3: Grouping and Summarizing Data

• Summarizing Data by Using Aggregate Functions
• Summarizing Grouped Data
• Ranking Grouped Data
• Creating Crosstab Queries
• Lab: Grouping and Summarizing Data
  • Summarizing Data by Using Aggregate Functions
  • Summarizing Grouped Data
  • Ranking Grouped Data
  • Creating Crosstab Queries

Lesson 4: Joining Data from Multiple Tables

• Querying Multiple Tables by Using Joins
• Applying Joins for Typical Reporting Needs
• Combining and Limiting Result Set
• Lab: Joining Data from Multiple Tables
  • Querying Multiple Tables by Using Joins
  • Applying Joins for Typical Reporting Needs
  • Combining and Limiting Result Sets

Lesson 5: Working with Subqueries

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

Lesson 6: Modifying Data in Tables

• Inserting Data into Tables
• Deleting Data from Tables
• Updating Data in Tables
• Overview of Transactions
• Lab: Modifying Data in Tables
  • Inserting Data into Tables
  • Deleting Data from Tables
  • Updating Data in Tables
  • Working with Transactions

Lesson 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
• Lab: Querying Metadata, XML, and Full-Text Indexes
  • Querying Metadata
  • Querying XML Data
  • Creating and Querying Full-Text Indexes

Lesson 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
• Lab: Using Programming Objects for Data Retrieval
  • Creating Views
  • Creating User-Defined Functions
  • Creating Stored Procedures
  • Writing Distributed Queries

Lesson 9: Using Advanced Querying Techniques

• Considerations for Querying Data
• Working with Data Types
• Cursors and Set-Based Queries
• Dynamic SQL
• Maintaining Query Files
• Lab: Using Advanced Querying Techniques
  • Using Execution Plans
  • Converting Data Types
  • Implementing a Hierarchy
  • Using Cursors and Set-Based Queries

6940 Tudsbury Road, Baltimore, MD 21244
P: 410-597-9722
www.nhbaltimore.com