Developing SQL Queries for SQL Server^®:
Hands-On

What is this course about?

In this course, you learn to write robust, reliable and efficient SQL queries to retrieve data from database tables.

What is SQL Server? What is Transact-SQL?

Microsoft's SQL Server is a full-featured relational database management system with support for robust server-side development and data replication. It is a key component of Microsoft's Windows Server System.

Transact-SQL is Microsoft's dialect of SQL. It is used to create databases, manipulate data administer databases. Knowledge of this language is essential for those who are developing applications for SQL Server.

Isn't SQL a simple language to learn?

The syntax of SQL is simple, but using SQL to its maximum power is not. This course provides an in-depth understanding of the language necessary to take full advantage of SQL's capabilities.

It can be difficult to guarantee that a SQL statement will produce the correct result. This course focuses heavily on the pitfalls and dangers of writing seemingly correct queries that nevertheless potentially could produce wrong results.

What background do I need?

You should be familiar with SQL, particularly the basic constructs of the SELECT statement. If you are new to SQL or feel that you need to review the basics, you should first take Course 137, SQL Server^® 2008: A Comprehensive Hands-On Introduction, or Course 925, SQL Programming Language: A Comprehensive Hands-On Introduction, which provides the needed prerequisite knowledge.

Does this course cover SQL Server 2000, SQL Server 2005, or SQL Server 2008?

The course runs on SQL Server 2008. However, the fundamentals of SQL are not version dependent. Most of the course material applies to earlier versions as well as SQL Server 2008. Features that are new to version 2008 make up a small part of the course and are clearly identified. And even if you are currently using an older version of SQL Server, it will be useful for you to learn about features which will be available once you upgrade.

What platform is used for the hands-on exercises?

This course uses Microsoft SQL Server 2008 R2 running on Windows Server 2008.

Who will benefit from this course?

This course is valuable for all who want to utilise the full power of SQL to increase their productivity, including programmers and application designers, database administrators and advanced power users who write their own ad hoc queries. Typical participants include:

• Programmers, analysts, developers, designers, consultants and others who design and write Microsoft SQL Server applications
• DBAs who want to become proficient in querying and manipulating a SQL Server database with Transact-SQL
• Those who are familiar with SQL but wish to learn the special features of the Transact-SQL dialect
• Experienced data processing professionals working with traditional database systems-hierarchical or network-who are now moving to the relational world

I mostly use frontend tools that generate SQL automatically. Do I still need this course?

Most frontend tools that generate SQL statements require you to enhance the statements or add in your own statements in order to use the full capabilities of the tool. Therefore, the ability to write complex SQL statements will be a useful skill when working with such tools.

Is this a complete Transact-SQL programming course?

This course focuses on the query and data retrieval aspects of Transact-SQL. It does not teach procedural Transact-SQL programming, updating data or database definition. Course 532, SQL Server Transact-SQL^® Programming: Hands-On, is recommended for those who want more in-depth knowledge of Transact-SQL programming.

How does this course differ from Course 532?

Course 534 covers Transact-SQL, but focuses on developing robust and efficient queries using the SELECT statement. It is designed to teach you how to solve complex business problems with SQL.

Conversely, Course 532, SQL Server Transact-SQL^® Programming: Hands-On, is designed to cover the syntax and usage of the Transact-SQL programming language and include some details of the ANSI standard SELECT statement, but the focus of the course is Microsoft's extensions to SQL, particularly the procedural extensions used to develop stored procedures, user-defined functions and triggers.

To acquire a complete command of the Transact-SQL language, we recommend taking both courses.

I am planning to take both this course and Course 532. Which should I take first?

Although Course 534 doesn't require the material covered in 532, you will find this course easier if you take 532 first.

How much time is spent on each topic?

How much hands-on experience will I get?

You spend about 50% of the course doing hands-on exercises. Every concept addressed in the course is illustrated with both examples and hands-on exercises.

Does the course cover the ANSI SQL standard?

This course focuses on the advantages of Microsoft's implementation of SQL. SQL Server has some powerful additional features that are not ANSI-standard compatible, but are very useful in a real-world environment. The course covers such features in detail.

In cases in which Transact-SQL and ANSI SQL have similar functionality with different syntax, the course points out what the ANSI syntax is but doesn't go into extensive detail.

Is performance a main topic in this course?

Database performance issues become increasingly important as queries become more complex. This course shows how different queries can perform the same task and give identical results, but with very different performance. You learn how to choose the appropriate query to achieve the best performance for a given task.

How does this course relate to other Learning Tree courses?

• 532, SQL Server Transact-SQL® Programming: Hands-On focuses on SQL Server from a developer perspective. This course details using Transact-SQL and .NET to accomplish building stored procedures and triggers.
• 925, SQL Programming Language: A Comprehensive Hands-On Introduction 
• 535, Developing High-Performance SQL Server® Databases: Hands-On provides the skills needed to achieve a high-performance SQL Server 2000 database solution.
• 382, Relational Database Design, Tools and Techniques: Hands-On focuses on what relational databases are, why they are used, and how to design and set up a relational database.

You might also be interested in these Learning Tree courses:

• 137, SQL Server® 2008: A Comprehensive Hands-On Introduction 
• 523, SQL Server® 2005 Reporting Services: Hands-On 
• 140, SQL Server® 2008 Reporting Services: Hands-On 

UK Dates

US East Coast Dates