New Horizons Computer Learning Center
410-597-9722

Writing Queries Using Microsoft SQL Server 2005 Transact-SQL Course Outline

Course Length: 3 Days

Overview:
In this three-day course, students who are new to SQL Server will learn how to write basic Transact-SQL queries for Microsoft SQL Server 2005.

Prerequisites:
An understanding of basic relational database concepts, including: Logical database design
Physical database design
How data is stored in tables (rows and columns)
Data integrity concepts
Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, and many-to-many)
Basic knowledge of the Microsoft Windows operating system and its core functionality

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

• Get started with databases and Transact-SQL
• Perform basic queries
• Group and summarize data
• Join data from multiple tables
• Work with subqueries
• Modify data
• Query metadata, XML, and full-text indexes
• Use programming objects for data retrieval
• Use advanced querying techniques

Course Content

Lesson 1: Getting Started with Databases and Transact-SQL in Microsoft SQL Server 2005

• Overview of SQL Server 2005
• Overview of SQL Server Databases
• Overview of the SQL Language
• Syntax Elements of T-SQL
• Working with T-SQL Scripts
• Using T-SQL Querying Tools
• Lab: Getting Started with Databases and T-SQL
• Using SQL Server Management Studio

Lesson 2: Performing Basic Queries in Microsoft SQL Server 2005

• Using the SELECT Statement
• Filtering Data
• Working with NULL Values
• Formatting Result Sets
• Performance Considerations for Writing Queries
• Lab: Performing Basic Queries
• Retrieving data from tables
• Formatting the result set
• Using efficient search arguments

Lesson 3: Grouping and Summarizing Data in Microsoft SQL Server 2005

• Summarizing Data by Using Aggregate Functions
• Summarizing Grouped Data
• Ranking Grouped Data
• Creating Crosstab Queries
• Lab: Grouping and Summarizing Data
• Using aggregate functions
• Summarizing grouped data
• Ranking grouped data
• Pivoting data

Lesson 4: Joining Data from Multiple Tables in Microsoft SQL Server 2005

• Querying Multiple Tables by Using Joins
• Applying Joins for Typical Reporting Needs
• Combining and Limiting Result Sets
• Lab: Joining Data from Multiple Tables
• Joining data from multiple tables
• Combining result sets from multiple queries

Lesson 5: Working with Subqueries in Microsoft SQL Server 2005

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

Lesson 6: Modifying Data in Microsoft SQL Server 2005

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

Lesson 7: Querying Metadata, XML, and Full-Text Indexes in Microsoft SQL Server 2005

• Querying Metadata
• Overview of XML
• Querying XML Data
• Overview of Full-Text Indexes
• Querying Full-Text Indexes Implementing Message Transport
• Lab: Querying Metadata, XML, and Full-Text Indexes
• Querying metadata
• Querying XML data
• Query Full-Text indexes

Lesson 8: Using Programming Objects for Data Retrieval in Microsoft SQL Server 2005

• Encapsulating Expressions by Using User-Defined Functions
• Encapsulating Queries by Using Views
• Overview of Stored Procedures
• Writing Distributed Queries
• Lab: Using Programming Objects for Data Retrieval in Microsoft SQL Server 2005
• Using User-Defined Functions
• Using Views
• Querying distributed data

Lesson 9: Using Advanced Querying Techniques in Microsoft SQL Server 2005

• Recommendations for Querying Complex Data
• Querying Complex Table Structures
• Writing Efficient Queries
• Explain the recommendations for querying complex data.
• Using Different Techniques When Working with Complex Queries
• Maintaining Query Files
• Lab: Using Advanced Querying Techniques
• Writing efficient queries
• Solving complex reporting requirements

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