Why take this course?
After completing this course, you will be able to
- correctly code single and multiple OUTER/INNER JOINS to handle NULLS.
- use the UNION, EXCEPT, and INTERSECT operators
to combine multiple result sets into a single result set.
- code Nested and Common Table Expressions.
- write recursive SQL using Common Table Expressions.
- use Row Expressions to extend the power of predicate evaluation.
- nest sub-queries and use them to enhance update and insert SQL as well as the HAVING clause.
- optimize sub-query execution using Order By and Fetch First.
- use Data Change Tables.
- use the Case Expression within Functions.
- use the ARRAY type and associated functions.
- understand the major factors contributing to database application performance.
- learn how tablespace, table, and index design effect performance.
- have a comprehensive understanding of how application design affects performance.
- learn about the impact of concurrency on performance.
- understand how database optimizers works.
- gain an in-depth knowledge of database access Paths.
- become fully conversant in the use of tools such the Explain facility, including the use of Optimizer hints.
- be able to judge the efficiency of SQL statements and tune ineffective queries.
- learn to use utilities as part of the performance tuning process.
This 5-day, hands-on course examines database performance issues from the application programmer's perspective.
We'll discuss the factors affecting database performance and give you practical tips and techniques for designing and implementing efficient database applications. You'll also look at advanced SQL and get an in-depth study of efficient coding techniques. Hands-on exercises ensure an effective learning experience.
Who should attend?
Application designers, programmer analysts, programmers and database administrators who design, code, or support database applications. Primary emphasis is DB2 11 for z/OS however most content is applicable to any database application environment.
Any prerequisites?
You need to be familiar with the SQL language and have some application programming background.
Dates and Fees
5 Days 
- $ 1,747 (CAD) Up to 3 - $ 3,494 Up to 6 - $ 5,244 Plus - $ 874 Sorry, no classes are currently scheduled, please contact us to request a class
Advanced SQL Topics
Review
Advanced Multi-table Inner and Outer Joins
- Multi-Table Inner Joins
- Multi-Table Outer Joins
- Table Join Processing
- Join Guidelines
Advanced Unions, Exists and Intersects
- Advanced Unions
- Excepts
- Intersects
Table Expressions
- Nested Table Expressions
- Common Table Expressions
- Recursive SQL
Advanced Sub-Query Usage and Optimization
- Type of Sub-Queries
- Scalar Full Selects
- List Full Selects
- Row Expressions
- Optimization Options
Data Change Tables and Case Expressions
- Data Change Tables
- Case Expressions
Arrays and Array Functions
- Array Types and Usage
- Aggregation and Removal
- First, Next and Prior
- Cardinality
