G555 Advanced SQL Query Building

Advanced SQL Query Building - Topical Outline

The Sample Data Base

Review of Relational Concepts: Select, Project, Join

Access Strategies in DB2

EXPLAIN, and the Use of Indexes

Computer Exercise: EXPLAIN and PLAN_TABLEs

Performance Considerations

DB2 Processing Sequence

Predicate Characteristics

Indexable, Stage 1, and Stage 2 Predicates

Filter Factors

Aggregated Queries

Built-in Column Functions

GROUP BY

HAVING

ORDER BY

Computer Exercise: Data Aggregates

Special Registers

Dates, Times, Timestamps, and Durations

Built-in Scalar Functions

Nested Table Expressions

Computer Exercise: Special Registers and Scalar Functions

Joins

Name Specification

Primary Keys and Foreign Keys

Joins on non-key columns

Joins and aggregates

Join Access Strategies

Computer Exercise: Joins

Joins, Part 2

Outer Joins

Outer Joins and Nested Table Expressions

The "Classic" Join Syntax

Influencing the Join Access Path

Computer Exercise: Outer Joins

Subqueries

IN and NOT IN

Arithmetic Comparison

ANY and ALL

EXISTS and NOT EXISTS

Correlated subqueries

Finding mismatches between tables

Subquery Access Strategies

Computer Exercise: Subqueries

UNION and UNION ALL

Query parallelism

EXPLAINing a Package or Application Plan

Programming Considerations

Static and Dynamic SQL

Cursor issues

Optional Computer Exercise: UNION and Plan Explains

G555 / 2 Days

Materials version: V6.x

Course Objectives
Course Description
Home page