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.
This course gives you advanced SQL query coding. We place significant emphasis on practical tips and techniques as well as performance considerations.
Firm up your knowledge of selects, predicates, scalar and aggregate functions, group by, simple and correlated sub-selects, union, and inner and outer joins.
Who should attend?
Anyone who needs to code complex SQL. Primary emphasis is DB2 11 for z/OS however most content is applicable to any database application environment.
Any prerequisites?You should have a basic working knowledge of SQL, and a solid understanding of how to code basic SQL SELECT statements. Ideally, you should have completed one of the following Profience's courses:
DB2 11 for z/OS SQL Queries Workshop
Interactive SQL Workshop
DB2 COBOL Application Programming Workshop
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
- 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