Developing SQL Queries for SQL Server®

Course 534 | 4 Days

Attendee Rating

Evaluations in the Last 12 Months
  • 5 stars: 79%
    5 Star Ratings: 79%
  • 4 stars: 20%
    4 Star Ratings: 20%
  • 3 stars: 1%
    3 Star Ratings: 1%
  • 2 stars: 0%
    2 Star Ratings: 0%
  • 1 star: 0%
    1 Star Ratings: 0%
4.77 stars (517 reviews)
Developing SQL Queries for SQL Server

You Will Learn How To:

  • Develop complex and robust SQL queries, test queries and avoid common pitfalls
  • Leverage SQL to apply best practices for solving problems
  • Query multiple tables with inner joins, outer joins and self-joins
  • Transform data with built-in functions
  • Summarize data using aggregation and grouping
Course participants building correlated subqueries.

Hands-On Experience Includes:

  • Handling NULL values in expressions and conditions
  • Implementing self-joins and coding inner and outer joins
  • Computing aggregate results
  • Employing ranking and analytic functions
  • Reusing subqueries as common table expressions
  • Analyzing query plans and tuning queries

About This Course: Microsoft's Transact-SQL is the cornerstone of all SQL Server database operations. In this hands-on course, you learn to exploit the full potential of the SELECT statement to write robust queries using the best query method for your application, test your queries and avoid common errors and pitfalls.


NEW! FREE After-Course Instructor Coaching - available after May 1, 2014.
Click here to learn more about this & other Attendee Benefits

  • Course Info

    Course 534 Content

    Introduction and Overview

    SQL fundamentals

    • Retrieving data with SELECT
    • Expressions
    • Literals
    • Handling NULLs properly

    Executing queries

    • Analyzing query plans
    • Enhancing query performance
    • Selecting the best alternatives
    • Avoiding errors and pitfalls

    Querying Multiple Tables

    Implementing various types of joins

    • Inner joins
    • Cross joins
    • Left, right and full outer joins
    • Equijoins vs. theta joins
    • Adding filter conditions to outer joins

    Writing self-joins

    • Joining a table to itself
    • Chaining self-joins
    • Solving time-interval problems

    Combining queries with set operators

    • UNION
    • UNION ALL
    • INTERSECT
    • EXCEPT

    Scalar and Aggregate Functions

    Taking advantage of scalar functions

    • Converting datatypes
    • Performing calculations on dates and times
    • Extracting date and time components
    • Manipulating strings
    • Choosing the right function for the job

    Summarizing data with aggregate functions

    • COUNT
    • SUM
    • AVG
    • MIN
    • MAX
    • Managing NULLs
    • Suppressing duplicates

    Grouping data

    • GROUP BY and GROUP BY ALL
    • Applying conditions with HAVING

    Extending group queries

    • Nesting grouped aggregates
    • Joins and grouping

    Building crosstab reports

    • Using CASE to turn rows into columns
    • Applying PIVOT

    Performing Analysis with Analytic Functions

    The OVER clause

    • Specifying the ordering before applying the function
    • Splitting the result set into logical partitions

    Calculating ranks

    • RANK and DENSE_RANK
    • ROW_NUMBER with ordered sets

    Extending the use of aggregates

    • Partitioning in multiple levels
    • Comparing row and aggregate values

    Building Subqueries

    Simple subqueries

    • Subqueries in conditions and column expressions
    • Creating multilevel subqueries
    • Avoiding problems when subqueries return NULLs

    Correlated subqueries

    • Accessing values from the outer query
    • Avoiding accidental correlation

    Common table expressions

    • Reusable and recursive subqueries
    • Traversing hierarchies

    Breaking Down Complex Queries

    • Overcoming SQL limitations
    • Reducing complexity and improving performance
    • Exploring alternatives for decomposing: temporary tables, views, common table expressions

    Who Should Attend

    Those who are developing systems using SQL Server databases, or who are using SQL to extract and analyze data from SQL Server databases and have knowledge at the level of Course 2107, "SQL Server 2012 Comprehensive Introduction," Course 137, "SQL Server 2008 Comprehensive Introduction," or Course 925, "SQL Programming Language Introduction."

    Training Hours

    Standard class hours:
    9:00 a.m. - 4:30 p.m.

    Last day class hours:
    9:00 a.m. - 3:30 p.m.

    Free optional course exam:
    3:30 p.m. - 4:30 p.m.

    Each class day:
    Informal discussion with instructor about your projects or areas of special interest:
    4:30 p.m. - 5:30 p.m.

    Important Note:
    These training hours apply to our Regular Start Time Course Events and not to our Afternoon Start Time Course Events, which have varied training hours. Please see your course confirmation for later training hours or contact us at 1-888-THE-TREE (843-8733).

  • More Course Info

    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 Introduction, Course 2107, SQL Server® 2012 Introduction, or Course 925, SQL Programming Language Introduction, which provides the needed prerequisite knowledge.

    Which version of SQL Server is used in the course?

    The course runs on SQL Server 2012. However, the fundamentals of SQL are not version dependent. Most of the course material applies to earlier versions as well as SQL Server 2012. Features that are new to versions 2012, 2008 or 2005 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 2012 running on Windows Server 2008.

    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?

    Content Hours
    Introduction and Overview 2.5
    Querying Multiple Tables 5.0
    Scalar and Aggregate Functions 5.0
    Performing Analysis with Analytic Functions 4.0
    Building Subqueries 4.0
    Breaking Down Complex Queries 2.5

    Times, including the workshops, are estimates; exact times may vary according to the needs of each class.

    How much hands-on experience will I get?

    Approximately 50% of class time is devoted to hands-on exercises that provide immediate reinforcement of material covered. Exercises include:

    • Handling NULL values in expressions and conditions
    • Implementing self-joins and coding inner and outer joins
    • Computing aggregate results
    • Employing ranking and analytic functions
    • Reusing subqueries as common table expressions
    • Analyzing query plans and tuning queries

    I'm attending this course from work using AnyWare - Learning Tree's web-based remote attendance platform. How will that impact what I learn?

    You will participate fully in the course and acquire the same knowledge and skills as your classmates who participate in the classroom. You will have the same course materials, be able to easily communicate back and forth and ask questions of your instructors and peers, and you will control an in-classroom workstation dedicated entirely to you. Your instructor will be able to see exactly what you're doing and can interactively offer concrete help.

    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, 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, 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?

  • Certification/Credits

    Many Learning Tree courses provide college credit and industry continuing education credits. You can also earn a Learning Tree Professional Certification in your area of expertise and prepare for popular industry certifications. See below for continuing professional development credits associated with this course.

    College Credit – 2 Semester Hours This course qualifies for 2 semester hours of college credit as certified by the American Council on Education's College Credit Recommendation Service (ACE CREDIT). Read More...
    CPE 23 CreditsThis course qualifies for 23 CPE credits from the National Association of State Boards of Accountancy CPE program. Read More...
  • Tuition

    4-Day TuitionNew AttendeeReturning AttendeeNotes
    Commercial $2,810$2,530

    Click here for multi-course savings plans.

    Tuition payment is not required at time of enrollment.

    Government $2,499$2,249

