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. Once you master the full power of SQL queries, it will enable you to solve many complex business problems easily, efficiently and elegantly with only a few lines of SQL code. This is useful for all who want to utilize the full power of SQL to increase their productivity.

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, and many programmers are unaware of the huge potential this language has. This course provides an in-depth understanding enabling you to take full advantage of SQL's capabilities.

The hard part of SQL is to guarantee that a query will always produce a correct result. This course describes typical errors and pitfalls that could potentially produce wrong results. Being aware of those pitfalls will help you in writing correct and robust queries.

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.

Which version of SQL Server is used in the course?

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. The course also describes a few new features which will become available in SQL Server 2012.

What platform is used for the hands-on exercises?

This course uses Microsoft SQL Server 2008 R2 running on Windows Server 2008. A pre-production version of SQL Server 2012 is also available, for the benefit of those who want to check out some of the new features.

Who will benefit from this course?

This course is valuable for all who want to utilize the full power of SQL. Typical participants include:

• Programmers, analysts, developers, designers, consultants and others who design and write SQL Server applications
• Business analysts and power users who need to extract data from their SQL Server databases
• DBAs who want to become proficient in querying and manipulating a SQL Server database with SQL
• Those who are familiar with SQL in general but wish to learn the special features of SQL Server's 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.

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.

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.

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?

• 137, SQL Server® 2008: A Comprehensive Hands-On Introduction is strongly recommended for anyone who is not familiar with the basics of SQL Server.
• 925, SQL Programming Language: A Comprehensive Hands-On Introduction 
• 532, SQL Server Transact-SQL® Programming: Hands-On focuses on SQL Server from a developer perspective. This course uses Transact-SQL and .NET to accomplish building stored procedures and triggers.
• 535, Developing High-Performance SQL Server® Databases: Hands-On provides the skills needed to achieve a high-performance SQL Server database solution.