Opmerking

Bij deze training volgt u de eLearning MS2778 (voorbereiding op examen 70-431) in de Open Master Class en onder actieve begeleiding van een ervaren en gecertificeerde docent. Alle benodigde en beschreven eLEarning modules (4326, 4327, 4328, 4329, 4330, 4331, 4332, 4333, 4334) zijn inbegrepen.

Omschrijving

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.

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.

Doelgroep

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

Voorkennis

To get the most out of this collection, you should be familiar with a programming or scripting language and understand relational databases and the concept of data normalization.

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

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.

Course 4327: Performing Basic Queries in Microsoft SQL Server 2005

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.

Course 4328: Grouping and Summarizing Data in Microsoft SQL Server 2005

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.

Course 4329: Joining Data from Multiple Tables in Microsoft SQL Server 2005

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.

Course 4330: Working with Subqueries in Microsoft SQL Server 2005

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.

Course 4331: Modifying Data in Microsoft SQL Server 2005

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.

Course 4332: Querying Metadata, XML, and Full-Text Indexes in Microsoft SQL Server 2005

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.

Course 4333: Using Programming Objects for Data Retrieval in Microsoft SQL Server 2005

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.

Course 4334: Using Advanced Querying Techniques in Microsoft SQL Server 2005

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.