Guaranteed to Run Guaranteed to Run event

Scheduled Event Scheduled event

Afternoon Start Time Event Afternoon Start Time event

LiveLinked Event LiveLinked event

Course Dates
4 Days

Attend from Home, Work or AnyWare Center Via AnyWare Learn More »

AnyWare

Attend In-Class or LiveLinked at an Education Center
Learn More »

Ed. Center

1

First - Select Your Date:

2

Then - Select Your Attendance Method:

*NOTE: Los Angeles events run from 9am-4:30pm Pacific Time thus also from 12pm-7:30pm Eastern Time

Your Attendance Options
for this Instructor-Led Course

AnyWare

Attend from Home, Work or your nearest AnyWare Learning Center via AnyWare

Attend via AnyWare

Experience your course from home, work or your nearest AnyWare Learning Center and enjoy the same highly effective, hands-on instructor-led training using AnyWare – our superior, web-based course attendance platform. To learn more about AnyWare, Click here »

Education Center

Attend from one of our Education Centers or from a Attend from a LiveLinked ClassroomLiveLinked Classroom™

Attend in an Education Center

In-Class at an Education Center

Experience your course in a purpose-built Education Center that provides many unique amenities. To learn more about our Education Centers, Click here »

From a Attend from a LiveLinked ClassroomLiveLinked Classroom

Experience your course in a custom-designed LiveLinked Classroom, which is connected to your instructor-led course at the hosting education center. You and your fellow attendees are provided with:

  • Dedicated computers
  • Multiple large high-definition displays
  • An advanced audio and video system

To learn more about LiveLinked Classrooms, Click here »

Tuition & Savings Plans

Take advantage of our Multi-Course Tuition Savings Plans. Learn more »



Enroll Today – Hassle Free!

Call 1-888-843-8733 or enroll online by selecting your date above.

  • No advance payment required.
  • No fees for cancellation or rescheduling, ever.
  • Take up to 30 days after your course date to pay.
  • Satisfaction guaranteed – or you pay no tuition.

You can bring this course on-site to your organization

Bring Learning Tree Training to your workplace with our On-Site Training Solution. Learn more »


"The Learning Tree SQL Queries course was well developed and covered the course material in great technical detail."

– T. Walton III
IT Specialist
Department of Agriculture