Course Information

Course Outline

 Writing Queries Using Microsoft SQL Server 2008 Transact-SQL

Introduction
At the end of 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.

Audience
IT Professionals, IT generalists, and Information Workers who want to learn about writing queries for Microsoft SQL Server 2005. Candidates should have at least 3 years experience working in the IT field-typically in the area of databases. No experience with SQL Server is necessary.

At Course Completion
After completing 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.

· 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. For example, how to use Windows Explorer, open and save files, and what a client/server application interaction means.

· Exam 70-431: TS: Microsoft SQL Server 2005-Implementation and Maintenance.:

· 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

· Using SQL Server Management Studio

· Describe the architecture and components of SQL Server 2005.

· Describe the structure of a SQL Server database.

· Explain the basics of the SQL language.

· Describe the syntax elements of T-SQL.

· Explain how to manage T-SQL scripts.

· Use T-SQL querying tools to query SQL Server 2005 databases.

· Using the SELECT Statement

· Filtering Data

· Working with NULL Values

· Formatting Result Sets

· Performance Considerations for Writing Queries

· Retrieving data from tables

· Formatting the result set

· Using efficient search arguments

· Retrieve data by using the SELECT statement.

· Filter data by using different search conditions.

· Explain how to work with NULL values.

· Format result sets.

· Describe the performance considerations that affect data retrieval.

· Summarizing Data by Using Aggregate Functions

· Summarizing Grouped Data

· Ranking Grouped Data

· Creating Crosstab Queries

· Using aggregate functions

· Summarizing grouped data

· Ranking grouped data

· Pivoting data

· Summarize data by using aggregate functions.

· Summarize grouped data by using the GROUP BY and COMPUTE clauses.

· Rank grouped data.

· Create cross-tabulation queries by using the PIVOT and UNPIVOT clauses.

· Querying Multiple Tables by Using Joins

· Applying Joins for Typical Reporting Needs

· Combining and Limiting Result Sets

· Joining data from multiple tables

· Combining result sets from multiple queries

· Query multiple tables by using joins.

· Apply joins for typical reporting needs.

· Combine and limit result sets.

· Writing Basic Subqueries

· Writing Correlated Subqueries

· Comparing Subqueries with Joins and Temporary Tables

· Using Common Table Expressions

· Performing subqueries

· Using temporary tables

· Using Common Table Expressions

· Write basic subqueries.

· Write correlated subqueries.

· Compare subqueries with joins and temporary tables.

· Use common table expressions in queries.

· Overview of Transactions

· Inserting Data into Tables

· Deleting Data from Tables

· Updating Data in Tables

· Inserting data

· Deleting data

· Updating data

· Implement client access servers.

· Implement client access features.

· Implement Outlook Web Access.

· Implement Mobile Messaging.

· Querying Metadata

· Overview of XML

· Querying XML Data

· Overview of Full-Text Indexes

· Querying Full-Text Indexes Implementing Message Transport

· Querying metadata

· Querying XML data

· Query Full-Text indexes

· Query metadata.

· Describe the functionality of XML.

· Query XML data.

· Describe the functionality of full-text indexes.

· Query full-text indexes.

· Encapsulating Expressions by Using User-Defined Functions

· Encapsulating Queries by Using Views

· Overview of Stored Procedures

· Writing Distributed Queries

· Using User-Defined Functions

· Using Views

· Querying distributed data

· Encapsulate expressions by using user-defined functions.

· Encapsulate queries by using views.

· Explain how stored procedures encapsulate T-SQL logic.

· Write distributed queries.

· 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

· Writing efficient queries

· Solving complex reporting requirements

· Explain the recommendations for querying complex data.

· Query complex table structures.

· Write efficient queries.

· Use various techniques when working with complex queries.