Writing Queries Using Microsoft® SQL Server™ 2005 Transact-SQL

This 18-hour, 9-course online collection is comprised of a rich multimedia experience coupled with comprehensive technical labs.

This collection introduces you to Transact-SQL starting with basic SELECT queries, then advancing to grouping, joins, subqueries, data modification, transactions, and heterogeneous data sources.

This collection is intended for IT Professionals who must access and manipulate data in SQL Server 2005 databases and other data sources.

After completing this collection, you 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.
To get the most out of this collection, you should:
Be familiar with a programming or scripting language.
Understand relational databases and the concept of data normalization
This collection also helps prepare you for certification exam 70-431.

Course 4326: Getting Started with Databases and Transact-SQL in Microsoft® SQL Server™ 2005

Description:
In this course, you analyze how client/server architecture works, and examine the various database and business tasks that you can perform by using the components of Microsoft SQL Server 2005. You also query SQL Server 2005 databases by using T-SQL querying tools. In addition, this course explains the basics of the T-SQL language, its syntax, and how to manage T-SQL scripts. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
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.
 
Course 4327: Performing Basic Queries in Microsoft® SQL Server™ 2005

Description:
In this course, you perform basic queries in Microsoft SQL Server 2005. You use Transact-SQL (T-SQL) statements such as SELECT to write queries, filter data, and format result sets. In addition, this course covers the performance considerations that affect data retrieval and the use of the Query Optimizer. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
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.
 
Course 4328: Grouping and Summarizing Data in Microsoft® SQL Server™ 2005

Description:
In this course, you group and summarize data when generating reports in Microsoft SQL Server 2005. You rank group data and create cross-tabulation queries. You also use aggregate functions including functions native to SQL Server, functions that use NULL values, and custom aggregate functions using the .NET Framework. In addition, this course covers the GROUP BY, HAVING, COMPUTE, PIVOT, UNPIVOT, ROLLUP, and CUBE clauses and functions such as SUM, AVG, COUNT, MAX, MIN, GROUPING, RANK, DENSE_RANK, NTILE, and ROW_NUMBER. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
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.
 
Course 4329: Joining Data from Multiple Tables in Microsoft® SQL Server™ 2005

Description:
In this course, you retrieve data from multiple tables by using Transact-SQL (T-SQL) in Microsoft SQL Server 2005. You apply the inner join, outer join, cross join, and self join to retrieve the desired result set. You also use the APPLY operator to join using a user-defined function. In addition, you examine the various techniques provided by SQL Server 2005 to combine and limit result sets such as UNION, EXCEPT, INTERCEPT, TOP, and TABLESAMPLE. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
Query multiple tables by using joins.
Apply joins for typical reporting needs.
Combine and limit result sets.
 
Course 4330: Working with Subqueries in Microsoft® SQL Server™ 2005

Description:
In this course, you write subqueries and correlated subqueries by using Transact-SQL (T-SQL) in Microsoft SQL Server 2005. You determine the best option among subqueries, joins, and temporary tables to get your desired result. You refine subqueries by using the ANY, ALL, and SOME operators. In addition, this course explains common table expressions (CTEs) to write recursive queries. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
Write basic subqueries.
Write correlated subqueries.
Compare subqueries with joins and temporary tables.
Use common table expressions in queries.
 
Course 4331: Modifying Data in Microsoft® SQL Server™ 2005

Description:
In this course, you modify data in tables using Transact-SQL (T-SQL) in Microsoft SQL Server 2005. You insert, delete, and update single or multiple rows of data in the tables. You use statements such as INSERT, DELETE, UPDATE, INSERT"¦SELECT, SELECT INTO, and TRUNCATE TABLE statements. In addition, you examine implicit and explicit transactions, the importance of transaction isolation levels, and how to manage transactions. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
Explain how SQL Server implements transactions.
Insert data into tables.
Delete data from tables.
Update data in tables.
 
Course 4332: Querying Metadata, XML, and Full-Text Indexes in Microsoft® SQL Server™ 2005

Description:
In this course, you query metadata, XML data, and full-text indexes by using Transact-SQL (T-SQL) in Microsoft SQL Server 2005. You query metadata by using SQL Server views and commands. You query XML data by using OpenXML and XQuery. You also query relational data combined with XML data. In addition, you analyze the purpose and process of querying full-text indexes. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
Query metadata.
Describe the functionality of XML.
Query XML data.
Describe the functionality of full-text indexes.
Query full-text indexes.
 
Course 4333: Using Programming Objects for Data Retrieval in Microsoft® SQL Server™ 2005

Description:
In this course, you retrieve data by using the various programming objects provided by Microsoft SQL Server 2005. You encapsulate expressions with user-defined functions and SQL Server views. You also utilize stored procedures, and triggers to encapsulate T-SQL logic. In addition, you perform distributed queries and learn how SQL Server works with heterogeneous data such as databases, spreadsheets, and other servers. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
Encapsulate expressions by using user-defined functions.
Encapsulate queries by using views.
Explain how stored procedures encapsulate T-SQL logic.
Write distributed queries.
 
Course 4334: Using Advanced Querying Techniques in Microsoft® SQL Server™ 2005

Description:
In this course, you apply advanced querying techniques in Microsoft SQL Server 2005. You follow best practices to query complex data involving data/time and multinational data. You query complex table structures such as data stored in hierarchies and self-referencing tables. You also analyze query execution and optimize query performance. In addition, you use Microsoft Visual SourceSafe for managing query source files. This course also helps prepare you for certification exam 70-431.

Objectives:
At the end of the course, students will be able to:
Explain the recommendations for querying complex data.
Query complex table structures.
Write efficient queries.
Use various techniques when working with complex queries.
Maintain query files.